Console

1. In the Google Cloud console, go to theBigQuery page.

   Go to BigQuery

2. In the left pane, clickexploreExplorer.
3. In theExplorer pane, expand your project, clickDatasets, and then select a dataset.
4. In theDataset info section, clickadd_boxCreate table.
5. In theCreate table pane, specify the following details:
1. In theSource section, selectEmpty table in theCreate table from list.
2. In theDestination section, specify the following details:
   1. ForDataset, select the dataset in which you want to create the table.
   2. In theTable field, enter the name of the table that you want to create.
   3. Verify that theTable type field is set toNative table.
3. In theSchema section, enter theschema definition. The schema must include aDATE,TIMESTAMP, orDATETIME column for the partitioning column. For more information, seeSpecifying a schema. You can enter schema information manually by using one of the following methods:
   • Option 1: ClickEdit as text and paste the schema in the form of a JSON array. When you use a JSON array, you generate the schema using the same process ascreating a JSON schema file. You can view the schema of an existing table in JSON format by entering the following command:

     bqshow--format=prettyjsondataset.table

   • Option 2: Clickadd_boxAdd field and enter the table schema. Specify each field'sName,Type, andMode.
4. In thePartition and cluster settings section, in thePartitioning list, selectPartition by field, and then choose the partitioning column. This option is only available if the schema contains aDATE,TIMESTAMP, orDATETIME column.
5. Optional: To require a partition filter on all queries for this table, select theRequire partition filter checkbox. A partition filter can reduce cost and improve performance. For more information, seeSet partition filter requirements.
6. Select thePartitioning type. OnlyBy day is supported.
7. Optional: In theAdvanced options section, if you want to use a customer-managed encryption key, then select theUse a customer-managed encryption key (CMEK) option. By default, BigQueryencrypts customer content stored at rest by using a Google-owned and Google-managed encryption key.
8. ClickCreate table.

SQL

To create a time-unit column-partitioned table, use theCREATE TABLE DDL statementwith aPARTITION BY clause.

The following example creates a table with daily partitions based on thetransaction_date column:

1. In the Google Cloud console, go to theBigQuery page.

   Go to BigQuery

2. In the query editor, enter the following statement:

   CREATETABLEmydataset.newtable(transaction_idINT64,transaction_dateDATE)PARTITIONBYtransaction_dateOPTIONS(partition_expiration_days=3,require_partition_filter=TRUE);

   Use theOPTIONS clauseto set table options such as thepartition expirationand thepartition filter requirements.

3. Clickplay_circleRun.

For more information about how to run queries, seeRun an interactive query.

The default partitioning type forDATE columns is daily partitioning. Tospecify a different partitioning type, include theDATE_TRUNCfunction in thePARTITION BY clause. For example, the following querycreates a table with monthly partitions:

CREATETABLEmydataset.newtable(transaction_idINT64,transaction_dateDATE)PARTITIONBYDATE_TRUNC(transaction_date,MONTH)OPTIONS(partition_expiration_days=3,require_partition_filter=TRUE);

You can also specify aTIMESTAMP orDATETIME column as the partitioningcolumn. In that case, include theTIMESTAMP_TRUNC orDATETIME_TRUNCfunction in thePARTITION BY clause to specify the partition type. Forexample, the following statement creates a table with daily partitions basedon aTIMESTAMP column:

CREATETABLEmydataset.newtable(transaction_idINT64,transaction_tsTIMESTAMP)PARTITIONBYTIMESTAMP_TRUNC(transaction_ts,DAY)OPTIONS(partition_expiration_days=3,require_partition_filter=TRUE);

bq

1. In the Google Cloud console, activate Cloud Shell.

   Activate Cloud Shell

   At the bottom of the Google Cloud console, aCloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.

2. Use thebq mkcommand with the--table flag (or-t shortcut):

   bqmk\--table\--schemaSCHEMA\--time_partitioning_fieldCOLUMN\--time_partitioning_typeUNIT_TIME\--time_partitioning_expirationEXPIRATION_TIME\--require_partition_filter=BOOLEANPROJECT_ID:DATASET.TABLE

   Replace the following:

   • SCHEMA: A schema definition in the formatcolumn:data_type,column:data_type or the path to a JSONschema file on your local machine. For more information, seeSpecifying a schema.
   • COLUMN: The name of the partitioning column. In the tableschema, this column must be aTIMESTAMP,DATETIME, orDATE type.
   • UNIT_TIME: The partitioning type. Supported valuesincludeDAY,HOUR,MONTH, orYEAR.
   • EXPIRATION_TIME: The expiration time for the table'spartitions, in seconds. The--time_partitioning_expiration flag isoptional. For more information, seeSet the partition expiration.
   • BOOLEAN: Iftrue then queries on this table must includea partition filter. The--require_partition_filter flag is optional.For more information, seeSet partition filter requirements.
   • PROJECT_ID: The project ID. If omitted, your default projectis used.
   • DATASET: The name of a dataset in your project.
   • TABLE: The name of the table to create.

   For other command-line options,seebq mk.

   The following example creates a table namedmytable that is partitionedon thets column, using hourly partitioning. The partition expiration is259,200 seconds (3 days).

   bqmk\-t\--schema'ts:TIMESTAMP,qtr:STRING,sales:FLOAT'\--time_partitioning_fieldts\--time_partitioning_typeHOUR\--time_partitioning_expiration259200\mydataset.mytable

Terraform

Use thegoogle_bigquery_tableresource.

To authenticate to BigQuery, set up Application DefaultCredentials. For more information, seeSet up authentication for client libraries.

The following example creates a table namedmytable that is partitionedby day:

resource "google_bigquery_dataset" "default" {  dataset_id                      = "mydataset"  default_partition_expiration_ms = 2592000000  # 30 days  default_table_expiration_ms     = 31536000000 # 365 days  description                     = "dataset description"  location                        = "US"  max_time_travel_hours           = 96 # 4 days  labels = {    billing_group = "accounting",    pii           = "sensitive"  }}resource "google_bigquery_table" "default" {  dataset_id          = google_bigquery_dataset.default.dataset_id  table_id            = "mytable"  deletion_protection = false # set to "true" in production  time_partitioning {    type          = "DAY"    field         = "Created"    expiration_ms = 432000000 # 5 days  }  require_partition_filter = true  schema = <<EOF[  {    "name": "ID",    "type": "INT64",    "mode": "NULLABLE",    "description": "Item ID"  },  {    "name": "Created",    "type": "TIMESTAMP",    "description": "Record creation timestamp"  },  {    "name": "Item",    "type": "STRING",    "mode": "NULLABLE"  }]EOF}

To apply your Terraform configuration in a Google Cloud project, complete the steps in the following sections.

Prepare Cloud Shell

1. LaunchCloud Shell.

2. Set the default Google Cloud project where you want to apply your Terraform configurations.

   You only need to run this command once per project, and you can run it in any directory.

   export GOOGLE_CLOUD_PROJECT=PROJECT_ID

   Environment variables are overridden if you set explicit values in the Terraform configuration file.

Prepare the directory

Each Terraform configuration file must have its own directory (alsocalled aroot module).

1. InCloud Shell, create a directory and a new file within that directory. The filename must have the.tf extension—for examplemain.tf. In this tutorial, the file is referred to asmain.tf.

   mkdirDIRECTORY && cdDIRECTORY && touch main.tf

2. If you are following a tutorial, you can copy the sample code in each section or step.

   Copy the sample code into the newly createdmain.tf.

   Optionally, copy the code from GitHub. This is recommended when the Terraform snippet is part of an end-to-end solution.

3. Review and modify the sample parameters to apply to your environment.
4. Save your changes.
5. Initialize Terraform. You only need to do this once per directory.

   terraform init

   Optionally, to use the latest Google provider version, include the-upgrade option:

   terraform init -upgrade

Apply the changes

1. Review the configuration and verify that the resources that Terraform is going to create or update match your expectations:

   terraform plan

   Make corrections to the configuration as necessary.

2. Apply the Terraform configuration by running the following command and enteringyes at the prompt:

   terraform apply

   Wait until Terraform displays the "Apply complete!" message.

3. Open your Google Cloud project to view the results. In the Google Cloud console, navigate to your resources in the UI to make sure that Terraform has created or updated them.

API

Call thetables.insertmethod with a definedtable resourcethat specifies thetimePartitioning property and theschema property.

Go

import("context""fmt""time""cloud.google.com/go/bigquery")// createTablePartitioned demonstrates creating a table and specifying a time partitioning configuration.funccreateTablePartitioned(projectID,datasetID,tableIDstring)error{// projectID := "my-project-id"// datasetID := "mydatasetid"// tableID := "mytableid"ctx:=context.Background()client,err:=bigquery.NewClient(ctx,projectID)iferr!=nil{returnfmt.Errorf("bigquery.NewClient: %v",err)}deferclient.Close()sampleSchema:=bigquery.Schema{{Name:"name",Type:bigquery.StringFieldType},{Name:"post_abbr",Type:bigquery.IntegerFieldType},{Name:"date",Type:bigquery.DateFieldType},}metadata:=&bigquery.TableMetadata{TimePartitioning:&bigquery.TimePartitioning{Field:"date",Expiration:90*24*time.Hour,},Schema:sampleSchema,}tableRef:=client.Dataset(datasetID).Table(tableID)iferr:=tableRef.Create(ctx,metadata);err!=nil{returnerr}returnnil}

Java

importcom.google.cloud.bigquery.BigQuery;importcom.google.cloud.bigquery.BigQueryException;importcom.google.cloud.bigquery.BigQueryOptions;importcom.google.cloud.bigquery.Field;importcom.google.cloud.bigquery.Schema;importcom.google.cloud.bigquery.StandardSQLTypeName;importcom.google.cloud.bigquery.StandardTableDefinition;importcom.google.cloud.bigquery.TableId;importcom.google.cloud.bigquery.TableInfo;importcom.google.cloud.bigquery.TimePartitioning;// Sample to create a partition tablepublicclassCreatePartitionedTable{publicstaticvoidmain(String[]args){// TODO(developer): Replace these variables before running the sample.StringdatasetName="MY_DATASET_NAME";StringtableName="MY_TABLE_NAME";Schemaschema=Schema.of(Field.of("name",StandardSQLTypeName.STRING),Field.of("post_abbr",StandardSQLTypeName.STRING),Field.of("date",StandardSQLTypeName.DATE));createPartitionedTable(datasetName,tableName,schema);}publicstaticvoidcreatePartitionedTable(StringdatasetName,StringtableName,Schemaschema){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();TableIdtableId=TableId.of(datasetName,tableName);TimePartitioningpartitioning=TimePartitioning.newBuilder(TimePartitioning.Type.DAY).setField("date")//  name of column to use for partitioning.setExpirationMs(7776000000L)// 90 days.build();StandardTableDefinitiontableDefinition=StandardTableDefinition.newBuilder().setSchema(schema).setTimePartitioning(partitioning).build();TableInfotableInfo=TableInfo.newBuilder(tableId,tableDefinition).build();bigquery.create(tableInfo);System.out.println("Partitioned table created successfully");}catch(BigQueryExceptione){System.out.println("Partitioned table was not created. \n"+e.toString());}}}

Node.js

// Import the Google Cloud client libraryconst{BigQuery}=require('@google-cloud/bigquery');constbigquery=newBigQuery();asyncfunctioncreateTablePartitioned(){// Creates a new partitioned table named "my_table" in "my_dataset"./**   * TODO(developer): Uncomment the following lines before running the sample.   */// const datasetId = "my_dataset";// const tableId = "my_table";constschema='Name:string, Post_Abbr:string, Date:date';// For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resourceconstoptions={schema:schema,location:'US',timePartitioning:{type:'DAY',expirationMS:'7776000000',field:'date',},};// Create a new table in the datasetconst[table]=awaitbigquery.dataset(datasetId).createTable(tableId,options);console.log(`Table${table.id} created with partitioning: `);console.log(table.metadata.timePartitioning);}

Python

fromgoogle.cloudimportbigqueryclient=bigquery.Client()# Use format "your-project.your_dataset.your_table_name" for table_idtable_id=your_fully_qualified_table_idschema=[bigquery.SchemaField("name","STRING"),bigquery.SchemaField("post_abbr","STRING"),bigquery.SchemaField("date","DATE"),]table=bigquery.Table(table_id,schema=schema)table.time_partitioning=bigquery.TimePartitioning(type_=bigquery.TimePartitioningType.DAY,field="date",# name of column to use for partitioningexpiration_ms=1000*60*60*24*90,)# 90 daystable=client.create_table(table)print(f"Created table{table.project}.{table.dataset_id}.{table.table_id}, "f"partitioned on column{table.time_partitioning.field}.")

Console

1. Open the BigQuery page in the Google Cloud console.

   Go to the BigQuery page

2. In theExplorer panel, expand your project and select a dataset.

3. Expand themore_vertActions option and clickOpen.

4. In the details panel, clickCreate tableadd_box.

5. On theCreate table page, in theSource section, selectEmptytable.

6. In theDestination section:

   • ForDataset name, choose the appropriate dataset.
   • In theTable name field, enter the name of the table.
   • Verify thatTable type is set toNative table.

7. In theSchema section, enter theschemadefinition.

8. In thePartition and cluster settings section, forPartitioning,clickPartition by ingestion time.

9. (Optional) To require a partition filter on all queries for this table,select theRequire partition filter checkbox. Requiring a partitionfilter can reduce cost and improve performance. For more information, seeSet partition filter requirements.

10. ClickCreate table.

SQL

To create an ingestion-time partitioned table, use theCREATE TABLE statementwith aPARTITION BY clausethat partitions on_PARTITIONDATE.

The following example creates a table with daily partitions:

1. In the Google Cloud console, go to theBigQuery page.

   Go to BigQuery

2. In the query editor, enter the following statement:

   CREATETABLEmydataset.newtable(transaction_idINT64)PARTITIONBY_PARTITIONDATEOPTIONS(partition_expiration_days=3,require_partition_filter=TRUE);

   Use theOPTIONS clauseto set table options such as thepartition expirationand thepartition filter requirements.

3. Clickplay_circleRun.

For more information about how to run queries, seeRun an interactive query.

The default partitioning type for ingestion-time partitioning is dailypartitioning. To specify a different partitioning type, include theDATE_TRUNCfunction in thePARTITION BY clause. For example, the following querycreates a table with monthly partitions:

CREATETABLEmydataset.newtable(transaction_idINT64)PARTITIONBYDATE_TRUNC(_PARTITIONTIME,MONTH)OPTIONS(partition_expiration_days=3,require_partition_filter=TRUE);

bq

1. In the Google Cloud console, activate Cloud Shell.

   Activate Cloud Shell

   At the bottom of the Google Cloud console, aCloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.

2. Use thebq mkcommand with the--table flag (or-t shortcut):

   bqmk\--table\--schemaSCHEMA\--time_partitioning_typeUNIT_TIME\--time_partitioning_expirationEXPIRATION_TIME\--require_partition_filter=BOOLEAN\PROJECT_ID:DATASET.TABLE

   Replace the following:

   • SCHEMA: A definition in the formatcolumn:data_type,column:data_type or the path to a JSON schema file onyour local machine. For more information, seeSpecifying a schema.
   • UNIT_TIME: The partitioning type. Supported valuesincludeDAY,HOUR,MONTH, orYEAR.
   • EXPIRATION_TIME: The expiration time for the table'spartitions, in seconds. The--time_partitioning_expiration flag isoptional. For more information, seeSet the partition expiration.
   • BOOLEAN: Iftrue then queries on this table must includea partition filter. The--require_partition_filter flag is optional.For more information, seeSet partition filter requirements.
   • PROJECT_ID: The project ID. If omitted, your default projectis used.
   • DATASET: The name of a dataset in your project.
   • TABLE: The name of the table to create.

   For other command-line options,seebq mk.

   The following example creates an ingestion-time partitioned tablenamedmytable. The table has daily partitioning, with a partitionexpiration of 259,200 seconds (3 days).

   bqmk\-t\--schemaqtr:STRING,sales:FLOAT,year:STRING\--time_partitioning_typeDAY\--time_partitioning_expiration259200\mydataset.mytable

Terraform

Use thegoogle_bigquery_tableresource.

To authenticate to BigQuery, set up Application DefaultCredentials. For more information, seeSet up authentication for client libraries.

The following example creates a table namedmytable that is partitionedby ingestion time:

resource "google_bigquery_dataset" "default" {  dataset_id                      = "mydataset"  default_partition_expiration_ms = 2592000000  # 30 days  default_table_expiration_ms     = 31536000000 # 365 days  description                     = "dataset description"  location                        = "US"  max_time_travel_hours           = 96 # 4 days  labels = {    billing_group = "accounting",    pii           = "sensitive"  }}resource "google_bigquery_table" "default" {  dataset_id          = google_bigquery_dataset.default.dataset_id  table_id            = "mytable"  deletion_protection = false # set to "true" in production  time_partitioning {    type          = "MONTH"    expiration_ms = 604800000 # 7 days  }  require_partition_filter = true  schema = <<EOF[  {    "name": "ID",    "type": "INT64",    "mode": "NULLABLE",    "description": "Item ID"  },  {    "name": "Item",    "type": "STRING",    "mode": "NULLABLE"  }]EOF}

To apply your Terraform configuration in a Google Cloud project, complete the steps in the following sections.

Prepare Cloud Shell

1. LaunchCloud Shell.

2. Set the default Google Cloud project where you want to apply your Terraform configurations.

   You only need to run this command once per project, and you can run it in any directory.

   export GOOGLE_CLOUD_PROJECT=PROJECT_ID

   Environment variables are overridden if you set explicit values in the Terraform configuration file.

Prepare the directory

Each Terraform configuration file must have its own directory (alsocalled aroot module).

1. InCloud Shell, create a directory and a new file within that directory. The filename must have the.tf extension—for examplemain.tf. In this tutorial, the file is referred to asmain.tf.

   mkdirDIRECTORY && cdDIRECTORY && touch main.tf

2. If you are following a tutorial, you can copy the sample code in each section or step.

   Copy the sample code into the newly createdmain.tf.

   Optionally, copy the code from GitHub. This is recommended when the Terraform snippet is part of an end-to-end solution.

3. Review and modify the sample parameters to apply to your environment.
4. Save your changes.
5. Initialize Terraform. You only need to do this once per directory.

   terraform init

   Optionally, to use the latest Google provider version, include the-upgrade option:

   terraform init -upgrade

Apply the changes

1. Review the configuration and verify that the resources that Terraform is going to create or update match your expectations:

   terraform plan

   Make corrections to the configuration as necessary.

2. Apply the Terraform configuration by running the following command and enteringyes at the prompt:

   terraform apply

   Wait until Terraform displays the "Apply complete!" message.

3. Open your Google Cloud project to view the results. In the Google Cloud console, navigate to your resources in the UI to make sure that Terraform has created or updated them.

API

Call thetables.insertmethod with a definedtable resourcethat specifies thetimePartitioning property and theschema property.

Console

1. Open the BigQuery page in the Google Cloud console.

   Go to the BigQuery page

2. In theExplorer panel, expand your project and select a dataset.

3. Expand themore_vertActions option and clickOpen.

4. In the details panel, clickCreate tableadd_box.

5. On theCreate table page, in theSource section, selectEmptytable.

6. In theDestination section:

   • ForDataset name, choose the appropriate dataset.
   • In theTable name field, enter the name of the table.
   • Verify thatTable type is set toNative table.

7. In theSchema section, enter the schema definition. Make sure theschema includes anINTEGER column for the partitioning column. Formore information, seeSpecifying a schema.

8. In thePartition and cluster settings section, in thePartitioning drop-down list, selectPartition by field andchoose the partitioning column. This option is only available if theschema contains anINTEGER column.

9. Provide values forStart,End, andInterval:

   • Start is the start of first partition range (inclusive).
   • End is the end of last partition range (exclusive).
   • Interval is the width of each partition range.

   Values outside of these ranges go into a special__UNPARTITIONED__partition.

10. (Optional) To require a partition filter on all queries for this table,select theRequire partition filter checkbox. Requiring a partitionfilter can reduce cost and improve performance. For more information, seeSet partition filter requirements.

11. ClickCreate table.

SQL

To create an integer-range partitioned table, use theCREATE TABLE DDL statementwith aPARTITION BY clause.

The following example creates a table that is partitioned on thecustomer_id column with start 0, end 100, and interval 10:

1. In the Google Cloud console, go to theBigQuery page.

   Go to BigQuery

2. In the query editor, enter the following statement:

   CREATETABLEmydataset.newtable(customer_idINT64,date1DATE)PARTITIONBYRANGE_BUCKET(customer_id,GENERATE_ARRAY(0,100,10))OPTIONS(require_partition_filter=TRUE);

   Use theOPTIONS clauseto set table options such as thepartition filter requirements.

3. Clickplay_circleRun.

For more information about how to run queries, seeRun an interactive query.

bq

1. In the Google Cloud console, activate Cloud Shell.

   Activate Cloud Shell

   At the bottom of the Google Cloud console, aCloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.

2. Use thebq mkcommand with the--table flag (or-t shortcut):

   bqmk\--schemaschema\--range_partitioning=COLUMN_NAME,START,END,INTERVAL\--require_partition_filter=BOOLEAN\PROJECT_ID:DATASET.TABLE

   Replace the following:

   • SCHEMA: An inline schema definition in the formatcolumn:data_type,column:data_type or the path to a JSON schema file onyour local machine. For more information, seeSpecifying a schema.
   • COLUMN_NAME: The name of the partitioning column. In the tableschema, this column must be anINTEGER type.
   • START: The start of first partition range (inclusive).
   • END: The end of last partition range (exclusive).
   • INTERVAL: The width of each partition range.
   • BOOLEAN: Iftrue then queries on this table must includea partition filter. The--require_partition_filter flag is optional.For more information, seeSet partition filter requirements.
   • PROJECT_ID: The project ID. If omitted, your default projectis used.
   • DATASET: The name of a dataset in your project.
   • TABLE: The name of the table to create.

   Values outside of the partition range go into a special__UNPARTITIONED__partition.

   For other command-line options,seebq mk.

   The following example creates a table namedmytable that is partitionedon thecustomer_id column.

   bqmk\-t\--schema'customer_id:INTEGER,qtr:STRING,sales:FLOAT'\--range_partitioning=customer_id,0,100,10\mydataset.mytable

Terraform

Use thegoogle_bigquery_tableresource.

To authenticate to BigQuery, set up Application DefaultCredentials. For more information, seeSet up authentication for client libraries.

The following example creates a table namedmytable that is partitionedby integer range:

resource "google_bigquery_dataset" "default" {  dataset_id                      = "mydataset"  default_partition_expiration_ms = 2592000000  # 30 days  default_table_expiration_ms     = 31536000000 # 365 days  description                     = "dataset description"  location                        = "US"  max_time_travel_hours           = 96 # 4 days  labels = {    billing_group = "accounting",    pii           = "sensitive"  }}resource "google_bigquery_table" "default" {  dataset_id          = google_bigquery_dataset.default.dataset_id  table_id            = "mytable"  deletion_protection = false # set to "true" in production  range_partitioning {    field = "ID"    range {      start    = 0      end      = 1000      interval = 10    }  }  require_partition_filter = true  schema = <<EOF[  {    "name": "ID",    "type": "INT64",    "description": "Item ID"  },  {    "name": "Item",    "type": "STRING",    "mode": "NULLABLE"  }]EOF}

To apply your Terraform configuration in a Google Cloud project, complete the steps in the following sections.

Prepare Cloud Shell

1. LaunchCloud Shell.

2. Set the default Google Cloud project where you want to apply your Terraform configurations.

   You only need to run this command once per project, and you can run it in any directory.

   export GOOGLE_CLOUD_PROJECT=PROJECT_ID

   Environment variables are overridden if you set explicit values in the Terraform configuration file.

Prepare the directory

Each Terraform configuration file must have its own directory (alsocalled aroot module).

1. InCloud Shell, create a directory and a new file within that directory. The filename must have the.tf extension—for examplemain.tf. In this tutorial, the file is referred to asmain.tf.

   mkdirDIRECTORY && cdDIRECTORY && touch main.tf

2. If you are following a tutorial, you can copy the sample code in each section or step.

   Copy the sample code into the newly createdmain.tf.

   Optionally, copy the code from GitHub. This is recommended when the Terraform snippet is part of an end-to-end solution.

3. Review and modify the sample parameters to apply to your environment.
4. Save your changes.
5. Initialize Terraform. You only need to do this once per directory.

   terraform init

   Optionally, to use the latest Google provider version, include the-upgrade option:

   terraform init -upgrade

Apply the changes

1. Review the configuration and verify that the resources that Terraform is going to create or update match your expectations:

   terraform plan

   Make corrections to the configuration as necessary.

2. Apply the Terraform configuration by running the following command and enteringyes at the prompt:

   terraform apply

   Wait until Terraform displays the "Apply complete!" message.

3. Open your Google Cloud project to view the results. In the Google Cloud console, navigate to your resources in the UI to make sure that Terraform has created or updated them.

API

Call thetables.insertmethod with a definedtable resourcethat specifies therangePartitioning property and theschema property.

Java

importcom.google.cloud.bigquery.BigQuery;importcom.google.cloud.bigquery.BigQueryException;importcom.google.cloud.bigquery.BigQueryOptions;importcom.google.cloud.bigquery.Field;importcom.google.cloud.bigquery.RangePartitioning;importcom.google.cloud.bigquery.Schema;importcom.google.cloud.bigquery.StandardSQLTypeName;importcom.google.cloud.bigquery.StandardTableDefinition;importcom.google.cloud.bigquery.TableId;importcom.google.cloud.bigquery.TableInfo;// Sample to create a range partitioned tablepublicclassCreateRangePartitionedTable{publicstaticvoidmain(String[]args){// TODO(developer): Replace these variables before running the sample.StringdatasetName="MY_DATASET_NAME";StringtableName="MY_TABLE_NAME";Schemaschema=Schema.of(Field.of("integerField",StandardSQLTypeName.INT64),Field.of("stringField",StandardSQLTypeName.STRING),Field.of("booleanField",StandardSQLTypeName.BOOL),Field.of("dateField",StandardSQLTypeName.DATE));createRangePartitionedTable(datasetName,tableName,schema);}publicstaticvoidcreateRangePartitionedTable(StringdatasetName,StringtableName,Schemaschema){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();TableIdtableId=TableId.of(datasetName,tableName);// Note: The field must be a top- level, NULLABLE/REQUIRED field.// The only supported type is INTEGER/INT64RangePartitioningpartitioning=RangePartitioning.newBuilder().setField("integerField").setRange(RangePartitioning.Range.newBuilder().setStart(1L).setInterval(2L).setEnd(10L).build()).build();StandardTableDefinitiontableDefinition=StandardTableDefinition.newBuilder().setSchema(schema).setRangePartitioning(partitioning).build();TableInfotableInfo=TableInfo.newBuilder(tableId,tableDefinition).build();bigquery.create(tableInfo);System.out.println("Range partitioned table created successfully");}catch(BigQueryExceptione){System.out.println("Range partitioned table was not created. \n"+e.toString());}}}

Node.js

// Import the Google Cloud client libraryconst{BigQuery}=require('@google-cloud/bigquery');constbigquery=newBigQuery();asyncfunctioncreateTableRangePartitioned(){// Creates a new integer range partitioned table named "my_table"// in "my_dataset"./**   * TODO(developer): Uncomment the following lines before running the sample.   */// const datasetId = "my_dataset";// const tableId = "my_table";constschema=[{name:'fullName',type:'STRING'},{name:'city',type:'STRING'},{name:'zipcode',type:'INTEGER'},];// To use integer range partitioning, select a top-level REQUIRED or// NULLABLE column with INTEGER / INT64 data type. Values that are// outside of the range of the table will go into the UNPARTITIONED// partition. Null values will be in the NULL partition.constrangePartition={field:'zipcode',range:{start:0,end:100000,interval:10,},};// For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resourceconstoptions={schema:schema,rangePartitioning:rangePartition,};// Create a new table in the datasetconst[table]=awaitbigquery.dataset(datasetId).createTable(tableId,options);console.log(`Table${table.id} created with integer range partitioning: `);console.log(table.metadata.rangePartitioning);}

Python

fromgoogle.cloudimportbigquery# Construct a BigQuery client object.client=bigquery.Client()# TODO(developer): Set table_id to the ID of the table to create.# table_id = "your-project.your_dataset.your_table_name"schema=[bigquery.SchemaField("full_name","STRING"),bigquery.SchemaField("city","STRING"),bigquery.SchemaField("zipcode","INTEGER"),]table=bigquery.Table(table_id,schema=schema)table.range_partitioning=bigquery.RangePartitioning(# To use integer range partitioning, select a top-level REQUIRED /# NULLABLE column with INTEGER / INT64 data type.field="zipcode",range_=bigquery.PartitionRange(start=0,end=100000,interval=10),)table=client.create_table(table)# Make an API request.print("Created table{}.{}.{}".format(table.project,table.dataset_id,table.table_id))

SQL

Use theCREATE TABLEstatement. Include aPARTITION BYclause to configure the partitioning.

The following example creates a table that is partitioned on thetransaction_date column:

1. In the Google Cloud console, go to theBigQuery page.

   Go to BigQuery

2. In the query editor, enter the following statement:

   CREATETABLEmydataset.newtable(transaction_idINT64,transaction_dateDATE)PARTITIONBYtransaction_dateAS(SELECTtransaction_id,transaction_dateFROMmydataset.mytable);

   Use theOPTIONS clauseto set table options such as thepartition filter requirements.

3. Clickplay_circleRun.

For more information about how to run queries, seeRun an interactive query.

bq

1. In the Google Cloud console, activate Cloud Shell.

   Activate Cloud Shell

   At the bottom of the Google Cloud console, aCloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.

2. To create a partitioned table from a query, use thebq query commandwith the--destination_table flag and the--time_partitioning_typeflag.

   Time-unit column-partitioning:

   bqquery\--use_legacy_sql=false\--destination_tableTABLE_NAME\--time_partitioning_fieldCOLUMN\--time_partitioning_typeUNIT_TIME\'QUERY_STATEMENT'

   Ingestion-time partitioning:

   bqquery\--use_legacy_sql=false\--destination_tableTABLE_NAME\--time_partitioning_typeUNIT_TIME\'QUERY_STATEMENT'

   Integer-range partitioning:

   bqquery\--use_legacy_sql=false\--destination_tablePROJECT_ID:DATASET.TABLE\--range_partitioningCOLUMN,START,END,INTERVAL\'QUERY_STATEMENT'

   Replace the following:

   • PROJECT_ID: The project ID. If omitted, your default projectis used.
   • DATASET: The name of a dataset in your project.
   • TABLE: The name of the table to create.
   • COLUMN: The name of the partitioning column.
   • UNIT_TIME: The partitioning type. Supported values includeDAY,HOUR,MONTH, orYEAR.
   • START: The start of range partitioning, inclusive.
   • END: The end of range partitioning, exclusive.
   • INTERVAL: The width of each range within the partition.
   • QUERY_STATEMENT: The query used to populate the table.

   The following example creates a table that is partitioned on thetransaction_date column, using monthly partitioning.

   bqquery\--use_legacy_sql=false\--destination_tablemydataset.newtable\--time_partitioning_fieldtransaction_date\--time_partitioning_typeMONTH\'SELECT transaction_id, transaction_date FROM mydataset.mytable'

   The following example creates a table that is partitioned on thecustomer_id column, using integer-range partitioning.

   bqquery\--use_legacy_sql=false\--destination_tablemydataset.newtable\--range_partitioningcustomer_id,0,100,10\'SELECT * FROM mydataset.ponies'

   For ingestion-time partitioned tables, you can also load data into aspecific partition by using apartition decorator.The following example creates a new ingestion-time partitioned table andloads data into the20180201 (February 1, 2018) partition:

   bqquery\--use_legacy_sql=false\--time_partitioning_type=DAY\--destination_table='newtable$20180201'\'SELECT * FROM mydataset.mytable'

API

To save query results to a partitioned table, call thejobs.insert method.Configure aquery job. Specify the destination table in thedestinationTable. Specify the partitioning in either thetimePartitioning property or therangePartitioning property.