What is Cloud Spanner?

Cloud Spanner is the sole managed database service that combines enterprise-grade capabilities with global distribution and strong consistency. It is specifically designed for the cloud and merges the structural benefits of a relational database with the scalability of non-relational systems.

This service is fully managed, allowing for quick instance creation while handling all maintenance tasks automatically. With virtually limitless capacity and automatic replication, it can be distributed across various regions and zones.

Spanner achieves horizontal scaling by adding nodes, and its databases are replicated across multiple zones within a region, with the option for cross-region replication.

Key Features of Spanner:

• Exceptional Availability (99.999% SLA)
• Global Deployment Options
• Strong Consistency Guarantees
• Horizontal Scalability
• Automatic Data Replication

Databases within a single region offer a 99.99% availability SLA, while those across multiple regions enhance this to 99.999%. Spanner is built to scale horizontally, accommodating any number of machines as needed.

Understanding Spanner Architecture

Spanner operates as a distributed database, where nodes supply both computational and storage resources. Tables in Spanner are automatically divided into segments known as splits, which are replicated across multiple zones and potentially regions. These splits are organized by primary key and will be merged and balanced over time.

Creating Your First Spanner Instance

Let’s set up our first Spanner instance!

1. Navigate to the Google Cloud Console and type "Cloud Spanner" in the search bar.
2. On the Cloud Spanner homepage, click "Create a Provisioned Instance." The creation process involves three steps:

Step 1 — Naming Your Instance: In the Instance Name field, enter your chosen name. We’ll use “mediadirectory” for this example. The Instance ID will auto-populate; you can keep it as is and click "Continue."

Step 2 — Configuring Your Instance: Choose a configuration for your instance from the options of Regional, Dual-Region, or Multi-Region. For this case, we’ll select "Regional" and opt for "us-east1," then click "Continue."

Step 3 — Allocating Compute Capacity: Select the compute capacity based on the number of "Nodes" in Spanner. We will set this to "1" and click "Create."

In under a minute, your Spanner instance will be ready for use!

Creating a Database in Spanner

You will be automatically redirected to the Instance Overview Page, where you can click “Create Database.” The database creation involves two steps:

Step 1 — Naming Your Database: Enter a name for your database in the "Database name" field; we will use "mediadatabase."

Step 2 — Choosing a Database Dialect: Select between "Google Standard SQL" and "PostgreSQL." For our example, we’ll choose "Google Standard SQL" and click "Create."

You may specify DDL at this stage, but we’ll skip this for now since we will manually create tables in the next step. Click "Create" to finalize your database.

You will now be on the Database Overview page, where you can verify the Database Name, Dialect, Encryption type, and more.

Next, click on "Create Table" and enter your DDL for the table. We will use the following statement:

CREATE TABLE Singers (

SingerId INT64 NOT NULL,

FirstName STRING(1024),

LastName STRING(1024)

) PRIMARY KEY (SingerId);

Now, let’s insert a couple of records into this table:

INSERT INTO Singers(SingerId, FirstName, LastName) VALUES(101, "Mike", "San");

INSERT INTO Singers(SingerId, FirstName, LastName) VALUES(102, "John", "Tyler");

To retrieve some data, use the following query:

SELECT * FROM Singers WHERE SingerId = 101;

This will provide you with the details for the singer with SingerId 101. You can similarly create additional databases, tables, views, and execute SQL queries tailored to your business needs.

Exploring Spanner Databases

Spanner instances can host multiple databases, which can be created using the Google Cloud Console, Command Line Interface (CLI), Terraform, or your preferred programming language. During database creation, you can select the SQL dialect of your choice between Google Standard SQL and PostgreSQL.

Google Standard SQL utilizes common SQL syntax used in Spanner and BigQuery with added extensions, supporting the ANSI 2011 SQL Standard. Conversely, PostgreSQL is compatible with PostgreSQL tools, such as the psql command line tool, although certain features like stored procedures and triggers may not be supported.

Defining Relationships in Spanner

There are two primary methods for defining relationships in Spanner:

1. Primary Key-Foreign Key Constraint:

   This approach resembles traditional databases. However, as the data grows, splits can be added anywhere, potentially leading to parent-child data being stored on different splits, which may degrade read performance. Use this constraint when you frequently query parent or child data separately. You can create a foreign key constraint as follows:

   ALTER TABLE Orders

   ADD FOREIGN KEY (ProductID) REFERENCES Products (ProductID);

2. Interleaved Tables:

   This method stores parent-child data together within the same split. An interleaved table is declared as a child of another table to physically store its rows alongside the associated parent row.

   Use interleaved tables when you commonly retrieve child records with their parents. You can create interleaved tables using:

   CREATE TABLE Singers (

   SingerId INT64 NOT NULL,

   FirstName STRING(1024),

   LastName STRING(1024)

   ) PRIMARY KEY (SingerId);

   CREATE TABLE Albums (

   SingerId INT64 NOT NULL,

   AlbumId INT64 NOT NULL,

   AlbumTitle STRING(1024)

   ) PRIMARY KEY (SingerId, AlbumId),

   INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

Interleaved tables are stored on the same splits as the parent but are treated as separate entities. You can retrieve data from interleaved tables using joins, similar to other relational databases:

SELECT s.FirstName, a.AlbumTitle

FROM Singers AS s JOIN Albums AS a ON s.SingerId = a.SingerId;

Understanding Spanner Indexes

Indexes in Spanner are stored like tables and are essential for accelerating queries and sorting operations. An index is automatically created for the primary key(s), and secondary indexes can also be established using one or more fields from a table:

CREATE INDEX SingersByFirstLastName ON Singers(FirstName, LastName);

Creating Views in Spanner

A view in Spanner acts as a virtual table defined by a SQL query, similar to other relational databases. When a query referencing a view is executed, Spanner generates the virtual table by executing the view's defined query, using its content for the referring query.

CREATE OR REPLACE VIEW SingerNames

SQL SECURITY INVOKER AS

SELECT

CAST(Singers.SingerId AS INT64) AS SingerId,

CAST(Singers.FirstName AS STRING) || " " || CAST(Singers.LastName AS STRING) AS Name

FROM Singers;

Choosing the Right Primary Key

When a table's primary key increases or decreases sequentially, it can result in a single node handling all writes, creating a hotspot. Therefore, it's crucial to select a primary key that distributes evenly across the table splits.

Using UUIDs (Universally Unique Identifiers) as primary keys is an excellent choice. UUIDs are long, randomly generated strings. UUID version 1 uses timestamps for generation, which can create monotonic increases, making it unsuitable. Instead, UUID version 4 employs random numbers, ensuring they do not increase monotonically and are ideal for Spanner primary keys.

Sometimes, combining timestamps with other fields can create a composite primary key. Additionally, hashing monotonic keys can also yield an effective primary key for Spanner.

Conclusion

With Cloud Spanner, you have the capability to build real-time, mission-critical applications at scale. It is a fully managed relational database offering unlimited scalability, strong consistency, and availability of up to 99.999%. Utilizing TrueTime, it provides a global time reference, allowing for horizontal scaling while maintaining strong external consistency across rows, regions, and continents. You can deliver data with low latency and a cohesive experience, supporting all the features expected from a relational database, including schemas, SQL queries, and ACID transactions. Spanner ensures data encryption at rest and in transit, ensuring complete security. It is an excellent choice for high-scale online transaction processing and real-time decision-making workloads. When you aim to deliver mission-critical applications at scale with minimal maintenance, consider exploring Cloud Spanner!

Keep Learning, Keep Growing!!!

References

1. Cloud Spanner
2. Spanner Databases
3. Create and Manage Databases in Spanner
4. Spanner for Non-Relational Workloads