Using Cloud Spanner with Cloud Functions

Objectives

Write, deploy, and trigger an HTTP Cloud Function that accesses Cloud Spanner.

Costs

This topic uses Cloud Spanner and Cloud Functions, which are billable components of the Google Cloud Platform.

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

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

Before you begin

1. This topic assumes you have a Cloud 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 Go, Java, Node.js, or Python.

Note: After this step, you should have two tables, Singers and Albums, in your database with the sample data specified in the music application schema.

1. Enable the Cloud Functions API.
   Enable the Cloud Functions API

2. Install and initialize the Cloud SDK.

3. Update and install gcloud components:

   Node.js 8/10

   gcloud components update

   Node.js 6 (Deprecated)

   gcloud components update

   Python

   gcloud components update

   Go

   gcloud components update

4. Prepare your development environment:

   Node.js

   Go to the Node.js setup guide

   Python

   Go to the Python setup guide

   Go

   Go to the Go setup guide

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.

   Go

   git clone https://github.com/GoogleCloudPlatform/golang-samples.git

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

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

   Node.js

   cd nodejs-docs-samples/functions/spanner/

   Python

   cd python-docs-samples/functions/spanner/

   Go

   cd golang-samples/functions/spanner/

3. Take a look at the sample code:

   Node.js

   functions/spanner/index.js

   View on GitHub Feedback

   // Imports the Google Cloud client library
   const {Spanner} = require('@google-cloud/spanner');
   
   // 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.
    */
   exports.get = (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
     return database
       .run(query)
       .then(results => {
         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}`)
           .end();
       });
   };

   Python

   functions/spanner/main.py

   View on GitHub Feedback

   from google.cloud import spanner
   
   instance_id = 'test-instance'
   database_id = 'example-db'
   
   client = spanner.Client()
   
   
   def spanner_read_data(request):
       instance = client.instance(instance_id)
       database = instance.database(database_id)
   
       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)

   Go

   functions/spanner/spanner.go

   View on GitHub Feedback

   // Package spanner contains an example of using Spanner from a Cloud Function.
   package spanner
   
   import (
   	"context"
   	"fmt"
   	"log"
   	"net/http"
   
   	"cloud.google.com/go/spanner"
   	"google.golang.org/api/iterator"
   )
   
   // client is a global Spanner client, to avoid initializing a new client for
   // every request.
   var client *spanner.Client
   
   // db is the name of the database to query.
   var db = "projects/my-project/instances/my-instance/databases/example-db"
   
   // HelloSpanner is an example of querying Spanner from a Cloud Function.
   func HelloSpanner(w http.ResponseWriter, r *http.Request) {
   	if client == nil {
   		// Declare a separate err variable to avoid shadowing client.
   		var err error
   		client, err = spanner.NewClient(context.Background(), db)
   		if err != nil {
   			http.Error(w, "Error initializing database", http.StatusInternalServerError)
   			log.Printf("spanner.NewClient: %v", err)
   			return
   		}
   	}
   
   	fmt.Fprintln(w, "Albums:")
   	stmt := spanner.Statement{SQL: `SELECT SingerId, AlbumId, AlbumTitle FROM Albums`}
   	iter := client.Single().Query(r.Context(), stmt)
   	defer iter.Stop()
   	for {
   		row, err := iter.Next()
   		if err == iterator.Done {
   			return
   		}
   		if err != nil {
   			http.Error(w, "Error querying database", http.StatusInternalServerError)
   			log.Printf("iter.Next: %v", err)
   			return
   		}
   		var singerID, albumID int64
   		var albumTitle string
   		if err := row.Columns(&singerID, &albumID, &albumTitle); err != nil {
   			http.Error(w, "Error parsing database response", http.StatusInternalServerError)
   			log.Printf("row.Columns: %v", err)
   			return
   		}
   		fmt.Fprintf(w, "%d %d %s\n", singerID, albumID, albumTitle)
   	}
   }
   

   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 8

gcloud functions deploy get --runtime nodejs8 --trigger-http

Node.js 10 (Beta)

gcloud functions deploy get --runtime nodejs10 --trigger-http

Node.js 6 (Deprecated)

gcloud functions deploy get --runtime nodejs6 --trigger-http

Python

gcloud functions deploy spanner_read_data --runtime python37 --trigger-http

Go

gcloud functions deploy HelloSpanner --runtime go111 --trigger-http

Function deployment may take up to 2 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 Functions page in the Google Cloud Platform 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:

Node.js

curl "https://REGION-PROJECT_ID.cloudfunctions.net/get" 

Python

curl "https://REGION-PROJECT_ID.cloudfunctions.net/spanner_read_data" 

Go

curl "https://REGION-PROJECT_ID.cloudfunctions.net/HelloSpanner" 

where REGION and PROJECT_ID match the values that are visible in your terminal 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.

Cleanup

To avoid incurring additional charges to your GCP account for the Cloud Spanner and Cloud Functions resources used in this topic:

1. Delete the instance:

   gcloud spanner instances delete test-instance
   

2. Delete the function that you deployed:

   Node.js

   gcloud functions delete get 

   Python

   gcloud functions delete spanner_read_data 

   Go

   gcloud functions delete HelloSpanner 

What's next

• Learn more about writing Cloud Functions.
• Learn more about deploying Cloud Functions.
• Learn more about calling Cloud Functions.
• Learn more about monitoring Cloud Functions.