BigQuery Storage Write API: ISO 8601 timestamp not supported in JsonStreamWriter

[geirsagberg]

Environment details

1. Specify the API at the beginning of the title. For example, "BigQuery: ...").
   General, Core, and Other are also allowed as types
2. OS type and version: macOS Monterey 12.1.1
3. Java version: 11.0.11
4. version(s): implementation("com.google.cloud", "google-cloud-bigquerystorage", "2.11.0")

Steps to reproduce

1. Create a JsonStreamWriter
2. Write a timestamp field in ISO 8601 UTC format, e.g. 2022-03-16T13:00:00Z
3. See that the error "Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]" is thrown

Code example

        createJsonStreamWriter().use { writer ->
            val json = JSONArray("""[{"id": "1", "timestamp": "2010-01-01T00:00:00Z"}]""")
            val response = writer.append(json).get()

            response.error.code shouldBe 0
        }

Stack trace

Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]
java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]
	at java.sql/java.sql.Timestamp.valueOf(Timestamp.java:196)
	at com.google.cloud.bigquery.storage.v1.JsonToProtoMessage.fillField(JsonToProtoMessage.java:300)
	at com.google.cloud.bigquery.storage.v1.JsonToProtoMessage.convertJsonToProtoMessageImpl(JsonToProtoMessage.java:177)
	at com.google.cloud.bigquery.storage.v1.JsonToProtoMessage.convertJsonToProtoMessage(JsonToProtoMessage.java:116)
	at com.google.cloud.bigquery.storage.v1.JsonStreamWriter.append(JsonStreamWriter.java:147)
	at com.google.cloud.bigquery.storage.v1.JsonStreamWriter.append(JsonStreamWriter.java:106)

API reference

According to https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#timestamp_type ISO8601 string should be supported.

Any additional information below

Would also be helpful to be able to encode timestamp as ISO8601 string in protobuf.

[dark0dave]

@geirsagberg my issue is very similar even if the incorrect format is use it will still parse it into gibberish,

[stephaniewang526]

@dark0dave upon some more testing, looks like the formats commented out below don't work:

Error:

Could you take a look at adding more support?

[dark0dave]

@stephaniewang526 yes will fix.

[dark0dave]

@stephaniewang526 this is not ISO compilant:

2018/08/19 12:11

This is the iso standard below:
Date 2022-04-05
Date and time in UTC 2022-04-05T09:06:11+00:00
2022-04-05T09:06:11Z
20220405T090611Z

https://en.wikipedia.org/wiki/ISO_8601

Big Query's own format, for timestamps are as follows:

YYYY-[M]M-[D]D[( |T)[H]H:[M]M:[S]S[.F]][time zone]

Taken from the Big Query docs: https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#timestamp_type

Which does not support this, format.

I have tried to follow Big Query does best I can in parsing, but this format I do not feel the need to support as its not ISO.

[dark0dave]

Additionally, I don't think I want to allow for doubles or floats to be parsed as they are not exact at all. Doesn't really seem like a good TIMESTAMP value, which are supposed to point to exact moments in time.

[stephaniewang526]

The doc seems to say these are supposed to be supported TIMESTAMP formats.

[dark0dave]

oh wow. Interesting.

[dark0dave]

Can you provide a link to that please?

[stephaniewang526]

sure - https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-json#data_types

[dark0dave]

thanks @stephaniewang526, let me update my pr.

[dark0dave]

@stephaniewang526 all done, all formats you have described have been added as tests