Creating and using partitioned tables

This document describes how to create and use partitioned tables in BigQuery. For information on ingestion-time partitioned tables, see Creating and using ingestion-time partitioned tables.

After creating a partitioned table, you can:

  • Control access to your table data
  • Get information about your partitioned tables
  • List the partitioned tables in a dataset
  • Get partitioned table metadata using meta-tables

For more information on managing partitioned tables including updating partitioned table properties, copying a partitioned table, and deleting a partitioned table, see Managing partitioned tables.

Limitations

Partitioned tables are subject to the following limitations:

  • The partitioning column must be either a scalar DATE or TIMESTAMP column. While the mode of the column may be REQUIRED or NULLABLE, it cannot be REPEATED (array-based). Additionally, the partitioning column must be a top-level field. You cannot use a leaf field from a RECORD (STRUCT) as the partitioning column.
  • You cannot use legacy SQL to query partitioned tables or to write query results to partitioned tables.

Creating partitioned tables

You can create a partitioned table in BigQuery:

When you create a column-based, time-partitioned table in BigQuery, the table name must be unique per dataset. The table name can:

  • Contain up to 1,024 characters
  • Contain letters (upper or lower case), numbers, and underscores

Required permissions

To create a partitioned table, you must have WRITER access at the dataset level, or you must be assigned a project-level IAM role that includes bigquery.tables.create permissions. The following predefined, project-level IAM roles include bigquery.tables.create permissions:

In addition, because the bigquery.user role has bigquery.datasets.create permissions, a user assigned to the bigquery.user role can create partitioned tables in any dataset that user creates. When a user assigned to the bigquery.user role creates a dataset, that user is given OWNER access to the dataset. OWNER access to a dataset gives the user full control over it and all the tables in it.

For more information on IAM roles and permissions in BigQuery, see Access control. For more information on dataset-level roles, see Primitive roles for datasets.

Creating an empty partitioned table with a schema definition

You cannot create an empty partitioned table that does not have a schema definition. The schema is required in order to identify the column used to create the partitions.

When you create an empty partitioned table with a schema definition, you can:

  • Provide the schema inline using the command-line tool
  • Specify a JSON schema file using the command-line tool
  • Provide the schema in a table resource when calling the API's tables.insert method.

For more information on specifying a table schema, see Specifying a schema.

After the partitioned table is created, you can:

  • Load data into it
  • Write query results to it
  • Copy data into it

To create an empty partitioned table with a schema definition:

Console

  1. Open the BigQuery web UI in the GCP Console.
    Go to the BigQuery web UI

  2. Click Compose a new query.

  3. Type your CREATE TABLE DDL statement into the Query editor text area.

    The following query creates a table named newtable that is partitioned by the transaction_date DATE column and has a partition expiration of three days.

     #standardSQL
 CREATE TABLE mydataset.newtable (transaction_id INT64, transaction_date DATE)
 PARTITION BY transaction_date
 OPTIONS(
   partition_expiration_days=3,
   description="a table partitioned by transaction_date"
 )

  4. Click More and select Query settings. Query settings

  5. For Processing location, click Auto-select and choose your data's location. You can leave processing location set to unspecified if your data is in the US or EU multi-region location. When your data is in the US or the EU, the processing location is automatically detected. Query processing location

  6. Click Run. When the query completes, the table will appear in the Resources pane.

Classic UI

  1. Go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click Compose query.

  3. Type your CREATE TABLE DDL statement into the New Query text area.

    The following query creates a table named newtable that is partitioned by the transaction_date DATE column and has a partition expiration of three days.

     #standardSQL
 CREATE TABLE mydataset.newtable (transaction_id INT64, transaction_date DATE)
 PARTITION BY transaction_date
 OPTIONS(
   partition_expiration_days=3,
   description="a table partitioned by transaction_date"
 )

  4. Click Show Options.

  5. For Processing Location, click Unspecified and choose your data's location. You can leave processing location set to unspecified if your data is in the US or EU multi-region location. When your data is in the US or the EU, the processing location is automatically detected.

  6. Click Run query. When the query completes, the table appears in the navigation pane.

Command-line

Use the mk command with the --table flag (or -t shortcut), the --schema flag, and the --time_partitioning_field flag. You can supply table's schema definition inline or via a JSON schema file.

Optional parameters include --expiration, --description, --time_partitioning_expiration, --destination_kms_key, and --label. Currently, DAY is the only supported value for --time_partitioning_type so this flag is not needed.

If you are creating a table in a project other than your default project, add the project ID to the dataset in the following format: [PROJECT_ID]:[DATASET].

--destination_kms_key is not demonstrated here. For more information on using this flag, see customer-managed encryption keys.

Enter the following command to create an empty partitioned table with a schema definition:

bq mk --table --expiration [INTEGER1] --schema [SCHEMA] --time_partitioning_field [COLUMN] --time_partitioning_expiration [INTEGER2] --description "[DESCRIPTION]" --label [KEY:VALUE, KEY:VALUE] [PROJECT_ID]:[DATASET].[TABLE]

Where:

  • [INTEGER1] is the default lifetime (in seconds) for the table. The minimum value is 3600 seconds (one hour). The expiration time evaluates to the current time plus the integer value. If you set the table's expiration time when you create a time-partitioned table, the dataset's default table expiration setting is ignored. Setting this value deletes the table and all partitions after the specified time.
  • [SCHEMA] is an inline schema definition in the format [FIELD]:[DATA_TYPE],[FIELD]:[DATA_TYPE] or the path to the JSON schema file on your local machine.
  • [COLUMN] is the name of the TIMESTAMP or DATE column used to create the partitions.
  • [INTEGER2] is the default lifetime (in seconds) for the table's partitions. There is no minimum value. The expiration time evaluates to the partition's date plus the integer value. The partition expiration is independent of the table's expiration but does not override it. If you set a partition expiration that is longer than the table's expiration, the table expiration takes precedence.
  • [DESCRIPTION] is a description of the table in quotes.
  • [KEY:VALUE] is the key:value pair that represents a label. You can enter multiple labels using a comma-separated list.
  • [PROJECT_ID] is your project ID.
  • [DATASET] is a dataset in your project.
  • [TABLE] is the name of the partitioned table you're creating.

When you specify the schema on the command line, you cannot include a RECORD (STRUCT) type, you cannot include a column description, and you cannot specify the column's mode. All modes default to NULLABLE. To include descriptions, modes, and RECORD types, supply a JSON schema file instead.

Examples:

Enter the following command to create a partitioned table named mypartitionedtable in mydataset in your default project. The partitioning expiration is set to 86,400 seconds (1 day), the table's expiration is set to 2,592,000 (1 30-day month), the description is set to This is my partitioned table, and the label is set to organization:development. The command uses the -t shortcut instead of --table.

The schema is specified inline as: ts:TIMESTAMP,column1:STRING,column2:INTEGER,coumn4:STRING. The specified TIMESTAMP field ts is used to create the partitions.

bq mk -t --expiration 2592000 --schema 'ts:TIMESTAMP,column1:STRING,column2:INTEGER,coumn4:STRING' --time_partitioning_field ts --time_partitioning_expiration 86400  --description "This is my partitioned table" --label org:dev mydataset.mypartitionedtable

Enter the following command to create a partitioned table named mypartitionedtable in myotherproject, not your default project. The partitioning expiration is set to 259,200 seconds (3 days), the description is set to This is my partitioned table, and the label is set to organization:development. The command uses the -t shortcut instead of --table. This command does not specify a table expiration. If the dataset has a default table expiration, it is applied. If the dataset has no default table expiration, the table will never expire, but the partitions will expire in 3 days.

The schema is specified in a local JSON file: /tmp/myschema.json. The schema definition includes a TIMESTAMP field named ts that is used to create the partitions.

bq mk -t --expiration 2592000 --schema /tmp/myschema.json --time_partitioning_field ts --time_partitioning_expiration 86400  --description "This is my partitioned table" --label org:dev myotherproject:mydataset.mypartitionedtable

After the table is created, you can update the partitioned table's table expiration, partition expiration, description, and labels.

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 . 

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
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
}

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 . 

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_ref = client.dataset('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
    )
)

Creating a partitioned table from a query result

To create a partitioned table from a query result, write the results to a new destination table. You can create a partitioned table by querying either a partitioned table or a non-partitioned table. You cannot change an existing standard table to a partitioned table using query results.

When you create a partitioned table from a query result, you must use standard SQL. Currently, legacy SQL is not supported for querying partitioned tables or for writing query results to partitioned tables.

Partition decorators enable you to write the query results to a specific partition. To adjust for timezones, use a partition decorator to write data to a partition based on your preferred timezone. For example, if you are on Pacific Standard Time (PST), write all data that is generated on May 1, 2016 PST into the partition for that date by using the corresponding partition decorator:

[TABLE_NAME]$20160501

When writing query results to a specific partition using a partition decorator, the data that is being written to the partition must conform to the table's partitioning scheme. All rows written to the partition should have values that fall within the partition's date.

For example:

The following query writes data to the February 1, 2018 partition of table T. The table has two columns — a TIMESTAMP column named TS and an INT64 column named a. Because the query writes timestamp 2018-02-01 to partition $20180201, the command completes successfully. The query runs in the US multi-region.

bq --location=US query --nouse_legacy_sql  --destination_table=T$20180201 'SELECT TIMESTAMP("2018-02-01") as TS, 2 as a'

The following query also attempts to write data to table T, but it writes timestamp 2018-01-31 to partition $20180201. This query fails because the value you're attempting to write doesn't fall within the partition's date.

bq --location=US query --nouse_legacy_sql  --destination_table=T$20180201 'SELECT TIMESTAMP("2018-01-31") as TS, 2 as a'

For information on appending to or restating (replacing) data in partitioned tables, see Appending to and overwriting time-partitioned table data. For more information on querying partitioned tables, see Querying Partitioned Tables.

Console

You cannot specify partitioning options for a destination table when you query data using the BigQuery web UI.

Classic UI

You cannot specify partitioning options for a destination table when you query data using the BigQuery web UI.

CLI

Enter the bq query command, specify the --destination_table flag to create a permanent table based on the query results, and specify the --time_partitioning_field flag to create a partitioned destination table. Currently, DAY is the only supported value for --time_partitioning_type so this flag is not needed.

Specify the use_legacy_sql=false flag to use standard SQL syntax. To write the query results to a table that is not in your default project, add the project ID to the dataset name in the following format: [PROJECT_ID]:[DATASET].

Supply the --location flag and set the value to your location.

Enter the following command to create a new, partitioned destination table from a query result:

    bq --location=[LOCATION] query --destination_table [PROJECT_ID]:[DATASET].[TABLE] --time_partitioning_field [COLUMN] --use_legacy_sql=false '[QUERY]'

Where:

  • [LOCATION] is the name of your location. The --location flag is optional if your data is in the US or the EU multi-region location. For example, if you are using BigQuery in the Tokyo region, set the flag's value to asia-northeast1. You can set a default value for the location using the .bigqueryrc file.
  • [PROJECT_ID] is your project ID.
  • [DATASET] is the name of the dataset that will contain the new partitioned table.
  • [TABLE] is the name of the partitioned table you're creating using the query results.
  • [QUERY] is a query in standard SQL syntax. Currently, you cannot use legacy SQL to query partitioned tables or to write query results to partitioned tables.

Examples:

Enter the following command to write query results to a partitioned destination table named mypartitionedtable in mydataset. mydataset is in your default project. The query retrieves data from a non-partitioned table — the NHTSA Traffic Fatality public dataset. The table's timestamp_of_crash TIMESTAMP column is used to create the partitions.

bq --location=US query --destination_table mydataset.mypartitionedtable --time_partitioning_field timestamp_of_crash --use_legacy_sql=false 'SELECT state_number, state_name, day_of_crash, month_of_crash, year_of_crash, latitude, longitude, manner_of_collision, number_of_fatalities, timestamp_of_crash FROM `bigquery-public-data.nhtsa_traffic_fatalities.accident_2016` LIMIT 100'

Enter the following command to write query results to a partitioned destination table named mypartitionedtable in mydataset. mydataset is in myotherproject, not your default project. The query retrieves data from a non-partitioned table — the NHTSA Traffic Fatality public dataset. The table's timestamp_of_crash TIMESTAMP column is used to create the partitions.

bq --location=US query --destination_table myotherproject:mydataset.mypartitionedtable --time_partitioning_field timestamp_of_crash --use_legacy_sql=false 'SELECT state_number, state_name, day_of_crash, month_of_crash, year_of_crash, latitude, longitude, manner_of_collision, number_of_fatalities, timestamp_of_crash FROM `bigquery-public-data.nhtsa_traffic_fatalities.accident_2016` LIMIT 100'

API

To save query results to a permanent partitioned table, call the jobs.insert method, configure a query job, and include a value for the configuration.query.destinationTable and timePartitioning.field properties.

Specify your location in the location property in the jobReference section of the job resource.

Creating a partitioned table when loading data

You can create a partitioned table by specifying partitioning options when you load data into a new table. You do not need to create an empty partitioned table before loading data into it. You can create the partitioned table and load your data at the same time.

When you load data into BigQuery, you can supply the table schema, or for supported data formats, you can use schema auto-detection.

Partition decorators enable you to load data into a specific partition. To adjust for timezones, use a partition decorator to load data into a partition based on your preferred timezone. For example, if you are on Pacific Standard Time (PST), load all data that is generated on May 1, 2016 PST into the partition for that date by using the corresponding partition decorator:

[TABLE_NAME]$20160501

When loading data into a specific partition using a partition decorator, the data that is being loaded into the partition must conform to the table's partitioning scheme. All rows written to the partition should have values that fall within the partition's date.

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

API

To define partitioning configuration when creating a table via a load job, you can populate the configuration.load.timePartitioning message, with the related partitioning configuration, including configuration.load.timePartitioning.expirationMs for expiring partitions over time. The population of configuration.load.timePartitioning.field governs whether this table is partitioned by pseudocolumn or user data column.

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 . 

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
gcsRef := bigquery.NewGCSReference("gs://cloud-samples-data/bigquery/us-states/us-states-by-date.csv")
gcsRef.SkipLeadingRows = 1
gcsRef.Schema = bigquery.Schema{
	{Name: "name", Type: bigquery.StringFieldType},
	{Name: "post_abbr", Type: bigquery.StringFieldType},
	{Name: "date", Type: bigquery.DateFieldType},
}
loader := client.Dataset(destDatasetID).Table(destTableID).LoaderFrom(gcsRef)
loader.TimePartitioning = &bigquery.TimePartitioning{
	Field:      "date",
	Expiration: 90 * 24 * time.Hour,
}
loader.WriteDisposition = bigquery.WriteEmpty

job, err := loader.Run(ctx)
if err != nil {
	return err
}
status, err := job.Wait(ctx)
if err != nil {
	return err
}

if status.Err() != nil {
	return fmt.Errorf("Job completed with error: %v", status.Err())
}

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 . 

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_id = 'my_dataset'
table_id = "us_states_by_date"

dataset_ref = client.dataset(dataset_id)
job_config = bigquery.LoadJobConfig()
job_config.schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
    bigquery.SchemaField("date", "DATE"),
]
job_config.skip_leading_rows = 1
job_config.time_partitioning = bigquery.TimePartitioning(
    type_=bigquery.TimePartitioningType.DAY,
    field="date",  # name of column to use for partitioning
    expiration_ms=7776000000,
)  # 90 days
uri = "gs://cloud-samples-data/bigquery/us-states/us-states-by-date.csv"

load_job = client.load_table_from_uri(
    uri, dataset_ref.table(table_id), job_config=job_config
)  # API request

assert load_job.job_type == "load"

load_job.result()  # Waits for table load to complete.

table = client.get_table(dataset_ref.table(table_id))
print("Loaded {} rows to table {}".format(table.num_rows, table_id))

Controlling access to partitioned tables

You cannot assign access controls directly to tables or partitions. You can control table access by configuring access controls at the dataset level or at the project level.

Dataset-level access controls specify the operations users, groups, and service accounts are allowed to perform on tables in that specific dataset. If you assign only dataset-level permissions, you must also assign a primitive or predefined, project-level role that provides access to the project, for example, bigquery.user.

Instead of granting access to individual datasets, you can assign predefined, project-level IAM roles that grant permissions to all table data in all datasets in a project.

You can also create IAM custom roles. If you create a custom role, the permissions you grant depend on the table operations you want the user, group, or service account to be able to perform.

For more information on roles and permissions, see:

Using partitioned tables

Getting information about partitioned tables

You can get information about tables by using the GCP Console, the classic BigQuery web UI, the bq show CLI command, or by calling the tables.get API method.

Required permissions

To get information about tables, you must be assigned the READER role on the dataset, or you must be assigned a project-level IAM role that includes bigquery.tables.get permissions. If you are granted bigquery.tables.get permissions at the project level, you can get information about all tables in the project. All predefined, project-level IAM roles include bigquery.tables.get permissions except for bigquery.user and bigquery.jobUser.

In addition, a user assigned the bigquery.user role has bigquery.datasets.create permissions. This allows a user assigned to the bigquery.user role to get information about tables in any dataset that user creates. When a user assigned to the bigquery.user role creates a dataset, that user is given OWNER access to the dataset. OWNER access to a dataset gives the user full control over it and all the tables in it.

For more information on IAM roles and permissions in BigQuery, see Access Control. For more information on dataset-level roles, see Primitive roles for datasets.

Getting partitioned table information

To view information about a partitioned table:

Console

  1. Open the BigQuery web UI in the GCP Console.
    Go to the BigQuery web UI

  2. In the navigation panel, in the Resources section, expand your project and dataset, then click the table name in the list.

  3. Click Details below the Query editor. This tab displays the table's description and table information.

    Table details

  4. Click the Schema tab to view the table's schema definition. Notice partitioned tables do not include the _PARTITIONTIME pseudo column.

Classic UI

  1. In the navigation pane, down arrow icon down arrow icon to the left of your dataset to expand it, or double-click the dataset name. This displays the tables and views in the dataset.

  2. Click the table name.

  3. Click Details. The Table Details page displays the table's description and table information.

    Partitioned table details

  4. Click the Schema tab to view the table's schema definition. Notice partitioned tables do not include the _PARTITIONTIME pseudo column.

Command-line

Issue the bq show command to display all table information. Use the --schema flag to display only table schema information. The --format flag can be used to control the output.

If you are getting information about a table in a project other than your default project, add the project ID to the dataset in the following format: [PROJECT_ID]:[DATASET].

bq show --schema --format=prettyjson [PROJECT_ID]:[DATASET].[TABLE]

Where:

  • [PROJECT_ID] is your project ID.
  • [DATASET] is the name of the dataset.
  • [TABLE] is the name of the table.

Examples:

Enter the following command to display all information about mytable in mydataset. mydataset is in your default project.

bq show --format=prettyjson mydataset.mytable

Enter the following command to display all information about mytable in mydataset. mydataset is in myotherproject, not your default project.

bq show --format=prettyjson myotherproject:mydataset.mytable

Enter the following command to display only schema information about mytable in mydataset. mydataset is in myotherproject, not your default project.

bq show --schema --format=prettyjson myotherproject:mydataset.mytable

API

Call the bigquery.tables.get method and provide any relevant parameters.

Listing partitioned tables in a dataset

You can list tables in datasets (including partitioned tables) by using the GCP Console, the classic BigQuery web UI, the bq ls CLI command, or by calling the tables.list API method.

Required permissions

To list tables in a dataset, you must be assigned the READER role on the dataset, or you must be assigned a project-level IAM role that includes bigquery.tables.list permissions. If you are granted bigquery.tables.list permissions at the project level, you can list tables in any dataset in the project. All predefined, project-level IAM roles include bigquery.tables.list permissions except for bigquery.jobUser.

For more information on IAM roles and permissions in BigQuery, see Access control. For more information on dataset-level roles, see Primitive roles for datasets.

Listing partitioned tables

To list the tables in a dataset (including partitioned tables):

Console

  1. Open the BigQuery web UI in the GCP Console.
    Go to the BigQuery web UI

  2. In the navigation panel, in the Resources section, expand your project and click on your dataset.

  3. Scroll through the list to see the tables in the dataset. Tables, partitioned tables, models, and views are identified by different icons.

Classic UI

  1. In the BigQuery web UI, in the navigation pane, down arrow icon down arrow icon to the left of your dataset to expand it, or double-click the dataset name. This displays the tables and views in the dataset.

  2. Scroll through the list to see the tables in the dataset. Tables and views are identified by different icons.

    View tables

CLI

Issue the bq ls command. The --format flag can be used to control the output. If you are listing tables in a project other than your default project, add the project ID to the dataset in the following format: [PROJECT_ID]:[DATASET].

bq ls --format=pretty [PROJECT_ID]:[DATASET]

Where:

  • [PROJECT_ID] is your project ID.
  • [DATASET] is the name of the dataset.

When you run the command, the Type field displays either TABLE or VIEW. For partitioned tables, the Time Partitioning field displays DAY, the column used to create the partitions, and the partition expiration time in milliseconds (if any).

For example:

+-------------------------+-------+----------------------+---------------------------------------------------+
|         tableId         | Type  |        Labels        | Time Partitioning                                 |
+-------------------------+-------+----------------------+---------------------------------------------------+
| mytable                 | TABLE | department:shipping  |  DAY (field: source_date, expirationMs: 86400000) |
| myview                  | VIEW  |                      |                                                   |
+-------------------------+-------+----------------------+---------------------------------------------------+

Examples:

Enter the following command to list tables in dataset mydataset in your default project.

bq ls --format=pretty mydataset

Enter the following command to list tables in dataset mydataset in myotherproject.

bq ls --format=pretty myotherproject:mydataset

API

To list tables using the API, call the tables.list method.

Listing partitions in partitioned tables

You can list the partitions in a partitioned table by querying the __PARTITIONS_SUMMARY__ meta table using legacy SQL.

You can run the query in the GCP Console, the classic BigQuery web UI, using the bq query command, or by calling the jobs.insert method and configuring a query job.

Required permissions

To run a query job that uses the __PARTITIONS_SUMMARY__ meta-table, you must have bigquery.jobs.create permissions. The following predefined, project-level IAM roles include bigquery.jobs.create permissions:

You must also be assigned the READER role at the dataset level, or you must be assigned a project-level IAM role that includes bigquery.tables.getData permissions. All predefined, project-level IAM roles include bigquery.tables.getData permissions except for bigquery.user, bigquery.jobUser, and bigquery.metadataViewer.

For more information on IAM roles and permissions in BigQuery, see Access control. For more information on dataset-level roles, see Primitive roles for datasets.

Listing partitions in a partitioned table

You can list partitions in a partitioned table using legacy SQL. To list partitions in a partitioned table:

Console

  1. Open the BigQuery web UI in the GCP Console.
    Go to the BigQuery web UI

  2. Click the Compose new query button.

  3. Enter the following text into the Query editor box to query the __PARTITIONS_SUMMARY__ meta-table:

    #legacySQL
SELECT
  partition_id
FROM
  [[DATASET].[TABLE]$__PARTITIONS_SUMMARY__]

    Where:

    • [DATASET] is the dataset that contains the table.
    • [TABLE] is the name of the table.

  4. Click More and select Query settings.

    Query settings

  5. For Processing location, click Auto-select and choose your data's location. You can leave processing location set to auto-select if your data is in the US or EU multi-region location. When your data is in the US or the EU, the processing location is automatically detected.

    Query processing location

  6. Click Run.

Classic UI

  1. Go to the BigQuery web UI.

    Go to the BigQuery web UI

  2. Click the Compose query button.

  3. Enter the following text into the New Query box to query the __PARTITIONS_SUMMARY__ meta-table:

    #legacySQL
SELECT
  partition_id
FROM
  [[DATASET].[TABLE]$__PARTITIONS_SUMMARY__]

    Where:

    • [DATASET] is the dataset that contains the table.
    • [TABLE] is the name of the table.

  4. Click Show Options.

  5. For Processing Location, click Unspecified and choose your data's location. You can leave processing location set to unspecified if your data is in the US or EU multi-region location. When your data is in the US or the EU, the processing location is automatically detected.

  6. Click Run query.

CLI

Enter the following query using the bq query command:

bq --location=[LOCATION] query --use_legacy_sql=true '
SELECT
  partition_id
FROM
  [[DATASET].[TABLE]$__PARTITIONS_SUMMARY__]'

Where:

  + `[LOCATION]` is the name of your location. The `--location` flag is
    optional if your data is in the `US` or the `EU` multi-region
    location. For example, if you are using BigQuery in
    the Tokyo region, set the flag's value to `asia-northeast1`. You can
    set a default value for the location using the [.bigqueryrc file](/bigquery/docs/bq-command-line-tool#setting_default_values_for_command-line_flags).
  + `[DATASET]` is the dataset that contains the table.
  + `[TABLE]` is the name of the table.

API

Call the jobs.insert method and configure a query job that queries the table's __PARTITIONS_SUMMARY__ meta table.

Specify your location in the location property in the jobReference section of the job resource.

Getting table metadata using meta tables

You can get information about partitioned tables by using special tables called meta tables. Meta tables contain metadata such as the list of tables and views in a dataset. The meta tables are read only.

Getting partition metadata using meta tables

The __PARTITIONS_SUMMARY__ meta table is a special table whose contents represent metadata about partitions in a time-partitioned table. The __PARTITIONS_SUMMARY__ meta table is read-only.

To access metadata about the partitions in a time-partitioned table, use the __PARTITIONS_SUMMARY__ meta-table in a query's SELECT statement. You can run the query by using the console, the classic BigQuery web UI, the command-line tool's bq query command, or by calling the jobs.insert API method and configuring a query job.

Currently, standard SQL does not support the partition decorator separator ($) so you cannot query __PARTITIONS_SUMMARY__ in standard SQL. A legacy SQL query that uses the __PARTITIONS_SUMMARY__ meta-table looks like the following:

    SELECT [COLUMN] FROM [[DATASET].[TABLE]$__PARTITIONS_SUMMARY__]

Where:

  • [DATASET] is the name of your dataset.
  • [TABLE] is the name of the time-partitioned table.
  • [COLUMN] is one of the following:
Value Description
project_id Name of the project.
dataset_id Name of the dataset.
table_id Name of the time-partitioned table.
partition_id Name (date) of the partition.
creation_time The time at which the partition was created, in milliseconds since January 1, 1970 UTC.
last_modified_time The time at which the partition was last modified, in milliseconds since January 1, 1970 UTC.

Partition meta table permissions

To run a query job that uses the __PARTITIONS_SUMMARY__ meta-table, you must have bigquery.jobs.create permissions. The following predefined, project-level IAM roles include bigquery.jobs.create permissions:

You must also be assigned the READER role at the dataset level, or you must be assigned a project-level IAM role that includes bigquery.tables.getData permissions. All predefined, project-level IAM roles include bigquery.tables.getData permissions except for bigquery.user, bigquery.jobUser, and bigquery.metadataViewer.

Partition meta table examples

The following query retrieves all partition metadata for a time-partitioned table named mydataset.mytable.

Console 

#legacySQL
SELECT
  *
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]

Classic UI 

#legacySQL
SELECT
  *
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]

Command-line 

bq query --use_legacy_sql=true '
SELECT
  *
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]'

The output looks like the following:

+----------------+------------+----------------+--------------+---------------+--------------------+
|   project_id   | dataset_id |    table_id    | partition_id | creation_time | last_modified_time |
+----------------+------------+----------------+--------------+---------------+--------------------+
| myproject      | mydataset  | mytable        | 20160314     | 1517190224120 | 1517190224997      |
| myproject      | mydataset  | mytable        | 20160315     | 1517190224120 | 1517190224997      |
+----------------+------------+----------------+--------------+---------------+--------------------+

The following query lists the times when the partitions in mydataset.mytable were last modified.

Console 

#legacySQL
SELECT
  partition_id,
  last_modified_time
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]

Classic UI 

#legacySQL
SELECT
  partition_id,
  last_modified_time
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]

Command-line 

bq query --use_legacy_sql=true '
SELECT
  partition_id,
  last_modified_time
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]'

The output looks like the following:

+--------------+--------------------+
| partition_id | last_modified_time |
+--------------+--------------------+
| 20160102     |      1471632556179 |
| 20160101     |      1471632538142 |
| 20160103     |      1471632570463 |
+--------------+--------------------+

To display the last_modified_time field in human-readable format, use the FORMAT_UTC_USEC function. For example:

Console 

#legacySQL
SELECT
  partition_id,
  FORMAT_UTC_USEC(last_modified_time*1000) AS last_modified
FROM
  [mydataset.table1$__PARTITIONS_SUMMARY__]

Classic UI 

#legacySQL
SELECT
  partition_id,
  FORMAT_UTC_USEC(last_modified_time*1000) AS last_modified
FROM
  [mydataset.table1$__PARTITIONS_SUMMARY__]

Command-line 

bq query --use_legacy_sql=true '
SELECT
  partition_id,
  FORMAT_UTC_USEC(last_modified_time*1000) AS last_modified
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]'

The output looks like the following:

+--------------+----------------------------+
| partition_id |       last_modified        |
+--------------+----------------------------+
| 20160103     | 2016-08-19 18:49:30.463000 |
| 20160102     | 2016-08-19 18:49:16.179000 |
| 20160101     | 2016-08-19 18:48:58.142000 |
+--------------+----------------------------+

Next steps

¿Te ha resultado útil esta página? Enviar comentarios:

Enviar comentarios sobre...

Esta página
Comentarios sobre la documentación
BigQuery
Comentarios sobre el producto
Si necesitas ayuda, visita nuestra página de asistencia.