Class: Google::Cloud::Spanner::Snapshot

Inherits:

Object

Object
Google::Cloud::Spanner::Snapshot

show all

Defined in:: lib/google/cloud/spanner/snapshot.rb

Overview

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.

Examples:

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

Instance Method Summary collapse

#execute_query(sql, params: nil, types: nil, query_options: nil, call_options: nil, directed_read_options: nil) ⇒ Google::Cloud::Spanner::Results (also: #execute, #query, #execute_sql)

Executes a SQL query.
#fields(types) ⇒ Fields

Creates a configuration object (Fields) that may be provided to queries or used to create STRUCT objects.
#range(beginning, ending, exclude_begin: false, exclude_end: false) ⇒ Google::Cloud::Spanner::Range

Creates a Cloud Spanner Range.
#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 .
#timestamp ⇒ Time

The read timestamp chosen for snapshots.
#transaction_id ⇒ String

Identifier of the transaction results were run in.

Instance Method Details

#execute_query(sql, params: nil, types: nil, query_options: nil, call_options: nil, directed_read_options: nil) ⇒ `Google::Cloud::Spanner::Results` Also known as: execute, query, execute_sql

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.

Examples:

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

Parameters:

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.

See Data Types - Constructing a STRUCT.

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 in params. In these cases, the types 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

Returns:

(Google::Cloud::Spanner::Results) —

The results of the query execution.

# File 'lib/google/cloud/spanner/snapshot.rb', line 306

def execute_query sql, params: nil, types: nil, query_options: nil,
                  call_options: nil, directed_read_options: nil
  ensure_session!

  params, types = Convert.to_input_params_and_types params, types
  session.execute_query sql, params: params, types: types,
                             transaction: tx_selector,
                             query_options: query_options,
                             call_options: call_options,
                             directed_read_options: directed_read_options || @directed_read_options
end

#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.

See Data Types - Constructing a STRUCT.

Examples:

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

Parameters:

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 or String, or the field position as an Integer. 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.

Returns:

(Fields) —

The fields of the given types.



471
472
473

# File 'lib/google/cloud/spanner/snapshot.rb', line 471

def fields types
  Fields.new types
end

#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.

Examples:

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

Parameters:

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.

Returns:

(Google::Cloud::Spanner::Range) —

The new Range instance.

# File 'lib/google/cloud/spanner/snapshot.rb', line 504

def range beginning, ending, exclude_begin: false, exclude_end: false
  Range.new beginning, ending,
            exclude_begin: exclude_begin,
            exclude_end: exclude_end
end

#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 .

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.

Examples:

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

Parameters:

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

Returns:

(Google::Cloud::Spanner::Results) —

The results of the read operation.

# File 'lib/google/cloud/spanner/snapshot.rb', line 382

def read table, columns, keys: nil, index: nil, limit: nil,
         call_options: nil, directed_read_options: nil
  ensure_session!

  columns = Array(columns).map(&:to_s)
  keys = Convert.to_key_set keys

  session.read table, columns, keys: keys, index: index, limit: limit,
                               transaction: tx_selector,
                               call_options: call_options,
                               directed_read_options: directed_read_options || @directed_read_options
end

#timestamp ⇒ `Time`

The read timestamp chosen for snapshots.

Returns:

(Time) —

The chosen timestamp.

# File 'lib/google/cloud/spanner/snapshot.rb', line 58

def timestamp
  return nil if @grpc.nil?
  Convert.timestamp_to_time @grpc.read_timestamp
end

#transaction_id ⇒ `String`

Identifier of the transaction results were run in.

Returns:

(String) —

The transaction id.

# File 'lib/google/cloud/spanner/snapshot.rb', line 50

def transaction_id
  return nil if @grpc.nil?
  @grpc.id
end

Class: Google::Cloud::Spanner::Snapshot

Overview

Snapshot

Examples:

Instance Method Summary collapse

Instance Method Details

#execute_query(sql, params: nil, types: nil, query_options: nil, call_options: nil, directed_read_options: nil) ⇒ Google::Cloud::Spanner::Results Also known as: execute, query, execute_sql

Examples:

Query using query parameters:

Query with a SQL STRUCT query parameter as a Hash:

Specify the SQL STRUCT type using Fields object:

Or, query with a SQL STRUCT as a typed Data object:

Query using query options:

Query using custom timeout and retry policy:

#fields(types) ⇒ Fields

Examples:

Create a STRUCT value with named fields using Fields object:

Create a STRUCT value with anonymous field names:

Create a STRUCT value with duplicate field names:

#range(beginning, ending, exclude_begin: false, exclude_end: false) ⇒ Google::Cloud::Spanner::Range

Examples:

#read(table, columns, keys: nil, index: nil, limit: nil, call_options: nil, directed_read_options: nil) ⇒ Google::Cloud::Spanner::Results

Examples:

#timestamp ⇒ Time

#transaction_id ⇒ String

#execute_query(sql, params: nil, types: nil, query_options: nil, call_options: nil, directed_read_options: nil) ⇒ `Google::Cloud::Spanner::Results` Also known as: execute, query, execute_sql

#fields(types) ⇒ `Fields`

#range(beginning, ending, exclude_begin: false, exclude_end: false) ⇒ `Google::Cloud::Spanner::Range`

#read(table, columns, keys: nil, index: nil, limit: nil, call_options: nil, directed_read_options: nil) ⇒ `Google::Cloud::Spanner::Results`

#timestamp ⇒ `Time`

#transaction_id ⇒ `String`