TIMESTAMP AS OF in Delta Lake OSS?

34 views
Skip to first unread message

Jacek Laskowski

unread,
Jul 6, 2021, 8:41:10 AM7/6/21
to Delta Lake Users and Developers
Hi,

I've just stumbled upon the following in the official documentation of Delta Lake at https://docs.delta.io/latest [1]:

SELECT * FROM events TIMESTAMP AS OF '2018-10-18T22:15:12.013Z'
SELECT * FROM delta.`/delta/events` VERSION AS OF 123

I tried it out in Delta Lake OSS 1.0.0 and failed:

scala> sql("SELECT * FROM events TIMESTAMP AS OF '2018-10-18T22:15:12.013Z'")
== SQL ==
SELECT * FROM events TIMESTAMP AS OF '2018-10-18T22:15:12.013Z'
-------------------------------^^^

  at org.apache.spark.sql.catalyst.parser.ParseException.withCommand(ParseDriver.scala:255)
  at org.apache.spark.sql.catalyst.parser.AbstractSqlParser.parse(ParseDriver.scala:124)
  at org.apache.spark.sql.execution.SparkSqlParser.parse(SparkSqlParser.scala:49)
  at org.apache.spark.sql.catalyst.parser.AbstractSqlParser.parsePlan(ParseDriver.scala:75)
  at io.delta.sql.parser.DeltaSqlParser.$anonfun$parsePlan$1(DeltaSqlParser.scala:72)
  at io.delta.sql.parser.DeltaSqlParser.parse(DeltaSqlParser.scala:99)
  at io.delta.sql.parser.DeltaSqlParser.parsePlan(DeltaSqlParser.scala:69)

Looks like DeltaSqlParser gave up and requested SparkSqlParser for support.

Is TIMESTAMP AS OF supported in Delta Lake OSS? Wishing myself luck and hoping for "You bet! It's coming up in 1.1"

Tathagata Das

unread,
Jul 6, 2021, 9:46:59 AM7/6/21
to Jacek Laskowski, Delta Lake Users and Developers
Hey Jacek,

TIMESTAMP AS OF is not supported in Delta OSS because this requires Apache Spark's SQL parser to support that syntax. And it's hard to make Spark support it because
1. Its non-SQL-standard syntax
2. It's hard to patch/extended the parser with session extensions to support this like we support VACUUM and DESCRIBE DETAILS because this syntax inside SELECT grammar rule is hard to selectively override without significantly large overrides.

TD 

--
You received this message because you are subscribed to the Google Groups "Delta Lake Users and Developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to delta-users...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/delta-users/CAB_G8ZvCK1dJr%2BSBSPa5oJsiRgshX4tvCCWr5BpeaVoTW_Dc%2BQ%40mail.gmail.com.

Jacek Laskowski

unread,
Jul 6, 2021, 10:40:09 AM7/6/21
to Tathagata Das, Delta Lake Users and Developers
Hi TD,

Makes sense (and honestly I knew all this already), but since this is part of Delta Lake OSS' docs I thought I'd bring it up. It's confusing to find this kind of (mis)information in the official docs of Delta Lake OSS at docs.delta.io. Why is this there? What do you think about removing it from the docs?

Tathagata Das

unread,
Jul 6, 2021, 11:01:25 AM7/6/21
to Jacek Laskowski, Delta Lake Users and Developers
That is definitely a mistake if the SQL syntax shows up in the OSS docs. Can you point to me exactly where it shows up?

TD

Jacek Laskowski

unread,
Jul 6, 2021, 11:04:14 AM7/6/21
to Tathagata Das, Delta Lake Users and Developers
Hi TD,
To view this discussion on the web visit https://groups.google.com/d/msgid/delta-users/CA%2BAHuKmwEotTB0iMirLmW8gMj%3DZHbtVdrTeTChUeG14qWGwFwA%40mail.gmail.com.

Tathagata Das

unread,
Jul 6, 2021, 11:10:51 AM7/6/21
to Jacek Laskowski, Delta Lake Users and Developers
Aaah, thank you for catching this!
I will fix the docs and try to update the already published Delta 1.0 docs. 

TD

Jacek Laskowski

unread,
Jul 6, 2021, 11:14:10 AM7/6/21
to Tathagata Das, Delta Lake Users and Developers
Merci beaucoup mon ami !

(wished you fixed the code instead to have support but turns out Spark SQL is not flexible enough...ouch).

Tathagata Das

unread,
Jul 6, 2021, 11:32:14 AM7/6/21
to Jacek Laskowski, Delta Lake Users and Developers
Any grammar is hard to do invasive changes on. :(
Reply all
Reply to author
Forward
0 new messages