Using Data Definition Language Statements

Data definition language (DDL) statements allow you to create and modify BigQuery resources using standard SQL query syntax. Currently, you can use DDL commands in BigQuery to:

CREATE TABLE statement

To create a table in BigQuery, use the CREATE TABLE DDL statement.

Syntax

{CREATE TABLE | CREATE TABLE IF NOT EXISTS | CREATE OR REPLACE TABLE}
table_name
(
  column_name column_schema[, ...]
)
[PARTITION BY partition_expression]
[OPTIONS(options_clause[, ...])]

Where:

{CREATE TABLE | CREATE TABLE IF NOT EXISTS | CREATE OR REPLACE TABLE} is one of the following statements:

  • CREATE TABLE — creates a new table.
  • CREATE TABLE IF NOT EXISTS — creates a new table only if the table does not currently exist in the specified dataset.
  • CREATE OR REPLACE TABLE — creates a table and replaces an existing table with the same name in the specified dataset.

CREATE TABLE statements must comply with the following rules:

  • Only one CREATE statement is allowed.
  • Column names must be specified.
  • Duplicate names are not allowed in the list of columns.

table_name

table_name is the name of the table you're creating. The table name must be unique per dataset. The table name can:

  • Contain up to 1,024 characters
  • Contain letters (upper or lower case), numbers, and underscores

column_name and column_schema

(column_name column_schema[, ...]) contains the table's schema information in a comma-separated list:

  • 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 128 characters
  • column_schema is similar to a data type, but supports an optional NOT NULL constraint for types other than ARRAY. column_schema also supports options on top-level columns and STRUCT fields.
column_schema :=
   {simple_type [NOT NULL] |
    STRUCT<field_list> [NOT NULL] |
    ARRAY<array_element_schema>}
   [column_options_clause]

field_list := field_name column_schema [, ...]

array_element_schema := {simple_type | STRUCT<field_list>} [NOT NULL]

simple_type is any supported data type aside from STRUCT and ARRAY.

field_name is the name of the struct field. Struct field names have the same restrictions as column names.

When the NOT NULL constraint is present for a column or field, the column or field is created with REQUIRED mode. Conversely, when the NOT NULL constraint is absent, the column or field is created with NULLABLE mode.

Columns and fields of ARRAY type do not support the NOT NULL modifier. For example, a column_schema of ARRAY<INT64> NOT NULL is invalid, since ARRAY columns have REPEATED mode and can be empty but cannot be NULL. An array element in a table can never be NULL, regardless of whether the NOT NULL constraint is specified. For example, ARRAY<INT64> is equivalent to ARRAY<INT64 NOT NULL>.

The NOT NULL attribute of a table's column_schema does not propagate through queries over the table. If table T contains a column declared as x INT64 NOT NULL, for example, SELECT x FROM T returns a query result in which x is NULLABLE by default.

column_schema can be used only in the column definition list of CREATE TABLE statements. It cannot be used as a type in expressions. For example, CAST(1 AS INT64 NOT NULL) is not valid.

partition_expression

partition_expression is an optional expression that controls table partitioning. The partition expression can contain the following values:

options_clause

options_clause allows you to specify additional table creation options such as a label and an expiration time.

Specifying table options

The [options_clause] allows you to specify additional, optional table options such as a label and an expiration time. You can include multiple options using a comma-separated list.

Specify a table options clause in the following format:

OPTIONS(NAME=VALUE, ...)

Where:

  • NAME is the parameter.
  • VALUE is a constant expression containing only literals, query parameters, and scalar functions. If the constant expression evaluates to null, the corresponding option NAME is ignored. The constant expression cannot contain:
    • A reference to a table
    • Subqueries — SQL statements such as SELECT, CREATE, and UPDATE
    • 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

NAME and VALUE must be one of the following combinations:

NAME VALUE Details
expiration_timestamp TIMESTAMP

Example: expiration_timestamp=TIMESTAMP "2020-01-01 00:00:00 UTC"

This property is equivalent to the expirationTime table resource property.

partition_expiration_days

FLOAT64

Example: partition_expiration_days=7

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 used when creating a partitioned table.

kms_key_name

STRING

Example: kms_key_name="projects/[PROJECT_ID]/locations/[LOCATION]/keyRings/[KEYRING]/cryptoKeys/[KEY]"

This property is equivalent to the encryptionConfiguration.kmsKeyName table resource property.

See more details about Protecting Data with Cloud KMS Keys.

friendly_name

STRING

Example: friendly_name="my_table"

This property is equivalent to the friendlyName table resource property.

description

STRING

Example: description="a table that expires in 2020"

This property is equivalent to the description table resource property.

labels

ARRAY<STRUCT<STRING, STRING>>

Example: labels=[("org_unit", "development")]

This property is equivalent to the labels table resource property.

Examples

Creating a new table

The CREATE TABLE DDL statement creates a table with the specified options. If the table name exists in the dataset, the following error is returned:

Already Exists: [PROJECT]:[DATASET].[TABLE]

The following example creates a table named newtable in mydataset. If you do not have a default project configured, prepend it to the dataset name in following format: `[PROJECT].[DATASET].[TABLE]` (including the backticks); for example, `myproject.mydataset.newtable`.

The table schema contains 2 columns:

  • x — an integer
  • y — a STRUCT containing 2 columns: a (an array of strings) and b (a boolean)

The options clause specifies the:

  • Expiration time — January 1, 2020 at 00:00:00 UTC
  • Description — a table that expires in 2020
  • Label — org_unit = development

To create a new table using DDL:

Web UI

  1. Go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click Compose query.

  3. Type your DDL statement into the New Query text area.

     #standardSQL
     CREATE TABLE mydataset.newtable (x INT64, y STRUCT<a ARRAY<STRING>, b BOOL>)
     PARTITION BY DATE(_PARTITIONTIME)
     OPTIONS(
       expiration_timestamp=TIMESTAMP "2020-01-01 00:00:00 UTC",
       partition_expiration_days=1,
       description="a table that expires in 2020, with each partition living for 24 hours",
       labels=[("org_unit", "development")]
     )
     

  4. Click Run query. When the query completes, the table appears in the navigation pane.

Command-line

Enter the bq query command and supply the DDL statement as the query parameter.

bq query --use_legacy_sql=false '
CREATE TABLE mydataset.newtable (x INT64, y STRUCT<a ARRAY<STRING>, b BOOL>)
PARTITION BY DATE(_PARTITIONTIME)
OPTIONS(
  expiration_timestamp=TIMESTAMP "2020-01-01 00:00:00 UTC",
  partition_expiration_days=1,
  description="a table that expires in 2020, with each partition living for 24 hours",
  labels=[("org_unit", "development")]
)'

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

Creating a table only if the table doesn't exist

The CREATE TABLE IF NOT EXISTS DDL statement creates a table with the specified options only if the table name does not exist in the dataset. If the table name exists in the dataset, no error is returned, and no action is taken.

The following example creates a table named newtable in mydataset only if no table named newtable exists in mydataset. If you do not have a default project configured, prepend it to the dataset name in following format: `[PROJECT].[DATASET].[TABLE]` (including the backticks); for example, `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 options clause specifies the:

  • Expiration time — January 1, 2020 at 00:00:00 UTC
  • Description — a table that expires in 2020
  • Label — org_unit = development

To create a new table using DDL only if the table name doesn't exist in the dataset:

Web UI

  1. Go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click Compose query.

  3. Type your DDL statement into the New Query text area.

     #standardSQL
     CREATE TABLE IF NOT EXISTS mydataset.newtable (x INT64, y STRUCT<a ARRAY<STRING>, b BOOL>)
     OPTIONS(
       expiration_timestamp=TIMESTAMP "2020-01-01 00:00:00 UTC",
       description="a table that expires in 2020",
       labels=[("org_unit", "development")]
     )
     

  4. Click Run query. When the query completes, the table appears in the navigation pane.

Command-line

Enter the bq query command and supply the DDL statement as the query parameter.

bq query --use_legacy_sql=false '
CREATE TABLE IF NOT EXISTS mydataset.newtable (x INT64, y STRUCT<a ARRAY<STRING>, b BOOL>)
 OPTIONS(
   expiration_timestamp=TIMESTAMP "2020-01-01 00:00:00 UTC",
   description="a table that expires in 2020",
   labels=[("org_unit", "development")]
 )'

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

Creating or replacing a table

The CREATE OR REPLACE TABLE DDL statement creates a table with the specified options. If the table name exists in the dataset, the table is overwritten with an empty table.

The following example creates a table named newtable in mydataset, and if newtable exists in mydataset, it is overwritten. If you do not have a default project configured, prepend it to the dataset name in following format: `[PROJECT].[DATASET].[TABLE]` (including the backticks); for example, `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 options clause specifies the:

  • Expiration time — January 1, 2020 at 00:00:00 UTC
  • Description — a table that expires in 2020
  • Label — org_unit = development

To create a new table using DDL and to overwrite a table with the same name:

Web UI

  1. Go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click Compose query.

  3. Type your DDL statement into the New Query text area.

     #standardSQL
     CREATE OR REPLACE TABLE mydataset.newtable (x INT64, y STRUCT<a ARRAY<STRING>, b BOOL>)
     OPTIONS(
       expiration_timestamp=TIMESTAMP "2020-01-01 00:00:00 UTC",
       description="a table that expires in 2020",
       labels=[("org_unit", "development")]
     )
     

  4. Click Run query. When the query completes, the table appears in the navigation pane.

Command-line

Enter the bq query command and supply the DDL statement as the query parameter.

bq query --use_legacy_sql=false '
CREATE OR REPLACE TABLE mydataset.newtable (x INT64, y STRUCT<a ARRAY<STRING>, b BOOL>)
 OPTIONS(
   expiration_timestamp=TIMESTAMP "2020-01-01 00:00:00 UTC",
   description="a table that expires in 2020",
   labels=[("org_unit", "development")]
 )'

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

Creating a table with REQUIRED columns

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.

The following example creates a table named newtable in mydataset. If the table name exists in the dataset, the following error is returned:

Already Exists: [PROJECT]:[DATASET].[TABLE]

If you do not have a default project configured, prepend it to the dataset name in following format: `[PROJECT].[DATASET].[TABLE]` (including the backticks); for example, `myproject.mydataset.newtable`.

The table schema contains 3 columns:

  • x — a REQUIRED integer
  • y — a REQUIRED STRUCT containing a (an array of strings), b (a REQUIRED boolean), and c (a NULLABLE float)
  • z — a NULLABLE string

To create a new table with REQUIRED columns using DDL:

Web UI

  1. Go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click Compose query.

  3. Type your DDL statement into the New Query text area.

     #standardSQL
     CREATE TABLE my_dataset.new_table (
       x INT64 NOT NULL,
       y STRUCT<
         a ARRAY<STRING>,
         b BOOL NOT NULL,
         c FLOAT64
       > NOT NULL,
       z STRING
     )
     

  4. Click Run query. When the query completes, the table appears in the navigation pane.

Command-line

Enter the bq query command and supply the DDL statement as the query parameter.

bq query --use_legacy_sql=false '
 CREATE TABLE my_dataset.new_table (
   x INT64 NOT NULL,
   y STRUCT<
     a ARRAY<STRING>,
     b BOOL NOT NULL,
     c FLOAT64
   > NOT NULL,
   z STRING
 )'

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

Creating a partitioned table

The following example creates a partitioned table named newtable in mydataset using a DATE column. If you do not have a default project configured, prepend it to the dataset name in following format: `[PROJECT].[DATASET].[TABLE]` (including the backticks); for example, `myproject.mydataset.newtable`.

The table schema contains 2 columns:

  • transaction_id — an integer
  • transaction_date — a date

The options clause specifies the:

  • Partition expiration — three days
  • Description — a table partitioned by transaction_date

To create a new table using DDL:

Web UI

  1. Go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click Compose query.

  3. Type your DDL statement into the New Query text area.

     #standardSQL
     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"
     )
     

  4. Click Run query. When the query completes, the table appears in the navigation pane.

Command-line

Enter the bq query command and supply the DDL statement as the query parameter.

bq query --use_legacy_sql=false '
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"
)'

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

CREATE VIEW statement

To create a view in BigQuery, use the CREATE VIEW DDL statement.

Syntax

{CREATE VIEW | CREATE VIEW IF NOT EXISTS | CREATE OR REPLACE VIEW}
view_name
[OPTIONS(options_clause[, ...])]
AS query_expression

Where:

{CREATE VIEW | CREATE VIEW IF NOT EXISTS | CREATE OR REPLACE VIEW} is one of the following statements:

  • CREATE VIEW — creates a new view.
  • CREATE VIEW IF NOT EXISTS — creates a new view only if the view does not currently exist in the specified dataset.
  • CREATE OR REPLACE VIEW — creates a view and replaces an existing view with the same name in the specified dataset.

view_name is the name of the view you're creating. The view name must be unique per dataset. The view name can:

  • Contain up to 1,024 characters
  • Contain letters (upper or lower case), numbers, and underscores

options_clause allows you to specify additional view creation options such as a label and an expiration time.

CREATE VIEW statements must comply with the following rules:

  • Only one CREATE statement is allowed.

query_expression is the standard SQL query expression used to define the view.

Specifying view options

The [options_clause] allows you to specify additional, optional view options such as a label and an expiration time. You can include multiple options using a comma-separated list.

Specify a view options clause in the following format:

OPTIONS(NAME=VALUE, ...)

Where:

  • NAME is the parameter.
  • VALUEis a constant expression containing only literals, query parameters, and scalar functions. If the constant expression evaluates tonull, the corresponding optionNAME` is ignored. The constant expression cannot contain:
    • A reference to a table
    • Subqueries — SQL statements such as SELECT, CREATE, and UPDATE
    • 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

NAME and VALUE must be one of the following combinations:

NAME VALUE Details
expiration_timestamp TIMESTAMP

Example: expiration_timestamp=TIMESTAMP "2020-01-01 00:00:00 UTC"

This property is equivalent to the expirationTime table resource property.

friendly_name

STRING

Example: friendly_name="my_view"

This property is equivalent to the friendlyName table resource property.

description

STRING

Example: description="a view that expires in 2020"

This property is equivalent to the description table resource property.

labels

ARRAY<STRUCT<STRING, STRING>>

Example: labels=[("org_unit", "development")]

This property is equivalent to the labels table resource property.

Examples

Creating a new view

The CREATE VIEW DDL statement creates a view with the specified options. If the view name exists in the dataset, the following error is returned:

Already Exists: [PROJECT]:[DATASET].[VIEW]

The following example creates a view named newview in mydataset. When you use a DDL statement to create a view, you must specify the project, dataset, and view in the following format: `[PROJECT].[DATASET].[VIEW]` (including the backticks); for example, `myproject.mydataset.newview`.

The view is defined using the following standard SQL query:

SELECT column_1, column_2, column_3 FROM myproject.mydataset.mytable

The options clause specifies the:

  • Expiration time — 48 hours from the time the view is created
  • Friendly name — newview
  • Description — a view that expires in 2 days
  • Label — org_unit = development

To create a new view using DDL:

Web UI

  1. Go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click Compose query.

  3. Type your DDL statement into the New Query text area.

     #standardSQL
     CREATE VIEW `myproject.mydataset.newview`
     OPTIONS(
       expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
       friendly_name="newview",
       description="a view that expires in 2 days",
       labels=[("org_unit", "development")]
     )
     AS SELECT column_1, column_2, column_3 FROM myproject.mydataset.mytable
     

  4. Click Run query. When the query completes, the table appears in the navigation pane.

Command-line

Enter the bq query command and supply the DDL statement as the query parameter.

bq query --use_legacy_sql=false '
CREATE TABLE myproject.mydataset.newview
 OPTIONS(
   expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
   friendly_name="newview",
   description="a view that expires in 2 days",
   labels=[("org_unit", "development")]
 )
 AS SELECT column_1, column_2, column_3 FROM myproject.mydataset.mytable'

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

Creating a view only if the view doesn't exist

The CREATE VIEW IF NOT EXISTS DDL statement creates a view with the specified options only if the view name does not exist in the dataset. If the view name exists in the dataset, no error is returned, and no action is taken.

The following example creates a view named newview in mydataset only if no view named newview exists in mydataset. When you use a DDL statement to create a view, you must specify the project, dataset, and view in the following format: `[PROJECT].[DATASET].[VIEW]` (including the backticks); for example, `myproject.mydataset.newview`.

The view is defined using the following standard SQL query:

SELECT column_1, column_2, column_3 FROM myproject.mydataset.mytable

The options clause specifies the:

  • Expiration time — 48 hours from the time the view is created
  • Friendly name — newview
  • Description — a view that expires in 2 days
  • Label — org_unit = development

To create a new view using DDL only if the view name doesn't exist in the dataset:

Web UI

  1. Go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click Compose query.

  3. Type your DDL statement into the New Query text area.

     #standardSQL
     CREATE VIEW IF NOT EXISTS `myproject.mydataset.newview`
     OPTIONS(
       expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
       friendly_name="newview",
       description="a view that expires in 2 days",
       labels=[("org_unit", "development")]
     )
     AS SELECT column_1, column_2, column_3 FROM myproject.mydataset.mytable
     

  4. Click Run query. When the query completes, the table appears in the navigation pane.

Command-line

Enter the bq query command and supply the DDL statement as the query parameter.

bq query --use_legacy_sql=false '
CREATE VIEW IF NOT EXISTS myproject.mydataset.newview
 OPTIONS(
   expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
   friendly_name="newview",
   description="a view that expires in 2 days",
   labels=[("org_unit", "development")]
 )
 AS SELECT column_1, column_2, column_3 FROM myproject.mydataset.mytable'

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

Creating or replacing a view

The CREATE OR REPLACE VIEW DDL statement creates a view with the specified options. If the view name exists in the dataset, the view is overwritten using the specified query expression.

The following example creates a view named newview in mydataset, and if newview exists in mydataset, it is overwritten. When you use a DDL statement to create a view, you must specify the project, dataset, and view in the following format: `[PROJECT].[DATASET].[VIEW]` (including the backticks); for example, `myproject.mydataset.newview`.

The view is defined using the following standard SQL query:

    SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`

The options clause specifies the:

  • Expiration time — 48 hours from the time the view is created
  • Friendly name — newview
  • Description — a view that expires in 2 days
  • Label — org_unit = development

To create a new view using DDL and to overwrite a view with the same name:

Web UI

  1. Go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click Compose query.

  3. Type your DDL statement into the New Query text area.

     #standardSQL
     CREATE OR REPLACE VIEW `myproject.mydataset.newview`
     OPTIONS(
       expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
       friendly_name="newview",
       description="a view that expires in 2 days",
       labels=[("org_unit", "development")]
     )
     AS SELECT column_1, column_2, column_3 FROM myproject.mydataset.mytable
     

  4. Click Run query. When the query completes, the table appears in the navigation pane.

Command-line

Enter the bq query command and supply the DDL statement as the query parameter.

bq query --use_legacy_sql=false '
CREATE OR REPLACE VIEW myproject.mydataset.newview
 OPTIONS(
   expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
   friendly_name="newview",
   description="a view that expires in 2 days",
   labels=[("org_unit", "development")]
 )
 AS SELECT column_1, column_2, column_3 FROM myproject.mydataset.mytable'

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

DROP TABLE statement

To delete a table in BigQuery, use the DROP TABLE DDL statement.

Syntax

{DROP TABLE | DROP TABLE IF EXISTS}
table_name

Where:

{DROP TABLE | DROP TABLE IF EXISTS} is one of the following statements:

  • DROP TABLE — deletes a table in the specified dataset
  • DROP TABLE IF EXISTS — deletes a table only if the table exists in the specified dataset

table_name is the name of the table you're deleting.

Examples

Deleting a table

The DROP TABLE DDL statement deletes a table in the specified dataset. If the table name does not exist in the dataset, the following error is returned:

Error: Not found: Table myproject:mydataset.mytable

If you are deleting a table in another project, you must specify the project, dataset, and table in the following format: `[PROJECT].[DATASET].[TABLE]` (including the backticks); for example, `myproject.mydataset.mytable`.

To delete a table using DDL:

Web UI

  1. Go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click Compose query.

  3. Type your DDL statement into the New Query text area.

     #standardSQL
     DROP TABLE mydataset.mytable
     

  4. Click Run query. When the query completes, the table is removed from the navigation pane.

Command-line

Enter the bq query command and supply the DDL statement as the query parameter.

bq query --use_legacy_sql=false '
DROP TABLE mydataset.mytable'

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

Deleting a table only if the table exists

The DROP TABLE IF EXISTS DDL statement deletes a table in the specified dataset only if the table exists. If the table name does not exist in the dataset, no error is returned, and no action is taken.

If you are deleting a table in another project, you must specify the project, dataset, and table in the following format: `[PROJECT].[DATASET].[TABLE]` (including the backticks); for example, `myproject.mydataset.mytable`.

To delete a table using DDL only if the table exists:

Web UI

  1. Go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click Compose query.

  3. Type your DDL statement into the New Query text area.

     #standardSQL
     DROP TABLE IF EXISTS mydataset.mytable
     

  4. Click Run query. When the query completes, the table is removed from the navigation pane.

Command-line

Enter the bq query command and supply the DDL statement as the query parameter.

bq query --use_legacy_sql=false '
DROP TABLE IF EXISTS mydataset.mytable'

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

DROP VIEW statement

To delete a view in BigQuery, use the DROP VIEW DDL statement.

Syntax

{DROP VIEW | DROP VIEW IF EXISTS}
view_name

Where:

{DROP VIEW | DROP VIEW IF EXISTS} is one of the following statements:

  • DROP VIEW — deletes a view in the specified dataset
  • DROP VIEW IF EXISTS — deletes a view only if the view exists in the specified dataset

view_name is the name of the view you're deleting.

Examples

Deleting a view

The DROP VIEW DDL statement deletes a view in the specified dataset. If the view name does not exist in the dataset, the following error is returned:

Error: Not found: Table myproject:mydataset.myview

If you are deleting a view in another project, you must specify the project, dataset, and view in the following format: `[PROJECT].[DATASET].[VIEW]` (including the backticks); for example, `myproject.mydataset.myview`.

To delete a view using DDL:

Web UI

  1. Go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click Compose query.

  3. Type your DDL statement into the New Query text area.

     #standardSQL
     DROP VIEW mydataset.myview
     

  4. Click Run query. When the query completes, the view is removed from the navigation pane.

Command-line

Enter the bq query command and supply the DDL statement as the query parameter.

bq query --use_legacy_sql=false '
DROP VIEW mydataset.myview'

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

Deleting a view only if the view exists

The DROP VIEW IF EXISTS DDL statement deletes a view in the specified dataset only if the view exists. If the view name does not exist in the dataset, no error is returned, and no action is taken.

If you are deleting a view in another project, you must specify the project, dataset, and view in the following format: `[PROJECT].[DATASET].[VIEW]` (including the backticks); for example, `myproject.mydataset.myview`.

To delete a view using DDL only if the view exists:

Web UI

  1. Go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click Compose query.

  3. Type your DDL statement into the New Query text area.

     #standardSQL
     DROP VIEW IF EXISTS mydataset.myview
     

  4. Click Run query. When the query completes, the table is removed from the navigation pane.

Command-line

Enter the bq query command and supply the DDL statement as the query parameter.

bq query --use_legacy_sql=false '
DROP VIEW IF EXISTS mydataset.myview'

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

Features under development

The following features are being developed but are not currently available in the beta release:

  • Automatically updating the table list in the BigQuery web UI after using a DROP TABLE DDL statement
  • CREATE TABLE AS SELECT statements

Send feedback about...