Raymond Raymond / Code Snippets & Tips

BigQuery: Create Partitioned Table as SELECT

event 2021-05-17 visibility 6,314 comment 0 insights toc
more_vert
insights Stats
toc Table of contents

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.

More from Kontext
comment Comments
No comments yet.

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts