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.
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.