如何在sparksql中获得两个日期之间的月、年差异 [英] how to get months,years difference between two dates in sparksql

问题描述

我收到错误:

org.apache.spark.sql.analysisexception: cannot resolve 'year'

我的输入数据:

1,2012-07-21,2014-04-09

我的代码:

val sqlContext = new org.apache.spark.sql.SQLContext(sc)
import sqlContext.implicits._
import org.apache.spark.sql.SaveMode
import org.apache.spark.sql._
import org.apache.spark.sql.functions._
case class c (id:Int,start:String,end:String)
val c1 = sc.textFile("date.txt")
val c2 = c1.map(_.split(",")).map(r=>(c(r(0).toInt,r(1).toString,r(2).toString)))
val c3 = c2.toDF();
c3.registerTempTable("c4")
val r = sqlContext.sql("select id,datediff(year,to_date(end), to_date(start)) AS date from c4")

如何解决上述错误?

我已经尝试了以下代码，但我在几天内得到了输出，我在几年内需要它

I have tried the following code but I got the output in days and I need it in years

val r = sqlContext.sql("select id,datediff(to_date(end), to_date(start)) AS date from c4")

请告诉我是否可以使用任何像 to_date 这样的函数来获得年差.

Please advise me if i can use any function like to_date to get year difference.

推荐答案

val r = sqlContext.sql("select id,datediff(year,to_date(end), to_date(start)) AS date from c4")

在上面的代码中，year"不是数据框中的列，即它不是表c4"中的有效列，这就是为什么当查询无效时抛出分析异常，查询无法找到年份"列.

In the above code, "year" is not a column in the data frame i.e it is not a valid column in table "c4" that is why analysis exception is thrown as query is invalid, query is not able to find the "year" column.

使用 Spark 用户定义函数 (UDF)，这将是一个更强大的方法.

Use Spark User Defined Function (UDF), that will be a more robust approach.

这篇关于如何在sparksql中获得两个日期之间的月、年差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！