基于Spark DataFrame生成指定日期过去36个月的迭代日期问题

Since you're working with Spark 1.4 (which doesn't have the add_months function introduced in Spark 1.5), we'll use a mix of Scala's native date utilities and Spark core APIs to generate the required date sequence. Here's a step-by-step implementation tailored to your environment:

Step 1: Extract the Maximum Timestamp Value #

First, we'll pull the latest date from your FiloDB dataset and convert it into a usable timestamp value:

import org.apache.spark.sql.functions.max

// Load data and compute the maximum invoice date
val vGetDateDF = hc.read.format("filodb.spark")
  .option("database", "YYYYY")
  .option("dataset", "XXX")
  .load()
  .agg(max("inv_date").alias("max_date"))

// Extract the timestamp (handle cases where no data exists)
val maxTimestampOpt = Option(vGetDateDF.select("max_date").first().getTimestamp(0))

Step 2: Generate the 36-Month Date Sequence #

Using java.util.Calendar, we'll iterate backward 36 times, subtracting one month each iteration. This handles edge cases like months with fewer days (e.g., 31st March → 28th February in non-leap years):

import java.util.Calendar
import java.sql.Timestamp

val past36Months = maxTimestampOpt.map { ts =>
  val calendar = Calendar.getInstance()
  calendar.setTime(ts)
  
  // Generate dates by subtracting one month per iteration
  (1 to 36).map { _ =>
    calendar.add(Calendar.MONTH, -1)
    new Timestamp(calendar.getTimeInMillis)
  }.reverse // Uncomment for oldest → newest order; remove for newest → oldest
}.getOrElse(Seq.empty[Timestamp])

Step 3: Convert to Spark DataFrame (Optional) #

If you need the dates as a Spark DataFrame for further processing with Cassandra or FiloDB, convert the sequence:

// Create a DataFrame with a single "month_date" column
val datesDF = hc.createDataFrame(past36Months.map(ts => (ts))).toDF("month_date")

// Preview the results
datesDF.show(36)

Key Notes: #

• Null Safety: The Option wrapper prevents crashes if your source dataset has no records.
• Date Adjustments: Calendar automatically adjusts days for months with fewer days, ensuring valid dates every time.
• Order Control: The reverse call reorders the sequence to start from the oldest date (36 months prior) and end at one month before your max date. Remove it if you want the sequence to start from max date minus 1 month and go back 36 steps.

内容的提问来源于stack exchange，提问作者Rajdip