3

I am trying to learn Spark. I have a org.apache.spark.sql.Column which I am reading in as a DataFrame. And then I am trying to filter it using a condition on a column:

val resultDataFrame = dataFrame.filter(col("DATECOL") >= date)

The DATECOL is being read as DataTypes.DateTypein to the DataFrame. date is a variable that I have to hardcode.

What I am trying to figure out is how can I define date i.e. how can I create an instance of DataTypes.DateType or convert to it from a String or so, so that I can run the above expression. I tried using a String and it does not give an error, but it returns with no results where it should.

rgamber
  • 5,749
  • 10
  • 55
  • 99

1 Answers1

5

You can make it a java.sql.Date:

val df = Seq(("2016-10-10", 2), ("2017-02-02", 10)).toDF("DATECOL", "value")

val df1 = df.withColumn("DATECOL", to_date($"DATECOL"))
// df1: org.apache.spark.sql.DataFrame = [DATECOL: date, value: int]

df1.show
+----------+-----+
|   DATECOL|value|
+----------+-----+
|2016-10-10|    2|
|2017-02-02|   10|
+----------+-----+

val date = java.sql.Date.valueOf("2016-11-01")
// date: java.sql.Date = 2016-11-01

df1.filter($"DATECOL" > date).show
+----------+-----+
|   DATECOL|value|
+----------+-----+
|2017-02-02|   10|
+----------+-----+
Psidom
  • 209,562
  • 33
  • 339
  • 356
  • 1
    hold on, a `String` works as well! I think I made a mistake looking at the results earlier. – rgamber Feb 03 '17 at 03:15
  • Hmm. A string of standard format does work, this will be just in case you have some [peculiar case](http://stackoverflow.com/questions/41703517/create-a-new-column-based-on-date-checking). – Psidom Feb 03 '17 at 03:18
  • Sorry, what do you mean [Peculiar case]? Is using a String not a "recommended" way to go in this case? – rgamber Feb 03 '17 at 03:20
  • No, I mean if some times you have non standard format string that spark doesn't understand, such as `2016-OCT-12`. But since you are hard coding here, it is actually irrelevant. – Psidom Feb 03 '17 at 03:22
  • Makes sense. Thanks, this helps! – rgamber Feb 03 '17 at 03:23