Create a Cloud Run function that returns Spanner results

This tutorial shows you how to write an HTTP Cloud Run function that returns Spanner results.

Objectives

Write, deploy, and trigger an HTTP function that accesses Spanner.

Costs

This document uses Spanner and Cloud Run, which are billable components of Google Cloud.

  • For information on the cost of using Spanner, see Spanner pricing.

  • For information on the cost of using Cloud Run, including free invocations, see Cloud Run pricing.

Before you begin

  1. This document assumes you have a Spanner instance named test-instance and a database named example-db that uses the music application schema. For instructions on creating an instance and database with the music application schema, see Quickstart using the console or the tutorials for Getting Started in Node.js, or Python.

  2. Enable the Cloud Run and Cloud Build APIs.

    Enable the APIs

  3. Install and initialize the gcloud CLI.

    If you already have the gcloud CLI installed, update it by running the following command:

    gcloud components update

  4. Prepare your development environment:

    Node.js

    See the Node.js setup guide.

    Python

    See the Python setup guide.

Required roles

To get the permissions that you need to deploy Cloud Run services from source, ask your administrator to grant you the following IAM roles:

For a list of IAM roles and permissions that are associated with Cloud Run, see Cloud Run IAM roles and Cloud Run IAM permissions. If your Cloud Run service interfaces with Google Cloud APIs, such as Cloud Client Libraries, see the service identity configuration guide. For more information about granting roles, see deployment permissions and manage access.

Roles for the Cloud Build service account

You or your administrator must grant the Cloud Build service account the following IAM role.

Click to view required roles for the Cloud Build service account

Cloud Build automatically uses the Compute Engine default service account as the default Cloud Build service account to build your source code and Cloud Run resource, unless you override this behavior. For Cloud Build to build your sources, ask your administrator to grant Cloud Run Builder (roles/run.builder) to the Compute Engine default service account on your project:

  gcloud projects add-iam-policy-binding PROJECT_ID \
      --member=serviceAccount:PROJECT_NUMBER-compute@developer.gserviceaccount.com \
      --role=roles/run.builder

Replace PROJECT_NUMBER with your Google Cloud project number, and PROJECT_ID with your Google Cloud project ID. For detailed instructions on how to find your project ID, and project number, see Creating and managing projects.

Granting the Cloud Run builder role to the Compute Engine default service account takes a couple of minutes to propagate.

Roles for the service identity

You must grant the service identity that you use for the Cloud Run service the following IAM role.

  • Spanner Database Reader (roles/spanner.databaseReader)

For more information on granting roles, see Manage access to projects, folders, and organizations.

Prepare the application

  1. Clone the sample app repository to your local machine:

    Node.js 

    git clone https://github.com/GoogleCloudPlatform/nodejs-docs-samples.git

    Alternatively, you can download the sample as a zip file and extract it.

    Python 

    git clone https://github.com/GoogleCloudPlatform/python-docs-samples.git

    Alternatively, you can download the sample as a zip file and extract it.

  2. Change to the directory that contains the Cloud Run functions sample code for accessing Spanner:

    Node.js 

    cd nodejs-docs-samples/functions/spanner

    Python 

    cd python-docs-samples/functions/spanner

  3. Take a look at the sample code:

    Node.js 

    // Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');

// Imports the functions framework to register your HTTP function
const functions = require('@google-cloud/functions-framework');

// Instantiates a client
const spanner = new Spanner();

// Your Cloud Spanner instance ID
const instanceId = 'test-instance';

// Your Cloud Spanner database ID
const databaseId = 'example-db';

/**
 * HTTP Cloud Function.
 *
 * @param {Object} req Cloud Function request context.
 * @param {Object} res Cloud Function response context.
 */
functions.http('spannerQuickstart', async (req, res) => {
  // Gets a reference to a Cloud Spanner instance and database
  const instance = spanner.instance(instanceId);
  const database = instance.database(databaseId);

  // The query to execute
  const query = {
    sql: 'SELECT * FROM Albums',
  };

  // Execute the query
  try {
    const results = await database.run(query);
    const rows = results[0].map(row => row.toJSON());
    rows.forEach(row => {
      res.write(
        `SingerId: ${row.SingerId}, ` +
          `AlbumId: ${row.AlbumId}, ` +
          `AlbumTitle: ${row.AlbumTitle}\n`
      );
    });
    res.status(200).end();
  } catch (err) {
    res.status(500).send(`Error querying Spanner: ${err}`);
  }
});

    Python 

    import functions_framework
from google.cloud import spanner

instance_id = "test-instance"
database_id = "example-db"

client = spanner.Client()
instance = client.instance(instance_id)
database = instance.database(database_id)


@functions_framework.http
def spanner_read_data(request):
    query = "SELECT * FROM Albums"

    outputs = []
    with database.snapshot() as snapshot:
        results = snapshot.execute_sql(query)

        for row in results:
            output = "SingerId: {}, AlbumId: {}, AlbumTitle: {}".format(*row)
            outputs.append(output)

    return "\n".join(outputs)

    The function sends a SQL query to fetch all Albums data from your database. The function is executed when you make an HTTP request to the function's endpoint.

Deploy the function

To deploy the function with an HTTP trigger, run the following command in the spanner directory:

Node.js 

gcloud run deploy nodejs-spanner-function \
    --source . \
    --region REGION \
    --function spannerQuickstart \
    --base-image RUNTIME_ID \
    --log-http

Python 

gcloud run deploy python-spanner-function \
    --source . \
    --region REGION \
    --function spanner_read_data \
    --base-image RUNTIME_ID \
    --log-http

Replace:

Function deployment might take up to two minutes.

Note the url value returned when your function finishes deploying. You will use it when you trigger the function.

You can view your deployed functions on the Cloud Run page in the Google Cloud console. You can also create and edit functions on that page, and get details and diagnostics for your functions.

Trigger the function

Make an HTTP request to your function:

curl URL

Replace URL with the URL value returned when your function finishes deploying.

You should see output that shows the results of the SQL query, assuming you worked through a Getting Started tutorial and populated the database:

SingerId: 2, AlbumId: 2, AlbumTitle: Forever Hold Your Peace
SingerId: 1, AlbumId: 2, AlbumTitle: Go, Go, Go
SingerId: 2, AlbumId: 1, AlbumTitle: Green
SingerId: 2, AlbumId: 3, AlbumTitle: Terrified
SingerId: 1, AlbumId: 1, AlbumTitle: Total Junk

You can also visit the function's URL in your browser to see the result of your SQL query.

Clean up

To avoid incurring additional charges to your Google Cloud account for the Spanner and Cloud Run functions resources used in this document:

  1. Delete the instance:

    gcloud CLI instances delete test-instance

  2. Delete the Cloud Run service you deployed in this tutorial:

    Node.js 

    gcloud run services delete nodejs-spanner-function

    Python 

    gcloud run services delete python-spanner-function

What's next