Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://cloud.google.com/bigquery/docs/creating-partitioned-tables below:

Creating partitioned tables | BigQuery

Stay organized with collections Save and categorize content based on your preferences.

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

Grant Identity and Access Management (IAM) roles that give users the necessary permissions to perform each task in this document.

To create a table, you need the following IAM permissions:

• bigquery.tables.create
• bigquery.tables.updateData
• bigquery.jobs.create

Additionally, you might require the bigquery.tables.getData permission to access the data that you write to the table.

Each of the following predefined IAM roles includes the permissions that you need in order to create a table:

• roles/bigquery.dataEditor
• roles/bigquery.dataOwner
• roles/bigquery.admin (includes the bigquery.jobs.create permission)
• roles/bigquery.user (includes the bigquery.jobs.create permission)
• roles/bigquery.jobUser (includes the bigquery.jobs.create permission)

Additionally, if you have the bigquery.datasets.create permission, you can create and update tables in the datasets that you create.

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

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.

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

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

   Go to BigQuery

2. In the Explorer pane, expand your project, and then select a dataset.
3. In the Dataset info section, click add_box Create table.
4. In the Create table panel, specify the following details:
1. In the Source section, select Empty table in the Create table from list.
2. In the Destination section, specify the following details:
   1. For Dataset, select the dataset in which you want to create the table.
   2. In the Table field, enter the name of the table that you want to create.
   3. Verify that the Table type field is set to Native table.
3. In the Schema section, enter the schema definition. The schema must include a DATE, TIMESTAMP, or DATETIME column for the partitioning column. For more information, see Specifying a schema. You can enter schema information manually by using one of the following methods:
   • Option 1: Click Edit 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 as creating a JSON schema file. You can view the schema of an existing table in JSON format by entering the following command:

         bq show --format=prettyjson dataset.table
         

   • Option 2: Click add_box Add field and enter the table schema. Specify each field's Name, Type, and Mode.
4. In the Partition and cluster settings section, in the Partitioning list, select Partition by field, and then choose the partitioning column. This option is only available if the schema contains a DATE, TIMESTAMP, or DATETIME column.
5. Optional: To require a partition filter on all queries for this table, select the Require partition filter checkbox. A partition filter can reduce cost and improve performance. For more information, see Set partition filter requirements.
6. Select the Partitioning type to choose daily, hourly, monthly, or yearly partitioning.
7. Optional: In the Advanced options section, if you want to use a customer-managed encryption key, then select the Use a customer-managed encryption key (CMEK) option. By default, BigQuery encrypts customer content stored at rest by using a Google-owned and Google-managed encryption key.
8. Click Create table.

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

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

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

   Go to BigQuery

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

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

   Use the OPTIONS clause to set table options such as the partition expiration and the partition filter requirements.

3. Click play_circle Run.

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

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

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

   Activate Cloud Shell

   At the bottom of the Google Cloud console, a Cloud 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 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. For more information, see Set the partition expiration.
   • BOOLEAN: If true then queries on this table must include a partition filter. The --require_partition_filter flag is optional. For more information, see Set partition filter requirements.
   • 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

Use the google_bigquery_table resource.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

The following example creates a table named mytable that is partitioned by day:

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

1. Launch Cloud 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.

Each Terraform configuration file must have its own directory (also called a root module).

1. In Cloud Shell, create a directory and a new file within that directory. The filename must have the .tf extension—for example main.tf. In this tutorial, the file is referred to as main.tf.

   mkdir DIRECTORY && cd DIRECTORY && 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 created main.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

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 entering yes 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.

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

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.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

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.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

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.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

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.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

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

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

   Go to the BigQuery page

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

3. Expand the more_vert Actions option and click Open.

4. In the details panel, click Create table add_box.

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 Set partition filter requirements.

10. Click Create table.

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

The following example creates a table with daily partitions:

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

   Go to BigQuery

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

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

   Use the OPTIONS clause to set table options such as the partition expiration and the partition filter requirements.

3. Click play_circle Run.

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

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

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

   Activate Cloud Shell

   At the bottom of the Google Cloud console, a Cloud 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 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. For more information, see Set the partition expiration.
   • BOOLEAN: If true then queries on this table must include a partition filter. The --require_partition_filter flag is optional. For more information, see Set partition filter requirements.
   • 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

Use the google_bigquery_table resource.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

The following example creates a table named mytable that is partitioned by ingestion time:

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

1. Launch Cloud 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.

Each Terraform configuration file must have its own directory (also called a root module).

1. In Cloud Shell, create a directory and a new file within that directory. The filename must have the .tf extension—for example main.tf. In this tutorial, the file is referred to as main.tf.

   mkdir DIRECTORY && cd DIRECTORY && 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 created main.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

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 entering yes 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.

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

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

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

   Go to the BigQuery page

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

3. Expand the more_vert Actions option and click Open.

4. In the details panel, click Create table add_box.

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 Set partition filter requirements.

11. Click Create table.

To create an integer-range partitioned table, use the CREATE TABLE DDL 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:

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

   Go to BigQuery

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

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

   Use the OPTIONS clause to set table options such as the partition filter requirements.

3. Click play_circle Run.

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

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

   Activate Cloud Shell

   At the bottom of the Google Cloud console, a Cloud 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 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. For more information, see Set partition filter requirements.
   • 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

Use the google_bigquery_table resource.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

The following example creates a table named mytable that is partitioned by integer range:

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

1. Launch Cloud 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.

Each Terraform configuration file must have its own directory (also called a root module).

1. In Cloud Shell, create a directory and a new file within that directory. The filename must have the .tf extension—for example main.tf. In this tutorial, the file is referred to as main.tf.

   mkdir DIRECTORY && cd DIRECTORY && 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 created main.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

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 entering yes 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.

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

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.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

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.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

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.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

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

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

• Call the jobs.insert API method and specify the partitioning in either the timePartitioning property or the rangePartitioning property.

Use the CREATE TABLE statement. Include a PARTITION BY clause to configure the partitioning.

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

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

   Go to BigQuery

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

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

   Use the OPTIONS clause to set table options such as the partition filter requirements.

3. Click play_circle Run.

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

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

   Activate Cloud Shell

   At the bottom of the Google Cloud console, a Cloud 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 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_date \
      --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'

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.

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=PARTITION_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, --add_tags, or --description flags. You can add labels, a table expiration, tags, and a description to the table after it is created.

When you run the partition command, BigQuery creates a copy job 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.

Access control for partitioned tables is the same as access control for standard tables. For more information, see Introduction to table access controls.

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

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-08-07 UTC.

[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-08-07 UTC."],[[["Creating a partitioned table in BigQuery involves specifying partitioning options alongside other table settings, similar to creating a standard table."],["You can create time-unit column-partitioned tables based on `DATE`, `TIMESTAMP`, or `DATETIME` columns, with options for daily, hourly, monthly, or yearly partitioning, and set requirements such as partition expiration and partition filters."],["Ingestion-time partitioned tables can be created, which are partitioned based on the data ingestion time, by using the `_PARTITIONDATE` or `_PARTITIONTIME` columns, with options to also set partition expiration and filter requirements."],["Integer-range partitioned tables can be created by using an `INTEGER` type column to partition data with specified start, end, and interval values, additionally giving users the ability to set filter requirements."],["Partitioned tables can be generated from query results, via `CREATE TABLE ... AS SELECT` statements, by using the `bq` command-line tool or the BigQuery API to define a destination table and set partitioning."]]],[]]

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4