Picking the right database for your application is not easy. The choice depends heavily on your use case—transactional processing, analytical processing, in-memory database, and so on—but it also depends on other factors. This post covers the different database options available within Google Cloud across relational (SQL) and non-relational (NoSQL) databases and explains which use cases are best suited for each database option. 

Relational databases 

In relational databases information is stored in tables, rows and columns, which typically works best for structured data. As a result they are used for applications in which the structure of the data does not change often. SQL (Structured Query Language) is used when interacting with most relational databases. They offer ACID consistency mode for the data, which means:

• Atomic: All operations in a transaction succeed or the operation is rolled back.
• Consistent: On the completion of a transaction, the database is structurally sound.
• Isolated: Transactions do not contend with one another. Contentious access to data is moderated by the database so that transactions appear to run sequentially.
• Durable: The results of applying a transaction are permanent, even in the presence of failures.

Because of these properties, relational databases are used in applications that require high accuracy and for transactional queries such as financial and retail transactions. For example: In banking when a customer makes a funds transfer request, you want to make sure the transaction is possible and it actually happens on the most up-to-date account balance, in this case an error or resubmit request is likely fine.

There are three relational database options in Google Cloud: Cloud SQL, Cloud Spanner, and Bare Metal Solution.

Cloud SQL: Provides managed MySQL, PostgreSQL and SQL Server databases on Google Cloud. It reduces maintenance cost and automates database provisioning, storage capacity management, back ups, and out-of-the-box high availability and disaster recovery/failover. For these reasons it is best for general-purpose web frameworks, CRM, ERP, SaaS and e-commerce applications.

Cloud Spanner: Cloud Spanner is an enterprise-grade, globally-distributed, and strongly-consistent database that offers up to 99.999% availability, built specifically to combine the benefits of relational database structure with non-relational horizontal scale. It is a unique database that combines ACID transactions, SQL queries, and relational structure with the scalability that you typically associate with non-relational or NoSQL databases. As a result, Spanner is best used for applications such as gaming, payment solutions, global financial ledgers, retail banking and inventory management that require ability to scale limitlessly with strong-consistency and high-availability. 

Bare Metal Solution: Provides hardware to run specialized workloads with low latency on Google Cloud. This is specifically useful if there is an Oracle database that you want to lift and shift into Google Cloud. This enables data center retirements and paves a path to modernize legacy applications. 

Non-relational databases

Non-relational databases (or NoSQL databases) store compex, unstructured data in a non-tabular form such as documents. Non-relational databases are often used when large quantities of complex and diverse data need to be organized. Unlike relational databases, they perform faster because a query doesn’t have to access several tables to deliver an answer, making them ideal for storing data that may change frequently or for applications that handle many different kinds of data. 

For example, an apparel store might have a database in which shirts have their own document containing all of their information, including size, brand, and color with room for adding more parameters later such as sleeve size, collars, and so on.

Qualities that make NoSQL databases fast:

• Eventual consistency: stores usually exhibit consistency at some later point (e.g., lazily at read time)
• Horizontal scaling, usually using hashed distributions
• Typically, they are optimized for a specific workload pattern (i.e., key-value, graph, wide-column)
• Typically, they don’t support cross shard transactions or flexible isolation modes.

Because of these properties, non-relational databases are used in applications that require large scale, reliability, availability, and frequent data changes.They can easily scale horizontally by adding more servers, unlike some relational databases, which scale vertically by increasing the machine size as the data grows. Although, some relations databases such as Cloud Spanner support scale-out and strict consistency.

Non-relational databases can store a variety of unstructured data such as documents, key-value, graphs, wide columns, and more. Here are your non-relational database options in Google Cloud: 

• Document databases: Store information as documents (in formats such as JSON and XML). For example: Firestore
• Key-value stores: Group associated data in collections with records that are identified with unique keys for easy retrieval. Key-value stores have just enough structure to mirror the value of relational databases while still preserving the benefits of NoSQL. For example: Datastore, Bigtable, Memorystore
• In-memory database: Purpose-built database that relies primarily on memory for data storage. These are designed to attain minimal response time by eliminating the need to access disks. They are ideal for applications that require microsecond response times and can have large spikes in traffic. For example: Memorystore
• Wide-column databases: Use the tabular format but allow a wide variance in how data is named and formatted in each row, even in the same table. They have some basic structure while preserving a lot of flexibility. For example: Bigtable
• Graph databases: Use graph structures to define the relationships between stored data points; useful for identifying patterns in unstructured and semi-structured information. For example: JanusGraph

There are three non-relational databases in Google Cloud:

• Firestore: Is a serverless document database which scales on demand and acts as a backend-as-a-service. It is DBaaS that increases the speed of building applications. It is perfect for all general purpose uses cases such as ecommerce, gaming, IoT and real time dashboards. With Firestore users can interact with and collaborate on live and offline data making it great for real-time application and mobile apps.  
• Cloud Bigtable: Cloud Bigtable is a sparsely populated table that can scale to billions of rows and thousands of columns, enabling you to store terabytes or even petabytes of data. It is ideal for storing very large amounts of single-keyed data with very low latency. It supports high read and write throughput at sub-millisecond latency, and it is an ideal data source for MapReduce operations. It also supports the open-source HBase API standard to easily integrate with the Apache ecosystem including HBase, Beam, Hadoop and Spark along with Google Cloud ecosystem.
• Memorystore: Memorystore is a fully managed in-memory data store service for Redis and Memcached at Google Cloud. It is best for in-memory and transient data stores and automates the complex tasks of provisioning, replication, failover, and patching so you can spend more time coding. Because it offers extremely low latency and high performance, Memorystore is great for web and mobile, gaming, leaderboard, social, chat, and news feed applications.

Conclusion

Choosing a relational or a non-relational database largely depends on the use case. Broadly, if your application requires ACID transactions and your data structure is not going to change much, select a relational database. 

In Google Cloud use Cloud SQL for any general-purpose SQL database and Cloud Spanner for large-scale globally scalable, strongly consistent use cases. In general, if your data structure may change later and if scale and availability is a bigger requirement than consistency then a non-relational database is a preferable choice.  Google Cloud offers Firestore, Memorystore, and Cloud Bigtable to support a variety of use cases across the document, key-value, and wide column database spectrum.

For more comparison resources on each database check out the overview. For more hands-on experience with Bigtable, check out our on-demand training here and learn about migrating databases to managed services check out this whitepaper.  

https://youtube.com/watch?v=2TZXSnCTd7E%3Fenablejsapi%3D1%26

For more #GCPSketchnote, follow the GitHub repo. For similar cloud content follow me on Twitter @pvergadia and keep an eye out on thecloudgirl.dev.

As CentOS 7 reaches end of life, many enterprises are considering their options for an enterprise-grade, downstream Linux distribution on which to run their production applications. Rocky Linux has emerged as a strong alternative that, like CentOS, is 100% compatible with Red Hat Enterprise Linux.

In April 2022, we announced a customer support partnership with CIQ, the official support and services partner and sponsor of Rocky Linux, as the first step in providing a best-in-class enterprise-grade supported experience for Rocky Linux on Google Cloud. Today we’re excited to announce the general availability of Rocky Linux Optimized for Google Cloud. We developed this collection of Compute Engine virtual machine images in close collaboration with CIQ so that you get optimal performance when using Rocky Linux on Compute Engine to run your CentOS workloads.

These new images contain customized variants of the Rocky Linux kernel and modules that optimize networking performance on Compute Engine infrastructure, while retaining bug-for-bug compatibility with Community Rocky Linux and Red Hat Enterprise Linux. The high bandwidth networking enabled by these customizations will be beneficial to virtually any workload, and are especially valuable for clustered workloads such as HPC (see this page for more details on configuring a VM with high bandwidth).

Going forward, we’ll collaborate with CIQ to publish both the community and Optimized for Google Cloud editions of Rocky Linux for every major release, and both sets of images will receive the latest kernel and security updates provided by CIQ and the Rocky Linux community. And of course, we’ll offer support with CIQ for both these images, per our partnership.

Rocky Linux Optimized for Google Cloud lets you take advantage of everything Compute Engine has to offer, including day-one support for our latest VM families, GPUs, and high-bandwidth networking. And for customers building for a multi-cloud deployment environment, the community Rocky images have you covered.

Starting today, Rocky Linux 8 Optimized for Google Cloud is available for all x86-based Compute Engine VM families (and soon for the new Arm-based Tau T2A), with version 9 soon to follow. Give it a try and let us know what you think.

Do hamburgers really come from Hamburg? This may still be a matter of debate, but the popularity of American-style burger joints not just in Hamburg but all over Germany, is clear. Germany’s top two fast food companies are both burger chains. One of them is BURGER KING®, a global brand that welcomes more than 11 million customers worldwide every day. The company arrived in Germany in 1976, when its first restaurant opened in Berlin. It now operates more than 100 restaurants across Germany, with franchisees operating more than 600 restaurants of their own.

“In the fast food industry, being able to move quickly is very important. That means delivering the right promotion to our app or launching a viral campaign within days. To do that across more than 700 restaurants, we need the support of the right technology.”

—Oliver Mielentz, IT Manager, BURGER KING® Deutschland GmbH

Previously a subsidiary of the U.S. business, BURGER KING® Germany became an independent company in 2015. As a result, it needed to develop its own IT infrastructure, and the changeover needed to happen fast. “We had to put in place systems that would work for the entire network of franchisees and enable us to easily roll out campaigns,” explains Oliver Mielentz, IT Manager at BURGER KING® Deutschland GmbH.

With the help of Google Cloud Premier partner Cloudwürdig, BURGER KING® Germany chose Google Cloud and G Suite as the right combination to suit its needs.

“In the fast food industry, being able to move quickly is very important,” says Oliver. “That means delivering the right promotion to our app or launching a viral campaign within days. To do that across more than 700 restaurants, we need the support of the right technology.”

Building a franchisee platform in just three months

When a business has multiple franchisees, it’s important to make sure everyone is on the same page, especially in the fast-paced fast food environment. “We have to collate data from all our franchisees and produce reports quickly in order to react to changes in customer behavior,” explains Oliver. “That means processing every transaction that takes place in our restaurants.” Following the restructure, BURGER KING® Germany also needed to build a secure invoicing system with data storage and optimize its communication channels.

“Using Tableau with BigQuery, we’re able to produce reports very quickly. Previously, it took much longer, as data had to be fetched manually. Our reaction time is now almost a business day faster.”

—Oliver Mielentz, IT Manager, BURGER KING® Deutschland GmbH

With support from Witter-IT, BURGER KING® Germany chose Cloudwürdig to build its BKD Connect internal platform on Google Cloud. Thanks to the ready-to-go tools on Google Cloud, it was able to put its invoicing system and data warehouse in place in just three months.

For the BURGER KING® Germany data warehouse, Cloudwürdig built an ETL pipeline that channels ticket data for every sale into BigQuery. “Data is gathered from the restaurants,” says Oliver, “and using Tableau with BigQuery, we’re able to produce reports very quickly. Previously, it took much longer, as data had to be fetched manually. Our reaction time is now almost a business day faster.”

As the ticket data for every transaction is stored in BigQuery, the marketing team can easily see exactly which products are selling well. That’s crucial for tweaking promotions as well as monitoring the supply chain to make sure enough produce is delivered to restaurants in response to changes in demand.

“Thanks to BigQuery, we have a speedy data pipeline that enables us to react on the same day to changes in the market and eliminate bottlenecks in production,” says Oliver.

Switching to G Suite to improve communication

To enable franchisees to sign in to its BKD Connect Platform, BURGER KING® Germany needed a secure authentication system. To solve that problem, it chose to provide franchisees with G Suite accounts. “It’s really easy to set up a new franchisee on the platform. I just create a new G Suite account and Drive folder for it, and it’s ready to go,” says Oliver. G Suite also helps the franchise network to run efficiently, as daily reports are automatically saved to Drive and shared to the appropriate regional network. “Thanks to that system, it’s much easier for any team at headquarters to access the information it needs,” Oliver explains.

BURGER KING® Germany also recently extended its use of G Suite across the whole company. “Following an evaluation of our previous email and productivity software, I made the decision to switch solely to G Suite,” says Oliver. BURGER KING® Germany employees now use Gmail, Calendar, and Drive for their day-to-day productivity needs. “We only just completed the migration, but already, everyone’s happy,” says Oliver. “It’s so easy to share a file using Drive or set up a meeting on Calendar.”

“We’re big fans of Hangouts Meet, and we have two rooms here at our Hanover headquarters equipped with Hangouts Meet hardware,” Oliver adds. “The speech quality is good, and it’s helpful to be able to see every participant, especially when you’re running a meeting with multiple franchisees.”

Optimizing infrastructure to power innovative campaigns

The BURGER KING® app, available for iOS and Android, helps the company to deliver a great customer experience. Through their MyBK accounts, guests can access coupons and special promotions. “We had a really interesting campaign for Easter: guests used the app to hunt for virtual Easter eggs,” explains Oliver. “We knew it was going to be big, and our previous back end wouldn’t have been able to handle the traffic.”

To enable the marketing campaign to go ahead, BURGER KING® Germany moved the back end of the app, along with its website, to Google Cloud. For developing and running its web and app back ends, it now uses App Engine and virtual machines on Compute Engine, as well as Memorystore and Cloud Functions. For monitoring and logging, it uses Stackdriver, and Cloud CDN and Cloud DNS to easily handle its traffic.

“We ran the campaign without any performance issues, even though we were receiving several million hits a day,” says Oliver. Since migrating the back end to Google Cloud, the marketing team also launched the popular “Escape the Clown” campaign. “That campaign blew our minds!” says Oliver. “It wouldn’t have been possible without Google Cloud, because it required a lot of back end capacity.”

To develop the app infrastructure it needs, BURGER KING® Germany relies on Cloudwürdig. “Working with Cloudwürdig is great because the team has the same agile mindset as us,” says Oliver. “When we have a new idea, we just set up a meeting, and in a couple of days the new infrastructure is in place. For Escape the Clown, it only took a few weeks to get everything ready to launch.”

Leveraging integrated tools to grow the business

Using Google Cloud together with G Suite enables BURGER KING® Germany to run its franchise network efficiently, while keeping its IT team lean. “Google Cloud and G Suite are the perfect fit for the way of working at BURGER KING® Germany,” says Oliver. “Many of the company’s operatives are often on the road, visiting restaurants and franchisees. With these tools, they can work flexibly and react quickly to the situation on the ground.”

“In order to grow the business, we need to use the data we receive every day to understand exactly what is happening in our restaurants. With the tools provided by Google Cloud, we can get more guests through the door and offer them a better experience.”

—Oliver Mielentz, IT Manager, BURGER KING® Deutschland GmbH

It also helps to keep infrastructure costs under control. “With Google Cloud, we only pay for what we use, which is really important for us,” Oliver explains. “It means we can scale up quickly if we see an opportunity to react to a trend in customer behavior and launch a new marketing campaign that resonates with the moment. When it’s finished, we can then scale down again, and that definitely saves us money.”

BURGER KING® is now working with Cloudwürdig to add more functionality to the BURGER KING® app using Google Kubernetes Engine. “We like to work with customers long-term to support their digital transformation. BURGER KING® Germany is a great example of how one project can develop into a great collaboration,” says Benny Woletz, Managing Director of Cloudwürdig.

BURGER KING® also plans to expand its presence in Germany and gain a greater market share by further tailoring both its marketing and the way it runs its restaurants to answer its guests’ needs. “In order to grow the business, we need to use the data we receive every day to understand exactly what is happening in our restaurants,” says Oliver. “With the tools provided by Google Cloud, we can get more guests through the door and offer them a better experience.”

As we become increasingly reliant on technology to make decisions, geospatial data is becoming more critical than ever. It is a powerful resource that can be used to solve a variety of problems, from tracking the movement of goods, identifying interest areas and potential areas of disaster.

Geospatial data incorporates data with a geographic component, such as latitude and longitude coordinates, addresses, postal codes, or place names, and can be obtained from a variety of sources, including satellites, sensors, and surveys, making it an incredibly powerful tool with a broad range of applications.

One of the key features of BigQuery, Google Cloud’s serverless enterprise data warehouse, is its ability to analyze geospatial data. However, oftentimes geospatial data sits in a variety of public clouds, not just Google Cloud. To access it effectively, you need a multi-cloud analytics solution that lets you capitalize on the distinct capabilities of each cloud platform, while extracting insights and value from data sitting across multiple cloud platforms.

BigQuery Omni is a multi-cloud analytics solution that enables the analysis of data stored across public cloud environments, including Google Cloud, Amazon Web Services (AWS) and Microsoft Azure, without the need to transfer the data. With BigQuery Omni, users can employ the same SQL queries and tools used to analyze data in Google Cloud to analyze data in other clouds, making it easier to gain insights from all data, regardless of the storage location. For businesses using multiple clouds, BigQuery Omni is an excellent tool to unify analytics and optimize the value of data.

With BigQuery Omni, organizations can analyze location-based information or geographic components, such as latitude and longitude coordinates, addresses, postal codes, or place names without even copying their data to Google Cloud. For example, you could use BigQuery Omni to analyze data from a fleet of delivery vehicles to track their location and identify potential problems.

BigQuery Omni and geospatial data analysis

If you are working with geospatial data, BigQuery Omni is a powerful tool that can help you to get insights from your data. It is scalable, reliable, and secure, making it a great option for unifying your analytics and getting the most out of your data. 

Here are a few examples of ways organizations might use BigQuery Omni for geospatial data:

• A transportation company could use BigQuery Omni to analyze data from GPS sensors in its vehicles to track the movement of its fleet and identify potential problems.
• A retail company could use BigQuery Omni to analyze data from its point-of-sale systems to track customer behavior and identify trends.
• A government agency could use BigQuery Omni to analyze data from its weather sensors to track the movement of storms and identify areas at risk of flooding.

BigQuery Omni and geospatial data can be used together to gain insights into a variety of business problems. Specifically, some of the advantages of using BigQuery Omni and geospatial data include:

• Access to quality geospatial data: BigQuery supports loading of newline-delimited GeoJSON files and provides built-in support for loading and querying geospatial data. Data from public data sources like BigQuery public datasets, the Earth Engine catalog, and the United States Geological Survey (USGS) can be easily integrated into your BigQuery environment. Earth Engine has an integrated data catalog with a comprehensive collection of analysis-ready datasets, including satellite imagery and climate data. This data can be combined with proprietary data sources such as SAP, Oracle, Esri ArcGIS Server, Carto, and QGIS.
• Loading and preprocessing of geospatial data: BigQuery has built-in support for loading and querying geospatial data types, and you can use partner solutions such as FME Spatial ETL to load data.
• Working with different geospatial data types and formats: BigQuery supports a variety of file types and formats including WKT, WKB, CSV and GeoJSON.
• Coordinate reference systems: BigQuery’s geography data type is globally consistent. That means that your data is registered to the WGS84 reference system and your analyses can span a city block or multiple continents.

Overall, geospatial analytics with BigQuery Omni provides a wide range of technical capabilities for processing and analyzing geospatial data, making it a powerful tool for businesses that need to work with location-based data.

Analyzing geospatial data with BigQuery Omni

Imagine a retailer who has a large chain of department stores with locations all over the country. They are looking to expand their business and want to identify areas with high sales potential. They want a way to get a better understanding of their sales volume within specific geographic boundaries. To achieve this goal, the retailer turns to the GIS (Geographic Information System) functions built into BigQuery. Here are the steps what the retailer takes to analyze this dataset:

Step 1 : An initial orders dataset on AWS S3 contains 5.54 million rows, and with separate locations (300 rows) and zipcode (33144 rows) metadata files on AWS S3.

Orders Parquet files:

Location and Zipcode files:

Step  2 : The retailer uses BigQuery Omni to establish a connection between the data stored in AWS and BigQuery, enabling them to access the S3 datasets externally.

External Connection for AWS

External Table for orders

External Table for locations

External Table for zipcode

Step 3 : They combine the orders and locations datasets using BigQuery Omni, and remotely aggregate the data on AWS. Joining this dataset with geospatial datasets helps them derive geospatial coordinates.

The final aggregated dataset is reduced to 23 rows. Subsequently, they bring back the result dataset, which contains just 23 rows. This helps them reduce the extraction of millions of rows to just 23 rows for their geospatial analytics.

Select sales.store_city store_city,
sales.number_of_sales_last_10_mins number_of_sales_last_10_mins,
sales.store_zip store_zip,
 ST_GeogPoint(zip_lat_lng.longitude ,
   zip_lat_lng.latitude  ) geo
FROM (
 SELECT
   FORMAT_DATETIME("%X",
     CURRENT_DATETIME("America/Los_Angeles")) current_time,
   MAX(DATETIME(time_of_sale,
       "America/Los_Angeles")) time_of_last_sale,
   COUNT(1) number_of_sales_last_10_mins,
   locations.city store_city,
   locations.zip store_zip
 FROM
   `bqomni-blog.aws_locations.orders_small`  sales
 JOIN
   `bqomni-blog.aws_locations.locations` locations
 ON
   sales.store_id = locations.id
 GROUP BY
   locations.city,
   locations.zip ) sales
JOIN
   `bqomni-blog.aws_locations.zipcode` zip_lat_lng
ON
 cast(sales.store_zip as INT) = cast(zip_lat_lng.zipcode as INT)
 WHERE ST_WITHIN( ST_GeogPoint(zip_lat_lng.longitude , zip_lat_lng.latitude  ) ,ST_GeogFromText(zip_lat_lng.zipcode_geom ) )
 AND zip_lat_lng.state_name  = "New York"
 ORDER BY number_of_sales_last_10_mins

Aggregated Sales data by region

To build richer views of their sales volume data, the retailer uses BigQuery GeoViz integration, a powerful tool that allows for visualizing geographic data on maps.BigQuery Geo Viz is a web tool for visualization of geospatial data in BigQuery using Google Maps APIs. You can run a SQL query and display the results on an interactive map

BigQuery Geo view for sales data analysis

With the geo-tagged data in place, the retailer can now see regional sales volume, sales density, distribution by department by time, and distribution within department, all powered through BigQuery.

Satellite view

Benefits of using BigQuery Omni 

Beyond geospatial analysis, BigQuery Omni offers a number of benefits, including:

Reduced costs: BigQuery Omni’s ability to eliminate data transfers between clouds can help organizations reduce costs and simplify data management, making it a valuable tool for multi-cloud analytics. Also, the ability to access and analyze data across multiple clouds can reduce the need for data replication and synchronization, which can further simplify the ETL process and improve data consistency.

Unified governance: BigQuery Omni uses the same security controls as BigQuery, which include features such as encryption, access controls, and audit logs, to help protect data from unauthorized access.

Single pane for analytics: BigQuery Omni provides a single interface for querying data across all three clouds, which can simplify the process of analyzing data and reduce the need for organizations to use multiple analytics tools.

Flexibility: Analyze data stored in any of the supported cloud storage services, giving organizations the flexibility to work with the data they have regardless of where it’s located.

BigQuery Omni is a valuable tool for geospatial analysis because it allows you to analyze data from multiple sources without having to move the data. This can save you time and money, and it can also help you to get more accurate insights from your data.If you are looking for a way to improve the accuracy, efficiency, and decision-making of your business, using BigQuery Omni to analyze geospatial data can be a powerful tool.

References

• Omni-introduction 
• Omni-benefits 
• Omni-aws-create-connection
• Omni-azure-create-connection 
• BigQuery Omni Retail Demo on Looker
• Omni-pricing 
• Working with Geospatial Data
• Geospatial Analytics – Intro

Learn more about how BigQuery Omni can help your organization.