Examples:
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"
results = db.execute_query "SELECT * FROM users"
results.rows.each do |row|
puts "User #{row[:id]} is #{row[:name]}"
end
Query using query parameters:
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"
results = db.execute_query(
"SELECT * FROM users WHERE active = @active",
params: { active: true }
)
results.rows.each do |row|
puts "User #{row[:id]} is #{row[:name]}"
end
Query with a SQL STRUCT query parameter as a Hash:
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"
user_hash = { id: 1, name: "Charlie", active: false }
results = db.execute_query(
"SELECT * FROM users WHERE " \
"ID = @user_struct.id " \
"AND name = @user_struct.name " \
"AND active = @user_struct.active",
params: { user_struct: user_hash }
)
results.rows.each do |row|
puts "User #{row[:id]} is #{row[:name]}"
end
Specify the SQL STRUCT type using Fields object:
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"
user_type = db.fields id: :INT64, name: :STRING, active: :BOOL
user_hash = { id: 1, name: nil, active: false }
results = db.execute_query(
"SELECT * FROM users WHERE " \
"ID = @user_struct.id " \
"AND name = @user_struct.name " \
"AND active = @user_struct.active",
params: { user_struct: user_hash },
types: { user_struct: user_type }
)
results.rows.each do |row|
puts "User #{row[:id]} is #{row[:name]}"
end
Or, query with a SQL STRUCT as a typed Data object:
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"
user_type = db.fields id: :INT64, name: :STRING, active: :BOOL
user_data = user_type.struct id: 1, name: nil, active: false
results = db.execute_query(
"SELECT * FROM users WHERE " \
"ID = @user_struct.id " \
"AND name = @user_struct.name " \
"AND active = @user_struct.active",
params: { user_struct: user_data }
)
results.rows.each do |row|
puts "User #{row[:id]} is #{row[:name]}"
end
Query using query options:
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"
results = db.execute_query \
"SELECT * FROM users", query_options: {
optimizer_version: "1",
optimizer_statistics_package: "auto_20191128_14_47_22UTC"
}
results.rows.each do |row|
puts "User #{row[:id]} is #{row[:name]}"
end
Query using custom timeout and retry policy:
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new
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 }
results = db.execute_query \
"SELECT * FROM users", call_options: call_options
results.rows.each do |row|
puts "User #{row[:id]} is #{row[:name]}"
end
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"
request_options = { priority: :PRIORITY_MEDIUM }
results = db.execute_query "SELECT * FROM users",
request_options: request_options
results.rows.each do |row|
puts "User #{row[:id]} is #{row[:name]}"
end
Query using tag for request query statistics collection.
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new
db = spanner.client "my-instance", "my-database"
request_options = { tag: "Read-Users" }
results = db.execute_query "SELECT * FROM users",
request_options: request_options
results.rows.each do |row|
puts "User #{row[:id]} is #{row[:name]}"
end