如何按日期范围在星火SQL过滤 [英] How to filter by date range in Spark SQL

问题描述

我试图使用数据的砖块下面的数据，这是响应返回null过滤器的时间范围。
我的CSV数据是这样的：

I'm trying to filter the date range from the following data using Data bricks, which returns null as response. My csv data looks like:

ID, Desc, Week_Ending_Date
100, AAA, 13-06-2015
101, BBB, 11-07-2015
102, CCC, 15-08-2015
103, DDD, 05-09-2015
100, AAA, 29-08-2015
100, AAA, 22-08-2015

我的查询是：

df.select(df("ID"), date_format(df("Week_Ending_Date"), "yyyy-MM-dd"))
.filter(date_format(df("Week_Ending_Date"), "yyyy-MM-  dd").between("2015-07-05", "2015-09-02"))

任何帮助很多AP preciated。

Any help is much appreciated.

推荐答案

从我的头顶，我会做通过转换日期列在阅读它，然后使用别名应用滤镜以下内容：

From the top of my head, I would have done the following by converting the date column while reading it and then apply the filter using an alias :

import java.text.SimpleDateFormat

val format = new SimpleDateFormat("dd-MM-yyyy")
val data = sc.parallelize(
  List((100, "AAA", "13-06-2015"), (101, "BBB", "11-07-2015"), (102, "CCC", "15-08-2015"), (103, "DDD", "05-09-2015"), (100, "AAA", "29-08-2015"), (100, "AAA", "22-08-2015")).toSeq).map {
  r =>
    val date: java.sql.Date = new java.sql.Date(format.parse(r._3).getTime);
    (r._1, r._2, date)
}.toDF("ID", "Desc", "Week_Ending_Date")

data.show

//+---+----+----------------+
//| ID|Desc|Week_Ending_Date|
//+---+----+----------------+
//|100| AAA|      2015-06-13|
//|101| BBB|      2015-07-11|
//|102| CCC|      2015-08-15|
//|103| DDD|      2015-09-05|
//|100| AAA|      2015-08-29|
//|100| AAA|      2015-08-22|
//+---+----+----------------+

val filteredData = data.select(data("ID"), date_format(data("Week_Ending_Date"), "yyyy-MM-dd").alias("date")).filter($"date".between("2015-07-05", "2015-09-02"))

//+---+----------+
//| ID|      date|
//+---+----------+
//|101|2015-07-11|
//|102|2015-08-15|
//|100|2015-08-29|
//|100|2015-08-22|
//+---+----------+

这篇关于如何按日期范围在星火SQL过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！