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

  1. Go to the BigQuery page in the Google Cloud console.

    Go to BigQuery

  2. Click Compose new query.

  3. Enter the DDL statement into the Query editortext area. For example:

      
 CREATE 
  
 TABLE 
  
 mydataset 
 . 
 newtable 
  
 ( 
  
 x 
  
 INT64 
  
 )

  4. 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. Or DROP 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 a CREATE TABLE/VIEW statement or a DROP 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.

Before trying this sample, follow the Java setup instructions in the BigQuery quickstart using client libraries . For more information, see the BigQuery Java API reference documentation .

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries .

  import 
  
 com.google.cloud.bigquery.BigQuery 
 ; 
 import 
  
 com.google.cloud.bigquery.BigQueryException 
 ; 
 import 
  
 com.google.cloud.bigquery.BigQueryOptions 
 ; 
 import 
  
 com.google.cloud.bigquery.Job 
 ; 
 import 
  
 com.google.cloud.bigquery.JobInfo 
 ; 
 import 
  
 com.google.cloud.bigquery.QueryJobConfiguration 
 ; 
 // Sample to create a view using DDL 
 public 
  
 class 
 DDLCreateView 
  
 { 
  
 public 
  
 static 
  
 void 
  
 runDDLCreateView 
 () 
  
 { 
  
 // TODO(developer): Replace these variables before running the sample. 
  
 String 
  
 projectId 
  
 = 
  
 "MY_PROJECT_ID" 
 ; 
  
 String 
  
 datasetId 
  
 = 
  
 "MY_DATASET_ID" 
 ; 
  
 String 
  
 tableId 
  
 = 
  
 "MY_VIEW_ID" 
 ; 
  
 String 
  
 ddl 
  
 = 
  
 "CREATE VIEW " 
  
 + 
  
 "`" 
  
 + 
  
 projectId 
  
 + 
  
 "." 
  
 + 
  
 datasetId 
  
 + 
  
 "." 
  
 + 
  
 tableId 
  
 + 
  
 "`" 
  
 + 
  
 " OPTIONS(" 
  
 + 
  
 " expiration_timestamp=TIMESTAMP_ADD(" 
  
 + 
  
 " CURRENT_TIMESTAMP(), INTERVAL 48 HOUR)," 
  
 + 
  
 " friendly_name=\"new_view\"," 
  
 + 
  
 " description=\"a view that expires in 2 days\"," 
  
 + 
  
 " labels=[(\"org_unit\", \"development\")]" 
  
 + 
  
 " )" 
  
 + 
  
 " AS SELECT name, state, year, number" 
  
 + 
  
 " FROM `bigquery-public-data.usa_names.usa_1910_current`" 
  
 + 
  
 " WHERE state LIKE 'W%'`" 
 ; 
  
 ddlCreateView 
 ( 
 ddl 
 ); 
  
 } 
  
 public 
  
 static 
  
 void 
  
 ddlCreateView 
 ( 
 String 
  
 ddl 
 ) 
  
 { 
  
 try 
  
 { 
  
 // Initialize client that will be used to send requests. This client only needs to be created 
  
 // once, and can be reused for multiple requests. 
  
 BigQuery 
  
 bigquery 
  
 = 
  
 BigQueryOptions 
 . 
 getDefaultInstance 
 (). 
 getService 
 (); 
  
 QueryJobConfiguration 
  
 config 
  
 = 
  
 QueryJobConfiguration 
 . 
 newBuilder 
 ( 
 ddl 
 ). 
 build 
 (); 
  
 // create a view using query and it will wait to complete job. 
  
 Job 
  
 job 
  
 = 
  
 bigquery 
 . 
 create 
 ( 
 JobInfo 
 . 
 of 
 ( 
 config 
 )); 
  
 job 
  
 = 
  
 job 
 . 
 waitFor 
 (); 
  
 if 
  
 ( 
 job 
 . 
 isDone 
 ()) 
  
 { 
  
 System 
 . 
 out 
 . 
 println 
 ( 
 "View created successfully" 
 ); 
  
 } 
  
 else 
  
 { 
  
 System 
 . 
 out 
 . 
 println 
 ( 
 "View was not created" 
 ); 
  
 } 
  
 } 
  
 catch 
  
 ( 
 BigQueryException 
  
 | 
  
 InterruptedException 
  
 e 
 ) 
  
 { 
  
 System 
 . 
 out 
 . 
 println 
 ( 
 "View was not created. \n" 
  
 + 
  
 e 
 . 
 toString 
 ()); 
  
 } 
  
 } 
 }