BatchSnapshot
Represents a read-only transaction that can be configured to read at timestamps in the past and allows for exporting arbitrarily large amounts of data from Cloud Spanner databases. This is a snapshot which additionally allows to partition a read or query request. The read/query request can then be executed independently over each partition while observing the same snapshot of the database. A BatchSnapshot can also be shared across multiple processes/machines by passing around its serialized value and then recreating the transaction using #dump.
Unlike locking read-write transactions, BatchSnapshot will never abort. They can fail if the chosen read timestamp is garbage collected; however any read or query activity within an hour on the transaction avoids garbage collection and most applications do not need to worry about this in practice.
See Google::Cloud::Spanner::BatchClient#batch_snapshot and Google::Cloud::Spanner::BatchClient#load_batch_snapshot.
Inherits
- Object
Example
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new batch_client = spanner.batch_client "my-instance", "my-database" batch_snapshot = batch_client.batch_snapshot partitions = batch_snapshot.partition_read "users", [:id, :name] partition = partitions.first results = batch_snapshot.execute_partition partition batch_snapshot.close
Methods
#close
def close()
Closes the batch snapshot and releases the underlying resources.
This should only be called once the batch snapshot is no longer needed anywhere. In particular if this batch snapshot is being used across multiple machines, calling this method on any of the machines will render the batch snapshot invalid everywhere.
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new batch_client = spanner.batch_client "my-instance", "my-database" batch_snapshot = batch_client.batch_snapshot partitions = batch_snapshot.partition_read "users", [:id, :name] partition = partitions.first results = batch_snapshot.execute_partition partition batch_snapshot.close
#dump
def dump() -> String
Serializes the batch snapshot object so it can be recreated on another process. See Google::Cloud::Spanner::BatchClient#load_batch_snapshot.
- (String) — The serialized representation of the batch snapshot.
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new batch_client = spanner.batch_client "my-instance", "my-database" batch_snapshot = batch_client.batch_snapshot partitions = batch_snapshot.partition_read "users", [:id, :name] partition = partitions.first serialized_snapshot = batch_snapshot.dump serialized_partition = partition.dump # In a separate process new_batch_snapshot = batch_client.load_batch_snapshot \ serialized_snapshot new_partition = batch_client.load_partition \ serialized_partition results = new_batch_snapshot.execute_partition \ new_partition
#execute
def execute(sql, params: nil, types: nil, query_options: nil, call_options: nil) -> Google::Cloud::Spanner::Results
Executes a SQL query.
-
sql (String) — The SQL query string. See Query
syntax.
The SQL query string can contain parameter placeholders. A parameter placeholder consists of "@" followed by the parameter name. Parameter names consist of any combination of letters, numbers, and underscores.
-
params (Hash) (defaults to: nil) — SQL parameters for the query string. The
parameter placeholders, minus the "@", are the the hash keys, and
the literal values are the hash values. If the query string contains
something like "WHERE id > @msg_id", then the params must contain
something like
:msg_id => 1
.Ruby types are mapped to Spanner types as follows:
| Spanner | Ruby | Notes | |-------------|----------------|---| |
BOOL
|true
/false
| | |INT64
|Integer
| | |FLOAT64
|Float
| | |STRING
|String
| | |DATE
|Date
| | |TIMESTAMP
|Time
,DateTime
| | |BYTES
|File
,IO
,StringIO
, or similar | | |ARRAY
|Array
| Nested arrays are not supported. | |STRUCT
|Hash
, Data | |See Data types.
-
types (Hash) (defaults to: nil) — Types of the SQL parameters in
params
. It is not always possible for Cloud Spanner to infer the right SQL type from a value inparams
. In these cases, thetypes
hash must be used to specify the SQL type for these values.The keys of the hash should be query string parameter placeholders, minus the "@". The values of the hash should be Cloud Spanner type codes from the following list:
:BOOL
:BYTES
:DATE
:FLOAT64
:INT64
:STRING
:TIMESTAMP
Array
- Lists are specified by providing the type code in an array. For example, an array of integers are specified as[:INT64]
.- Fields - Types for STRUCT values (
Hash
/Data objects) are specified using a Fields object.
Types are optional.
-
query_options (Hash) (defaults to: nil) —
A hash of values to specify the custom query options for executing SQL query. Query options are optional. The following settings can be provided:
:optimizer_version
(String) The version of optimizer to use. Empty to use database default. "latest" to use the latest available optimizer version.:optimizer_statistics_package
(String) Statistics package to use. Empty to use the database default.
-
call_options (Hash) (defaults to: nil) —
A hash of values to specify the custom call options, e.g., timeout, retries, etc. Call options are optional. The following settings can be provided:
:timeout
(Numeric) A numeric value of custom timeout in seconds that overrides the default setting.:retry_policy
(Hash) A hash of values that overrides the default setting of retry policy with the following keys::initial_delay
(Numeric
) - The initial delay in seconds.:max_delay
(Numeric
) - The max delay in seconds.:multiplier
(Numeric
) - The incremental backoff multiplier.:retry_codes
(Array<String>
) - The error codes that should trigger a retry.
- (Google::Cloud::Spanner::Results) — The results of the query execution.
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new batch_client = spanner.batch_client "my-instance", "my-database" batch_snapshot = batch_client.batch_snapshot results = batch_snapshot.execute_query "SELECT * FROM users" results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end
Query using query parameters:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new batch_client = spanner.batch_client "my-instance", "my-database" batch_snapshot = batch_client.batch_snapshot results = batch_snapshot.execute_query( "SELECT * FROM users " \ "WHERE active = @active", params: { active: true } ) results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end
Query with a SQL STRUCT query parameter as a Hash:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new batch_client = spanner.batch_client "my-instance", "my-database" batch_snapshot = batch_client.batch_snapshot user_hash = { id: 1, name: "Charlie", active: false } results = batch_snapshot.execute_query( "SELECT * FROM users WHERE " \ "ID = @user_struct.id " \ "AND name = @user_struct.name " \ "AND active = @user_struct.active", params: { user_struct: user_hash } ) results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end
Specify the SQL STRUCT type using Fields object:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new batch_client = spanner.batch_client "my-instance", "my-database" batch_snapshot = batch_client.batch_snapshot user_type = batch_client.fields( { id: :INT64, name: :STRING, active: :BOOL } ) user_hash = { id: 1, name: nil, active: false } results = batch_snapshot.execute_query( "SELECT * FROM users WHERE " \ "ID = @user_struct.id " \ "AND name = @user_struct.name " \ "AND active = @user_struct.active", params: { user_struct: user_hash }, types: { user_struct: user_type } ) results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end
Or, query with a SQL STRUCT as a typed Data object:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new batch_client = spanner.batch_client "my-instance", "my-database" batch_snapshot = batch_client.batch_snapshot user_type = batch_client.fields( { id: :INT64, name: :STRING, active: :BOOL } ) user_data = user_type.struct id: 1, name: nil, active: false results = batch_snapshot.execute_query( "SELECT * FROM users WHERE " \ "ID = @user_struct.id " \ "AND name = @user_struct.name " \ "AND active = @user_struct.active", params: { user_struct: user_data } ) results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end
Query using query options:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new batch_client = spanner.batch_client "my-instance", "my-database" batch_snapshot = batch_client.batch_snapshot results = batch_snapshot.execute_query \ "SELECT * FROM users", query_options: { optimizer_version: "1", optimizer_statistics_package: "auto_20191128_14_47_22UTC" } results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end
Query using custom timeout and retry policy:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new batch_client = spanner.batch_client "my-instance", "my-database" batch_snapshot = batch_client.batch_snapshot timeout = 30.0 retry_policy = { initial_delay: 0.25, max_delay: 32.0, multiplier: 1.3, retry_codes: ["UNAVAILABLE"] } call_options = { timeout: timeout, retry_policy: retry_policy } results = batch_snapshot.execute_query \ "SELECT * FROM users", call_options: call_options results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end
#execute_partition
def execute_partition(partition, call_options: nil)
Execute the partition to return a Results. The result returned could be zero or more rows. The row metadata may be absent if no rows are returned.
- partition (Google::Cloud::Spanner::Partition) — The partition to be executed.
-
call_options (Hash) (defaults to: nil) —
A hash of values to specify the custom call options, e.g., timeout, retries, etc. Call options are optional. The following settings can be provided:
:timeout
(Numeric) A numeric value of custom timeout in seconds that overrides the default setting.:retry_policy
(Hash) A hash of values that overrides the default setting of retry policy with the following keys::initial_delay
(Numeric
) - The initial delay in seconds.:max_delay
(Numeric
) - The max delay in seconds.:multiplier
(Numeric
) - The incremental backoff multiplier.:retry_codes
(Array<String>
) - The error codes that should trigger a retry.
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new batch_client = spanner.batch_client "my-instance", "my-database" batch_snapshot = batch_client.batch_snapshot partitions = batch_snapshot.partition_read "users", [:id, :name] partition = partitions.first results = batch_snapshot.execute_partition partition batch_snapshot.close
#execute_query
def execute_query(sql, params: nil, types: nil, query_options: nil, call_options: nil) -> Google::Cloud::Spanner::Results
Executes a SQL query.
-
sql (String) — The SQL query string. See Query
syntax.
The SQL query string can contain parameter placeholders. A parameter placeholder consists of "@" followed by the parameter name. Parameter names consist of any combination of letters, numbers, and underscores.
-
params (Hash) (defaults to: nil) — SQL parameters for the query string. The
parameter placeholders, minus the "@", are the the hash keys, and
the literal values are the hash values. If the query string contains
something like "WHERE id > @msg_id", then the params must contain
something like
:msg_id => 1
.Ruby types are mapped to Spanner types as follows:
| Spanner | Ruby | Notes | |-------------|----------------|---| |
BOOL
|true
/false
| | |INT64
|Integer
| | |FLOAT64
|Float
| | |STRING
|String
| | |DATE
|Date
| | |TIMESTAMP
|Time
,DateTime
| | |BYTES
|File
,IO
,StringIO
, or similar | | |ARRAY
|Array
| Nested arrays are not supported. | |STRUCT
|Hash
, Data | |See Data types.
-
types (Hash) (defaults to: nil) — Types of the SQL parameters in
params
. It is not always possible for Cloud Spanner to infer the right SQL type from a value inparams
. In these cases, thetypes
hash must be used to specify the SQL type for these values.The keys of the hash should be query string parameter placeholders, minus the "@". The values of the hash should be Cloud Spanner type codes from the following list:
:BOOL
:BYTES
:DATE
:FLOAT64
:INT64
:STRING
:TIMESTAMP
Array
- Lists are specified by providing the type code in an array. For example, an array of integers are specified as[:INT64]
.- Fields - Types for STRUCT values (
Hash
/Data objects) are specified using a Fields object.
Types are optional.
-
query_options (Hash) (defaults to: nil) —
A hash of values to specify the custom query options for executing SQL query. Query options are optional. The following settings can be provided:
:optimizer_version
(String) The version of optimizer to use. Empty to use database default. "latest" to use the latest available optimizer version.:optimizer_statistics_package
(String) Statistics package to use. Empty to use the database default.
-
call_options (Hash) (defaults to: nil) —
A hash of values to specify the custom call options, e.g., timeout, retries, etc. Call options are optional. The following settings can be provided:
:timeout
(Numeric) A numeric value of custom timeout in seconds that overrides the default setting.:retry_policy
(Hash) A hash of values that overrides the default setting of retry policy with the following keys::initial_delay
(Numeric
) - The initial delay in seconds.:max_delay
(Numeric
) - The max delay in seconds.:multiplier
(Numeric
) - The incremental backoff multiplier.:retry_codes
(Array<String>
) - The error codes that should trigger a retry.
- (Google::Cloud::Spanner::Results) — The results of the query execution.
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new batch_client = spanner.batch_client "my-instance", "my-database" batch_snapshot = batch_client.batch_snapshot results = batch_snapshot.execute_query "SELECT * FROM users" results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end
Query using query parameters:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new batch_client = spanner.batch_client "my-instance", "my-database" batch_snapshot = batch_client.batch_snapshot results = batch_snapshot.execute_query( "SELECT * FROM users " \ "WHERE active = @active", params: { active: true } ) results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end
Query with a SQL STRUCT query parameter as a Hash:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new batch_client = spanner.batch_client "my-instance", "my-database" batch_snapshot = batch_client.batch_snapshot user_hash = { id: 1, name: "Charlie", active: false } results = batch_snapshot.execute_query( "SELECT * FROM users WHERE " \ "ID = @user_struct.id " \ "AND name = @user_struct.name " \ "AND active = @user_struct.active", params: { user_struct: user_hash } ) results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end
Specify the SQL STRUCT type using Fields object:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new batch_client = spanner.batch_client "my-instance", "my-database" batch_snapshot = batch_client.batch_snapshot user_type = batch_client.fields( { id: :INT64, name: :STRING, active: :BOOL } ) user_hash = { id: 1, name: nil, active: false } results = batch_snapshot.execute_query( "SELECT * FROM users WHERE " \ "ID = @user_struct.id " \ "AND name = @user_struct.name " \ "AND active = @user_struct.active", params: { user_struct: user_hash }, types: { user_struct: user_type } ) results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end
Or, query with a SQL STRUCT as a typed Data object:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new batch_client = spanner.batch_client "my-instance", "my-database" batch_snapshot = batch_client.batch_snapshot user_type = batch_client.fields( { id: :INT64, name: :STRING, active: :BOOL } ) user_data = user_type.struct id: 1, name: nil, active: false results = batch_snapshot.execute_query( "SELECT * FROM users WHERE " \ "ID = @user_struct.id " \ "AND name = @user_struct.name " \ "AND active = @user_struct.active", params: { user_struct: user_data } ) results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end
Query using query options:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new batch_client = spanner.batch_client "my-instance", "my-database" batch_snapshot = batch_client.batch_snapshot results = batch_snapshot.execute_query \ "SELECT * FROM users", query_options: { optimizer_version: "1", optimizer_statistics_package: "auto_20191128_14_47_22UTC" } results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end
Query using custom timeout and retry policy:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new batch_client = spanner.batch_client "my-instance", "my-database" batch_snapshot = batch_client.batch_snapshot timeout = 30.0 retry_policy = { initial_delay: 0.25, max_delay: 32.0, multiplier: 1.3, retry_codes: ["UNAVAILABLE"] } call_options = { timeout: timeout, retry_policy: retry_policy } results = batch_snapshot.execute_query \ "SELECT * FROM users", call_options: call_options results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end
#execute_sql
def execute_sql(sql, params: nil, types: nil, query_options: nil, call_options: nil) -> Google::Cloud::Spanner::Results
Executes a SQL query.
-
sql (String) — The SQL query string. See Query
syntax.
The SQL query string can contain parameter placeholders. A parameter placeholder consists of "@" followed by the parameter name. Parameter names consist of any combination of letters, numbers, and underscores.
-
params (Hash) (defaults to: nil) — SQL parameters for the query string. The
parameter placeholders, minus the "@", are the the hash keys, and
the literal values are the hash values. If the query string contains
something like "WHERE id > @msg_id", then the params must contain
something like
:msg_id => 1
.Ruby types are mapped to Spanner types as follows:
| Spanner | Ruby | Notes | |-------------|----------------|---| |
BOOL
|true
/false
| | |INT64
|Integer
| | |FLOAT64
|Float
| | |STRING
|String
| | |DATE
|Date
| | |TIMESTAMP
|Time
,DateTime
| | |BYTES
|File
,IO
,StringIO
, or similar | | |ARRAY
|Array
| Nested arrays are not supported. | |STRUCT
|Hash
, Data | |See Data types.
-
types (Hash) (defaults to: nil) — Types of the SQL parameters in
params
. It is not always possible for Cloud Spanner to infer the right SQL type from a value inparams
. In these cases, thetypes
hash must be used to specify the SQL type for these values.The keys of the hash should be query string parameter placeholders, minus the "@". The values of the hash should be Cloud Spanner type codes from the following list:
:BOOL
:BYTES
:DATE
:FLOAT64
:INT64
:STRING
:TIMESTAMP
Array
- Lists are specified by providing the type code in an array. For example, an array of integers are specified as[:INT64]
.- Fields - Types for STRUCT values (
Hash
/Data objects) are specified using a Fields object.
Types are optional.
-
query_options (Hash) (defaults to: nil) —
A hash of values to specify the custom query options for executing SQL query. Query options are optional. The following settings can be provided:
:optimizer_version
(String) The version of optimizer to use. Empty to use database default. "latest" to use the latest available optimizer version.:optimizer_statistics_package
(String) Statistics package to use. Empty to use the database default.
-
call_options (Hash) (defaults to: nil) —
A hash of values to specify the custom call options, e.g., timeout, retries, etc. Call options are optional. The following settings can be provided:
:timeout
(Numeric) A numeric value of custom timeout in seconds that overrides the default setting.:retry_policy
(Hash) A hash of values that overrides the default setting of retry policy with the following keys::initial_delay
(Numeric
) - The initial delay in seconds.:max_delay
(Numeric
) - The max delay in seconds.:multiplier
(Numeric
) - The incremental backoff multiplier.:retry_codes
(Array<String>
) - The error codes that should trigger a retry.
- (Google::Cloud::Spanner::Results) — The results of the query execution.
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new batch_client = spanner.batch_client "my-instance", "my-database" batch_snapshot = batch_client.batch_snapshot results = batch_snapshot.execute_query "SELECT * FROM users" results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end
Query using query parameters:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new batch_client = spanner.batch_client "my-instance", "my-database" batch_snapshot = batch_client.batch_snapshot results = batch_snapshot.execute_query( "SELECT * FROM users " \ "WHERE active = @active", params: { active: true } ) results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end
Query with a SQL STRUCT query parameter as a Hash:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new batch_client = spanner.batch_client "my-instance", "my-database" batch_snapshot = batch_client.batch_snapshot user_hash = { id: 1, name: "Charlie", active: false } results = batch_snapshot.execute_query( "SELECT * FROM users WHERE " \ "ID = @user_struct.id " \ "AND name = @user_struct.name " \ "AND active = @user_struct.active", params: { user_struct: user_hash } ) results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end
Specify the SQL STRUCT type using Fields object:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new batch_client = spanner.batch_client "my-instance", "my-database" batch_snapshot = batch_client.batch_snapshot user_type = batch_client.fields( { id: :INT64, name: :STRING, active: :BOOL } ) user_hash = { id: 1, name: nil, active: false } results = batch_snapshot.execute_query( "SELECT * FROM users WHERE " \ "ID = @user_struct.id " \ "AND name = @user_struct.name " \ "AND active = @user_struct.active", params: { user_struct: user_hash }, types: { user_struct: user_type } ) results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end
Or, query with a SQL STRUCT as a typed Data object:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new batch_client = spanner.batch_client "my-instance", "my-database" batch_snapshot = batch_client.batch_snapshot user_type = batch_client.fields( { id: :INT64, name: :STRING, active: :BOOL } ) user_data = user_type.struct id: 1, name: nil, active: false results = batch_snapshot.execute_query( "SELECT * FROM users WHERE " \ "ID = @user_struct.id " \ "AND name = @user_struct.name " \ "AND active = @user_struct.active", params: { user_struct: user_data } ) results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end
Query using query options:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new batch_client = spanner.batch_client "my-instance", "my-database" batch_snapshot = batch_client.batch_snapshot results = batch_snapshot.execute_query \ "SELECT * FROM users", query_options: { optimizer_version: "1", optimizer_statistics_package: "auto_20191128_14_47_22UTC" } results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end
Query using custom timeout and retry policy:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new batch_client = spanner.batch_client "my-instance", "my-database" batch_snapshot = batch_client.batch_snapshot timeout = 30.0 retry_policy = { initial_delay: 0.25, max_delay: 32.0, multiplier: 1.3, retry_codes: ["UNAVAILABLE"] } call_options = { timeout: timeout, retry_policy: retry_policy } results = batch_snapshot.execute_query \ "SELECT * FROM users", call_options: call_options results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end
#partition_query
def partition_query(sql, params: nil, types: nil, partition_size_bytes: nil, max_partitions: nil, query_options: nil, call_options: nil) -> Array<Google::Cloud::Spanner::Partition>
Returns a list of Partition objects to execute a batch query against a database.
These partitions can be executed across multiple processes, even across different machines. The partition size and count can be configured, although the values given may not necessarily be honored depending on the query and options in the request.
The query must have a single distributed union operator at the root of the query plan. Such queries are root-partitionable. If a query cannot be partitioned at the root, Cloud Spanner cannot achieve the parallelism and in this case partition generation will fail.
-
sql (String) — The SQL query string. See Query
syntax.
The SQL query string can contain parameter placeholders. A parameter placeholder consists of "@" followed by the parameter name. Parameter names consist of any combination of letters, numbers, and underscores.
-
params (Hash) (defaults to: nil) — SQL parameters for the query string. The
parameter placeholders, minus the "@", are the the hash keys, and
the literal values are the hash values. If the query string contains
something like "WHERE id > @msg_id", then the params must contain
something like
:msg_id => 1
.Ruby types are mapped to Spanner types as follows:
| Spanner | Ruby | Notes | |-------------|----------------|---| |
BOOL
|true
/false
| | |INT64
|Integer
| | |FLOAT64
|Float
| | |STRING
|String
| | |DATE
|Date
| | |TIMESTAMP
|Time
,DateTime
| | |BYTES
|File
,IO
,StringIO
, or similar | | |ARRAY
|Array
| Nested arrays are not supported. | |STRUCT
|Hash
, Data | |See Data types.
-
types (Hash) (defaults to: nil) — Types of the SQL parameters in
params
. It is not always possible for Cloud Spanner to infer the right SQL type from a value inparams
. In these cases, thetypes
hash must be used to specify the SQL type for these values.The keys of the hash should be query string parameter placeholders, minus the "@". The values of the hash should be Cloud Spanner type codes from the following list:
:BOOL
:BYTES
:DATE
:FLOAT64
:INT64
:STRING
:TIMESTAMP
Array
- Lists are specified by providing the type code in an array. For example, an array of integers are specified as[:INT64]
.- Fields - Types for STRUCT values (
Hash
/Data objects) are specified using a Fields object.
Types are optional.
- partition_size_bytes (Integer) (defaults to: nil) — The desired data size for each partition generated. This is only a hint. The actual size of each partition may be smaller or larger than this size request.
- max_partitions (Integer) (defaults to: nil) — The desired maximum number of partitions to return. For example, this may be set to the number of workers available. This is only a hint and may provide different results based on the request.
-
query_options (Hash) (defaults to: nil) —
A hash of values to specify the custom query options for executing SQL query. Query options are optional. The following settings can be provided:
:optimizer_version
(String) The version of optimizer to use. Empty to use database default. "latest" to use the latest available optimizer version.:optimizer_statistics_package
(String) Statistics package to use. Empty to use the database default.
-
call_options (Hash) (defaults to: nil) —
A hash of values to specify the custom call options, e.g., timeout, retries, etc. Call options are optional. The following settings can be provided:
:timeout
(Numeric) A numeric value of custom timeout in seconds that overrides the default setting.:retry_policy
(Hash) A hash of values that overrides the default setting of retry policy with the following keys::initial_delay
(Numeric
) - The initial delay in seconds.:max_delay
(Numeric
) - The max delay in seconds.:multiplier
(Numeric
) - The incremental backoff multiplier.:retry_codes
(Array<String>
) - The error codes that should trigger a retry.
- (Array<Google::Cloud::Spanner::Partition>) — The partitions created by the query partition.
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new batch_client = spanner.batch_client "my-instance", "my-database" batch_snapshot = batch_client.batch_snapshot sql = "SELECT u.id, u.active FROM users AS u \ WHERE u.id < 2000="" and="" u.active="false" "="" partitions="batch_snapshot.partition_query" sql="" partition="partitions.first" results="batch_snapshot.execute_partition" partition="">
#partition_read
def partition_read(table, columns, keys: nil, index: nil, partition_size_bytes: nil, max_partitions: nil, call_options: nil) -> Array<Google::Cloud::Spanner::Partition>
Returns a list of Partition objects to read zero or more rows from a database.
These partitions can be executed across multiple processes, even across different machines. The partition size and count can be configured, although the values given may not necessarily be honored depending on the query and options in the request.
- table (String) — The name of the table in the database to be read.
- columns (Array<String, Symbol>) — The columns of table to be returned for each row matching this request.
- keys (Object, Array<Object>) (defaults to: nil) — A single, or list of keys or key ranges to match returned data to. Values should have exactly as many elements as there are columns in the primary key.
-
index (String) (defaults to: nil) — The name of an index to use instead of the
table's primary key when interpreting
id
and sorting result rows. Optional. - partition_size_bytes (Integer) (defaults to: nil) — The desired data size for each partition generated. This is only a hint. The actual size of each partition may be smaller or larger than this size request.
- max_partitions (Integer) (defaults to: nil) — The desired maximum number of partitions to return. For example, this may be set to the number of workers available. This is only a hint and may provide different results based on the request.
-
call_options (Hash) (defaults to: nil) —
A hash of values to specify the custom call options, e.g., timeout, retries, etc. Call options are optional. The following settings can be provided:
:timeout
(Numeric) A numeric value of custom timeout in seconds that overrides the default setting.:retry_policy
(Hash) A hash of values that overrides the default setting of retry policy with the following keys::initial_delay
(Numeric
) - The initial delay in seconds.:max_delay
(Numeric
) - The max delay in seconds.:multiplier
(Numeric
) - The incremental backoff multiplier.:retry_codes
(Array<String>
) - The error codes that should trigger a retry.
- (Array<Google::Cloud::Spanner::Partition>) — The partitions created by the read partition.
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new batch_client = spanner.batch_client "my-instance", "my-database" batch_snapshot = batch_client.batch_snapshot partitions = batch_snapshot.partition_read "users", [:id, :name] partition = partitions.first results = batch_snapshot.execute_partition partition batch_snapshot.close
#query
def query(sql, params: nil, types: nil, query_options: nil, call_options: nil) -> Google::Cloud::Spanner::Results
Executes a SQL query.
-
sql (String) — The SQL query string. See Query
syntax.
The SQL query string can contain parameter placeholders. A parameter placeholder consists of "@" followed by the parameter name. Parameter names consist of any combination of letters, numbers, and underscores.
-
params (Hash) (defaults to: nil) — SQL parameters for the query string. The
parameter placeholders, minus the "@", are the the hash keys, and
the literal values are the hash values. If the query string contains
something like "WHERE id > @msg_id", then the params must contain
something like
:msg_id => 1
.Ruby types are mapped to Spanner types as follows:
| Spanner | Ruby | Notes | |-------------|----------------|---| |
BOOL
|true
/false
| | |INT64
|Integer
| | |FLOAT64
|Float
| | |STRING
|String
| | |DATE
|Date
| | |TIMESTAMP
|Time
,DateTime
| | |BYTES
|File
,IO
,StringIO
, or similar | | |ARRAY
|Array
| Nested arrays are not supported. | |STRUCT
|Hash
, Data | |See Data types.
-
types (Hash) (defaults to: nil) — Types of the SQL parameters in
params
. It is not always possible for Cloud Spanner to infer the right SQL type from a value inparams
. In these cases, thetypes
hash must be used to specify the SQL type for these values.The keys of the hash should be query string parameter placeholders, minus the "@". The values of the hash should be Cloud Spanner type codes from the following list:
:BOOL
:BYTES
:DATE
:FLOAT64
:INT64
:STRING
:TIMESTAMP
Array
- Lists are specified by providing the type code in an array. For example, an array of integers are specified as[:INT64]
.- Fields - Types for STRUCT values (
Hash
/Data objects) are specified using a Fields object.
Types are optional.
-
query_options (Hash) (defaults to: nil) —
A hash of values to specify the custom query options for executing SQL query. Query options are optional. The following settings can be provided:
:optimizer_version
(String) The version of optimizer to use. Empty to use database default. "latest" to use the latest available optimizer version.:optimizer_statistics_package
(String) Statistics package to use. Empty to use the database default.
-
call_options (Hash) (defaults to: nil) —
A hash of values to specify the custom call options, e.g., timeout, retries, etc. Call options are optional. The following settings can be provided:
:timeout
(Numeric) A numeric value of custom timeout in seconds that overrides the default setting.:retry_policy
(Hash) A hash of values that overrides the default setting of retry policy with the following keys::initial_delay
(Numeric
) - The initial delay in seconds.:max_delay
(Numeric
) - The max delay in seconds.:multiplier
(Numeric
) - The incremental backoff multiplier.:retry_codes
(Array<String>
) - The error codes that should trigger a retry.
- (Google::Cloud::Spanner::Results) — The results of the query execution.
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new batch_client = spanner.batch_client "my-instance", "my-database" batch_snapshot = batch_client.batch_snapshot results = batch_snapshot.execute_query "SELECT * FROM users" results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end
Query using query parameters:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new batch_client = spanner.batch_client "my-instance", "my-database" batch_snapshot = batch_client.batch_snapshot results = batch_snapshot.execute_query( "SELECT * FROM users " \ "WHERE active = @active", params: { active: true } ) results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end
Query with a SQL STRUCT query parameter as a Hash:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new batch_client = spanner.batch_client "my-instance", "my-database" batch_snapshot = batch_client.batch_snapshot user_hash = { id: 1, name: "Charlie", active: false } results = batch_snapshot.execute_query( "SELECT * FROM users WHERE " \ "ID = @user_struct.id " \ "AND name = @user_struct.name " \ "AND active = @user_struct.active", params: { user_struct: user_hash } ) results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end
Specify the SQL STRUCT type using Fields object:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new batch_client = spanner.batch_client "my-instance", "my-database" batch_snapshot = batch_client.batch_snapshot user_type = batch_client.fields( { id: :INT64, name: :STRING, active: :BOOL } ) user_hash = { id: 1, name: nil, active: false } results = batch_snapshot.execute_query( "SELECT * FROM users WHERE " \ "ID = @user_struct.id " \ "AND name = @user_struct.name " \ "AND active = @user_struct.active", params: { user_struct: user_hash }, types: { user_struct: user_type } ) results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end
Or, query with a SQL STRUCT as a typed Data object:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new batch_client = spanner.batch_client "my-instance", "my-database" batch_snapshot = batch_client.batch_snapshot user_type = batch_client.fields( { id: :INT64, name: :STRING, active: :BOOL } ) user_data = user_type.struct id: 1, name: nil, active: false results = batch_snapshot.execute_query( "SELECT * FROM users WHERE " \ "ID = @user_struct.id " \ "AND name = @user_struct.name " \ "AND active = @user_struct.active", params: { user_struct: user_data } ) results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end
Query using query options:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new batch_client = spanner.batch_client "my-instance", "my-database" batch_snapshot = batch_client.batch_snapshot results = batch_snapshot.execute_query \ "SELECT * FROM users", query_options: { optimizer_version: "1", optimizer_statistics_package: "auto_20191128_14_47_22UTC" } results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end
Query using custom timeout and retry policy:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new batch_client = spanner.batch_client "my-instance", "my-database" batch_snapshot = batch_client.batch_snapshot timeout = 30.0 retry_policy = { initial_delay: 0.25, max_delay: 32.0, multiplier: 1.3, retry_codes: ["UNAVAILABLE"] } call_options = { timeout: timeout, retry_policy: retry_policy } results = batch_snapshot.execute_query \ "SELECT * FROM users", call_options: call_options results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end
#read
def read(table, columns, keys: nil, index: nil, limit: nil, call_options: nil) -> Google::Cloud::Spanner::Results
Read rows from a database table, as a simple alternative to #execute_query.
- table (String) — The name of the table in the database to be read.
- columns (Array<String, Symbol>) — The columns of table to be returned for each row matching this request.
- keys (Object, Array<Object>) (defaults to: nil) — A single, or list of keys or key ranges to match returned data to. Values should have exactly as many elements as there are columns in the primary key.
-
index (String) (defaults to: nil) — The name of an index to use instead of the
table's primary key when interpreting
id
and sorting result rows. Optional. - limit (Integer) (defaults to: nil) — If greater than zero, no more than this number of rows will be returned. The default is no limit.
-
call_options (Hash) (defaults to: nil) —
A hash of values to specify the custom call options, e.g., timeout, retries, etc. Call options are optional. The following settings can be provided:
:timeout
(Numeric) A numeric value of custom timeout in seconds that overrides the default setting.:retry_policy
(Hash) A hash of values that overrides the default setting of retry policy with the following keys::initial_delay
(Numeric
) - The initial delay in seconds.:max_delay
(Numeric
) - The max delay in seconds.:multiplier
(Numeric
) - The incremental backoff multiplier.:retry_codes
(Array<String>
) - The error codes that should trigger a retry.
- (Google::Cloud::Spanner::Results) — The results of the read operation.
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new batch_client = spanner.batch_client "my-instance", "my-database" batch_snapshot = batch_client.batch_snapshot results = batch_snapshot.read "users", [:id, :name] results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end
#serialize
def serialize() -> String
Serializes the batch snapshot object so it can be recreated on another process. See Google::Cloud::Spanner::BatchClient#load_batch_snapshot.
- (String) — The serialized representation of the batch snapshot.
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new batch_client = spanner.batch_client "my-instance", "my-database" batch_snapshot = batch_client.batch_snapshot partitions = batch_snapshot.partition_read "users", [:id, :name] partition = partitions.first serialized_snapshot = batch_snapshot.dump serialized_partition = partition.dump # In a separate process new_batch_snapshot = batch_client.load_batch_snapshot \ serialized_snapshot new_partition = batch_client.load_partition \ serialized_partition results = new_batch_snapshot.execute_partition \ new_partition
#timestamp
def timestamp() -> Time
The read timestamp chosen for batch snapshot.
- (Time) — The chosen timestamp.
#transaction_id
def transaction_id() -> String
Identifier of the batch snapshot transaction.
- (String) — The transaction id.