BigQuery pricing

For BigQuery pricing, refer to this page.

For BigQuery ML, see the BigQuery ML pricing page.

For BigQuery Data Transfer Service, see the BigQuery Data Transfer Service pricing page.

Overview

BigQuery offers scalable, flexible pricing options to meet your technical needs and your budget.

Storage costs are based on the amount of data stored in BigQuery. Storage charges can be:

  • Active — A monthly charge for data stored in tables or in partitions that have been modified in the last 90 days.
  • Long-term — A lower monthly charge for data stored in tables or in partitions that have not been modified in the last 90 days.

For query costs, you can choose between two pricing models:

  • On-demand — This is the most flexible option. On-demand pricing is based on the amount of data processed by each query you run.
  • Flat-rate — This predictable pricing option is best for customers with fixed budgets. Flat-rate customers purchase dedicated resources for query processing and are not charged for individual queries.

For more information on storage and query pricing, see Google Cloud Platform SKUs. Note that on-demand query pricing is referred to as analysis pricing on the SKUs page.

Pricing summary

The following table summarizes BigQuery pricing. BigQuery's Quotas and limits apply to these operations.

US (multi-region) EU (multi-region) Los Angeles (us-west2) Montréal (northamerica-northeast1) Northern Virginia (us-east4) São Paulo (southamerica-east1) Finland (europe-north1) London (europe-west2) Zürich (europe-west6) Hong Kong (asia-east2) Mumbai (asia-south1) Taiwan (asia-east1) Tokyo (asia-northeast1) Singapore (asia-southeast1) Sydney (australia-southeast1)
Monthly
Operation Pricing Details
Active storage The first 10 GB is free each month. See Storage pricing for details.
Long-term storage The first 10 GB is free each month. See Storage pricing for details.
BigQuery Storage API The BigQuery Storage API is not included in the free tier.
Streaming Inserts You are charged for rows that are successfully inserted. Individual rows are calculated using a 1 KB minimum size. See Streaming pricing for details.
Queries (on-demand) First 1 TB per month is free, see On-demand pricing for details.
Queries (monthly flat-rate) You can purchase additional slots in 500 slot increments. For details, see Monthly flat-rate pricing.
Queries (annual flat-rate) You can purchase additional slots in 500 slot increments. You are billed monthly. For details, see Annual flat-rate pricing.

If you pay in a currency other than USD, the prices listed in your currency on Cloud Platform SKUs apply.

How charges are billed

Each project you create has a billing account attached to it. Any charges incurred by BigQuery jobs run in the project (such as query jobs) are billed to the attached billing account. This is true even if project resources are shared with others outside your organization. BigQuery storage costs are also billed to the attached billing account.

How to analyze billing data

You can view BigQuery costs and trends by using the Cloud Billing reports page in the GCP Console. For information on analyzing billing data using reports, see View your cost trends with billing reports.

For information on analyzing your billing data in BigQuery, see Export billing data to BigQuery in the Cloud Billing documentation.

Free operations

The following table shows BigQuery operations that are free of charge in every location. BigQuery's Quotas and limits apply to these operations.

Operation Details
Loading data

When you load data into BigQuery from Cloud Storage, you are not charged for the load operation, but you do incur charges for storing the data in Cloud Storage. See Data storage on the Cloud Storage pricing page for details. Once the data is loaded into BigQuery, the data is subject to BigQuery's Storage pricing. For more information, see Loading data into BigQuery.

When you create a dataset in BigQuery, you must choose a location for the data. If you choose US, you can load data into tables in the dataset from a Cloud Storage bucket in any other region. When you load data from another region into a US dataset, there is currently no charge for internet egress.

If you choose a location other than US, you must do one of the following:

  • Load data from a Cloud Storage bucket in that region (the bucket can be either a multi-regional bucket or a regional bucket in the same region as the dataset)
  • Copy the data into a bucket in that region

When you copy data from one Cloud Storage region to another, Cloud Storage network pricing applies.

Copying data You are not charged for copying a table, but you do incur charges for storing the new table and the table you copied. For more information, see Copying an existing table.
Exporting data When you export data from BigQuery to Cloud Storage, you are not charged for the export operation, but you do incur charges for storing the data in Cloud Storage. See Data storage on the Cloud Storage pricing page for details. For more information, see Exporting data from BigQuery.
Deleting datasets You are not charged for deleting a dataset.
Deleting tables, views, and partitions You are not charged for deleting a table, deleting a view, or deleting individual table partitions.
Metadata operations You are not charged for list, get, patch, update and delete calls. Examples include (but are not limited to): listing datasets, updating a dataset's access control list, and updating a table's description.
Reading pseudo columns You are not charged for querying the contents of the following pseudo columns:

_TABLE_SUFFIX — Used when querying wildcard tables or to achieve table decorator semantics in standard SQL
_PARTITIONDATE — Used when querying ingestion-time partitioned tables
_PARTITIONTIME — Used when querying ingestion-time partitioned tables
_FILE_NAME — Used when querying tables based on external data sources
Reading meta tables You are not charged for querying the contents of the following meta tables:

__PARTITIONS_SUMMARY__ — Used when getting metadata about partitions in a partitioned table or an ingestion-time partitioned table
__TABLES_SUMMARY__ — Used when getting metadata about the tables and views in a dataset

Always free usage limits

As part of the Google Cloud Platform Free Tier, BigQuery offers some resources free of charge up to a specific limit. These free usage limits are available during and after the free trial period. If you go over these usage limits and are no longer in the free trial period, you will be charged according to the pricing on this page.

Resource Monthly Free Usage Limits Details
Storage The first 10 GB per month is free. BigQuery ML models and training data stored in BigQuery are included in the BigQuery storage free tier.
Queries (analysis) The first 1 TB of query data processed per month is free. Queries that use BigQuery ML prediction, inspection, and evaluation functions are included in the BigQuery analysis free tier. BigQuery ML queries that contain CREATE MODEL statements are not.
BigQuery flat-rate pricing is also available for high-volume customers that prefer a stable, monthly cost.
BigQuery ML CREATE MODEL queries The first 10 GB of data processed by queries that contain CREATE MODEL statements per month is free. BigQuery ML CREATE MODEL queries are independent of the BigQuery analysis free tier.

Query pricing

Query pricing refers to the cost of running your SQL commands, and user-defined functions, and qualifying Data Manipulation Language (DML) and Data Definition Language (DDL) statements.

BigQuery offers a choice of two pricing models:

  • On-demand pricing is flexible and efficient. You pay only for the queries you run.
  • Flat-rate pricing offers predictable and consistent month-to-month costs.

By default, you are billed according to the on-demand pricing model. You may choose a pricing model that meets your needs. You may also mix-and-match the two pricing models for each project and each location.

On-demand pricing

Under on-demand pricing, BigQuery charges for queries by using one metric: the number of bytes processed (also referred to as bytes read). You are charged for the number of bytes processed whether the data is stored in BigQuery or in an external data source such as Cloud Storage, Google Drive, or Cloud Bigtable. On-demand pricing is based solely on usage.

On-demand query pricing is as follows:

US (multi-region) EU (multi-region) Los Angeles (us-west2) Montréal (northamerica-northeast1) Northern Virginia (us-east4) São Paulo (southamerica-east1) Finland (europe-north1) London (europe-west2) Zürich (europe-west6) Hong Kong (asia-east2) Mumbai (asia-south1) Taiwan (asia-east1) Tokyo (asia-northeast1) Singapore (asia-southeast1) Sydney (australia-southeast1)
Monthly
Operation Pricing Details
Queries (on-demand) First 1 TB per month is free. Flat-rate pricing is also available for high-volume customers that prefer a stable, monthly cost.

If you pay in a currency other than USD, the prices listed in your currency on Cloud Platform SKUs apply.

Note the following regarding query charges:

  • BigQuery uses a columnar data structure. You're charged according to the total data processed in the columns you select, and the total data per column is calculated based on the types of data in the column. For more information about how your data size is calculated, see data size calculation.
  • You aren't charged for queries that return an error, or for queries that retrieve results from the cache.
  • Charges are rounded to the nearest MB, with a minimum 10 MB data processed per table referenced by the query, and with a minimum 10 MB data processed per query.
  • Cancelling a running query job may incur charges up to the full cost for the query were it allowed to run to completion.
  • When you run a query, you're charged according to the data processed in the columns you select, even if you set an explicit LIMIT on the results.
  • Partitioning and clustering your tables can help reduce the amount of data processed by queries. As a best practice, use partitioning and clustering whenever possible.
  • On-demand query pricing is referred to as analysis pricing on the Google Cloud Platform SKUs page.

On-demand query cost controls

BigQuery provides cost control mechanisms that enable you to cap your query costs. You can set:

Flat-rate pricing

BigQuery offers flat-rate pricing for customers who prefer a stable monthly cost for queries rather than paying the on-demand price per TB of data processed.

When you enroll in flat-rate pricing, you purchase dedicated query processing capacity which is measured in BigQuery slots. The cost of all bytes processed is included in the monthly flat-rate price. If your queries exceed your flat-rate capacity, your queries are queued until your flat-rate resources become available.

Flat-rate pricing:

  • Applies to query costs, including DML and DDL statements.
  • Does not apply to storage costs. See Storage pricing for details on storage costs.
  • Is purchased as a regional resource. Flat-rate capacity purchased in one region cannot be used in another region.
  • Allows customers to raise per-project concurrency quotas by contacting Google Cloud Platform Support.
  • Is available in monthly and annual commitments.

Flat-rate pricing details

When you enroll in flat-rate pricing:

  • Monthly commitments cannot be canceled or downgraded for 30 calendar days from the purchase confirmation date.

    After the first 30 calendar days, you can cancel or downgrade at any time. If you cancel or downgrade, your charges are prorated per-second at the monthly rate.

    For example:

    • You cannot cancel on day 29.
    • If you cancel during the first second of day 31, you are charged for 30 days and 1 second.
    • If you cancel at the midpoint of the third month, you are charged 50% of your monthly rate for that month.
  • Annual commitments cannot be canceled or downgraded for one calendar year.

    Prior to the anniversary of your commitment date, you can choose to renew for another year, or you can move to a monthly flat-rate plan beginning after the end of the year. If you move to the monthly rate, you can cancel any time, and you are charged per-second at the monthly rate.

    For example:

    • If you renew for another year after your annual commitment date, you enter into a new annual commitment, and you continue to be charged the yearly commitment rate.
    • If you do not renew for another year after your annual commitment date, you can cancel at any time, and your charges are prorated per-second at the monthly rate.
  • To purchase additional BigQuery slots, you must enter into a new commitment.

  • Flat-rate pricing is purchased for a specific BigQuery location
    When you purchase a flat-rate plan, you specify the allocation of slots by location. To use slots in multiple locations, you must purchase slots in each location.
  • Flat-rate and on-demand pricing can be used together
    A project can use either flat-rate or on-demand pricing. If you have multiple projects in a given location, you can choose which projects use flat-rate pricing and which projects use on-demand pricing.
  • To discontinue a flat-rate pricing plan, you must cancel or downgrade your commitment.

Monthly flat-rate pricing

When you enroll in flat-rate pricing, the capacity you purchase is measured in BigQuery slots. The following table shows the number of slots you are allocated based on your monthly flat-rate purchase.

US (multi-region) EU (multi-region) Los Angeles (us-west2) Montréal (northamerica-northeast1) Northern Virginia (us-east4) São Paulo (southamerica-east1) Finland (europe-north1) London (europe-west2) Zürich (europe-west6) Hong Kong (asia-east2) Mumbai (asia-south1) Taiwan (asia-east1) Tokyo (asia-northeast1) Singapore (asia-southeast1) Sydney (australia-southeast1)
Monthly
Monthly cost Number of slots
500

If you pay in a currency other than USD, the prices listed in your currency on Cloud Platform SKUs apply.

Annual flat-rate pricing

When you enroll in flat-rate pricing, the capacity you purchase is measured in BigQuery slots. The following table shows the number of slots you are allocated based on your annual flat-rate purchase. When you enroll in an annual flat-rate plan, you are billed monthly.

US (multi-region) EU (multi-region) Los Angeles (us-west2) Montréal (northamerica-northeast1) Northern Virginia (us-east4) São Paulo (southamerica-east1) Finland (europe-north1) London (europe-west2) Zürich (europe-west6) Hong Kong (asia-east2) Mumbai (asia-south1) Taiwan (asia-east1) Tokyo (asia-northeast1) Singapore (asia-southeast1) Sydney (australia-southeast1)
Monthly
Monthly cost Number of slots
500

If you pay in a currency other than USD, the prices listed in your currency on Cloud Platform SKUs apply.

Purchasing slots via monthly and yearly commitments is currently in alpha. To participate in the alpha, complete this form.

There are no changes for customers who prefer to continue with their existing flat-rate plan. To continue your existing flat-rate plan, work with your sales representative.

Storage pricing

Once your data is loaded into BigQuery, you are charged for storing it. Storage pricing is based on the amount of data stored in your tables when it is uncompressed.

The size of the data is calculated based on the data types of the individual columns. For a detailed explanation of how data size is calculated, see Data size calculation.

Active storage

Active storage charges are as follows:

US (multi-region) EU (multi-region) Los Angeles (us-west2) Montréal (northamerica-northeast1) Northern Virginia (us-east4) São Paulo (southamerica-east1) Finland (europe-north1) London (europe-west2) Zürich (europe-west6) Hong Kong (asia-east2) Mumbai (asia-south1) Taiwan (asia-east1) Tokyo (asia-northeast1) Singapore (asia-southeast1) Sydney (australia-southeast1)
Monthly
Storage type Pricing Details
Active storage The first 10 GB is free each month.

If you pay in a currency other than USD, the prices listed in your currency on Cloud Platform SKUs apply.

Storage pricing is prorated per MB, per second. For example, if you store:

  • 100 MB for half a month, you pay $0.001 (a tenth of a cent)
  • 500 GB for half a month, you pay $5
  • 1 TB for a full month, you pay $20

Long-term storage

If a table is not edited for 90 consecutive days, the price of storage for that table automatically drops by approximately 50 percent. There is no degradation of performance, durability, availability, or any other functionality when a table is considered long term storage.

Long-term storage pricing is as follows:

US (multi-region) EU (multi-region) Los Angeles (us-west2) Montréal (northamerica-northeast1) Northern Virginia (us-east4) São Paulo (southamerica-east1) Finland (europe-north1) London (europe-west2) Zürich (europe-west6) Hong Kong (asia-east2) Mumbai (asia-south1) Taiwan (asia-east1) Tokyo (asia-northeast1) Singapore (asia-southeast1) Sydney (australia-southeast1)
Monthly
Storage type Pricing Details
Long-term storage The first 10 GB is free each month.

If the table is edited, the price reverts back to the regular storage pricing, and the 90-day timer starts counting from zero. Anything that modifies the data in a table resets the timer, including:

Action Details
Loading data into a table Any load or query job that appends data to a destination table or overwrites a destination table.
Copying data into a table Any copy job appends data to a destination table or overwrites a destination table.
Writing query results to a table Any query job that appends data to a destination table or overwrites a destination table.
Using the Data Manipulation Language (DML) Using a DML statement to modify table data.
Using Data Definition Language (DDL) Using a `CREATE OR REPLACE TABLE` DDL statement to replace a table.
Streaming data into the table Ingesting data using the tabledata.insertAll API call.

All other actions do not reset the timer, including:

  • Querying a table
  • Creating a view that queries a table
  • Exporting data from a table
  • Copying a table (to another destination table)
  • Patching or updating a table resource

Each partition of a partitioned table is considered separately for long-term storage pricing. If a partition hasn't been modified in the last 90 days, the data in that partition is considered long term storage and is charged at the discounted price.

For tables that reach the 90-day threshold during a billing cycle, the price is prorated accordingly.

Long term storage pricing applies only to BigQuery storage, not to data stored in external data sources such as Cloud Bigtable, Cloud Storage, and Google Drive.

BigQuery Storage API pricing

BigQuery Storage API pricing is based on the data allocated to a stream during ReadRows streaming operations. Costs are based on the incoming data size, not on the transmitted bytes.

The size of the data is calculated based on the data types of the individual columns. For a detailed explanation of how data size is calculated, see Data size calculation.

On-demand pricing

On-demand BigQuery Storage API charges are as follows:

US (multi-region) EU (multi-region) Los Angeles (us-west2) Montréal (northamerica-northeast1) Northern Virginia (us-east4) São Paulo (southamerica-east1) Finland (europe-north1) London (europe-west2) Zürich (europe-west6) Hong Kong (asia-east2) Mumbai (asia-south1) Taiwan (asia-east1) Tokyo (asia-northeast1) Singapore (asia-southeast1) Sydney (australia-southeast1)
Monthly
Pricing Details
The BigQuery Storage API is not included in the free tier.

If you pay in a currency other than USD, the prices listed in your currency on Cloud Platform SKUs apply.

Flat-rate pricing

Currently, customers with flat-rate pricing can use the BigQuery Storage API to read up to 300 TB of data per month at no charge. Reads in excess of 300 TB per month are billed at the on-demand rate.

Data size calculation

When you load data into BigQuery or query the data, you're charged according to the data size. Data size is calculated based on the size of each column's data type.

The size of your stored data, and the size of the data processed by your queries is calculated in gigabytes (GB), where 1GB is 230 bytes. This unit of measurement is also known as a gibibyte (GiB). Similarly, 1TB is 240 bytes (1024 GBs).

The size of BigQuery's data types is as follows:

Data type Size
INT64/INTEGER 8 bytes
FLOAT64/FLOAT 8 bytes
NUMERIC 16 bytes
BOOL/BOOLEAN 1 byte
STRING 2 bytes + the UTF-8 encoded string size
BYTES 2 bytes + the UTF-8 encoded string size
DATE 8 bytes
DATETIME 8 bytes
TIME 8 bytes
TIMESTAMP 8 bytes
STRUCT/RECORD 0 bytes + the size of the contained fields
GEOGRAPHY 16 bytes + 24 bytes * the number of vertices in the geography type (you can verify the number of vertices using the ST_NumPoints function)

Null values for any data type are calculated as 0 bytes.

A repeated column is stored as an array, and the size is calculated based on the number of values. For example, an integer column (INT64) that is repeated (ARRAY<INT64>) and contains 4 entries is calculated as 32 bytes (4 entries x 8 bytes).

Streaming pricing

Loading data into BigQuery is free, with the exception of a small charge for streamed data.

Pricing for streaming inserts is as follows:

US (multi-region) EU (multi-region) Los Angeles (us-west2) Montréal (northamerica-northeast1) Northern Virginia (us-east4) São Paulo (southamerica-east1) Finland (europe-north1) London (europe-west2) Zürich (europe-west6) Hong Kong (asia-east2) Mumbai (asia-south1) Taiwan (asia-east1) Tokyo (asia-northeast1) Singapore (asia-southeast1) Sydney (australia-southeast1)
Monthly
Operation Pricing Details
Streaming Inserts You are charged for rows that are successfully inserted. Individual rows are calculated using a 1 KB minimum size.

Data Manipulation Language pricing

BigQuery charges for DML queries based on the number of bytes processed by the query.

DML pricing for non-partitioned tables

For non-partitioned tables, the number of bytes processed is calculated as follows:

DML statement Bytes processed
INSERT The sum of bytes processed for all the columns referenced from the tables scanned by the query.
UPDATE The sum of bytes in all the columns referenced from the tables scanned by the query
+ the sum of bytes for all columns in the updated table at the time the UPDATE starts.
DELETE The sum of bytes in all the columns referenced from the tables scanned by the query
+ the sum of bytes for all columns in the modified table at the time the DELETE starts.
MERGE If there are only INSERT clauses in the MERGE statement, you are charged for the sum of bytes processed for all the columns referenced in all tables scanned by the query.
If there is an UPDATE or DELETE clause in the MERGE statement, you are charged for the sum of the bytes processed for all the columns referenced in the source tables scanned by the query
+ the sum of bytes for all columns in the target table (at the time the MERGE starts).

DML pricing for partitioned tables

For partitioned tables, the number of bytes processed is calculated as follows:

DML statement Bytes processed
INSERT The sum of bytes processed for all the columns referenced in all partitions scanned by the query.
UPDATE The sum of bytes processed for all the columns referenced in all partitions for the tables scanned by the query
+ the sum of bytes for all columns in the updated or scanned partitions for the table being updated (at the time the UPDATE starts).
DELETE The sum of bytes processed for all the columns referenced in all partitions for the tables scanned by the query
+ the sum of bytes for all columns in the modified or scanned partitions for the table being modified (at the time the DELETE starts).
MERGE If there are only INSERT clauses in the MERGE statement, you are charged for the sum of bytes processed for all the columns referenced in all partitions scanned by the query.
If there is an UPDATE or DELETE clause in the MERGE statement, you are charged for the sum of the bytes processed for all the columns referenced in all partitions for the source tables scanned by the query
+ the sum of bytes for all columns in the updated, deleted or scanned partitions for the target table (at the time the MERGE starts).

Data Definition Language pricing

BigQuery charges for DDL queries based on the number of bytes processed by the query. The number of bytes processed is calculated as follows for DDL statements:

DDL statement Bytes processed
CREATE TABLE None.
CREATE TABLE ... AS SELECT ... The sum of bytes processed for all the columns referenced from the tables scanned by the query.
CREATE VIEW None.
DROP TABLE None.
DROP VIEW None.

Clustered table pricing

When you create and use clustered tables in BigQuery, your charges are based on how much data is stored in the tables and on the queries you run against the data. Clustered tables help you to reduce query costs by pruning data so it is not processed by the query. This process is called block pruning.

Block pruning

BigQuery sorts the data in a clustered table based on the values in the clustering columns and organizes them into blocks.

When you submit a query that contains a filter on a clustered column, BigQuery uses the clustering information to efficiently determine whether a block contains any data relevant to the query. This allows BigQuery to only scan the relevant blocks — a process referred to as block pruning.

Query pricing is based on the number of bytes processed. When you run a query against a clustered table, and the query includes a filter on the clustered columns, BigQuery uses the filter expression and the block metadata to prune the blocks scanned by the query.

When a block is pruned it is not scanned. Only the scanned blocks are used to calculate the bytes of data processed by the query. The number of bytes processed by a query against a clustered table equals the sum of the bytes read in each column referenced by the query in the scanned blocks.

If a clustered table is referenced multiple times in a query that uses several filters, BigQuery charges for scanning the columns in the appropriate blocks in each of the respective filters.

BigQuery pricing examples

Estimating query costs

For query pricing examples, see Estimating query costs.

Estimating storage costs

For storage pricing examples, see Estimating storage costs.

DML pricing examples for non-partitioned tables

The following examples demonstrate how BigQuery calculates bytes read for DML statements that modify non-partitioned tables.

Example 1: Non-partitioned table UPDATE

table1 has two columns: col1 of type INTEGER and col2 of type STRING.

UPDATE table1 SET col1 = 1 WHERE col1 = 2;

Bytes processed in this example =

  • sum of the number of bytes in col1 +
  • sum of the number of bytes in col2

Example 2: Non-partitioned table UPDATE

table1 has two columns: col1 of type INTEGER and col2 of type STRING. table2 has one column: field1 of type INTEGER.

UPDATE table1 SET col1 = 1 WHERE col1 in (SELECT field1 from table2)

Bytes processed in this example =

  • sum of the number of bytes in table1.col1 before UPDATE +
  • sum of the number of bytes in table1.col2 before UPDATE +
  • sum of the number of bytes in table2.field1

DML pricing examples for partitioned tables

The following examples demonstrate how BigQuery calculates bytes read for DML statements that modify ingestion-time and partitioned tables. To view the JSON schema representations for the tables used in the examples, see Tables used in examples on the Updating Partitioned Table Data Using DML Statements page.

Example 1: Ingestion-time partitioned table INSERT

mytable2 has two columns: id of type INTEGER and ts of type TIMESTAMP. mytable has two columns: field1 of type INTEGER and field2 of type STRING.

INSERT INTO mytable (_PARTITIONTIME, field1) AS SELECT TIMESTAMP(DATE(ts)), id from mytable2

Bytes processed in this example =

  • sum of the number of bytes in mytable2.ts +
  • sum of the number of bytes in mytable2.id

The size of table into which the rows are inserted — mytable — does not affect the cost of the query.

Example 2: Partitioned table INSERT

mytable2 has two columns: id of type INTEGER and ts of type TIMESTAMP. mycolumntable has four columns: field1 of type INTEGER, field2 of type STRING, field3 of type BOOLEAN, and ts of type TIMESTAMP.

INSERT INTO mycolumntable (ts, field1) AS SELECT ts, id from mytable2

Bytes processed in this example =

  • sum of the number of bytes in mytable2.ts +
  • sum of the number of bytes in mytable2.id

The size of table into which the rows are inserted — mycolumntable — does not affect the cost of the query.

Example 3: Ingestion-time partitioned table UPDATE

DML statement 1: Updating a single partition

mytable2 has two columns: id of type INTEGER and ts of type TIMESTAMP. mytable has two columns: field1 of type INTEGER and field2 of type STRING.

UPDATE project.mydataset.mytable T SET T.field1 = T.field1 + 100 WHERE T._PARTITIONTIME = TIMESTAMP(“2017-05-01”) AND EXISTS (SELECT S.id from project.mydataset.mytable2 S WHERE S.id = T.field1)

Bytes processed in this example =

  • sum of the number of bytes in mytable2.id +
  • sum of the number of bytes in mytable.field1 in the "2017-05-01" partition +
  • sum of the number of bytes in mytable.field2 in the "2017-05-01" partition

DML statement 2: Updating a partition based on another partition in the table

UPDATE project.mydataset.mytable T SET T._PARTITIONTIME = TIMESTAMP(“2017-06-01”), T.field1 = T.field1 + 100 WHERE T._PARTITIONTIME = TIMESTAMP(“2017-05-01”) AND EXISTS (SELECT 1 from project.mydataset.mytable S WHERE S.field1 = T.field1 AND S._PARTITIONTIME = TIMESTAMP("2017-06-01") )

Bytes processed in this example =

  • sum of the number of bytes in mytable.field1 in the "2017-05-01" partition +
  • sum of the number of bytes in mytable.field2 in the "2017-05-01" partition +
  • sum of the number of bytes in mytable.field1 in the "2017-06-01" partition +
  • sum of the number of bytes in mytable.field2 in the "2017-06-01" partition

In this case, the cost of the UPDATE statement is the sum of sizes of all fields in the partitions corresponding to "2017-05-01" and "2017-06-01".

Example 4: Partitioned table UPDATE

DML statement 1: Updating a single partition

mytable2 has two columns: id of type INTEGER and ts of type TIMESTAMP. mycolumntable has four columns: field1 of type INTEGER, field2 of type STRING, field3 of type BOOLEAN, and ts of type TIMESTAMP.

UPDATE project.mydataset.mycolumntable T SET T.field1 = T.field1 + 100 WHERE DATE(T.ts) = “2017-05-01” AND EXISTS (SELECT S.id from project.mydataset.mytable2 S WHERE S.id = T.field1)

Bytes processed in this example =

  • sum of the number of bytes in mytable2.id +
  • sum of the number of bytes in mycolumntable.field1 in the "2017-05-01" partition +
  • sum of the number of bytes in mycolumntable.field2 in the "2017-05-01" partition +
  • sum of the number of bytes in mycolumntable.field3 in the "2017-05-01" partition +
  • sum of the number of bytes in mycolumntable.ts in the "2017-05-01" partition

DML statement 2: Updating a partition based on another partition in the table

UPDATE project.mydataset.mycolumntable T SET T.ts = TIMESTAMP(“2017-06-01”), T.field1 = T.field1 + 100 WHERE DATE(T.ts) = “2017-05-01” AND EXISTS (SELECT 1 from project.mydataset.mycolumntable S WHERE S.field1 = T.field1 AND DATE(S.ts) = "2017-06-01")

Bytes processed in this example =

  • sum of the number of bytes in mycolumntable.field1 in the "2017-05-01" partition +
  • sum of the number of bytes in mycolumntable.field2 in the "2017-05-01" partition +
  • sum of the number of bytes in mycolumntable.field3 in the "2017-05-01" partition +
  • sum of the number of bytes in mycolumntable.ts in the "2017-05-01" partition +
  • sum of the number of bytes in mycolumntable.field1 in the "2017-06-01" partition +
  • sum of the number of bytes in mycolumntable.field2 in the "2017-06-01" partition +
  • sum of the number of bytes in mycolumntable.field3 in the "2017-06-01" partition +
  • sum of the number of bytes in mycolumntable.ts in the "2017-06-01" partition

In this case, the cost of the UPDATE statement is the sum of sizes of all fields in the partitions corresponding to "2017-05-01" and "2017-06-01".

Example 5: Ingestion-time partitioned table DELETE

mytable2 has two columns: id of type INTEGER and ts of type TIMESTAMP. mytable has two columns: field1 of type INTEGER and field2 of type STRING.

DELETE project.mydataset.mytable T WHERE T._PARTITIONTIME = TIMESTAMP(“2017-05-01”) AND EXISTS (SELECT S.id from project.mydataset.mytable2 S WHERE S.id = T.field1)

Bytes processed in this example =

  • sum of the number of bytes in mytable2.id +
  • sum of the number of bytes in mytable.field1 in the "2017-05-01" partition +
  • sum of the number of bytes in mytable.field2 in the "2017-05-01" partition

Example 6: Partitioned table DELETE

mytable2 has two columns: id of type INTEGER and ts of type TIMESTAMP. mycolumntable has four columns: field1 of type INTEGER, field2 of type STRING, field3 of type BOOLEAN, and ts of type TIMESTAMP.

DELETE project.mydataset.mycolumntable T WHERE DATE(T.ts) =“2017-05-01” AND EXISTS (SELECT S.id from project.mydataset.mytable2 S WHERE S.id = T.field1)

Bytes processed in this example =

  • sum of the number of bytes in mytable2.id +
  • sum of the number of bytes in mycolumntable.field1 in the "2017-05-01" partition +
  • sum of the number of bytes in mycolumntable.field2 in the "2017-05-01" partition +
  • sum of the number of bytes in mycolumntable.field3 in the "2017-05-01" partition +
  • sum of the number of bytes in mycolumntable.ts in the "2017-05-01" partition

Clustered table pricing example

You have a clustered table named ClusteredSalesData. The table is partitioned by the timestamp column, and it is clustered by the customer_id column. The data is organized into the following set of blocks:

Partition identifier Block ID Minimum value for customer_id in the block Maximum value for customer_id in the block
20160501 B1 10000 19999
20160501 B2 20000 24999
20160502 B3 15000 17999
20160501 B4 22000 27999

You run the following query against the table. The query contains a filter on the customer_id column.

SELECT
  SUM(totalSale)
FROM
  `mydataset.ClusteredSalesData`
WHERE
  customer_id BETWEEN 20000
  AND 23000
  AND DATE(timestamp) = "2016-05-01"

This query:

  • Scans the timestamp, customer_id, and totalSale columns in blocks B2 and B4.
  • Prunes the B3 block because of the DATE(timestamp) = "2016-05-01" filter predicate on the timestamp partitioning column.
  • Prunes the B1 block because of the customer_id BETWEEN 20000 AND 23000 filter predicate on the customer_id clustering column.
Kunde den här sidan hjälpa dig? Berätta:

Skicka feedback om ...

Behöver du hjälp? Besök vår supportsida.