BigQuery: Create Partitioned Table as SELECT

BigQuery supports creating table from a select statement. In addition to that, partitioned table can also be created using CREATE TABLE ... SELECT statement.

Code snippet

CREATE TABLE
  `myproject.mydataset.mynewtable` (TXN_ID INT64, TXN_DATE DATE, TXN_AMOUNT, TXN_TYPE)
PARTITION BY
  TXN_DATE
AS SELECT TXN_ID, TXN_DATE, TXN_AMOUNT,TXN_TYPE  FROM `myproect.mydataset.mytable`

The above statement creates a new table partitioned by TXN_DATEcolumn.

You can also use functions to truncate the partition date column.

CREATE TABLE
  `myproject.mydataset.mynewtable` (TXN_ID INT64, TXN_DATE DATE, TXN_AMOUNT, TXN_TYPE)
PARTITION BY
  DATE_TRUNC(TXN_DATE, MONTH)
AS SELECT TXN_ID, TXN_DATE, TXN_AMOUNT,TXN_TYPE  FROM `myproect.mydataset.mytable`

The above statement will create a table partitioned by the month start date of TXN_DATEcolumn.

Code snippet

In this article