Bigquery only outputs duplicate rows

[jensroy]

Hi!

I have a workflow where I grab data from Google sheets, then I push those data into a BigQuery database. This is the full workflow. Grabbing the data takes 5 seconds. Writing the data takes 3 minutes. There is only 20 rows of data. In addition, every row written becomes a duplicate of the 1st row.

The output looks like this:

It should look like this:

Any idea where I can start to troubleshoot this?

Simba ODBC driver (2.1.6.1006)

Alteryx 2018.1

[BenMoss]

Can you post a screenshot of the data before it is streaming into the output data tool.

Ben

[jensroy]

Good morning! The second screenshot is taken from the source, a google sheet.

[jensroy]

And here is another interesting find. I checked the BQ log and found that it starts many seperate queries to insert data. I am not running a batch macro.

[jensroy]

This is the SQL that Alteryx pushes:

Insert into `SPRAY_BOOKING`.`TEST1`(`TIME`,`NATIONAL`,`SALES_TYPE`,`CUSTOMER`,`PLACEMENT`,`EMAIL`,`SITE`,`WEEK_BOOKED`,`DAYS_BOOKED`,`BOOKING_TYPE`,`MATERIAL`,`RESPONSILBE1`,`RESPONSIBLE2`,`YEAR`) Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)

[BenMoss · Accepted solution]

@jensroy I would consider raising this more formally with support@alteryx.com as it is probably something that most members of the community cannot replicate without creating a bigquery account and so on.

[HJackson]

Hi @jensroy, where you able to find a solution to this?  I have found that the new write to bigquery tool is also an append/insert only, and does not delete existing records.  I'd love to hear whatever work around you came up with other than writing a view in BQ to only grab the most recently updated record (as this will be more costly, over time).

Thank you!

[jensroy]

Hello. In the end i was not able to advance with this. The duplicate rows seems to be a result of the SIMBA driver not really supporting write to BigQuery. The only other solution i found in the community that could have worked was to use the download tool to post a CSV file into bigquery. But in the end I solved this project by using Azure instead of BigQuery. Good luck though, and please write back if you solve it. It would be good learning