DATETIME is read as String and not able to write it in indirect mode

[lbg-manasa-padimalla]

DATETIME is the BQ column datatype
Connector reading as string
keeping it as bytearray in temp location as we are using indirect mode
and failing when writing to BQ

[davidrabinowitz]

@lbg-manasa-padimalla Which spark version do you use?

[lbg-manasa-padimalla]

3.5.4

[lbg-manasa-padimalla]

#1454 -any ETA on this PR merge and new tag availability.

[yalimu-g]

Hi @lbg-manasa-padimalla,
For spark version 3.4 or above, Datetime is converted to in TimestampNTZTypeConverter not string. The PR is not actually fixing any issues. Can you elaborate the error you have?

[lbg-manasa-padimalla]

error message: Parquet column 'b_posted_date' has type BYTE_ARRAY which does not match the target cpp_type INT64.
b_posted_date is a 'DATETIME' in BQ
write mode - indirect

[yalimu-g]

Hi @lbg-manasa-padimalla , can you also confirm the connector version you are using?
Alternatively, you can try the following options:

1. upgrade your connector to spark-3.5-bigquery if not.
2. cast the datetime column to timestamp: col("b_posted_date").cast("timestamp_ntz")
3. switch to AVRO format and see if the error persists? .option("intermediateFormat", "avro")

[lbg-manasa-padimalla]

Connector version used - spark-bigquery-with-dependencies_2.12-0.42.3.jar

[davidrabinowitz]

Can you please switch to the better adapted spark-3.5-bigquery-0.43.1.jar and try again?

[lbg-manasa-padimalla]

Hi @davidrabinowitz , I found the issue, with the default shipped connector with dataproc 2.2 has the timestampntzconvertor as serviceloader but when i opened this spark-bigquery-with-dependencies_2.12-0.42.3.jar in META-INF there is no timestampntzconverter and hence typeconverters are empty and this issue is happening.

why this was not shipped with spark-bigquery-with-dependencies jar ?

dataproc 2.2 default shipped with spark-3.4-bigquery-0.34.1.jar ??

[davidrabinowitz]

Yes. Please launch the cluster with the following flag: --metadata SPARK_BQ_CONNECTOR_URL=gs://spark-lib/bigquery/spark-3.5-bigquery-0.43.1.jar