languageEnglish

BigQuery: Create Partitioned Table as SELECT

visibility 57 comment 0 access_time 5 months ago

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 5 months ago copyright This page is subject to Site terms.

Subscribe newsletter

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

More from Kontext

visibility 2199
thumb_up 0
access_time 7 months ago