CREATE TABLE AS SELECT from another table in Bigquery (Copy DDL and Data)

To create a new table similar to another table copying both data and the structure,

CREATE TABLE mydataset.mynewtable
AS SELECT * FROM mydataset.myothertable;

To create a new partitioned table from a select query

CREATE TABLE mydataset.myclusteredtable
 (
   timestamp TIMESTAMP,
   customer_id STRING,
   transaction_amount NUMERIC
 )
 PARTITION BY DATE(timestamp)
 CLUSTER BY
   customer_id
 OPTIONS (
   partition_expiration_days=3,
   description="a table clustered by customer_id"
 )
 AS SELECT * FROM mydataset.myothertable

(or)

CREATE TABLE mydataset.myclusteredtable
 PARTITION BY DATE(timestamp)
 CLUSTER BY customer_id
 OPTIONS (
   partition_expiration_days=3,
   description="a table clustered by customer_id"
 )
 AS SELECT * FROM mydataset.myothertable

nVector

posted on 20 Sep 19