BigQuery: Split table based on a column

Question

Short question: I would like to split a BQ table into multiple small tables, based on the distinct values of a column. So, if column country has 10 distinct values, it should split the table into 10 individual tables, with each having respective country data. Best, if done from within a BQ query (using INSERT, MERGE, etc.).

What I am doing right now is importing data to gstorage -> local storage -> doing splits locally and then pushing into tables (which is kind of a very time consuming process).

Thanks.

Answer 1

If the data has the same schema, just leave it in one table and use the clustering feature: https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#creating_a_clustered_table

#standardSQL
 CREATE TABLE mydataset.myclusteredtable
 PARTITION BY dateCol
 CLUSTER BY country
 OPTIONS (
   description="a table clustered by country"
 ) AS (
   SELECT ....
 )

https://cloud.google.com/bigquery/docs/clustered-tables

The feature is in beta though.