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.”

With summer just around the corner, things are really heating up. But you’re in luck because this month BigQuery is supplying a cooler full of ice cold refreshments with this release of user-friendly SQL capabilities. 

We are pleased to announce three categories of BigQuery user-friendly SQL launches: Powerful Analytics Features, Flexible Schema Handling, and New Geospatial Tools.

Powerful Analytics Features

These powerful SQL analytics features provide greater flexibility to analysts for organizing, filtering, and rendering data in BigQuery than ever before. You can enable spreadsheet-like functionality on summarized data using PIVOT and UNPIVOT and filter irrelevant data in analytic functions using QUALIFY.

Through this section, we will become familiar with these new features through examples using the BigQuery Public dataset, usa_names.

PIVOT/UNPIVOT (Preview)

One of the most time-consuming tasks for data analytics practitioners is wrangling data into the right shape. SQL is great for wrangling data, but sometimes you want to reformat a table as you would in a spreadsheet, pivoting rows and columns interchangeably. To support this use case, we are pleased to introduce PIVOT and UNPIVOT operators in BigQuery. PIVOT creates columns from unique values in rows by aggregating values, and UNPIVOT reverses this action.The example below uses PIVOT on bigquery-public-data.usa_names.usa_1910_current to show the number of males and females born each year, representing each gender as a column. Then UNPIVOT reverses this action.

Language: SQL

  -- we start with SQL to create a simple table
-- we only include gender, year, and number. 
CREATE TABLE
  mydataset.sampletable1 AS (
  SELECT
    Gender,Year,SUM(Number) AS Number
  FROM
    `bigquery-public-data.usa_names.usa_1910_current`
  WHERE
    Year >= 2017
  GROUP BY
    Gender, Year);
-- The resulting table:
--+----------------------------------------+
--|   Gender   |    Year    |    Number    |
--+----------------------------------------+
--|      F     |    2019    |    1353716   |
--|      F     |    2017    |    1403989   |
--|      F     |    2018    |    1380382   |
--|      M     |    2018    |    1568678   |
--|      M     |    2019    |    1538056   |
--|      M     |    2017    |    1604609   |
--+----------------------------------------+
-- use PIVOT to create columns for “female” and “male” 
CREATE TABLE
  mydataset.Pivoted AS
SELECT
  year, male, female
FROM
  mydataset.sampletable1 
PIVOT( SUM(Number) FOR gender IN ('M' AS male,
  'F' AS female))
ORDER BY
  year;
-- The resulting pivoted table:
--+----------------------------------------+
--|    Year    |   female   |     male     |
--+----------------------------------------+
--|    2017    |   1403989   |    1604609  |
--|    2018    |   1380382   |    1568678  |
--|    2019    |   1353716   |    1538056  |
--+----------------------------------------+
-- UNPIVOT reverses the row/column rotation of PIVOT.
SELECT
  *
FROM
  mydataset.Pivoted 
UNPIVOT(number FOR gender IN (male AS 'M',
  female AS 'F'));

QUALIFY (Preview)

More advanced users of SQL know the power of analytic functions (aka window functions). These functions compute values over a group of rows, returning a single result for each row. For example, customers use analytic functions to compute a grand total, subtotal, moving average, rank, and more. With the announcement of support for QUALIFY, BigQuery users can now filter on the results of analytic functions by using the QUALIFY clause. 

QUALIFY belongs in the family of query clauses used for filtering along with WHERE and HAVING. The WHERE clause is used to filter individual rows in a query. The HAVING clause is used to filter aggregate rows in a result set after aggregate functions and GROUP BY clauses. The QUALIFY clause is used to filter results of analytic functions. 

To show the utility of QUALIFY, the example below uses QUALIFY to return the top 3 female names from each year in the last decade using from bigquery-public-data.usa_names.usa_1910_current. 

Language: SQL

  -- QUALIFY filters the result of the RANK function
SELECT
  name,year,SUM(number) AS total,
  RANK() OVER (PARTITION BY year 
  ORDER BY SUM(number) DESC) AS rank
FROM
  `bigquery-public-data.usa_names.usa_1910_current`
WHERE
  gender = 'F'
  AND YEAR >= 2010
GROUP BY 1,2 
QUALIFY RANK <= 3
ORDER BY 2,4;

Flexible Schema Handling

New SQL for administrators and data engineers enables table renaming for data pipeline processes, as well as flexible column management.

Table Rename (GA)

In data pipeline processes, tables are often created and then renamed so that they can make way for the next iteration of the pipeline run. To accomplish this, customers need a mechanism by which they can create a table and then subsequently rename it. Now if customers want to change this name using SQL, they can. Using the simple syntax that ALTER TABLE RENAME TO provides, customers will be able to rename a table after creation to clear the way for the next iteration of tables in the data pipeline.

Language: SQL

  -- create a sample table “tablename” in “mydataset”. 
-- You will rename this table.
CREATE OR REPLACE TABLE dataset.tablename(
    col1 STRING, 
    col2 NUMERIC);
-- if this table “tablename” becomes obsolete
-- perform Table Rename to “obsoletetable”
ALTER TABLE
  mydataset.name RENAME TO obsoletetable;

DROP NOT NULL constraints on a column (GA)

While BigQuery has historically provided many tools available in the UI, CLI and APIs, we know that many administrators prefer interfacing with the database using SQL. BigQuery recently released DDL statements which enable data administrators to provision and manage datasets and tables, greatly simplifying provisioning and management. Today, we continue the next addition in this line of releases by announcing ALTER COLUMN DROP NOT NULL constraint on a column:

• ALTER COLUMN DROP NOT NULL allows the administrator to remove the NOT NULL constraint from a column in BigQuery.

Language: SQL

  -- create a table to store credit card numbers
-- the business requires this field, so 
-- include a NOT NULL constraint 
CREATE TABLE
  mydataset.customers(credit_card_number STRING NOT NULL);
-- if needs of the business no longer require this field,
-- the customer can allow null entries in this column
-- by dropping the constraint
ALTER TABLE
  mydataset.customers 
ALTER COLUMN credit_card_number DROP NOT NULL;

CREATE VIEW with column list (GA)

Views are used ubiquitously by BigQuery customers to capture business logic. Oftentimes, BigQuery users have business requirements to assign aliases to columns in views. Now BigQuery supports doing so upon view creation in a column name list format with the release of CREATE VIEW with column list syntax.

Language: SQL

  -- aliases list1 and list2 can be assigned in a list format
CREATE VIEW
  myview (list1, list2) AS
SELECT
  column_1, column_2
FROM
  mydataset.exampletable

New Geospatial Tools

ST_POINTN, ST_STARTPOINT, and ST_ENDPOINT

Geospatial data is incredibly valuable to data analytics customers dealing with data from the physical world. BigQuery has very strong geospatial function support to help customers process marketing data, track storms, or manage self-driving cars. Particularly for analyzing vehicle or location tracking data, we’re thrilled to provide three new functions to allow users to easily extract or filter on key points:  

• ST_POINTN
• ST_STARTPOINT
• ST_ENDPOINT

For example, when working with vehicle histories,  ST_POINTN, ST_STARTPOINT, and ST_ENDPOINT allow users to extract elements such as the start and the end of a trip. For identifying origin-destination pairs these functions will make that task much easier.

Language: SQL

  -- pull the first, second, penultimate and final points 
-- from a linestring
WITH linestring as (
    SELECT ST_GeogFromText('linestring(1 1, 2 1, 3 2, 3 3)') g
)
SELECT
  ST_StartPoint(g) AS first, ST_EndPoint(g) AS last,
  ST_PointN(g,2) AS second, ST_PointN(g, -2) as second_to_last
FROM
  linestring
+--------------+--------------+--------------+----------------+
| first        | last         | second       | second_to_last |
+--------------+--------------+--------------+----------------+
| POINT(1 1)   | POINT(3 3)   | POINT(2 1)   | POINT(3 2)     |
+--------------+--------------+--------------+----------------+

As sure as a hot summer day pairs well with an ice-cold beverage, these new user-friendly SQL features in BigQuery pair well with your data analytics workflows. To learn more about BigQuery, visit our website, and get started immediately with the free BigQuery Sandbox.

Access to the cloud has advanced the distribution and consumption of financial information on a global scale. In parallel, the global financial data landscape has been transformed by an influx of alternative data sources, including social media, meteorological data, satellite imagery, and other data. Exchanges and market data providers now find they need to include these new datasets to enrich their products and compete, which has meant they now must consider cloud-based models to keep up with the demands of their customers who expect easy, quick, flexible and cost-efficient ways to consume market data.

To address these needs, today we’re announcing the general availability of Datashare for financial services, a new Google Cloud solution that brings together the entire capital markets ecosystem—data publishers, and data consumers—to exchange market data securely and easily.

Datashare helps organize third-party financial information, making it accessible and useful to market data publishers and data consumers. We open-sourced the entire Datashare solution so market data publishers can now onboard their licensed datasets to Google Cloud securely, quickly and easily, while data consumers can consume that data as a service in tools of their preference, such as BigQuery.

Three ways to distribute and consume your data

Batch data delivery

Datashare provides a batch data delivery mechanism for data publishers to deliver their reference data, historical tick data, alternative market data sources and more via BigQuery, reducing the administrative burden on data consumers to extract insights from data. 

Real-time data streaming delivery

By using this event-based data delivery channel for rapidly changing instrument prices, tick data, orders, news and others via Pub/Sub, data consumers can reliably process individual messages or rewind to a point in time to replay a prior market scenario and test model changes.

Monetizing licensed datasets

Market data publishers can onboard their licensed datasets to Google Cloud and make them available via a one-stop-shop on Google Cloud Marketplace, enabling a new sales channel to expand market reach.

Reference architecture

Check out the diagram below to see how you can share your batch and real-time data directly to your Google Cloud customers with BigQuery and Pub/Sub.

As you can see in the above reference architecture, both publishers and consumers can derive several benefits from the solution:

Benefits for data publishers

• You no longer have to maintain your own delivery and licensing infrastructure.
• You can easily package and deliver granular data products and experiments with SQL.
• You can have a solution that scales with your business as data volumes and number of customers grow.

Benefits for data consumers

• Your data is ready for analysis and machine learning (ML)— you no longer have to maintain extract, transform, and load (ETL) pipelines to load files and transform data.
• You can avoid the expense and burden of maintaining multiple copies of large data files.
• You can be more targeted with consumption of data using BigQuery queries, improving performance, and compliance, and reducing cost.

Accessing the datasets

Google Cloud has been working with multiple industry firms on innovating in the market data space. By using Datashare for publishing, data publishers can make their entire datasets available on Google Cloud. Early adopters of Datashare include firms such as OneTick and Accern. OneTick’s datasets include reference and historical futures data (that can be accessed in our console with your login). Accern’s datasets include alternative data such as market sentiment and credit analysis data (that can be accessed in our console with your login).

To make it more helpful, we partnered with Accern to create a hypothetical scenario to describe the data acquisition and analytics process step-by-step.

Accern use case 

As a sustainability analyst, you require an economic, social and governance (ESG) dataset to determine which sector is the most widely covered ESG sector by analysts, and to also identify the sector with the lowest ESG sentiment score. Now, you can discover and acquire an ESG dataset in Google Cloud.

Step 1. Navigate to the Financial Services solutions page in the Google Cloud console:

Step 2. Click a dataset, for example Accern AI-Generated ESG Insights, then review the overview details, plans and pricing, documentation and support information. To view the available pricing tiers, click ‘View All Plans’. Once you’ve decided on a tier that you would like to subscribe to, click ‘Select’, choose a billing account and review and accept the terms of service to complete the subscription. Once the steps are complete, click ‘Subscribe’ at the bottom. An overlay window will appear, click ‘Register with Accern’ to activate and complete the subscription.

Step 3. Once activation is complete, you’ll be directed to the Datashare ‘My Products’ screen. Voila! You are now subscribed to Accern’s ESG Scores dataset and can access it in your Google Cloud instance using BigQuery. To access the data, click the hour glass icon on the corresponding ‘My Products’ record that you just purchased. An overlay will present you with the details on the dataset and/or table. Click the ‘Navigate to Table’ button to navigate through to the BigQuery console.

Step 4. Now that you have access and are in the BigQuery console, it’s time to generate data insights.

^{For this example, we’ve eliminated the company identifying information that is included as part of the subscription and aggregated company ESG in a view where each row represents a day, an industry sector, a specific identified ‘ESG Issues’ (event_group and event) and the respective ‘ESG Sentiment’ per issue.}

For example, row 1 indicates that within the ‘Healthcare’ sector, there was a ‘Social – Civil Society’ issue identified and it had a negative ESG sentiment score of -15.35.

Step 5. Generate a report by exporting it to Data Studio to build visualizations and conduct additional analysis on the ESG data.

Select ‘Export’ and ‘Explore with Data Studio’.

Step 6. Build a simple/basic report.

Now that the ESG data appears in Data Studio, you can start by building a simple chart to help you understand which industry sectors have the highest volume of discussions around ESG and the overall ESG Sentiment per industry sector.

To build the chart:

• Select the chart type ‘Table’.

• Include Entity_Sector as your dimension to aggregate results by ‘Industry Sector.’
• Include Signal_ID as a measure to count the number of ESG passages identified per ‘Industry Sector.’
• Include AVG(Event_Sentiment) as a measure to display the overall ESG Sentiment per ‘Industry Sector’ across ESG Issues.

You can see sectors that are  most discussed when it comes to ESG related topics and their corresponding ‘ESG Sentiment’ scores.

Step 7. Build your final report in Data Studio.

As a next step you can further drill into the data to understand ESG data specific to each ‘Industry Sector’ and identify positive and negative ESG practices. 

Accern has built a more complex sample dashboard and made it available publicly here. You can interact with this report and play around with the data. The dashboard can help to identify material ESG insights for each sector to inform your investment and risk processes. If you have additional questions, you can reach out to Accern directly.

Discovering, accessing and analyzing licensed datasets is quick and easy. Stay tuned for more updates on new licensed datasets.

Publishing your data via Datashare

If you are a publisher of market data, alternative, or exotic data, you can use Datashare to get it published on Google Cloud Marketplace.

Start by joining the Partner Advantage program by registering for the Partner Advantage Portal and applying for the Partner Advantage Build Model engagement. Visit our getting started guide for information to get started on publishing licensed datasets in the Marketplace. Stay tuned for a future blog post about using Datashare to publish datasets in the Marketplace.

More solutions for capital markets

Check out other Google Cloud solutions for capital markets.

We’re all accustomed to Google magic in our daily lives. When we need information like the time a store closes, the best route to a destination, help cooking a favorite recipe — we just ask Google. Google is known for providing useful information everywhere, wherever you are.

But for business, it isn’t so easy to get answers to important questions. In a recent study by HBR, only 30% of respondents report having a well-articulated data strategy and, at most, 24% of respondents said they thought their organization was data-driven. If you asked, most of these executives would probably tell you they’re drowning in data, so how come they’re struggling to be data-driven?

Data’s journey begins in operational databases, where the data is born as users and systems interact with applications. Operational databases are the backbone of applications and are essential ingredients for building innovative customer experiences. At Google Cloud, we have been focused on building a unified and open platform that provides you the easiest and fastest way to innovate with data. Google’s data cloud enables data teams to manage each stage of the data lifecycle from operational transactions in databases to analytical applications across data warehouses, lakes, data marts, and to rich data-driven experiences.

For example, Merpay, a mobile payment service in Japan, wanted to build its experience on a database that delivered on requirements around availability, scalability and performance. By using Cloud Spanner, Merpay was able to reduce overhead costs and ultimately devote engineering resources to developing new tools and solutions for their customers.

If you’re looking to bring the same kinds of transformation to your organization, let’s dive into the latest database announcements and how they can help you do that:

Tap into transformative database capabilities

Google’s data cloud allows organizations to build transformative applications quickly with our one-of-a-kind databases such as Cloud Spanner, Google Cloud’s fully managed relational database with global scale, strong consistency, and industry leading 99.999% availability. Leading businesses across industries such as Sabre, Vimeo, Uber, ShareChat, Niantic, and Macy‘s use Spanner to power their mission-critical applications and delight customers. Recent innovations in Spanner such as granular instance sizing and PostgreSQL interface lower the barrier to entry for Spanner, thus making it accessible for any developer and for any application, big or small.

In that effort, we are excited to announce Spanner free trial instances to give developers an easy way to explore Spanner at no cost. The new free trial for Spanner provides a zero-cost, hands–on experience to try out Spanner and comes with guided tutorials and a sample database. The Spanner free trial provides a Spanner instance with 10GB storage for 90 days. Customers can upgrade to a paid instance anytime during the 90-day period to continue exploring Spanner and unlock the full capabilities of Spanner, such as unlimited scaling and multi-region deployments. To learn more about the new free trial, you can read this blog and watch this short video. Start your free trial today in the Google Cloud console.

We’re also excited to announce the preview of fine-grained access control for Spanner that lets you authorize access to Spanner data at the table and column level. Spanner already provides access control with Identity and Access Management (IAM) which enables a simple and consistent access control interface for all Google Cloud services. With fine-grained access control, it’s now easier than ever to protect your transactional data in Spanner and ensure appropriate security controls are in place when granting access to data. Learn more about fine-grained access control in this blog.

Generate exponential value from data with a unified data cloud

Google’s common infrastructure for data management has many advantages. Thanks to our highly durable distributed file systems, disaggregated compute and storage at every layer, and high-performance Google-owned global networking, we’re able to provide best-in-class, tightly integrated operational and analytical data services with superior availability and scale at the best price- performance and operational efficiency.

In that effort, today we’re excited to announce Datastream for BigQuery, now in preview. Developed in close partnership with the BigQuery team, Datastream for BigQuery delivers a unique, truly seamless and easy-to-use experience that enables real-time insights in BigQuery with just a few steps.

Datastream efficiently replicates updates directly from source systems into BigQuery tables in real-time by using BigQuery’s newly developed Change Data Capture (CDC) and Storage Write API’s UPSERT functionality. You no longer have to waste valuable resources building and managing complex data pipelines, self-managing staging tables, tricky DML merge logic, or manual conversion from database-specific data types into BigQuery data types. Just configure your source database, connection type, and destination in BigQuery and you’re all set.

Klook, a Hong Kong-based travel company, is one industry-leading enterprise using Datastream for BigQuery to help drive better business decisions. “Prior to adopting Datastream, we had a team of data engineers dedicated to the task of ingesting data into BigQuery, and we spent a lot of time and effort making sure that the data was accurate,” said Stacy Zhu, senior manager for data at Klook. “With Datastream, our data analysts can have accurate data readily available to them in BigQuery with a simple click. We enjoy Datastream’s ease of use and its performance helps us achieve large scale ELT data processing.”

Achievers, an award-winning employee engagement software and platform, recently adopted Datastream, as well. “Achievers had been heavily using Google Cloud VMs (GCE), and Google Kubernetes Engine (GKE)” says Daljeet Saini, lead data architect at Achievers. “With the help of Datastream, Achievers will be streaming data into BigQuery and enabling our analysts and data scientists to start using BigQuery for smart analytics, helping us take the data warehouse to the next level.”

To make it easier to stream database changes to BigQuery and other destinations, Datastream is also adding support for PostgreSQL databases as a source, also in preview. Datastream sources now include MySQL, PostgreSQL, AlloyDB for PostgreSQL, and Oracle databases, which can be hosted on premises, on Google Cloud services such as Cloud SQL or Bare Metal Solution for Oracle, or anywhere else on any cloud.

Key use cases for Datastream include real-time analytics, database replication via continuous change data capture, and enablement of event-driven application architectures. In real-world terms, real-time insights can help a call center provide better service by measuring call wait times continuously, rather than retrospectively at the end of the week or month. And retailers or logistics companies that do inventory management based on real-time data can become far less wasteful than if it were based on periodic reports.

Accelerate your modernization journey to the cloud

In recent years, application developers and IT decision makers have increasingly adopted open-source databases to ensure application portability and extensibility and prevent lock-in. They’re no longer willing to tolerate the opaque costs or the overpriced and restrictive licensing of legacy database vendors.

In particular, PostgreSQL has become the emerging standard for cloud-based enterprise applications. Many organizations are choosing to standardize on PostgreSQL to reduce the learning curve for their teams and avoid the lock-in from the previous generation of databases. Google’s data cloud offers several PostgreSQL database options including AlloyDB for PostgreSQL, a PostgreSQL-compatible database that provides a powerful option for migrating, modernizing, or building commercial-grade workloads

To help developers and data engineers modernize and migrate their applications and databases to the cloud, we’re pleased to announce, in preview, that our Database Migration Service (DMS) now supports PostgreSQL migrations to AlloyDB. DMS makes the journey from PostgreSQL to AlloyDB easier and faster, with a serverless migration you can set up in just a few clicks. Together with Oracle to PostgreSQL data and schema migration support, also in preview, DMS gives you a way to modernize legacy databases and adopt a modern, open technology platform. Learn more about PostgreSQL to AlloyDB migration with DMS in this blog.

Among organizations adopting PostgreSQL is SenseData. A platform created to improve the relationship between companies and customers, SenseData is a market leader in Latin America in the field of Customer Success.

“At Sensedata, we’ve built our customer success platform on PostgreSQL, and are looking to increase our platform performance and scale for the next phase of our growth,” said Paulo Souza, co-founder and CTO of SenseData. “We have a mixed database workload, requiring both fast transactional performance and powerful analytical processing capabilities, and our initial testing of AlloyDB for PostgreSQL has given impressive results, with more than a 350% performance improvement in our initial workload, without any application changes. We’re looking forward to using Database Migration Service for an easy migration of multiple terabytes of data to AlloyDB.”

CURA Grupo is one of Brazil’s largest medical diagnostics conglomerates with over 1,600 employees, 500 qualified physicians, and 6 million examinations conducted every year. With more than 30,000 examinations performed every day, storing the database on premises was becoming increasingly unfeasible. CURA Grupo used Database Migration Service to migrate to Google Cloud, along with synchronization between their on-prem server’s database and the cloud. The result was an easy transition with just around 20 minutes of downtime.

To learn more about these exciting innovations and more, join us at Next 22 happening on October 11-13. You will also hear from customers such as PLAID, Major League Baseball, DaVita, CERC, Credit Karma, Box, and Forbes who are all innovating faster with Google Cloud databases.

If you’re managing a crucial application that has to be fully fault-tolerant, you need your system to be able to handle every fault, no matter the type and scope of failure, with minimal downtime and data loss. Protecting against these faults means juggling numerous variables that can impact performance as well as recovery time and cost.

Today’s managed database services take over the operational complexity that used to exist for database administrators. Growing your organization’s tolerance required adding machines, compute, and storage, plus the operational costs of IT management: performing backups, writing scripts, creating dashboards, and carrying out testing to make sure your platform is ready when problems arise–all in a secure way.

At Google, our Cloud SQL managed database service offers three fault tolerance mechanisms —backup, high availability, and replication—and there are three major factors to consider for each of them:

• RTO (recovery time objective): When a failure happens, how much time can be lost before significant harm occurs?
• RPO (recovery point objective): When a failure happens, how much data can be lost before significant harm occurs?
• Cost: How cost-effective is this solution?  

We’ve heard from customers like Major League Baseball, HSBC, and Equifax that they have strict data-protection needs and require highly fault-tolerant multi-region applications—and they’ve all chosen Cloud SQL to meet those needs. 

Let’s take a closer look at how the decision-making process plays out for each recovery solution.  

High availability (HA)

If your application is business critical, you require minimum RTO and zero RPO— a high availability configuration ensures that you and your customers are protected. If the primary instance fails, there’s another standby instance ready to take over with no data loss. There’s an additional cost here, but doing this manually brings a great operational cost, since you have to detect and verify the fault, do the failover, and make sure it’s correct—you can’t have two primary instances or you risk data corruption—then finally connect the application to the new database.

Cloud SQL removes all that complexity. Choose high availability for a given instance and we’ll replicate the data across multiple zones, synchronously, to each zone’s persistent disk. If an HA instance has a failure, you don’t have to think about when to fail over because Cloud SQL detects the failure and automatically initiates failover, for a full recovery and no data loss within minutes. Cloud SQL also moves the IP address during failover so your application can easily reconnect. MLB, for example, uses Cloud SQL high availability to serve prediction data to live games with minimal downtime. Dev/test instances don’t need those same guarantees, but can use local backups to recover from any potential failure.

Cross-region replica

If a whole Google Cloud region goes down you still need your business to continue to run. That’s where cross-region replication comes in, a hot standby replica in another Google Cloud region provides RTO of minutes and RPO typically less than a minute . If you create a read replica in a region separate from your primary instance and you get hit with a regional outage, your application and database can start serving customers from another region within minutes. But this solution can be complex and enabling it yourself can be difficult and time-consuming. Securing cross-geography traffic demands end-to-end encryption and can bring connectivity issues too. 

This is where the fully managed Cloud SQL solution shines. We offer MySQL, PostgreSQL and SQL Server database engines as a cross-region replication solution that’s easily configured and bolstered by Google’s interconnected global network. Just say, “I’m in U.S. East, I want to create a replica in U.S. West,” and it’s done, reliably and securely.

Backup

When you suffer data loss because of an operations error (for example, a bug in a script dropped your tables) or human error (for example, someone dropped the wrong table by accident), backups help you restore lost data to your Cloud SQL instance. Our low cost backup mechanism features point-in-time, granular recovery, meaning that if you accidentally delete data or something else goes wrong, you can ask for recovery of, for example, the state of that database down to the millisecond, such as Monday at 12:53pm. Your valuable data is replicated multiple times in multiple geographic locations automatically. This enables the automatic handling of failover in cases of major failure. You can always rest assured that your database is available and data is secure, even in the times of major failure crises. 

Cloud SQL provides automated and on-demand backups. With automated backups, Google manages the backups so that you can easily restore them when required. Also, the scheduled backing is automatically taken by default. With on-demand backup, you can create a backup at any time. This could be useful if you are about to perform a risky operation on your database, as Cloud SQL lets you select a custom location for your backup data. When the backup is stored in multiple regions, and there’s an outage in the region that contains the source instance, you can restore a backup to a new or existing instance in a different region. This is also useful if your organization needs to comply with data residency regulations that require you to keep your backups within a specific geographic boundary.

Putting it all together

For critical workloads, MLB configures their Cloud SQL instances with backups, high availability, and cross-region replication. Doing so ensures they can recover from many failure types.

• To recover from human error (“Oops, I didn’t mean to delete that”), MLB uses backups and point-in-time recovery to recovery to a millisecond or specific database transaction
• To automatically recover from primary instance failures and zonal outages, MLB uses Cloud SQL’s high availability configuration
• To protect against regional outages, MLB uses cross-region replication

Creating a robust configuration, like MLB did, takes just a few minutes. Get started in our Console or review documentation.