Alteryx to BigQuery Upload - Date Fields Converting to Strings

Question

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!!

jessefu · Answer

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

JMB001 · Answer

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

franciscoruizrise · Answer

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.