topic Re: Wrong date type in Google Bigquery in Talend Studio

Wrong date type in Google Bigquery

ffanali0804 — Fri, 15 Nov 2024 22:28:08 GMT

hello,

I created a Talend job that do these actions:

call a SQL Server SP and fill a table
read from a SQL Server Table
write a Parquet file
upload the file in Google Storage
exec a sql statement in a tBigQuerySQLRow that load data from Parquet file to a BiqQuery table.

The table has some DATE data-type fields like these "yyyy-MM-dd" and if I check the schema of the parquet file, I see that the fields are of type Date (see screen attached). But when I check the parquet file the data type are transformed into timestamps and the bigquery table has timestamp fields too.

My question: is there a solution to write the field in Date rather than Timestamp?

Thanks a lot.

Re: Wrong date type in Google Bigquery

Anonymous — Wed, 12 Oct 2022 10:25:04 GMT

Your database is probably holding the dates with their timestamps, but you are viewing them without their timestamps. People often confuse what their query analyser application shows with what the DB is holding. When you create your Parquet file, you will need to format the dates appropriately so that the timestamps are not included. Can you show the job that creates your Parquet file?

Re: Wrong date type in Google Bigquery

ffanali0804 — Wed, 12 Oct 2022 10:33:17 GMT

hi @Richard Hall

I attached the imagine up 🙂

Re: Wrong date type in Google Bigquery

Anonymous — Wed, 12 Oct 2022 10:59:22 GMT

What does your Parquet schema look like?

Re: Wrong date type in Google Bigquery

ffanali0804 — Wed, 12 Oct 2022 11:54:47 GMT

is the same as that of the input table :

Re: Wrong date type in Google Bigquery

Anonymous — Wed, 12 Oct 2022 17:08:34 GMT

I have looked into this and I see the issue. The only type of "date" field that is used in Talend is the Java Date class. This always contains time. It holds everything. The formatting of Date columns simply deal with the human readable presentation of that data. The column itself does not lose any data. As such, in Parquet it will be creating a Timestamp field.

There is a feature request that I have found for this. But it is not ready for implementation yet.

Re: Wrong date type in Google Bigquery

ffanali0804 — Wed, 12 Oct 2022 17:44:24 GMT

hi @Richard Hall

it is really a pity that there is no solution. The only one I can think of is to copy the data in bigquery to a new table by making the appropriate casts.

Thank you very much for your support. 💪