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]
[CLUSTER BY clustering_column_list]
[OPTIONS(table_option_list)]
[AS query_statement]

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.
  • 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 the as 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 the as query_statement clause.
  • Column names must be specified either through the column list or as query_statement clause.
  • Duplicate column names are not allowed.

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>}
   [OPTIONS(column_option_list)]

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, CREATE TABLE dataset.newtable AS SELECT x FROM T creates a table named dataset.newtable in which x is NULLABLE.

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

  • PARTITION BY DATE(_PARTITIONTIME) — partitions the table using the date-based timestamp in the _PARTITIONTIME pseudo column. This syntax is only supported with CREATE TABLE without the AS query_statement clause.
  • PARTITION BY DATE(<timestamp_column>) — partitions the table using the date of the TIMESTAMP column
  • PARTITION BY <date_column> — partitions the table using the DATE column

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: 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 set if the table is partitioned.
require_partition_filter

BOOL

Example: require_partition_filter=true

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

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.

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

column_option_list

The column_option_list in column_schema allows you to specify optional column or field options. Column options have the same syntax and requirements as table options but with a different list of NAMEs and VALUEs:

NAME VALUE Details
description

STRING

Example: description="a unique id"

This property is equivalent to the schema.fields[].description table resource property.

query_statement

The AS query_statement clause specifies the query from which the table should be created. See the SQL syntax reference for the supported form of query_statement.

Known 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 an INSERT 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 a CREATE TABLE ... AS SELECT ... statement to recreate it.

CREATE TABLE 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 partitioned 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 uses the following partition_expression to partition the table: PARTITION BY DATE(_PARTITIONTIME). This expression partitions the table using the date-based timestamp in the _PARTITIONTIME pseudo column.

The table schema contains 2 columns:

  • x — an integer, with description "An optional INTEGER field"

  • y — a STRUCT containing 2 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, 2020 at 00:00:00 UTC
  • Partition expiration time — 1 day
  • Description — a table that expires in 2020
  • Label — org_unit = development

To create a new table using DDL:

Console

  1. Open the BigQuery web UI in the GCP Console.
    Go to the BigQuery web UI

  2. Click Compose new query.

    Compose new query

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

     #standardSQL
 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 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. (Optional) To change the data processing location, click More, then Query settings. Under Processing location, click Auto-select and choose your data's location. Finally, click Save to update the query settings.

  5. Click Run. When the query completes, the table appears in the resources pane.

Classic 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 OPTIONS(description="An optional INTEGER field"),
   y STRUCT<
     a ARRAY<STRING> OPTIONS(description="A repeated STRING field"),
     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 OPTIONS(description="An optional INTEGER field"),
  y STRUCT<
    a ARRAY<STRING> OPTIONS(description="A repeated STRING field"),
    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
  • 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

Creating a new table from an existing table

The CREATE TABLE ... AS SELECT DDL statement creates a table from a query. 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 top_words 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.rainy_days`.

The table schema contains 2 columns:

  • corpus — name of a Shakespeare corpus

  • top_words — an ARRAY of STRUCTs containing 2 fields: word (a STRING) and word_count (an INT64 with the word count)

The table option list specifies the:

  • Description — Top ten words per Shakespeare corpus

To create a new table using DDL:

Console

  1. Open the BigQuery web UI in the GCP Console.
    Go to the BigQuery web UI

  2. Click Compose new query.

    Compose new query

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

     #standardSQL
 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;

  4. (Optional) To change the data processing location, click More, then Query settings. Under Processing location, click Auto-select and choose your data's location. Finally, click Save to update the query settings.

  5. Click Run. When the query completes, the table appears in the resources pane.

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

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

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

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 table option list 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:

Console

  1. Open the BigQuery web UI in the GCP Console.
    Go to the BigQuery web UI

  2. Click Compose new query.

    Compose new query

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

     #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. (Optional) To change the data processing location, click More, then Query settings. Under Processing location, click Auto-select and choose your data's location. Finally, click Save to update the query settings.

  5. Click Run. When the query completes, the table appears in the resources pane.

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

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 table option list 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:

Console

  1. Open the BigQuery web UI in the GCP Console.
    Go to the BigQuery web UI

  2. Click Compose new query.

    Compose new query

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

     #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. (Optional) To change the data processing location, click More, then Query settings. Under Processing location, click Auto-select and choose your data's location. Finally, click Save to update the query settings.

  5. Click Run. When the query completes, the table appears in the resources pane.

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

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:

Console

  1. Open the BigQuery web UI in the GCP Console.
    Go to the BigQuery web UI

  2. Click Compose new query.

    Compose new query

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

     #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. (Optional) To change the data processing location, click More, then Query settings. Under Processing location, click Auto-select and choose your data's location. Finally, click Save to update the query settings.

  5. Click Run. When the query completes, the table appears in the resources pane.

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

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 table option list specifies the:

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

To create a new table using DDL:

Console

  1. Open the BigQuery web UI in the GCP Console.
    Go to the BigQuery web UI

  2. Click Compose new query.

    Compose new query

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

     #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. (Optional) To change the data processing location, click More, then Query settings. Under Processing location, click Auto-select and choose your data's location. Finally, click Save to update the query settings.

  5. Click Run. When the query completes, the table appears in the resources pane.

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

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

  • 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

To create a new table using DDL:

Console

  1. Open the BigQuery web UI in the GCP Console.
    Go to the BigQuery web UI

  2. Click Compose new query.

    Compose new query

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

     #standardSQL
 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 may 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

  4. (Optional) To change the data processing location, click More, then Query settings. Under Processing location, click Auto-select and choose your data's location. Finally, click Save to update the query settings.

  5. Click Run. When the query completes, the table appears in the resources pane.

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

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

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

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.

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

To create a clustered table using a DDL statement:

Console

  1. Open the BigQuery web UI in the GCP Console.
    Go to the BigQuery web UI

  2. Click Compose new query.

    Compose new query

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

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

  4. (Optional) To change the data processing location, click More, then Query settings. Under Processing location, click Auto-select and choose your data's location. Finally, click Save to update the query settings.

  5. Click Run. When the query completes, the table appears in the resources pane.

Classic UI

  1. Go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click Compose query.

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

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

  4. Click Show Options.

  5. (Optional) For Processing Location, click Unspecified and choose your data's location.

  6. 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.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"
)'

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

Example 2

The following example creates a clustered table named myclusteredtable in mydataset. The table is an ingestion-time partitioned 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.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"

To create a clustered table using a DDL statement:

Console

  1. Open the BigQuery web UI in the GCP Console.
    Go to the BigQuery web UI

  2. Click Compose new query.

    Compose new query

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

     #standardSQL
 CREATE TABLE mydataset.myclusteredtable
 (
   timestamp TIMESTAMP,
   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"
 )

  4. (Optional) To change the data processing location, click More, then Query settings. Under Processing location, click Auto-select and choose your data's location. Finally, click Save to update the query settings.

  5. Click Run. When the query completes, the table appears in the resources pane.

Classic UI

  1. Go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click Compose query.

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

     #standardSQL
 CREATE TABLE mydataset.myclusteredtable
 (
   timestamp TIMESTAMP,
   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"
 )

  4. Click Show Options.

  5. (Optional) For Processing Location, click Unspecified and choose your data's location.

  6. 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.myclusteredtable
(
  timestamp TIMESTAMP,
  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"
)'

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

Creating a clustered table from the result of a query

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.

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

To create a clustered table using a DDL statement:

Console

  1. Open the BigQuery web UI in the GCP Console.
    Go to the BigQuery web UI

  2. Click Compose new query.

    Compose new query

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

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

  4. (Optional) To change the data processing location, click More, then Query settings. Under Processing location, click Auto-select and choose your data's location. Finally, click Save to update the query settings.

  5. Click Run. When the query completes, the table appears in the resources pane.

Classic UI

  1. Go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click Compose query.

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

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

  4. Click Show Options.

  5. (Optional) For Processing Location, click Unspecified and choose your data's location.

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

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

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(view_option_list)]
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

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

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

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

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 view option list 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:

Console

  1. Open the BigQuery web UI in the GCP Console.
    Go to the BigQuery web UI

  2. Click Compose new query.

    Compose new query

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

     #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. (Optional) To change the data processing location, click More, then Query settings. Under Processing location, click Auto-select and choose your data's location. Finally, click Save to update the query settings.

  5. Click Run. When the query completes, the table appears in the resources pane.

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

google-cloud-examples/src/test/java/com/google/cloud/examples/bigquery/snippets/ITCloudSnippets.java 
// import com.google.cloud.bigquery.*;
// String projectId = "my-project";
// String datasetId = "my_dataset";
// String tableId = "new_view";
// BigQuery bigquery = BigQueryOptions.getDefaultInstance().toBuilder()
//     .setProjectId(projectId)
//     .build().getService();

String sql =
    String.format(
        "CREATE VIEW `%s.%s.%s`\n"
            + "OPTIONS(\n"
            + "  expiration_timestamp=TIMESTAMP_ADD(\n"
            + "    CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),\n"
            + "  friendly_name=\"new_view\",\n"
            + "  description=\"a view that expires in 2 days\",\n"
            + "  labels=[(\"org_unit\", \"development\")]\n"
            + ")\n"
            + "AS SELECT name, state, year, number\n"
            + "  FROM `bigquery-public-data.usa_names.usa_1910_current`\n"
            + "  WHERE state LIKE 'W%%';\n",
        projectId, datasetId, tableId);

// Make an API request to run the query job.
Job job = bigquery.create(JobInfo.of(QueryJobConfiguration.newBuilder(sql).build()));

// Wait for the query to finish.
job = job.waitFor();

QueryJobConfiguration jobConfig = (QueryJobConfiguration) job.getConfiguration();
System.out.printf(
    "Created new view \"%s.%s.%s\".\n",
    jobConfig.getDestinationTable().getProject(),
    jobConfig.getDestinationTable().getDataset(),
    jobConfig.getDestinationTable().getTable());

PYTHON

Call the Client.query() method to start a query job. Call the QueryJob.result() method to wait for the DDL query to finish.

bigquery/docs/snippets.py 
# from google.cloud import bigquery
# project = 'my-project'
# dataset_id = 'my_dataset'
# table_id = 'new_view'
# client = bigquery.Client(project=project)

sql = """
CREATE VIEW `{}.{}.{}`
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%'
""".format(
    project, dataset_id, table_id
)

job = client.query(sql)  # API request.
job.result()  # Waits for the query to finish.

print(
    'Created new view "{}.{}.{}".'.format(
        job.destination.project,
        job.destination.dataset_id,
        job.destination.table_id,
    )
)

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 view option list 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:

Console

  1. Open the BigQuery web UI in the GCP Console.
    Go to the BigQuery web UI

  2. Click Compose new query.

    Compose new query

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

     #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. (Optional) To change the data processing location, click More, then Query settings. Under Processing location, click Auto-select and choose your data's location. Finally, click Save to update the query settings.

  5. Click Run. When the query completes, the table appears in the resources pane.

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

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 view option list 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:

Console

  1. Open the BigQuery web UI in the GCP Console.
    Go to the BigQuery web UI

  2. Click Compose new query.

    Compose new query

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

     #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. (Optional) To change the data processing location, click More, then Query settings. Under Processing location, click Auto-select and choose your data's location. Finally, click Save to update the query settings.

  5. Click Run. When the query completes, the table appears in the resources pane.

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

CREATE FUNCTION statement

Google BigQuery supports user-defined functions (UDFs). A UDF enables you to create a function using a SQL expression or JavaScript. These functions accept columns of input and perform actions, returning the result of those actions as a value.

For information on user-defined functions in legacy SQL, see User-defined functions in legacy SQL.

UDFs can be temporary or persistent. A temporary UDF can only be used for the current query or command-line session.

General UDF Syntax

User-defined functions in Google BigQuery use the following general syntax:

CREATE { [TEMPORARY | TEMP] FUNCTION | OR REPLACE [TEMPORARY | TEMP] FUNCTION |
    [TEMPORARY | TEMP] FUNCTION IF NOT EXISTS }
    function_name ([named_parameter[, ...]])
  [RETURNS data_type]
  { [LANGUAGE language AS """body"""] | [AS (function_definition)] }
[OPTIONS (library = library_array)];

named_parameter:
  param_name param_type

This syntax consists of the following components:

  • CREATE { [TEMPORARY | TEMP] FUNCTION | OR REPLACE [TEMPORARY | TEMP] FUNCTION | [TEMPORARY | TEMP] FUNCTION IF NOT EXISTS }. Creates a new function. A function can contain zero or more named_parameters. To make the function temporary, use the TEMP or TEMPORARY keyword. To replace any existing function with the same name, use the OR REPLACE keyword. To treat the query as successful and take no action if a function with the same name already exists, use the IF NOT EXISTS clause.
  • named_parameter. Consists of a comma-separated param_name and param_type pair. The value of param_type is a Google BigQuery data type. For a SQL UDF, the value of param_type may also be ANY TYPE.
  • [RETURNS data_type]. Specifies the data type that the function returns. If the function is defined in SQL, then the RETURNS clause is optional and Google BigQuery infers the result type of the function from the SQL function body. If the function is defined in JavaScript, then the RETURNS clause is required. See Supported UDF data types for more information about allowed values for data_type.
  • [LANGUAGE language AS """body"""]. Specifies the JavaScript language for the function and the code that defines the function.
  • AS (function_definition). Specifies the SQL code that defines the function. function_definition is a SQL expression.
  • [OPTIONS (library = library_array)]. For a JavaScript UDF, specifies an array of JavaScript libraries to include in the function definition.

SQL UDF structure

Create SQL UDFs using the following syntax:

CREATE { [TEMPORARY | TEMP] FUNCTION | OR REPLACE [TEMPORARY | TEMP] FUNCTION |
    [TEMPORARY | TEMP] FUNCTION IF NOT EXISTS } function_name ([named_parameter[, ...]])
  [RETURNS data_type]
  AS (sql_expression)

named_parameter:
  param_name param_type

Templated SQL UDF parameters

A templated parameter can match more than one argument type at function call time. If a function signature includes a templated parameter, Google BigQuery allows function calls to pass one of several argument types to the function.

SQL user-defined function signatures can contain the following templated param_type value:

  • ANY TYPE. The function will accept an input of any type for this argument. If more than one parameter has the type ANY TYPE, Google BigQuery does not enforce any relationship between these arguments at the time of function creation. However, passing the function arguments of types that are incompatible with the function definition will result in an error at call time.

SQL UDF examples

The following example creates a UDF.

CREATE FUNCTION mydataset.multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS """
  return x*y;
""";

WITH numbers AS
  (SELECT 1 AS x, 5 as y
  UNION ALL
  SELECT 2 AS x, 10 as y
  UNION ALL
  SELECT 3 as x, 15 as y)
SELECT x, y, mydataset.multiplyInputs(x, y) as product
FROM numbers;

The example above produces the following output:

+-----+-----+--------------+
| x   | y   | product      |
+-----+-----+--------------+
| 1   | 5   | 5            |
| 2   | 10  | 20           |
| 3   | 15  | 45           |
+-----+-----+--------------+

You can make this function temporary by using the TEMP or TEMPORARY keyword.

The following example shows a UDF that employs a SQL function.

CREATE TEMP FUNCTION addFourAndDivide(x INT64, y INT64) AS ((x + 4) / y);

WITH numbers AS
  (SELECT 1 as val
  UNION ALL
  SELECT 3 as val
  UNION ALL
  SELECT 4 as val
  UNION ALL
  SELECT 5 as val)
SELECT val, addFourAndDivide(val, 2) AS result
FROM numbers;

The above example produces the following output:

+-----+--------+
| val | result |
+-----+--------+
| 1   | 2.5    |
| 3   | 3.5    |
| 4   | 4      |
| 5   | 4.5    |
+-----+--------+

The following example shows a SQL UDF that uses a templated parameter. The resulting function accepts arguments of various types.

CREATE TEMP FUNCTION addFourAndDivideAny(x ANY TYPE, y ANY TYPE) AS (
  (x + 4) / y
);
SELECT addFourAndDivideAny(3, 4) AS integer_output,
       addFourAndDivideAny(1.59, 3.14) AS floating_point_output;

The above example returns the following output:

+----------------+-----------------------+
| integer_output | floating_point_output |
+----------------+-----------------------+
| 1.75           | 1.7802547770700636    |
+----------------+-----------------------+

The following example shows a SQL UDF that uses a templated parameter to return the last element of an array of any type.

CREATE TEMP FUNCTION lastArrayElement(arr ANY TYPE) AS (
  arr[ORDINAL(ARRAY_LENGTH(arr))]
);
SELECT
  names[OFFSET(0)] AS first_name,
  lastArrayElement(names) AS last_name
FROM (
  SELECT ['Fred', 'McFeely', 'Rogers'] AS names UNION ALL
  SELECT ['Marie', 'Skłodowska', 'Curie']
);

The above example returns the following output:

+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Fred       | Rogers    |
| Marie      | Curie     |
+------------+-----------+

JavaScript UDF structure

Create JavaScript UDFs using the following structure.

CREATE { [TEMPORARY | TEMP] FUNCTION | OR REPLACE [TEMPORARY | TEMP] FUNCTION |
    [TEMPORARY | TEMP] FUNCTION IF NOT EXISTS } function_name ([named_parameter[, ...]])
  [RETURNS data_type]
  LANGUAGE js
  AS javascript_code

JavaScript UDF examples

CREATE TEMP FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS """
  return x*y;
""";

WITH numbers AS
  (SELECT 1 AS x, 5 as y
  UNION ALL
  SELECT 2 AS x, 10 as y
  UNION ALL
  SELECT 3 as x, 15 as y)
SELECT x, y, multiplyInputs(x, y) as product
FROM numbers;

The above example returns the following output:

+-----+-----+--------------+
| x   | y   | product      |
+-----+-----+--------------+
| 1   | 5   | 5            |
| 2   | 10  | 20           |
| 3   | 15  | 45           |
+-----+-----+--------------+

You can create multiple UDFs before a query. For example:

CREATE TEMP FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS """
  return x*y;
""";

CREATE TEMP FUNCTION divideByTwo(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS """
  return x / 2;
""";

WITH numbers AS
  (SELECT 1 AS x, 5 as y
  UNION ALL
  SELECT 2 AS x, 10 as y
  UNION ALL
  SELECT 3 as x, 15 as y)
SELECT x,
  y,
  multiplyInputs(x, y) as product,
  divideByTwo(x) as half_x,
  divideByTwo(y) as half_y
FROM numbers;

The above example returns the following output:

+-----+-----+--------------+--------+--------+
| x   | y   | product      | half_x | half_y |
+-----+-----+--------------+--------+--------+
| 1   | 5   | 5            | 0.5    | 2.5    |
| 2   | 10  | 20           | 1      | 5      |
| 3   | 15  | 45           | 1.5    | 7.5    |
+-----+-----+--------------+--------+--------+

You can pass the result of a UDF as input to another UDF. For example:

CREATE TEMP FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS """
  return x*y;
""";

CREATE TEMP FUNCTION divideByTwo(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS """
  return x/2;
""";

WITH numbers AS
  (SELECT 1 AS x, 5 as y
  UNION ALL
  SELECT 2 AS x, 10 as y
  UNION ALL
  SELECT 3 as x, 15 as y)
SELECT x,
  y,
  multiplyInputs(divideByTwo(x), divideByTwo(y)) as half_product
FROM numbers;

The above example returns the following output:

+-----+-----+--------------+
| x   | y   | half_product |
+-----+-----+--------------+
| 1   | 5   | 1.25         |
| 2   | 10  | 5            |
| 3   | 15  | 11.25        |
+-----+-----+--------------+

The following example sums the values of all fields named "foo" in the given JSON string.

CREATE TEMP FUNCTION SumFieldsNamedFoo(json_row STRING)
  RETURNS FLOAT64
  LANGUAGE js
  AS """
  function SumFoo(obj) {
    var sum = 0;
    for (var field in obj) {
      if (obj.hasOwnProperty(field) &amp;&amp; obj[field] != null) {
        if (typeof obj[field] == "object") {
          sum += SumFoo(obj[field]);
        } else if (field == "foo") {
          sum += obj[field];
        }
      }
    }
    return sum;
  }
  var row = JSON.parse(json_row);
  return SumFoo(row);
  """;

WITH Input AS (
  SELECT STRUCT(1 AS foo, 2 AS bar, STRUCT('foo' AS x, 3.14 AS foo) AS baz) AS s, 10 AS foo UNION ALL
  SELECT NULL, 4 AS foo UNION ALL
  SELECT STRUCT(NULL, 2 AS bar, STRUCT('fizz' AS x, 1.59 AS foo) AS baz) AS s, NULL AS foo
)
SELECT
  TO_JSON_STRING(t) AS json_row,
  SumFieldsNamedFoo(TO_JSON_STRING(t)) AS foo_sum
FROM Input AS t;

The above example returns the following output:

+---------------------------------------------------------------------+---------+
| json_row                                                            | foo_sum |
+---------------------------------------------------------------------+---------+
| {"s":{"foo":1,"bar":2,"baz":{"x":"foo","foo":3.14}},"foo":10}       | 14.14   |
| {"s":null,"foo":4}                                                  | 4       |
| {"s":{"foo":null,"bar":2,"baz":{"x":"fizz","foo":1.59}},"foo":null} | 1.59    |
+---------------------------------------------------------------------+---------+

Supported JavaScript UDF data types

For JavaScript UDFs, BigQuery supports the following data types:

  • ARRAY
  • BOOL
  • BYTES
  • DATE
  • FLOAT64
  • NUMERIC
  • STRING
  • STRUCT
  • TIMESTAMP

SQL type encodings in JavaScript

Some SQL types have a direct mapping to JavaScript types, but others do not.

Because JavaScript does not support a 64-bit integer type, INT64 is unsupported as an input type for JavaScript UDFs. Instead, use FLOAT64 to represent integer values as a number, or STRING to represent integer values as a string.

BigQuery does support INT64 as a return type in JavaScript UDFs. In this case, the JavaScript function body can return either a JavaScript Number or a String. BigQuery then converts either of these types to INT64.

BigQuery represents types in the following manner:

BigQuery Data Type JavaScript Data Type
ARRAY ARRAY
BOOL BOOLEAN
BYTES base64-encoded STRING
FLOAT64 NUMBER
NUMERIC If a NUMERIC value can be represented exactly as an IEEE 754 floating-point value and has no fractional part, it is encoded as a Number. These values are in the range [-253, 253]. Otherwise, it is encoded as a String.
STRING STRING
STRUCT OBJECT where each STRUCT field is a named field
TIMESTAMP DATE with a microsecond field containing the microsecond fraction of the timestamp
DATE DATE

Quoting rules

You must enclose JavaScript code in quotes. For simple, one line code snippets, you can use a standard quoted string:

CREATE TEMP FUNCTION plusOne(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS "return x+1;";

SELECT val, plusOne(val) AS result
FROM UNNEST([1, 2, 3, 4, 5]) AS val;

The above example returns the following output:

+-----------+-----------+
| val       | result    |
+-----------+-----------+
| 1         | 2         |
| 2         | 3         |
| 3         | 4         |
| 4         | 5         |
| 5         | 6         |
+-----------+-----------+

In cases where the snippet contains quotes, or consists of multiple lines, use triple-quoted blocks:

CREATE TEMP FUNCTION customGreeting(a STRING)
RETURNS STRING
LANGUAGE js AS """
  var d = new Date();
  if (d.getHours() &lt; 12) {
    return 'Good Morning, ' + a + '!';
  } else {
    return 'Good Evening, ' + a + '!';
  }
  """;
SELECT customGreeting(names) as everyone
FROM UNNEST(["Hannah", "Max", "Jakob"]) AS names;

The above example produces the following output:

+-----------------------+
| everyone              |
+-----------------------+
| Good Morning, Hannah! |
| Good Morning, Max!    |
| Good Morning, Jakob!  |
+-----------------------+

Including JavaScript libraries

You can extend your JavaScript UDFs using the OPTIONS section. This section allows you to specify JavaScript code libraries for the UDF.

CREATE TEMP FUNCTION myFunc(a FLOAT64, b STRING)
  RETURNS STRING
  LANGUAGE js AS
  """
      // Assumes 'doInterestingStuff' is defined in one of the library files.
      return doInterestingStuff(a, b);
  """
OPTIONS (
  library="gs://my-bucket/path/to/lib1.js",
  library=["gs://my-bucket/path/to/lib2.js", "gs://my-bucket/path/to/lib3.js"]
);

SELECT myFunc(3.14, 'foo');</code></pre>

In the preceding example, code in lib1.js, lib2.js, and lib3.js is available to any code in the javascript_code section of the UDF. Notice that you can specify library files using single-element or array syntax.

UDFs and the Web UI

You can use the BigQuery web UI to run queries using one or more UDFs.

Running a query with a UDF

  1. Open the BigQuery web UI in the GCP Console.
    Go to the BigQuery web UI

  2. Click Compose new query.

    Compose new query

  3. Enter the UDF statement into the Query editor text area. For example:

      CREATE TEMPORARY FUNCTION timesTwo(x FLOAT64)
  RETURNS FLOAT64
    LANGUAGE js AS """
    return x*2;
  """;

  4. Below the UDF statement, type your query. For example:

      SELECT timesTwo(numbers) as doubles
  FROM UNNEST([1, 2, 3, 4, 5]) AS numbers;

  5. (Optional) To change the data processing location, click More, then Query settings. Under Processing location, click Auto-select and choose your data's location. Finally, click Save to update the query settings.

  6. Click Run.

UDFs and the bq command-line tool

You can use the bq Command-Line Tool from the Google Cloud SDK to run a query containing one or more UDFs.

Use the following syntax to run a query with a UDF:

bq query <statement_with_udf_and_query>

Best practices for JavaScript UDFs

Pre-filter your input

If your input can be easily filtered down before being passed to a JavaScript UDF, your query will likely be faster and cheaper.

Avoid persistent mutable state

Do not store or access mutable state across JavaScript UDF calls.

Use memory efficiently

The JavaScript processing environment has limited memory available per query. JavaScript UDF queries that accumulate too much local state may fail due to memory exhaustion.

Limitations

The following limits apply to temporary and persistent user-defined functions:

  • The DOM objects Window, Document and Node, and functions that require them, are unsupported.
  • JavaScript functions that rely on native code are unsupported.
  • Because of their non-deterministic nature, queries that invoke JavaScript user-defined functions cannot use cached results.
  • You cannot reference a table in a UDF.
  • Views cannot invoke UDFs.
  • The amount of data that your JavaScript UDF outputs when processing a single row — approximately 5 MB or less.
  • Concurrent rate limit for legacy SQL queries that contain user-defined functions (UDFs) — 6 concurrent queries.

    • The concurrent rate limit for legacy SQL queries that contain UDFs includes both interactive and batch queries. Interactive queries that contain UDFs also count toward the concurrent rate limit for interactive queries. This limit does not apply to standard SQL queries.

  • Maximum number of JavaScript UDF resources, such as inline code blobs or external files, in a query job — 50
  • Maximum size of each inline code blob — 32 KB.
  • Maximum size of each external code resource — 1 MB.

The following limits apply to persistent user-defined functions.
  • Maximum length of a function name — 256 characters
  • Maximum number of arguments — 256
  • Maximum length of a argument name — 128 characters
  • Maximum depth of a user-defined function reference chain — 16
  • Maximum depth of argument or output of type STRUCT — 15
  • Maximum number of fields in argument or output of type STRUCT per UDF — 1024
  • Maximum number of unique UDF plus table references per query — 1000 After full expansion, each UDF can reference up to 1000 combined unique tables and UDFs.
  • Maximum number of JavaScript libraries in CREATE FUNCTION statement — 50
  • Max length of included JavaScript library paths — 5000 characters
  • Maximum update rate per UDF — 5 per 10 seconds After function creation, you can update each function up to 5 times per 10 seconds.
  • Each inline code blob is limited to a maximum size of 32 KB.
  • Each JavaScript code resource is limited to a maximum size of 1 MB.
  • Bitwise operations in JavaScript handle only the most significant 32 bits.
  • Each project can only contain one persistent UDF with the same function_name. However, you can create a UDF whose function_name is the same as the name of a table in the current project.
  • When referencing a persistent UDF from another persistent UDF or from a logical view, you must qualify the reference with the project name. For example:
    CREATE FUNCTION mydataset.referringFunction() AS (myproject.mydataset.referencedFunction());

The following limits apply to temporary user-defined functions.

  • When creating a temporary UDF, function_name cannot contain periods.
  • Queries that reference temporary UDFs cannot be saved as views.

ALTER TABLE SET OPTIONS statement

To set the options on a table in BigQuery, use the ALTER TABLE SET OPTIONS DDL statement.

Syntax

{ALTER TABLE | ALTER TABLE IF EXISTS}
table_name
SET OPTIONS(table_set_options_list)

Where:

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

  • ALTER TABLE — alters the options on an existing table.
  • ALTER TABLE IF EXISTS — alters the options on a table only if it exists.

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

table_set_options_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: 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 set if the table is partitioned.
require_partition_filter

BOOL

Example: require_partition_filter=true

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

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.

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

Setting the VALUE replaces the existing value of that option for the table, if there was one. Setting the VALUE to NULL clears the table's value for that option.

Examples

Setting the expiration timestamp and description on a table

The following example sets the expiration timestamp on a table to seven days from the execution time of the ALTER TABLE statement, and sets the description as well:

Console

  1. Open the BigQuery web UI in the GCP Console.
    Go to the BigQuery web UI

  2. Click Compose new query.

    Compose new query

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

     #standardSQL
 ALTER TABLE mydataset.mytable
 SET OPTIONS (
   expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 7 DAY),
   description="Table that expires seven days from now"
 )

  4. (Optional) To change the data processing location, click More, then Query settings. Under Processing location, click Auto-select and choose your data's location. Finally, click Save to update the query settings.

  5. Click Run. When the query completes, the table appears in the resources pane.

Classic 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
 ALTER TABLE mydataset.mytable
 SET OPTIONS (
   expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 7 DAY),
   description="Table that expires seven days from now"
 )

  4. Click Run query. When the query completes, the property has been set on the table.

Command-line

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

bq query --use_legacy_sql=false '
ALTER TABLE mydataset.mytable
SET OPTIONS (
  expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 7 DAY),
  description="Table that expires seven days from now"
)'

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

Setting the require partition filter attribute on a partitioned table

The following example sets the timePartitioning.requirePartitionFilter attribute on a partitioned table. When set to true, queries that reference this table must use a filter on the partitioning column, or else BigQuery will return an error. Setting this option to true can help prevent mistakes in querying more data than intended:

Console

  1. Open the BigQuery web UI in the GCP Console.
    Go to the BigQuery web UI

  2. Click Compose new query.

    Compose new query

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

     #standardSQL
 ALTER TABLE mydataset.mypartitionedtable
 SET OPTIONS (require_partition_filter=true)

  4. (Optional) To change the data processing location, click More, then Query settings. Under Processing location, click Auto-select and choose your data's location. Finally, click Save to update the query settings.

  5. Click Run. When the query completes, the table appears in the resources pane.

Classic 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
 ALTER TABLE mydataset.mypartitionedtable
 SET OPTIONS (require_partition_filter=true)

  4. Click Run query. When the query completes, the property has been set on the table.

Command-line

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

bq query --use_legacy_sql=false '
ALTER TABLE mydataset.mypartitionedtable
SET OPTIONS (require_partition_filter=true)'

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

Clearing the expiration timestamp on a table

The following example clears the expiration timestamp on a table so that it will not expire:

Console

  1. Open the BigQuery web UI in the GCP Console.
    Go to the BigQuery web UI

  2. Click Compose new query.

    Compose new query

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

     #standardSQL
 ALTER TABLE mydataset.mytable
 SET OPTIONS (expiration_timestamp=NULL)

  4. (Optional) To change the data processing location, click More, then Query settings. Under Processing location, click Auto-select and choose your data's location. Finally, click Save to update the query settings.

  5. Click Run. When the query completes, the table appears in the resources pane.

Classic 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
 ALTER TABLE mydataset.mytable
 SET OPTIONS (expiration_timestamp=NULL)

  4. Click Run query. When the query completes, the table no longer has an expiration time.

Command-line

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

bq query --use_legacy_sql=false '
ALTER TABLE mydataset.mytable
SET OPTIONS (expiration_timestamp=NULL)'

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

ALTER VIEW SET OPTIONS statement

To set the options on a view in BigQuery, use the ALTER VIEW SET OPTIONS DDL statement.

Syntax

{ALTER VIEW | ALTER VIEW IF EXISTS}
view_name
SET OPTIONS(view_set_options_list)

Where:

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

  • ALTER VIEW — alters the options on an existing view.
  • ALTER VIEW IF EXISTS — alters the options on a view only if it exists.

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

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

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

Setting the VALUE replaces the existing value of that option for the view, if there was one. Setting the VALUE to NULL clears the view's value for that option.

Examples

Setting the expiration timestamp and description on a view

The following example sets the expiration timestamp on a view to seven days from the execution time of the ALTER VIEW statement, and sets the description as well:

Console

  1. Open the BigQuery web UI in the GCP Console.
    Go to the BigQuery web UI

  2. Click Compose new query.

    Compose new query

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

     #standardSQL
 ALTER VIEW mydataset.myview
 SET OPTIONS (
   expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 7 DAY),
   description="View that expires seven days from now"
 )

  4. (Optional) To change the data processing location, click More, then Query settings. Under Processing location, click Auto-select and choose your data's location. Finally, click Save to update the query settings.

  5. Click Run. When the query completes, the table appears in the resources pane.

Classic 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
 ALTER VIEW mydataset.myview
 SET OPTIONS (
   expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 7 DAY),
   description="View that expires seven days from now"
 )

  4. Click Run query. When the query completes, the property has been set on the view.

Command-line

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

bq query --use_legacy_sql=false '
ALTER VIEW mydataset.myview
SET OPTIONS (
  expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 7 DAY),
  description="View that expires seven days from now"
)'

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

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:

Console

  1. Open the BigQuery web UI in the GCP Console.
    Go to the BigQuery web UI

  2. Click Compose new query.

    Compose new query

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

     #standardSQL
 DROP TABLE mydataset.mytable

  4. (Optional) To change the data processing location, click More, then Query settings. Under Processing location, click Auto-select and choose your data's location. Finally, click Save to update the query settings.

  5. Click Run. When the query completes, the table appears in the resources pane.

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

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:

Console

  1. Open the BigQuery web UI in the GCP Console.
    Go to the BigQuery web UI

  2. Click Compose new query.

    Compose new query

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

     #standardSQL
 DROP TABLE IF EXISTS mydataset.mytable

  4. (Optional) To change the data processing location, click More, then Query settings. Under Processing location, click Auto-select and choose your data's location. Finally, click Save to update the query settings.

  5. Click Run. When the query completes, the table appears in the resources pane.

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

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:

Console

  1. Open the BigQuery web UI in the GCP Console.
    Go to the BigQuery web UI

  2. Click Compose new query.

    Compose new query

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

     #standardSQL
 DROP VIEW mydataset.myview

  4. (Optional) To change the data processing location, click More, then Query settings. Under Processing location, click Auto-select and choose your data's location. Finally, click Save to update the query settings.

  5. Click Run. When the query completes, the table appears in the resources pane.

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

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:

Console

  1. Open the BigQuery web UI in the GCP Console.
    Go to the BigQuery web UI

  2. Click Compose new query.

    Compose new query

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

     #standardSQL
 DROP VIEW IF EXISTS mydataset.myview

  4. (Optional) To change the data processing location, click More, then Query settings. Under Processing location, click Auto-select and choose your data's location. Finally, click Save to update the query settings.

  5. Click Run. When the query completes, the table appears in the resources pane.

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

DROP FUNCTION statement

Syntax

DROP FUNCTION [ IF EXISTS ] [`project_name`.]dataset_name.function_name

Description

Deletes function function_name in dataset dataset_name.

Optional Clauses

IF EXISTS Delete the function only if the function exists in the specified dataset.

project_name. Specifies the project that contains the function. If the function is not located in the current project, project_name must be present.

Examples

The following example statement deletes the function parseJsonAsStruct contained in the dataset mydataset.

DROP FUNCTION mydataset.parseJsonAsStruct;

The following example statement deletes the function parseJsonAsStruct from the dataset sample_dataset in the project other_project.

DROP FUNCTION `other_project`.sample_dataset.parseJsonAsStruct;
Was this page helpful? Let us know how we did:

Send feedback about...

This page
Documentation feedback
BigQuery
Product feedback
Need help? Visit our support page.