Alteryx to BigQuery Upload - Date Fields Converting to Strings

[alvinmok]

I was trying to do BQ upload from Alteryx and for some reasons the field type would become “string” for “date” fields after it’s uploaded. The dates in Alteryx flow are in “Date” type and the date format is in yyyy-mm-dd.

I ran some SQL to convert date from string to date in BQ, and tried both “Overwrite Table” and “Delete Data & Append” (thought append would not change the field type) as my output options, but they both will overwrite the BQ table and change date to string fields. I also made sure the column orders are the same, but it doesn’t help. See below my output config.

After I ran SQL in BQ -

After running the flow with output config shown above..

Any way we can solve this issue, so I don’t need to run SQL in BQ every time I update the table? Thanks!!

[franciscoruizrise]

I have the same issue while trying a BULK upload data into BigQuery, DATE fields are converted into STRING and I haven´t been able to solve it.

[apathetichell]

can you capture your odbc driver logs (toggle on logging in odbc 64) and look for any sensitive data. If there is no sensitive data - upload them. This could be at the APi level, the driver level or the implementation level. 

Does datetime work the same?

[franciscoruizrise]

Hello,

This issue was resolved in the latest PATCH:

Designer 2023.2 Release Notes (alteryx.com)

TDCB-4948 GCSE-1521

BigQuery Bulk Loader loads date fields as strings.

2023.2.1.133

Fixed

But DATETIME fields are still being written in BigQuery as TIMESTAMP.

Regards.

[JMB001]

We use an Event with a batch script that uses Google CLI and creates a temp table with the correct data types, then changes the name to correct name. A bit cumbersome to manage, since any schema changes have to be reflected in the batch script. @STP 

[jessefu]

In Alteryx 2023.1, you can still use the Google BigQuery Connector that would bulk load and preserve data types.