Data definition language (DDL) statements in GoogleSQL
Data definition language (DDL) statements let you create and modify BigQuery resources using GoogleSQL 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.
Run 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.
On-demand query size calculation
If you use on-demand billing, BigQuery charges for data definition language (DDL) queries based on the number of bytes processed by the query.
DDL statement | Bytes processed |
---|---|
CREATE TABLE |
None. |
CREATE TABLE ... AS SELECT ... |
The sum of bytes processed for all the columns referenced from the tables scanned by the query. |
CREATE VIEW |
None. |
DROP TABLE |
None. |
DROP VIEW |
None. |
For more information about cost estimation, see Estimate and control costs.
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.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 i