Alteryx Cloud Designer BigQueryUnexpectedException when writing to BigQuery table

[rholowczak]

Folks:
I am working in Alteryx Cloud Designer. I have a relatively simple workflow that reads a CSV file, selects a few fields, sorts them, removes duplicates, adds a surrogate key (row number) and then attempts to load this into a table in Google BigQuery.

When I run the workflow, everything seems to go well until the last step when I receive the following error:

BigQueryUnexpectedException: {"cause":{"cause":null,"message":"URI: tfs://my-workspace-name/113997/.trifacta/c730a878-bad6-4d22-a931-5519769e3561/2410144/.publish/table_c730a878-bad6-4d22-a931-5519769e3561.csv/data.csv is not a GCS URI"},"message":"Error while publishing to Bigquery: URI: tfs://my-workspace-name/113997/.trifacta/c730a878-bad6-4d22-a931-5519769e3561/2410144/.publish/table_c730a878-bad6-4d22-a931-5519769e3561.csv/data.csv is not a GCS URI"}

(I replaced my actual workspace name with 'my-workspace-name' above)

It is my understanding that the Google BigQuery driver first has to write the data to a CSV file on Google Cloud Storage and then read the CSV to load into BigQuery.

I have gone through different Alteryx documentation and none of what I find seems to coincide with the Alteryx Cloud Designer. For example, I see references to setting the temporary storage bucket name, but that seems to only be found in the desktop version of Designer.

Please let me know if you have any suggestions.

Thanks

[Dean4U]

Hi @rholowczak,

Thanks for reaching out to the community. Could you please check out the KB doc and let us know if it helps? Even though most of the documentation talks about desktop Designer, the same requirements apply to Cloud Designer workflows:

https://knowledge.alteryx.com/index/s/article/How-to-set-up-Google-Big-Query-Bulk-Write-in-Designer-...

The error might be because the BigQuery bulk write process expects a Google Cloud Storage (GCS) bucket as a staging area for the data being written. Please try to ensure that a valid GCS Bucket is specified and that the bucket exists and is in the correct region. You will also need to verify that your service account has the required GCP permissions.

Below are some similar articles that can guide you further:

Google BigQuery Connections

Failed to complete write to Google BigQuery

Hope this helps. 🙂

Take care.

[rholowczak]

Hi

Thanks for taking the time to look into this issue.

All of this work is taking place in Alteryx One using Designer Cloud. 

Ultimately, I was able to solve this by switching the entire workspace over to using Google Cloud Storage instead of the Alteryx Data Storage.    Once I made that change, I was able to use the Settings > Storage options to specify a temporary bucket.  Pro-tip: If/When you do this, do not include the gs:// part of the resource identifier.  Just give the name of the bucket on GCS to store temporary files.

With that in place, now when the output of a workflow goes to BigQuery, Alteryx first writes the data to a temporary CSV file on GCS. Then that file is loaded into BigQuery.

So this seems to be solved.

Thanks again

Rich H.

[Dean4U]

Thanks for letting us know, Rich! 🙂