Add date and timestamp support to to_json [databricks] #9600

andygrove · 2023-11-01T18:31:55Z

Closes #9515
Part of #9518

This PR adds support for date and timestamp in to_json.

Changes:

Allow date and timestamp in GpuOverrides
Check JSON options for date/timestamp formats and fall back to CPU if not defaults (there is a follow on issue [FEA][JSON] Support custom date and timestamp formats in to_json #9602 for supporting custom formats but I am not sure what the priority of that is)
Add quotes around date and timestamp values
Implement a new method for formatting timestamps for JSON
Update tests and docs

Signed-off-by: Andy Grove <[email protected]>

sql-plugin/src/main/scala/com/nvidia/spark/rapids/GpuCast.scala

andygrove · 2023-11-14T16:23:20Z

build

andygrove · 2023-11-14T18:40:40Z

Build failed with:

2023-11-14T17:22:13.3676904Z [2023-11-14T16:58:19.497Z] ^[[31m- convert large InternalRow iterator to cached batch single col *** FAILED ***^[[0m
2023-11-14T17:22:13.3678719Z [2023-11-14T16:58:19.497Z] ^[[31m  java.lang.RuntimeException: ai.rapids.cudf.CudfException: RMM failure at:/home/jenkins/agent/workspace/jenkins-spark-rapids-jni_nightly-pre_release-203-cuda11/thirdparty/cudf/cpp/build/_deps/rmm-src/include/rmm/mr/device/arena_memory_resource.hpp:238: allocation not found^[[0m

andygrove · 2023-11-14T22:51:31Z

build

andygrove · 2023-11-14T23:01:42Z

build

andygrove · 2023-11-14T23:37:39Z

build

andygrove · 2023-11-15T00:55:40Z

build

revans2 · 2023-11-15T14:28:18Z

integration_tests/src/main/python/json_test.py

@@ -628,7 +628,8 @@ def test_read_case_col_name(spark_tmp_path, v1_enabled_list, col_name):
    pytest.param(True, marks=pytest.mark.xfail(reason='https://github.com/NVIDIA/spark-rapids/issues/9517')),
    False
 ])
-def test_structs_to_json(spark_tmp_path, data_gen, ignore_null_fields, pretty):
+@pytest.mark.parametrize('timezone', ['UTC', 'Etc/UTC'])
+def test_structs_to_json(spark_tmp_path, data_gen, ignore_null_fields, pretty, timezone):


Can we add a fallback test for non-utc?

revans2 · 2023-11-15T14:30:07Z

integration_tests/src/main/python/json_test.py

-    pytest.param(date_gen, marks=pytest.mark.xfail(reason='https://github.com/NVIDIA/spark-rapids/issues/9515')),
-    pytest.param(timestamp_gen, marks=pytest.mark.xfail(reason='https://github.com/NVIDIA/spark-rapids/issues/9515')),
+    date_gen,
+    timestamp_gen,


Can we add some fallback tests for the options timestampFormat, dateFormat, and if "spark.sql.legacy.timeParserPolicy" is set to LEGACY? At least from the code all of these impact the format that is used to write the JSON output.

revans2 · 2023-11-15T14:35:53Z

sql-plugin/src/main/scala/com/nvidia/spark/rapids/GpuOverrides.scala

+          if (hasTimestamps) {
+            // check if the default format is being used
+            val defaultFormat = "yyyy-MM-dd'T'HH:mm:ss[.SSS][XXX]"
+            val timestampFormat = a.options.getOrElse("timestampFormat", defaultFormat)


I don't think this does 100% of what we want

https://github.com/apache/spark/blob/7120e6b88f2327ffb71c4bca14b10b15aeb26c32/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/json/JSONOptions.scala#L125-L130

include a check for the legacyTimeParserPolicy. Either we need to use JSONOptions directly to get the format that we should use, or we need to replicate the behavior exactly. I would prefer JSONOptions if possible, even through a trampoline, because it lets us know exactly what it is, even if it changes from one version of spark to another.

andygrove · 2023-11-20T23:35:11Z

build

andygrove · 2023-11-21T13:08:21Z

@revans2 I have addressed feedback now.

winningsix · 2023-11-21T14:00:00Z

sql-plugin/src/main/scala/org/apache/spark/sql/rapids/GpuStructsToJson.scala

+    rule: DataFromReplacementRule
+  ) extends UnaryExprMeta[StructsToJson](expr, conf, parent, rule) {
+
+  lazy val supportedTimezones = Seq("UTC", "Etc/UTC").map(ZoneId.of)


nit: We can move this into

spark-rapids/sql-plugin/src/main/scala/com/nvidia/spark/rapids/GpuOverrides.scala

Line 440 in 2667941

val UTC_TIMEZONE_ID = ZoneId.of("UTC").normalized()

winningsix · 2023-11-21T14:03:51Z

sql-plugin/src/main/scala/org/apache/spark/sql/rapids/GpuStructsToJson.scala

+  lazy val supportedTimezones = Seq("UTC", "Etc/UTC").map(ZoneId.of)
+
+  override def tagExprForGpu(): Unit = {
+    if (expr.options.get("pretty").exists(_.equalsIgnoreCase("true"))) {


Just FYI. #9719 touches a little bit of timezone check. given StructsToJson is also a TimeZoneAwareExpression.

andygrove · 2023-11-21T23:01:10Z

build

andygrove · 2023-11-28T01:47:31Z

build

andygrove · 2023-11-28T14:53:22Z

There is a failure after upmerging. I am looking into it.

andygrove · 2023-11-28T14:56:20Z

@revans2 Could you take another look when you get a chance

andygrove · 2023-11-28T15:14:45Z

build

revans2

We need to be careful that we coordinate this going in with other timezone work being done, but nothing I see here as being a blocker.

revans2 · 2023-11-28T22:31:59Z

sql-plugin/src/main/scala/com/nvidia/spark/rapids/GpuOverrides.scala

@@ -438,6 +438,7 @@ object GpuOverrides extends Logging {
    "the Unicode version used by cuDF and the JVM may differ, resulting in some " +
    "corner-case characters not changing case correctly."
  val UTC_TIMEZONE_ID = ZoneId.of("UTC").normalized()
+  val SUPPORTED_TIMEZONE_IDS = Seq("UTC", "Etc/UTC").map(ZoneId.of)


Is there a reason we need to have an explicit allow list for specific ZoneIds instead of normalizing them and comparing them to UTC_TIMESONE_ID?

Thanks. I wasn't familiar with ZoneId, but that makes sense. I have pushed that fix.

andygrove · 2023-11-29T15:40:51Z

build

Add date and timestamp support to to_json

0989865

andygrove self-assigned this Nov 1, 2023

fall back to CPU for non-default date and timestamp format options

d4541b5

andygrove mentioned this pull request Nov 1, 2023

[FEA][JSON] Support custom date and timestamp formats in to_json #9602

Open

add link to follow on issue for supporting custom formats

9e982b3

andygrove changed the title ~~[WIP] Add date and timestamp support to to_json~~ Add date and timestamp support to to_json Nov 1, 2023

update comment

e7a1c13

Signed-off-by: Andy Grove <[email protected]>

andygrove force-pushed the to_json_dates branch from aa0e1e3 to e7a1c13 Compare November 1, 2023 21:55

andygrove marked this pull request as ready for review November 1, 2023 21:55

andygrove added 3 commits November 1, 2023 15:56

remove blank line

541c6f7

add support for Etc/UTC timezone

09bdcaf

generate docs

a14c5bf

sameerz added the feature request New feature or request label Nov 2, 2023

upmerge

3ffa025

ttnghia reviewed Nov 8, 2023

View reviewed changes

sql-plugin/src/main/scala/com/nvidia/spark/rapids/GpuCast.scala Outdated Show resolved Hide resolved

andygrove added 2 commits November 14, 2023 08:57

Merge remote-tracking branch 'nvidia/branch-23.12' into to_json_dates

08e3101

Remove redundant cast

ca8c926

andygrove changed the title ~~Add date and timestamp support to to_json~~ Add date and timestamp support to to_json [databricks] Nov 14, 2023

andygrove added 5 commits November 14, 2023 17:00

upmerge

02db97d

fix merge conflict

40f1085

fix merge conflict

ead3fd9

fix merge conflict

b69f257

fix merge conflict

55ae3f1

revans2 reviewed Nov 15, 2023

View reviewed changes

andygrove added 2 commits November 20, 2023 10:57

Merge branch 'branch-23.12' into to_json_dates

fb6f8a9

Use parsed JSONOptions

f4bddca

andygrove marked this pull request as draft November 20, 2023 19:57

andygrove added 3 commits November 20, 2023 14:58

use timestampFormatInWrite not InRead and add fallback tests

cd8bc7a

shims

881b75f

more tests

2c3c4ec

andygrove marked this pull request as ready for review November 20, 2023 23:34

update test

9fb7166

winningsix reviewed Nov 21, 2023

View reviewed changes

andygrove added 2 commits November 21, 2023 09:44

move UTC timezone list to GpuOverrides

db9582b

upmerge

95ccce3

upmerge

635b3ef

fix merge conflict

c4440ed

revans2 previously approved these changes Nov 28, 2023

View reviewed changes

remove hard-coded list of timezones

13eb70c

andygrove dismissed revans2’s stale review via 13eb70c November 29, 2023 15:40

revans2 approved these changes Nov 29, 2023

View reviewed changes

andygrove merged commit 7efcb81 into NVIDIA:branch-23.12 Nov 29, 2023
37 checks passed

andygrove deleted the to_json_dates branch November 29, 2023 20:50

andygrove mentioned this pull request Nov 30, 2023

[FEA] Support temporal types in to_json #9515

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Add date and timestamp support to to_json [databricks] #9600

Add date and timestamp support to to_json [databricks] #9600

andygrove commented Nov 1, 2023 •

edited

Loading

andygrove commented Nov 14, 2023

andygrove commented Nov 14, 2023

andygrove commented Nov 14, 2023

andygrove commented Nov 14, 2023

andygrove commented Nov 14, 2023

andygrove commented Nov 15, 2023

revans2 Nov 15, 2023

revans2 Nov 15, 2023

revans2 Nov 15, 2023

andygrove commented Nov 20, 2023

andygrove commented Nov 21, 2023

winningsix Nov 21, 2023

winningsix Nov 21, 2023

andygrove commented Nov 21, 2023

andygrove commented Nov 28, 2023

andygrove commented Nov 28, 2023

andygrove commented Nov 28, 2023

andygrove commented Nov 28, 2023

revans2 left a comment

revans2 Nov 28, 2023

andygrove Nov 29, 2023

andygrove commented Nov 29, 2023

Add date and timestamp support to to_json [databricks] #9600

Add date and timestamp support to to_json [databricks] #9600

Conversation

andygrove commented Nov 1, 2023 • edited Loading

andygrove commented Nov 14, 2023

andygrove commented Nov 14, 2023

andygrove commented Nov 14, 2023

andygrove commented Nov 14, 2023

andygrove commented Nov 14, 2023

andygrove commented Nov 15, 2023

revans2 Nov 15, 2023

Choose a reason for hiding this comment

revans2 Nov 15, 2023

Choose a reason for hiding this comment

revans2 Nov 15, 2023

Choose a reason for hiding this comment

andygrove commented Nov 20, 2023

andygrove commented Nov 21, 2023

winningsix Nov 21, 2023

Choose a reason for hiding this comment

winningsix Nov 21, 2023

Choose a reason for hiding this comment

andygrove commented Nov 21, 2023

andygrove commented Nov 28, 2023

andygrove commented Nov 28, 2023

andygrove commented Nov 28, 2023

andygrove commented Nov 28, 2023

revans2 left a comment

Choose a reason for hiding this comment

revans2 Nov 28, 2023

Choose a reason for hiding this comment

andygrove Nov 29, 2023

Choose a reason for hiding this comment

andygrove commented Nov 29, 2023

andygrove commented Nov 1, 2023 •

edited

Loading