Learn Bigquery
Introduction
Architecture
Architecture Compute Storage
Unique Features
Enncryption Column Level Security Time Travel Caching Compression
Database Management
Create Table Create View Create Procedure
Best Practices
Execution plans
Interview Questions
Top Questions
26 Jan 21 · npack · #Bigquery ·   Bookmark  

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;