Creating partitioned tables

This page describes how to create partitioned tables in BigQuery. For an overview of partitioned tables, see Introduction to partitioned tables.

Required permissions

At a minimum, to create a table, you must be granted the following permissions:

• bigquery.tables.create permissions to create the table
• bigquery.tables.updateData to write data to the table by using a load job, a query job, or a copy job
• bigquery.jobs.create to run a query job, load job, or copy job that writes data to the table

Additional permissions such as bigquery.tables.getData might be required to access the data you're writing to the table.

The following predefined IAM roles include both bigquery.tables.create and bigquery.tables.updateData permissions:

• bigquery.dataEditor
• bigquery.dataOwner
• bigquery.admin

The following predefined IAM roles include bigquery.jobs.create permissions:

• bigquery.user
• bigquery.jobUser
• bigquery.admin

In addition, if a user has bigquery.datasets.create permissions, when that user creates a dataset, they are granted bigquery.dataOwner access to it. bigquery.dataOwner access gives the user the ability to create and update tables in the dataset.

For more information on IAM roles and permissions in BigQuery, see Predefined roles and permissions.

Create an empty partitioned table

The steps to create a partitioned table in BigQuery are similar to creating a standard table, except that you specify the partitioning options, along with any other table options.

Create a time-unit column-partitioned table

To create an empty time-unit column-partitioned table with a schema definition:

Console

1. Open the BigQuery page in the Cloud Console.

   Go to the BigQuery page

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

   Note: The default experience is the Preview Cloud Console. If you clicked Hide preview features to go to the Generally Available Cloud Console, then perform the following step instead: In the navigation panel, in the Resources section, expand your project and select a dataset.

3. Expand the Actions option and click Open.

4. In the details panel, click Create table .

5. On the Create table page, in the Source section, select Empty table.

6. In the Destination section:

   • For Dataset name, choose the appropriate dataset.
   • In the Table name field, enter the name of the table.
   • Verify that Table type is set to Native table.

7. In the Schema section, enter the schema definition. Make sure the schema includes a DATE, TIMESTAMP, or DATETIME column for the partitioning column. For more information, see Specifying a schema.

8. In the Partition and cluster settings section, in the Partitioning drop-down list, select Partition by field and choose the partitioning column. This option is only available if the schema contains a DATE, TIMESTAMP, or DATETIME column.

9. Select the Partitioning type to choose daily, hourly, monthly, or yearly partitioning.

10. (Optional) To require a partition filter on all queries for this table, select the Require partition filter checkbox. Requiring a partition filter can reduce cost and improve performance. For more information, see Querying partitioned tables.

11. Click Create table.

Note: You can't set the partition expiration in the Cloud Console. To set the partition after you create the table, see Updating the partition expiration.

SQL

To create a time-unit column-partitioned table, use the CREATE TABLE statement with a PARTITION BY clause.

The following example creates a table with daily partitions based on the transaction_date column.

CREATE TABLE
  mydataset.newtable (transaction_id INT64, transaction_date DATE)
PARTITION BY
  transaction_date
OPTIONS(
  partition_expiration_days=3,
  require_partition_filter=true
)

The default partitioning type for DATE columns is daily partitioning. To specify a different partitioning type, include the DATE_TRUNC function in the PARTITION BY clause. For example, the following query creates a table with monthly partitions:

CREATE TABLE
  mydataset.newtable (transaction_id INT64, transaction_date DATE)
PARTITION BY
  DATE_TRUNC(transaction_date, MONTH)
OPTIONS(
  partition_expiration_days=3,
  require_partition_filter=true
)

You can also specify a TIMESTAMP or DATETIME column as the partitioning column. In that case, include the TIMESTAMP_TRUNC or DATETIME_TRUNC function in the PARTITION BY clause to specify the partition type. For example, the following statement creates a table with daily partitions based on a TIMESTAMP column:

CREATE TABLE
  mydataset.newtable (transaction_id INT64, transaction_ts TIMESTAMP)
PARTITION BY
  TIMESTAMP_TRUNC(transaction_ts, DAY)
OPTIONS(
  partition_expiration_days=3,
  require_partition_filter=true
)

For information about how to run queries, see Running interactive queries.

bq

Use the bq mk command with the --table flag (or -t shortcut):

bq mk --table \
  --schema SCHEMA \
  --time_partitioning_field COLUMN \
  --time_partitioning_type UNIT_TIME \
  --time_partitioning_expiration EXPIRATION_TIME \
  --require_partition_filter=BOOLEAN
  PROJECT_ID:DATASET.TABLE

Replace the following:

• SCHEMA: A schema definition in the format column:data_type,column:data_type or the path to a JSON schema file on your local machine. For more information, see Specifying a schema.
• COLUMN: The name of the partitioning column. In the table schema, this column must be a TIMESTAMP, DATETIME, or DATE type.
• UNIT_TIME: The partitioning type. Supported values include DAY, HOUR, MONTH, or YEAR.
• EXPIRATION_TIME: The expiration time for the table's partitions, in seconds. The --time_partitioning_expiration flag is optional.
• BOOLEAN: If true then queries on this table must include a partition filter. The --require_partition_filter flag is optional.
• PROJECT_ID: The project ID. If omitted, your default project is used.
• DATASET: The name of a dataset in your project.
• TABLE: The name of the table to create.

For other command-line options, see bq mk.

The following example creates a table named mytable that is partitioned on the ts column, using hourly partitioning. The partition expiration is 259,200 seconds (3 days).

bq mk -t \
  --schema 'ts:TIMESTAMP,qtr:STRING,sales:FLOAT' \
  --time_partitioning_field ts \
  --time_partitioning_type HOUR \
  --time_partitioning_expiration 259200  \
  mydataset.mytable

API

Call the tables.insert method with a defined table resource that specifies the timePartitioning property and the schema property.

Go

Before trying this sample, follow the Go setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Go API reference documentation.

View on GitHub Feedback

import (
	"context"
	"fmt"
	"time"

	"cloud.google.com/go/bigquery"
)

// createTablePartitioned demonstrates creating a table and specifying a time partitioning configuration.
func createTablePartitioned(projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydatasetid"
	// tableID := "mytableid"
	ctx := context.Background()

	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.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)
	if err := tableRef.Create(ctx, metadata); err != nil {
		return err
	}
	return nil
}

Java

Before trying this sample, follow the Java setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Java API reference documentation.

View on GitHub Feedback

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.StandardTableDefinition;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;
import com.google.cloud.bigquery.TimePartitioning;

// Sample to create a partition table
public class CreatePartitionedTable {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    Schema schema =
        Schema.of(
            Field.of("name", StandardSQLTypeName.STRING),
            Field.of("post_abbr", StandardSQLTypeName.STRING),
            Field.of("date", StandardSQLTypeName.DATE));
    createPartitionedTable(datasetName, tableName, schema);
  }

  public static void createPartitionedTable(String datasetName, String tableName, Schema schema) {
    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.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(datasetName, tableName);

      TimePartitioning partitioning =
          TimePartitioning.newBuilder(TimePartitioning.Type.DAY)
              .setField("date") //  name of column to use for partitioning
              .setExpirationMs(7776000000L) // 90 days
              .build();

      StandardTableDefinition tableDefinition =
          StandardTableDefinition.newBuilder()
              .setSchema(schema)
              .setTimePartitioning(partitioning)
              .build();
      TableInfo tableInfo = TableInfo.newBuilder(tableId, tableDefinition).build();

      bigquery.create(tableInfo);
      System.out.println("Partitioned table created successfully");
    } catch (BigQueryException e) {
      System.out.println("Partitioned table was not created. \n" + e.toString());
    }
  }
}

Node.js

Before trying this sample, follow the Node.js setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Node.js API reference documentation.

View on GitHub Feedback

// Import the Google Cloud client library
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function createTablePartitioned() {
  // 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";
  const schema = 'Name:string, Post_Abbr:string, Date:date';

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    schema: schema,
    location: 'US',
    timePartitioning: {
      type: 'DAY',
      expirationMS: '7776000000',
      field: 'date',
    },
  };

  // Create a new table in the dataset
  const [table] = await bigquery
    .dataset(datasetId)
    .createTable(tableId, options);
  console.log(`Table ${table.id} created with partitioning: `);
  console.log(table.metadata.timePartitioning);
}

Python

Before trying this sample, follow the Python setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Python API reference documentation.

View on GitHub Feedback

# from google.cloud import bigquery
# client = bigquery.Client()
# project = client.project
# dataset_ref = bigquery.DatasetReference(project, 'my_dataset')

table_ref = dataset_ref.table("my_partitioned_table")
schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
    bigquery.SchemaField("date", "DATE"),
]
table = bigquery.Table(table_ref, schema=schema)
table.time_partitioning = bigquery.TimePartitioning(
    type_=bigquery.TimePartitioningType.DAY,
    field="date",  # name of column to use for partitioning
    expiration_ms=7776000000,
)  # 90 days

table = client.create_table(table)

print(
    "Created table {}, partitioned on column {}".format(
        table.table_id, table.time_partitioning.field
    )
)

Create an ingestion-time partitioned table

To create an empty ingestion-time partitioned table with a schema definition:

Console

1. Open the BigQuery page in the Cloud Console.

   Go to the BigQuery page

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

   Note: The default experience is the Preview Cloud Console. If you clicked Hide preview features to go to the Generally Available Cloud Console, then perform the following step instead: In the navigation panel, in the Resources section, expand your project and select a dataset.

3. Expand the Actions option and click Open.

4. In the details panel, click Create table .

5. On the Create table page, in the Source section, select Empty table.

6. In the Destination section:

   • For Dataset name, choose the appropriate dataset.
   • In the Table name field, enter the name of the table.
   • Verify that Table type is set to Native table.

7. In the Schema section, enter the schema definition.

8. In the Partition and cluster settings section, for Partitioning, click Partition by ingestion time.

9. (Optional) To require a partition filter on all queries for this table, select the Require partition filter checkbox. Requiring a partition filter can reduce cost and improve performance. For more information, see Querying partitioned tables.

10. Click Create table.

Note: You can't set the partition expiration in the Cloud Console. To set the partition after you create the table, see Updating the partition expiration.

SQL

To create an ingestion-time partitioned table, use the CREATE TABLE statement with a PARTITION BY clause that partitions on _PARTITIONTIME.

The following example creates a table with daily partitions.

CREATE TABLE
  mydataset.newtable (transaction_id INT64)
PARTITION BY
  _PARTITIONDATE
OPTIONS(
  partition_expiration_days=3,
  require_partition_filter=true
)

The default partitioning type for ingestion-time partitioning is daily partitioning. To specify a different partitioning type, include the DATE_TRUNC function in the PARTITION BY clause. For example, the following query creates a table with monthly partitions:

CREATE TABLE
  mydataset.newtable (transaction_id INT64)
PARTITION BY
  DATE_TRUNC(_PARTITIONTIME, MONTH)
OPTIONS(
  partition_expiration_days=3,
  require_partition_filter=true
)

For information about how to run queries, see Running interactive queries.

bq

Use the bq mk command with the --table flag (or -t shortcut):

bq mk --table \
  --schema SCHEMA \
  --time_partitioning_type UNIT_TIME \
  --time_partitioning_expiration EXPIRATION_TIME \
  --require_partition_filter=BOOLEAN  \
  PROJECT_ID:DATASET.TABLE

Replace the following:

• SCHEMA: A definition in the format column:data_type,column:data_type or the path to a JSON schema file on your local machine. For more information, see Specifying a schema.
• UNIT_TIME: The partitioning type. Supported values include DAY, HOUR, MONTH, or YEAR.
• EXPIRATION_TIME: The expiration time for the table's partitions, in seconds. The --time_partitioning_expiration flag is optional.
• BOOLEAN: If true then queries on this table must include a partition filter. The --require_partition_filter flag is optional.
• PROJECT_ID: The project ID. If omitted, your default project is used.
• DATASET: The name of a dataset in your project.
• TABLE: The name of the table to create.

For other command-line options, see bq mk.

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

bq mk -t \
  --schema qtr:STRING,sales:FLOAT,year:STRING \
  --time_partitioning_type DAY \
  --time_partitioning_expiration 259200 \
  mydataset.mytable

API

Call the tables.insert method with a defined table resource that specifies the timePartitioning property and the schema property.

Create an integer-range partitioned table

To create an empty integer-range partitioned table with a schema definition:

Console

1. Open the BigQuery page in the Cloud Console.

   Go to the BigQuery page

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

   Note: The default experience is the Preview Cloud Console. If you clicked Hide preview features to go to the Generally Available Cloud Console, then perform the following step instead: In the navigation panel, in the Resources section, expand your project and select a dataset.

3. Expand the Actions option and click Open.

4. In the details panel, click Create table .

5. On the Create table page, in the Source section, select Empty table.

6. In the Destination section:

   • For Dataset name, choose the appropriate dataset.
   • In the Table name field, enter the name of the table.
   • Verify that Table type is set to Native table.

7. In the Schema section, enter the schema definition. Make sure the schema includes an INTEGER column for the partitioning column. For more information, see Specifying a schema.

8. In the Partition and cluster settings section, in the Partitioning drop-down list, select Partition by field and choose the partitioning column. This option is only available if the schema contains an INTEGER column.

9. Provide values for Start, End, and Interval:

   • 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 the Require partition filter checkbox. Requiring a partition filter can reduce cost and improve performance. For more information, see Querying partitioned tables.

11. Click Create table.

Note: You can't set the partition expiration in the Cloud Console. To set the partition after you create the table, see Updating the partition expiration.

SQL

To create an integer-range partitioned table, use the CREATE TABLE statement with a PARTITION BY clause.

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

CREATE TABLE mydataset.newtable (customer_id INT64, date1 DATE)
PARTITION BY
  RANGE_BUCKET(customer_id, GENERATE_ARRAY(0, 100, 10))
OPTIONS(
  require_partition_filter=true
)

For information about how to run queries, see Running interactive queries.

bq

Use the bq mk command with the --table flag (or -t shortcut):

bq mk \
  --schema schema \
  --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 format column:data_type,column:data_type or the path to a JSON schema file on your local machine. For more information, see Specifying a schema.
• COLUMN_NAME: The name of the partitioning column. In the table schema, this column must be an INTEGER 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: If true then queries on this table must include a partition filter. The --require_partition_filter flag is optional.
• PROJECT_ID: The project ID. If omitted, your default project is 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, see bq mk.

The following example creates a table named mytable that is partitioned on the customer_id column.

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

API

Call the tables.insert method with a defined table resource that specifies the rangePartitioning property and the schema property.

Java

Before trying this sample, follow the Java setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Java API reference documentation.

View on GitHub Feedback

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.RangePartitioning;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.StandardTableDefinition;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;

// Sample to create a range partitioned table
public class CreateRangePartitionedTable {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    Schema schema =
        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);
  }

  public static void createRangePartitionedTable(
      String datasetName, String tableName, Schema schema) {
    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.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(datasetName, tableName);

      // Note: The field must be a top- level, NULLABLE/REQUIRED field.
      // The only supported type is INTEGER/INT64
      RangePartitioning partitioning =
          RangePartitioning.newBuilder()
              .setField("integerField")
              .setRange(
                  RangePartitioning.Range.newBuilder()
                      .setStart(1L)
                      .setInterval(2L)
                      .setEnd(10L)
                      .build())
              .build();

      StandardTableDefinition tableDefinition =
          StandardTableDefinition.newBuilder()
              .setSchema(schema)
              .setRangePartitioning(partitioning)
              .build();
      TableInfo tableInfo = TableInfo.newBuilder(tableId, tableDefinition).build();

      bigquery.create(tableInfo);
      System.out.println("Range partitioned table created successfully");
    } catch (BigQueryException e) {
      System.out.println("Range partitioned table was not created. \n" + e.toString());
    }
  }
}

Node.js

Before trying this sample, follow the Node.js setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Node.js API reference documentation.

View on GitHub Feedback

// Import the Google Cloud client library
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function createTableRangePartitioned() {
  // 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";

  const schema = [
    {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.
  const rangePartition = {
    field: 'zipcode',
    range: {
      start: 0,
      end: 100000,
      interval: 10,
    },
  };

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    schema: schema,
    rangePartitioning: rangePartition,
  };

  // Create a new table in the dataset
  const [table] = await bigquery
    .dataset(datasetId)
    .createTable(tableId, options);

  console.log(`Table ${table.id} created with integer range partitioning: `);
  console.log(table.metadata.rangePartitioning);
}

Python

Before trying this sample, follow the Python setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Python API reference documentation.

View on GitHub Feedback

from google.cloud import bigquery

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

Create a partitioned table from a query result

You can create a partitioned table from a query result in the following ways:

• Use the bq command-line tool or the BigQuery API to set a destination table for a query. When the query runs, BigQuery writes the results to the destination table. You can use this approach for any partitioning type.

• In SQL, use a CREATE TABLE ... AS SELECT statement. You can use this approach to create a table that is partitioned by time-unit column or integer range, but not ingestion time.

SQL

Use the CREATE TABLE statement with a SELECT AS clause for the query. Include a PARTITION BY clause to configure the partitioning.

The following example creates a table that is partitioned on the transaction_date column.

CREATE TABLE
  mydataset.newtable (transaction_id INT64, transaction_date DATE)
PARTITION BY
  transaction_date
AS SELECT transaction_id, transaction_date FROM mydataset.mytable

bq

To create a partitioned table from a query, use the bq query command with the --destination_table flag and the --time_partitioning_type flag.

Time-unit column-partitioning:

bq query \
  --use_legacy_sql=false \
  --destination_table TABLE_NAME \
  --time_partitioning_field COLUMN \
  --time_partitioning_type UNIT_TIME \
  'QUERY_STATEMENT'

Ingestion-time partitioning:

bq query \
  --use_legacy_sql=false \
  --destination_table TABLE_NAME \
  --time_partitioning_type UNIT_TIME \
  'QUERY_STATEMENT'

Integer-range partitioning:

bq query \
  --use_legacy_sql=false \
  --destination_table PROJECT_ID:DATASET.TABLE \
  --range_partitioning COLUMN,START,END,INTERVAL \
  'QUERY_STATEMENT'

Replace the following:

• PROJECT_ID: The project ID. If omitted, your default project is 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 include DAY, HOUR, MONTH, or YEAR.
• 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 the transaction_date column, using monthly partitioning.

bq query \
  --use_legacy_sql=false
  --destination_table mydataset.newtable
  --time_partitioning_field transaction_id
  --time_partitioning_type MONTH
  'SELECT transaction_id, transaction_date FROM mydataset.mytable'

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

bq query \
  --use_legacy_sql=false
  --destination_table mydataset.newtable
  --range_partitioning customer_id,0,100,10
  'SELECT * FROM mydataset.ponies'

For ingestion-time partitioned tables, you can also load data into a specific partition by using a partition decorator. The following example creates a new ingestion-time partitioned table and loads data into the 20180201 (February 1, 2018) partition:

bq query \
  --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 the jobs.insert method. Configure a query job. Specify the destination table in the destinationTable. Specify the partitioning in either the timePartitioning property or the rangePartitioning property.

Write data to a specific partition

Partition decorators enable you to write data into a specific partition. A partition decorator has the following form:

table_name$partition_id

The following example writes data into the 20160501 (May 1, 2016) partition of an existing table, assuming the table is already partitioned by date:

bq load --source_format=CSV 'mydataset.mytable$20160501' data.csv

You can also writes the results of a query to a specific partition:

bq query \
  --use_legacy_sql=false  \
  --destination_table='mytable$20160501' \
  --append_table=true \
  'SELECT * FROM mydataset.another_table'

With ingestion-time partitioning, you can use this technique to load older data, or to adjust for time zones. For example, if you are on Pacific Standard Time (PST), you can load data that was generated on May 1, 2016 PST into the partition for that date by using the corresponding partition decorator, $20160501. (By default, ingestion-time partitions are based on UTC time.)

For time-unit column and integer-range partitioned tables, the partition ID specified in the decorator must match the data being written. For example, if the table is partitioned on a DATE column, the decorator must match the value in that column. Otherwise, an error occurs. However, if you know beforehand that your data is in a single partition, specifying the partition decorator can improve write performance.

For more information on loading data, see Introduction to loading data into BigQuery.

Convert date-sharded tables into ingestion-time partitioned tables

If you previously created date-sharded tables, you can convert the entire set of related tables into a single ingestion-time partitioned table by using the partition command in the bq command-line tool.

bq --location=LOCATION partition \
  --time_partitioning_type=PARTION_TYPE \
  --time_partitioning_expiration INTEGER \
  PROJECT_ID:SOURCE_DATASET.SOURCE_TABLE \
  PROJECT_ID:DESTINATION_DATASET.DESTINATION_TABLE

Replace the following:

• LOCATION: The name of your location. The --location flag is optional.
• PARTITION_TYPE: The partition type. Possible values include DAY, HOUR, MONTH, or YEAR.
• INTEGER: The partition expiration time, in seconds. There is no minimum value. The expiration time evaluates to the partition's UTC date plus the integer value. The time_partitioning_expiration flag is optional.
• PROJECT_ID: Your project ID.
• SOURCE_DATASET: The dataset that contains the date-sharded tables.
• SOURCE_TABLE: The prefix of your date-sharded tables.
• DESTINATION_DATASET; The dataset for the new partitioned table.
• DESTINATION_TABLE; The name of the partitioned table to create.

The partition command does not support the --label, --expiration, or --description flags. You can add labels, a table expiration, and a description to the table after it is created.

When you run the partition command, BigQuery creates a copy job is created that generates partitions from the sharded tables.

The following example creates an ingestion-time partitioned table named mytable_partitioned from a set of date-sharded tables prefixed with sourcetable_. The new table is partitioned daily, with a partition expiration of 259,200 seconds (3 days).

bq partition \
--time_partitioning_type=DAY \
--time_partitioning_expiration 259200 \
mydataset.sourcetable_ \
mydataset.mytable_partitioned

If the date-sharded tables were sourcetable_20180126 and sourcetable_20180127, this command would create the following partitions: mydataset.mytable_partitioned$20180126 and mydataset.mytable_partitioned$20180127.

Next steps

• To learn how to manage and update partitioned tables, see Managing partitioned tables.
• For information on querying partitioned tables, see Querying partitioned tables.