BigQuery: Create Partitioned Table as SELECT

visibility 1,082 access_time 2 years ago languageEnglish

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_DATE column.

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_DATE column.

info Last modified by Raymond 2 years ago copyright This page is subject to Site terms.

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

timeline Stats
Page index 2.94
More from Kontext
BigQuery SQL - Retrieve DISTINCT Values
visibility 332
thumb_up 0
access_time 2 years ago
Python: Read Data from BigQuery
visibility 395
thumb_up 0
access_time 2 years ago
Get List of Tables/Views and Schema in BigQuery
visibility 3,969
thumb_up 1
access_time 2 years ago