spanner/spanner_samples.rb (1,699 lines of code) (raw):
# Copyright 2017 Google, Inc
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
def create_instance project_id:, instance_id:, instance_config_id: "regional-us-central1"
# [START spanner_create_instance]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# instance_config_id = "Your Spanner InstanceConfig ID"
require "google/cloud/spanner"
require "google/cloud/spanner/admin/instance"
instance_admin_client = Google::Cloud::Spanner::Admin::Instance.instance_admin
project_path = instance_admin_client.project_path project: project_id
instance_path = instance_admin_client.instance_path project: project_id, instance: instance_id
instance_config_path = instance_admin_client.instance_config_path project: project_id, instance_config: instance_config_id
job = instance_admin_client.create_instance parent: project_path,
instance_id: instance_id,
instance: { name: instance_path,
config: instance_config_path,
display_name: instance_id,
node_count: 2,
labels: { cloud_spanner_samples: "true" } }
puts "Waiting for create instance operation to complete"
job.wait_until_done!
if job.error?
puts job.error
else
puts "Created instance #{instance_id}"
end
# [END spanner_create_instance]
end
def create_instance_with_processing_units project_id:, instance_id:
# [START spanner_create_instance_with_processing_units]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
require "google/cloud/spanner"
require "google/cloud/spanner/admin/instance"
instance_admin_client = Google::Cloud::Spanner::Admin::Instance.instance_admin
project_path = instance_admin_client.project_path project: project_id
instance_path = instance_admin_client.instance_path project: project_id, instance: instance_id
instance_config_path = instance_admin_client.instance_config_path project: project_id, instance_config: "regional-us-central1"
job = instance_admin_client.create_instance parent: project_path,
instance_id: instance_id,
instance: { name: instance_path,
config: instance_config_path,
display_name: instance_id,
processing_units: 500,
labels: { cloud_spanner_samples: "true" } }
puts "Waiting for creating instance operation to complete"
job.wait_until_done!
if job.error?
puts job.error
else
puts "Created instance #{instance_id}"
end
instance = instance_admin_client.get_instance name: instance_path
puts "Instance #{instance_id} has #{instance.processing_units} processing units."
# [END spanner_create_instance_with_processing_units]
end
def create_database project_id:, instance_id:, database_id:
# [START spanner_create_database]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"
database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin
instance_path = database_admin_client.instance_path project: project_id, instance: instance_id
job = database_admin_client.create_database parent: instance_path,
create_statement: "CREATE DATABASE `#{database_id}`",
extra_statements: [
"CREATE TABLE Singers (
SingerId INT64 NOT NULL,
FirstName STRING(1024),
LastName STRING(1024),
SingerInfo BYTES(MAX)
) PRIMARY KEY (SingerId)",
"CREATE TABLE Albums (
SingerId INT64 NOT NULL,
AlbumId INT64 NOT NULL,
AlbumTitle STRING(MAX)
) PRIMARY KEY (SingerId, AlbumId),
INTERLEAVE IN PARENT Singers ON DELETE CASCADE"
]
puts "Waiting for create database operation to complete"
job.wait_until_done!
puts "Created database #{database_id} on instance #{instance_id}"
# [END spanner_create_database]
end
def create_database_with_version_retention_period project_id:, instance_id:, database_id:
# [START spanner_create_database_with_version_retention_period]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"
database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin
instance_path = database_admin_client.instance_path project: project_id, instance: instance_id
version_retention_period = "7d"
db_path = database_admin_client.database_path project: project_id,
instance: instance_id,
database: database_id
job = database_admin_client.create_database parent: instance_path,
create_statement: "CREATE DATABASE `#{database_id}`",
extra_statements: [
"CREATE TABLE Singers (
SingerId INT64 NOT NULL,
FirstName STRING(1024),
LastName STRING(1024),
SingerInfo BYTES(MAX)
) PRIMARY KEY (SingerId)",
"CREATE TABLE Albums (
SingerId INT64 NOT NULL,
AlbumId INT64 NOT NULL,
AlbumTitle STRING(MAX)
) PRIMARY KEY (SingerId, AlbumId),
INTERLEAVE IN PARENT Singers ON DELETE CASCADE",
"ALTER DATABASE `#{database_id}`
SET OPTIONS ( version_retention_period = '#{version_retention_period}' )"
]
puts "Waiting for create database operation to complete"
job.wait_until_done!
database = database_admin_client.get_database name: db_path
puts "Created database #{database_id} on instance #{instance_id}"
puts "\tVersion retention period: #{database.version_retention_period}"
puts "\tEarliest version time: #{database.earliest_version_time}"
# [END spanner_create_database_with_version_retention_period]
end
def create_database_with_encryption_key project_id:, instance_id:, database_id:, kms_key_name:
# [START spanner_create_database_with_encryption_key]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
# kms_key_name = "Database eencryption KMS key"
require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"
database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin
instance_path = database_admin_client.instance_path project: project_id, instance: instance_id
db_path = database_admin_client.database_path project: project_id,
instance: instance_id,
database: database_id
job = database_admin_client.create_database parent: instance_path,
create_statement: "CREATE DATABASE `#{database_id}`",
extra_statements: [
"CREATE TABLE Singers (
SingerId INT64 NOT NULL,
FirstName STRING(1024),
LastName STRING(1024),
SingerInfo BYTES(MAX)
) PRIMARY KEY (SingerId)",
"CREATE TABLE Albums (
SingerId INT64 NOT NULL,
AlbumId INT64 NOT NULL,
AlbumTitle STRING(MAX)
) PRIMARY KEY (SingerId, AlbumId),
INTERLEAVE IN PARENT Singers ON DELETE CASCADE"
],
encryption_config: { kms_key_name: kms_key_name }
puts "Waiting for create database operation to complete"
job.wait_until_done!
database = database_admin_client.get_database name: db_path
puts "Database #{database_id} created with encryption key #{database.encryption_config.kms_key_name}"
# [END spanner_create_database_with_encryption_key]
end
def create_database_with_multiple_kms_keys(
project_id:, instance_id:, database_id:, kms_key_names:
)
# [START spanner_create_database_with_MR_CMEK]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
# kms_key_names = ["key1", "key2", "key3"]
require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"
database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin
instance_path = database_admin_client.instance_path(
project: project_id, instance: instance_id
)
encryption_config = {
kms_key_names: kms_key_names
}
db_path = database_admin_client.database_path(
project: project_id,
instance: instance_id,
database: database_id
)
job = database_admin_client.create_database(
parent: instance_path,
create_statement: "CREATE DATABASE `#{database_id}`",
extra_statements: [
<<~STATEMENT,
CREATE TABLE Singers (
SingerId INT64 NOT NULL,
FirstName STRING(1024),
LastName STRING(1024),
SingerInfo BYTES(MAX)
) PRIMARY KEY (SingerId)
STATEMENT
<<~STATEMENT
CREATE TABLE Albums (
SingerId INT64 NOT NULL,
AlbumId INT64 NOT NULL,
AlbumTitle STRING(MAX)
) PRIMARY KEY (SingerId, AlbumId),
INTERLEAVE IN PARENT Singers
ON DELETE CASCADE
STATEMENT
],
encryption_config: encryption_config
)
puts "Waiting for create database operation to complete"
job.wait_until_done!
database = database_admin_client.get_database name: db_path
puts "Database #{database_id} created with encryption key " \
"#{database.encryption_config.kms_key_names}"
# [END spanner_create_database_with_MR_CMEK]
end
def create_dml_database project_id:, instance_id:, database_id:
require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"
database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin
instance_path = database_admin_client.instance_path project: project_id, instance: instance_id
job = database_admin_client.create_database parent: instance_path,
create_statement: "CREATE DATABASE `#{database_id}`",
extra_statements: [
"CREATE TABLE Singers (
SingerId INT64 NOT NULL,
FirstName STRING(1024),
LastName STRING(1024),
SingerInfo BYTES(MAX),
FullName STRING(2048) AS (ARRAY_TO_STRING([FirstName, LastName], \" \")) STORED
) PRIMARY KEY (SingerId)",
"CREATE TABLE Albums (
SingerId INT64 NOT NULL,
AlbumId INT64 NOT NULL,
AlbumTitle STRING(MAX),
MarketingBudget INT64
) PRIMARY KEY (SingerId, AlbumId),
INTERLEAVE IN PARENT Singers ON DELETE CASCADE"
]
puts "Waiting for create database operation to complete"
job.wait_until_done!
puts "Created database #{database_id} on instance #{instance_id}"
end
def create_table_with_timestamp_column project_id:, instance_id:, database_id:
# [START spanner_create_table_with_timestamp_column]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"
database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin
db_path = database_admin_client.database_path project: project_id,
instance: instance_id,
database: database_id
job = database_admin_client.update_database_ddl database: db_path,
statements: [
"CREATE TABLE Performances (
SingerId INT64 NOT NULL,
VenueId INT64 NOT NULL,
EventDate Date,
Revenue INT64,
LastUpdateTime TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true)
) PRIMARY KEY (SingerId, VenueId, EventDate),
INTERLEAVE IN PARENT Singers ON DELETE CASCADE"
]
puts "Waiting for update database operation to complete"
job.wait_until_done!
puts "Created table Performances in #{database_id}"
# [END spanner_create_table_with_timestamp_column]
end
def insert_data project_id:, instance_id:, database_id:
# [START spanner_insert_data]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
client.commit do |c|
c.insert "Singers", [
{ SingerId: 1, FirstName: "Marc", LastName: "Richards" },
{ SingerId: 2, FirstName: "Catalina", LastName: "Smith" },
{ SingerId: 3, FirstName: "Alice", LastName: "Trentor" },
{ SingerId: 4, FirstName: "Lea", LastName: "Martin" },
{ SingerId: 5, FirstName: "David", LastName: "Lomond" }
]
c.insert "Albums", [
{ SingerId: 1, AlbumId: 1, AlbumTitle: "Total Junk" },
{ SingerId: 1, AlbumId: 2, AlbumTitle: "Go, Go, Go" },
{ SingerId: 2, AlbumId: 1, AlbumTitle: "Green" },
{ SingerId: 2, AlbumId: 2, AlbumTitle: "Forever Hold Your Peace" },
{ SingerId: 2, AlbumId: 3, AlbumTitle: "Terrified" }
]
end
puts "Inserted data"
# [END spanner_insert_data]
end
def insert_dml_data project_id:, instance_id:, database_id:
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
client.commit do |c|
c.insert "Singers", [
{ SingerId: 1, FirstName: "Marc", LastName: "Richards" },
{ SingerId: 2, FirstName: "Catalina", LastName: "Smith" },
{ SingerId: 3, FirstName: "Alice", LastName: "Trentor" },
{ SingerId: 4, FirstName: "Lea", LastName: "Martin" },
{ SingerId: 5, FirstName: "David", LastName: "Lomond" }
]
c.insert "Albums", [
{ SingerId: 1, AlbumId: 1, AlbumTitle: "Total Junk", MarketingBudget: 20_000 },
{ SingerId: 1, AlbumId: 2, AlbumTitle: "Go, Go, Go", MarketingBudget: 20_000 },
{ SingerId: 2, AlbumId: 1, AlbumTitle: "Green", MarketingBudget: 20_000 },
{ SingerId: 2, AlbumId: 2, AlbumTitle: "Forever Hold Your Peace", MarketingBudget: 20_000 },
{ SingerId: 2, AlbumId: 3, AlbumTitle: "Terrified", MarketingBudget: 20_000 }
]
end
puts "Inserted data"
end
def insert_data_with_timestamp_column project_id:, instance_id:, database_id:
# [START spanner_insert_data_with_timestamp_column]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
# Get commit_timestamp
commit_timestamp = client.commit_timestamp
client.commit do |c|
c.insert "Performances", [
{ SingerId: 1, VenueId: 4, EventDate: "2017-10-05", Revenue: 11_000, LastUpdateTime: commit_timestamp },
{ SingerId: 1, VenueId: 19, EventDate: "2017-11-02", Revenue: 15_000, LastUpdateTime: commit_timestamp },
{ SingerId: 2, VenueId: 42, EventDate: "2017-12-23", Revenue: 7000, LastUpdateTime: commit_timestamp }
]
end
puts "Inserted data"
# [END spanner_insert_data_with_timestamp_column]
end
def query_data project_id:, instance_id:, database_id:
# [START spanner_query_data]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
client.execute("SELECT SingerId, AlbumId, AlbumTitle FROM Albums").rows.each do |row|
puts "#{row[:SingerId]} #{row[:AlbumId]} #{row[:AlbumTitle]}"
end
# [END spanner_query_data]
end
def query_data_with_timestamp_column project_id:, instance_id:, database_id:
# [START spanner_query_data_with_timestamp_column]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
client.execute("SELECT SingerId, AlbumId, MarketingBudget, LastUpdateTime
FROM Albums ORDER BY LastUpdateTime DESC").rows.each do |row|
puts "#{row[:SingerId]} #{row[:AlbumId]} #{row[:MarketingBudget]} #{row[:LastUpdateTime]}"
end
# [END spanner_query_data_with_timestamp_column]
end
def read_data project_id:, instance_id:, database_id:
# [START spanner_read_data]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
client.read("Albums", [:SingerId, :AlbumId, :AlbumTitle]).rows.each do |row|
puts "#{row[:SingerId]} #{row[:AlbumId]} #{row[:AlbumTitle]}"
end
# [END spanner_read_data]
end
def delete_data project_id:, instance_id:, database_id:
# [START spanner_delete_data]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
# Delete individual rows
client.delete "Albums", [[2, 1], [2, 3]]
# Delete a range of rows where the column key is >=3 and <5
key_range = client.range 3, 5, exclude_end: true
client.delete "Singers", key_range
# Delete remaining Singers rows, which will also delete the remaining
# Albums rows because Albums was defined with ON DELETE CASCADE
client.delete "Singers"
# [END spanner_delete_data]
end
def read_stale_data project_id:, instance_id:, database_id:
# [START spanner_read_stale_data]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
# Perform a read with a data staleness of 15 seconds
client.snapshot staleness: 15 do |snapshot|
snapshot.read("Albums", [:SingerId, :AlbumId, :AlbumTitle]).rows.each do |row|
puts "#{row[:SingerId]} #{row[:AlbumId]} #{row[:AlbumTitle]}"
end
end
# [END spanner_read_stale_data]
end
def create_index project_id:, instance_id:, database_id:
# [START spanner_create_index]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"
database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin
db_path = database_admin_client.database_path project: project_id,
instance: instance_id,
database: database_id
job = database_admin_client.update_database_ddl database: db_path,
statements: [
"CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)"
]
puts "Waiting for database update to complete"
job.wait_until_done!
puts "Added the AlbumsByAlbumTitle index"
# [END spanner_create_index]
end
def create_storing_index project_id:, instance_id:, database_id:
# [START spanner_create_storing_index]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"
database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin
db_path = database_admin_client.database_path project: project_id,
instance: instance_id,
database: database_id
job = database_admin_client.update_database_ddl database: db_path,
statements: [
"CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle)
STORING (MarketingBudget)"
]
puts "Waiting for database update to complete"
job.wait_until_done!
puts "Added the AlbumsByAlbumTitle2 storing index"
# [END spanner_create_storing_index]
end
def add_column project_id:, instance_id:, database_id:
# [START spanner_add_column]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"
database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin
db_path = database_admin_client.database_path project: project_id,
instance: instance_id,
database: database_id
job = database_admin_client.update_database_ddl database: db_path,
statements: [
"ALTER TABLE Albums ADD COLUMN MarketingBudget INT64"
]
puts "Waiting for database update to complete"
job.wait_until_done!
puts "Added the MarketingBudget column"
# [END spanner_add_column]
end
def add_timestamp_column project_id:, instance_id:, database_id:
# [START spanner_add_timestamp_column]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"
database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin
db_path = database_admin_client.database_path project: project_id,
instance: instance_id,
database: database_id
job = database_admin_client.update_database_ddl database: db_path,
statements: [
"ALTER TABLE Albums ADD COLUMN LastUpdateTime TIMESTAMP
OPTIONS (allow_commit_timestamp=true)"
]
puts "Waiting for database update to complete"
job.wait_until_done!
puts "Added the LastUpdateTime as a commit timestamp column in Albums table"
# [END spanner_add_timestamp_column]
end
def add_numeric_column project_id:, instance_id:, database_id:
# [START spanner_add_numeric_column]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"
database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin
db_path = database_admin_client.database_path project: project_id,
instance: instance_id,
database: database_id
job = database_admin_client.update_database_ddl database: db_path,
statements: [
"ALTER TABLE Venues ADD COLUMN Revenue NUMERIC"
]
puts "Waiting for database update to complete"
job.wait_until_done!
puts "Added the Revenue as a numeric column in Venues table"
# [END spanner_add_numeric_column]
end
def write_struct_data project_id:, instance_id:, database_id:
# [START spanner_write_data_for_struct_queries]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
client.commit do |c|
c.insert "Singers", [
{ SingerId: 6, FirstName: "Elena", LastName: "Campbell" },
{ SingerId: 7, FirstName: "Gabriel", LastName: "Wright" },
{ SingerId: 8, FirstName: "Benjamin", LastName: "Martinez" },
{ SingerId: 9, FirstName: "Hannah", LastName: "Harris" }
]
end
puts "Inserted Data for Struct queries"
# [END spanner_write_data_for_struct_queries]
end
def query_with_struct project_id:, instance_id:, database_id:
# [START spanner_create_struct_with_data]
name_struct = { FirstName: "Elena", LastName: "Campbell" }
# [END spanner_create_struct_with_data]
# [START spanner_query_data_with_struct]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
client.execute(
"SELECT SingerId FROM Singers WHERE " +
"(FirstName, LastName) = @name",
params: { name: name_struct }
).rows.each do |row|
puts row[:SingerId]
end
# [END spanner_query_data_with_struct]
end
def query_with_array_of_struct project_id:, instance_id:, database_id:
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
# [START spanner_create_user_defined_struct]
name_type = client.fields FirstName: :STRING, LastName: :STRING
# [END spanner_create_user_defined_struct]
# [START spanner_create_array_of_struct_with_data]
band_members = [name_type.struct(["Elena", "Campbell"]),
name_type.struct(["Gabriel", "Wright"]),
name_type.struct(["Benjamin", "Martinez"])]
# [END spanner_create_array_of_struct_with_data]
# [START spanner_query_data_with_array_of_struct]
client.execute(
"SELECT SingerId FROM Singers WHERE " +
"STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) IN UNNEST(@names)",
params: { names: band_members }
).rows.each do |row|
puts row[:SingerId]
end
# [END spanner_query_data_with_array_of_struct]
end
def query_struct_field project_id:, instance_id:, database_id:
# [START spanner_field_access_on_struct_parameters]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
name_struct = { FirstName: "Elena", LastName: "Campbell" }
client.execute(
"SELECT SingerId FROM Singers WHERE FirstName = @name.FirstName",
params: { name: name_struct }
).rows.each do |row|
puts row[:SingerId]
end
# [END spanner_field_access_on_struct_parameters]
end
def query_nested_struct_field project_id:, instance_id:, database_id:
# [START spanner_field_access_on_nested_struct_parameters]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
name_type = client.fields FirstName: :STRING, LastName: :STRING
song_info_struct = {
SongName: "Imagination",
ArtistNames: [name_type.struct(["Elena", "Campbell"]), name_type.struct(["Hannah", "Harris"])]
}
client.execute(
"SELECT SingerId, @song_info.SongName " \
"FROM Singers WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) " \
"IN UNNEST(@song_info.ArtistNames)",
params: { song_info: song_info_struct }
).rows.each do |row|
puts (row[:SingerId]), (row[:SongName])
end
# [END spanner_field_access_on_nested_struct_parameters]
end
def update_data project_id:, instance_id:, database_id:
# [START spanner_update_data]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
client.commit do |c|
c.update "Albums", [
{ SingerId: 1, AlbumId: 1, MarketingBudget: 100_000 },
{ SingerId: 2, AlbumId: 2, MarketingBudget: 500_000 }
]
end
puts "Updated data"
# [END spanner_update_data]
end
def update_data_with_timestamp_column project_id:, instance_id:, database_id:
# [START spanner_update_data_with_timestamp_column]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
commit_timestamp = client.commit_timestamp
client.commit do |c|
c.update "Albums", [
{ SingerId: 1, AlbumId: 1, MarketingBudget: 100_000, LastUpdateTime: commit_timestamp },
{ SingerId: 2, AlbumId: 2, MarketingBudget: 750_000, LastUpdateTime: commit_timestamp }
]
end
puts "Updated data"
# [END spanner_update_data_with_timestamp_column]
end
def update_data_with_numeric_column project_id:, instance_id:, database_id:
# [START spanner_update_data_with_numeric_column]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
client.commit do |c|
c.update "Venues", [
{ VenueId: 4, Revenue: "35000" },
{ VenueId: 19, Revenue: "104500" },
{ VenueId: 42, Revenue: "99999999999999999999999999999.99" }
]
end
puts "Updated data"
# [END spanner_update_data_with_numeric_column]
end
def query_data_with_new_column project_id:, instance_id:, database_id:
# [START spanner_query_data_with_new_column]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
client.execute("SELECT SingerId, AlbumId, MarketingBudget FROM Albums").rows.each do |row|
puts "#{row[:SingerId]} #{row[:AlbumId]} #{row[:MarketingBudget]}"
end
# [END spanner_query_data_with_new_column]
end
def read_write_transaction project_id:, instance_id:, database_id:
# [START spanner_read_write_transaction]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
transfer_amount = 200_000
client.transaction do |transaction|
first_album = transaction.read("Albums", [:MarketingBudget], keys: [[1, 1]]).rows.first
second_album = transaction.read("Albums", [:MarketingBudget], keys: [[2, 2]]).rows.first
raise "The second album does not have enough funds to transfer" if second_album[:MarketingBudget] < transfer_amount
new_first_album_budget = first_album[:MarketingBudget] + transfer_amount
new_second_album_budget = second_album[:MarketingBudget] - transfer_amount
transaction.update "Albums", [
{ SingerId: 1, AlbumId: 1, MarketingBudget: new_first_album_budget },
{ SingerId: 2, AlbumId: 2, MarketingBudget: new_second_album_budget }
]
end
puts "Transaction complete"
# [END spanner_read_write_transaction]
end
def query_data_with_index project_id:, instance_id:, database_id:, start_title: "Ardvark", end_title: "Goo"
# [START spanner_query_data_with_index]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
# start_title = "An album title to start with such as 'Ardvark'"
# end_title = "An album title to end with such as 'Goo'"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
sql_query = "SELECT AlbumId, AlbumTitle, MarketingBudget
FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle}
WHERE AlbumTitle >= @start_title AND AlbumTitle < @end_title"
params = { start_title: start_title, end_title: end_title }
param_types = { start_title: :STRING, end_title: :STRING }
client.execute(sql_query, params: params, types: param_types).rows.each do |row|
puts "#{row[:AlbumId]} #{row[:AlbumTitle]} #{row[:MarketingBudget]}"
end
# [END spanner_query_data_with_index]
end
def read_data_with_index project_id:, instance_id:, database_id:
# [START spanner_read_data_with_index]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
result = client.read "Albums", [:AlbumId, :AlbumTitle],
index: "AlbumsByAlbumTitle"
result.rows.each do |row|
puts "#{row[:AlbumId]} #{row[:AlbumTitle]}"
end
# [END spanner_read_data_with_index]
end
def read_data_with_storing_index project_id:, instance_id:, database_id:
# [START spanner_read_data_with_storing_index]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
result = client.read "Albums", [:AlbumId, :AlbumTitle, :MarketingBudget],
index: "AlbumsByAlbumTitle2"
result.rows.each do |row|
puts "#{row[:AlbumId]} #{row[:AlbumTitle]} #{row[:MarketingBudget]}"
end
# [END spanner_read_data_with_storing_index]
end
def read_only_transaction project_id:, instance_id:, database_id:
# [START spanner_read_only_transaction]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
client.snapshot do |snapshot|
snapshot.execute("SELECT SingerId, AlbumId, AlbumTitle FROM Albums").rows.each do |row|
puts "#{row[:AlbumId]} #{row[:AlbumTitle]} #{row[:SingerId]}"
end
# Even if changes occur in-between the reads, the transaction ensures that
# both return the same data.
snapshot.read("Albums", [:AlbumId, :AlbumTitle, :SingerId]).rows.each do |row|
puts "#{row[:AlbumId]} #{row[:AlbumTitle]} #{row[:SingerId]}"
end
end
# [END spanner_read_only_transaction]
end
def spanner_batch_client project_id:, instance_id:, database_id:
# [START spanner_batch_client]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
# Prepare a thread pool with number of processors
processor_count = Concurrent.processor_count
thread_pool = Concurrent::FixedThreadPool.new processor_count
# Prepare AtomicFixnum to count total records using multiple threads
total_records = Concurrent::AtomicFixnum.new
# Create a new Spanner batch client
spanner = Google::Cloud::Spanner.new project: project_id
batch_client = spanner.batch_client instance_id, database_id
# Get a strong timestamp bound batch_snapshot
batch_snapshot = batch_client.batch_snapshot strong: true
# Get partitions for specified query
# data_boost_enabled option is an optional parameter which can be used for partition read
# and query to execute the request via spanner independent compute resources.
partitions = batch_snapshot.partition_query "SELECT SingerId, FirstName, LastName FROM Singers", data_boost_enabled: true
total_partitions = partitions.size
# Enqueue a new thread pool job
partitions.each_with_index do |partition, _partition_index|
thread_pool.post do
# Increment total_records per new row
batch_snapshot.execute_partition(partition).rows.each do |_row|
total_records.increment
end
end
end
# Wait for queued jobs to complete
thread_pool.shutdown
thread_pool.wait_for_termination
# Close the client connection and release resources.
batch_snapshot.close
# Collect statistics for batch query
average_records_per_partition = 0.0
if total_partitions != 0
average_records_per_partition = total_records.value / total_partitions.to_f
end
puts "Total Partitions: #{total_partitions}"
puts "Total Records: #{total_records.value}"
puts "Average records per Partition: #{average_records_per_partition}"
# [END spanner_batch_client]
end
def insert_using_dml project_id:, instance_id:, database_id:
# [START spanner_dml_standard_insert]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
row_count = 0
client.transaction do |transaction|
row_count = transaction.execute_update(
"INSERT INTO Singers (SingerId, FirstName, LastName) VALUES (10, 'Virginia', 'Watson')"
)
end
puts "#{row_count} record inserted."
# [END spanner_dml_standard_insert]
end
def update_using_dml project_id:, instance_id:, database_id:
# [START spanner_dml_standard_update]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
row_count = 0
client.transaction do |transaction|
row_count = transaction.execute_update(
"UPDATE Albums
SET MarketingBudget = MarketingBudget * 2
WHERE SingerId = 1 and AlbumId = 1"
)
end
puts "#{row_count} record updated."
# [END spanner_dml_standard_update]
end
def delete_using_dml project_id:, instance_id:, database_id:
# [START spanner_dml_standard_delete]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
row_count = 0
client.transaction do |transaction|
row_count = transaction.execute_update(
"DELETE FROM Singers WHERE FirstName = 'Alice'"
)
end
puts "#{row_count} record deleted."
# [END spanner_dml_standard_delete]
end
def update_using_dml_with_timestamp project_id:, instance_id:, database_id:
# [START spanner_dml_standard_update_with_timestamp]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
row_count = 0
client.transaction do |transaction|
row_count = transaction.execute_update(
"UPDATE Albums SET LastUpdateTime = PENDING_COMMIT_TIMESTAMP() WHERE SingerId = 1"
)
end
puts "#{row_count} records updated."
# [END spanner_dml_standard_update_with_timestamp]
end
def write_and_read_using_dml project_id:, instance_id:, database_id:
# [START spanner_dml_write_then_read]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
row_count = 0
client.transaction do |transaction|
row_count = transaction.execute_update(
"INSERT INTO Singers (SingerId, FirstName, LastName) VALUES (11, 'Timothy', 'Campbell')"
)
puts "#{row_count} record updated."
transaction.execute("SELECT FirstName, LastName FROM Singers WHERE SingerId = 11").rows.each do |row|
puts "#{row[:FirstName]} #{row[:LastName]}"
end
end
# [END spanner_dml_write_then_read]
end
def update_using_dml_with_struct project_id:, instance_id:, database_id:
# [START spanner_dml_structs]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
row_count = 0
name_struct = { FirstName: "Timothy", LastName: "Campbell" }
client.transaction do |transaction|
row_count = transaction.execute_update(
"UPDATE Singers SET LastName = 'Grant'
WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) = @name",
params: { name: name_struct }
)
end
puts "#{row_count} record updated."
# [END spanner_dml_structs]
end
def write_using_dml project_id:, instance_id:, database_id:
# [START spanner_dml_getting_started_insert]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
row_count = 0
client.transaction do |transaction|
row_count = transaction.execute_update(
"INSERT INTO Singers (SingerId, FirstName, LastName) VALUES
(12, 'Melissa', 'Garcia'),
(13, 'Russell', 'Morales'),
(14, 'Jacqueline', 'Long'),
(15, 'Dylan', 'Shaw'),
(16, 'Billie', 'Eillish'),
(17, 'Judy', 'Garland'),
(18, 'Taylor', 'Swift'),
(19, 'Miley', 'Cyrus'),
(20, 'Michael', 'Jackson'),
(21, 'Ariana', 'Grande'),
(22, 'Elvis', 'Presley'),
(23, 'Kanye', 'West'),
(24, 'Lady', 'Gaga'),
(25, 'Nick', 'Jonas')"
)
end
puts "#{row_count} records inserted."
# [END spanner_dml_getting_started_insert]
end
def query_with_parameter project_id:, instance_id:, database_id:
# [START spanner_query_with_parameter]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
sql_query = "SELECT SingerId, FirstName, LastName
FROM Singers
WHERE LastName = @lastName"
params = { lastName: "Garcia" }
param_types = { lastName: :STRING }
client.execute(sql_query, params: params, types: param_types).rows.each do |row|
puts "#{row[:SingerId]} #{row[:FirstName]} #{row[:LastName]}"
end
# [END spanner_query_with_parameter]
end
def query_with_numeric_parameter project_id:, instance_id:, database_id:
# [START spanner_query_with_numeric_parameter]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
require "bigdecimal"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
sql_query = "SELECT VenueId, Revenue FROM Venues WHERE Revenue < @revenue"
params = { revenue: BigDecimal("100000") }
param_types = { revenue: :NUMERIC }
client.execute(sql_query, params: params, types: param_types).rows.each do |row|
puts "#{row[:VenueId]} #{row[:Revenue]}"
end
# [END spanner_query_with_numeric_parameter]
end
def write_with_transaction_using_dml project_id:, instance_id:, database_id:
# [START spanner_dml_getting_started_update]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
transfer_amount = 200_000
client.transaction do |transaction|
first_album = transaction.execute(
"SELECT MarketingBudget from Albums
WHERE SingerId = 1 and AlbumId = 1"
).rows.first
second_album = transaction.execute(
"SELECT MarketingBudget from Albums
WHERE SingerId = 2 and AlbumId = 2"
).rows.first
raise "The second album does not have enough funds to transfer" if second_album[:MarketingBudget] < transfer_amount
new_first_album_budget = first_album[:MarketingBudget] + transfer_amount
new_second_album_budget = second_album[:MarketingBudget] - transfer_amount
transaction.execute_update(
"UPDATE Albums SET MarketingBudget = @albumBudget WHERE SingerId = 1 and AlbumId = 1",
params: { albumBudget: new_first_album_budget }
)
transaction.execute_update(
"UPDATE Albums SET MarketingBudget = @albumBudget WHERE SingerId = 2 and AlbumId = 2",
params: { albumBudget: new_second_album_budget }
)
end
puts "Transaction complete"
# [END spanner_dml_getting_started_update]
end
def update_using_partitioned_dml project_id:, instance_id:, database_id:
# [START spanner_dml_partitioned_update]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
row_count = client.execute_partition_update(
"UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1"
)
puts "#{row_count} records updated."
# [END spanner_dml_partitioned_update]
end
def delete_using_partitioned_dml project_id:, instance_id:, database_id:
# [START spanner_dml_partitioned_delete]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
row_count = client.execute_partition_update(
"DELETE FROM Singers WHERE SingerId > 10"
)
puts "#{row_count} records deleted."
# [END spanner_dml_partitioned_delete]
end
def update_using_batch_dml project_id:, instance_id:, database_id:
# [START spanner_dml_batch_update]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
row_counts = nil
client.transaction do |transaction|
row_counts = transaction.batch_update do |b|
b.batch_update(
"INSERT INTO Albums " \
"(SingerId, AlbumId, AlbumTitle, MarketingBudget) " \
"VALUES (1, 3, 'Test Album Title', 10000)"
)
b.batch_update(
"UPDATE Albums " \
"SET MarketingBudget = MarketingBudget * 2 " \
"WHERE SingerId = 1 and AlbumId = 3"
)
end
end
statement_count = row_counts.count
puts "Executed #{statement_count} SQL statements using Batch DML."
# [END spanner_dml_batch_update]
end
def create_table_with_datatypes project_id:, instance_id:, database_id:
# [START spanner_create_table_with_datatypes]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.database instance_id, database_id
job = client.update statements: [
"CREATE TABLE Venues (
VenueId INT64 NOT NULL,
VenueName STRING(100),
VenueInfo BYTES(MAX),
Capacity INT64,
AvailableDates ARRAY<DATE>,
LastContactDate DATE,
OutdoorVenue BOOL,
PopularityScore FLOAT64,
LastUpdateTime TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true)
) PRIMARY KEY (VenueId)"
]
puts "Waiting for update database operation to complete"
job.wait_until_done!
puts "Created table Venues in #{database_id}"
# [END spanner_create_table_with_datatypes]
end
def write_datatypes_data project_id:, instance_id:, database_id:
# [START spanner_insert_datatypes_data]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
# Get commit_timestamp
commit_timestamp = client.commit_timestamp
client.commit do |c|
c.insert "Venues", [
{ VenueId: 4, VenueName: "Venue 4", VenueInfo: StringIO.new("Hello World 1"),
Capacity: 1_800, AvailableDates: ["2020-12-01", "2020-12-02", "2020-12-03"],
LastContactDate: "2018-09-02", OutdoorVenue: false, PopularityScore: 0.85543,
LastUpdateTime: commit_timestamp },
{ VenueId: 19, VenueName: "Venue 19", VenueInfo: StringIO.new("Hello World 2"),
Capacity: 6_300, AvailableDates: ["2020-11-01", "2020-11-05", "2020-11-15"],
LastContactDate: "2019-01-15", OutdoorVenue: true, PopularityScore: 0.98716,
LastUpdateTime: commit_timestamp },
{ VenueId: 42, VenueName: "Venue 42", VenueInfo: StringIO.new("Hello World 3"),
Capacity: 3_000, AvailableDates: ["2020-10-01", "2020-10-07"],
LastContactDate: "2018-10-01", OutdoorVenue: false, PopularityScore: 0.72598,
LastUpdateTime: commit_timestamp }
]
end
puts "Inserted data"
# [END spanner_insert_datatypes_data]
end
def query_with_array project_id:, instance_id:, database_id:
# [START spanner_query_with_array_parameter]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
sql_query = "SELECT VenueId, VenueName, AvailableDate FROM Venues v,
UNNEST(v.AvailableDates) as AvailableDate
WHERE AvailableDate in UNNEST(@available_dates)"
params = { available_dates: ["2020-10-01", "2020-11-01"] }
param_types = { available_dates: [:DATE] }
client.execute(sql_query, params: params, types: param_types).rows.each do |row|
puts "#{row[:VenueId]} #{row[:VenueName]} #{row[:AvailableDate]}"
end
# [END spanner_query_with_array_parameter]
end
def query_with_bool project_id:, instance_id:, database_id:
# [START spanner_query_with_bool_parameter]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
sql_query = "SELECT VenueId, VenueName, OutdoorVenue FROM Venues
WHERE OutdoorVenue = @outdoor_venue"
params = { outdoor_venue: true }
param_types = { outdoor_venue: :BOOL }
client.execute(sql_query, params: params, types: param_types).rows.each do |row|
puts "#{row[:VenueId]} #{row[:VenueName]} #{row[:OutdoorVenue]}"
end
# [END spanner_query_with_bool_parameter]
end
def query_with_bytes project_id:, instance_id:, database_id:
# [START spanner_query_with_bytes_parameter]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
example_bytes = StringIO.new "Hello World 1"
sql_query = "SELECT VenueId, VenueName FROM Venues
WHERE VenueInfo = @venue_info"
params = { venue_info: example_bytes }
param_types = { venue_info: :BYTES }
client.execute(sql_query, params: params, types: param_types).rows.each do |row|
puts "#{row[:VenueId]} #{row[:VenueName]}"
end
# [END spanner_query_with_bytes_parameter]
end
def query_with_date project_id:, instance_id:, database_id:
# [START spanner_query_with_date_parameter]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
sql_query = "SELECT VenueId, VenueName, LastContactDate FROM Venues
WHERE LastContactDate < @last_contact_date"
params = { last_contact_date: "2019-01-01" }
param_types = { last_contact_date: :DATE }
client.execute(sql_query, params: params, types: param_types).rows.each do |row|
puts "#{row[:VenueId]} #{row[:VenueName]} #{row[:LastContactDate]}"
end
# [END spanner_query_with_date_parameter]
end
def query_with_float project_id:, instance_id:, database_id:
# [START spanner_query_with_float_parameter]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
sql_query = "SELECT VenueId, VenueName, PopularityScore FROM Venues
WHERE PopularityScore > @popularity_score"
params = { popularity_score: 0.8 }
param_types = { popularity_score: :FLOAT64 }
client.execute(sql_query, params: params, types: param_types).rows.each do |row|
puts "#{row[:VenueId]} #{row[:VenueName]} #{row[:PopularityScore]}"
end
# [END spanner_query_with_float_parameter]
end
def query_with_int project_id:, instance_id:, database_id:
# [START spanner_query_with_int_parameter]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
sql_query = "SELECT VenueId, VenueName, Capacity FROM Venues
WHERE Capacity >= @capacity"
params = { capacity: 3_000 }
param_types = { capacity: :INT64 }
client.execute(sql_query, params: params, types: param_types).rows.each do |row|
puts "#{row[:VenueId]} #{row[:VenueName]} #{row[:Capacity]}"
end
# [END spanner_query_with_int_parameter]
end
def query_with_string project_id:, instance_id:, database_id:
# [START spanner_query_with_string_parameter]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
sql_query = "SELECT VenueId, VenueName FROM Venues
WHERE VenueName = @venue_name"
params = { venue_name: "Venue 42" }
param_types = { venue_name: :STRING }
client.execute(sql_query, params: params, types: param_types).rows.each do |row|
puts "#{row[:VenueId]} #{row[:VenueName]}"
end
# [END spanner_query_with_string_parameter]
end
def query_with_timestamp project_id:, instance_id:, database_id:
# [START spanner_query_with_timestamp_parameter]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
example_timestamp = DateTime.now
sql_query = "SELECT VenueId, VenueName, LastUpdateTime FROM Venues
WHERE LastUpdateTime < @last_update_time"
params = { last_update_time: example_timestamp }
param_types = { last_update_time: :TIMESTAMP }
client.execute(sql_query, params: params, types: param_types).rows.each do |row|
puts "#{row[:VenueId]} #{row[:VenueName]} #{row[:LastUpdateTime]}"
end
# [END spanner_query_with_timestamp_parameter]
end
def query_with_query_options project_id:, instance_id:, database_id:
# [START spanner_query_with_query_options]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
sql_query = "SELECT VenueId, VenueName, LastUpdateTime FROM Venues"
query_options = {
optimizer_version: "1",
# The list of available statistics packagebs can be
# found by querying the "INFORMATION_SCHEMA.SPANNER_STATISTICS"
# table.
optimizer_statistics_package: "latest"
}
client.execute(sql_query, query_options: query_options).rows.each do |row|
puts "#{row[:VenueId]} #{row[:VenueName]} #{row[:LastUpdateTime]}"
end
# [END spanner_query_with_query_options]
end
def create_client_with_query_options project_id:, instance_id:, database_id:
# [START spanner_create_client_with_query_options]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
query_options = {
optimizer_version: "1",
# The list of available statistics packages can be
# found by querying the "INFORMATION_SCHEMA.SPANNER_STATISTICS"
# table.
optimizer_statistics_package: "latest"
}
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id, query_options: query_options
sql_query = "SELECT VenueId, VenueName, LastUpdateTime FROM Venues"
client.execute(sql_query).rows.each do |row|
puts "#{row[:VenueId]} #{row[:VenueName]} #{row[:LastUpdateTime]}"
end
# [END spanner_create_client_with_query_options]
end
def write_read_bool_array project_id:, instance_id:, database_id:
# [START spanner_write_read_bool_array]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"
require "securerandom"
database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin
db_path = database_admin_client.database_path project: project_id,
instance: instance_id,
database: database_id
job = database_admin_client.update_database_ddl database: db_path, statements: [
"CREATE TABLE Boxes (
BoxId STRING(36) NOT NULL,
Heights ARRAY<INT64>,
Weights ARRAY<FLOAT64>,
ErrorChecks ARRAY<BOOL>
) PRIMARY KEY (BoxId)"
]
job.wait_until_done!
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
box_id = SecureRandom.uuid
client.insert "Boxes", [{ BoxId: box_id, ErrorChecks: [true, false, true] }]
results = client.read "Boxes", [:BoxId, :ErrorChecks], keys: box_id
results.rows.each do |row|
puts row["ErrorChecks"]
end
# [END spanner_write_read_bool_array]
end
def write_read_empty_int64_array project_id:, instance_id:, database_id:
# [START spanner_write_read_empty_int64_array]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"
require "securerandom"
database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin
db_path = database_admin_client.database_path project: project_id,
instance: instance_id,
database: database_id
job = database_admin_client.update_database_ddl database: db_path, statements: [
"CREATE TABLE Boxes (
BoxId STRING(36) NOT NULL,
Heights ARRAY<INT64>,
Weights ARRAY<FLOAT64>,
ErrorChecks ARRAY<BOOL>
) PRIMARY KEY (BoxId)"
]
job.wait_until_done!
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
box_id = SecureRandom.uuid
client.insert "Boxes", [{ BoxId: box_id, Heights: [] }]
results = client.read "Boxes", [:BoxId, :Heights], keys: box_id
results.rows.each do |row|
puts row["Heights"].empty?
end
# [END spanner_write_read_empty_int64_array]
end
def write_read_null_int64_array project_id:, instance_id:, database_id:
# [START spanner_write_read_null_int64_array]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"
require "securerandom"
database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin
db_path = database_admin_client.database_path project: project_id,
instance: instance_id,
database: database_id
job = database_admin_client.update_database_ddl database: db_path, statements: [
"CREATE TABLE Boxes (
BoxId STRING(36) NOT NULL,
Heights ARRAY<INT64>,
Weights ARRAY<FLOAT64>,
ErrorChecks ARRAY<BOOL>
) PRIMARY KEY (BoxId)"
]
job.wait_until_done!
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
box_id = SecureRandom.uuid
client.insert "Boxes", [{ BoxId: box_id, Heights: [nil, nil, nil] }]
results = client.read "Boxes", [:BoxId, :Heights], keys: box_id
results.rows.each do |row|
row["Heights"].each { |height| puts height.nil? }
end
# [END spanner_write_read_null_int64_array]
end
def write_read_int64_array project_id:, instance_id:, database_id:
# [START spanner_write_read_int64_array]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"
require "securerandom"
database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin
db_path = database_admin_client.database_path project: project_id,
instance: instance_id,
database: database_id
job = database_admin_client.update_database_ddl database: db_path, statements: [
"CREATE TABLE Boxes (
BoxId STRING(36) NOT NULL,
Heights ARRAY<INT64>,
Weights ARRAY<FLOAT64>,
ErrorChecks ARRAY<BOOL>
) PRIMARY KEY (BoxId)"
]
job.wait_until_done!
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
box_id = SecureRandom.uuid
client.insert "Boxes", [{ BoxId: box_id, Heights: [10, 11, 12] }]
results = client.read "Boxes", [:BoxId, :Heights], keys: box_id
results.rows.each do |row|
puts row["Heights"]
end
# [END spanner_write_read_int64_array]
end
def write_read_empty_float64_array project_id:, instance_id:, database_id:
# [START spanner_write_read_empty_float64_array]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"
require "securerandom"
database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin
db_path = database_admin_client.database_path project: project_id,
instance: instance_id,
database: database_id
job = database_admin_client.update_database_ddl database: db_path, statements: [
"CREATE TABLE Boxes (
BoxId STRING(36) NOT NULL,
Heights ARRAY<INT64>,
Weights ARRAY<FLOAT64>,
ErrorChecks ARRAY<BOOL>
) PRIMARY KEY (BoxId)"
]
job.wait_until_done!
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
box_id = SecureRandom.uuid
client.insert "Boxes", [{ BoxId: box_id, Weights: [] }]
results = client.read "Boxes", [:BoxId, :Weights], keys: box_id
results.rows.each do |row|
puts row["Weights"].empty?
end
# [END spanner_write_read_empty_float64_array]
end
def write_read_null_float64_array project_id:, instance_id:, database_id:
# [START spanner_write_read_null_float64_array]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"
require "securerandom"
database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin
db_path = database_admin_client.database_path project: project_id,
instance: instance_id,
database: database_id
job = database_admin_client.update_database_ddl database: db_path, statements: [
"CREATE TABLE Boxes (
BoxId STRING(36) NOT NULL,
Heights ARRAY<INT64>,
Weights ARRAY<FLOAT64>,
ErrorChecks ARRAY<BOOL>
) PRIMARY KEY (BoxId)"
]
job.wait_until_done!
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
box_id = SecureRandom.uuid
client.insert "Boxes", [{ BoxId: box_id, Weights: [nil, nil, nil] }]
results = client.read "Boxes", [:BoxId, :Weights], keys: box_id
results.rows.each do |row|
row["Weights"].each { |weight| puts weight.nil? }
end
# [END spanner_write_read_null_float64_array]
end
def write_read_float64_array project_id:, instance_id:, database_id:
# [START spanner_write_read_float64_array]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"
require "securerandom"
database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin
db_path = database_admin_client.database_path project: project_id,
instance: instance_id,
database: database_id
job = database_admin_client.update_database_ddl database: db_path, statements: [
"CREATE TABLE Boxes (
BoxId STRING(36) NOT NULL,
Heights ARRAY<INT64>,
Weights ARRAY<FLOAT64>,
ErrorChecks ARRAY<BOOL>
) PRIMARY KEY (BoxId)"
]
job.wait_until_done!
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
box_id = SecureRandom.uuid
client.insert "Boxes", [{ BoxId: box_id, Weights: [10.001, 11.1212, 104.4123101] }]
results = client.read "Boxes", [:BoxId, :Weights], keys: box_id
results.rows.each do |row|
puts row["Weights"]
end
# [END spanner_write_read_float64_array]
end
def create_backup project_id:, instance_id:, database_id:, backup_id:, version_time:
# [START spanner_create_backup]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
# backup_id = "Your Spanner backup ID"
# version_time = Time.now - 60 * 60 * 24 # 1 day ago
require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"
database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin
instance_path = database_admin_client.instance_path project: project_id, instance: instance_id
db_path = database_admin_client.database_path project: project_id,
instance: instance_id,
database: database_id
backup_path = database_admin_client.backup_path project: project_id,
instance: instance_id,
backup: backup_id
expire_time = Time.now + (14 * 24 * 3600) # 14 days from now
job = database_admin_client.create_backup parent: instance_path,
backup_id: backup_id,
backup: {
database: db_path,
expire_time: expire_time,
version_time: version_time
}
puts "Backup operation in progress"
job.wait_until_done!
backup = database_admin_client.get_backup name: backup_path
puts "Backup #{backup_id} of size #{backup.size_bytes} bytes was created at #{backup.create_time} for version of database at #{backup.version_time}"
# [END spanner_create_backup]
end
def create_backup_with_encryption_key project_id:, instance_id:, database_id:, backup_id:, kms_key_name:
# [START spanner_create_backup_with_encryption_key]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
# backup_id = "Your Spanner backup ID"
# kms_key_name = "Your backup encryption database KMS key"
require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"
database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin
instance_path = database_admin_client.instance_path project: project_id, instance: instance_id
db_path = database_admin_client.database_path project: project_id,
instance: instance_id,
database: database_id
backup_path = database_admin_client.backup_path project: project_id,
instance: instance_id,
backup: backup_id
expire_time = Time.now + (14 * 24 * 3600) # 14 days from now
encryption_config = {
encryption_type: :CUSTOMER_MANAGED_ENCRYPTION,
kms_key_name: kms_key_name
}
job = database_admin_client.create_backup parent: instance_path,
backup_id: backup_id,
backup: {
database: db_path,
expire_time: expire_time
},
encryption_config: encryption_config
puts "Backup operation in progress"
job.wait_until_done!
backup = database_admin_client.get_backup name: backup_path
puts "Backup #{backup_id} of size #{backup.size_bytes} bytes was created at #{backup.create_time} using encryption key #{kms_key_name}"
# [END spanner_create_backup_with_encryption_key]
end
def create_backup_with_multiple_kms_keys(
project_id:, instance_id:, database_id:, backup_id:, kms_key_names:
)
# [START spanner_create_backup_with_MR_CMEK]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
# backup_id = "Your Spanner backup ID"
# kms_key_names = ["key1", "key2", "key3"]
require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"
database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin
instance_path = database_admin_client.instance_path(
project: project_id, instance: instance_id
)
db_path = database_admin_client.database_path project: project_id,
instance: instance_id,
database: database_id
backup_path = database_admin_client.backup_path project: project_id,
instance: instance_id,
backup: backup_id
expire_time = Time.now + (14 * 24 * 3600) # 14 days from now
encryption_config = {
encryption_type: :CUSTOMER_MANAGED_ENCRYPTION,
kms_key_names: kms_key_names
}
job = database_admin_client.create_backup parent: instance_path,
backup_id: backup_id,
backup: {
database: db_path,
expire_time: expire_time
},
encryption_config: encryption_config
puts "Backup operation in progress"
job.wait_until_done!
backup = database_admin_client.get_backup name: backup_path
puts "Backup #{backup_id} of size #{backup.size_bytes} bytes was created " \
"at #{backup.create_time} using encryption key #{kms_key_names}"
# [END spanner_create_backup_with_MR_CMEK]
end
def restore_backup project_id:, instance_id:, database_id:, backup_id:
# [START spanner_restore_backup]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID of where to restore"
# backup_id = "Your Spanner backup ID"
require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"
database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin
instance_path = database_admin_client.instance_path project: project_id, instance: instance_id
db_path = database_admin_client.database_path project: project_id,
instance: instance_id,
database: database_id
backup_path = database_admin_client.backup_path project: project_id,
instance: instance_id,
backup: backup_id
job = database_admin_client.restore_database parent: instance_path,
database_id: database_id,
backup: backup_path
puts "Waiting for restore backup operation to complete"
job.wait_until_done!
database = database_admin_client.get_database name: db_path
restore_info = database.restore_info
puts "Database #{restore_info.backup_info.source_database} was restored to #{database_id} from backup #{restore_info.backup_info.backup} with version time #{restore_info.backup_info.version_time}"
# [END spanner_restore_backup]
end
def restore_database_with_encryption_key project_id:, instance_id:, database_id:, backup_id:, kms_key_name:
# [START spanner_restore_backup_with_encryption_key]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID of where to restore"
# backup_id = "Your Spanner backup ID"
# kms_key_name = "Your backup encryption database KMS key"
require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"
database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin
instance_path = database_admin_client.instance_path project: project_id, instance: instance_id
db_path = database_admin_client.database_path project: project_id,
instance: instance_id,
database: database_id
backup_path = database_admin_client.backup_path project: project_id,
instance: instance_id,
backup: backup_id
encryption_config = {
encryption_type: :CUSTOMER_MANAGED_ENCRYPTION,
kms_key_name: kms_key_name
}
job = database_admin_client.restore_database parent: instance_path,
database_id: database_id,
backup: backup_path,
encryption_config: encryption_config
puts "Waiting for restore backup operation to complete"
job.wait_until_done!
database = database_admin_client.get_database name: db_path
restore_info = database.restore_info
puts "Database #{restore_info.backup_info.source_database} was restored to #{database_id} from backup #{restore_info.backup_info.backup} using encryption key #{database.encryption_config.kms_key_name}"
# [END spanner_restore_backup_with_encryption_key]
end
def restore_database_with_multiple_kms_keys(
project_id:, instance_id:, database_id:, backup_id:, kms_key_names:
)
# [START spanner_restore_backup_with_MR_CMEK]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID of where to restore"
# backup_id = "Your Spanner backup ID"
# kms_key_names = ["key1", "key2", "key3"]
require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"
database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin
instance_path = database_admin_client.instance_path(
project: project_id, instance: instance_id
)
db_path = database_admin_client.database_path project: project_id,
instance: instance_id,
database: database_id
backup_path = database_admin_client.backup_path project: project_id,
instance: instance_id,
backup: backup_id
encryption_config = {
encryption_type: :CUSTOMER_MANAGED_ENCRYPTION,
kms_key_names: kms_key_names
}
job = database_admin_client.restore_database(
parent: instance_path,
database_id: database_id,
backup: backup_path,
encryption_config: encryption_config
)
puts "Waiting for restore backup operation to complete"
job.wait_until_done!
database = database_admin_client.get_database name: db_path
restore_info = database.restore_info
puts "Database #{restore_info.backup_info.source_database} was restored " \
"to #{database_id} from backup #{restore_info.backup_info.backup} " \
"using encryption key #{database.encryption_config.kms_key_names}"
# [END spanner_restore_backup_with_MR_CMEK]
end
def create_backup_cancel project_id:, instance_id:, database_id:, backup_id:
# [START spanner_cancel_backup_create]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
# backup_id = "Your Spanner backup ID"
require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"
database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin
instance_path = database_admin_client.instance_path project: project_id, instance: instance_id
db_path = database_admin_client.database_path project: project_id,
instance: instance_id,
database: database_id
backup_path = database_admin_client.backup_path project: project_id,
instance: instance_id,
backup: backup_id
expire_time = Time.now + (14 * 24 * 3600) # 14 days from now
job = database_admin_client.create_backup parent: instance_path,
backup_id: backup_id,
backup: {
database: db_path,
expire_time: expire_time
}
puts "Backup operation in progress"
job.cancel
job.wait_until_done!
begin
backup = database_admin_client.get_backup name: backup_path
database_admin_client.delete_backup name: backup_path if backup
rescue StandardError
nil # no cleanup needed when a backup is not created
end
puts "#{backup_id} creation job cancelled"
# [END spanner_cancel_backup_create]
end
def list_backup_operations project_id:, instance_id:, database_id:
# [START spanner_list_backup_operations]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"
database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin
instance_path = database_admin_client.instance_path project: project_id, instance: instance_id
jobs = database_admin_client.list_backup_operations parent: instance_path,
filter: "metadata.@type:type.googleapis.com/google.spanner.admin.database.v1.CreateBackupMetadata"
jobs.each do |job|
if job.error?
puts job.error
else
puts "Backup #{job.results.name} on database #{database_id} is #{job.metadata.progress.progress_percent}% complete"
end
end
# [END spanner_list_backup_operations]
end
def list_copy_backup_operations project_id:, instance_id:, backup_id:
# [START spanner_list_copy_backup_operations]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# backup_id = "You Spanner backup ID"
require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"
database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin
instance_path = database_admin_client.instance_path project: project_id, instance: instance_id
filter = "(metadata.@type:type.googleapis.com/google.spanner.admin.database.v1.CopyBackupMetadata) AND (metadata.source_backup:#{backup_id})"
jobs = database_admin_client.list_backup_operations parent: instance_path,
filter: filter
jobs.each do |job|
if job.error?
puts job.error
else
puts "Backup #{job.results.name} on source backup #{backup_id} is #{job.metadata.progress.progress_percent}% complete"
end
end
# [END spanner_list_copy_backup_operations]
end
def list_database_operations project_id:, instance_id:
# [START spanner_list_database_operations]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"
database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin
instance_path = database_admin_client.instance_path project: project_id, instance: instance_id
jobs = database_admin_client.list_database_operations parent: instance_path,
filter: "metadata.@type:type.googleapis.com/google.spanner.admin.database.v1.OptimizeRestoredDatabaseMetadata"
jobs.each do |job|
if job.error?
puts job.error
elsif job.results
progress_percent = job.metadata.progress.progress_percent
puts "Database #{job.results.name} restored from backup is #{progress_percent}% optimized"
end
end
puts "List database operations with optimized database filter found #{jobs.count} jobs."
# [END spanner_list_database_operations]
end
def list_backups project_id:, instance_id:, backup_id:, database_id:
# [START spanner_list_backups]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# backup_id = "Your Spanner database backup ID"
# database_id = "Your Spanner databaseID"
require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"
database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin
instance_path = database_admin_client.instance_path project: project_id, instance: instance_id
puts "All backups"
database_admin_client.list_backups(parent: instance_path).each do |backup|
puts backup.name
end
puts "All backups with backup name containing \"#{backup_id}\":"
database_admin_client.list_backups(parent: instance_path, filter: "name:#{backup_id}").each do |backup|
puts backup.name
end
puts "All backups for databases with a name containing \"#{database_id}\":"
database_admin_client.list_backups(parent: instance_path, filter: "database:#{database_id}").each do |backup|
puts backup.name
end
puts "All backups that expire before a timestamp:"
expire_time = Time.now + (30 * 24 * 3600) # 30 days from now
database_admin_client.list_backups(parent: instance_path, filter: "expire_time < \"#{expire_time.iso8601}\"").each do |backup|
puts backup.name
end
puts "All backups with a size greater than 500 bytes:"
database_admin_client.list_backups(parent: instance_path, filter: "size_bytes >= 500").each do |backup|
puts backup.name
end
puts "All backups that were created after a timestamp that are also ready:"
create_time = Time.now - (24 * 3600) # From 1 day ago
database_admin_client.list_backups(parent: instance_path, filter: "create_time >= \"#{create_time.iso8601}\" AND state:READY").each do |backup|
puts backup.name
end
puts "All backups with pagination:"
list = database_admin_client.list_backups parent: instance_path, page_size: 5
list.each do |backup|
puts backup.name
end
# [END spanner_list_backups]
end
def delete_backup project_id:, instance_id:, backup_id:
# [START spanner_delete_backup]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# backup_id = "Your Spanner backup ID"
require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"
database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin
instance_path = database_admin_client.instance_path project: project_id, instance: instance_id
backup_path = database_admin_client.backup_path project: project_id,
instance: instance_id,
backup: backup_id
database_admin_client.delete_backup name: backup_path
puts "Backup #{backup_id} deleted"
# [END spanner_delete_backup]
end
def update_backup project_id:, instance_id:, backup_id:
# [START spanner_update_backup]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# backup_id = "Your Spanner backup ID"
require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"
database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin
instance_path = database_admin_client.instance_path project: project_id, instance: instance_id
backup_path = database_admin_client.backup_path project: project_id,
instance: instance_id,
backup: backup_id
backup = database_admin_client.get_backup name: backup_path
backup.expire_time = Time.now + (60 * 24 * 3600) # Extending the expiry time by 60 days from now.
database_admin_client.update_backup backup: backup,
update_mask: { paths: ["expire_time"] }
puts "Expiration time updated: #{backup.expire_time}"
# [END spanner_update_backup]
end
def copy_backup project_id:, instance_id:, backup_id:, source_backup_id:
# [START spanner_copy_backup]
# project_id = "Your Google Cloud project ID"
# instance_id = "The ID of the destination instance that will contain the backup copy"
# backup_id = "The ID of the backup copy"
# source_backup = "The source backup to be copied"
require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"
database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin
instance_path = database_admin_client.instance_path project: project_id, instance: instance_id
backup_path = database_admin_client.backup_path project: project_id,
instance: instance_id,
backup: backup_id
source_backup = database_admin_client.backup_path project: project_id,
instance: instance_id,
backup: source_backup_id
expire_time = Time.now + (14 * 24 * 3600) # 14 days from now
job = database_admin_client.copy_backup parent: instance_path,
backup_id: backup_id,
source_backup: source_backup,
expire_time: expire_time
puts "Copy backup operation in progress"
job.wait_until_done!
backup = database_admin_client.get_backup name: backup_path
puts "Backup #{backup_id} of size #{backup.size_bytes} bytes was copied at #{backup.create_time} from #{source_backup} for version #{backup.version_time}"
# [END spanner_copy_backup]
end
def copy_backup_with_multiple_kms_keys(project_id:, instance_id:, backup_id:,
source_backup_id:, kms_key_names:)
# [START spanner_copy_backup_with_MR_CMEK]
# project_id = "Your Google Cloud project ID"
# instance_id = "The ID of the destination instance that will contain the backup copy"
# backup_id = "The ID of the backup copy"
# source_backup = "The source backup to be copied"
# kms_key_names = ["key1", "key2", "key3"]
require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"
database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin
instance_path = database_admin_client.instance_path(
project: project_id, instance: instance_id
)
backup_path = database_admin_client.backup_path project: project_id,
instance: instance_id,
backup: backup_id
source_backup = database_admin_client.backup_path project: project_id,
instance: instance_id,
backup: source_backup_id
expire_time = Time.now + (14 * 24 * 3600) # 14 days from now
encryption_config = {
encryption_type: :CUSTOMER_MANAGED_ENCRYPTION,
kms_key_names: kms_key_names
}
job = database_admin_client.copy_backup parent: instance_path,
backup_id: backup_id,
source_backup: source_backup,
expire_time: expire_time,
encryption_config: encryption_config
puts "Copy backup operation in progress"
job.wait_until_done!
backup = database_admin_client.get_backup name: backup_path
puts "Backup #{backup_id} of size #{backup.size_bytes} bytes was copied at " \
"#{backup.create_time} from #{source_backup} for version " \
"#{backup.version_time} using encryption keys #{kms_key_names}"
# [END spanner_copy_backup_with_MR_CMEK]
end
def set_custom_timeout_and_retry project_id:, instance_id:, database_id:
# [START spanner_set_custom_timeout_and_retry]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
row_count = 0
timeout = 60.0
retry_policy = {
initial_delay: 0.5,
max_delay: 16.0,
multiplier: 1.5,
retry_codes: ["UNAVAILABLE"]
}
call_options = { timeout: timeout, retry_policy: retry_policy }
client.transaction do |transaction|
row_count = transaction.execute_update(
"INSERT INTO Singers (SingerId, FirstName, LastName) VALUES (10, 'Virginia', 'Watson')",
call_options: call_options
)
end
puts "#{row_count} record inserted."
# [END spanner_set_custom_timeout_and_retry]
end
def commit_stats project_id:, instance_id:, database_id:
# [START spanner_get_commit_stats]
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
records = [
{ SingerId: 1, AlbumId: 1, MarketingBudget: 200_000 },
{ SingerId: 2, AlbumId: 2, MarketingBudget: 400_000 }
]
commit_options = { return_commit_stats: true }
resp = client.upsert "Albums", records, commit_options: commit_options
puts "Updated data with #{resp.stats.mutation_count} mutations."
# [END spanner_get_commit_stats]
end
def usage
puts <<~USAGE
Usage: bundle exec ruby spanner_samples.rb [command] [arguments]
Commands:
create_instance <instance_id> Create Instance
create_database <instance_id> <database_id> Create Database
create_database_with_encryption_key <instance_id> <database_id> Create Database with encryption
create_database_with_multiple_kms_keys <instance_id> <database_id> Create Database with multiple KMS keys
create_table_with_timestamp_column <instance_id> <database_id> Create table Performances with commit timestamp column
insert_data <instance_id> <database_id> Insert Data
insert_data_with_timestamp_column <instance_id> <database_id> Inserts data into Performances table containing the commit timestamp column
query_data <instance_id> <database_id> Query Data
read_data <instance_id> <database_id> Read Data
delete_data <instance_id> <database_id> Delete Data
read_stale_data <instance_id> <database_id> Read Stale Data
create_index <instance_id> <database_id> Create Index
create_storing_index <instance_id> <database_id> Create Storing Index
add_column <instance_id> <database_id> Add Column
add_timestamp_column <instance_id> <database_id> Alters existing Albums table, adding a commit timestamp column
add_numeric_column <instance_id> <database_id> Alters existing Venues table, adding a numeric column
update_data <instance_id> <database_id> Update Data
update_data_with_timestamp_column <instance_id> <database_id> Updates two records in the altered table where the commit timestamp column was added
update_data_with_numeric_column <instance_id> <database_id> Updates three records in the altered table where the numeric column was added
query_data_with_new_column <instance_id> <database_id> Query Data with New Column
query_data_with_timestamp_column <instance_id> <database_id> Queries data from altered table where the commit timestamp column was added
write_struct_data <instance_id> <database_id> Inserts sample data that can be used for STRUCT queries
query_with_struct <instance_id> <database_id> Queries data using a STRUCT paramater
query_with_array_of_struct <instance_id> <database_id> Queries data using an array of STRUCT values as parameter
query_struct_field <instance_id> <database_id> Queries data by accessing field from a STRUCT parameter
query_nested_struct_field <instance_id> <database_id> Queries data by accessing field from nested STRUCT parameters
query_data_with_index <instance_id> <database_id> <start_title> <end_title> Query Data with Index
read_write_transaction <instance_id> <database_id> Read-Write Transaction
read_data_with_index <instance_id> <database_id> Read Data with Index
read_data_with_storing_index <instance_id> <database_id> Read Data with Storing Index
read_only_transaction <instance_id> <database_id> Read-Only Transaction
spanner_batch_client <instance_id> <database_id> Use Spanner batch query with a thread pool
insert_using_dml <instance_id> <database_id> Insert Data using a DML statement.
update_using_dml <instance_id> <database_id> Update Data using a DML statement.
delete_using_dml <instance_id> <database_id> Delete Data using a DML statement.
update_using_dml_with_timestamp <instance_id> <database_id> Update the timestamp value of specifc records using a DML statement.
write_and_read_using_dml <instance_id> <database_id> Insert data using a DML statement and then read the inserted data.
update_using_dml_with_struct <instance_id> <database_id> Update data using a DML statement combined with a Spanner struct.
write_using_dml <instance_id> <database_id> Insert multiple records using a DML statement.
query_with_parameter <instance_id> <database_id> Query record inserted using DML with a query parameter.
query_with_numeric_parameter <instance_id> <database_id> Query record inserted using DML with a numeric query parameter.
write_with_transaction_using_dml <instance_id> <database_id> Update data using a DML statement within a read-write transaction.
update_using_partitioned_dml <instance_id> <database_id> Update multiple records using a partitioned DML statement.
delete_using_partitioned_dml <instance_id> <database_id> Delete multiple records using a partitioned DML statement.
update_using_batch_dml <instance_id> <database_id> Updates sample data in the database using Batch DML.
create_table_with_datatypes <instance_id> <database_id> Create table Venues with supported datatype columns.
write_datatypes_data <instance_id> <database_id> Inserts sample data that can be used for datatype queries.
query_with_array <instance_id> <database_id> Queries data using an ARRAY parameter.
query_with_bool <instance_id> <database_id> Queries data using a BOOL parameter.
query_with_bytes <instance_id> <database_id> Queries data using a BYTES parameter.
query_with_date <instance_id> <database_id> Queries data using a DATE parameter.
query_with_float <instance_id> <database_id> Queries data using a FLOAT64 parameter.
query_with_int <instance_id> <database_id> Queries data using a INT64 parameter.
query_with_string <instance_id> <database_id> Queries data using a STRING parameter.
query_with_timestamp <instance_id> <database_id> Queries data using a TIMESTAMP parameter.
query_with_query_options <instance_id> <database_id> Queries data with query options.
create_client_with_query_options <instance_id> <database_id> Create a client with query options.
write_read_bool_array <instance_id> <database_id> Writes and read BOOL array.
write_read_empty_int64_array <instance_id> <database_id> Writes empty INT64 array and read.
write_read_null_int64_array <instance_id> <database_id> Writes nil to INT64 array and read.
write_read_int64_array <instance_id> <database_id> Writes INT64 array and read.
write_read_empty_float64_array <instance_id> <database_id> Writes empty FLOAT64 array and read.
write_read_null_float64_array <instance_id> <database_id> Writes nil to FLOAT64 array and read.
write_read_float64_array <instance_id> <database_id> Writes FLOAT64 array and read.
create_backup <instance_id> <database_id> <backup_id> <version_time> Create a backup.
create_backup_with_encryption_key <instance_id> <database_id> <backup_id> <kms_key_name> Create a backup using encryption key.
create_backup_with_multiple_kms_keys <instance_id> <database_id> <backup_id> "<kms_key_name>,<kms_key_name>" Create a backup using multiple KMS keys.
restore_backup <instance_id> <database_id> <backup_id> Restore a database.
restore_database_with_encryption_key <instance_id> <database_id> <backup_id> <kms_key_name> Restore a database using encryption key.
restore_database_with_multiple_kms_keys <instance_id> <database_id> <backup_id> "<kms_key_name>,<kms_key_name>" Restore a database using multiple KMS keys
create_backup_cancel <instance_id> <database_id> <backup_id> Cancel a backup.
list_backup_operations <instance_id> List backup operations.
list_database_operations <instance_id> List database operations.
list_backups <instance_id> <backup_id> <database_id> List and filter backups.
delete_backup <instance_id> <backup_id> Delete a backup.
update_backup <instance_id> <backup_id> Update the backup.
copy_backup <instance_id> <backup_id> <source_backup> Copies a backup
copy_backup_with_multiple_kms_keys <instance_id> <backup_id> <source_backup> "<kms_key_name>,<kms_key_name>" Copies a backup with multiple KMS keys
set_custom_timeout_and_retry <instance_id> <database_id> Set custom timeout and retry settings.
commit_stats <instance_id> <database_id> Get commit stats.
Environment variables:
GOOGLE_CLOUD_PROJECT must be set to your Google Cloud project ID
USAGE
end
def run_sample arguments
command = arguments.shift
instance_id = arguments.shift
database_id = arguments.shift
project_id = ENV["GOOGLE_CLOUD_PROJECT"]
commands = [
"create_instance", "create_instance_with_processing_units", "create_database", "create_table_with_timestamp_column",
"create_database_with_version_retention_period", "insert_data", "insert_data_with_timestamp_column", "query_data",
"query_data_with_timestamp_column", "read_data", "delete_data", "read_stale_data",
"create_index", "create_storing_index", "add_column", "add_timestamp_column",
"add_numeric_column", "update_data", "query_data_with_new_column",
"update_data_with_timestamp_column", "read_write_transaction",
"query_data_with_index", "read_data_with_index",
"read_data_with_storing_index", "read_only_transaction",
"spanner_batch_client", "write_struct_data", "query_with_struct",
"query_with_array_of_struct", "query_struct_field", "query_nested_struct_field",
"insert_using_dml", "update_using_dml", "delete_using_dml",
"update_using_dml_with_timestamp", "write_and_read_using_dml",
"update_using_dml_with_struct", "write_using_dml", "query_with_parameter",
"write_with_transaction_using_dml", "update_using_partitioned_dml",
"delete_using_partitioned_dml", "update_using_batch_dml",
"create_table_with_datatypes", "write_datatypes_data",
"query_with_array", "query_with_bool", "query_with_bytes", "query_with_date",
"query_with_float", "query_with_int", "query_with_string",
"query_with_timestamp", "query_with_query_options",
"create_client_with_query_options", "write_read_bool_array",
"write_read_empty_int64_array", "write_read_null_int64_array",
"write_read_int64_array", "write_read_empty_float64_array",
"write_read_null_float64_array", "write_read_float64_array",
"create_backup", "restore_backup", "create_backup_cancel",
"list_backup_operations", "list_database_operations", "list_backups",
"delete_backup", "update_backup_expiration_time", "copy_backup",
"set_custom_timeout_and_retry", "query_with_numeric_parameter",
"update_data_with_numeric_column", "commit_stats", "create_database_with_encryption_key",
"create_backup_with_encryption_key", "restore_database_with_encryption_key", "update_backup"
]
if command.eql?("query_data_with_index") && instance_id && database_id && arguments.size >= 2
query_data_with_index project_id: project_id,
instance_id: instance_id,
database_id: database_id,
start_title: arguments.shift,
end_title: arguments.shift
elsif commands.include?(command) && instance_id && database_id
send command, project_id: project_id,
instance_id: instance_id,
database_id: database_id
else
usage
end
end
if $PROGRAM_NAME == __FILE__
run_sample ARGV
end