Reference documentation and code samples for the Cloud Spanner API class Google::Cloud::Spanner::Snapshot.
Snapshot
A snapshot in Cloud Spanner is a set of reads that execute atomically at a single logical point in time across columns, rows, and tables in a database.
Inherits
- Object
Example
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.snapshot do |snp| results = snp.execute_query "SELECT * FROM users" results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end end
Methods
#execute
def execute(sql, params: nil, types: nil, query_options: nil, call_options: nil, directed_read_options: nil) -> Google::Cloud::Spanner::Results
Executes a SQL query.
The following settings can be provided:
:exclude_replicas
(Hash) Exclude_replicas indicates what replicas should be excluded from serving requests. Spanner will not route requests to the replicas in this list.:include_replicas
(Hash) Include_replicas indicates the order of replicas to process the request. If auto_failover_disabled is set to true and all replicas are exhausted without finding a healthy replica, Spanner will wait for a replica in the list to become available, requests may fail due to DEADLINE_EXCEEDED errors.
-
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
| | |FLOAT32
|Float
| | |NUMERIC
|BigDecimal
| | |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
:FLOAT32
:NUMERIC
: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.
-
directed_read_options (Hash) (defaults to: nil) — Client options used to set the directed_read_options
for all ReadRequests and ExecuteSqlRequests that indicates which replicas
or regions should be used for non-transactional reads or queries.
Will represent
Google::Cloud::Spanner::V1::DirectedReadOptions
- (Google::Cloud::Spanner::Results) — The results of the query execution.
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.snapshot do |snp| results = snp.execute_query "SELECT * FROM users" results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end end
Query using query parameters:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.snapshot do |snp| results = snp.execute_query "SELECT * FROM users " \ "WHERE active = @active", params: { active: true } results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end end
Query with a SQL STRUCT query parameter as a Hash:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.snapshot do |snp| user_hash = { id: 1, name: "Charlie", active: false } results = snp.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 end
Specify the SQL STRUCT type using Fields object:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.snapshot do |snp| user_type = snp.fields id: :INT64, name: :STRING, active: :BOOL user_hash = { id: 1, name: nil, active: false } results = snp.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 end
Or, query with a SQL STRUCT as a typed Data object:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.snapshot do |snp| user_type = snp.fields id: :INT64, name: :STRING, active: :BOOL user_data = user_type.struct id: 1, name: nil, active: false results = snp.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 end
Query using query options:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.snapshot do |snp| results = snp.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 end
Query using custom timeout and retry policy:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" 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 } db.snapshot do |snp| results = snp.execute_query \ "SELECT * FROM users", call_options: call_options results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end end
#execute_query
def execute_query(sql, params: nil, types: nil, query_options: nil, call_options: nil, directed_read_options: nil) -> Google::Cloud::Spanner::Results
Executes a SQL query.
The following settings can be provided:
:exclude_replicas
(Hash) Exclude_replicas indicates what replicas should be excluded from serving requests. Spanner will not route requests to the replicas in this list.:include_replicas
(Hash) Include_replicas indicates the order of replicas to process the request. If auto_failover_disabled is set to true and all replicas are exhausted without finding a healthy replica, Spanner will wait for a replica in the list to become available, requests may fail due to DEADLINE_EXCEEDED errors.
-
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
| | |FLOAT32
|Float
| | |NUMERIC
|BigDecimal
| | |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
:FLOAT32
:NUMERIC
: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.
-
directed_read_options (Hash) (defaults to: nil) — Client options used to set the directed_read_options
for all ReadRequests and ExecuteSqlRequests that indicates which replicas
or regions should be used for non-transactional reads or queries.
Will represent
Google::Cloud::Spanner::V1::DirectedReadOptions
- (Google::Cloud::Spanner::Results) — The results of the query execution.
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.snapshot do |snp| results = snp.execute_query "SELECT * FROM users" results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end end
Query using query parameters:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.snapshot do |snp| results = snp.execute_query "SELECT * FROM users " \ "WHERE active = @active", params: { active: true } results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end end
Query with a SQL STRUCT query parameter as a Hash:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.snapshot do |snp| user_hash = { id: 1, name: "Charlie", active: false } results = snp.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 end
Specify the SQL STRUCT type using Fields object:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.snapshot do |snp| user_type = snp.fields id: :INT64, name: :STRING, active: :BOOL user_hash = { id: 1, name: nil, active: false } results = snp.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 end
Or, query with a SQL STRUCT as a typed Data object:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.snapshot do |snp| user_type = snp.fields id: :INT64, name: :STRING, active: :BOOL user_data = user_type.struct id: 1, name: nil, active: false results = snp.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 end
Query using query options:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.snapshot do |snp| results = snp.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 end
Query using custom timeout and retry policy:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" 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 } db.snapshot do |snp| results = snp.execute_query \ "SELECT * FROM users", call_options: call_options results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end end
#execute_sql
def execute_sql(sql, params: nil, types: nil, query_options: nil, call_options: nil, directed_read_options: nil) -> Google::Cloud::Spanner::Results
Executes a SQL query.
The following settings can be provided:
:exclude_replicas
(Hash) Exclude_replicas indicates what replicas should be excluded from serving requests. Spanner will not route requests to the replicas in this list.:include_replicas
(Hash) Include_replicas indicates the order of replicas to process the request. If auto_failover_disabled is set to true and all replicas are exhausted without finding a healthy replica, Spanner will wait for a replica in the list to become available, requests may fail due to DEADLINE_EXCEEDED errors.
-
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
| | |FLOAT32
|Float
| | |NUMERIC
|BigDecimal
| | |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
:FLOAT32
:NUMERIC
: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.
-
directed_read_options (Hash) (defaults to: nil) — Client options used to set the directed_read_options
for all ReadRequests and ExecuteSqlRequests that indicates which replicas
or regions should be used for non-transactional reads or queries.
Will represent
Google::Cloud::Spanner::V1::DirectedReadOptions
- (Google::Cloud::Spanner::Results) — The results of the query execution.
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.snapshot do |snp| results = snp.execute_query "SELECT * FROM users" results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end end
Query using query parameters:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.snapshot do |snp| results = snp.execute_query "SELECT * FROM users " \ "WHERE active = @active", params: { active: true } results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end end
Query with a SQL STRUCT query parameter as a Hash:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.snapshot do |snp| user_hash = { id: 1, name: "Charlie", active: false } results = snp.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 end
Specify the SQL STRUCT type using Fields object:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.snapshot do |snp| user_type = snp.fields id: :INT64, name: :STRING, active: :BOOL user_hash = { id: 1, name: nil, active: false } results = snp.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 end
Or, query with a SQL STRUCT as a typed Data object:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.snapshot do |snp| user_type = snp.fields id: :INT64, name: :STRING, active: :BOOL user_data = user_type.struct id: 1, name: nil, active: false results = snp.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 end
Query using query options:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.snapshot do |snp| results = snp.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 end
Query using custom timeout and retry policy:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" 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 } db.snapshot do |snp| results = snp.execute_query \ "SELECT * FROM users", call_options: call_options results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end end
#fields
def fields(types) -> Fields
Creates a configuration object (Fields) that may be provided to queries or used to create STRUCT objects. (The STRUCT will be represented by the Data class.) See Client#execute and/or Fields#struct.
For more information, see Data Types - Constructing a STRUCT.
-
types (Array, Hash) —
Accepts an array or hash types.
Arrays can contain just the type value, or a sub-array of the field's name and type value. Hash keys must contain the field name as a
Symbol
orString
, or the field position as anInteger
. Hash values must contain the type value. If a Hash is used the fields will be created using the same order as the Hash keys.Supported type values include:
:BOOL
:BYTES
:DATE
:FLOAT64
:FLOAT32
:NUMERIC
: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 - Nested Structs are specified by providing a Fields object.
- (Fields) — The fields of the given types.
Create a STRUCT value with named fields using Fields object:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.snapshot do |snp| named_type = snp.fields( { id: :INT64, name: :STRING, active: :BOOL } ) named_data = named_type.struct( { id: 42, name: nil, active: false } ) end
Create a STRUCT value with anonymous field names:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.snapshot do |snp| anon_type = snp.fields [:INT64, :STRING, :BOOL] anon_data = anon_type.struct [42, nil, false] end
Create a STRUCT value with duplicate field names:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.snapshot do |snp| dup_type = snp.fields [[:x, :INT64], [:x, :STRING], [:x, :BOOL]] dup_data = dup_type.struct [42, nil, false] end
#query
def query(sql, params: nil, types: nil, query_options: nil, call_options: nil, directed_read_options: nil) -> Google::Cloud::Spanner::Results
Executes a SQL query.
The following settings can be provided:
:exclude_replicas
(Hash) Exclude_replicas indicates what replicas should be excluded from serving requests. Spanner will not route requests to the replicas in this list.:include_replicas
(Hash) Include_replicas indicates the order of replicas to process the request. If auto_failover_disabled is set to true and all replicas are exhausted without finding a healthy replica, Spanner will wait for a replica in the list to become available, requests may fail due to DEADLINE_EXCEEDED errors.
-
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
| | |FLOAT32
|Float
| | |NUMERIC
|BigDecimal
| | |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
:FLOAT32
:NUMERIC
: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.
-
directed_read_options (Hash) (defaults to: nil) — Client options used to set the directed_read_options
for all ReadRequests and ExecuteSqlRequests that indicates which replicas
or regions should be used for non-transactional reads or queries.
Will represent
Google::Cloud::Spanner::V1::DirectedReadOptions
- (Google::Cloud::Spanner::Results) — The results of the query execution.
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.snapshot do |snp| results = snp.execute_query "SELECT * FROM users" results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end end
Query using query parameters:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.snapshot do |snp| results = snp.execute_query "SELECT * FROM users " \ "WHERE active = @active", params: { active: true } results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end end
Query with a SQL STRUCT query parameter as a Hash:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.snapshot do |snp| user_hash = { id: 1, name: "Charlie", active: false } results = snp.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 end
Specify the SQL STRUCT type using Fields object:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.snapshot do |snp| user_type = snp.fields id: :INT64, name: :STRING, active: :BOOL user_hash = { id: 1, name: nil, active: false } results = snp.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 end
Or, query with a SQL STRUCT as a typed Data object:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.snapshot do |snp| user_type = snp.fields id: :INT64, name: :STRING, active: :BOOL user_data = user_type.struct id: 1, name: nil, active: false results = snp.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 end
Query using query options:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.snapshot do |snp| results = snp.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 end
Query using custom timeout and retry policy:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" 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 } db.snapshot do |snp| results = snp.execute_query \ "SELECT * FROM users", call_options: call_options results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end end
#range
def range(beginning, ending, exclude_begin: false, exclude_end: false) -> Google::Cloud::Spanner::Range
Creates a Cloud Spanner Range. This can be used in place of a Ruby Range when needing to exclude the beginning value.
- beginning (Object) — The object that defines the beginning of the range.
- ending (Object) — The object that defines the end of the range.
-
exclude_begin (Boolean) (defaults to: false) — Determines if the range excludes its
beginning value. Default is
false
. -
exclude_end (Boolean) (defaults to: false) — Determines if the range excludes its
ending value. Default is
false
.
- (Google::Cloud::Spanner::Range) — The new Range instance.
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.snapshot do |snp| key_range = db.range 1, 100 results = snp.read "users", [:id, :name], keys: key_range results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end end
#read
def read(table, columns, keys: nil, index: nil, limit: nil, call_options: nil, directed_read_options: nil) -> Google::Cloud::Spanner::Results
Read rows from a database table, as a simple alternative to #execute_query.
The following settings can be provided:
:exclude_replicas
(Hash) Exclude_replicas indicates what replicas should be excluded from serving requests. Spanner will not route requests to the replicas in this list.:include_replicas
(Hash) Include_replicas indicates the order of replicas to process the request. If auto_failover_disabled is set to true and all replicas are exhausted without finding a healthy replica, Spanner will wait for a replica in the list to become available, requests may fail due to DEADLINE_EXCEEDED errors.
- 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.
-
directed_read_options (Hash) (defaults to: nil) — Client options used to set the directed_read_options
for all ReadRequests and ExecuteSqlRequests that indicates which replicas
or regions should be used for non-transactional reads or queries.
Will represent
Google::Cloud::Spanner::V1::DirectedReadOptions
- (Google::Cloud::Spanner::Results) — The results of the read operation.
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.snapshot do |snp| results = snp.read "users", [:id, :name] results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end end
#timestamp
def timestamp() -> Time
The read timestamp chosen for snapshots.
- (Time) — The chosen timestamp.
#transaction_id
def transaction_id() -> String
Identifier of the transaction results were run in.
- (String) — The transaction id.