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.
- 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
After the job is done we can go to our created bucket to check the result. We can see the database is exported in avro format.
Now let’s wrap up by deleting our Cloud Spanner instance and GCS storage bucket.