BigQuery - Transfers automation from Google Cloud Storage - Overwrite table

Question

Here's the case:

• Our client daily uploads CSVs (overwritten) to a bucket in Google Cloud Storage (each table in a different file).
• We use BigQuery as DataSource in DataStudio
• We want to automatically transfer the CSVs to BigQuery.

The thing is, even though we've:

1. Declared the tables in BigQuery with "Overwrite table" write preference option
2. Configured the daily Transfers vía UI (BigQuery > Transfers) to automatically upload the CSVs from Google Cloud one hour after the files are uploaded to Google Cloud, as stated by the limitations.

The automated transfer/load is by default in "WRITE_APPEND", thus the tables are appended instead of overwritten in BigQuery.

Hence the question: How/where can we change the

configuration.load.writeDisposition = WRITE_TRUNCATE

as stated here in order to overwrite the tables when the CSVs are automatically loaded?

I think that's what we're missing.

Cheers.

Answer 1

None of the above worked for us, so I'm posting this in case anyone has the same issue.

We scheduled a query to erase the table content just before the automatic importation process starts:

DELETE FROM project.tableName WHERE true

And then, new data will be imported to a void table, therefore default "WRITE_APPEND" doesn't affect us.

Answer 2

1) One way to do this is to use DDL to CREATE and REPLACE your table before running the query which imports the data.

This is an example of how to create a table

#standardSQL
 CREATE TABLE mydataset.top_words
 OPTIONS(
   description="Top ten words per Shakespeare corpus"
 ) AS
 SELECT
   corpus,
   ARRAY_AGG(STRUCT(word, word_count) ORDER BY word_count DESC LIMIT 10) AS top_words
 FROM bigquery-public-data.samples.shakespeare
 GROUP BY corpus;

Now that it's created you can import your data.

2) Another way is to use BigQuery schedule Queries

3) If you write Python you can find an even better solution here