How to use Cloud Spanner Database: A Step-By-Step Guide In Google Cloud

Spanner Database relies on the concepts of Primary Keys, Table interleaving, and Foreign Keys to maximize database efficiency and performance. In this example we will create a new database and a table then performing some basic database operations.

Step 1: Create Database

• Click on the instance name, we have just created and it will open the details of the instance. Scroll down and look for Database and click on Create Database.

• Now give a name to your database and select the database dialect. Here we are going to choose the Google Standard SQL. If you want you can choose PostgreSQL as database dialect.

• Now, click on create and the Spanner database will be created.

Step 2: Create Table

• Click on the database name to open it’s details and then select Create Table which will open the Spanner Studio on your screen. Now to create a table use the following format,

CREATE TABLE
  <table_name> ( <col_name> <col_type>,
    )
PRIMARY KEY
  (<col_name>);

• Let’s create a Singers table with some attributes, using the following query.

CREATE TABLE Singers (
  SingerId   INT64 NOT NULL,
  FirstName  STRING(1024),
  LastName   STRING(1024),
  SingerInfo BYTES(MAX),
  BirthDate  DATE
) PRIMARY KEY(SingerId);

• The following screenshot illustrates on running the SQL Queris In Edior.

• Click on RUN to execute the query to create Singers table.

Output:

Step 3: Perform Database Operations

• Let’s move forward and perform some database operations on the Spanner Database.

a) Insert Data: We will be using Spanner Studio to perform all database operations. To insert data use the following structure for query.

INSERT INTO
  Singers (SingerId,
    BirthDate,
    FirstName,
    LastName,
    SingerInfo)
VALUES
  (<SingerId>, -- type: INT64
    <BirthDate>, -- type: DATE
    <FirstName>, -- type: STRING(1024)
    <LastName>, -- type: STRING(1024)
    <SingerInfo> -- type: BYTES(MAX)
    );

• Let’s insert few records into the table using the above format.

• Click on Run to execute Query.

b) Read Operation: We have inserted two more records in the table. Let’s check all the rows in the database. Following is the format to check all the records available in the table.

select * from <table_name>

c) Update Operation: Let’s query again to add birthdate to “Jayesh Haldar” using query. We can also change other values in the same update statement. Let’s change the Singer Name and birthday.

UPDATE
  Singers
SET
  BirthDate='2000-01-01',
  FirstName='Raj'
WHERE
  SingerId=3;

Output:

• Let’s again check the rows to see if the changes are made.

d) Delete operation: Let’s try deleting the first singer from the table.

DELETE from Singers WHERE SingerId = 1

Output:

• Singer Table after deleting the first row.

In today’s era when data is everywhere, the demand for databases which can seamlessly accumulate massive amount of data is increasing. Databases also have to provide consistency, reliability and high availability. Google Cloud Platform is one of famous cloud providers in the market providing cloud solutions including storage and databases. Cloud Spanner is a fully managed, scalable, distributed database service, offered by Google Cloud Platform. Cloud Spanner combines the benefits of a traditional relational database system with the scale and flexibility of a NoSQL database. In this article we will be learning about, how we can create and configure Cloud Spanner in Google Cloud Platform.