Create Table Statement in Google BigQuery with Examples

Create Table Example:

CREATE TABLE mydataset.Employee
(
Id INT64, 
Name STRING,
DOJ DATE,
Branch INT64,
SALARY NUMERIC
)
PARTITION BY DOJ
CLUSTER BY Branch, Id

BigQuery Create Table with partition expiration:

Partitions older than 7 days will be auto deleted

CREATE TABLE mydataset.Employee
(
Id INT64, 
Name STRING,
DOJ DATE,
Branch INT64
)
PARTITION BY DOJ
CLUSTER BY Branch, Id
OPTIONS(partition_expiration_days=7);

BigQuery Create Table with table expiration:

Entire table will be auto dropped post the expiration time

CREATE TABLE mydataset.Employee
(
Id INT64, 
Name STRING,
DOJ DATE,
Branch INT64
)
PARTITION BY DOJ
CLUSTER BY Branch, Id
OPTIONS(expiration_timestamp=TIMESTAMP "2021-01-31 00:00:00");

Create table from a query

CREATE TABLE mydataset.Employee
as SELECT * FROM mydataset.Emp;

(or)

CREATE TABLE mydataset.Employee
PARTITION BY DOJ
CLUSTER BY Branch, Id
OPTIONS(expiration_timestamp=TIMESTAMP "2021-01-31 00:00:00");
as SELECT * FROM mydataset.Emp;