Big Query CTAS partitioned by Date or Int example

It took me a while to realize but…

you can do the following create as select partitioned by date in BigQuery:

CREATE TABLE
`MyProject.MyDataset.t5`
PARTITION BY
date1
AS SELECT 1 AS customer_id, DATE "2019-10-01" AS date1

Hope this helps simply your AIRFLOW dags.

You also use partitioned by Int example (relativity new feature with minimal support):

in order to create an integer range partitioned table, in addition to the field by which the table will be partitioned, an array containing the ranges specification is required.[1]

CREATE TABLE
`MyProject.MyDataset.t4`
PARTITION BY
RANGE_BUCKET(customer_id, GENERATE_ARRAY(0, 100, 10))
AS SELECT 1 AS customer_id, DATE "2019-10-01" AS date1

This query creates a table named “newtable” with an integer range partition on the “customer_id” column with start 0, end 100, and interval 10; therefore, you need to include an array using the “GENERATE_ARRAY” statement to set the ranges for your table.

Additionally, please consider that this feature is in a Beta stage and might change or have limited support. Please note that currently, the table details tab doesn’t list the partitioned settings information for integer range partitioned tables. In order to verify if a table is partitioned on an integer column, you will need to examine the table schema, for example by running the “bq show –format=prettyjson my_dataset.my_table” command.

[1] https://cloud.google.com/bigquery/docs/creating-integer-range-partitions#creating_an_empty_partitioned_table_with_a_schema_definition

——————————————————————————————————————————

I put a lot of thoughts into these blogs, so I could share the information in a clear and useful way. If you have any comments, thoughts, questions, or you need someone to consult with, feel free to contact me:

https://www.linkedin.com/in/omid-vahdaty/