Scala:Spark SQL to_date(unix_timestamp)返回NULL [英] Scala: Spark SQL to_date(unix_timestamp) returning NULL
问题描述
Spark Version: spark-2.0.1-bin-hadoop2.7
Scala: 2.11.8
Spark Version: spark-2.0.1-bin-hadoop2.7
Scala: 2.11.8
我正在将原始csv加载到DataFrame中.在csv中,尽管该列支持日期格式,但它们被写为20161025而不是2016-10-25.参数date_format
包含需要转换为yyyy-mm-dd格式的列名称字符串.
I am loading a raw csv into a DataFrame. In csv, although the column is support to be in date format, they are written as 20161025 instead of 2016-10-25. The parameter date_format
includes string of column names that need to be converted to yyyy-mm-dd format.
在下面的代码中,我首先通过schema
将Date列的csv作为StringType加载,然后检查date_format
是否不为空,即是否有需要转换为String
开始,然后使用unix_timestamp
和to_date
强制转换每一列.但是,在csv_df.show()
中,返回的行都是null
.
In the following code, I first loaded the csv of Date column as StringType via the schema
, and then I check if the date_format
is not empty, that is there are columns that need to be converted to Date
from String
, then cast each column using unix_timestamp
and to_date
. However, in the csv_df.show()
, the returned rows are all null
.
def read_csv(csv_source:String, delimiter:String, is_first_line_header:Boolean,
schema:StructType, date_format:List[String]): DataFrame = {
println("|||| Reading CSV Input ||||")
var csv_df = sqlContext.read
.format("com.databricks.spark.csv")
.schema(schema)
.option("header", is_first_line_header)
.option("delimiter", delimiter)
.load(csv_source)
println("|||| Successfully read CSV. Number of rows -> " + csv_df.count() + " ||||")
if(date_format.length > 0) {
for (i <- 0 until date_format.length) {
csv_df = csv_df.select(to_date(unix_timestamp(
csv_df(date_format(i)), "yyyy-MM-dd").cast("timestamp")))
csv_df.show()
}
}
csv_df
}
返回前20行:
+-------------------------------------------------------------------------+
|to_date(CAST(unix_timestamp(prom_price_date, YYYY-MM-DD) AS TIMESTAMP))|
+-------------------------------------------------------------------------+
| null|
| null|
| null|
| null|
| null|
| null|
| null|
| null|
| null|
| null|
| null|
| null|
| null|
| null|
| null|
| null|
| null|
| null|
| null|
| null|
+-------------------------------------------------------------------------+
为什么我会全部得到null
?
推荐答案
要将yyyyMMdd
转换为yyyy-MM-dd
,您可以:
spark.sql("""SELECT DATE_FORMAT(
CAST(UNIX_TIMESTAMP('20161025', 'yyyyMMdd') AS TIMESTAMP), 'yyyy-MM-dd'
)""")
具有功能:
date_format(unix_timestamp(col, "yyyyMMdd").cast("timestamp"), "yyyy-MM-dd")
这篇关于Scala:Spark SQL to_date(unix_timestamp)返回NULL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!