Google Cloud Spanner
What is Google Cloud Spanner?
Spanner is a NewSQL database. NewSQL databases combine the scalability and high availability of NoSQL with a relational model, transactional support, and SQL of RDBMS. Spanner was made available in the Google Cloud Platform in February 2017. Spanner is a fully managed, globally distributed, highly consistent database service and is specifically built from a cloud/distributed design perspective. Spanner separates compute resources from data storage, which makes it possible to increase, decrease, or reallocate the pool of processing resources without any changes to the underlying storage.
Spanner uses:
· Paxos algorithm as part of its operation to shard/partition data across servers.
·TrueTime for external consistency, consistent reads without locking, and consistent snapshots.
Cloud Spanner:
· is a distributed database
· is a relational database
· is an ACID compliant database
· is a SQL database
· Technically CP and effectively CA assumes 99.999% availability (CAP theorem)
As an OLTP solution, Google Spanner is ideal for workloads supported by traditional relational databases, e.g. inventory management and financial services as well as Ad tech, Global Supply Chain and Retail and Inventory Management.
Photo by daniel-torobekov on pexels
Understanding Spanner
Instance
A Cloud Spanner instance contains one or more databases and has allocated resources (compute and storage) that are used by all its databases.
The resources are allocated during instance creation and are controlled by two main configurations. These configurations decide the compute and the way data is replicated, stored, and computed. The node allocation determines the compute and the storage.
Cloud Spanner instances automatically replicate the data based on the instance configuration chosen. There are two options available: Regional and Multi-Regional. Spanner distribute the data based on the nodes allocated and split the data along the primary key dimension.
In a Regional configuration, Cloud Spanner maintains 3 read write replicas, each within a different zone in that region. Each read write replica contains a full copy of the operational database. Regional provides safety against failure of an entire zone.
Whereas in a Multi-Regional configuration, data is replicated across four zones in different regions, which are determined by the continent selected by the user. Multi-Region provides higher availability guarantees by providing safety against failure of an entire region itself.
The Regional configuration implies that the data will be replicated across three zones within a region. A five-node instance means within each zone, the data will be distributed across five nodes.
Below is the Google Spanner instance creation page in the GCP console. Under the configuration option, we can select Regional or Multiregion as well as specify the number of nodes to allocate.
Data Modelling
Cloud Spanner is kind of similar to any relational database management system.
Once we create an instance, next step is to create database and tables for data storage. We can specify whether columns in the table can be NULL or not.
Primary Keys
While creating tables in Spanner, we need to create primary key. Similar to RDBMS, Spanner’s primary key can uniquely locate any data row. It can contain single column or multiple columns.
Spanner being distributed automatically partition the data and distribute across the allotted nodes. The partitioning done along the primary key dimension.
Hotspotting
Hotspotting happens when say out of the 3 nodes, only one node ends up doing all the work in terms of reads and writes. Let’s say Split 1 has rows from Primary Key 1 to 100.Split 2 has rows from Primary Key 101 to 200. Split 3 has primary keys 201 onwards… New row inserted with Primary Key 305 it gets into Split3. 310 similarly gets into Split3. So on….Hence all the writes happening on only one node, impacting the performance.
In order to avoid this scenario, we need to select the Primary Keys carefully.
· Well distributed Primary Key
· Creating a column containing the hash of the actual unique key and then using this hash column as the primary key.
· UUID as the primary key
Splits
The partition of data at a point along the primary key dimension is the Split. As the part of the Split, the Root table row and all its descendants are in one logical group.
Cloud Spanner organizes rows of data in a table by the Primary Key. Based on the size of the tables and load patterns, the data gets divided into splits. Splits are the distinct ranges of records of data based on the Primary Key.
There is always only one node that controls the writes to a split. This is to ensure strong consistency.
Cloud Spanner is a distributed system, with many nodes reading and writing to the database at any one time. In order to achieve scalability along with ACID transactions and strong consistency, only one node at any one time can have the write responsibility for a given row.
Interleaving
How related tables created in Spanner for efficient querying.
Based on the querying data pattern, tables are created to store the data. When tables are related and are frequently queried together, we can define the parent child relationship between the tables handling relationships as well as enabling efficient retrieval of data. When data is stored together querying becomes efficient. The relationship is defined by creating the child table as an interleaved table of the parent table.
For example, dept is the parent and emp is the child table since it has dept id as a reference to the dept table.
The child tables are the interleaved tables. Parent tables are the root tables. This enables fast querying as data that is queried together stored together.
“On Delete Cascade” is any row deleted in parent record will delete all the corresponding child records as well.
“On Delete No Action” is when we first need to delete all the child records before we can delete the parent record.
Interleaving the indexes has the benefits of collocating the related rows near to each other.
Secondary Indexes:
When we query data by the primary key, faster querying happens. When we query on non-key fields result in full table scan leading to slower query performance. Spanner enables creation of secondary indexes on the non key fields.
SELECT empid FROM emp WHERE ename = “Alice J”;
Storing clause enables to copy the column’s data in the index resulting in quicker fetch of the needed data.
Indexes make queries faster by storing data in the format needed but pay the cost of having to update them as and when data changes. Indexes are storing additional data as needed to avoid table scans.
During Insert/Update and Delete both table and index data needed to be modified accordingly.
Replication
How data is distributed and replicated?
· Cloud Spanner instances automatically replicate the data based on the instance configuration chosen.
· Distribute the data based on the nodes allocated.
· Split the data along the primary key dimension.
· A node hosts one or more splits. Replication is at the split level.
Transactions:
Spanner supports two modes of transaction:
· Read/Write
· Read Only
Read/Write: Insert/Update/Deletes on the underlying data is enabled in this method of transaction. In order to maintain data consistency, locks are acquired.
Example…
The read acquires a shared lock. Within the database at any point in time, there can be multiple concurrent transactions running. This lock enables the other transactions to continue reading. It doesn’t block the other reads. When commit is initiated, it implies that the transaction is ready with the data to be applied back to the database. At this point it tries to acquire an exclusive lock on the datapoint. This blocks all the new read requests for the data and will wait for the existing locks to be cleared. With the exclusive lock, all the writes are applied. The amount field will display the updated/modified data.
The updates in Cloud Spanner is not in place update. Spanner uses MultiVersion Concurrent Control (MVCC) method to modify the data. This makes sure that the write do not overwrite the data. An immutable copy is created timestamped with the write’s transaction commit time.
Read Only
When only read and no write involved at the start of the transaction, a snapshot of the data as valid at that point in time and all the reads within the transaction returns the same value.
Cloud Spanner enables strong reads and stale reads.
Strong reads are the default reads that get the freshest data.
Stale reads enable to read past data.
Exact staleness reads data at a user specified timestamp t.
Bounded staleness where user specifies a bound say 10 seconds and when transactions run, spanner choses the newest timestamp within the bound. The read type is controlled by specifying the timestamp bound on the reads.
Since the second transaction TR2 started committing after the first transaction TR1 finishes, new changes are available in TR2.
The first transaction TR1 will be updating the data. TR2 will retry later point in time.
At any point of time if multiple concurrent transaction start, only one will be updating the data. The others retry later.
Cloud Spanner locks at cell level. Example below at Row#N, Col2 lock is acquired.
Since the columns are different even though Row is the same both transactions executed successfully.
TrueTime guarantees unique timestamp to each transaction. Specifically, every transaction is assigned a timestamp that reflects the instant at which Cloud Spanner considers it to have occurred.
Paxos algorithm is used to maintain one leader at a time and rest of the nodes will be the followers. Paxos is a family of distributed algorithms used to reach consensus.
[photo by Unsplash]
Interacting with Spanner via GCP console
· Creating an instance “testinstance”. Click on Spanner under Databases in GCP. Then click on the Create Instance. Once we are in the instance creation page, can provide the name, can chose a configuration as well as the number of nodes to allocate.
· Once testinstance created and available, next is creating the database “empdeptdb” as shown below.
· Creating tables department and employee. Below screenshots show how we can create tables, indexes, specifiy the NOT NULL constraint as well as altering table to add a new column.
· Populating both tables with data. Will go to the Query tab and write our inserts to load both the tables with data.
· Querying the tables. An index on ename created and the query plan shows index scan “enameidx”. JOIN keyword for joining department and employee tables. GROUP BY in employee table to count the number of employees grouping by department id.
· Interacting with Spanner via gcloud
>gcloud spanner instances list. Below one instance is displayed.
>gcloud spanner instances create testinstance1 — config=regional-asia-east1 — description=”Test Instance1" — nodes=1
>gcloud spanner instances list. Below two instances are displayed.
>gcloud spanner instances create testinstance2 — config=nam3 — description=”Test Instance2" — nodes=1
>gcloud spanner instances list. Below three instances are displayed.
>gcloud spanner instances update testinstance2 — description=”Test Instances 2" — nodes=3
>gcloud spanner instances list. Below three instances are displayed. The description is modified.
>gcloud spanner databases create testdb — instance=testinstance. New instance testdb gets created.
>gcloud spanner databases ddl update testdb — instance=testinstance — ddl=’create table customers(custid INT64 NOT NULL,custname STRING(1024) NOT NULL, email STRING(1024),country STRING(1024) NOT NULL) PRIMARY KEY (custid);’ Customers table created in the testdb database.
>gcloud beta spanner rows insert — instance=testinstance — database=testdb — table=customers — data=custid=5,custname=’Mary Jones’,email=’maryjones@yahoo.com’,country=’USA’. A row gets inserted in the customers table as shown below.
>gcloud spanner instances delete testinstance2. We can see below that testinstance2 is deleted.
Working with Spanner as the database from Python development environment
Created the department and employee table and performed insert/update/delete on the department table. The python library is google.cloud.spanner to connect to the spanner database.
Takeaways and some links
Cloud Spanner is a managed, mission-critical, globally consistent and scalable relational database service.
What makes Cloud Spanner a bit different than other database offerings is that it offers a combination of features found in traditional relational databases and NoSQL databases.
For developers, Google is offering a database that offers a lot of traditional features of a SQL-based database — an example is ACID transactions — but also one that can scale and support modern applications. There’s also support for programming languages such as Java, Go, Python and Node.js.
· Quizlet Tests Cloud Spanner: https://quizlet.com/blog/quizlet-cloud-spanner
· Build a real-time inventory management system with Cloud Spanner: https://www.youtube.com/watch?v=8kj_uA5vJfo&ab_channel=GoogleCloudPlatform
· Cloud Spanner in a minute: https://www.youtube.com/watch?v=5bjYk6Hhd10&ab_channel=GoogleCloudPlatform
