Data definition language (DDL) statements in GoogleSQL

Data definition language (DDL) statements let you create and modifyBigQuery resources usingGoogleSQLquery 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 thebigquery.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 rolesbigquery.user,bigquery.jobUser, andbigquery.admin include the requiredbigquery.jobs.create permission.

For more information about IAM roles in BigQuery,seePredefined roles and permissions or theIAM permissions reference.

Run DDL statements

You can run DDL statements by using the Google Cloud console, by using thebq command-line tool, by calling thejobs.query REST API, orprogrammatically using theBigQuery 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 definitionlanguage (DDL) queries based on the number of bytes processed by the query.

For more information about cost estimation, seeEstimate 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, theCREATEstatement has no effect.

• DEFAULT COLLATE collate_specification: When a new table is created in thedataset, the table inherits adefaultcollation specificationunless a collation specification is explicitly specified for a table or acolumn.

  If you remove or change this collation specification later with theALTER SCHEMA statement, this will not change existingcollation specifications in this dataset. If you want to update an existingcollation specification in a dataset, you must alter the column that containsthe 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 creatingthe dataset.

Details

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

For more information about creating a dataset, seeCreating datasets. For information about quotas, seeDataset limits.

schema_option_list

The following options are supported:

Required permissions

This statement requires the followingIAM permissions:

Examples

Creating a new dataset

The following example creates a dataset with a default table expiration and aset 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 nameand 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. Cannotappear withIF NOT EXISTS.

• TEMP | TEMPORARY: Creates atemporary table.

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

• table_name: The name of the table to create. SeeTable path syntax. For temporary tables, do not includethe 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 explicitcollation specification,thecolumn inherits thiscollation specification forSTRING types.

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

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

• partition_expression: An expression that determineshow to partition the table.

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

• connection_name: Specifies aconnection resource that hascredentials for accessing the external data. Specify the connection namein the formPROJECT_ID.LOCATION.CONNECTION_ID. If theproject ID or location contains a dash, enclose the connection name inbackticks (`). To use adefault connection,specifyDEFAULT instead of the connection string containingPROJECT_ID.LOCATION.CONNECTION_ID.

• table_option_list: A list of options for creating thetable.

• query_statement: The query from which the table should be created. For thequery syntax, seeSQL syntax reference.If a collation specification is used on this table, collation passesthrough this query statement.

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

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

Details

CREATE TABLE statements must comply with the following rules:

• Only oneCREATE statement is allowed.
• Either the column list, theAS query_statement clause, or both must bepresent.
• When both the column list and theAS query_statement clause are present,BigQuery ignores the names in theAS query_statementclause and matches the columns with the column list by position.
• When theAS query_statement clause is present and the column list isabsent, BigQuery determines the column names and typesfrom theAS query_statement clause.
• Column names must be specified either through the column list,theAS query_statement clause or schema of the table in theLIKE clause.
• Duplicate column names are not allowed.
• When both theLIKE and theAS query_statement clause are present, thecolumn list in the query statement must match the columns of the tablereferenced by theLIKE clause.
• Table names are case-sensitive unless the dataset they belong to is not.To create a case-insensitive dataset, seeCreating a case-insensitive dataset.To alter a dataset to make it case-insensitive dataset, seeTurning on case insensitivity for a dataset.

Limitations:

• It is not possible to create aningestion-time partitioned tablefrom the result of a query. Instead, use aCREATE TABLE DDL statement tocreate the table, and then use anINSERT DML statementto insert data into it.
• It is not possible to use theOR REPLACE modifier to replace a table witha different kind of partitioning. Instead,DROP the table, and then use aCREATE 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 thesame schema as an existing table.
• CREATE TABLE COPY: Create a table by copyingschema and data from an existing table.

column

(column_name column_schema[, ...]) contains the table'sschema 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|GENERATEDALWAYSAS(generation_expression)STOREDOPTIONS(generation_option_list)][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 adata type, but supports anoptionalNOT NULL constraint for types other thanARRAY.column_schemaalso supports options on top-level columns andSTRUCT fields.

  column_schema can be used only in the column definition list ofCREATE TABLE statements. It cannot be used as a type in expressions.

• simple_type: Anysupported data typeaside fromSTRUCT andARRAY.

  Ifsimple_type is aSTRING, it supports an additional clause forcollation,which defines how a resultingSTRING can be compared and sorted.The syntax looks like this:

  STRINGCOLLATEcollate_specification

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

• default_expression: Thedefault valueassigned to the column. You cannot specifyDEFAULT ifGENERATED ALWAYS ASis specified.

• generation_expression: (Preview)An expression for an automatically generated embedding column.Setting this field enablesautonomous embedding generationon the table. The onlysupportedgeneration_expression syntax is a call to theAI.EMBED function.

  • You can't specifyGENERATED ALWAYS AS ifDEFAULT is specified.
  • Theconnection_id argument toAI.EMBED is required when used in ageneration expression.
  • The type of the column must beSTRUCT<result ARRAY<FLOAT64>, status STRING>.

• generation_option_list: The options for a generated column.The only supported option isasynchronous = TRUE.

• field_list: Represents the fields in a struct.

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

• NOT NULL: When theNOT NULL constraint is present for a column or field,the column or field is created withREQUIRED mode. Conversely, when theNOT NULL constraint is absent, the column or field is created withNULLABLE mode.

  Columns and fields ofARRAY type do not support theNOT NULL modifier. Forexample, acolumn_schema ofARRAY<INT64> NOT NULL is invalid, sinceARRAY columns haveREPEATED mode and can be empty but cannot beNULL.An array element in a table can never beNULL, regardless of whether theNOT NULL constraint is specified. For example,ARRAY<INT64> is equivalenttoARRAY<INT64 NOT NULL>.

  TheNOT NULL attribute of a table'scolumn_schema does not propagatethrough queries over the table. If tableT contains a column declared asx INT64 NOT NULL, for example,CREATE TABLE dataset.newtable AS SELECT x FROM T creates a table nameddataset.newtable in whichx isNULLABLE.

partition_expression

PARTITION BY is an optional clause that controlstable andvector index partitioning.partition_expression is an expression that determines how to partition thetable or vector index. The partition expression can contain the followingvalues:

• _PARTITIONDATE. Partition by ingestion time with daily partitions. Thissyntax cannot be used with theAS query_statement clause.
• DATE(_PARTITIONTIME). Equivalent to_PARTITIONDATE. This syntax cannot beused with theAS query_statement clause.
• <date_column>. Partition by aDATE column with daily partitions.
• DATE({ <timestamp_column> | <datetime_column> }). Partition by aTIMESTAMPorDATETIME column with daily partitions.
• DATETIME_TRUNC(<datetime_column>, { DAY | HOUR | MONTH | YEAR }). Partitionby aDATETIME column with the specified partitioning type.
• TIMESTAMP_TRUNC(<timestamp_column>, { DAY | HOUR | MONTH | YEAR }).Partition by aTIMESTAMP column with the specified partitioning type.
• TIMESTAMP_TRUNC(_PARTITIONTIME, { DAY | HOUR | MONTH | YEAR }). Partitionby ingestion time with the specified partitioning type. This syntax cannot beused with theAS query_statement clause.
• DATE_TRUNC(<date_column>, { MONTH | YEAR }). Partition by aDATE columnwith 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 controlstable clustering.clustering_column_list is a comma-separated list that determines how tocluster the table. The clustering column list can contain a list of up to fourclustering columns.

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

Specify a table option list in the following format:

NAME=VALUE, ...

NAME andVALUE must be one of the following combinations:

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

The constant expressioncannot contain:

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

IfVALUE evaluates toNULL, the corresponding optionNAME in theCREATE TABLE statement is ignored.

column_option_list

Specify a column option list in the following format:

NAME=VALUE, ...

NAME andVALUE must be one of the following combinations:

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

The constant expressioncannot contain:

• A reference to a table
• Subqueries or SQL statements such asSELECT,CREATE, orUPDATE
• 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 theVALUE replaces the existing value of that option for the column, ifthere was one. Setting theVALUE toNULL clears the column's value for thatoption.

Required permissions

This statement requires the followingIAM permissions:

In addition, theOR REPLACE clause requiresbigquery.tables.update andbigquery.tables.updateData permissions.

If theOPTIONS clause includes any expiration options, then thebigquery.tables.delete permission is also required.

Examples

Creating a new table

The following example creates a partitioned table namednewtable inmydataset:

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 datasetname in the example SQL, and enclose the name in backticks ifproject_idcontains special characters:`project_id.dataset.table`. So, instead ofmydataset.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 followingpartition_expression to partition the table:PARTITION BY _PARTITIONDATE. This expression partitions the table usingthe 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
  Note: When you examine the table schema in the Google Cloud console, aSTRUCT is displayed as a RECORD column, and an ARRAY is displayed as aREPEATED column. The STRUCT and ARRAY data types are used to create nestedand repeated data in BigQuery. For more information, seeSpecifying nested and repeated fields.

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 namedtop_words inmydataset from aquery:

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 datasetname in the example SQL, and enclose the name in backticks ifproject_idcontains special characters:`project_id.dataset.table`. So, instead ofmydataset.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: AnARRAY ofSTRUCTs containing 2 fields:word (aSTRING) andword_count (anINT64 with the word count)

  Note: When you examine the table schema in the Google Cloud console, aSTRUCT is displayed as a RECORD column, and an ARRAY is displayed as aREPEATED column. The STRUCT and ARRAY data types are used to create nestedand repeated data in BigQuery. For more information, seeSpecifying nested and repeated fields.

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 namednewtable inmydataset only if notable namednewtable exists inmydataset. If the table name exists in thedataset, 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 datasetname in the example SQL, and enclose the name in backticks ifproject_idcontains special characters:`project_id.dataset.table`. So, instead ofmydataset.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 (aboolean)

  Note: When you examine the table schema in the Google Cloud console, aSTRUCT is displayed as a RECORD, and an ARRAY is displayed as REPEATED. TheSTRUCT and ARRAY data types are used to create nested and repeated data inBigQuery. For more information, seeSpecifying nested and repeated fields.

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 namednewtable inmydataset, and ifnewtable exists inmydataset, 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 datasetname in the example SQL, and enclose the name in backticks ifproject_idcontains special characters:`project_id.dataset.table`. So, instead ofmydataset.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 (aboolean)

  Note: When you examine the table schema in the Google Cloud console, aSTRUCT is displayed as a RECORD, and an ARRAY is displayed as REPEATED. TheSTRUCT and ARRAY data types are used to create nested and repeated data inBigQuery. For more information, seeSpecifying nested and repeated fields.

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 withREQUIRED columns

The following example creates a table namednewtable inmydataset. TheNOTNULL modifier in the column definition list of aCREATE TABLE statementspecifies that a column or field is created inREQUIRED 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 datasetname in the example SQL, and enclose the name in backticks ifproject_idcontains special characters:`project_id.dataset.table`. So, instead ofmydataset.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: AREQUIRED integer
• y: AREQUIRED STRUCT containing a (an array of strings), b (aREQUIRED boolean), and c (aNULLABLE float)

• z: ANULLABLE string

  Note: When you examine the table schema in the Google Cloud console, aSTRUCT is displayed as a RECORD, and an ARRAY is displayed as REPEATED. TheSTRUCT and ARRAY data types are used to create nested and repeated data inBigQuery. For more information, seeSpecifying nested and repeated fields.

Creating a table with collation support

The following examples create a table namednewtable inmydataset withcolumnsa,b,c, and a struct with fieldsx andy.

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

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

Onlyb andy 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 namednewtable inmydataset. Theparameters in parentheses specify that the column contains a parameterized datatype. SeeParameterized Data Typesfor 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 datasetname in the example SQL, and enclose the name in backticks ifproject_idcontains special characters:`project_id.dataset.table`. Instead ofmydataset.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 (aparameterized 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 apartitioned tablenamednewtable inmydataset using aDATE 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 datasetname in the example SQL, and enclose the name in backticks ifproject_idcontains special characters:`project_id.dataset.table`. So, instead ofmydataset.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 apartitioned tablenameddays_with_rain inmydataset using aDATE 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 datasetname in the example SQL, and enclose the name in backticks ifproject_idcontains special characters:`project_id.dataset.table`. So, instead ofmydataset.days_with_rain, your table qualifier might be`myproject.mydataset.days_with_rain`.

The table schema contains 2 columns:

• date: TheDATE of data collection
• station_name: The name of the weather station as aSTRING
• prcp: The amount of precipitation in inches as aFLOAT64

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 aclustered tablenamedmyclusteredtable inmydataset. The table is apartitioned table,partitioned by a truncatedTIMESTAMP column and clustered by aSTRING columnnamedcustomer_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 datasetname in the example SQL, and enclose the name in backticks ifproject_id contains special characters:`project_id.dataset.table`. So, instead ofmydataset.myclusteredtable, your table qualifier might be`myproject.mydataset.myclusteredtable`.

The table schema contains 3 columns:

• input_timestamp: The time of data collection as aTIMESTAMP
• customer_id: The customer ID as aSTRING
• transaction_amount: The transaction amount asNUMERIC

The table option list specifies the:

• Partition expiration: 3 days
• Description:A table clustered by customer_id

Example 2

The following example creates aclustered tablenamedmyclusteredtable inmydataset. The table is aningestion-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 datasetname in the example SQL, and enclose the name in backticks ifproject_id contains special characters:`project_id.dataset.table`. So, instead ofmydataset.myclusteredtable, your table qualifier might be`myproject.mydataset.myclusteredtable`.

The table schema contains 2 columns:

• customer_id: The customer ID as aSTRING
• transaction_amount: The transaction amount asNUMERIC

The table option list specifies the:

• Partition expiration: 3 days
• Description:A table clustered by customer_id

Example 3

The following example creates aclustered tablenamedmyclusteredtable inmydataset. 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 datasetname in the example SQL, and enclose the name in backticks ifproject_id contains special characters:`project_id.dataset.table`. So, instead ofmydataset.myclusteredtable, your table qualifier might be`myproject.mydataset.myclusteredtable`.

The table schema contains 2 columns:

• customer_id: The customer ID as aSTRING
• transaction_amount: The transaction amount asNUMERIC

The table option list specifies the:

• Description:A table clustered by customer_id

Creating a table with autonomous embedding generation

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

The following example creates a table namedembedded_table inmydataset withanautonomous embedding generationcolumnembedding that generates embeddings fromcontent column:

CREATETABLEmydataset.embedded_table(idINT64,contentSTRING,embeddingSTRUCT,statusSTRING>GENERATEDALWAYSAS(AI.EMBED(content,connection_id=>"US.embed_connection",endpoint=>"text-embedding-005"))STOREDOPTIONS(asynchronous=true));

Creating a clustered table from the result of a query

Example 1

The following example creates a partitioned andclustered tablenamedmyclusteredtable inmydataset 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 datasetname in the example SQL, and enclose the name in backticks ifproject_id contains special characters:`project_id.dataset.table`. So, instead ofmydataset.myclusteredtable, your table qualifier might be`myproject.mydataset.myclusteredtable`.

The table schema contains 3 columns:

• input_timestamp: The time of data collection as aTIMESTAMP
• customer_id: The customer ID as aSTRING
• transaction_amount: The transaction amount asNUMERIC

The table option list specifies the:

• Partition expiration: 3 days
• Description:A table clustered by customer_id

Example 2

The following example creates aclustered tablenamedmyclusteredtable inmydataset using the result of a query. The tableis 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 datasetname in the example SQL, and enclose the name in backticks ifproject_id contains special characters:`project_id.dataset.table`. So, instead ofmydataset.myclusteredtable, your table qualifier might be`myproject.mydataset.myclusteredtable`.

The table schema contains 2 columns:

• customer_id: The customer ID as aSTRING
• transaction_amount: The transaction amount asNUMERIC

The table option list specifies the:

• Description:A table clustered by customer_id

Creating a temporary table

The following example creates a temporary table namedExample and insertsvalues 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 namedmyawsdataset.orders thatreferences data fromAmazon S3.You want to transfer data from that table to aBigQuery tablemyotherdataset.shipments in the US multi-region.

First, display information about themyawsdataset.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 themyotherdataset.shipments table:

bqshowmyotherdataset.shipments

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

  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 theCREATE TABLE AS SELECT statement you can selectively load datato themyotherdataset.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 destinationtable using theINSERT 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-timepartitioned 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 theCREATE TABLE statement and has the samelimitations.Other than the use of theLIKE clause in place of a column list,the syntax is identical to theCREATE TABLE syntax.

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

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

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

Required permissions

This statement requires the followingIAM permissions:

In addition, theOR REPLACE clause requiresbigquery.tables.update andbigquery.tables.updateData permissions.

If theOPTIONS clause includes any expiration options, then thebigquery.tables.delete permission is also required.

Examples

Example 1

The following example creates a new table namednewtable inmydataset with the same metadata assourcetable:

CREATETABLEmydataset.newtableLIKEmydataset.sourcetable

Example 2

The following example creates a new table namednewtable inmydataset with the same metadata assourcetable and the data from theSELECT 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, atable clone, or atable snapshot.

Syntax

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

Details

This statement is a variant of theCREATE TABLE statement and has the samelimitations.Other than the use of theCOPY clause in place of a column list,the syntax is identical to theCREATE TABLE syntax.

TheCREATE TABLE COPY statement copies both the metadata and data from thesource table.

The new table inherits partitioning and clustering from the source table. Bydefault, the table options metadata from the source table are also inherited,but you can override table options by using theOPTIONS clause. The behavioris equivalent to runningALTER 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 followingIAM permissions:

In addition, theOR REPLACE clause requiresbigquery.tables.update andbigquery.tables.updateData permissions.

If theOPTIONS clause includes any expiration options, then thebigquery.tables.delete permission is also required.

CREATE SNAPSHOT TABLE statement

Creates atable snapshot based on asource table. The source table can be a table, atable clone, ora 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 othertable resourceexists with the same name, theCREATE 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. SeeTable path syntax.

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

  If the source table is a standard table, then BigQuery createsa 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 timespecified bytimestamp_expression. It can only be used when creating asnapshot of a table; it can't be used when making a copy of a table snapshot.

• snapshot_option_list: Additional table snapshotcreation options such as alabel and an expirationtime.

Details

CREATE SNAPSHOT TABLE statements must comply with the following rules:

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

snapshot_option_list

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

Specify a table snapshot option list in the following format:

NAME=VALUE, ...

NAME andVALUE 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 asSELECT,CREATE, andUPDATE
• 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

IfVALUE evaluates toNULL, the corresponding optionNAME in theCREATE SNAPSHOT TABLE statement is ignored.

Required permissions

This statement requires the followingIAM permissions:

Examples

Create a table snapshot: fail if it already exists

The following example creates a table snapshot of the tablemyproject.mydataset.mytable. The table snapshot is created in the datasetmydataset and is namedmytablesnapshot:

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 followingerror 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 tablemyproject.mydataset.mytable. The table snapshot is created in the datasetmydataset and is namedmytablesnapshot:

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 alreadyexists in the dataset, then no action is taken, and no error is returned.

For information about restoring table snapshots, seeCREATE TABLE CLONE.

For information about removing table snapshots, seeDROP SNAPSHOT TABLE.

CREATE TABLE CLONE statement

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

Syntax

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

Details

Other than the use of theCLONE clause in place of a column list, the syntaxis identical to theCREATE TABLE syntax.

Arguments

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

• destination_table_name: The name of the table that you want to create.The table name mustbe 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 specifyadditional table creation options such as alabel andan expiration time.

• source_table_name: The name of the source table.

CREATE TABLE CLONE statements must comply with the following rules:

• Only oneCREATE statement is allowed.
• The table that is being cloned must be a table, a table clone, ora table snapshot.

OPTIONS

CREATE TABLE CLONE options are the same asCREATE TABLE options.

Required permissions

This statement requires the followingIAM permissions:

If theOPTIONS clause includes any expiration options, then thebigquery.tables.delete permission is also required.

Examples

Restore a table snapshot: fail if destination table already exists

The following example creates the tablemyproject.mydataset.mytable from the table snapshotmyproject.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 isreturned:

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 clonemyproject.mydataset.mytableclone based on the tablemyproject.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 erroris returned.

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

For information about creating a snapshot of a table, seeCREATE 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. Cannotappear withIF NOT EXISTS.

• IF NOT EXISTS: If a view or othertable resourceexists with the same name, theCREATE statement has no effect. Cannot appearwithOR REPLACE.

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

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

• view_option_list: Additional view creation optionssuch as alabel and an expiration time.

• query_expression: The GoogleSQL query expression used to define theview.

Details

CREATE VIEW statements must comply with the following rules:

• Only oneCREATE 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 theage column is dropped from themydataset.people table, then the view created in the previous example becomes invalid.

view_column_option_list

Theview_column_option_list lets you specify optional top-level columnoptions. Column options for a view have the same syntax and requirements asfor a table, but with a different list ofNAME andVALUE fields:

view_option_list

The option list allows you to set view options such as alabel and an expiration time. You can include multipleoptions using a comma-separated list.

Specify a view option list in the following format:

NAME=VALUE, ...

NAME andVALUE must be one of the following combinations:

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

The constant expressioncannot contain:

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

IfVALUE evaluates toNULL, the corresponding optionNAME in theCREATE VIEW statement is ignored.

privacy_policy

The following policies are available in theprivacy_policy view optionto createanalysis rules. A policy representsa 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  }}'

CREATEVIEWmyProject.myDataset.myViewASSELECT*FROManotherDataset.myTable;

This statement requires the followingIAM permissions:

In addition, theOR REPLACE clause requiresbigquery.tables.updatepermission.

If theOPTIONS clause includes an expiration time, then thebigquery.tables.delete permission is also required.

Examples

Creating a new view

The following example creates a view namednewview inmydataset:

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 namednewview inmydataset only if noview namednewview exists inmydataset. If the view name exists in thedataset, 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 namednewview inmydataset, and ifnewview exists inmydataset, it is overwritten using the specified queryexpression.

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 FROMmyproject.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 namednewview inmydataset. This viewdefinition provides the column description for each column inmytable.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 withIF NOT EXISTS.

• IF NOT EXISTS: If a materialized view or othertable resourceexists with the same name, theCREATE statement has no effect. Cannot appearwithOR REPLACE.

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

  If theproject_name is omitted from the materialized view name, or it is thesame as the project that runs this DDL query, then the latter is also used asthe default project for references to tables, functions, and other resourcesinquery_expression. The default project of the references is fixed and doesnot depend on the future queries that invoke the new materialized view.Otherwise, all references inquery_expression must be qualified withproject names.

  The materialized view name must be unique per dataset.

• partition_expression: An expression that determineshow to partition the table. A materialized view can only be partitioned in thesame way as the table inquery expression (thebase table) is partitioned.

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

• materialized_view_option_list: Allows youto specify additional materialized view options such as a whether refresh isenabled, the refresh interval, alabel, and anexpiration time.

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

Details

CREATE MATERIALIZED VIEW statements must comply with the following rules:

• Only oneCREATE statement is allowed.

Default project in materialized view body

If the materialized view is created in the same project used to run theCREATE MATERIALIZED VIEWstatement, the materialized view bodyquery_expression can reference entities withoutspecifying the project; the default project is the projectwhich owns the materialized view. Consider the sample query below.

CREATEMATERIALIZEDVIEWmyProject.myDataset.myViewASSELECT*FROManotherDataset.myTable;

After running the aboveCREATE MATERIALIZED VIEW query in the projectmyProject, you canrun the querySELECT * FROM myProject.myDataset.myView. Regardless of the project youchoose to run thisSELECT query, the referenced tableanotherDataset.myTableis always resolved against projectmyProject.

If the materialized view is not created in the same project used to run theCREATE VIEWstatement, then all references in the materialized view bodyquery_expression must bequalified with project IDs. For instance, the preceding sampleCREATE MATERIALIZED VIEW queryis invalid if it runs in a project different frommyProject.

materialized_view_option_list

The option list allows you to set materialized view options such as a whetherrefresh is enabled. the refresh interval, alabel andan expiration time. You can include multiple options using a comma-separatedlist.

Specify a materialized view option list in the following format:

NAME=VALUE, ...

NAME andVALUE must be one of the following combinations:

Required permissions

This statement requires the followingIAM permissions:

In addition, theOR REPLACE clause requiresbigquery.tables.updatepermission.

If theOPTIONS clause includes any expiration options, then thebigquery.tables.delete permission is also required.

Examples

Creating a new materialized view

The following example creates a materialized view namednew_mv inmydataset:

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

Already Exists:project_id:dataset.materialized_view

When you use a DDL statement to create a materialized view, you must specify theproject, dataset, and materialized view in the following format:`project_id.dataset.materialized_view`(including the backticks ifproject_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 namednew_mv inmydatasetonly if no materialized view namednew_mv exists inmydataset. If thematerialized view name exists in the dataset, no error is returned, and noaction 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 namednew_mv inmydataset,partitioned by thecol_datetime column and clusteredby thecol_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 thecol_datetime column. For more information, seeWorking with partitioned and clustered tables.

CREATE MATERIALIZED VIEW AS REPLICA OF statement

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

For more information, seeCreate 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, intable path syntax. If the project name is omitted from thematerialized 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 arereplicating, in table path syntax. The source materialized view must be overan Amazon S3 BigLake table, and must be authorized on thedataset 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 followingIAM 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 namedmv_replicainbq_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 anexternal data source at the dataset level. For more information about creatingfederated 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, theCREATEstatement has no effect.

• dataset_name: The name of the dataset to create.

• connection_name: Specifies aconnection resource that hascredentials for accessing the external data. Specify the connection namein the formPROJECT_ID.LOCATION.CONNECTION_ID. If theproject ID or location contains a dash, enclose the connection name inbackticks (`).

• external_schema_option_list: A list ofoptions for creating the federated dataset.

Details

The dataset is created in the location that you specify in the query settings.For more information, seeSpecify 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 AWSlocations.

For more information about creating a dataset, seeCreate datasets. For information about quotas, seedataset limits.

external_schema_option_list

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

The following options are supported:

Required permissions

This statement requires the followingIAM 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 ofBigQuery storage. For more information about external tables, seeIntroduction 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 withIF NOT EXISTS.

• IF NOT EXISTS: If an external table or othertable resourceexists with the same name, theCREATE statement has no effect. Cannot appearwithOR REPLACE.

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

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

• column_schema: Specifies the schema of the column. It uses the samesyntax as thecolumn_schema definition intheCREATE TABLE statement. If you don't includethis clause, BigQuery detects the schema automatically.

• connection_name: Specifies aconnection resource that hascredentials for accessing the external data. Specify the connection namein the formPROJECT_ID.LOCATION.CONNECTION_ID. If theproject ID or location contains a dash, enclose the connection name inbackticks (`). To use adefault connection,specifyDEFAULT instead of the connection string containingPROJECT_ID.LOCATION.CONNECTION_ID.

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

• partition_column_type: The partition column type.

• external_table_option_list: A list of optionsfor creating the external table.

Details

TheCREATE EXTERNAL TABLE statement does not support creating temporaryexternal tables.

To create an externally partitioned table, use theWITH PARTITION COLUMNSclause to specify the partition schema details. BigQueryvalidates the column definitions against the external data location. The schemadeclaration must strictly follow the ordering of the fields in the externalpath. For more information about external partitioning, seeQuerying externally partitioned data.

external_table_option_list

The option list specifies options for creating the external table. Theformatanduris options are required. Specify the option list in the followingformat:NAME=VALUE, ...

Required permissions

This statement requires the followingIAM permissions:

In addition, theOR REPLACE clause requiresbigquery.tables.updatepermission.

If theOPTIONS clause includes an expiration time, then thebigquery.tables.delete permission is also required.

Examples

The following example creates a BigLake table and explicitlyspecifies the schema. It also specifies refreshing metadata cache automaticallyat 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 dataformat 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 explicitlyspecifies the schema. It also specifies the field delimiter ('|') and sets themaximum 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 isgs://bucket/path/field_1=first/field_2=1/data.parquet, the partition columns are detected asfield_1 (STRING) andfield_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 patterngs://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 newuser-defined function(UDF). BigQuery supports UDFs written in SQL, JavaScript, orPython.

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:

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

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. Cannotappear withIF NOT EXISTS.

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

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

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

• dataset_name: For persistent functions, the name of the dataset whereyou are creating the function. Defaults to thedefaultDataset in therequest. Do not include the dataset name for temporary functions.

• function_name: The name of the function.

• named_parameter: A comma-separatedparam_nameandparam_type pair. The value ofparam_type is aBigQuerydata type. For a SQL UDF, thevalue ofparam_type can also beANY TYPE.

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

  • DETERMINISTIC: The function always returns the same result when passedthe same arguments. The query result is potentially cacheable. Forexample, if the functionadd_one(i) always returnsi + 1, thefunction is deterministic.

  • NOT DETERMINISTIC: The function does not always return the same resultwhen passed the same arguments, and therefore is not cacheable. Forexample, if the functionjadd_random(i) returnsi + rand(), thefunction is not deterministic and BigQuery does not usecached results.

    If all of the invoked functions areDETERMINISTIC,BigQuery tries to cache the result, unless the resultscan't be cached for other reasons. For more information, seeUsing cached query results.

• data_type: The data type that the function returns.

  • If the function is defined in SQL, then theRETURNS clause isoptional. If theRETURNS clause is omitted, then BigQueryinfers the result type of the function from the SQL function body when aquery calls the function.

  • If the function is defined in JavaScript, then theRETURNS clause isrequired. For more information about allowed values fordata_type,seeSupportedJavaScript UDF data types.

• sql_expression: The SQL expression that defines the function.

• function_option_list: A list of options forcreating the function.

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

  • Quoted string"return \"\\n\";". Both quotes and backslashes needto be escaped.
  • Triple quoted string:"""return "\\n";""". Backslashes needto 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 astring literal.If the code includes quotes and backslashes, it must be escaped orrepresented as a raw string. For example, the codereturn "\n"; can berepresented as one of the following:

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

• connection_name: Specifies aconnection resource that hascredentials for accessing the remote endpoint or for running Python code.Specify the connection name in the formproject_name.location.connection_id: If theproject name or location contains a dash, enclose the connection name inbackticks (`).

function_option_list

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