access_time 25 days ago languageEnglish
more_vert

BigQuery: Create Partitioned Table as SELECT

visibility 11 comment 0

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 25 days ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Follow Kontext

Get our latest updates on LinkedIn.

Want to contribute on Kontext to help others?

Learn more

More from Kontext

Load JSON File into BigQuery
visibility 200
thumb_up 0
access_time 3 months ago
visibility 23
thumb_up 0
access_time 4 months ago
Differences between BigQuery and Cloud BigTable
visibility 10
thumb_up 0
access_time 25 days ago