def __init__(self):
super().__init__()
self._client = Client()
self._instance = self._client.instance(INSTANCE)
self._database = self._instance.database(DATABASE)
self._many_rows = []
self._many_rows2 = []
birth_date = datetime.date(1998, 10, 6)
picture = base64.b64encode("123".encode())
for num in self._many_rows_ids:
self._many_rows.append(
{
"id": num,
"first_name": "Pete",
"last_name": "Allison",
"birth_date": birth_date,
"picture": picture,
}
)
for num in self._many_rows2_ids:
self._many_rows2.append((num, "Pete", "Allison", birth_date, picture))
# initiate a session
with self._database.snapshot():
pass
def setUpModule():
if USE_EMULATOR:
from google.auth.credentials import AnonymousCredentials
emulator_project = os.getenv("GCLOUD_PROJECT", "emulator-test-project")
Config.CLIENT = Client(project=emulator_project,
credentials=AnonymousCredentials())
else:
Config.CLIENT = Client()
retry = RetryErrors(exceptions.ServiceUnavailable)
configs = list(retry(Config.CLIENT.list_instance_configs)())
instances = retry(_list_instances)()
EXISTING_INSTANCES[:] = instances
# Delete test instances that are older than an hour.
cutoff = int(time.time()) - 1 * 60 * 60
for instance_pb in Config.CLIENT.list_instances(
"labels.python-spanner-dbapi-systests:true"):
instance = Instance.from_pb(instance_pb, Config.CLIENT)
if "created" not in instance.labels:
continue
create_time = int(instance.labels["created"])
if create_time > cutoff:
continue
# Instance cannot be deleted while backups exist.
for backup_pb in instance.list_backups():
backup = Backup.from_pb(backup_pb, instance)
backup.delete()
instance.delete()
if CREATE_INSTANCE:
if not USE_EMULATOR:
# Defend against back-end returning configs for regions we aren't
# actually allowed to use.
configs = [config for config in configs if "-us-" in config.name]
if not configs:
raise ValueError("List instance configs failed in module set up.")
Config.INSTANCE_CONFIG = configs[0]
config_name = configs[0].name
create_time = str(int(time.time()))
labels = {
"python-spanner-dbapi-systests": "true",
"created": create_time
}
Config.INSTANCE = Config.CLIENT.instance(INSTANCE_ID,
config_name,
labels=labels)
created_op = Config.INSTANCE.create()
created_op.result(
SPANNER_OPERATION_TIMEOUT_IN_SECONDS) # block until completion
else:
Config.INSTANCE = Config.CLIENT.instance(INSTANCE_ID)
Config.INSTANCE.reload()
def __enter__(self):
project = os.getenv(
"GOOGLE_CLOUD_PROJECT",
os.getenv("PROJECT_ID", "emulator-test-project"),
)
client = Client(project=project)
config = f"{client.project_name}/instanceConfigs/{REGION}"
name = "spanner-django-test-{}".format(str(int(time.time())))
self._instance = client.instance(name, config)
created_op = self._instance.create()
created_op.result(120) # block until completion
return name
def __init__(self):
self._create_table()
self._one_row = (
1,
"Pete",
"Allison",
"2.1",
)
self._client = Client()
self._instance = self._client.instance(INSTANCE_ID)
self._database = self._instance.database(DATABASE_NAME)
self._many_rows = []
self._many_rows2 = []
for i in range(99):
num = round(random.randint(0, 100000000))
self._many_rows.append((num, "Pete", "Allison", "2.1"))
num2 = round(random.randint(0, 100000000))
self._many_rows2.append((num2, "Pete", "Allison", "2.1"))
# initiate a session
with self._database.snapshot():
pass
from google.api_core.exceptions import AlreadyExists, ResourceExhausted
from google.cloud.spanner_v1 import Client
from google.cloud.spanner_v1.instance import Instance
USE_EMULATOR = os.getenv("SPANNER_EMULATOR_HOST") is not None
PROJECT = os.getenv(
"GOOGLE_CLOUD_PROJECT",
os.getenv("PROJECT_ID", "emulator-test-project"),
)
CLIENT = None
if USE_EMULATOR:
from google.auth.credentials import AnonymousCredentials
CLIENT = Client(project=PROJECT, credentials=AnonymousCredentials())
else:
CLIENT = Client(project=PROJECT)
def delete_stale_test_instances():
"""Delete test instances that are older than four hours."""
cutoff = int(time.time()) - 4 * 60 * 60
instances_pbs = CLIENT.list_instances(
"labels.python-spanner-sqlalchemy-systest:true")
for instance_pb in instances_pbs:
instance = Instance.from_pb(instance_pb, CLIENT)
if "created" not in instance.labels:
continue
create_time = int(instance.labels["created"])
if create_time > cutoff:
rows = list(
snapshot.execute_sql("SELECT COUNT(*) FROM {}".format(table_desc.table))
)
assert len(rows) == 1
count = rows[0][0]
if count != table_desc.row_count:
print_func("Repopulating table: {}".format(table_desc.table))
chunk_me = table_desc.value()
row_data = [
(index, chunk_me, chunk_me) for index in range(table_desc.row_count)
]
with database.batch() as batch:
batch.delete(table_desc.table, all_)
batch.insert(table_desc.table, columns, row_data)
else:
print_func("Leaving table: {}".format(table_desc.table))
def populate_streaming(client):
database = ensure_database(client)
populate_table(database, FOUR_KAY)
populate_table(database, FORTY_KAY)
populate_table(database, FOUR_HUNDRED_KAY)
# Max STRING column size is just larger than 2 Mb, so use two columns
populate_table_2_columns(database, FOUR_MEG)
if __name__ == "__main__":
client = Client()
populate_streaming(client)
class SpannerBenchmarkTest(BenchmarkTestBase):
"""The original Spanner performance testing class."""
def __init__(self):
super().__init__()
self._client = Client()
self._instance = self._client.instance(INSTANCE)
self._database = self._instance.database(DATABASE)
self._many_rows = []
self._many_rows2 = []
birth_date = datetime.date(1998, 10, 6)
picture = base64.b64encode("123".encode())
for num in self._many_rows_ids:
self._many_rows.append(
{
"id": num,
"first_name": "Pete",
"last_name": "Allison",
"birth_date": birth_date,
"picture": picture,
}
)
for num in self._many_rows2_ids:
self._many_rows2.append((num, "Pete", "Allison", birth_date, picture))
# initiate a session
with self._database.snapshot():
pass
@measure_execution_time
def insert_one_row_with_fetch_after(self):
self._database.run_in_transaction(insert_one_row, self._one_row)
@measure_execution_time
def insert_many_rows(self):
self._database.run_in_transaction(insert_many_rows, self._many_rows)
@measure_execution_time
def insert_many_rows_with_mutations(self):
with self._database.batch() as batch:
batch.insert(
table="Singers",
columns=("id", "first_name", "last_name", "birth_date", "picture"),
values=self._many_rows2,
)
@measure_execution_time
def read_one_row(self):
with self._database.snapshot() as snapshot:
keyset = KeySet(all_=True)
snapshot.read(
table="Singers",
columns=("id", "first_name", "last_name", "birth_date", "picture"),
keyset=keyset,
).one()
@measure_execution_time
def select_many_rows(self):
with self._database.snapshot() as snapshot:
rows = list(
snapshot.execute_sql("SELECT * FROM Singers ORDER BY last_name")
)
if len(rows) != 100:
raise ValueError("Wrong number of rows read")
class SpannerBenchmarkTest:
"""The Spanner performace testing class."""
def __init__(self):
self._create_table()
self._one_row = (
1,
"Pete",
"Allison",
"2.1",
)
self._client = Client()
self._instance = self._client.instance(INSTANCE_ID)
self._database = self._instance.database(DATABASE_NAME)
self._many_rows = []
self._many_rows2 = []
for i in range(99):
num = round(random.randint(0, 100000000))
self._many_rows.append((num, "Pete", "Allison", "2.1"))
num2 = round(random.randint(0, 100000000))
self._many_rows2.append((num2, "Pete", "Allison", "2.1"))
# initiate a session
with self._database.snapshot():
pass
def _create_table(self):
"""Create a table for performace testing."""
conn = spanner_dbapi.connect(INSTANCE_ID, DATABASE_NAME)
conn.database.update_ddl([
"""
CREATE TABLE Author (
id INT64,
first_name STRING(20),
last_name STRING(20),
rating STRING(50),
) PRIMARY KEY (id)
"""
]).result(120)
conn.close()
@measure_execution_time
def insert_one_row_with_fetch_after(self):
self._database.run_in_transaction(insert_one_row, self._one_row)
@measure_execution_time
def insert_many_rows(self):
self._database.run_in_transaction(insert_many_rows, self._many_rows)
@measure_execution_time
def insert_many_rows_with_mutations(self):
with self._database.batch() as batch:
batch.insert(
table="Author",
columns=("id", "first_name", "last_name", "rating"),
values=self._many_rows2,
)
@measure_execution_time
def read_one_row(self):
with self._database.snapshot() as snapshot:
keyset = KeySet(all_=True)
snapshot.read(
table="Author",
columns=("id", "first_name", "last_name", "rating"),
keyset=keyset,
).one()
@measure_execution_time
def select_many_rows(self):
with self._database.snapshot() as snapshot:
rows = list(
snapshot.execute_sql(
"SELECT * FROM Author ORDER BY last_name"))
if len(rows) != 100:
raise ValueError("Wrong number of rows read")
def _cleanup(self):
"""Drop the test table."""
conn = spanner_dbapi.connect(INSTANCE_ID, DATABASE_NAME)
conn.database.update_ddl(["DROP TABLE Author"])
conn.close()
def run(self):
"""Execute every test case."""
measures = {}
for method in (
self.insert_one_row_with_fetch_after,
self.read_one_row,
self.insert_many_rows,
self.select_many_rows,
self.insert_many_rows_with_mutations,
):
method(measures)
self._cleanup()
return measures
# 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.
import os
from google.cloud.spanner_v1 import Client
if os.getenv(
"SPANNER_EMULATOR_HOST"
): # otherwise instance will be created by parallelize_tests
project = os.getenv(
"GOOGLE_CLOUD_PROJECT",
os.getenv("PROJECT_ID", "emulator-test-project"),
)
client = Client(project=project)
config = f"{client.project_name}/instanceConfigs/regional-us-central1"
instance = client.instance("google-cloud-django-backend-tests", config)
created_op = instance.create()
created_op.result(30) # block until completion
