This document describes how to create and use ingestion-time partitioned tables in BigQuery. For information on partitioned tables, see Creating and Using Partitioned Tables.
After creating an ingestion-time 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.
Creating ingestion-time partitioned tables
You can create an ingestion-time partitioned table in BigQuery:
- By using a DDL
CREATE TABLEstatement with apartition_expression - Manually using the BigQuery web UI or the command line tool's
bq mkcommand - Programmatically by calling the
tables.insertAPI method - From query results
- When you load data
- By converting date-sharded tables into a partitioned tables
When you create an ingestion-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 an ingestion-time 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 ingestion-time partitioned table with a schema definition
When you create an empty time-partitioned table with a schema definition, you can:
- Enter the schema using the web UI
- Provide the schema inline using the command-line tool
- Submit a JSON schema file using the command-line tool
- Provide the schema in a table resource
when calling the API's
tables.insertmethod.
For more information on specifying a table schema, see Specifying a Schema.
After the time-partitioned table is created, you can:
- Load data into it
- Write query results to it
- Copy data into it
To create an empty time-partitioned table with a schema definition:
Web UI
Click the down arrow icon
next to your dataset name in the navigation and click Create new table.On the Create Table page, in the Source Data section, click Create empty table.
On the Create Table page, in the Destination Table section:
- For Table name, choose the appropriate dataset, and in the table name field, enter the name of the table you're creating.
- Verify that Table type is set to Native table.
In the Schema section, manually enter the schema definition.
You can enter schema information manually by:
Clicking Edit as text and entering the table schema as a JSON array:
Using Add Field to input the schema:
In the Options section:
- For Partitioning, click None and select Day.
- For Encryption Type, leave the
Defaultoption. This property is for customer-managed encryption keys. By default, BigQuery encrypts customer content stored at rest.
Click Create Table.
After the table is created, you can update the partitioned table's table expiration, description, and labels. You cannot add a partition expiration after a table is created using the BigQuery web UI.
Command-line
Use the mk command with the --table flag (or -t shortcut), and the
--time_partitioning_type=DAY flag. Currently, DAY is the only supported
value for --time_partitioning_type. 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. 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 ingestion-time partitioned table with a schema definition:
bq mk --table --expiration [INTEGER1] --time_partitioning_type=DAY --time_partitioning_expiration [INTEGER2] --description "[DESCRIPTION]" --label [KEY:VALUE, KEY:VALUE] [PROJECT_ID]:[DATASET].[TABLE] [SCHEMA]
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 partitioned table, the dataset's default table expiration setting is ignored. Setting this value deletes the table and all partitions after the specified time.[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 time-partitioned table you're creating.[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.
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 an ingestion-time partitioned table
named mytable in mydataset in your default project. The partitioning
expiration is set to 259,200 seconds (3 days), the table's expiration is set
to 2,592,000 (1 30-day month), the description is set to
This is my time-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:
qtr:STRING,sales:FLOAT,year:STRING.
bq mk -t --expiration 2592000 --time_partitioning_type=DAY --time_partitioning_expiration 259200 --description "This is my time-partitioned table" --label organization:development mydataset.mytable qtr:STRING,sales:FLOAT,year:STRING
Enter the following command to create a time-partitioned table named
mytable 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 time-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 path to the schema file is
/tmp/myschema.json.
bq mk -t --time_partitioning_type=DAY --time_partitioning_expiration 259200 --description "This is my time-partitioned table" --label organization:development myotherproject:mydataset.mytable /tmp/myschema.json
After the table is created, you can update the ingestion-time 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.
Creating an ingestion-time 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 new partitioned table by querying a partitioned table or a non-partitioned table. You cannot change an existing table to a partitioned table using query results.
For more information on querying time-partitioned tables, see Querying Partitioned Tables.
Web 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_type=DAY flag to create an ingestion-time partitioned
destination table. Currently, DAY is the only supported value for
--time_partitioning_type.
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, ingestion-time partitioned destination table from a query result:
bq --location=[LOCATION] query --destination_table [PROJECT_ID]:[DATASET].[TABLE] --time_partitioning_type=DAY --use_legacy_sql=false '[QUERY]'
Where:
[LOCATION]is the name of your location. The--locationflag is optional. For example, if you are using BigQuery in the Tokyo region, you can set the flag's value toasia-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 time-partitioned table.[TABLE]is the name of the time-partitioned table you're creating using the query results.[QUERY]is a query in standard SQL syntax.
Examples:
Enter the following command to write query results to an ingestion-time
partitioned destination table named mytable in mydataset. The
dataset is in your default project. The query retrieves data from a
non-partitioned table — the USA Name Data
public dataset.
bq --location=US query --destination_table mydataset.mytable --time_partitioning_type=DAY --use_legacy_sql=false 'SELECT name,number FROM `bigquery-public-data.usa_names.usa_1910_current` WHERE gender = "M" ORDER BY number DESC'
Enter the following command to write query results to an ingestion-time
partitioned destination table named mytable in mydataset. The
dataset is in myotherproject, not your default project. The query
retrieves data from a non-partitioned table — the USA Name Data
public dataset.
bq --location=US query --destination_table myotherproject:mydataset.mytable --time_partitioning_type=DAY --use_legacy_sql=false 'SELECT name,number FROM `bigquery-public-data.usa_names.usa_1910_current` WHERE gender = "M" ORDER BY number DESC'
API
To save query results to a permanent, ingestion-time partitioned table, call
the jobs.insert method,
configure a query
job, and include a value for the configuration.query.destinationTable
and timePartitioning
properties.
Specify your location in the location property in the
jobReference section of the job resource.
Creating an ingestion-time partitioned table when loading data
You can create an ingestion time-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
For more information on loading data, see Introduction to Loading Data into BigQuery.
Converting date-sharded tables into ingestion-time partitioned tables
If you have 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. The date-sharded tables must
use the following naming convention: [TABLE]_YYYYMMDD. For example,
mytable_20160101, ... , mytable_20160331.
Optional parameters include --time_partitioning_expiration and
--time_partitioning_type. Since --time_partitioning_type=DAY is currently
the only supported value, this parameter is optional. If your source tables or
your destination table are in a project other than 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 convert a series of date-sharded tables to a single ingestion-time partitioned table:
bq --location=[LOCATION] partition --time_partitioning_type=DAY --time_partitioning_expiration [INTEGER] [PROJECT_ID]:[DATASET].[SOURCE_TABLE]_ [PROJECT_ID]:[DATASET].[DESTINATION_TABLE]
Where:
[LOCATION]is the name of your location. The--locationflag is optional. For example, if you are using BigQuery in the Tokyo region, set the flag's value toasia-northeast1. You can set a default value for the location using the .bigqueryrc file.[INTEGER]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.[PROJECT_ID]is your project ID.[DATASET]is a dataset in your project.[SOURCE_TABLE]_is the prefix of your date-sharded table.[DESTINATION_TABLE]is the name of the partitioned table you're creating.
Examples:
Enter the following command to create an ingestion-time partitioned table named
mytable_partitioned in mydataset in your default project. The partitioning
expiration is set to 259,200 seconds (3 days). The date sharded source tables
are prefixed with sourcetable_. The source tables are also in your default
project. mydataset was created in the US multi-region location.
bq --location=US partition --time_partitioning_type=DAY --time_partitioning_expiration 259200 mydataset.sourcetable_ mydataset.mytable_partitioned
When you run the command, a copy job is created that generates partitions from
the sharded tables. For example, if the date-sharded tables were sourcetable_20180126
and sourcetable_20180127, the copy job would create the following partitions:
mydataset.mytable_partitioned$20180126 and
mydataset.mytable_partitioned$20180127.
Enter the following command to create an ingestion-time partitioned table named
mytable_partitioned in mydataset. mydataset is in myotherproject, not
your default project. The partitioning expiration is set to 86,400 seconds
(1 day). The date sharded source tables are prefixed with sourcetable_. The
source tables are in your default project. mydataset was created in the US
multi-region location.
bq --location=US partition --time_partitioning_type=DAY --time_partitioning_expiration 86400 mydataset.sourcetable_ myotherproject:mydataset.mytable_partitioned
Enter the following command to create an ingestion-time partitioned table named
mytable_partitioned in mydataset in your default project. mydataset was
created in the asia-northeast1 region. The partitioning
expiration is set to 259,200 seconds (3 days). The date sharded source tables
are prefixed with sourcetable_. The source tables are also in your default
project.
bq --location=asia-northeast1 partition --time_partitioning_type=DAY --time_partitioning_expiration 259200 mydataset.sourcetable_ mydataset.mytable_partitioned
Controlling access to time-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:
- Understanding Roles in the IAM documentation
- BigQuery Access Controls
- Assigning access controls to datasets.
Using ingestion-time partitioned tables
Getting information about ingestion-time partitioned tables
You can get information about tables using the BigQuery web UI, using 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.jobUser and
bigquery.user.
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 ingestion-time partitioned table information
To view information about an ingestion-time partitioned table:
Web UI
In the navigation pane, 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.Click the table name.
Click Details. The Table Details page displays the table's description and table information.
Click the Schema tab to view the table's schema definition. Notice the additional pseudo column
_PARTITIONTIMEthat contains the date-based timestamp for data that is loaded into the table.
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.
When you show schema information for a time-partitioned table, the
_PARTITIONTIME pseudo column is not displayed.
bq show --schema --format=prettyjson myotherproject:mydataset.mytable
API
Call the bigquery.tables.get
method and provide any relevant parameters.
Listing ingestion-time partitioned tables in a dataset
You can list tables in datasets (including partitioned tables) by using the
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):
Web UI
In the web UI, in the navigation pane, 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.Scroll through the list to see the tables in the dataset. Tables and views are identified by different icons.
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 ingestion-time partitioned tables, the Time Partitioning field
displays DAY and the partition expiration time in milliseconds (if any).
For example:
+-------------------------+-------+----------------------+--------------------------------+ | tableId | Type | Labels | Time Partitioning | +-------------------------+-------+----------------------+--------------------------------+ | mytable | TABLE | department:shipping | DAY (expirationMs: 259200000) | | 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 ingestion-time partitioned tables
You can list the partitions in an ingestion-time partitioned table by querying
the _PARTITIONTIME pseudo column (in standard SQL) or the
__PARTITIONS_SUMMARY__ meta table (in legacy SQL).
You can run the query in the 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, or the
_PARTITIONTIME pseudo column, 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 an ingestion-time partitioned table
You can list partitions in an ingestion-time partitioned table using standard SQL (preferred) or legacy SQL. To list partitions:
Standard SQL:
Web UI
Go to the BigQuery web UI.
Click the Compose query button.
Enter the following text into the New Query box to query a table's
_PARTITIONTIMEpseudo column:#standardSQL SELECT _PARTITIONTIME as pt FROM `[DATASET].[TABLE]` GROUP BY 1
Where:
[DATASET]is the dataset that contains the table.[TABLE]is the name of the table.
Click Show Options.
(Optional) For Processing Location, click Unspecified and choose your data's location.
Click Run query.
CLI
Enter the following query using the bq query command:
bq --location=[LOCATION] query --use_legacy_sql=false '
SELECT
_PARTITIONTIME as pt
FROM
`[DATASET].[TABLE]`
GROUP BY 1'
Where:
+ `[LOCATION]` is the name of your location. The `--location` flag is
optional. For example, if you are using BigQuery in
the Tokyo region, you can 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 _PARTITIONTIME pseudo column.
Specify your location in the location property in the
jobReference section of the job resource.
Legacy SQL:
Web UI
Go to the BigQuery web UI.
Click the Compose query button.
Enter the following text into the New Query box to query a table's
__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.
Click Show Options.
(Optional) For Processing Location, click Unspecified and choose your data's location.
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. For example, if you are using BigQuery in
the Tokyo region, you can 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 __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
The process for getting metadata about an ingestion-time partitioned table is the same as the process for getting metadata about a standard table. For more information, see Getting table metadata using meta tables.
When you query the __TABLES_SUMMARY__ meta-table, all tables are listed. There
is no type option to distinguish partitioned tables from non-partitioned
tables.
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 using the BigQuery web UI, using 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.
Web 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.
Web 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:
Web 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 | +--------------+----------------------------+
Example: Creating an ingestion-time partitioned table using query results
In this example, you create an ingestion-time partitioned table by using the command-line tool, and you add data to three partitions using query results. The table will contain weather data, partitioned by date, for the first three days in 2016.
This example queries the NOAA GSOD weather dataset
for temperatures for the first three days of 2016 and writes the results to the
a partitioned table. This example uses the --location=US flag because you
are querying a public dataset. The BigQuery public datasets are
stored in the US multi-region. Because the public datasets are stored in the US,
you cannot write public data query results to a table in another region, and you
cannot join tables in public datasets with tables in another region.
Step 1. Create an empty ingestion-time partitioned table named temps in a
dataset named mydataset. There is no need to specify a schema for the table.
The schema from the query results is used as the schema definition for the table.
bq mk --time_partitioning_type=DAY mydataset.temps
To view the configuration settings, use the bq show command:
bq show --format=prettyjson mydataset.temps
Look for the timePartitioning entry in the bq show command output:
{
...
"timePartitioning": {
"type": "DAY"
},
"type": "TABLE"
}
Step 2. Query the NOAA GSOD weather dataset
for temperatures for the first three days of 2016 and write the results to the
respective partitions in the temps table. The following queries use the
--destination_table option and standard SQL syntax to write 100 rows of query
results to a partition.
-
Run the following query to write January 1, 2016 temperatures to partition
mydataset.temps$20160101:bq --location=US query --use_legacy_sql=false \ --destination_table 'mydataset.temps$20160101' \ 'SELECT stn,temp from `bigquery-public-data.noaa_gsod.gsod2016` WHERE mo="01" AND da="01" limit 100' -
Run the following query to write January 2, 2016 temperatures to partition
mydataset.temps$20160102:bq --location=US query --use_legacy_sql=false \ --destination_table 'mydataset.temps$20160102' \ 'SELECT stn,temp from `bigquery-public-data.noaa_gsod.gsod2016` WHERE mo="01" AND da="02" limit 100' -
Run the following query to write January 3, 2016 temperatures to partition
mydataset.temps$20160103:bq --location=US query --use_legacy_sql=false \ --destination_table 'mydataset.temps$20160103' \ 'SELECT stn,temp from `bigquery-public-data.noaa_gsod.gsod2016` WHERE mo="01" AND da="03" limit 100'
Step 3. Confirm that you have 300 rows in your table by using the bq show
command.
bq show mydataset.temps
The results show the Schema and Total Rows.
Last modified Schema Total Rows Total Bytes Expiration Time Partitioning Labels kmsKeyName
----------------- ---------------- ------------ ------------- ----------------- ------------------- -------- ------------
28 Jan 15:03:45 |- stn: string 300 4800 29 Jan 15:00:32 DAY
|- temp: float
Next steps
- For an overview of partitioned table support in BigQuery, see Introduction to Partitioned Tables.
- To learn how to create and use partitioned tables, see Creating and Using Partitioned Tables.
- To learn how to manage and update partitioned tables, see Managing Partitioned Tables.
- For information on querying partitioned tables, see Querying Partitioned Tables.
Need help? Visit our