Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Google BigQuery Output Tool

FabianS_dup_107
7 - Meteor

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? 

7 REPLIES 7
DanM
Alteryx Community Team
Alteryx Community Team

@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
7 - Meteor

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

@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
13 - Pulsar

@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
Alteryx Community Team
Alteryx Community Team

@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
7 - Meteor
cmcclellan
13 - Pulsar

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

Labels