How to get week of year in spark 3.0+?

user_10168218 0
Viewed 2952

I'm trying to create a calendar file with columns for day, month, etc. The following code works fine, but I couldn't find a clean way to extract the week of year (1-52). In spark 3.0+, the following line of code doesn't work: .withColumn("week_of_year", date_format(col("day_id"), "W"))

I know that I can create a view/table and then run a SQL query on it to extract the week_of_year, but is there no better way to do it? `

df.withColumn("day_id", to_date(col("day_id"), date_fmt))
.withColumn("week_day", date_format(col("day_id"), "EEEE"))
.withColumn("month_of_year", date_format(col("day_id"), "M"))
.withColumn("year", date_format(col("day_id"), "y"))
.withColumn("day_of_month", date_format(col("day_id"), "d"))
.withColumn("quarter_of_year", date_format(col("day_id"), "Q"))
apache-spark-sql apache-spark scala
2 Answers

It seems those patterns are not supported anymore in spark 3+

Caused by: java.lang.IllegalArgumentException: All week-based patterns are unsupported since Spark 3.0, detected: w, Please use the SQL function EXTRACT instead

You can use this:

 import org.apache.spark.sql.functions._

df.withColumn("week_of_year", weekofyear($"date"))

TESTING

INPUT

    val df  = List("2021-05-15", "1985-10-05")
               .toDF("date")
               .withColumn("date", to_date($"date", "yyyy-MM-dd")

df.show
    +----------+
    |      date|
    +----------+
    |2021-05-15|
    |1985-10-05|
    +----------+

OUTPUT

 df.withColumn("week_of_year", weekofyear($"date")).show
+----------+------------+
|      date|week_of_year|
+----------+------------+
|2021-05-15|          19|
|1985-10-05|          40|
+----------+------------+
user_63783110

The exception you saw, recomend to use EXTRACT SQL function instead https://spark.apache.org/docs/3.0.0/api/sql/index.html#extract

 val df =  Seq(("2019-11-16 16:50:59.406")).toDF("input_timestamp")
 df.selectExpr("input_timestamp", "extract(week FROM input_timestamp) as w").show
 +--------------------+---+
 |     input_timestamp|  w|
 +--------------------+---+
 |2019-11-16 16:50:...| 46|
 +--------------------+---+
user_45583580
Related