Top Five Google Cloud Database Services — Part 1 (SQL)

Google Cloud Platform offers various cloud database tools and services based on what a company needs to accomplish with them. Thus, the different tools have different purposes.

In this article, we'll look at the top five Google Cloud database services/tools that support SQL in one form or another. I'll take you through each of their features and benefits and provide some code snippets. Above all, I'll briefly explain when you can use them. Here is what we will cover:

1. Cloud SQL

Cloud SQL is a tool that enables you to manage all your relational databases, supports MySQL and PostgreSQL, and offers support for SQL Server. The tool eliminates the need for manual management and has robust features that make your process hassle-free.

Features

• Cloud SQL automates all major DBMS-oriented processes, such as cloud backups, replication, and encryption patches. It also ensures that your database scales automatically so there's no downtime, and you can perform all your tasks without any form of interruption.
• You can easily connect your database systems with App Engine, Google Kubernetes Engine, and the Compute Engine based on your requirements. The platform also allows you to work with on-premise systems and data.
• The platform complies with major standards such as ISO 27001, SSAE 16, and PCI DSS. This way, it will always be in sync with the industry.
• Besides cloud backups, one can also automate the failover process and perform cloud migration with minimal downtime. Since it is serverless, various manual operations such as managing, monitoring, and provisioning aren't required either.

Benefits

• Highly secure.
• Easily scalable.
• Hassle-free setup.

When to Use

Cloud SQL is used when the storage requirement is less than 10TB. It performs end-to-end relational database management for all your systems as long as it is within this limit.

Sample Code

A nested query in Cloud SQL will look something like the code given below:

SELECT firstname, lastname, total_quantity
    FROM (SELECT buyerid, sum(qtysold) total_quantity
            FROM sales
            GROUP BY buyerid
            ORDER BY total_quantity desc limit 10) Q, users
    WHERE Q.buyerid = userid
    ORDER BY Q.total_quantity desc;

2. Cloud Spanner

Cloud Spanner provides all the functions of Cloud SQL and goes even further by providing 99.999% availability at all times. Furthermore, it provides higher row consistency, and the performance is much more enhanced.

Features

• Cloud Spanner enables the availability mentioned above across multiple regions and has much lesser downtime compared to four nines.
• The performance is elevated because it shares the data based on the load and size. This way, scaling becomes more efficient and requires lesser human intervention.
• Data is consistent across multiple regions. This means no matter where the users are from and how many users are simultaneously working on the database, the changes are up-to-date and consistent.
• Once you choose a granular instance, you don't need to re-architecture or worry about scaling it.

Benefits

• Automated scaling without any limits.
• Available across the world at any time.
• Simple experience and better performance.

When to Use

As mentioned earlier, Cloud Spanner provides better performance and higher availability than Cloud SQL. Furthermore, it has no storage limit like Cloud SQL. So if your infrastructure requires all this and more, you should use Cloud Spanner.

Sample Code

Here instead of selecting or retrieving data from the table, you can emulate a temporary table name using the WITH clause.

WITH Roster AS
 (SELECT 'Adams' as LastName, 50 as SchoolID UNION ALL
  SELECT 'Buchanan', 52 UNION ALL
  SELECT 'Coolidge', 52 UNION ALL
  SELECT 'Davis', 51 UNION ALL
  SELECT 'Eisenhower', 77)

The above command would create this table:

3. AlloyDB for PostgreSQL

AlloyDB is one of the latest tools launched by Google: the tool sets itself apart from Cloud SQL and Cloud Spanner. This is because the latter two are compatible with PostgreSQL, whereas AlloyDB is a standard PostgreSQL database at its core.

Features

• AlloyDB is 4x faster for transactional workloads and 10x faster for queries compared to the standard PostgreSQL. It is twice as fast as Amazon's Aurora PostgreSQL for transactional workloads.
• Like Cloud Spanner, it has 99.999% availability across all regions.
• The tool also allows moving legacy databases to their cloud with ease. This way, it tries to make its infrastructure more open-source and eliminates issues such as licensing and other boundaries.
• Furthermore, with Cloud SQL and Cloud Spanner, one has to integrate BigQuery for better analytics. With AlloyDB, users won't have to do the same as the functionalities are in-built and readily available.
• It also has built-in Vertex AI, which enables users to leverage AI and machine learning models for analytics.

Benefits

• The tool showcases elevated performance and efficiency, which is better than Cloud Spanner, Cloud SQL, and Amazon's Aurora PostgreSQL.
• A fit for AI and ML-powered systems.
• Pricing is transparent and predictable without charges for licensing or opaque I/O.

When to Use

If your organization and infrastructure need a more open-sourced way of functioning with maximum performance, efficiency, and features, then AlloyDB is your go-to Google tool.

Sample Code

Since AlloyDB is a PostgreSQL-compatible tool, the sample code is of the same query language. The query below will help you to insert values into a table.

postgres=# insert into dummy_table values('XYZ','location-A',25);
        INSERT 0 1
        postgres=# insert into dummy_table values('ABC','location-B',35);
        INSERT 0 1
        postgres=# insert into dummy_table values('DEF','location-C',40);
        INSERT 0 1
        postgres=# insert into dummy_table values('PQR','location-D',54);
        INSERT 0 1

4. Bare Metal Solution for Oracle

A Bare Metal Solution is essential for organizations that have specialized workloads and involve the usage of highly sophisticated services but find it difficult to leverage the usual cloud. Google provides certified equipment for these workloads and places it in the data centers that run the cloud services.

This way, organizations can move to the cloud and use these high-intensive services that are integrated with the usual cloud services.

Features

• With a minimal 2ms latency, organizations can use cloud services.
• All major Oracle capabilities include database clustering, replication, and more.
• The tool acts as a bridge between on-premises legacy systems and the cloud.
• It also enables integration with services such as Ansible Based Toolkit and Kubernetes Operator for Oracle.

Benefits

• Organizations can move their legacy-based infrastructure to the cloud without much hassle.
• There's minimal latency while using various services.
• Allows access to all Oracle capabilities, such as RAC and RMAN.

When to Use

This solution mainly applies to organizations with infrastructures rooted in Oracle. It enables them to modernize their whole setup by moving it to the cloud. It would also help companies to avoid being locked in and enable them to use functionalities from different vendors.

Sample Code

Since databases are connected with GCP database tools, you can use any query language depending on the database tool. Here I'll share a sample code for those running MySQL.

CREATE TABLE shop (
    article INT UNSIGNED  DEFAULT '0000' NOT NULL,
    dealer  CHAR(20)      DEFAULT ''     NOT NULL,
    price   DECIMAL(16,2) DEFAULT '0.00' NOT NULL,
    PRIMARY KEY(article, dealer));
INSERT INTO shop VALUES
    (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
    (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);

5. BigQuery

BigQuery was launched to handle analytics dealing with millions of rows. As mentioned in the previous section, it is mainly used with Cloud SQL and Cloud Spanner for the same purpose since they have little-to-no analytical functionalities for such a scale.

Features

• The tool enables natural language processing with the help of integrations such as Data Q&A and Analyze. This allows people to work with data from chatbots, spreadsheets, and other custom-built UIs.
• Like AlloyDB, even BigQuery integrates Vertex AI. It also has TensorFlow integrated, and with these two, organizations can create custom AI/ML models of extensive complexity with just SQL.
• BigQuery is one of the fundamental elements of Business Intelligence solutions that provide operations such as transformation, analysis, visualization, and reporting.
• The tool provides real-time data capturing, analytics, and replication, which helps organizations make quicker decisions, thus increasing their performance and efficiency.

Besides all these, other robust features help companies work with petabytes of structured and unstructured data, allowing geospatial analytics, fine-grained data governance, and security on a row-based and column-based level.

Benefits

• All data analytics operations reside in one place.
• Ability to handle intensive data capturing and analysis for large-scale databases.
• Real-time operations across different verticals.

When to Use

The tool can be used by large organizations that require real-time operations on petabytes of data. Its connectivity with other Google Cloud Database solutions, such as CloudSQL and Cloud Spanner, enables all kinds of operations, from the most basic to the most sophisticated.

Sample code can be found below:

WITH locations AS
  (SELECT STRUCT("Seattle" AS city, "Washington" AS state) AS location
  UNION ALL
  SELECT STRUCT("Phoenix" AS city, "Arizona" AS state) AS location)
SELECT l.location.*
FROM locations l;

In this case, we've used array structures in a nested query. While this is merely a sample, with BigQuery, you perform much more complex operations,  and hence the queries get complex too.

Conclusion

These are some of the most popular Google Cloud Database tools organizations use for their infrastructure. The code samples explain one simple task, and you can explore further by looking at the official Google Documents and Github. There are more SQL tools offered by Google, which we'll cover in this article's second part.