Data definition language (DDL) statements in GoogleSQL

Data definition language (DDL) statements let you create and modify BigQuery resources using GoogleSQL query syntax. You can use DDL commands to create, alter, and delete resources, such as the following:

• Datasets
• Tables
• Table schemas
• Table clones
• Table snapshots
• Views
• User-defined functions (UDFs)
• Indexes
• Capacity commitments and reservations
• Row-level access policies

Required permissions

To create a job that runs a DDL statement, you must have the bigquery.jobs.create permission for the project where you are running the job. Each DDL statement also requires specific permissions on the affected resources, which are documented under each statement.

IAM roles

The predefined IAM roles bigquery.user, bigquery.jobUser, and bigquery.admin include the required bigquery.jobs.create permission.

For more information about IAM roles in BigQuery, see Predefined roles and permissions or the IAM permissions reference.

Primary keys and foreign keys

In typical relational databases, primary keys and foreign keys are used to ensure data integrity. A primary key value is unique for each row and is not NULL. Each foreign key value in a row must be present in the primary key column of the referenced table, or be NULL. Primary and foreign key relationships are created and managed through DDL statements.

Limitations

Primary keys and foreign keys are subject to the following limitations:

• BigQuery only supports unenforced primary keys. Ensure that your values match their respective constraints, otherwise you might get incorrect results. Specifically:
  • Primary keys must have unique values.
  • Primary keys cannot exceed 16 columns.
  • Foreign keys must have values that are present in the referenced table column. These values can be NULL.
  • Primary keys and foreign keys must be of one of the following types: BIGNUMERIC, BOOLEAN, DATE, DATETIME, INT64, NUMERIC, STRING, or TIMESTAMP.
• Primary keys and foreign keys can only be set on top-level columns.
• The primary keys cannot be named.
• Tables with primary key constraints cannot be renamed.
• A table can contain up to 64 foreign keys defined in the table.
• A foreign key cannot refer to a column in the same table.
• Fields that are part of primary key constraints or foreign key constraints cannot be renamed, or have their type changed.
• If you copy, clone, restore, or snapshot a table without the -a or --append_table option, the source table constraints are copied and overwritten to the destination table. If you use the -a or --append_table option, only the source table records are added to the destination table without the table constraints.

Run DDL statements

You can run DDL statements by using the Google Cloud console, by using the bq command-line tool, by calling the jobs.query REST API, or programmatically using the BigQuery API client libraries.

bqquery--use_legacy_sql=false\'CREATE TABLE mydataset.newtable ( x INT64 )'

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

// Import the Google Cloud client library and create a clientconst{BigQuery}=require('@google-cloud/bigquery');constbigquery=newBigQuery();asyncfunctionddlCreateView(){// Creates a view via a DDL query/** * TODO(developer): Uncomment the following lines before running the sample. */// const projectId = "my_project"// const datasetId = "my_dataset"// const tableId = "my_new_view"constquery=` CREATE VIEW \`${projectId}.${datasetId}.${tableId}\` OPTIONS( expiration_timestamp=TIMESTAMP_ADD( CURRENT_TIMESTAMP(), INTERVAL 48 HOUR), friendly_name="new_view", description="a view that expires in 2 days", labels=[("org_unit", "development")] ) AS SELECT name, state, year, number FROM \`bigquery-public-data.usa_names.usa_1910_current\` WHERE state LIKE 'W%'`;// For all options, see https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/queryconstoptions={query:query,};// Run the query as a jobconst[job]=awaitbigquery.createQueryJob(options);job.on('complete',metadata=>{console.log(`Created new view ${tableId} via job ${metadata.id}`);});}

# 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,))

On-demand query size calculation

If you use on-demand billing, BigQuery charges for data definition language (DDL) queries based on the number of bytes processed by the query.

For more information about cost estimation, see Estimate and control costs.

CREATE SCHEMA statement

Creates a new dataset.

Syntax

CREATESCHEMA[IFNOTEXISTS][project_name.]dataset_name[DEFAULTCOLLATEcollate_specification][OPTIONS(schema_option_list)]

Arguments

• IF NOT EXISTS: If any dataset exists with the same name, the CREATE statement has no effect.

• DEFAULT COLLATE collate_specification: When a new table is created in the dataset, the table inherits a default collation specification unless a collation specification is explicitly specified for a table or a column.

  If you remove or change this collation specification later with the ALTER SCHEMA statement, this will not change existing collation specifications in this dataset. If you want to update an existing collation specification in a dataset, you must alter the column that contains the specification.

• project_name: The name of the project where you are creating the dataset. Defaults to the project that runs this DDL statement.

• dataset_name: The name of the dataset to create.

• schema_option_list: A list of options for creating the dataset.

Details

The dataset is created in the location that you specify in the query settings. For more information, see Specifying your location.

For more information about creating a dataset, see Creating datasets. For information about quotas, see Dataset limits.

schema_option_list

The following options are supported:

Required permissions

This statement requires the following IAM permissions:

Examples

Creating a new dataset

The following example creates a dataset with a default table expiration and a set of labels.

CREATESCHEMAmydatasetOPTIONS(location="us",default_table_expiration_days=3.75,labels=[("label1","value1"),("label2","value2")])

Creating a case-insensitive dataset

The following example creates a case-insensitive dataset. Both the dataset name and table names inside the dataset are case-insensitive.

CREATESCHEMAmydatasetOPTIONS(is_case_insensitive=TRUE)

Creating a dataset with collation support

The following example creates a dataset with a collation specification.

CREATESCHEMAmydatasetDEFAULTCOLLATE'und:ci'

CREATE TABLE statement

Creates a new table.

Syntax

CREATE[ORREPLACE][TEMP|TEMPORARY]TABLE[IFNOTEXISTS]table_name[(column|constraint_definition[,...])][DEFAULTCOLLATEcollate_specification][PARTITIONBYpartition_expression][CLUSTERBYclustering_column_list][WITHCONNECTIONconnection_name][OPTIONS(table_option_list)][ASquery_statement]column:=column_definitionconstraint_definition:=[primary_key]|[[CONSTRAINTconstraint_name]foreign_key,...]primary_key:=PRIMARYKEY(column_name[,...])NOTENFORCEDforeign_key:=FOREIGNKEY(column_name[,...])foreign_referenceforeign_reference:=REFERENCESprimary_key_table(column_name[,...])NOTENFORCED

Arguments

• OR REPLACE: Replaces any table with the same name if it exists. Cannot appear with IF NOT EXISTS.

• TEMP | TEMPORARY: Creates a temporary table.

• IF NOT EXISTS: If any table exists with the same name, the CREATE statement has no effect. Cannot appear with OR REPLACE.

• table_name: The name of the table to create. See Table path syntax. For temporary tables, do not include the project name or dataset name.

• column: The table's schema information.

• constraint_definition: An expression that defines a table constraint.

• collation_specification: When a new column is added to the table without an explicit collation specification, the column inherits this collation specification for STRING types.

  If you remove or change this collation specification later with the ALTER TABLE statement, this will not change existing collation specifications in this table. If you want to update an existing collation specification in a table, you must alter the column that contains the specification.

  If the table is part of a dataset, the default collation specification for this table overrides the default collation specification for the dataset.

• partition_expression: An expression that determines how to partition the table.

• clustering_column_list: A comma-separated list of column references that determine how to cluster the table. You cannot have collation on columns in this list.

• connection_name: Specifies a connection resource that has credentials for accessing the external data. Specify the connection name in the form PROJECT_ID.LOCATION.CONNECTION_ID. If the project ID or location contains a dash, enclose the connection name in backticks (`). To use a default connection, specify DEFAULT instead of the connection string containing PROJECT_ID.LOCATION.CONNECTION_ID.

• table_option_list: A list of options for creating the table.

• query_statement: The query from which the table should be created. For the query syntax, see SQL syntax reference. If a collation specification is used on this table, collation passes through this query statement.

• primary_key: An expression that defines a primary key table constraint. BigQuery only supports unenforced primary keys.

• foreign_key: An expression that defines a foreign key table constraint. BigQuery only supports unenforced foreign keys.

Details

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, the AS query_statement clause or schema of the table in the LIKE clause.
• Duplicate column names are not allowed.
• When both the LIKE and the AS query_statement clause are present, the column list in the query statement must match the columns of the table referenced by the LIKE clause.
• Table names are case-sensitive unless the dataset they belong to is not. To create a case-insensitive dataset, see Creating a case-insensitive dataset. To alter a dataset to make it case-insensitive dataset, see Turning on case insensitivity for a dataset.

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.

This statement supports the following variants, which have the same limitations:

• CREATE TABLE LIKE: Create a table with the same schema as an existing table.
• CREATE TABLE COPY: Create a table by copying schema and data from an existing table.

column

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

column:=column_namecolumn_schemacolumn_schema:={simple_type|STRUCT<field_list>|ARRAY<array_element_schema>}[PRIMARYKEYNOTENFORCED|REFERENCEStable_name(column_name)NOTENFORCED][DEFAULTdefault_expression][NOTNULL][OPTIONS(column_option_list)]simple_type:={data_type|STRINGCOLLATEcollate_specification}field_list:=field_namecolumn_schema[,...]array_element_schema:={simple_type|STRUCT<field_list>}[NOTNULL]

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

• column_schema: 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 can be used only in the column definition list of CREATE TABLE statements. It cannot be used as a type in expressions.

• simple_type: Any supported data type aside from STRUCT and ARRAY.

  If simple_type is a STRING, it supports an additional clause for collation, which defines how a resulting STRING can be compared and sorted. The syntax looks like this:

  STRINGCOLLATEcollate_specification

  If you have DEFAULT COLLATE collate_specification assigned to the table, the collation specification for a column overrides the specification for the table.

• default_expression: The default value assigned to the column.

• field_list: Represents the fields in a struct.

• field_name: The name of the struct field. Struct field names have the same restrictions as column names.

• NOT NULL: 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.

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:

• _PARTITIONDATE. Partition by ingestion time with daily partitions. This syntax cannot be used with the AS query_statement clause.
• DATE(_PARTITIONTIME). Equivalent to _PARTITIONDATE. This syntax cannot be used with the AS query_statement clause.
• <date_column>. Partition by a DATE column with daily partitions.
• DATE({ <timestamp_column> | <datetime_column> }). Partition by a TIMESTAMP or DATETIME column with daily partitions.
• DATETIME_TRUNC(<datetime_column>, { DAY | HOUR | MONTH | YEAR }). Partition by a DATETIME column with the specified partitioning type.
• TIMESTAMP_TRUNC(<timestamp_column>, { DAY | HOUR | MONTH | YEAR }). Partition by a TIMESTAMP column with the specified partitioning type.
• TIMESTAMP_TRUNC(_PARTITIONTIME, { DAY | HOUR | MONTH | YEAR }). Partition by ingestion time with the specified partitioning type. This syntax cannot be used with the AS query_statement clause.
• DATE_TRUNC(<date_column>, { MONTH | YEAR }). Partition by a DATE column with the specified partitioning type.

• RANGE_BUCKET(<int64_column>, GENERATE_ARRAY(<start>, <end>[, <interval>])). Partition by an integer column with the specified range, where:

  • start is the start of range partitioning, inclusive.
  • end is the end of range partitioning, exclusive.
  • interval is the width of each range within the partition. Defaults to 1.

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

VALUE is a constant expression containing only literals, query parameters, and scalar functions.

The constant expression cannot contain:

• A reference to a table
• Subqueries or SQL statements such as SELECT, CREATE, or 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

If VALUE evaluates to NULL, the corresponding option NAME in the CREATE TABLE statement is ignored.

column_option_list

Specify a column option list in the following format:

NAME=VALUE, ...

NAME and VALUE must be one of the following combinations:

VALUE is a constant expression containing only literals, query parameters, and scalar functions.

The constant expression cannot contain:

• A reference to a table
• Subqueries or SQL statements such as SELECT, CREATE, or 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 column, if there was one. Setting the VALUE to NULL clears the column's value for that option.

Required permissions

This statement requires the following IAM permissions:

In addition, the OR REPLACE clause requires bigquery.tables.update and bigquery.tables.updateData permissions.

If the OPTIONS clause includes any expiration options, then the bigquery.tables.delete permission is also required.

Examples

Creating a new table

The following example creates a partitioned table named newtable in mydataset:

CREATETABLEmydataset.newtable(xINT64OPTIONS(description="An optional INTEGER field"),ySTRUCT< aARRAY<STRING>OPTIONS(description="A repeated STRING field"),bBOOL> )PARTITIONBY_PARTITIONDATEOPTIONS(expiration_timestamp=TIMESTAMP"2025-01-01 00:00:00 UTC",partition_expiration_days=1,description="a table that expires in 2025, with each partition living for 24 hours",labels=[("org_unit","development")])

If you haven't configured a default project, prepend a project ID to the dataset name in the example SQL, and enclose the name in backticks if project_id contains special characters: `project_id.dataset.table`. So, instead of mydataset.newtable, your table qualifier might be `myproject.mydataset.newtable`.

If the table name exists in the dataset, the following error is returned:

Already Exists: project_id:dataset.table

The table uses the following partition_expression to partition the table: PARTITION BY _PARTITIONDATE. This expression partitions the table using the date in the _PARTITIONDATE pseudocolumn.

The table schema contains two columns:

• x: An integer, with description "An optional INTEGER field"

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

Creating a new table from an existing table

The following example creates a table named top_words in mydataset from a query:

CREATETABLEmydataset.top_wordsOPTIONS(description="Top ten words per Shakespeare corpus")ASSELECTcorpus,ARRAY_AGG(STRUCT(word,word_count)ORDERBYword_countDESCLIMIT10)AStop_wordsFROM`bigquery-public-data`.samples.shakespeareGROUPBYcorpus;

If you haven't configured a default project, prepend a project ID to the dataset name in the example SQL, and enclose the name in backticks if project_id contains special characters: `project_id.dataset.table`. So, instead of mydataset.top_words, your table qualifier might be `myproject.mydataset.top_words`.

If the table name exists in the dataset, the following error is returned:

Already Exists: project_id:dataset.table

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

Creating a table only if the table doesn't exist

The following example creates a table named newtable in mydataset only if no table named newtable exists in mydataset. If the table name exists in the dataset, no error is returned, and no action is taken.

CREATETABLEIFNOTEXISTSmydataset.newtable(xINT64,ySTRUCT<aARRAY<STRING>,bBOOL>)OPTIONS(expiration_timestamp=TIMESTAMP"2025-01-01 00:00:00 UTC",description="a table that expires in 2025",labels=[("org_unit","development")])

If you haven't configured a default project, prepend a project ID to the dataset name in the example SQL, and enclose the name in backticks if project_id contains special characters: `project_id.dataset.table`. So, instead of mydataset.newtable, your table qualifier might be `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, 2025 at 00:00:00 UTC
• Description:A table that expires in 2025
• Label:org_unit = development

Creating or replacing a table

The following example creates a table named newtable in mydataset, and if newtable exists in mydataset, it is overwritten with an empty table.

CREATEORREPLACETABLEmydataset.newtable(xINT64,ySTRUCT<aARRAY<STRING>,bBOOL>)OPTIONS(expiration_timestamp=TIMESTAMP"2025-01-01 00:00:00 UTC",description="a table that expires in 2025",labels=[("org_unit","development")])

If you haven't configured a default project, prepend a project ID to the dataset name in the example SQL, and enclose the name in backticks if project_id contains special characters: `project_id.dataset.table`. So, instead of mydataset.newtable, your table qualifier might be `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, 2025 at 00:00:00 UTC
• Description:A table that expires in 2025
• Label:org_unit = development

Creating a table with REQUIRED columns

The following example creates a table named newtable in mydataset. 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.

CREATETABLEmydataset.newtable(xINT64NOTNULL,ySTRUCT< aARRAY<STRING>,bBOOLNOTNULL,cFLOAT64 > NOTNULL,zSTRING)

If you haven't configured a default project, prepend a project ID to the dataset name in the example SQL, and enclose the name in backticks if project_id contains special characters: `project_id.dataset.table`. So, instead of mydataset.newtable, your table qualifier might be `myproject.mydataset.newtable`.

If the table name exists in the dataset, the following error is returned:

Already Exists: project_id:dataset.table

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

Creating a table with collation support

The following examples create a table named newtable in mydataset with columns a, b, c, and a struct with fields x and y.

All STRING column schemas in this table are collated with 'und:ci':

CREATETABLEmydataset.newtable(aSTRING,bSTRING,cSTRUCT< xFLOAT64yARRAY<STRING> > )DEFAULTCOLLATE'und:ci';

Only b and y are collated with 'und:ci':

CREATETABLEmydataset.newtable(aSTRING,bSTRINGCOLLATE'und:ci',cSTRUCT< xFLOAT64yARRAY<STRINGCOLLATE'und:ci'> > );

Creating a table with parameterized data types

The following example creates a table named newtable in mydataset. The parameters in parentheses specify that the column contains a parameterized data type. See Parameterized Data Types for more information about parameterized types.

CREATETABLEmydataset.newtable(xSTRING(10),ySTRUCT< aARRAY<BYTES(5)>,bNUMERIC(15,2)OPTIONS(rounding_mode='ROUND_HALF_EVEN'),cFLOAT64>,zBIGNUMERIC(35))

If you haven't configured a default project, prepend a project ID to the dataset name in the example SQL, and enclose the name in backticks if project_id contains special characters: `project_id.dataset.table`. Instead of mydataset.newtable, your table qualifier should be `myproject.mydataset.newtable`.

If the table name exists in the dataset, the following error is returned:

Already Exists: project_id:dataset.table

The table schema contains 3 columns:

• x: A parameterized string with a maximum length of 10
• y: A STRUCT containing a (an array of parameterized bytes with a maximum length of 5), b (a parameterized NUMERIC with a maximum precision of 15, maximum scale of 2, and rounding mode set to 'ROUND_HALF_EVEN'), and c (a float)
• z: A parameterized BIGNUMERIC with a maximum precision of 35 and maximum scale of 0

Creating a partitioned table

The following example creates a partitioned table named newtable in mydataset using a DATE column:

CREATETABLEmydataset.newtable(transaction_idINT64,transaction_dateDATE)PARTITIONBYtransaction_dateOPTIONS(partition_expiration_days=3,description="a table partitioned by transaction_date")

If you haven't configured a default project, prepend a project ID to the dataset name in the example SQL, and enclose the name in backticks if project_id contains special characters: `project_id.dataset.table`. So, instead of mydataset.newtable, your table qualifier might be `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

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:

CREATETABLEmydataset.days_with_rainPARTITIONBYdateOPTIONS(partition_expiration_days=365,description="weather stations with precipitation, partitioned by day")ASSELECTDATE(CAST(yearASINT64),CAST(moASINT64),CAST(daASINT64))ASdate,(SELECTANY_VALUE(name)FROM`bigquery-public-data.noaa_gsod.stations`ASstationsWHEREstations.usaf=stn)ASstation_name,-- Stations can have multiple namesprcpFROM`bigquery-public-data.noaa_gsod.gsod2017`ASweatherWHEREprcp!=99.9-- Filter unknown valuesANDprcp > 0-- Filter stations/days with no precipitation

If you haven't configured a default project, prepend a project ID to the dataset name in the example SQL, and enclose the name in backticks if project_id contains special characters: `project_id.dataset.table`. So, instead of mydataset.days_with_rain, your table qualifier might be `myproject.mydataset.days_with_rain`.

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

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 truncated TIMESTAMP column and clustered by a STRING column named customer_id.

CREATETABLEmydataset.myclusteredtable(input_timestampTIMESTAMP,customer_idSTRING,transaction_amountNUMERIC)PARTITIONBYTIMESTAMP_TRUNC(input_timestamp,HOUR)CLUSTERBYcustomer_idOPTIONS(partition_expiration_days=3,description="a table clustered by customer_id")

If you haven't configured a default project, prepend a project ID to the dataset name in the example SQL, and enclose the name in backticks if project_id contains special characters: `project_id.dataset.table`. So, instead of mydataset.myclusteredtable, your table qualifier might be `myproject.mydataset.myclusteredtable`.

The table schema contains 3 columns:

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

Example 2

The following example creates a clustered table named myclusteredtable in mydataset. The table is an ingestion-time partitioned table.

CREATETABLEmydataset.myclusteredtable(customer_idSTRING,transaction_amountNUMERIC)PARTITIONBYDATE(_PARTITIONTIME)CLUSTERBYcustomer_idOPTIONS(partition_expiration_days=3,description="a table clustered by customer_id")

If you haven't configured a default project, prepend a project ID to the dataset name in the example SQL, and enclose the name in backticks if project_id contains special characters: `project_id.dataset.table`. So, instead of mydataset.myclusteredtable, your table qualifier might be `myproject.mydataset.myclusteredtable`.

The table schema contains 2 columns:

• 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

Example 3

The following example creates a clustered table named myclusteredtable in mydataset. The table is not partitioned.

CREATETABLEmydataset.myclusteredtable(customer_idSTRING,transaction_amountNUMERIC)CLUSTERBYcustomer_idOPTIONS(description="a table clustered by customer_id")

If you haven't configured a default project, prepend a project ID to the dataset name in the example SQL, and enclose the name in backticks if project_id contains special characters: `project_id.dataset.table`. So, instead of mydataset.myclusteredtable, your table qualifier might be `myproject.mydataset.myclusteredtable`.

The table schema contains 2 columns:

• customer_id: The customer ID as a STRING
• transaction_amount: The transaction amount as NUMERIC

The table option list specifies the:

• Description:A table clustered by customer_id

Creating a clustered table from the result of a query

Example 1

The following example creates a partitioned and clustered table named myclusteredtable in mydataset using the result of a query.

CREATETABLEmydataset.myclusteredtable(input_timestampTIMESTAMP,customer_idSTRING,transaction_amountNUMERIC)PARTITIONBYDATE(input_timestamp)CLUSTERBYcustomer_idOPTIONS(partition_expiration_days=3,description="a table clustered by customer_id")ASSELECT*FROMmydataset.myothertable

If you haven't configured a default project, prepend a project ID to the dataset name in the example SQL, and enclose the name in backticks if project_id contains special characters: `project_id.dataset.table`. So, instead of mydataset.myclusteredtable, your table qualifier might be `myproject.mydataset.myclusteredtable`.

The table schema contains 3 columns:

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

Example 2

The following example creates a clustered table named myclusteredtable in mydataset using the result of a query. The table is not partitioned.

CREATETABLEmydataset.myclusteredtable(customer_idSTRING,transaction_amountNUMERIC)CLUSTERBYcustomer_idOPTIONS(description="a table clustered by customer_id")ASSELECT*FROMmydataset.myothertable

If you haven't configured a default project, prepend a project ID to the dataset name in the example SQL, and enclose the name in backticks if project_id contains special characters: `project_id.dataset.table`. So, instead of mydataset.myclusteredtable, your table qualifier might be `myproject.mydataset.myclusteredtable`.

The table schema contains 2 columns:

• customer_id: The customer ID as a STRING
• transaction_amount: The transaction amount as NUMERIC

The table option list specifies the:

• Description:A table clustered by customer_id

Creating a temporary table

The following example creates a temporary table named Example and inserts values into it.

CREATETEMPTABLEExample(xINT64,ySTRING);INSERTINTOExampleVALUES(5,'foo');INSERTINTOExampleVALUES(6,'bar');SELECT*FROMExample;

This script returns the following output:

+-----+---+-----+|Row|x|y|+-----+---|-----+|1|5|foo||2|6|bar|+-----+---|-----+

Load data across clouds

Example 1

Suppose you have a BigLake table named myawsdataset.orders that references data from Amazon S3. You want to transfer data from that table to a BigQuery table myotherdataset.shipments in the US multi-region.

First, display information about the myawsdataset.orders table:

bqshowmyawsdataset.orders;

The output is similar to the following:

 Last modified Schema Type Total URIs Expiration ----------------- -------------------------- ---------- ------------ ----------- 31 Oct 17:40:28 |- l_orderkey: integer EXTERNAL 1 |- l_partkey: integer |- l_suppkey: integer |- l_linenumber: integer |- l_returnflag: string |- l_linestatus: string |- l_commitdate: date 

Next, display information about the myotherdataset.shipments table:

bqshowmyotherdataset.shipments

The output is similar to the following. Some columns are omitted to simplify the output.

 Last modified Schema Total Rows Total Bytes Expiration Time Partitioning Clustered Fields Total Logical ----------------- --------------------------- ------------ ------------- ------------ ------------------- ------------------ --------------- 31 Oct 17:34:31 |- l_orderkey: integer 3086653 210767042 210767042 |- l_partkey: integer |- l_suppkey: integer |- l_commitdate: date |- l_shipdate: date |- l_receiptdate: date |- l_shipinstruct: string |- l_shipmode: string 

Now, using the CREATE TABLE AS SELECT statement you can selectively load data to the myotherdataset.orders table in the US multi-region:

CREATEORREPLACETABLEmyotherdataset.ordersPARTITIONBYDATE_TRUNC(l_commitdate,YEAR)ASSELECT*FROMmyawsdataset.ordersWHEREEXTRACT(YEARFROMl_commitdate)=1992;

You can then perform a join operation with the newly created table:

SELECTorders.l_orderkey,orders.l_orderkey,orders.l_suppkey,orders.l_commitdate,orders.l_returnflag,shipments.l_shipmode,shipments.l_shipinstructFROMmyotherdataset.shipmentsJOIN`myotherdataset.orders`asordersONorders.l_orderkey=shipments.l_orderkeyANDorders.l_partkey=shipments.l_partkeyANDorders.l_suppkey=shipments.l_suppkeyWHEREorders.l_returnflag='R';-- 'R' means refunded.

When new data is available, append the data of the 1993 year to the destination table using the INSERT INTO SELECT statement:

INSERTINTOmyotherdataset.ordersSELECT*FROMmyawsdataset.ordersWHEREEXTRACT(YEARFROMl_commitdate)=1993;

Example 2

The following example inserts data into an ingestion-time partitioned table:

CREATETABLEmydataset.orders(idString,numeric_idINT64)PARTITIONBY_PARTITIONDATE;

After creating a partitioned table, you can insert data into the ingestion-time partitioned table:

INSERTINTOmydataset.orders(_PARTITIONTIME,id,numeric_id)SELECTTIMESTAMP("2023-01-01"),id,numeric_id,FROMmydataset.ordersof23WHEREnumeric_id>4000000;

CREATE TABLE LIKE statement

Creates a new table with all of the same metadata of another table.

Syntax

CREATE[ORREPLACE]TABLE[IFNOTEXISTS]table_nameLIKE[[project_name.]dataset_name.]source_table_name...[OPTIONS(table_option_list)]

Details

This statement is a variant of the CREATE TABLE statement and has the same limitations. Other than the use of the LIKE clause in place of a column list, the syntax is identical to the CREATE TABLE syntax.

The CREATE TABLE LIKE statement copies only the metadata of the source table. You can use the AS query_statement clause to include data into the new table.

The new table has no relationship to the source table after creation; thus modifications to the source table will not propagate to the new table.

By default, the new table inherits partitioning, clustering, and options metadata from the source table. You can customize metadata in the new table by using the optional clauses in the SQL statement. For example, if you want to specify a different set of options for the new table, then include the OPTIONS clause with a list of options and values. This behavior matches that of ALTER TABLE SET OPTIONS.

Required permissions

This statement requires the following IAM permissions:

In addition, the OR REPLACE clause requires bigquery.tables.update and bigquery.tables.updateData permissions.

If the OPTIONS clause includes any expiration options, then the bigquery.tables.delete permission is also required.

Examples

Example 1

The following example creates a new table named newtable in mydataset with the same metadata as sourcetable:

CREATETABLEmydataset.newtableLIKEmydataset.sourcetable

Example 2

The following example creates a new table named newtable in mydataset with the same metadata as sourcetable and the data from the SELECT statement:

CREATETABLEmydataset.newtableLIKEmydataset.sourcetableASSELECT*FROMmydataset.myothertable

CREATE TABLE COPY statement

Creates a table that has the same metadata and data as another table. The source table can be a table, a table clone, or a table snapshot.

Syntax

CREATE[ORREPLACE]TABLE[IFNOTEXISTS]table_nameCOPYsource_table_name...[OPTIONS(table_option_list)]

Details

This statement is a variant of the CREATE TABLE statement and has the same limitations. Other than the use of the COPY clause in place of a column list, the syntax is identical to the CREATE TABLE syntax.

The CREATE TABLE COPY statement copies both the metadata and data from the source table.

The new table inherits partitioning and clustering from the source table. By default, the table options metadata from the source table are also inherited, but you can override table options by using the OPTIONS clause. The behavior is equivalent to running ALTER TABLE SET OPTIONS after the table is copied.

The new table has no relationship to the source table after creation; modifications to the source table are not propagated to the new table.

Required permissions

This statement requires the following IAM permissions:

In addition, the OR REPLACE clause requires bigquery.tables.update and bigquery.tables.updateData permissions.

If the OPTIONS clause includes any expiration options, then the bigquery.tables.delete permission is also required.

CREATE SNAPSHOT TABLE statement

Creates a table snapshot based on a source table. The source table can be a table, a table clone, or a table snapshot.

Syntax

CREATESNAPSHOTTABLE[IFNOTEXISTS]table_snapshot_nameCLONEsource_table_name[FORSYSTEM_TIMEASOFtime_expression][OPTIONS(snapshot_option_list)]

Arguments

• IF NOT EXISTS: If a table snapshot or other table resource exists with the same name, the CREATE statement has no effect.

• table_snapshot_name: The name of the table snapshot that you want to create. The table snapshot name must be unique per dataset. See Table path syntax.

• source_table_name: The name of the table that you want to snapshot or the table snapshot that you want to copy. See Table path syntax.

  If the source table is a standard table, then BigQuery creates a table snapshot of the source table. If the source table is a table snapshot, then BigQuery creates a copy of the table snapshot.

• FOR SYSTEM_TIME AS OF: Lets you select the version of the table that was current at the time specified by timestamp_expression. It can only be used when creating a snapshot of a table; it can't be used when making a copy of a table snapshot.

• snapshot_option_list: Additional table snapshot creation options such as a label and an expiration time.

Details

CREATE SNAPSHOT TABLE statements must comply with the following rules:

• Only one CREATE statement is allowed.
• The source table must be one of the following:
  • A table
  • A table clone
  • A table snapshot
• The FOR SYSTEM_TIME AS OF clause can only be used when creating a snapshot of a table or table clone; it can't be used when making a copy of a table snapshot.

snapshot_option_list

The option list lets you set table snapshot options such as a label and an expiration time. You can include multiple options using a comma-separated list.

Specify a table snapshot option list in the following format:

NAME=VALUE, ...

NAME and VALUE must be one of the following combinations:

VALUE is a constant expression that contains only literals, query parameters, and scalar functions.

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

If VALUE evaluates to NULL, the corresponding option NAME in the CREATE SNAPSHOT TABLE statement is ignored.

Required permissions

This statement requires the following IAM permissions:

Examples

Create a table snapshot: fail if it already exists

The following example creates a table snapshot of the table myproject.mydataset.mytable. The table snapshot is created in the dataset mydataset and is named mytablesnapshot:

CREATESNAPSHOTTABLE`myproject.mydataset.mytablesnapshot` CLONE`myproject.mydataset.mytable`OPTIONS(expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(),INTERVAL48HOUR),friendly_name="my_table_snapshot",description="A table snapshot that expires in 2 days",labels=[("org_unit","development")])

If the table snapshot name already exists in the dataset, then the following error is returned:

Already Exists: myproject.mydataset.mytablesnapshot

The table snapshot option list specifies the following:

• Expiration time: 48 hours after the time the table snapshot is created
• Friendly name:my_table_snapshot
• Description:A table snapshot that expires in 2 days
• Label:org_unit = development

Create a table snapshot: ignore if it already exists

The following example creates a table snapshot of the table myproject.mydataset.mytable. The table snapshot is created in the dataset mydataset and is named mytablesnapshot:

CREATESNAPSHOTTABLEIFNOTEXISTS`myproject.mydataset.mytablesnapshot` CLONE`myproject.mydataset.mytable`OPTIONS(expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(),INTERVAL48HOUR),friendly_name="my_table_snapshot",description="A table snapshot that expires in 2 days"labels=[("org_unit","development")])

The table snapshot option list specifies the following:

• Expiration time: 48 hours after the time the table snapshot is created
• Friendly name:my_table_snapshot
• Description:A table snapshot that expires in 2 days
• Label:org_unit = development

If the table snapshot name already exists in the dataset, then no action is taken, and no error is returned.

For information about restoring table snapshots, see CREATE TABLE CLONE.

For information about removing table snapshots, see DROP SNAPSHOT TABLE.

CREATE TABLE CLONE statement

Creates a table clone based on a source table. The source table can be a table, a table clone, or a table snapshot.

Syntax

CREATETABLE[IFNOTEXISTS]destination_table_nameCLONEsource_table_name[FORSYSTEM_TIMEASOFtime_expression]...[OPTIONS(table_option_list)]

Details

Other than the use of the CLONE clause in place of a column list, the syntax is identical to the CREATE TABLE syntax.

Arguments

• IF NOT EXISTS: If the specified destination table name already exists, the CREATE statement has no effect.

• destination_table_name: The name of the table that you want to create. The table name must be unique per dataset. The table name can contain the following:

  • Up to 1,024 characters
  • Letters (upper or lower case), numbers, and underscores

• OPTIONS(table_option_list): Lets you specify additional table creation options such as a label and an expiration time.

• source_table_name: The name of the source table.

CREATE TABLE CLONE statements must comply with the following rules:

• Only one CREATE statement is allowed.
• The table that is being cloned must be a table, a table clone, or a table snapshot.

OPTIONS

CREATE TABLE CLONE options are the same as CREATE TABLE options.

Required permissions

This statement requires the following IAM permissions:

If the OPTIONS clause includes any expiration options, then the bigquery.tables.delete permission is also required.

Examples

Restore a table snapshot: fail if destination table already exists

The following example creates the table myproject.mydataset.mytable from the table snapshot myproject.mydataset.mytablesnapshot:

CREATETABLE`myproject.mydataset.mytable` CLONE`myproject.mydataset.mytablesnapshot`OPTIONS(expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(),INTERVAL365DAY),friendly_name="my_table",description="A table that expires in 1 year",labels=[("org_unit","development")])

If the table name exists in the dataset, then the following error is returned:

Already Exists: myproject.mydataset.mytable.

The table option list specifies the following:

• Expiration time: 365 days after the time that the table is created
• Friendly name:my_table
• Description:A table that expires in 1 year
• Label:org_unit = development

Create a clone of a table: ignore if the destination table already exists

The following example creates the table clone myproject.mydataset.mytableclone based on the table myproject.mydataset.mytable:

CREATETABLEIFNOTEXISTS`myproject.mydataset.mytableclone`CLONE`myproject.mydataset.mytable`OPTIONS(expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(),INTERVAL365DAY),friendly_name="my_table",description="A table that expires in 1 year",labels=[("org_unit","development")])

The table option list specifies the following:

• Expiration time: 365 days after the time the table is created
• Friendly name:my_table
• Description:A table that expires in 1 year
• Label:org_unit = development

If the table name exists in the dataset, then no action is taken, and no error is returned.

For information about creating a copy of a table, see CREATE TABLE COPY.

For information about creating a snapshot of a table, see CREATE SNAPSHOT TABLE.

CREATE VIEW statement

Creates a new view.

Syntax

CREATE[ORREPLACE]VIEW[IFNOTEXISTS]view_name[(view_column_name_list)][OPTIONS(view_option_list)]ASquery_expressionview_column_name_list:=view_column[,...]view_column:=column_name[OPTIONS(view_column_option_list)]

Arguments

• OR REPLACE: Replaces any view with the same name if it exists. Cannot appear with IF NOT EXISTS.

• IF NOT EXISTS: If a view or other table resource exists with the same name, the CREATE statement has no effect. Cannot appear with OR REPLACE.

• view_name: The name of the view you're creating. See Table path syntax.

• view_column_name_list: Lets you explicitly specify the column names of the view, which may be aliases to the column names in the underlying SQL query.

• view_option_list: Additional view creation options such as a label and an expiration time.

• query_expression: The GoogleSQL query expression used to define the view.

Details

CREATE VIEW statements must comply with the following rules:

• Only one CREATE statement is allowed.

view_column_name_list

The view's column name list is optional. The names must be unique but do not have to be the same as the column names of the underlying SQL query. For example, if your view is created with the following statement:

CREATEVIEWmydataset.age_groups(age,count)ASSELECTage,COUNT(*)FROMmydataset.peoplegroupbyage;

Then you can query it with:

SELECTage,countfrommydataset.age_groups;

The number of columns in the column name list must match the number of columns in the underlying SQL query. If the columns in the table of the underlying SQL query is added or dropped, the view becomes invalid and must be recreated. For example, if the age column is dropped from the mydataset.people table, then the view created in the previous example becomes invalid.

view_column_option_list

The view_column_option_list lets you specify optional top-level column options. Column options for a view have the same syntax and requirements as for a table, but with a different list of NAME and VALUE fields:

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:

VALUE is a constant expression containing only literals, query parameters, and scalar functions.

The constant expression cannot contain:

• A reference to a table
• Subqueries or SQL statements such as SELECT, CREATE, or 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

If VALUE evaluates to NULL, the corresponding option NAME in the CREATE VIEW statement is ignored.

privacy_policy

The following policies are available in the privacy_policy view option to create analysis rules. A policy represents a condition that needs to be met before a query can be run.

 '{ "aggregation_threshold_policy": { "threshold": value, "privacy_unit_columns": value } }' 

 '{ "differential_privacy_policy": { "privacy_unit_column": value, "max_epsilon_per_query": value, "epsilon_budget": value, "delta_per_query": value, "delta_budget": value, "max_groups_contributed": value } }' 

 '{ "join_restriction_policy": { "join_condition": value, "join_allowed_columns": value } }' 

Default project in view body

If the view is created in the same project used to run the CREATE VIEW statement, the view body query_expression can reference entities without specifying the project; the default project is the project which owns the view. Consider the sample query below.

CREATEVIEWmyProject.myDataset.myViewASSELECT*FROManotherDataset.myTable;

After running the above CREATE VIEW query in the project myProject, you can run the query SELECT * FROM myProject.myDataset.myView. Regardless of the project you choose to run this SELECT query, the referenced table anotherDataset.myTable is always resolved against project myProject.

If the view is not created in the same project used to run the CREATE VIEW statement, then all references in the view body query_expression must be qualified with project IDs. For instance, the preceding sample CREATE VIEW query is invalid if it runs in a project different from myProject.

Required permissions

This statement requires the following IAM permissions:

In addition, the OR REPLACE clause requires bigquery.tables.update permission.

If the OPTIONS clause includes an expiration time, then the bigquery.tables.delete permission is also required.

Examples

Creating a new view

The following example creates a view named newview in mydataset:

CREATEVIEW`myproject.mydataset.newview`OPTIONS(expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(),INTERVAL48HOUR),friendly_name="newview",description="a view that expires in 2 days",labels=[("org_unit","development")])ASSELECTcolumn_1,column_2,column_3FROM`myproject.mydataset.mytable`

If the view name exists in the dataset, the following error is returned:

Already Exists: project_id:dataset.table

The view is defined using the following GoogleSQL 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

Creating a view only if the view doesn't exist

The following example creates a view named newview in mydataset only if no view named newview exists in mydataset. If the view name exists in the dataset, no error is returned, and no action is taken.

CREATEVIEWIFNOTEXISTS`myproject.mydataset.newview`OPTIONS(expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(),INTERVAL48HOUR),friendly_name="newview",description="a view that expires in 2 days",labels=[("org_unit","development")])ASSELECTcolumn_1,column_2,column_3FROM`myproject.mydataset.mytable`

The view is defined using the following GoogleSQL 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

Creating or replacing a view

The following example creates a view named newview in mydataset, and if newview exists in mydataset, it is overwritten using the specified query expression.

CREATEORREPLACEVIEW`myproject.mydataset.newview` OPTIONS(expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(),INTERVAL48HOUR),friendly_name="newview",description="a view that expires in 2 days",labels=[("org_unit","development")])ASSELECTcolumn_1,column_2,column_3FROM`myproject.mydataset.mytable`

The view is defined using the following GoogleSQL 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

Creating a view with column descriptions

The following example creates a view named newview in mydataset. This view definition provides the column description for each column in mytable. You can rename columns from the original query.

CREATEVIEW`myproject.mydataset.newview`(column_1_new_nameOPTIONS(DESCRIPTION='Description of the column 1 contents'),column_2_new_nameOPTIONS(DESCRIPTION='Description of the column 2 contents'),column_3_new_nameOPTIONS(DESCRIPTION='Description of the column 3 contents'))ASSELECTcolumn_1,column_2,column_3FROM`myproject.mydataset.mytable`

CREATE MATERIALIZED VIEW statement

Creates a new materialized view.

Syntax

CREATE[ORREPLACE]MATERIALIZEDVIEW[IFNOTEXISTS]materialized_view_name[PARTITIONBYpartition_expression][CLUSTERBYclustering_column_list][OPTIONS(materialized_view_option_list)]ASquery_expression

Arguments

• OR REPLACE: Replaces a materialized view with the same name if it exists. Cannot appear with IF NOT EXISTS.

• IF NOT EXISTS: If a materialized view or other table resource exists with the same name, the CREATE statement has no effect. Cannot appear with OR REPLACE.

• materialized_view_name: The name of the materialized view you're creating. See Table path syntax.

  If the project_name is omitted from the materialized view name, or it is the same as the project that runs this DDL query, then the latter is also used as the default project for references to tables, functions, and other resources in query_expression. The default project of the references is fixed and does not depend on the future queries that invoke the new materialized view. Otherwise, all references in query_expression must be qualified with project names.

  The materialized view name must be unique per dataset.

• partition_expression: An expression that determines how to partition the table. A materialized view can only be partitioned in the same way as the table in query expression (the base table) is partitioned.

• clustering_column_list: A comma-separated list of column references that determine how to cluster the materialized view.

• materialized_view_option_list: Allows you to specify additional materialized view options such as a whether refresh is enabled, the refresh interval, a label, and an expiration time.

• query_expression: The GoogleSQL query expression used to define the materialized view.

Details

CREATE MATERIALIZED VIEW statements must comply with the following rules:

• Only one CREATE statement is allowed.

Default project in materialized view body

If the materialized view is created in the same project used to run the CREATE MATERIALIZED VIEW statement, the materialized view body query_expression can reference entities without specifying the project; the default project is the project which owns the materialized view. Consider the sample query below.

CREATEMATERIALIZEDVIEWmyProject.myDataset.myViewASSELECT*FROManotherDataset.myTable;

After running the above CREATE MATERIALIZED VIEW query in the project myProject, you can run the query SELECT * FROM myProject.myDataset.myView. Regardless of the project you choose to run this SELECT query, the referenced table anotherDataset.myTable is always resolved against project myProject.

If the materialized view is not created in the same project used to run the CREATE VIEW statement, then all references in the materialized view body query_expression must be qualified with project IDs. For instance, the preceding sample CREATE MATERIALIZED VIEW query is invalid if it runs in a project different from myProject.

materialized_view_option_list

The option list allows you to set materialized view options such as a whether refresh is enabled. the refresh interval, a label and an expiration time. You can include multiple options using a comma-separated list.

Specify a materialized view option list in the following format:

NAME=VALUE, ...

NAME and VALUE must be one of the following combinations:

Required permissions

This statement requires the following IAM permissions:

In addition, the OR REPLACE clause requires bigquery.tables.update permission.

If the OPTIONS clause includes any expiration options, then the bigquery.tables.delete permission is also required.

Examples

Creating a new materialized view

The following example creates a materialized view named new_mv in mydataset:

CREATEMATERIALIZEDVIEW`myproject.mydataset.new_mv` OPTIONS(expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(),INTERVAL48HOUR),friendly_name="new_mv",description="a materialized view that expires in 2 days",labels=[("org_unit","development")],enable_refresh=true,refresh_interval_minutes=20)ASSELECTcolumn_1,SUM(column_2)ASsum_2,AVG(column_3)ASavg_3FROM`myproject.mydataset.mytable`GROUPBYcolumn_1

If the materialized view name exists in the dataset, the following error is returned:

Already Exists: project_id:dataset.materialized_view

When you use a DDL statement to create a materialized view, you must specify the project, dataset, and materialized view in the following format: `project_id.dataset.materialized_view` (including the backticks if project_id contains special characters); for example, `myproject.mydataset.new_mv`.

The materialized view is defined using the following GoogleSQL query:

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

The materialized view option list specifies the:

• Expiration time: 48 hours from the time the materialized view is created
• Friendly name:new_mv
• Description:A materialized view that expires in 2 days
• Label:org_unit = development
• Refresh enabled: true
• Refresh interval: 20 minutes

Creating a materialized view only if the materialized view doesn't exist

The following example creates a materialized view named new_mv in mydataset only if no materialized view named new_mv exists in mydataset. If the materialized view name exists in the dataset, no error is returned, and no action is taken.

CREATEMATERIALIZEDVIEWIFNOTEXISTS`myproject.mydataset.new_mv` OPTIONS(expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(),INTERVAL48HOUR),friendly_name="new_mv",description="a view that expires in 2 days",labels=[("org_unit","development")],enable_refresh=false)ASSELECTcolumn_1,column_2,column_3FROM`myproject.mydataset.mytable`

The materialized view is defined using the following GoogleSQL query:

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

The materialized view option list specifies the:

• Expiration time: 48 hours from the time the view is created
• Friendly name:new_mv
• Description:A view that expires in 2 days
• Label:org_unit = development
• Refresh enabled: false

Creating a materialized view with partitioning and clustering

The following example creates a materialized view named new_mv in mydataset, partitioned by the col_datetime column and clustered by the col_int column:

CREATEMATERIALIZEDVIEW`myproject.mydataset.new_mv` PARTITIONBYDATE(col_datetime)CLUSTERBYcol_intASSELECTcol_int,col_datetime,COUNT(1)ascntFROM`myproject.mydataset.mv_base_table` GROUPBYcol_int,col_datetime

The base table, mv_base_table, must also be partitioned by the col_datetime column. For more information, see Working with partitioned and clustered tables.

CREATE MATERIALIZED VIEW AS REPLICA OF statement

Creates a replica of a materialized view. The source materialized view must be over an Amazon Simple Storage Service (Amazon S3) BigLake table. You can use the materialized view replica to make Amazon S3 data available locally for joins.

For more information, see Create materialized view replicas.

Syntax

CREATEMATERIALIZEDVIEWreplica_name[OPTIONS(materialized_view_replica_option_list)]ASREPLICAOFsource_materialized_view_name

Arguments

• replica_name: The name of the materialized view replica you're creating, in table path syntax. If the project name is omitted from the materialized view replica name, the current project is used as the default.

  The materialized view replica name must be unique for each dataset.

• materialized_view_replica_option_list: Allows you to specify options such as the replication interval.

• source_materialized_view_name: The name of the materialized view you are replicating, in table path syntax. The source materialized view must be over an Amazon S3 BigLake table, and must be authorized on the dataset that contains that table.

materialized_view_replica_option_list

The option list lets you set materialized view replica options.

Specify a materialized view replica option list in the following format:

NAME=VALUE, ...

Required permissions

This statement requires the following IAM permissions:

• bigquery.tables.create
• bigquery.tables.get
• bigquery.tables.getData
• bigquery.tables.replicateData
• bigquery.jobs.create

Example

The following example creates a materialized view replica named mv_replica in bq_dataset:

CREATEMATERIALIZEDVIEW`myproject.bq_dataset.mv_replica` OPTIONS(replication_interval_seconds=600)ASREPLICAOF`myproject.s3_dataset.my_s3_mv`

CREATE EXTERNAL SCHEMA statement

Creates a new federated dataset.

A federated dataset is a connection between BigQuery and an external data source at the dataset level. For more information about creating federated datasets, see the following:

• Create AWS Glue federated datasets.
• Create Spanner federated datasets.

Syntax

CREATEEXTERNALSCHEMA[IFNOTEXISTS]dataset_name[WITHCONNECTIONconnection_name][OPTIONS(external_schema_option_list)]

Arguments

• IF NOT EXISTS: If any dataset exists with the same name, the CREATE statement has no effect.

• dataset_name: The name of the dataset to create.

• connection_name: Specifies a connection resource that has credentials for accessing the external data. Specify the connection name in the form PROJECT_ID.LOCATION.CONNECTION_ID. If the project ID or location contains a dash, enclose the connection name in backticks (`).

• external_schema_option_list: A list of options for creating the federated dataset.

Details

The dataset is created in the location that you specify in the query settings. For more information, see Specify locations. The location must support the kind of federated dataset that you are creating, for example, you can only create AWS Glue federated datasets in AWS locations.

For more information about creating a dataset, see Create datasets. For information about quotas, see dataset limits.

external_schema_option_list

The option list specifies options for the federated dataset. Specify the options in the following format: NAME=VALUE, ...

The following options are supported:

Required permissions

This statement requires the following IAM permissions:

Examples

The following example creates an AWS Glue federated dataset:

CREATEEXTERNALSCHEMAmydatasetWITHCONNECTIONmyproject.`aws-us-east-1`.myconnectionOPTIONS(external_source='aws-glue://arn:aws:glue:us-east-1:123456789:database/test_database',location='aws-us-east-1');

CREATE EXTERNAL TABLE statement

Creates a new external table.

External tables let BigQuery query data that is stored outside of BigQuery storage. For more information about external tables, see Introduction to external data sources.

Syntax

CREATE[ORREPLACE]EXTERNALTABLE[IFNOTEXISTS]table_name[(column_namecolumn_schema,...)][WITHCONNECTION{connection_name|DEFAULT}][WITHPARTITIONCOLUMNS[(partition_column_namepartition_column_type,...)]]OPTIONS(external_table_option_list,...);

Arguments

• OR REPLACE: Replaces any external table with the same name if it exists. Cannot appear with IF NOT EXISTS.

• IF NOT EXISTS: If an external table or other table resource exists with the same name, the CREATE statement has no effect. Cannot appear with OR REPLACE.

• table_name: The name of the external table. See Table path syntax.

• column_name: The name of a column in the table.

• column_schema: Specifies the schema of the column. It uses the same syntax as the column_schema definition in the CREATE TABLE statement. If you don't include this clause, BigQuery detects the schema automatically.

• connection_name: Specifies a connection resource that has credentials for accessing the external data. Specify the connection name in the form PROJECT_ID.LOCATION.CONNECTION_ID. If the project ID or location contains a dash, enclose the connection name in backticks (`). To use a default connection, specify DEFAULT instead of the connection string containing PROJECT_ID.LOCATION.CONNECTION_ID.

• partition_column_name: The name of a partition column. Include this field if your external data uses a hive-partitioned layout. For more information, see: Supported data layouts.

• partition_column_type: The partition column type.

• external_table_option_list: A list of options for creating the external table.

Details

The CREATE EXTERNAL TABLE statement does not support creating temporary external tables.

To create an externally partitioned table, use the WITH PARTITION COLUMNS clause to specify the partition schema details. BigQuery validates the column definitions against the external data location. The schema declaration must strictly follow the ordering of the fields in the external path. For more information about external partitioning, see Querying externally partitioned data.

external_table_option_list

The option list specifies options for creating the external table. The format and uris options are required. Specify the option list in the following format: NAME=VALUE, ...

Required permissions

This statement requires the following IAM permissions:

In addition, the OR REPLACE clause requires bigquery.tables.update permission.

If the OPTIONS clause includes an expiration time, then the bigquery.tables.delete permission is also required.

Examples

The following example creates a BigLake table and explicitly specifies the schema. It also specifies refreshing metadata cache automatically at a system-defined interval.

CREATEORREPLACEEXTERNALTABLEmydataset.newtable(xINT64,ySTRING,zBOOL)WITHCONNECTIONmyconnectionOPTIONS(format="PARQUET",max_staleness=STALENESS_INTERVAL,metadata_cache_mode='AUTOMATIC');

The following example creates an external table from multiple URIs. The data format is CSV. This example uses schema auto-detection.

CREATEEXTERNALTABLEdataset.CsvTableOPTIONS(format='CSV',uris=['gs://bucket/path1.csv','gs://bucket/path2.csv']);

The following example creates an external table from a CSV file and explicitly specifies the schema. It also specifies the field delimiter ('|') and sets the maximum number of bad records allowed.

CREATEORREPLACEEXTERNALTABLEdataset.CsvTable(xINT64,ySTRING)OPTIONS(format='CSV',uris=['gs://bucket/path1.csv'],field_delimiter='|',max_bad_records=5);

The following example creates an externally partitioned table. It uses schema auto-detection to detect both the file schema and the hive partitioning layout. If the external path is gs://bucket/path/field_1=first/field_2=1/data.parquet, the partition columns are detected as field_1 (STRING) and field_2 (INT64).

CREATEEXTERNALTABLEdataset.AutoHivePartitionedTableWITHPARTITIONCOLUMNSOPTIONS(uris=['gs://bucket/path/*'],format='PARQUET',hive_partition_uri_prefix='gs://bucket/path',require_hive_partition_filter=false);

The following example creates an externally partitioned table by explicitly specifying the partition columns. This example assumes that the external file path has the pattern gs://bucket/path/field_1=first/field_2=1/data.parquet.

CREATEEXTERNALTABLEdataset.CustomHivePartitionedTableWITHPARTITIONCOLUMNS(field_1STRING,-- column order must match the external pathfield_2INT64)OPTIONS(uris=['gs://bucket/path/*'],format='PARQUET',hive_partition_uri_prefix='gs://bucket/path',require_hive_partition_filter=false);

CREATE FUNCTION statement

Creates a new user-defined function (UDF). BigQuery supports UDFs written in SQL, JavaScript, or Python.

Syntax

To create a SQL UDF, use the following syntax:

CREATE[ORREPLACE][TEMPORARY|TEMP]FUNCTION[IFNOTEXISTS][[project_name.]dataset_name.]function_name([named_parameter[,...]])([named_parameter[,...]])[RETURNSdata_type]AS(sql_expression)[OPTIONS(function_option_list)]named_parameter:param_nameparam_type

To create a JavaScript UDF, use the following syntax:

CREATE[ORREPLACE][TEMPORARY|TEMP]FUNCTION[IFNOTEXISTS][[project_name.]dataset_name.]function_name([named_parameter[,...]])RETURNSdata_type[determinism_specifier]LANGUAGEjs[OPTIONS(function_option_list)]ASjavascript_codenamed_parameter:param_nameparam_typedeterminism_specifier:{DETERMINISTIC|NOTDETERMINISTIC}

To create a Python UDF, use the following syntax:

CREATE[ORREPLACE]FUNCTION[IFNOTEXISTS][project_name.]dataset_name.function_name([named_parameter[,...]])RETURNSdata_typeLANGUAGEpython[WITHCONNECTIONconnection_path]OPTIONS(function_option_list)ASpython_codenamed_parameter:param_nameparam_type

To create a remote function, use the following syntax:

CREATE[ORREPLACE][TEMPORARY|TEMP]FUNCTION[IFNOTEXISTS][[project_name.]dataset_name.]function_name([named_parameter[,...]])RETURNSdata_typeREMOTEWITHCONNECTIONconnection_path[OPTIONS(function_option_list)]named_parameter:param_nameparam_type

Routine names must contain only letters, numbers, and underscores, and be at most 256 characters long.

Arguments

• OR REPLACE: Replaces any function with the same name if it exists. Cannot appear with IF NOT EXISTS.

• IF NOT EXISTS: If any dataset exists with the same name, the CREATE statement has no effect. Cannot appear with OR REPLACE.

• TEMP or TEMPORARY: Creates a temporary function. If the clause is not present, the statement creates a persistent UDF. You can reuse persistent UDFs across multiple queries, whereas you can only use temporary UDFs in a single query, script, session, or procedure.

• project_name: For persistent functions, the name of the project where you are creating the function. Defaults to the project that runs the DDL query. Do not include the project name for temporary functions.

• dataset_name: For persistent functions, the name of the dataset where you are creating the function. Defaults to the defaultDataset in the request. Do not include the dataset name for temporary functions.

• function_name: The name of the function.

• named_parameter: A comma-separated param_name and param_type pair. The value of param_type is a BigQuery data type. For a SQL UDF, the value of param_type can also be ANY TYPE.

• determinism_specifier: Applies only to JavaScript UDFs. Provides a hint to BigQuery as to whether the query result can be cached. Can be one of the following values:

  • DETERMINISTIC: The function always returns the same result when passed the same arguments. The query result is potentially cacheable. For example, if the function add_one(i) always returns i + 1, the function is deterministic.

  • NOT DETERMINISTIC: The function does not always return the same result when passed the same arguments, and therefore is not cacheable. For example, if the functionj add_random(i) returns i + rand(), the function is not deterministic and BigQuery does not use cached results.

    If all of the invoked functions are DETERMINISTIC, BigQuery tries to cache the result, unless the results can't be cached for other reasons. For more information, see Using cached query results.

• data_type: The data type that the function returns.

  • If the function is defined in SQL, then the RETURNS clause is optional. If the RETURNS clause is omitted, then BigQuery infers the result type of the function from the SQL function body when a query calls the function.

  • If the function is defined in JavaScript, then the RETURNS clause is required. For more information about allowed values for data_type, see Supported JavaScript UDF data types.

• sql_expression: The SQL expression that defines the function.

• function_option_list: A list of options for creating the function.

• javascript_code: The definition of a JavaScript function. The value is a string literal. If the code includes quotes and backslashes, it must be either escaped or represented as a raw string. For example, the code return "\n"; can be represented as one of the following:

  • Quoted string"return \"\\n\";". Both quotes and backslashes need to be escaped.
  • Triple quoted string: """return "\\n";""". Backslashes need to be escaped, quotes don't.
  • Raw string: r"""return "\n";""". No escaping is needed.

• python_code: The definition of a Python function. The value is a string literal. If the code includes quotes and backslashes, it must be escaped or represented as a raw string. For example, the code return "\n"; can be represented as one of the following:

  • Quoted string: "return \"\\n\";". Both quotes and backslashes need to be escaped.
  • Triple quoted string: """return "\\n";""". Backslashes need to be escaped, quotes don't.
  • Raw string: r"""return "\n";""". No escaping is needed.

• connection_name: Specifies a connection resource that has credentials for accessing the remote endpoint or for running Python code. Specify the connection name in the form project_name.location.connection_id: If the project name or location contains a dash, enclose the connection name in backticks (`).

function_option_list

The option list specifies options for creating a UDF. The following options are supported:

Required permissions

This statement requires the following IAM permissions:

In addition, the OR REPLACE clause requires bigquery.routines.update permission.

To create a remote function, additional IAM permissions are needed:

Examples

Create a SQL UDF

The following example creates a persistent SQL UDF named multiplyInputs in a dataset named mydataset.

CREATEFUNCTIONmydataset.multiplyInputs(xFLOAT64,yFLOAT64)RETURNSFLOAT64AS(x*y);

Create a JavaScript UDF

The following example creates a temporary JavaScript UDF named multiplyInputs and calls it from inside a SELECT statement.

CREATETEMPFUNCTIONmultiplyInputs(xFLOAT64,yFLOAT64)RETURNSFLOAT64LANGUAGEjsASr""" return x*y;""";SELECTmultiplyInputs(a,b)FROM(SELECT3asa,2asb);

Create a remote function

The following example creates a temporary remote function named tempRemoteMultiplyInputs in US location, using a connection called myconnection in the 'US' region.

CREATETEMPFUNCTIONtempRemoteMultiplyInputs(xFLOAT64,yFLOAT64)RETURNSFLOAT64REMOTEWITHCONNECTIONus.myconnectionOPTIONS(endpoint="https://us-central1-myproject.cloudfunctions.net/multiply");

The following example creates a persistent remote function named remoteMultiplyInputs in a dataset named mydataset using a connection called myconnection. The location and project of the dataset and the connection must match.

CREATEFUNCTIONmydataset.remoteMultiplyInputs(xFLOAT64,yFLOAT64)RETURNSFLOAT64REMOTEWITHCONNECTIONus.myconnectionOPTIONS(endpoint="https://us-central1-myproject.cloudfunctions.net/multiply");

Create a Python UDF

The following example creates a Python UDF named multiplyInputs.

CREATEFUNCTIONmydataset.multiplyInputs(xFLOAT64,yFLOAT64)RETURNSFLOAT64LANGUAGEpythonOPTIONS(entry_point='multiply',runtime_version='python-3.11'packages=['pandas==2.2'])ASr"""import pandas as pddef multiply(df: pd.DataFrame): return df['x'] * df['y']""";

CREATE AGGREGATE FUNCTION statement (SQL)

Creates a new SQL user-defined aggregate function (UDAF).

Syntax

To create a SQL UDAF, use the following syntax:

CREATE[ORREPLACE][{TEMPORARY|TEMP}]AGGREGATEFUNCTION[IFNOTEXISTS]function_path([function_parameter[,...]])[RETURNSdata_type]AS(sql_function_body)[OPTIONS(function_option_list)]function_path:[[project_name.]dataset_name.]function_namefunction_parameter:parameter_namedata_type[NOTAGGREGATE]