This page describes how to perform reads in Spanner outside the context of read-only and read-write transactions. If either of the following applies, you should read the Transactions page instead:
-
If you need to write, depending on the value of one or more reads, you should execute the read as part of a read-write transaction. For more information, see read-write transactions .
-
If you are making multiple read calls that require a consistent view of your data, you should execute the reads as part of a read-only transaction. For more information, see read-only transactions .
Read types
Spanner lets you determine how current the data should be when you read data by offering two types of reads:
- A strong read is a read at a current timestamp and is guaranteed to see all data that has been committed up until the start of this read. Spanner defaults to using strong reads to serve read requests.
- A stale read is read at a timestamp in the past. If your application is latency sensitive but tolerant of stale data, then stale reads can provide performance benefits.
To choose which type of read you want, set a timestamp bound on the read request. Use the following best practices when choosing a timestamp bound:
-
Choose strong reads whenever possible. These are the default timestamp bound for Spanner reads, including read-only transactions. Strong reads are guaranteed to observe the effects of all transactions that committed before the start of the operation, independent of which replica receives the read. Because of this, strong reads make application code simpler and applications more trustworthy. Read more about Spanner's consistency properties in TrueTime and External Consistency .
-
If latency makes strong reads infeasible in some situations, then use stale reads(bounded-staleness or exact-staleness) to improve performance in places where you don't need reads to be as recent as possible. As described on the Replication page, 15 seconds is a reasonable staleness value to use for good performance.
Read data with a database role
If you are a fine-grained access control user, you must select a database role to execute SQL statements and queries, and to perform row operations on a database. Your role selection persists throughout your session until you change the role.
For instructions on how to perform a read with a database role, see Access a database with fine-grained access control .
Single read methods
Spanner supports single read methods (that is, a read outside the context of a transaction) on a database for:
- Executing the read as a SQL query statement or using Spanner's read API.
- Performing a strong read from a single row or multiple rows in a table.
- Performing a stale read from a single row or multiple rows in a table.
- Reading from a single row or multiple rows in a secondary index.
If you want to route single reads to a specific replica or region within a multi-region instance configuration or a custom regional configuration with optional read-only region(s), see Directed reads .
The following sections describe how to use read methods using Spanner client libraries.
Execute a query
The following shows how to execute a SQL query statement against a database.
GoogleSQL
C++
Use ExecuteQuery()
to execute a SQL query statement against a database.
C#
Use ExecuteReaderAsync()
to query the database.
Go
Use Client.Single().Query
to query the database.
Java
Use ReadContext.executeQuery
to query the database.
Node.js
Use Database.run
to query the database.
PHP
Use Database::execute
to query the database.
Python
Use Database.execute_sql
to query the database.
Ruby
Use Client#execute
to query the database.
Consult the SQL Query Syntax and Functions and Operators references when constructing a SQL statement.
Perform a strong read
The following shows how to perform a strong read of zero or more rows from a database.
GoogleSQL
C++
The code to read data is the same as the previous sample for querying Spanner by executing a SQL query .
C#
The code to read data is the same as the previous sample for querying Spanner by executing a SQL query .
Go
Use Client.Single().Read
to read rows from the database.
The example uses AllKeys
to define a collection of keys or key ranges to
read.
Java
Use ReadContext.read
to read rows from the database.
The example uses KeySet
to define a collection of keys or key ranges to
read.
Node.js
Use Table.read
to read rows from the database.
The example uses keySet
to define a collection of keys or key ranges to
read.
PHP
Use Database::read
to read rows from the database.
The example uses keySet
to define a collection of keys or key ranges to
read.
Python
Use Database.read
to read rows from the database.
The example uses KeySet
to define a collection of keys or key ranges to
read.
Ruby
Use Client#read
to read rows from the database.
Perform a stale read
The following sample code shows how to perform a stale read of zero or more rows from a database using an exact-stalenesstimestamp bound. For instructions on how to perform a stale read using a bounded-stalenesstimestamp bound, see the note after the sample code. See Timestamp bounds for more information on the different types of timestamp bounds that are available.
GoogleSQL
C++
Use ExecuteQuery()
with MakeReadOnlyTransaction()
and Transaction::ReadOnlyOptions()
to perform a stale read.
C#
Use the BeginReadOnlyTransactionAsync
method on a connection
with a
specified TimestampBound.OfExactStaleness()
value to query the database.
Go
Use Client.ReadOnlyTransaction().WithTimestampBound()
and specify an ExactStaleness
value to perform a read of rows from the database using an
exact-staleness timestamp bound.
The example uses AllKeys
to define a collection of keys or key ranges to
read.
Java
Use the read
method of a ReadContext
that has a specified TimestampBound.ofExactStaleness()
to perform a read of rows from the
database using an exact-staleness timestamp bound.
The example uses KeySet
to define a collection of keys or key ranges to
read.
Node.js
Use Table.read
with the exactStaleness
option to perform a read of rows
from the database using an exact-staleness timestamp bound.
The example uses keySet
to define a collection of keys or key ranges to
read.
PHP
Use Database::read
with a exactStaleness
value specified to perform a
read of rows from the database using an exact-staleness timestamp bound.
The example uses keySet
to define a collection of keys or key ranges to
read.
Python
Use the read
method of a Database
snapshot
that has a specified exact_staleness
value to perform a read of rows from the database using an
exact-staleness timestamp bound.
The example uses KeySet
to define a collection of keys or key ranges to
read.
Ruby
Use the read
method of a snapshot Client
that has a specified staleness
value (in seconds) to perform a read of rows from the database
using an exact-staleness timestamp bound.
Perform a read using an index
The following shows how to read zero or more rows from a database using an index :
GoogleSQL
C++
Use the Read()
function to perform a read using an index.
C#
Read data using the index by executing a query that explicitly specifies the index:
Go
Use Client.Single().ReadUsingIndex
to read rows from the database using an
index.
Java
Use ReadContext.readUsingIndex
to read rows from the database using an
index.
Node.js
Use Table.read
and specify the index in the query to read rows from the
database using an index.
PHP
Use Database::read
and specify the index to read rows from the database
using an index.
Python
Use Database.read
and specify the index to read rows from the database
using an index.
Ruby
Use Client#read
and specify the index to read rows from the database using
an index.
Read data in parallel
When performing bulk read or query operations involving very large amounts of
data from Spanner, you can use the PartitionQuery
API for faster results. The API divides the query into batches, or partitions
, by using multiple machines to fetch the partitions in parallel. Be
mindful that using the PartitionQuery
API cause higher latency because it
is only intended for bulk operations such as exporting or scanning the whole
database.
You can perform any read API operation in parallel using the Spanner client libraries. However, you can only partition SQL queries when queries are root-partitionable. For a query to be root-partitionable, the query plan must satisfy one of the following conditions:
-
The first operator in the query execution plan is a distributed union and the query execution plan only contains one distributed union (excluding Local Distribution Unions). Your query plan can't contain any other distributed operators, such as distributed cross apply .
-
There are no distributed operators in the query plan.
The PartitionQuery
API runs the queries in batch mode. Spanner
might choose a query execution plan that makes the queries root-partitionable
when run in batch mode. As a result, the PartitionQuery
API and
Spanner Studio might use different query execution plans
for the same query. You might not be able to get the query execution plan
used by the PartitionQuery
API on Spanner Studio.
For partitioned queries like this, you can choose to enable Spanner Data Boost. Data Boost lets you run large analytic queries with near-zero impact to existing workloads on the provisioned Spanner instance. The C++, Go, Java, Node.js, and Python code examples on this page show how to enable Data Boost.
For more information about Data Boost, see Data Boost overview .
GoogleSQL
C++
This example fetches partitions of a SQL query of the Singers
table and
executes the query over each partition through the following steps:
- Creating a Spanner batch transaction.
- Generating partitions for the query, so that the partitions can be distributed to multiple workers.
- Retrieving the query results for each partition.
C#
This example fetches partitions of a SQL query of the Singers
table and
executes the query over each partition through the following steps:
- Creating a Spanner batch transaction.
- Generating partitions for the query, so that the partitions can be distributed to multiple workers.
- Retrieving the query results for each partition.
Go
This example fetches partitions of a SQL query of the Singers
table and
executes the query over each partition through the following steps:
- Creating a Spanner client and a transaction.
- Generating partitions for the query, so that the partitions can be distributed to multiple workers.
- Retrieving the query results for each partition.
Java
This example fetches partitions of a SQL query of the Singers
table and
executes the query over each partition through the following steps:
- Creating a Spanner batch client and a transaction.
- Generating partitions for the query, so that the partitions can be distributed to multiple workers.
- Retrieving the query results for each partition.
Node.js
This example fetches partitions of a SQL query of the Singers
table and
executes the query over each partition through the following steps:
- Creating a Spanner client and a batch.
- Generating partitions for the query, so that the partitions can be distributed to multiple workers.
- Retrieving the query results for each partition.
PHP
This example fetches partitions of a SQL query of the Singers
table and
executes the query over each partition through the following steps:
- Creating a Spanner client and a batch.
- Generating partitions for the query, so that the partitions can be distributed to multiple workers.
- Retrieving the query results for each partition.
Python
This example fetches partitions of a SQL query of the Singers
table and
executes the query over each partition through the following steps:
- Creating a Spanner client and a batch transaction.
- Generating partitions for the query, so that the partitions can be distributed to multiple workers.
- Retrieving the query results for each partition.
Ruby
This example fetches partitions of a SQL query of the Singers
table and
executes the query over each partition through the following steps:
- Creating a Spanner batch client.
- Creating partitions for the query, so that the partitions can be distributed to multiple workers.
- Retrieving the query results for each partition.
