CODEWITHSUNDEEP
google-bigquery
user3424848
user3424848

Reputation: 1

Is it possible to write a single BQ Query that reads from external table and writes to multiple tables?

I need your expert advice on the below query, your help is greatly appreciated.

Query: I have an External Table (in GCS), I have a requirement to read this table every 5 mins(300GB) and run multiple filters/transformations/aggregations on that data and insert the results into 8 different tables.

Example:

INSERT INTO ds.Native_Cube1 (col1,col2, sumagg) SELECT col1,col2,sum(col25) as sumagg FROM ds.External_Table Where CAST(SUBSTR(_FILE_NAME,43,12) AS INT64) > 123456 GROUP BY sumagg

INSERT INTO ds.Native_Cube2 (col1,col2, col3, meancol5) SELECT col1,col2,col3,mean(col5) as meancol5 FROM ds.External_Table Where CAST(SUBSTR(_FILE_NAME,43,12) AS INT64) > 123456 AND col3=http GROUP BY meancol5

...8 such queries.

With this approach I end up reading the input data multiple times and pay for it. I want to read the input data only once and populate these native_cubeN tables appropriately.

So the question is, Is it possible to avoid these extra reads and cost? If Yes, please suggest how can achieve.

Thank you for listening to me.

Upvotes: 0

Views: 84

Answers (1)

Hua Zhang
Hua Zhang

Reputation: 1551

A query can only update one table, so maybe you can try to reduce the read cost for each query instead. It seems all your queries have a filter on CAST(SUBSTR(_FILE_NAME,43,12) AS INT64). A possible way to reduce cost is to import the external table into a native table, partitioned on a column with value CAST(SUBSTR(_FILE_NAME,43,12) AS INT64). Then queries would read from this native table, with filters on the partitioning column. The queries would only read the matching partitions, instead of the whole table.

Upvotes: 0

Related Questions