In this lab, you write applications that utilize Spanner databases, and deploy them to both Cloud Run Functions and Cloud Run. You also install, configure, and enable the Spanner emulator for use in development environments.
Objectives
In this lab, you learn how to:
Deploy Cloud Run Functions that read and write to Spanner databases.
Set up and use the Spanner emulator for development.
Build a REST API that allows you to read and write Spanner data.
Deploy a REST API to Google Cloud Run.
Setup and Requirements
Before you click the Start Lab button
Read these instructions. Labs are timed and you cannot pause them. The timer, which starts when you click Start Lab, shows how long Google Cloud resources will be made available to you.
This hands-on lab lets you do the lab activities yourself in a real cloud environment, not in a simulation or demo environment. It does so by giving you new, temporary credentials that you use to sign in and access Google Cloud for the duration of the lab.
To complete this lab, you need:
Access to a standard internet browser (Chrome browser recommended).
Note: Use an Incognito or private browser window to run this lab. This prevents any conflicts between your personal account and the Student account, which may cause extra charges incurred to your personal account.
Time to complete the lab. Remember, once you start, you cannot pause a lab.
Note: If you already have your own personal Google Cloud account or project, do not use it for this lab to avoid extra charges to your account.
Activate Cloud Shell
Cloud Shell is a virtual machine that is loaded with development tools. It offers a persistent 5GB home directory and runs on the Google Cloud. Cloud Shell provides command-line access to your Google Cloud resources.
Click Activate Cloud Shell at the top of the Google Cloud console.
When you are connected, you are already authenticated, and the project is set to your PROJECT_ID. The output contains a line that declares the PROJECT_ID for this session:
Your Cloud Platform project in this session is set to YOUR_PROJECT_ID
gcloud is the command-line tool for Google Cloud. It comes pre-installed on Cloud Shell and supports tab-completion.
(Optional) You can list the active account name with this command:
gcloud auth list
Click Authorize.
Your output should now look like this:
Output:
ACTIVE: *
ACCOUNT: student-01-xxxxxxxxxxxx@qwiklabs.net
To set the active account, run:
$ gcloud config set account `ACCOUNT`
(Optional) You can list the project ID with this command:
gcloud config list project
Output:
[core]
project = <project_ID>
Example output:
[core]
project = qwiklabs-gcp-44776a13dea667a6
Note: For full documentation of gcloud, in Google Cloud, refer to the gcloud CLI overview guide.
Task 1. Create a database with test data
On the Google Cloud Console title bar, click Activate Cloud Shell (). If prompted, click Continue. Optionally, you can do the same by selecting your Cloud Console window, then typing on your keyboard the G key and then the S key.
Run the following command to enable the Cloud Build, Cloud Run and Event Arc APIs. If you are asked to Authorize the command, then do so.
Task 2. Create a Cloud Run Function to read from Spanner
Create a folder for your first Cloud Run Function with the following command.
mkdir ~/lab-files/spanner_get_pets
cd ~/lab-files/spanner_get_pets
Create 2 files for your application: main.py and requirements.txt.
touch main.py requirements.txt
Click the Open Editor button. In the lab-files/spanner_get_pets/requirements.txt file you just created, add the following code.
google-cloud-spanner==3.27.0
In the lab-files/spanner_get_pets/main.py file add the following code that reads from the database and returns the pets.
from google.cloud import spanner
instance_id = 'test-spanner-instance'
database_id = 'pets-db'
client = spanner.Client()
instance = client.instance(instance_id)
database = instance.database(database_id)
def spanner_get_pets(request):
query = """SELECT OwnerName, PetName, PetType, Breed
FROM Owners
JOIN Pets ON Owners.OwnerID = Pets.OwnerID;"""
outputs = []
with database.snapshot() as snapshot:
results = snapshot.execute_sql(query)
output = '<div>OwnerName,PetName,PetType,Breed</div>'
outputs.append(output)
for row in results:
output = '<div>{},{},{},{}</div>'.format(*row)
outputs.append(output)
return '\n'.join(outputs)
Click the Open Terminal button. Then, deploy the Cloud Run Function with the following command. Note, the trigger will be an HTTP trigger, which means a URL will generated for invoking the function. (It will take a couple minutes for the command to complete.)
When the command completes to deploy the Cloud Run Function, test the Cloud Run Function using the following command. The test data should be returned.
Task 3. Create a Cloud Run Function to Write to Spanner
Create a folder for your second Cloud Run Function with the following command.
mkdir ~/lab-files/spanner_save_pets
cd ~/lab-files/spanner_save_pets
Create two files for your application: main.py and requirements.txt.
touch main.py requirements.txt
Click the Open Editor button. In the lab-files/spanner_save_pets/requirements.txt file you just created, add the following code.
google-cloud-spanner==3.27.0
In the lab-files/spanner_save_pets/main.py file, add the following code that reads from the database and returns the pets.
from google.cloud import spanner
import base64
import uuid
import json
instance_id = 'test-spanner-instance'
database_id = 'pets-db'
client = spanner.Client()
instance = client.instance(instance_id)
database = instance.database(database_id)
def spanner_save_pets(event, context):
pubsub_message = base64.b64decode(event['data']).decode('utf-8')
data = json.loads(pubsub_message)
# Check to see if the Owner already exists
with database.snapshot() as snapshot:
results = snapshot.execute_sql("""
SELECT OwnerID FROM OWNERS
WHERE OwnerName = @owner_name""",
params={"owner_name": data["OwnerName"]},
param_types={"owner_name": spanner.param_types.STRING})
row = results.one_or_none()
if row != None:
owner_exists = True
owner_id = row[0]
else:
owner_exists = False
owner_id = str(uuid.uuid4())
# Need a UUID for the new pet
pet_id = str(uuid.uuid4())
def insert_owner_pet(transaction, data, owner_exists):
try:
row_ct = 0
params = { "owner_id": owner_id,
"owner_name": data["OwnerName"],
"pet_id": pet_id,
"pet_name": data["PetName"],
"pet_type": data["PetType"],
"breed": data["Breed"],
}
param_types = { "owner_id": spanner.param_types.STRING,
"owner_name": spanner.param_types.STRING,
"pet_id": spanner.param_types.STRING,
"pet_name": spanner.param_types.STRING,
"pet_type": spanner.param_types.STRING,
"breed": spanner.param_types.STRING,
}
# Only add the Owner if they don't exist already
if not owner_exists:
row_ct = transaction.execute_update(
"""INSERT Owners (OwnerID, OwnerName) VALUES (@owner_id, @owner_name)""",
params=params,
param_types=param_types,)
# Add the pet
row_ct += transaction.execute_update(
"""INSERT Pets (PetID, OwnerID, PetName, PetType, Breed) VALUES (@pet_id, @owner_id, @pet_name, @pet_type, @breed)
""",
params=params,
param_types=param_types,)
except:
row_ct = 0
return row_ct
row_ct = database.run_in_transaction(insert_owner_pet, data, owner_exists)
print("{} record(s) inserted.".format(row_ct))
Click the Open Terminal button. This Cloud Run Function triggers on a Pub/Sub message. For it to work, you first need to create the Pub/Sub topic with the following command.
gcloud pubsub topics create new-pet-topic
Deploy the Cloud Run Function with the following command. (It will take a few minutes for the command to complete.)
When the command completes, in the Console, navigate to the Pub/Sub service. Click the new-pet-topic topic to view its details.
On the Details page, click the Messages tab, then click the Publish Message button.
Enter the message below, then click the Publish button. Note: the message is in JSON format and must use the correct schema as shown for the function to work.
The emulator takes over the terminal, so in the Cloud Shell toolbar, click the + icon to open a new terminal tab. Run the following commands to configure the Cloud SDK to use the emulator.
gcloud config configurations create emulator
gcloud config set auth/disable_credentials true
gcloud config set project $GOOGLE_CLOUD_PROJECT
gcloud config set api_endpoint_overrides/spanner http://localhost:9020/
Create the instance and database using gcloud, but note that these commands are using the emulator now, not Spanner in the cloud. Run each separately, not as a whole.
To have the code from the Python client library use the emulator, the SPANNER_EMULATOR_HOST environment variable must be set. Run the following code now to do that.
export SPANNER_EMULATOR_HOST=localhost:9010
Task 5. Writing a REST API for the Spanner Pets database
Create a folder for the Cloud Run program files and add the files needed.
mkdir ~/lab-files/cloud-run
cd ~/lab-files/cloud-run
touch Dockerfile main.py requirements.txt
Click the Open Editor button. In the lab-files/cloud-run/requirements.txt file you just created, add the following code.
In main.py, add the following. This code uses Python Flask and the Flask-RESTful library to build a REST API for the Pets database.
Note: the use of the environment variables near the top of the file (lines 11 to 20). When you deploy to Cloud Run, you set these variables to point to the real Spanner database. When the variables are not set, it defaults to the emulator.
import os
import uuid
from flask import Flask, jsonify, request
from flask_restful import Api, Resource
from google.api_core import exceptions
from google.cloud import spanner
from werkzeug.exceptions import BadRequest, NotFound
# --- Configuration ---
# Use emulator settings by default, but override with environment variables if they exist.
INSTANCE_ID = os.environ.get("INSTANCE_ID", "emulator-instance")
DATABASE_ID = os.environ.get("DATABASE_ID", "pets-db")
# --- Database Repository ---
class SpannerRepository:
"""
A repository class to handle all database interactions with Google Cloud Spanner.
This separates database logic from the API/view layer.
"""
def __init__(self, instance_id, database_id):
spanner_client = spanner.Client()
self.instance = spanner_client.instance(instance_id)
self.database = self.instance.database(database_id)
def list_all_pets(self):
"""Retrieves all pets with their owner information."""
query = """
SELECT o.OwnerID, o.OwnerName, p.PetID, p.PetName, p.PetType, p.Breed
FROM Owners o JOIN Pets p ON o.OwnerID = p.OwnerID
"""
with self.database.snapshot() as snapshot:
results = snapshot.execute_sql(query)
# 1. Materialize the iterator into a list of rows.
# Each 'row' in this list is a regular Python list of values.
rows = list(results)
# 2. If there are no rows, the metadata might be incomplete.
# This check prevents the original 'NoneType' error.
if not rows:
return []
# 3. If we have rows, the metadata is guaranteed to be available.
# Get the column names from the result set's metadata.
# Note: results.metadata is the correct attribute.
keys = [field.name for field in results.metadata.row_type.fields]
# 4. Zip the keys with each row's list of values to create dicts.
return [dict(zip(keys, row)) for row in rows]
def get_pet_by_id(self, pet_id):
"""Retrieves a single pet by its ID."""
query = """
SELECT o.OwnerID, o.OwnerName, p.PetID, p.PetName, p.PetType, p.Breed
FROM Owners o JOIN Pets p ON o.OwnerID = p.OwnerID
WHERE p.PetID = @pet_id
"""
params = {"pet_id": pet_id}
param_types = {"pet_id": spanner.param_types.STRING}
with self.database.snapshot() as snapshot:
results = snapshot.execute_sql(
query, params=params, param_types=param_types
)
# results.one_or_none() returns a special Row object (not a list)
# that has a .keys() method, so this is correct and simpler.
row = results.one_or_none()
if not row:
return None
return dict(zip(row.keys(), row))
def create_pet_and_owner(self, data):
"""
Creates a new pet. If the owner doesn't exist, creates the owner as well.
This entire operation is performed in a single transaction.
"""
def _tx_create_pet(transaction):
pet_id = str(uuid.uuid4())
owner_name = data["OwnerName"]
owner_result = transaction.execute_sql(
"SELECT OwnerID FROM Owners WHERE OwnerName = @name",
params={"name": owner_name},
param_types={"name": spanner.param_types.STRING},
).one_or_none()
if owner_result:
owner_id = owner_result[0]
else:
owner_id = str(uuid.uuid4())
transaction.insert(
"Owners",
columns=("OwnerID", "OwnerName"),
values=[(owner_id, owner_name)],
)
pet_columns = ["PetID", "OwnerID", "PetName", "PetType", "Breed"]
pet_values = [
pet_id,
owner_id,
data["PetName"],
data["PetType"],
data["Breed"],
]
transaction.insert("Pets", columns=pet_columns, values=[pet_values])
new_pet_data = {
"PetID": pet_id,
"OwnerID": owner_id,
"OwnerName": owner_name,
"PetName": data["PetName"],
"PetType": data["PetType"],
"Breed": data["Breed"],
}
return new_pet_data
return self.database.run_in_transaction(_tx_create_pet)
def delete_pet_by_id(self, pet_id):
"""Deletes a single pet by its ID in a transaction."""
def _tx_delete_pet(transaction):
return transaction.execute_update(
"DELETE FROM Pets WHERE PetID = @pet_id",
params={"pet_id": pet_id},
param_types={"pet_id": spanner.param_types.STRING},
)
return self.database.run_in_transaction(_tx_delete_pet)
def delete_all_pets_and_owners(self):
"""Deletes all owners, which cascades to delete all pets."""
def _tx_delete_all(transaction):
return transaction.execute_update("DELETE FROM Owners WHERE true")
return self.database.run_in_transaction(_tx_delete_all)
# --- API Resources ---
db_repo = SpannerRepository(INSTANCE_ID, DATABASE_ID)
class PetsList(Resource):
def get(self):
"""Returns a list of all pets."""
pets = db_repo.list_all_pets()
return jsonify(pets)
def post(self):
"""Creates a new pet and possibly a new owner."""
try:
data = request.get_json(force=True)
required_fields = ["OwnerName", "PetName", "PetType", "Breed"]
if not all(field in data for field in required_fields):
raise BadRequest("Missing required fields in JSON payload.")
except BadRequest as e:
return {"message": str(e)}, 400
try:
new_pet = db_repo.create_pet_and_owner(data)
return new_pet, 201
except exceptions.GoogleAPICallError as e:
return {"message": "Database transaction failed", "error": str(e)}, 500
def delete(self):
"""Deletes all owners and pets."""
deleted_count = db_repo.delete_all_pets_and_owners()
return {"message": f"{deleted_count} owner record(s) deleted (pets cascaded)."}, 200
class Pet(Resource):
def get(self, pet_id):
"""Returns a single pet by ID."""
pet = db_repo.get_pet_by_id(pet_id)
if pet:
return jsonify(pet)
raise NotFound("Pet with the specified ID was not found.")
def delete(self, pet_id):
"""Deletes a single pet by ID."""
try:
deleted_count = db_repo.delete_pet_by_id(pet_id)
if deleted_count > 0:
return {"message": f"Pet with ID {pet_id} was deleted."}, 200
raise NotFound("Pet with the specified ID was not found.")
except exceptions.NotFound:
raise NotFound("Pet with the specified ID was not found.")
def patch(self, pet_id):
"""This endpoint is not implemented."""
return {"message": "Update operation is not implemented."}, 501
# --- Flask App Initialization ---
def create_app():
"""Application factory to create and configure the Flask app."""
app = Flask(__name__)
api = Api(app)
# API Resource routing
api.add_resource(PetsList, "/pets")
api.add_resource(Pet, "/pets/<string:pet_id>")
# Centralized error handling
@app.errorhandler(NotFound)
def handle_not_found(e):
return jsonify({"message": str(e)}), 404
@app.errorhandler(BadRequest)
def handle_bad_request(e):
return jsonify({"message": str(e)}), 400
@app.errorhandler(Exception)
def handle_generic_error(e):
app.logger.error(f"An unhandled exception occurred: {e}", exc_info=True)
return jsonify({"message": "An internal server error occurred."}), 500
return app
app = create_app()
if __name__ == "__main__":
# Use debug=False in a production environment
app.run(host="0.0.0.0", port=8080, debug=True)
Click the Open Terminal button, and then run the following code to install the necessary packages.
pip install -r requirements.txt
Now run the following code in terminal to start the program.
python main.py
In the Cloud Shell toolbar, click the + icon again to open a third terminal tab.
You can use curl to test the API. First, add some records using HTTP POST commands.
See if the records were added using an HTTP GET command. The records should be returned in JSON format.
curl http://localhost:8080/pets
Note: In case, you missed any steps or not getting the desired output. Try to follow the steps again from the Task 4 using the CloudShell.
Type exit to close the third terminal tab, then type Ctrl + C to stop the Python program in the second terminal tab. Close the second tab by typing exit.
Return to the first terminal tab and stop the emulator by typing Ctrl + C.
Note: These records were added to the emulator. Next, you deploy to Cloud Run and use the real Spanner instance.
Task 6. Deploying an app to Cloud Run
To deploy to Cloud Run, you need a Docker image. First change directories to the cloud-run folder.
cd ~/lab-files/cloud-run
To create the Docker image, you need to add instructions to the Dockerfile file. Click the Open Editor button and open the Dockerfile file. Paste the following code into it.
FROM python:3.9
WORKDIR /app
COPY . .
RUN pip install gunicorn
RUN pip install -r requirements.txt
ENV PORT=8080
CMD exec gunicorn --bind :$PORT --workers 1 --threads 8 main:app
Return to the terminal and run the following code to create the Docker image. (Make sure you are in the ~/lab-files/cloud-run folder.)
Now, deploy the Cloud Run application using the following command. Do note how the environment variables are set in the command, so the code will use the Cloud Spanner instance, not the emulator. It takes a couple minutes for the command to complete.
gcloud run deploy spanner-pets-api --image gcr.io/$GOOGLE_CLOUD_PROJECT/spanner-pets-api:v1.0 --update-env-vars INSTANCE_ID=test-spanner-instance,DATABASE_ID=pets-db --region={{{project_0.default_region|place_holder_text}}}
When the command completes, note the service URL and copy it to the clipboard. As you did with the emulator, you can use curl commands to test the API. First, create a variable to store the URL as shown below. Make sure you paste your full URL including the https://... in place of the full placeholder - <YOUR_SERVICE_URL_HERE>.
So, records have been added successfully. In the Console, navigate to the Cloud Run service and click your service to view its details and look at the logs. You will see each request you made logged there.
From the Console, delete the Spanner instance so you are no longer being charged for it.
Congratulations! You have written applications that utilize Spanner databases and deployed them to both Cloud Run Functions and Cloud Run. You also installed, configured, and enabled the Spanner emulator for use in development environments.
End your lab
When you have completed your lab, click End Lab. Your account and the resources you've used are removed from the lab platform.
You will be given an opportunity to rate the lab experience. Select the applicable number of stars, type a comment, and then click Submit.
The number of stars indicates the following:
1 star = Very dissatisfied
2 stars = Dissatisfied
3 stars = Neutral
4 stars = Satisfied
5 stars = Very satisfied
You can close the dialog box if you don't want to provide feedback.
For feedback, suggestions, or corrections, please use the Support tab.
Copyright 2024 Google LLC All rights reserved. Google and the Google logo are trademarks of Google LLC. All other company and product names may be trademarks of the respective companies with which they are associated.
Labs create a Google Cloud project and resources for a fixed time
Labs have a time limit and no pause feature. If you end the lab, you'll have to restart from the beginning.
On the top left of your screen, click Start lab to begin
Use private browsing
Copy the provided Username and Password for the lab
Click Open console in private mode
Sign in to the Console
Sign in using your lab credentials. Using other credentials might cause errors or incur charges.
Accept the terms, and skip the recovery resource page
Don't click End lab unless you've finished the lab or want to restart it, as it will clear your work and remove the project
This content is not currently available
We will notify you via email when it becomes available
Great!
We will contact you via email if it becomes available
One lab at a time
Confirm to end all existing labs and start this one
Use private browsing to run the lab
Use an Incognito or private browser window to run this lab. This
prevents any conflicts between your personal account and the Student
account, which may cause extra charges incurred to your personal account.
In this lab, you write applications that utilize Spanner databases, and deploy them to both Cloud Run Functions and Cloud Run. You also install, configure, and enable the Spanner emulator for use in development environments.
