Spark Scala-尝试从时间戳提取时间时为Null

debugcn 发表于 Dev

峰值状态

我正在尝试使用以下代码从时间戳中提取时间，但它返回的是空值而不是时间。我已经过滤了数据集以获取所需的记录，因此可以忽略来自输入列的AM / PM。

我做了一些阅读，看来date_format在这种情况下应该可以使用。

有什么想法吗？

电流输出：

+----------------------+----------------------+---------+------------+------------+
|tpep_pickup_datetime  |tpep_dropoff_datetime |timestamp|total_amount|pickupWindow|
+----------------------+----------------------+---------+------------+------------+
|05/18/2018 09:56:20 PM|05/18/2018 10:50:38 PM|35780    |52.87       |null        |
|05/18/2018 10:52:49 PM|05/18/2018 11:08:47 PM|39169    |14.76       |null        |
|05/18/2018 09:01:22 PM|05/18/2018 09:05:36 PM|32482    |6.3         |null        |
|05/18/2018 09:00:29 PM|05/18/2018 09:05:31 PM|32429    |7.56        |null        |
+----------------------+----------------------+---------+------------+------------+

当前代码：

val taxiSub = spark.read.format("csv").option("header", true).option("inferSchema", true).load("/user/zeppelin/taxi/TaxiSubset.csv") //read Data
taxiSub.createOrReplaceTempView("taxiSub") //Create View
val stamp = taxiSub.withColumn("timestamp", unix_timestamp($"tpep_pickup_datetime", "MM/dd/yyyy hh:mm:ss")) //create timestamp
val h = hour(unix_timestamp($"tpep_pickup_datetime","MM/dd/yyyy hh:mm:ss").cast("timestamp"))
val subset= stamp.withColumn("hour",h).filter("hour BETWEEN 9 AND 10").where($"tpep_pickup_datetime".contains("PM")).filter($"total_amount" < 200.00) //filter records between 9pm and 11pm and < 200 total amount

val myData = subset.withColumn("tmp",to_timestamp(col("tpep_pickup_datetime"),"MM/dd/yyyy HH:mm:ss")).//add new timestamp type field
withColumn("timestamp", unix_timestamp(concat_ws(":",hour(col("tmp")),minute(col("tmp")),second(col("tmp"))),"hh:mm:ss")). //extract hour,minute and convert to epoch timestamp value
drop("tmp").select("tpep_pickup_datetime","tpep_dropoff_datetime","timestamp","total_amount")

val testing = myData.withColumn("pickupWindow",date_format($"tpep_pickup_datetime","hh:mm:ss"))
testing.show(false)

Shu

.dateformat()期望colyyyy-MM-dd [hh|HH]:mm:ss格式的值，但输入数据具有MM / dd / yyyy..etc。

首先，我们需要tpep_pickup_datetime使用to_timestamp函数将其转换为时间戳，然后将其应用于date_format提取hh:mm:ss。

Example:

df.show(false)
//+----------------------+
//|tpep_pickup_datetime  |
//+----------------------+
//|05/18/2018 09:56:20 PM|
//|05/18/2018 10:52:49 PM|
//+----------------------+

//to get 24hr format HH value
df.withColumn("pickupWindow",date_format(to_timestamp(col("tpep_pickup_datetime"),"MM/dd/yyyy hh:mm:ss a"),"HH:mm:ss")).
show()

//using from_unixtime,unix_timestamp
df.withColumn("pickupWindow",from_unixtime(unix_timestamp(col("tpep_pickup_datetime"),"MM/dd/yyyy hh:mm:ss a"),"HH:mm:ss")).show()

//+--------------------+------------+
//|tpep_pickup_datetime|pickupWindow|
//+--------------------+------------+
//|05/18/2018 09:56:...|    21:56:20|
//|05/18/2018 10:52:...|    22:52:49|
//+--------------------+------------+

//to get 12hr format hh value
df.withColumn("pickupWindow",date_format(to_timestamp(col("tpep_pickup_datetime"),"MM/dd/yyyy hh:mm:ss a"),"hh:mm:ss")).
show()

//Or using unix_timestamp,from_unixtime
df.withColumn("pickupWindow",from_unixtime(unix_timestamp(col("tpep_pickup_datetime"),"MM/dd/yyyy hh:mm:ss a"),"hh:mm:ss")).show()

//+--------------------+------------+
//|tpep_pickup_datetime|pickupWindow|
//+--------------------+------------+
//|05/18/2018 09:56:...|    09:56:20|
//|05/18/2018 10:52:...|    10:52:49|
//+--------------------+------------+

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。