Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
PostgreSQL interface 
3.
Components of the PostgreSQL interface
3.1.
PostgreSQL SQL dialect support
3.2.
PostgreSQL client support
3.3.
Choose between Google Standard SQL and PostgreSQL
3.4.
Best practices for using the PostgreSQL interface
4.
Create the database and query a database by using the Google Cloud console
5.
Create an instance
6.
Create a database
7.
Create a schema for your database
8.
Insert and modify data
8.1.
Insert
8.2.
Edit data
8.3.
Delete data
9.
Run a query
10.
Clean up
10.1.
Delete the database
10.2.
Delete the Instance
11.
Frequently Asked Questions
11.1.
What is a Cloud Database?
11.2.
What type of database is Cloud spanner?
11.3.
How do Cloud SQL and Cloud spanner differ from one another?
11.4.
Is spanner horizontally scalable?
11.5.
Does cloud spanner support MySQL?
12.
Conclusion
Last Updated: Mar 27, 2024

Cloud Spanner Concepts

Author Muskan Sharma
0 upvote
Leveraging ChatGPT - GenAI as a Microsoft Data Expert
Speaker
Prerita Agarwal
Data Specialist @
23 Jul, 2024 @ 01:30 PM

Introduction

We all have worked on databases while doing any project. So google provides us with a relational database service. Cloud Spanner is a fully managed, mission-critical relational database service that supports ANSI 2011 with extensions for Google Standard SQL and PostgreSQL, as well as transactional consistency at a global scale with automatic, synchronous replication for high availability.

So let's dive in more to learn about this topic.

PostgreSQL interface 

The PostgreSQL interface allows users of the open-source PostgreSQL ecosystem to access the features of Spanner, including completely managed, infinite scale, robust consistency, high performance, and up to 99.999 percent worldwide availability. It has integration with current Google tools, such as Dataflow, support for the psql command-line tool, support for native language clients, and a core subset of the PostgreSQL SQL dialect. Spanner exposes its scale-out capabilities using PostgreSQL-compatible syntax, unlike other services that control real PostgreSQL database instances. Although there is some partial PostgreSQL compatibility, this offers developers familiarity and portability.

Spanner's current console, APIs, and tools, such as the gcloud CLI, are used by administrators to provision, administer, and watch over databases that support the PostgreSQL interface. At database creation time, the PostgreSQL interface is customized for each database. Both Google Standard SQL and PostgreSQL interface dialect databases can be found in a Spanner instance. Both database dialects have the same scalability, consistency, performance, and security attributes because they employ the same underlying distributed database engine.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Components of the PostgreSQL interface

Support for the PostgreSQL SQL dialect and implementation for clients connecting to databases that support the PostgreSQL interface make up the two main features of the PostgreSQL interface.

PostgreSQL SQL dialect support

The PostgreSQL interface offers enhancements to handle Spanner features like interleaved tables and query hinting, as well as a subset of the PostgreSQL SQL dialect, including DQL, DML, and DDL.

PostgreSQL client support

Through the use of native, open-source Spanner clients for Java, Go, Python, Node.js, Ruby, PHP, C#, and C++ environments, developers can link their programs to a PostgreSQL interface database. Drivers and ORMs for PostgreSQL other than Spanner are not currently supported.

The psql command-line utility is also supported by the PostgreSQL interface. In many PostgreSQL setups, psql is an interactive environment for executing queries, viewing metadata, and loading data. PGAdapter, a lightweight proxy that converts the PostgreSQL wire protocol into Spanner's native gRPC interface and takes care of connection management and IAM authentication for you, enables psql functionality.

Choose between Google Standard SQL and PostgreSQL

You can choose between Google Standard SQL and PostgreSQL languages when building a Spanner database. Your applications' use of queries and various data types, as well as how they connect to the database, are all governed by the dialect's syntax and semantics.

Business needs and objectives, not SQL language support for Spanner capabilities, should determine whether a specific application or project uses Google Standard SQL or PostgreSQL. The SQL language dialects are similar to one another in terms of Spanner features:

  • The same distributed storage and query processing base underpins both implementations. Since they are similar, their performance, scalability, consistency, and availability traits are also similar.
  • You use the Spanner client libraries for DevOps automation, the gcloud CLI, and the console as the management interfaces for both.
  • Database metadata and statistics may be accessed both using the Spanner INFORMATION SCHEMA and SPANNER SYS system schemas.

The Google Standard SQL and PostgreSQL languages are not yet fully compatible in the current PostgreSQL interface release. Longer term, however, you should consider the following factors when deciding whether to utilize PostgreSQL or Google Standard SQL for a certain application or project:

  • Select PostgreSQL if your firm currently uses it as a standard or if the development team is familiar with the open-source PostgreSQL environment.
  • Selecting Google Standard SQL
    • If the development team has expertise working with other Google Cloud databases that accept Google Standard SQL, such as BigQuery, or if they have prior Spanner experience.
    • If your organization is standardizing on the use of Google Standard SQL or the ANSI 2011 standard that underpins it.

Best practices for using the PostgreSQL interface

Google suggests that you: properly use the PostgreSQL interface:

  • In a Spanner instance, create your PostgreSQL database using the terminal or the Google Cloud CLI. (You can make databases in Google Standard SQL and PostgreSQL using the same Instance.)
  • The client libraries for Spanner can be used to link programs that use your database.
  • For interactive database work, use the psql command-line tool through the PGAdapter proxy. As an alternative, you can utilize the Write DDL and Query pages on the console.

Create the database and query a database by using the Google Cloud console

In this, you'll get to learn how to use the Google Cloud console to carry out fundamental tasks in Cloud Spanner. 

Setting up before you begin.

  1. Create an account if you're new to Google Cloud to see how well our products work in practical situations. Additionally, new users receive $300 in complimentary credits to run, test, and deploy workloads.
  2. Choose or create a Google Cloud project from the project selector page in the Google Cloud dashboard.

Go to project selector.

  1. Make sure your Cloud project's billing is enabled. Find out how to determine whether billing is enabled for a project.
  2. For a project, enable the Cloud Spanner API.

Enable the Cloud Spanner API

Create an instance

When using Cloud Spanner for the first time, you must create an instance, which is a resource allocation that the Cloud Spanner databases in that instance use.

  1. Navigate to the Cloud Spanner Instances page in the Google Cloud console.

Go to Cloud Spanner Instances 

  1. Create an instance by clicking.
  2. Enter a name, such as Test Instance, for the instance name field.
  3. Based on the instance name, such as test-instance, the instance ID is automatically entered. Change it if necessary.
  4. Choose your configuration from the drop-down menu, keeping Regional as your default choice.
  5. Retain the default setting of 1000 processing units in Allocate Compute Capacity.
  6. Click on Create.

Create a database

  1. Navigate to the Cloud Spanner Instances page in the Google Cloud console.

Go to Cloud Spanner Instances 

2. Create an instance by clicking.

3. Enter a name, such as Test Instance, for the instance name field.

4. Click Create database on the instance Overview screen that appears.

5. Put a name in database name field, such example-db.

6. Pick a dialect for the database.

7. Click on Create.

Create a schema for your database

  1. Click Create table on the database Overview page's Tables section.
  2. Enter this in the Write DDL statements box:
CREATE TABLE Singers (
  SingerId INT64 NOT NULL,
  FirstName STRING(1024),
  LastName STRING(1024),
  SingerInfo BYTES(MAX),
  BirthDate DATE,
) PRIMARY KEY(SingerId);

3. Click on Submit.

Insert and modify data

An interface is available for adding, updating, and removing data via the Google Cloud console.

Insert

  1. Click the Singers table from the list of tables on the database Overview screen.
  2. To view the Data page for the Singers table, select Data from the navigation menu.
  3. Press Insert.

 -- Add the new values in VALUES clause in order of column list.
  -- Each value must be the type compatible with its associated column.
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)
    );
  -- Change values in WHERE condition to match the inserted row.
SELECT
  *
FROM
  Singers
WHERE
  SingerId=<SingerId>;

4. Edit the WHERE clause of the SELECT statement and the VALUES clause of the INSERT statement:

-- Add new values in VALUES clause in order of column list.
  -- Each value must be the type compatible with its associated column.
INSERT INTO
  Singers (SingerId,
    BirthDate,
    FirstName,
    LastName,
    SingerInfo)
VALUES
  (1, -- type: INT64
    NULL, -- type: DATE
    'Marc', -- type: STRING(1024)
    'Richards', -- type: STRING(1024)
    NULL -- type: BYTES(MAX)
    );
  -- Change values in the WHERE condition to match the inserted row.
SELECT
  *
FROM
  Singers
WHERE
  SingerId=1;

5. Press RUN.

6. In the Results tab, click the table link. There is now one row on the Singers table.

7. To Insert a row click on Insert.

8. Edit the WHERE clause of the SELECT statement and the VALUES clause of the INSERT statement:


 -- Add the new values in VALUES clause in order of column list.
  -- Each value must be the type compatible with its associated column.
INSERT INTO
  Singers (SingerId,
    BirthDate,
    FirstName,
    LastName,
    SingerInfo)
VALUES
  (2, -- type: INT64
    NULL, -- type: DATE
    'Catalina', -- type: STRING(1024)
    'Smith', -- type: STRING(1024)
    NULL -- type: BYTES(MAX)
    );
  -- Change values in WHERE condition to match the inserted row.
SELECT
  *
FROM
  Singers
WHERE
  SingerId=2;

9. Press RUN.

10. In the Results tab, click on the table link. There are now two rows on the Singers table.

You can also insert the empty string values when you enter data.

  1. To Insert a row click on Insert.
  2. Edit the WHERE clause of the SELECT statement and the VALUES clause of the INSERT statement:
  -- Add new values in VALUES clause in order of column list.
  -- Each value must be the type compatible with its associated column.
INSERT INTO
  Singers (SingerId,
    BirthDate,
    FirstName,
    LastName,
    SingerInfo)
VALUES
  (3, -- type: INT64
    NULL, -- type: DATE
    'Kena', -- type: STRING(1024)
    '', -- type: STRING(1024)
    NULL -- type: BYTES(MAX)
    );
  -- Change values in WHERE condition to match the inserted row.
SELECT
  *
FROM
  Singers
WHERE
  SingerId=3;

Observe that the last name column's value is an empty string, "," rather than a NULL value.

  1. Press RUN.
  2. Click the link to the table. There are three rows in the Singers table, and the row with primary key value 3 has an empty string in LastName column.

Edit data

  1. Select the checkbox in the row with the primary key whose value is three on the Data page for the Singers table, then click Edit.

The Query page is displayed by the Cloud Spanner along with a new tab that contains editable template UPDATE and SELECT queries. The row that has to be updated is the one with the primary key value of 3, according to the WHERE clauses of both statements.

-- Change the values in the SET clause to update the row where the WHERE condition is true.
UPDATE
  Singers
SET
  BirthDate='',
  FirstName='Kena',
  LastName='',
  SingerInfo=''
WHERE
  SingerId=3;
SELECT
  *
FROM
  Singers
WHERE
  SingerId=3;

3. Change only the birthdate in the SET clause of the UPDATE statement:

  -- Change the values in the SET clause to update the row where the WHERE condition is true.
UPDATE
  Singers
SET
  BirthDate='1961-04-01'
WHERE
  SingerId=3;
SELECT
  *
FROM
  Singers
WHERE
  SingerId=3;

4. Press Run

5. In the Results tab, click on the table link. 

Delete data

  1. Select the tick on the row with 2 in the first column on the Data page for the Singers table, then click Delete.
  2. Click Confirm on the box that displays.

The table for the Singers now has two rows

Run a query

  1. In the navigation menu on the database Overview screen, select Query.
  2. To add a new query tab, click the New tab. In the query editor, type the following Query after that:
   SELECT * FROM Singers;

3. Press Run

Clean up

Delete the database and the Instance you created to stop further charges from being applied to your Cloud Billing account. All databases created in an instance are automatically deleted when the Instance is deleted.

Delete the database

  1. In the Google Cloud console, go to Cloud Spanner Instances page.

Go to the Cloud Spanner Instances 

  1. Click the name of Instance that has the database that you want to delete, for example, Test Instance.
  2. To delete a database, click the name of the database, such as example-db.
  3. Click Delete Database on the Database Details page.
  4. Enter the database name and click Delete to confirm that you wish to delete the database.

Delete the Instance

  1. In the Google Cloud console, go to Cloud Spanner Instances page.

Go to Cloud Spanner Instances 

  1. To delete an instance, click the name of the Instance, such as the example- Test Instance.
  2. Press Delete Instance.
  3. Enter the Instance name and click Delete to confirm that you wish to delete the Instance.

Frequently Asked Questions

What is a Cloud Database?

A database service created and accessible using a cloud platform is known as a cloud database.

What type of database is Cloud spanner?

Running on Google Cloud, Google Cloud Spanner is a distributed relational database service.

How do Cloud SQL and Cloud spanner differ from one another?

Cloud SQL has a 30 TB data storage capacity. More than 30 TB of data is kept in Cloud Spanner.

Is spanner horizontally scalable?

Since Spanner automatically provides dynamic data resharding and data replication, achieving horizontal or vertical scaling requires no work.

Does cloud spanner support MySQL?

Different sets of data types are supported by Spanner versus MySQL.

Conclusion

This blog has extensively discussed Cloud Spanner Concepts, PostgreSQL interface creating and querying the database, etc. We hope this article was helpful and enhanced your knowledge about the Key points of Cloud Spanner. If you want to learn more deeply, check out the excellent content on the Coding Ninjas Website:

Cloud Logging in GCP, Monitoring Agent

Refer to our guided paths on the Coding Ninjas Studio platform to learn more about DSA, DBMS, Competitive Programming, Python, Java, JavaScript, etc. 

Refer to the links problemstop 100 SQL problemsresources, and mock tests to enhance your knowledge.

For placement preparations, visit interview experiences and interview bundle.

Thank you

Do upvote our blog to help other ninjas grow. Happy Coding!

Live masterclass