Transaction
A transaction in Cloud Spanner is a set of reads and writes that execute atomically at a single logical point in time across columns, rows, and tables in a database.
All changes are accumulated in memory until the block passed to Client#transaction completes. Transactions will be automatically retried when possible. See Client#transaction.
Inherits
- Object
Example
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.transaction do |tx| # Read the second album budget. second_album_result = tx.read "Albums", ["marketing_budget"], keys: [[2, 2]], limit: 1 second_album_row = second_album_result.rows.first second_album_budget = second_album_row.values.first transfer_amount = 200000 if second_album_budget < 300000="" #="" raising="" an="" exception="" will="" automatically="" roll="" back="" the="" #="" transaction.="" raise="" "the="" second="" album="" doesn't="" have="" enough="" funds="" to="" transfer"="" end="" #="" read="" the="" first="" album's="" budget.="" first_album_result="tx.read" "albums",="" ["marketing_budget"],="" keys:="" [[1,="" 1]],="" limit:="" 1="" first_album_row="first_album_result.rows.first" first_album_budget="first_album_row.values.first" #="" update="" the="" budgets.="" second_album_budget="" -="transfer_amount" first_album_budget="" +="transfer_amount" puts="" "setting="" first="" album's="" budget="" to="" #{first_album_budget}="" and="" "="" \="" "the="" second="" album's="" budget="" to="" #{second_album_budget}."="" #="" update="" the="" rows.="" rows="[" {singer_id:="" 1,="" album_id:="" 1,="" marketing_budget:="" first_album_budget},="" {singer_id:="" 2,="" album_id:="" 2,="" marketing_budget:="" second_album_budget}="" ]="" tx.update="" "albums",="" rows="">
Methods
#batch_update
def batch_update(request_options: nil, call_options: nil, &block) { |batch_update| ... } -> Array<Integer>
Executes DML statements in a batch.
-
request_options (Hash) (defaults to: nil) —
Common request options.
:priority
(String) The relative priority for requests. The priority acts as a hint to the Cloud Spanner scheduler and does not guarantee priority or order of execution. Valid values are:PRIORITY_LOW
,:PRIORITY_MEDIUM
,:PRIORITY_HIGH
. If priority not set then default isPRIORITY_UNSPECIFIED
is equivalent to:PRIORITY_HIGH
.:tag
(String) A per-request tag which can be applied to queries or reads, used for statistics collection. Tag must be a valid identifier of the form:[a-zA-Z][a-zA-Z0-9_\-]
between 2 and 64 characters in length.
-
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.
- (batch_update) — a batch update object
- batch_update (Google::Cloud::Spanner::BatchUpdate) — a batch update object accepting DML statements and optional parameters and types of the parameters.
- (Array<Integer>) — A list with the exact number of rows that were modified for each DML statement.
- (Google::Cloud::Spanner::BatchUpdateError) — If an error occurred while executing a statement. The error object contains a cause error with the service error type and message, and a list with the exact number of rows that were modified for each successful statement before the error.
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.transaction do |tx| begin row_counts = tx.batch_update do |b| statement_count = b.batch_update( "UPDATE users SET name = 'Charlie' WHERE id = 1" ) end puts row_counts.inspect rescue Google::Cloud::Spanner::BatchUpdateError => err puts err.cause.message puts err.row_counts.inspect end end
Update using SQL parameters:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.transaction do |tx| begin row_counts = tx.batch_update do |b| statement_count = b.batch_update( "UPDATE users SET name = 'Charlie' WHERE id = 1", params: { id: 1, name: "Charlie" } ) end puts row_counts.inspect rescue Google::Cloud::Spanner::BatchUpdateError => err puts err.cause.message puts err.row_counts.inspect end end
#commit_timestamp
def commit_timestamp() -> ColumnValue
Creates a column value object representing setting a field's value to the timestamp of the commit. (See Client#commit_timestamp)
This placeholder value can only be used for timestamp columns that have set the option "(allow_commit_timestamp=true)" in the schema.
- (ColumnValue) — The commit timestamp column value object.
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.transaction do |tx| tx.insert "users", [ { id: 5, name: "Murphy", updated_at: tx.commit_timestamp } ] end
#delete
def delete(table, keys = [])
Deletes rows from a table. Succeeds whether or not the specified rows were present.
All changes are accumulated in memory until the block passed to Client#transaction completes.
- table (String) — The name of the table in the database to be modified.
- keys (Object, Array<Object>) — 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.
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.transaction { |tx| tx.delete "users", [1, 2, 3] }
#execute
def execute(sql, params: nil, types: nil, query_options: nil, request_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
| | |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
: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.
-
request_options (Hash) (defaults to: nil) —
Common request options.
:priority
(String) The relative priority for requests. The priority acts as a hint to the Cloud Spanner scheduler and does not guarantee priority or order of execution. Valid values are:PRIORITY_LOW
,:PRIORITY_MEDIUM
,:PRIORITY_HIGH
. If priority not set then default isPRIORITY_UNSPECIFIED
is equivalent to:PRIORITY_HIGH
.:tag
(String) A per-request tag which can be applied to queries or reads, used for statistics collection. Tag must be a valid identifier of the form:[a-zA-Z][a-zA-Z0-9_\-]
between 2 and 64 characters in length.
-
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 db = spanner.client "my-instance", "my-database" db.transaction do |tx| results = tx.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.transaction do |tx| results = tx.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.transaction do |tx| user_hash = { id: 1, name: "Charlie", active: false } results = tx.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.transaction do |tx| user_type = tx.fields id: :INT64, name: :STRING, active: :BOOL user_hash = { id: 1, name: nil, active: false } results = tx.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.transaction do |tx| user_type = tx.fields id: :INT64, name: :STRING, active: :BOOL user_data = user_type.struct id: 1, name: nil, active: false results = tx.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.transaction do |tx| results = tx.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.transaction do |tx| results = tx.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, request_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
| | |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
: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.
-
request_options (Hash) (defaults to: nil) —
Common request options.
:priority
(String) The relative priority for requests. The priority acts as a hint to the Cloud Spanner scheduler and does not guarantee priority or order of execution. Valid values are:PRIORITY_LOW
,:PRIORITY_MEDIUM
,:PRIORITY_HIGH
. If priority not set then default isPRIORITY_UNSPECIFIED
is equivalent to:PRIORITY_HIGH
.:tag
(String) A per-request tag which can be applied to queries or reads, used for statistics collection. Tag must be a valid identifier of the form:[a-zA-Z][a-zA-Z0-9_\-]
between 2 and 64 characters in length.
-
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 db = spanner.client "my-instance", "my-database" db.transaction do |tx| results = tx.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.transaction do |tx| results = tx.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.transaction do |tx| user_hash = { id: 1, name: "Charlie", active: false } results = tx.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.transaction do |tx| user_type = tx.fields id: :INT64, name: :STRING, active: :BOOL user_hash = { id: 1, name: nil, active: false } results = tx.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.transaction do |tx| user_type = tx.fields id: :INT64, name: :STRING, active: :BOOL user_data = user_type.struct id: 1, name: nil, active: false results = tx.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.transaction do |tx| results = tx.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.transaction do |tx| results = tx.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, request_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
| | |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
: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.
-
request_options (Hash) (defaults to: nil) —
Common request options.
:priority
(String) The relative priority for requests. The priority acts as a hint to the Cloud Spanner scheduler and does not guarantee priority or order of execution. Valid values are:PRIORITY_LOW
,:PRIORITY_MEDIUM
,:PRIORITY_HIGH
. If priority not set then default isPRIORITY_UNSPECIFIED
is equivalent to:PRIORITY_HIGH
.:tag
(String) A per-request tag which can be applied to queries or reads, used for statistics collection. Tag must be a valid identifier of the form:[a-zA-Z][a-zA-Z0-9_\-]
between 2 and 64 characters in length.
-
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 db = spanner.client "my-instance", "my-database" db.transaction do |tx| results = tx.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.transaction do |tx| results = tx.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.transaction do |tx| user_hash = { id: 1, name: "Charlie", active: false } results = tx.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.transaction do |tx| user_type = tx.fields id: :INT64, name: :STRING, active: :BOOL user_hash = { id: 1, name: nil, active: false } results = tx.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.transaction do |tx| user_type = tx.fields id: :INT64, name: :STRING, active: :BOOL user_data = user_type.struct id: 1, name: nil, active: false results = tx.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.transaction do |tx| results = tx.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.transaction do |tx| results = tx.execute_query \ "SELECT * FROM users", call_options: call_options results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end end
#execute_update
def execute_update(sql, params: nil, types: nil, query_options: nil, request_options: nil, call_options: nil) -> Integer
Executes a DML statement.
-
sql (String) — The DML statement string. See Query
syntax.
The DML statement 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) — Parameters for the DML statement 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
| | |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 can be used to specify the exact SQL type for some or all of the SQL query parameters.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
: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.
-
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.
-
request_options (Hash) (defaults to: nil) —
Common request options.
:priority
(String) The relative priority for requests. The priority acts as a hint to the Cloud Spanner scheduler and does not guarantee priority or order of execution. Valid values are:PRIORITY_LOW
,:PRIORITY_MEDIUM
,:PRIORITY_HIGH
. If priority not set then default isPRIORITY_UNSPECIFIED
is equivalent to:PRIORITY_HIGH
.:tag
(String) A per-request tag which can be applied to queries or reads, used for statistics collection. Tag must be a valid identifier of the form:[a-zA-Z][a-zA-Z0-9_\-]
between 2 and 64 characters in length.
-
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.
- (Integer) — The exact number of rows that were modified.
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.transaction do |tx| row_count = tx.execute_update( "UPDATE users SET name = 'Charlie' WHERE id = 1" ) end
Update using SQL parameters:
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.transaction do |tx| row_count = tx.execute_update( "UPDATE users SET name = @name WHERE id = @id", params: { id: 1, name: "Charlie" } ) end
Update using query options
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.transaction do |tx| row_count = tx.execute_update( "UPDATE users SET name = 'Charlie' WHERE id = 1", query_options: { optimizer_version: "1", optimizer_statistics_package: "auto_20191128_14_47_22UTC" } ) end
Update 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.transaction do |tx| row_count = tx.execute_update( "UPDATE users SET name = 'Charlie' WHERE id = 1", call_options: call_options ) 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 incude:
:BOOL
:BYTES
:DATE
:FLOAT64
: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.transaction do |tx| named_type = tx.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.transaction do |tx| anon_type = tx.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.transaction do |tx| dup_type = tx.fields [[:x, :INT64], [:x, :STRING], [:x, :BOOL]] dup_data = dup_type.struct [42, nil, false] end
#initialize
def initialize() -> Transaction
- (Transaction) — a new instance of Transaction
#insert
def insert(table, *rows)
Inserts new rows in a table. If any of the rows already exist, the write or request fails with error AlreadyExistsError.
All changes are accumulated in memory until the block passed to Client#transaction completes.
- table (String) — The name of the table in the database to be modified.
-
rows (Array<Hash>) — One or more hash objects with the hash keys
matching the table's columns, and the hash values matching the
table's values.
Ruby types are mapped to Spanner types as follows:
| Spanner | Ruby | Notes | |-------------|----------------|---| |
BOOL
|true
/false
| | |INT64
|Integer
| | |FLOAT64
|Float
| | |NUMERIC
|BigDecimal
| | |STRING
|String
| | |DATE
|Date
| | |TIMESTAMP
|Time
,DateTime
| | |BYTES
|File
,IO
,StringIO
, or similar | | |ARRAY
|Array
| Nested arrays are not supported. |See Data types.
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.transaction do |tx| tx.insert "users", [{ id: 1, name: "Charlie", active: false }, { id: 2, name: "Harvey", active: true }] end
#query
def query(sql, params: nil, types: nil, query_options: nil, request_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
| | |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
: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.
-
request_options (Hash) (defaults to: nil) —
Common request options.
:priority
(String) The relative priority for requests. The priority acts as a hint to the Cloud Spanner scheduler and does not guarantee priority or order of execution. Valid values are:PRIORITY_LOW
,:PRIORITY_MEDIUM
,:PRIORITY_HIGH
. If priority not set then default isPRIORITY_UNSPECIFIED
is equivalent to:PRIORITY_HIGH
.:tag
(String) A per-request tag which can be applied to queries or reads, used for statistics collection. Tag must be a valid identifier of the form:[a-zA-Z][a-zA-Z0-9_\-]
between 2 and 64 characters in length.
-
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 db = spanner.client "my-instance", "my-database" db.transaction do |tx| results = tx.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.transaction do |tx| results = tx.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.transaction do |tx| user_hash = { id: 1, name: "Charlie", active: false } results = tx.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.transaction do |tx| user_type = tx.fields id: :INT64, name: :STRING, active: :BOOL user_hash = { id: 1, name: nil, active: false } results = tx.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.transaction do |tx| user_type = tx.fields id: :INT64, name: :STRING, active: :BOOL user_data = user_type.struct id: 1, name: nil, active: false results = tx.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.transaction do |tx| results = tx.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.transaction do |tx| results = tx.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.transaction do |tx| key_range = tx.range 1, 100 results = tx.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, request_options: 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.
-
request_options (Hash) (defaults to: nil) —
Common request options.
:priority
(String) The relative priority for requests. The priority acts as a hint to the Cloud Spanner scheduler and does not guarantee priority or order of execution. Valid values are:PRIORITY_LOW
,:PRIORITY_MEDIUM
,:PRIORITY_HIGH
. If priority not set then default isPRIORITY_UNSPECIFIED
is equivalent to:PRIORITY_HIGH
.:tag
(String) A per-request tag which can be applied to queries or reads, used for statistics collection. Tag must be a valid identifier of the form:[a-zA-Z][a-zA-Z0-9_\-]
between 2 and 64 characters in length.
-
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 db = spanner.client "my-instance", "my-database" db.transaction do |tx| results = tx.read "users", [:id, :name] results.rows.each do |row| puts "User #{row[:id]} is #{row[:name]}" end end
#replace
def replace(table, *rows)
Inserts or replaces rows in a table. If any of the rows already exist,
it is deleted, and the column values provided are inserted instead.
Unlike #upsert, this means any values not explicitly written become
NULL
.
All changes are accumulated in memory until the block passed to Client#transaction completes.
- table (String) — The name of the table in the database to be modified.
-
rows (Array<Hash>) — One or more hash objects with the hash keys
matching the table's columns, and the hash values matching the
table's values.
Ruby types are mapped to Spanner types as follows:
| Spanner | Ruby | Notes | |-------------|----------------|---| |
BOOL
|true
/false
| | |INT64
|Integer
| | |FLOAT64
|Float
| | |NUMERIC
|BigDecimal
| | |STRING
|String
| | |DATE
|Date
| | |TIMESTAMP
|Time
,DateTime
| | |BYTES
|File
,IO
,StringIO
, or similar | | |ARRAY
|Array
| Nested arrays are not supported. |See Data types.
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.transaction do |tx| tx.replace "users", [{ id: 1, name: "Charlie", active: false }, { id: 2, name: "Harvey", active: true }] end
#save
def save(table, *rows)
Inserts or updates rows in a table. If any of the rows already exist, then its column values are overwritten with the ones provided. Any column values not explicitly written are preserved.
All changes are accumulated in memory until the block passed to Client#transaction completes.
- table (String) — The name of the table in the database to be modified.
-
rows (Array<Hash>) — One or more hash objects with the hash keys
matching the table's columns, and the hash values matching the
table's values.
Ruby types are mapped to Spanner types as follows:
| Spanner | Ruby | Notes | |-------------|----------------|---| |
BOOL
|true
/false
| | |INT64
|Integer
| | |FLOAT64
|Float
| | |NUMERIC
|BigDecimal
| | |STRING
|String
| | |DATE
|Date
| | |TIMESTAMP
|Time
,DateTime
| | |BYTES
|File
,IO
,StringIO
, or similar | | |ARRAY
|Array
| Nested arrays are not supported. |See Data types.
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.transaction do |tx| tx.upsert "users", [{ id: 1, name: "Charlie", active: false }, { id: 2, name: "Harvey", active: true }] end
#transaction_id
def transaction_id() -> String
Identifier of the transaction results were run in.
- (String) — The transaction id.
#update
def update(table, *rows)
Updates existing rows in a table. If any of the rows does not already exist, the request fails with error NotFoundError.
All changes are accumulated in memory until the block passed to Client#transaction completes.
- table (String) — The name of the table in the database to be modified.
-
rows (Array<Hash>) — One or more hash objects with the hash keys
matching the table's columns, and the hash values matching the
table's values.
Ruby types are mapped to Spanner types as follows:
| Spanner | Ruby | Notes | |-------------|----------------|---| |
BOOL
|true
/false
| | |INT64
|Integer
| | |FLOAT64
|Float
| | |NUMERIC
|BigDecimal
| | |STRING
|String
| | |DATE
|Date
| | |TIMESTAMP
|Time
,DateTime
| | |BYTES
|File
,IO
,StringIO
, or similar | | |ARRAY
|Array
| Nested arrays are not supported. |See Data types.
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.transaction do |tx| tx.update "users", [{ id: 1, name: "Charlie", active: false }, { id: 2, name: "Harvey", active: true }] end
#upsert
def upsert(table, *rows)
Inserts or updates rows in a table. If any of the rows already exist, then its column values are overwritten with the ones provided. Any column values not explicitly written are preserved.
All changes are accumulated in memory until the block passed to Client#transaction completes.
- table (String) — The name of the table in the database to be modified.
-
rows (Array<Hash>) — One or more hash objects with the hash keys
matching the table's columns, and the hash values matching the
table's values.
Ruby types are mapped to Spanner types as follows:
| Spanner | Ruby | Notes | |-------------|----------------|---| |
BOOL
|true
/false
| | |INT64
|Integer
| | |FLOAT64
|Float
| | |NUMERIC
|BigDecimal
| | |STRING
|String
| | |DATE
|Date
| | |TIMESTAMP
|Time
,DateTime
| | |BYTES
|File
,IO
,StringIO
, or similar | | |ARRAY
|Array
| Nested arrays are not supported. |See Data types.
require "google/cloud/spanner" spanner = Google::Cloud::Spanner.new db = spanner.client "my-instance", "my-database" db.transaction do |tx| tx.upsert "users", [{ id: 1, name: "Charlie", active: false }, { id: 2, name: "Harvey", active: true }] end