Google BigQuery Output Tool

[FabianS_dup_107]

Hey Community!

I am testing the new Google BigQuery Tools and it seems that the output tool can only write new lines to a table in BigQuery, and not delete or alter rows that already exist. Does anybody know whether I am overlooking something or if this is a feature that will be added later? 

[DanM · Accepted solution]

@FabianS_dup_107,

You are correct that the tool at this point can only write new lines. Here is the Help documentation on the tool with all of the specifics.

https://help.alteryx.com/current/GoogleBigQueryOutput.htm

Outputting data from your designer workflow to Google BigQuery streams new rows to the table in BigQuery. Data will be held in a temporary streaming buffer for up to 90 minutes while processes in Google BigQuery convert the row-based data to columnar-based storage. In most cases, the data in the streaming buffer is still available for querying within seconds. However, you will not be able to see the inserted data in the Preview tab of the Google BigQuery console for the table until the extraction processes are complete. Learn more about the streaming buffer in this Google Blog Post. More information about the availability of streamed data can be found on the Google BigQuery Documentation site.

[HJackson]

Does anyone have any nifty workarounds for deleting data in BQ then inserting?  The reason I ask is that 1) I'm super stoked about the tool.  2) I'm super NOT stoked that it is append only. 3) managing the replication of records in BQ via views will be more costly overtime.  

[FabianS_dup_107]

@DanM Thanks !

So the answer is no... too bad, it would have been nice to be able to control tables (deleting, implement versioning etc.) and als create schema's. Now I think the only option is to write scripts in BigQuery.

@HJackson I assume you could write scripts in BigQuery that align with your workflows (if scheduled), but this is far from ideal.

[cmcclellan]

@DanM et al, I've spent the last 2 weeks using Google BigQuery and have spent some time in the last few days writing a macro that we can use so we don't have to use the Google BigQuery Output tool - why ? because it uses streaming and therefore locks the table for all other DML (update & delete commands to be precise) for "a while".  I've read different places in the Google documentation where "a while" is referred to as about 90 mins and another place where it says "a very long time".

My macro uses the BQ command line utility to accomplish what @FabianS_dup_107  was asking - to be able to truncate the table.  We don't have a need to alter records, but that's not a simple thing to do in BigQuery either :( 

What's the chance of changing the BigQuery macro to NOT stream and be able to choose replace/append and other options as well?

[DanM]

@cmcclellan ,

I can't speak to the "chances" of changes to any of the tools, but can suggest that if you would like to see more functionality added to create a post or see if anyone has had the same idea in the Designer Ideas. Our teams look at these posts for these types of enhancements. 

Regards,

DanM

[HJackson]

The idea is already here: https://community.alteryx.com/t5/Alteryx-Designer-Ideas/Update-Revamp-Google-BigQuery-Output-Tool-Al...

Please upvote!

[cmcclellan]

Yes, I've already starred that idea,  I think that's the correct way of upvoting here :)