Data definition language (DDL) statements in Google Standard SQL
Data definition language (DDL) statements let you create and modify BigQuery resources using Google Standard SQL query syntax. You can use DDL commands to create, alter, and delete resources, such as tables, table clones, table snapshots, views, user-defined functions (UDFs), and row-level access policies.
Required permissions
To create a job that runs a DDL statement, you must have the
bigquery.jobs.create
permission for the project where you are running the job.
Each DDL statement also requires specific permissions on the affected resources,
which are documented under each statement.
IAM roles
The predefined IAM roles bigquery.user
,
bigquery.jobUser
, and bigquery.admin
include the required
bigquery.jobs.create
permission.
For more information about IAM roles in BigQuery, see Predefined roles and permissions or the IAM permissions reference.
Running DDL statements
You can run DDL statements by using the Google Cloud console, by using the
bq
command-line tool, by calling the
jobs.query
REST API, or
programmatically using the
BigQuery API client libraries.
Console
Go to the BigQuery page in the Google Cloud console.
Click Compose new query.
Enter the DDL statement into the Query editor text area. For example:
CREATE TABLE mydataset.newtable ( x INT64 )
Click Run.
bq
Enter the
bq query
command
and supply the DDL statement as the query parameter. Set the
use_legacy_sql
flag to false
.
bq query --use_legacy_sql=false \ 'CREATE TABLE mydataset.newtable ( x INT64 )'
API
Call the jobs.query
method
and supply the DDL statement in the request body's query
property.
DDL functionality extends the information returned by a
Jobs resource.
statistics.query.statementType
includes the following additional values for DDL
support:
CREATE_TABLE
CREATE_TABLE_AS_SELECT
DROP_TABLE
CREATE_VIEW
DROP_VIEW
statistics.query
has 2 additional fields:
ddlOperationPerformed
: The DDL operation performed, possibly dependent on the existence of the DDL target. Current values include:CREATE
: The query created the DDL target.SKIP
: No-op. Examples —CREATE TABLE IF NOT EXISTS
was submitted, and the table exists. OrDROP TABLE IF EXISTS
was submitted, and the table does not exist.REPLACE
: The query replaced the DDL target. Example —CREATE OR REPLACE TABLE
was submitted, and the table already exists.DROP
: The query deleted the DDL target.
ddlTargetTable
: When you submit aCREATE TABLE/VIEW
statement or aDROP TABLE/VIEW
statement, the target table is returned as an object with 3 fields:- "projectId": string
- "datasetId": string
- "tableId": string
Java
Call the
BigQuery.create()
method to start a query job. Call the
Job.waitFor()
method to wait for the DDL query to finish.
Node.js
Python
Call the
Client.query()
method to start a query job. Call the
QueryJob.result()
method to wait for the DDL query to finish.
CREATE SCHEMA
statement
Creates a new dataset.
Syntax
CREATE SCHEMA [ IF NOT EXISTS ] [project_name.]dataset_name [DEFAULT COLLATE collate_specification] [OPTIONS(schema_option_list)]
Arguments
IF NOT EXISTS
: If any dataset exists with the same name, theCREATE
statement has no effect. Cannot appear withOR REPLACE
.DEFAULT COLLATE collate_specification
: When a new table is created in the dataset, the table inherits a default collation specification unless a collation specification is explicitly specified for a column.If you remove or change this collation specification later with the
ALTER SCHEMA
statement, this will not change existing collation specifications in this dataset. If you want to update an existing collation specification in a dataset, you must alter the column that contains the specification.project_name
: The name of the project where you are creating the dataset. Defaults to the project that runs this DDL statement.dataset_name
: The name of the dataset to create.schema_option_list
: A list of options for creating the dataset.
Details
The dataset is created in the location that you specify in the query settings. For more information, see Specifying your location.
For more information about creating a dataset, see Creating datasets. For information about quotas, see Dataset limits.
schema_option_list
The option list specifies options for the dataset. Specify the options in the
following format: NAME=VALUE, ...
The following options are supported:
NAME |
VALUE |
Details |
---|---|---|
default_kms_key_name |
STRING |
Specifies the default Cloud KMS key for encrypting table data in this dataset. You can override this value when you create a table. |
default_partition_expiration_days |
FLOAT64 |
Specifies the default expiration time, in days, for table partitions in this dataset. You can override this value when you create a table. |
default_table_expiration_days |
FLOAT64 |
Specifies the default expiration time, in days, for tables in this dataset. You can override this value when you create a table. |
description |
STRING |
The description of the dataset. |
friendly_name |
STRING |
A descriptive name for the dataset. |
is_case_insensitive |
BOOL |
In preview.
Available for
|
labels |
<ARRAY<STRUCT<STRING, STRING>>> |
An array of labels for the dataset, expressed as key-value pairs. |
location |
STRING |
The location in which to create the dataset. If you don't specify this option, the dataset is created in the location where the query runs. If you specify this option and also explicitly set the location for the query job, the two values must match; otherwise the query fails. |
max_time_travel_hours |
SMALLINT |
In preview. Specifies the duration in hours of the time travel window for the
dataset. The For more information on the time travel window, see Configuring the time travel window. |
storage_billing_model |
STRING |
In preview. Alters the
storage billing model
for the dataset to use physical bytes instead of logical bytes
when calculating storage changes. To make this change,
set the When you change a dataset's billing model, it takes 24 hours for the change to take effect. If you change a dataset's storage billing model to use physical bytes, you can't change it back to using logical bytes. |
Required permissions
This statement requires the following IAM permissions:
Permission | Resource |
---|---|
bigquery.datasets.create |
The project where you create the dataset. |
Examples
Creating a new schema
The following example creates a dataset with a default table expiration and a set of labels.
CREATE SCHEMA mydataset OPTIONS( location="us", default_table_expiration_days=3.75, labels=[("label1","value1"),("label2","value2")] )
Creating a case-insensitive dataset
The following example creates a case-insensitive dataset. Both the dataset name and table names inside the dataset are case-insensitive.
CREATE SCHEMA mydataset OPTIONS( is_case_insensitive=TRUE )
Creating a schema with collation support
The following example creates a dataset with a collation specification.
CREATE SCHEMA mydataset DEFAULT COLLATE 'und:ci'
CREATE TABLE
statement
Creates a new table.
Syntax
CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] TABLE [ IF NOT EXISTS ] table_name [( column[, ...] )] [DEFAULT COLLATE collate_specification] [PARTITION BY partition_expression] [CLUSTER BY clustering_column_list] [OPTIONS(table_option_list)] [AS query_statement]
Arguments
OR REPLACE
: Replaces any table with the same name if it exists. Cannot appear withIF NOT EXISTS
.TEMP | TEMPORARY
: Creates a temporary table.IF NOT EXISTS
: If any table exists with the same name, theCREATE
statement has no effect. Cannot appear withOR REPLACE
.table_name
: The name of the table to create. See Table path syntax. For temporary tables, do not include the project name or dataset name.column
: The table's schema information.collation_specification
: When a new column is added to the table without an explicit collation specification, the column inherits this collation specification forSTRING
types.If you remove or change this collation specification later with the
ALTER TABLE
statement, this will not change existing collation specifications in this table. If you want to update an existing collation specification in a table, you must alter the column that contains the specification.If the table is part of a dataset, the default collation specification for this table overrides the default collation specification for the dataset.
partition_expression
: An expression that determines how to partition the table.clustering_column_list
: A comma-separated list of column references that determine how to cluster the table. You cannot have collation on columns in this list.table_option_list
: A list of options for creating the table.query_statement
: The query from which the table should be created. For the query syntax, see SQL syntax reference. If a collation specification is used on this table, collation passes through this query statement.
Details
CREATE TABLE
statements must comply with the following rules:
- Only one
CREATE
statement is allowed. - Either the column list, the
as query_statement
clause, or both must be present. - When both the column list and the
as query_statement
clause are present, BigQuery ignores the names in theas query_statement
clause and matches the columns with the column list by position. - When the
as query_statement
clause is present and the column list is absent, BigQuery determines the column names and types from theas query_statement
clause. - Column names must be specified either through the column list,
the
as query_statement
clause or schema of the table in theLIKE
clause. - Duplicate column names are not allowed.
- When both the
LIKE
and theas query_statement
clause are present, the column list in the query statement must match the columns of the table referenced by theLIKE
clause.
Limitations:
- It is not possible to create an
ingestion-time partitioned table
from the result of a query. Instead, use a
CREATE TABLE
DDL statement to create the table, and then use anINSERT
DML statement to insert data into it. - It is not possible to use the
OR REPLACE
modifier to replace a table with a different kind of partitioning. Instead,DROP
the table, and then use aCREATE TABLE ... AS SELECT ...
statement to recreate it.
This statement supports the following variants, which have the same limitations:
CREATE TABLE LIKE
: Create a table with the same schema as an existing table.CREATE TABLE COPY
: Create a table by copying schema and data from an existing table.
column
(column_name column_schema[, ...])
contains the table's
schema information in a comma-separated list.
column := column_name column_schema column_schema := { simple_type | STRUCT<field_list> | ARRAY<array_element_schema> } [DEFAULT default_expression] [NOT NULL] [OPTIONS(column_option_list)] field_list := field_name column_schema [, ...] array_element_schema := { simple_type | STRUCT<field_list> } [NOT NULL] simple_type := { data_type | STRING COLLATE collate_specification }
column_name
is the name of the column. A column name:- Must contain only letters (a-z, A-Z), numbers (0-9), or underscores (_)
- Must start with a letter or underscore
- Can be up to 300 characters
column_schema
: Similar to a data type, but supports an optionalNOT NULL
constraint for types other thanARRAY
.column_schema
also supports options on top-level columns andSTRUCT
fields.column_schema
can be used only in the column definition list ofCREATE TABLE
statements. It cannot be used as a type in expressions.simple_type
: Any supported data type aside fromSTRUCT
andARRAY
.If
simple_type
is aSTRING
, it supports an additional clause for collation, which defines how a resultingSTRING
can be compared and sorted. The syntax looks like this:STRING COLLATE collate_specification
If you have
DEFAULT COLLATE collate_specification
assigned to the table, the collation specification for a column overrides the specification for the table.default_expression
: The default value assigned to the column.field_list
: Represents the fields in a struct.field_name
: The name of the struct field. Struct field names have the same restrictions as column names.NOT NULL
: When theNOT NULL
constraint is present for a column or field, the column or field is created withREQUIRED
mode. Conversely, when theNOT NULL
constraint is absent, the column or field is created withNULLABLE
mode.Columns and fields of
ARRAY
type do not support theNOT NULL
modifier. For example, acolumn_schema
ofARRAY<INT64> NOT NULL
is invalid, sinceARRAY
columns haveREPEATED
mode and can be empty but cannot beNULL
. An array element in a table can never beNULL
, regardless of whether theNOT NULL
constraint is specified. For example,ARRAY<INT64>
is equivalent toARRAY<INT64 NOT NULL>
.The
NOT NULL
attribute of a table'scolumn_schema
does not propagate through queries over the table. If tableT
contains a column declared asx INT64 NOT NULL
, for example,CREATE TABLE dataset.newtable AS SELECT x FROM T
creates a table nameddataset.newtable
in whichx
isNULLABLE
.
partition_expression
PARTITION BY
is an optional clause that controls
table partitioning. partition_expression
is an expression that determines how to partition the table. The partition
expression can contain the following values:
_PARTITIONDATE
. Partition by ingestion time with daily partitions. This syntax cannot be used with theAS query_statement
clause.DATE(_PARTITIONTIME)
. Equivalent to_PARTITIONDATE
. This syntax cannot be used with theAS query_statement
clause.<date_column>
. Partition by aDATE
column with daily partitions.DATE({ <timestamp_column> | <datetime_column> })
. Partition by aTIMESTAMP
orDATETIME
column with daily partitions.DATETIME_TRUNC(<datetime_column>, { DAY | HOUR | MONTH | YEAR })
. Partition by aDATETIME
column with the specified partitioning type.TIMESTAMP_TRUNC(<timestamp_column>, { DAY | HOUR | MONTH | YEAR })
. Partition by aTIMESTAMP
column with the specified partitioning type.TIMESTAMP_TRUNC(_PARTITIONTIME, { DAY | HOUR | MONTH | YEAR })
. Partition by ingestion time with the specified partitioning type. This syntax cannot be used with theAS query_statement
clause.DATE_TRUNC(<date_column>, { MONTH | YEAR })
. Partition by aDATE
column with the specified partitioning type.RANGE_BUCKET(<int64_column>, GENERATE_ARRAY(<start>, <end>[, <interval>]))
. Partition by an integer column with the specified range, where:start
is the start of range partitioning, inclusive.end
is the end of range partitioning, exclusive.interval
is the width of each range within the partition. Defaults to 1.
clustering_column_list
CLUSTER BY
is an optional clause that controls table clustering.
clustering_column_list
is a comma-separated list that determines how to
cluster the table. The clustering column list can contain a list of up to four
clustering columns.
table_option_list
The option list allows you to set table options such as a label and an expiration time. You can include multiple options using a comma-separated list.
Specify a table option list in the following format:
NAME=VALUE, ...
NAME
and VALUE
must be one of the following combinations:
NAME |
VALUE |
Details |
---|---|---|
expiration_timestamp |
TIMESTAMP |
Example: This property is equivalent to the expirationTime table resource property. |
partition_expiration_days |
|
Example: Sets the partition expiration in days. For more information, see Set the partition expiration. By default, partitions do not expire. This property is equivalent to the timePartitioning.expirationMs table resource property but uses days instead of milliseconds. One day is equivalent to 86400000 milliseconds, or 24 hours. This property can only be set if the table is partitioned. |
require_partition_filter |
|
Example: Specifies whether queries on this table must include a a predicate
filter that filters on the partitioning column. For more information,
see
Set partition filter requirements. The default value is
This property is equivalent to the timePartitioning.requirePartitionFilter table resource property. This property can only be set if the table is partitioned. |
kms_key_name |
|
Example: This property is equivalent to the encryptionConfiguration.kmsKeyName table resource property. See more details about Protecting data with Cloud KMS keys. |
friendly_name |
|
Example: This property is equivalent to the friendlyName table resource property. |
description |
|
Example: This property is equivalent to the description table resource property. |
labels |
|
Example: This property is equivalent to the labels table resource property. |
VALUE
is a constant expression containing only literals, query parameters,
and scalar functions.
The constant expression cannot contain:
- A reference to a table
- Subqueries or SQL statements such as
SELECT
,CREATE
, orUPDATE
- User-defined functions, aggregate functions, or analytic functions
- The following scalar functions:
ARRAY_TO_STRING
REPLACE
REGEXP_REPLACE
RAND
FORMAT
LPAD
RPAD
REPEAT
SESSION_USER
GENERATE_ARRAY
GENERATE_DATE_ARRAY
If VALUE
evaluates to NULL
, the corresponding option NAME
in the
CREATE TABLE
statement is ignored.
column_option_list
The column_option_list
in column_schema
lets you specify optional
column or field options. Column options have the same syntax and requirements as
table options but with a different list of NAME
s and VALUE
s:
NAME |
VALUE |
Details |
---|---|---|
description |
|
Example: This property is equivalent to the schema.fields[].description table resource property. |
rounding_mode |
|
In preview. Example: This specifies the rounding mode
that's used for values written to a parameterized
This property is equivalent to the
|
Required permissions
This statement requires the following IAM permissions:
Permission | Resource |
---|---|
bigquery.tables.create |
The dataset where you create the table. |
In addition, the OR REPLACE
clause requires bigquery.tables.update
and
bigquery.tables.updateData
permissions.
If the OPTIONS
clause includes any expiration options, then
bigquery.tables.delete
permission is also required.
Examples
Creating a new table
The following example creates a partitioned table named newtable
in
mydataset
:
CREATE TABLE mydataset.newtable ( x INT64 OPTIONS(description="An optional INTEGER field"), y STRUCT< a ARRAY<STRING> OPTIONS(description="A repeated STRING field"), b BOOL > ) PARTITION BY _PARTITIONDATE OPTIONS( expiration_timestamp=TIMESTAMP "2025-01-01 00:00:00 UTC", partition_expiration_days=1, description="a table that expires in 2025, with each partition living for 24 hours", labels=[("org_unit", "development")] )
If you haven't configured a default project, prepend a project ID to the dataset
name in the example SQL, and enclose the name in backticks if project_id
contains special characters:
`project_id.dataset.table`
. So, instead of
mydataset.newtable
, your table qualifier might be
`myproject.mydataset.newtable`
.
If the table name exists in the dataset, the following error is returned:
Already Exists: project_id:dataset.table
The table uses the following partition_expression
to partition the table:
PARTITION BY _PARTITIONDATE
. This expression partitions the table using
the date in the _PARTITIONDATE
pseudo column.
The table schema contains two columns:
- x: An integer, with description "An optional INTEGER field"
y: A STRUCT containing two columns:
- a: An array of strings, with description "A repeated STRING field"
- b: A boolean
The table option list specifies the:
- Table expiration time: January 1, 2025 at 00:00:00 UTC
- Partition expiration time: 1 day
- Description:
A table that expires in 2025
- Label:
org_unit = development
Creating a new table from an existing table
The following example creates a table named top_words
in mydataset
from a
query:
CREATE TABLE mydataset.top_words OPTIONS( description="Top ten words per Shakespeare corpus" ) AS SELECT corpus, ARRAY_AGG(STRUCT(word, word_count) ORDER BY word_count DESC LIMIT 10) AS top_words FROM bigquery-public-data.samples.shakespeare GROUP BY corpus;
If you haven't configured a default project, prepend a project ID to the dataset
name in the example SQL, and enclose the name in backticks if project_id
contains special characters:
`project_id.dataset.table`
. So, instead of
mydataset.top_words
, your table qualifier might be
`myproject.mydataset.top_words`
.
If the table name exists in the dataset, the following error is returned:
Already Exists: project_id:dataset.table
The table schema contains 2 columns:
- corpus: Name of a Shakespeare corpus
top_words: An
ARRAY
ofSTRUCT
s containing 2 fields:word
(aSTRING
) andword_count
(anINT64
with the word count)
The table option list specifies the:
- Description:
Top ten words per Shakespeare corpus
Creating a table only if the table doesn't exist
The following example creates a table named newtable
in mydataset
only if no
table named newtable
exists in mydataset
. If the table name exists in the
dataset, no error is returned, and no action is taken.
CREATE TABLE IF NOT EXISTS mydataset.newtable (x INT64, y STRUCT<a ARRAY<STRING>, b BOOL>) OPTIONS( expiration_timestamp=TIMESTAMP "2025-01-01 00:00:00 UTC", description="a table that expires in 2025", labels=[("org_unit", "development")] )
If you haven't configured a default project, prepend a project ID to the dataset
name in the example SQL, and enclose the name in backticks if project_id
contains special characters:
`project_id.dataset.table`
. So, instead of
mydataset.newtable
, your table qualifier might be
`myproject.mydataset.newtable`
.
The table schema contains 2 columns:
- x: An integer
y: A STRUCT containing a (an array of strings) and b (a boolean)
The table option list specifies the:
- Expiration time: January 1, 2025 at 00:00:00 UTC
- Description:
A table that expires in 2025
- Label:
org_unit = development
Creating or replacing a table
The following example creates a table named newtable
in mydataset
, and if
newtable
exists in mydataset
, it is overwritten with an empty table.
CREATE OR REPLACE TABLE mydataset.newtable (x INT64, y STRUCT<a ARRAY<STRING>, b BOOL>) OPTIONS( expiration_timestamp=TIMESTAMP "2025-01-01 00:00:00 UTC", description="a table that expires in 2025", labels=[("org_unit", "development")] )
If you haven't configured a default project, prepend a project ID to the dataset
name in the example SQL, and enclose the name in backticks if project_id
contains special characters:
`project_id.dataset.table`
. So, instead of
mydataset.newtable
, your table qualifier might be
`myproject.mydataset.newtable`
.
The table schema contains 2 columns:
- x: An integer
y: A STRUCT containing a (an array of strings) and b (a boolean)
The table option list specifies the:
- Expiration time: January 1, 2025 at 00:00:00 UTC
- Description:
A table that expires in 2025
- Label:
org_unit = development
Creating a table with REQUIRED
columns
The following example creates a table named newtable
in mydataset
. The NOT
NULL
modifier in the column definition list of a CREATE TABLE
statement
specifies that a column or field is created in REQUIRED
mode.
CREATE TABLE mydataset.newtable ( x INT64 NOT NULL, y STRUCT< a ARRAY<STRING>, b BOOL NOT NULL, c FLOAT64 > NOT NULL, z STRING )
If you haven't configured a default project, prepend a project ID to the dataset
name in the example SQL, and enclose the name in backticks if project_id
contains special characters:
`project_id.dataset.table`
. So, instead of
mydataset.newtable
, your table qualifier might be
`myproject.mydataset.newtable`
.
If the table name exists in the dataset, the following error is returned:
Already Exists: project_id:dataset.table
The table schema contains 3 columns:
- x: A
REQUIRED
integer - y: A
REQUIRED
STRUCT containing a (an array of strings), b (aREQUIRED
boolean), and c (aNULLABLE
float) z: A
NULLABLE
string
Creating a table with collation support
The following examples create a table named newtable
in mydataset
with
columns a
, b
, c
, and a struct with fields x
and y
.
All STRING
column schemas in this table are collated with 'und:ci'
:
CREATE TABLE mydataset.newtable ( a STRING, b STRING, c STRUCT< x FLOAT64 y ARRAY<STRING> > ) DEFAULT COLLATE 'und:ci';
Only b
and y
are collated with 'und:ci'
:
CREATE TABLE mydataset.newtable ( a STRING, b STRING COLLATE 'und:ci', c STRUCT< x FLOAT64 y ARRAY<STRING COLLATE 'und:ci'> > );
Creating a table with parameterized data types
The following example creates a table named newtable
in mydataset
. The
parameters in parentheses specify that the column contains a parameterized data
type. See Parameterized Data Types
for more information about parameterized types.
CREATE TABLE mydataset.newtable ( x STRING(10), y STRUCT< a ARRAY<BYTES(5)>, b NUMERIC(15, 2) OPTIONS(rounding_mode = 'ROUND_HALF_EVEN'), c FLOAT64 >, z BIGNUMERIC(35) )
If you haven't configured a default project, prepend a project ID to the dataset
name in the example SQL, and enclose the name in backticks if project_id
contains special characters:
`project_id.dataset.table`
. Instead of
mydataset.newtable
, your table qualifier should be
`myproject.mydataset.newtable`
.
If the table name exists in the dataset, the following error is returned:
Already Exists: project_id:dataset.table
The table schema contains 3 columns:
- x: A parameterized string with a maximum length of 10
- y: A STRUCT containing a (an array of parameterized bytes with a maximum length of 5), b (a parameterized NUMERIC with a maximum precision of 15, maximum scale of 2, and rounding mode set to 'ROUND_HALF_EVEN'), and c (a float)
- z: A parameterized BIGNUMERIC with a maximum precision of 35 and maximum scale of 0
Creating a partitioned table
The following example creates a
partitioned table
named newtable
in mydataset
using a DATE
column:
CREATE TABLE mydataset.newtable (transaction_id INT64, transaction_date DATE) PARTITION BY transaction_date OPTIONS( partition_expiration_days=3, description="a table partitioned by transaction_date" )
If you haven't configured a default project, prepend a project ID to the dataset
name in the example SQL, and enclose the name in backticks if project_id
contains special characters:
`project_id.dataset.table`
. So, instead of
mydataset.newtable
, your table qualifier might be
`myproject.mydataset.newtable`
.
The table schema contains 2 columns:
- transaction_id: An integer
- transaction_date: A date
The table option list specifies the:
- Partition expiration: Three days
- Description:
A table partitioned by transaction_date
Creating a partitioned table from the result of a query
The following example creates a
partitioned table
named days_with_rain
in mydataset
using a DATE
column:
CREATE TABLE mydataset.days_with_rain PARTITION BY date OPTIONS ( partition_expiration_days=365, description="weather stations with precipitation, partitioned by day" ) AS SELECT DATE(CAST(year AS INT64), CAST(mo AS INT64), CAST(da AS INT64)) AS date, (SELECT ANY_VALUE(name) FROM `bigquery-public-data.noaa_gsod.stations` AS stations WHERE stations.usaf = stn) AS station_name, -- Stations can have multiple names prcp FROM `bigquery-public-data.noaa_gsod.gsod2017` AS weather WHERE prcp != 99.9 -- Filter unknown values AND prcp > 0 -- Filter stations/days with no precipitation
If you haven't configured a default project, prepend a project ID to the dataset
name in the example SQL, and enclose the name in backticks if project_id
contains special characters:
`project_id.dataset.table`
. So, instead of
mydataset.days_with_rain
, your table qualifier might be
`myproject.mydataset.days_with_rain`
.
The table schema contains 2 columns:
- date: The
DATE
of data collection - station_name: The name of the weather station as a
STRING
- prcp: The amount of precipitation in inches as a
FLOAT64
The table option list specifies the:
- Partition expiration: One year
- Description:
Weather stations with precipitation, partitioned by day
Creating a clustered table
Example 1
The following example creates a
clustered table
named myclusteredtable
in mydataset
. The table is a partitioned table,
partitioned by a TIMESTAMP
column and clustered by a STRING
column named
customer_id
.
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" )
If you haven't configured a default project, prepend a project ID to the dataset
name in the example SQL, and enclose the name in backticks if project_id
contains special characters:
`project_id.dataset.table`
. So, instead of
mydataset.myclusteredtable
, your table qualifier might be
`myproject.mydataset.myclusteredtable`
.
The table schema contains 3 columns:
- timestamp: The time of data collection as a
TIMESTAMP
- customer_id: The customer ID as a
STRING
- transaction_amount: The transaction amount as
NUMERIC
The table option list specifies the:
- Partition expiration: 3 days
- Description:
A table clustered by customer_id
Example 2
The following example creates a
clustered table
named myclusteredtable
in mydataset
. The table is an
ingestion-time partitioned table.
CREATE TABLE mydataset.myclusteredtable ( customer_id STRING, transaction_amount NUMERIC ) PARTITION BY DATE(_PARTITIONTIME) CLUSTER BY customer_id OPTIONS ( partition_expiration_days=3, description="a table clustered by customer_id" )
If you haven't configured a default project, prepend a project ID to the dataset
name in the example SQL, and enclose the name in backticks if project_id
contains special characters:
`project_id.dataset.table`
. So, instead of
mydataset.myclusteredtable
, your table qualifier might be
`myproject.mydataset.myclusteredtable`
.
The table schema contains 2 columns:
- customer_id: The customer ID as a
STRING
- transaction_amount: The transaction amount as
NUMERIC
The table option list specifies the:
- Partition expiration: 3 days
- Description:
A table clustered by customer_id
Example 3
The following example creates a
clustered table
named myclusteredtable
in mydataset
. The table is not partitioned.
CREATE TABLE mydataset.myclusteredtable ( customer_id STRING, transaction_amount NUMERIC ) CLUSTER BY customer_id OPTIONS ( description="a table clustered by customer_id" )
If you haven't configured a default project, prepend a project ID to the dataset
name in the example SQL, and enclose the name in backticks if project_id
contains special characters:
`project_id.dataset.table`
. So, instead of
mydataset.myclusteredtable
, your table qualifier might be
`myproject.mydataset.myclusteredtable`
.
The table schema contains 2 columns:
- customer_id: The customer ID as a
STRING
- transaction_amount: The transaction amount as
NUMERIC
The table option list specifies the:
- Description:
A table clustered by customer_id
Creating a clustered table from the result of a query
Example 1
The following example creates a
clustered table
named myclusteredtable
in mydataset
using the result of a query. The table
is a partitioned table, partitioned by a
TIMESTAMP
column.
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
If you haven't configured a default project, prepend a project ID to the dataset
name in the example SQL, and enclose the name in backticks if project_id
contains special characters:
`project_id.dataset.table`
. So, instead of
mydataset.myclusteredtable
, your table qualifier might be
`myproject.mydataset.myclusteredtable`
.
The table schema contains 3 columns:
- timestamp: The time of data collection as a
TIMESTAMP
- customer_id: The customer ID as a
STRING
- transaction_amount: The transaction amount as
NUMERIC
The table option list specifies the:
- Partition expiration: 3 days
- Description:
A table clustered by customer_id
Example 2
The following example creates a
clustered table
named myclusteredtable
in mydataset
using the result of a query. The table
is not partitioned.
CREATE TABLE mydataset.myclusteredtable ( customer_id STRING, transaction_amount NUMERIC ) CLUSTER BY customer_id OPTIONS ( description="a table clustered by customer_id" ) AS SELECT * FROM mydataset.myothertable
If you haven't configured a default project, prepend a project ID to the dataset
name in the example SQL, and enclose the name in backticks if project_id
contains special characters:
`project_id.dataset.table`
. So, instead of
mydataset.myclusteredtable
, your table qualifier might be
`myproject.mydataset.myclusteredtable`
.
The table schema contains 2 columns:
- customer_id: The customer ID as a
STRING
- transaction_amount: The transaction amount as
NUMERIC
The table option list specifies the:
- Description:
A table clustered by customer_id
Creating a temporary table
The following example creates a temporary table named Example
and inserts
values into it.
CREATE TEMP TABLE Example
(
x INT64,
y STRING
);
INSERT INTO Example
VALUES (5, 'foo');
INSERT INTO Example
VALUES (6, 'bar');
SELECT *
FROM Example;
This script returns the following output:
+-----+---+-----+
| Row | x | y |
+-----+---|-----+
| 1 | 5 | foo |
| 2 | 6 | bar |
+-----+---|-----+
CREATE TABLE LIKE
statement
Creates a new table with all of the same metadata of another table.
Syntax
CREATE [ OR REPLACE ] TABLE [ IF NOT EXISTS ] table_name LIKE [[project_name.]dataset_name.]source_table_name ... [OPTIONS(table_option_list)]
Details
This statement is a variant of the CREATE TABLE
statement and has the same
limitations.
Other than the use of the LIKE
clause in place of a column list,
the syntax is identical to the CREATE TABLE
syntax.
The CREATE TABLE LIKE
statement copies only the metadata of the source table.
You can use the as query_statement
clause to include data into the new table.
The new table has no relationship to the source table after creation; thus modifications to the source table will not propagate to the new table.
By default, the new table inherits partitioning, clustering, and options
metadata from the source table. You can customize metadata in the new table by
using the optional clauses in the SQL statement. For example, if you want to
specify a different set of options for the new table, then include the OPTIONS
clause with a list of options and values. This behavior matches that of
ALTER TABLE SET OPTIONS
.
Required permissions
This statement requires the following IAM permissions:
Permission | Resource |
---|---|
bigquery.tables.create |
The dataset where you create the table. |
bigquery.tables.get |
The source table. |
In addition, the OR REPLACE
clause requires bigquery.tables.update
and
bigquery.tables.updateData
permissions.
If the OPTIONS
clause includes any expiration options, then
bigquery.tables.delete
permission is also required.
Examples
Example 1
The following example creates a new table named newtable
in
mydataset
with the same metadata as sourcetable
:
CREATE TABLE mydataset.newtable LIKE mydataset.sourcetable
Example 2
The following example creates a new table named newtable
in
mydataset
with the same metadata as sourcetable
and the data from the
SELECT
statement:
CREATE TABLE mydataset.newtable LIKE mydataset.sourcetable AS SELECT * FROM mydataset.myothertable
CREATE TABLE COPY
statement
Creates a table that has the same metadata and data as another table. The source table can be a table, a table clone, or a table snapshot.
Syntax
CREATE [ OR REPLACE ] TABLE [ IF NOT EXISTS ] table_name COPY source_table_name ... [OPTIONS(table_option_list)]
Details
This statement is a variant of the CREATE TABLE
statement and has the same
limitations.
Other than the use of the COPY
clause in place of a column list,
the syntax is identical to the CREATE TABLE
syntax.
The CREATE TABLE COPY
statement copies both the metadata and data from the
source table.
The new table inherits partitioning and clustering from the source table. By
default, the table options metadata from the source table are also inherited,
but you can override table options by using the OPTIONS
clause. The behavior
is equivalent to running ALTER TABLE SET OPTIONS
after the table is copied.
The new table has no relationship to the source table after creation; modifications to the source table are not propagated to the new table.
Required permissions
This statement requires the following IAM permissions:
Permission | Resource |
---|---|
bigquery.tables.create |
The dataset where you create the table snapshot. |
bigquery.tables.get |
The source table. |
bigquery.tables.getData |
The source table. |
In addition, the OR REPLACE
clause requires bigquery.tables.update
and
bigquery.tables.updateData
permissions.
If the OPTIONS
clause includes any expiration options, then
bigquery.tables.delete
permission is also required.
CREATE SNAPSHOT TABLE
statement
Creates a table snapshot based on a source table. The source table can be a table, a table clone, or a table snapshot.
Syntax
CREATE SNAPSHOT TABLE [ IF NOT EXISTS ] table_snapshot_name CLONE source_table_name [FOR SYSTEM_TIME AS OF time_expression] [OPTIONS(snapshot_option_list)]
Arguments
IF NOT EXISTS
: If a table snapshot or other table resource exists with the same name, theCREATE
statement has no effect.table_snapshot_name
: The name of the table snapshot that you want to create. The table snapshot name must be unique per dataset. See Table path syntax.source_table_name
: The name of the table that you want to snapshot or the table snapshot that you want to copy. See Table path syntax.If the source table is a standard table, then BigQuery creates a table snapshot of the source table. If the source table is a table snapshot, then BigQuery creates a copy of the table snapshot.
FOR SYSTEM_TIME AS OF
: Lets you select the version of the table that was current at the time specified bytimestamp_expression
. It can only be used when creating a snapshot of a table; it can't be used when making a copy of a table snapshot.snapshot_option_list
: Additional table snapshot creation options such as a label and an expiration time.
Details
CREATE SNAPSHOT TABLE
statements must comply with the following rules:
- Only one
CREATE
statement is allowed. - The source table must be one of the following:
- A table
- A table clone
- A table snapshot
- The
FOR SYSTEM_TIME AS OF
clause can only be used when creating a snapshot of a table or table clone; it can't be used when making a copy of a table snapshot.
snapshot_option_list
The option list lets you set table snapshot options such as a label and an expiration time. You can include multiple options using a comma-separated list.
Specify a table snapshot option list in the following format:
NAME=VALUE, ...
NAME
and VALUE
must be one of the following combinations:
NAME |
VALUE |
Details |
---|---|---|
expiration_timestamp |
TIMESTAMP |
Example: This property is equivalent to the
|
friendly_name |
|
Example: This property is equivalent to the
|
description |
|
Example: This property is equivalent to the
|
labels |
|
Example: This property is equivalent to the
|
VALUE
is a constant expression that contains only literals, query parameters,
and scalar functions.
The constant expression cannot contain:
- A reference to a table
- Subqueries or SQL statements such as
SELECT
,CREATE
, andUPDATE
- User-defined functions, aggregate functions, or analytic functions
- The following scalar functions:
ARRAY_TO_STRING
REPLACE
REGEXP_REPLACE
RAND
FORMAT
LPAD
RPAD
REPEAT
SESSION_USER
GENERATE_ARRAY
GENERATE_DATE_ARRAY
If VALUE
evaluates to NULL
, the corresponding option NAME
in the
CREATE SNAPSHOT TABLE
statement is ignored.
Required permissions
This statement requires the following IAM permissions:
Permission | Resource |
---|---|
bigquery.tables.create
|
The dataset where you create the table snapshot. |
bigquery.tables.createSnapshot |
The source table. |
bigquery.tables.get |
The source table. |
bigquery.tables.getData |
The source table. |
Examples
Create a table snapshot: fail if it already exists
The following example creates a table snapshot of the table
myproject.mydataset.mytable
. The table snapshot is created in the dataset
mydataset
and is named mytablesnapshot
:
CREATE SNAPSHOT TABLE `myproject.mydataset.mytablesnapshot` CLONE `myproject.mydataset.mytable` OPTIONS( expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR), friendly_name="my_table_snapshot", description="A table snapshot that expires in 2 days", labels=[("org_unit", "development")] )
If the table snapshot name already exists in the dataset, then the following error is returned:
Already Exists: myproject.mydataset.mytablesnapshot
The table snapshot option list specifies the following:
- Expiration time: 48 hours after the time the table snapshot is created
- Friendly name:
my_table_snapshot
- Description:
A table snapshot that expires in 2 days
- Label:
org_unit = development
Create a table snapshot: ignore if it already exists
The following example creates a table snapshot of the table
myproject.mydataset.mytable
. The table snapshot is created in the dataset
mydataset
and is named mytablesnapshot
:
CREATE SNAPSHOT TABLE IF NOT EXISTS `myproject.mydataset.mytablesnapshot` CLONE `myproject.mydataset.mytable` OPTIONS( expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR), friendly_name="my_table_snapshot", description="A table snapshot that expires in 2 days" labels=[("org_unit", "development")] )
The table snapshot option list specifies the following:
- Expiration time: 48 hours after the time the table snapshot is created
- Friendly name:
my_table_snapshot
- Description:
A table snapshot that expires in 2 days
- Label:
org_unit = development
If the table snapshot name already exists in the dataset, then no action is taken, and no error is returned.
For information about restoring table snapshots, see
CREATE TABLE CLONE
.
For information about removing table snapshots, see
DROP SNAPSHOT TABLE
.
CREATE TABLE CLONE
statement
Creates a table clone based on a source table. The source table can be a table, a table clone, or a table snapshot.
Syntax
CREATE [ OR REPLACE ] TABLE [ IF NOT EXISTS ] destination_table_name CLONE source_table_name [FOR SYSTEM_TIME AS OF time_expression] ... [OPTIONS(table_option_list)]
Details
Other than the use of the CLONE
clause in place of a column list, the syntax
is identical to the CREATE TABLE
syntax.
Arguments
OR REPLACE
: Replaces a table with the same name if it exists. Cannot appear withIF NOT EXISTS
.IF NOT EXISTS
: If the specified destination table name already exists, theCREATE
statement has no effect. Cannot appear withOR REPLACE
.
destination_table_name
is the name of the table that you want to create.
The table name must
be unique per dataset. The table name can contain the following:
- Up to 1,024 characters
- Letters (upper or lower case), numbers, and underscores
OPTIONS(table_option_list)
lets you specify
additional table creation options such as a
label and
an expiration time.
source_table_name
is the name of the source
table.
CREATE TABLE CLONE
statements must comply with the following rules:
- Only one
CREATE
statement is allowed. - The table that is being cloned must be a table, a table clone, or a table snapshot.
OPTIONS
CREATE TABLE CLONE
options are the same as
CREATE TABLE
options.
Required permissions
This statement requires the following IAM permissions:
Permission | Resource |
---|---|
bigquery.tables.create |
The dataset where you create the table clone. |
bigquery.tables.get |
The source table. |
bigquery.tables.getData |
The source table. |
bigquery.tables.restoreSnapshot |
The source table (required only if the source table is a table snapshot). |
In addition, the OR REPLACE
clause requires bigquery.tables.update
and
bigquery.tables.updateData
permissions.
If the OPTIONS
clause includes any expiration options, then
bigquery.tables.delete
permission is also required.
Examples
Restore a table snapshot: fail if destination table already exists
The following example creates the table
myproject.mydataset.mytable
from the table snapshot
myproject.mydataset.mytablesnapshot
:
CREATE TABLE `myproject.mydataset.mytable` CLONE `myproject.mydataset.mytablesnapshot` OPTIONS( expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 365 DAY), friendly_name="my_table", description="A table that expires in 1 year", labels=[("org_unit", "development")] )
If the table name exists in the dataset, then the following error is returned:
Already Exists: myproject.mydataset.mytable.
The table option list specifies the following:
- Expiration time: 365 days after the time that the table is created
- Friendly name:
my_table
- Description:
A table that expires in 1 year
- Label:
org_unit = development
Create a clone of a table: ignore if the destination table already exists
The following example creates the table clone
myproject.mydataset.mytableclone
based on the table
myproject.mydataset.mytable
:
CREATE TABLE IF NOT EXISTS `myproject.mydataset.mytableclone` CLONE `myproject.mydataset.mytable` OPTIONS( expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 365 DAY), friendly_name="my_table", description="A table that expires in 1 year", labels=[("org_unit", "development")] )
The table option list specifies the following:
- Expiration time: 365 days after the time the table is created
- Friendly name:
my_table
- Description:
A table that expires in 1 year
- Label:
org_unit = development
If the table name exists in the dataset, then no action is taken, and no error is returned.
For information about creating a copy of a table, see
CREATE TABLE COPY
.
For information about creating a snapshot of a table, see
CREATE SNAPSHOT TABLE
.
CREATE VIEW
statement
Creates a new view.
Syntax
CREATE [ OR REPLACE ] VIEW [ IF NOT EXISTS ] view_name [(view_column_name_list)] [OPTIONS(view_option_list)] AS query_expression
Arguments
OR REPLACE
: Replaces any view with the same name if it exists. Cannot appear withIF NOT EXISTS
.IF NOT EXISTS
: If a view or other table resource exists with the same name, theCREATE
statement has no effect. Cannot appear withOR REPLACE
.view_name
: The name of the view you're creating. See Table path syntax.view_column_name_list
: Lets you explicitly specify the column names of the view, which may be aliases to the column names in the underlying SQL query.view_option_list
: Additional view creation options such as a label and an expiration time.query_expression
: The Google Standard SQL query expression used to define the view.
Details
CREATE VIEW
statements must comply with the following rules:
- Only one
CREATE
statement is allowed.
view_column_name_list
The view's column name list is optional. The names must be unique but do not have to be the same as the column names of the underlying SQL query. For example, if your view is created with the following statement:
CREATE VIEW mydataset.age_groups(age, count) AS SELECT age, COUNT(*)
FROM mydataset.people
group by age;
Then you can query it with:
SELECT age, count from mydataset.age_groups;
The number of columns in the column name list must match the number of columns in the underlying SQL query. If the columns in the table of the underlying SQL query is added or dropped, the view becomes invalid and must be recreated. For example, if the age
column is dropped from the mydataset.people
table, then the view created in the previous example becomes invalid.
view_option_list
The option list allows you to set view options such as a label and an expiration time. You can include multiple options using a comma-separated list.
Specify a view option list in the following format:
NAME=VALUE, ...
NAME
and VALUE
must be one of the following combinations:
NAME |
VALUE |
Details |
---|---|---|
expiration_timestamp |
TIMESTAMP |
Example: This property is equivalent to the expirationTime table resource property. |
friendly_name |
|
Example: This property is equivalent to the friendlyName table resource property. |
description |
|
Example: This property is equivalent to the description table resource property. |
labels |
|
Example: This property is equivalent to the labels table resource property. |
VALUE
is a constant expression containing only literals, query parameters,
and scalar functions.
The constant expression cannot contain:
- A reference to a table
- Subqueries or SQL statements such as
SELECT
,CREATE
, orUPDATE
- User-defined functions, aggregate functions, or analytic functions
- The following scalar functions:
ARRAY_TO_STRING
REPLACE
REGEXP_REPLACE
RAND
FORMAT
LPAD
RPAD
REPEAT
- <