Setup Cloud Spanner database and exporting it

Cloud spanner is a transactional database that designed for cloud with horizontal and vertical scaling capability. We will try to setup a cloud spanner instance and create a database with it. Finally we will export the database to a cloud storage bucket through dataflow.

Prerequisites:

Enable the Cloud Spanner API under the APIs and services section

1. Create Cloud Spanner instance

Under the Google Cloud Platform navigation, go to Spanner, create instance.

Give your instance a name and the instance ID would be auto-filled. We only need a regional instance with 1 node. Create the instance after filling in the setting.

2. Create database in Spanner instance

Now we need to create the database in the instance.

Name the database as “footballer”. Then put the following code in the schema field to create the table to store footballer information.

CREATE TABLE Footballers ( FootballerId INT64 NOT NULL, FirstName STRING(1024), LastName STRING(1024), FootballerInfo BYTES(MAX), Goals INT64, Assists INT64 ) PRIMARY KEY(FootballerId);

3. Insert data to the database

Access our created database, click data on the navigation panel. We can start insert data.

Enter the following to the query editor to insert the data of the first footballer.

INSERT INTO
  Footballers (FootballerId,
    Assists,
    FirstName,
    FootballerInfo,
    Goals,
    LastName)
VALUES
  (101, -- type: INT64
    2, -- type: INT64
    "Marco", -- type: STRING(1024)
    NULL, -- type: BYTES(MAX)
    10, -- type: INT64
    "Reus" -- type: STRING(1024)
    );

Then input the second footballer information with another query.

INSERT INTO
  Footballers (FootballerId,
    Assists,
    FirstName,
    FootballerInfo,
    Goals,
    LastName)
VALUES
  (101, -- type: INT64
    10, -- type: INT64
    "Cristiano", -- type: STRING(1024)
    NULL, -- type: BYTES(MAX)
    15, -- type: INT64
    "Ronaldo" -- type: STRING(1024)
    );

Now let do a query to find the record we just inserted. It should return footballer with ID 102.

SELECT
  *
FROM
  Footballers
WHERE
  FootballerId=102;

4. Create bucket for export storage

Under the GCP navigation, go to Cloud Storage and create a regional bucket. After it’s created, go back to Spanner.

5. Export your Spanner database to the bucket

With your instance selected, click export to start the process.

For the destination, select the GCS bucket you just created. Pick the footballer database to export and select the exporting job region as the same as your instance region.

After submitting the job you’ll see the job is starting. The export job will automatic creates all the necessary resources like compute engine and Dataflow job. Click the Dataflow job and it will bring us to the actual Dataflow job