Use the BigQuery Storage Read API to read table data

The BigQuery Storage Read API provides fast access toBigQuery-managed storage by using anrpc-basedprotocol.

Background

Historically, users of BigQuery have had two mechanisms foraccessing BigQuery-managed table data:

• Record-based paginated access by using thetabledata.list orjobs.getQueryResults REST API methods. The BigQuery APIprovides structured row responses in a paginated fashion appropriate for smallresult sets.

• Bulk data export using BigQueryextract jobs that export tabledata to Cloud Storage in a variety of file formats such as CSV, JSON,and Avro. Table exports are limited by daily quotas and by the batchnature of the export process.

The BigQuery Storage Read API provides a third option that represents animprovement over prior options. When you use the Storage Read API,structured data is sent over the wire in a binary serialization format. Thisallows for additional parallelism among multiple consumers for a set of results.

The Storage Read API does not provide functionality related tomanaging BigQuery resources such as datasets, jobs, or tables.

Key features

• Multiple Streams: The Storage Read API allows consumers toread disjoint sets of rows from a table using multiple streams within asession. This facilitates consumption from distributed processing frameworksor from independent consumer threads within a single client.

• Column Projection: At session creation, users can select an optionalsubset of columns to read. This allows efficient reads when tables containmany columns.

• Column Filtering: Users may provide simple filter predicates to enablefiltration of data on the server side before transmission to a client.

• Snapshot Consistency: Storage sessions read based on a snapshotisolation model. All consumers read based on a specific point in time.The default snapshot time is based on the session creation time, but consumersmay read data from an earlier snapshot.

Enabling the API

The Storage Read API is distinct from the BigQuery API, andshows up separately in the Google Cloud console as theBigQuery Storage API.However, the Storage Read API is enabled in all projects in whichthe BigQuery API is enabled; no additional activation steps are required.

Permissions

To get the permissions that you need to create and update read sessions, ask your administrator to grant you the Read Session User (bigquery.readSessionUser) IAM role on the project. For more information about granting roles, seeManage access to projects, folders, and organizations.

This predefined role contains the permissions required to create and update read sessions. To see the exact permissions that are required, expand theRequired permissions section:

You might also be able to get these permissions withcustom roles or otherpredefined roles.

For more information about BigQuery roles and permissions, seeBigQuery IAM roles and permissions.

Basic API flow

This section describes the basic flow of using the Storage Read API. Forexamples, see thelibraries and samples page.

Create a session

Storage Read API usage begins with the creation of a read session. Themaximum number of streams, the snapshot time, the set of columns to return, andthe predicate filter are all specified as part of theReadSession messagesupplied to theCreateReadSession RPC.

TheReadSession response contains a set ofStream identifiers. When a readsession is created, the server determines the amount of data that can be read inthe context of the session and creates one or more streams, each of whichrepresents approximately the same amount of table data to be scanned. This meansthat, to read all the data from a table, callers must read from allStreamidentifiers returned in theReadSession response. This is a change fromearlier versions of the API, in which no limit existed on the amount of datathat could be read in a single stream context.

TheReadSession response contains a reference schema for the session and alist of availableStream identifiers. Sessions expire automatically and do notrequire any cleanup or finalization. The expiration time is returned as part oftheReadSession response and is guaranteed to be at least 6 hours from sessioncreation time.

Read from a session stream

Data from a given stream is retrieved by invoking theReadRows streaming RPC.Once the read request for aStream is initiated, the backend will begintransmitting blocks of serialized row data. RPC flow control ensures thatthe server does not transmit more data when the client is not ready to receive.If the client does not request data for more than 1 hour, then the serversuspects that the stream is stalled and closes it to free up resources for otherstreams. If there is an error, you can restart reading a stream at a particular point by supplying the row offset when you callReadRows.

To support dynamic work rebalancing, the Storage Read API provides anadditional method to split aStream into two childStream instances whosecontents are, together, equal to the contents of the parentStream. For moreinformation, see theAPI reference.

Decode row blocks

Row blocks must be deserialized once they are received. Users ofthe Storage Read API may specify all data in a session to be serializedusing either Apache Avro format, or Apache Arrow.

The reference schema is sent as part of the initialReadSession response,appropriate for the data format selected. In most cases, decoders can belong-lived because the schema and serialization are consistent among all streamsand row blocks in a session.

Schema conversion

Avro schema details

Due to type system differences between BigQuery and the Avrospecification, Avro schemas may include additional annotations that identify howto map the Avro types to BigQuery representations. When compatible,Avro base types and logical types are used. The Avro schema may also includeadditional annotations for types present in BigQuery that do nothave a well defined Avro representation.

To represent nullable columns, unions with the AvroNULL type are used.

{    "name": "range_date_1",    "type": {        "type": "record",        "namespace": "google.sqlType",        "name": "RANGE_DATE",        "sqlType": "RANGE",        "fields": [            {                 "name": "start",                 "type": ["null", {"type": "int", "logicalType": "date"}]            },            {                 "name": "end",                 "type": ["null", {"type": "int", "logicalType": "date"}]            },        ]    }},{    "name": "range_date_2",    "type": "google.sqlType.RANGE_DATE"}

Arrow schema details

The Apache Arrow format works well with Python data science workloads.

For cases where multiple BigQuery types converge on a singleArrow data type, the metadata property of the Arrow schema field indicatesthe original data type.

If you're working in an older version of the Storage Read API, thenuse the appropriate version of Arrow as follows:

• v1beta1: Arrow 0.14 and earlier
• v1: Arrow 0.15 and later

Regardless of API version, to access API functions, we recommend that you usetheBigQuery Storage API client libraries. The libraries canbe used with any version of Arrow and don't obstruct its updates.

Limitations

• Because the Storage Read API operates on storage,you cannot use the Storage Read API to directly read from logical ormaterialized views. As a workaround, you can execute a BigQueryquery over the view and use the Storage Read API to read from theresulting table. Some connectors, including theSpark-BigQuery connector,support this workflow natively.

• Readingexternal tables is not supported. Touse the Storage Read API with external data sources, useBigLake tables.

Supported regions

The Storage Read API is supported in the same regions asBigQuery. See theDataset locations page for acomplete list of supported regions and multi-regions.

Data locality

Data locality is the process of moving the computation closer to the locationwhere the data resides. Data locality impacts both the peak throughput andconsistency of performance.

BigQuerydetermines the location to run your load, query, or extract jobs based on thedatasets referenced in the request. For information about location considerations,seeBigQuery locations.

Troubleshoot errors

The following are common errors encountered when using theStorage Read API:

Error:Stream removed

Resolution: Retry the Storage Read API request. This is likelya transient error that can be resolved by retrying the request. If the problempersists,contact support.

Error:Stream expired

Cause: This error occurs when the Storage Read API sessionreaches the6 hour timeout.

Resolution:

1. Increase the parallelism of the job.
2. If the CPU utilization of the worker nodes is relatively consistent anddoesn't spike above 85%, consider running the job on a larger machine type.
3. Split the job into multiple jobs or smaller queries.

Quotas and limits

For Storage Read API quotas and limits, seeStorage Read API limits.

Monitor Storage Read API use

To monitor the data egress and processing associated with theStorage Read API, specific fields are available in theBigQuery AuditLogs.These logs provide a detailed view of the bytes scanned and the bytes returnedto the client.

The relevant API method for these logs isgoogle.cloud.bigquery.storage.v1.BigQueryRead.ReadRows.

Pricing

For information on Storage Read API pricing, see thePricing page.