Editor’s note: When Europe’s largest mobile communications company, Vodafone, was asked by the European Commission to help understand population movement across the European Union and the UK to help fight COVID-19, it was able to provide anonymized mobile network-based insights to answer the call. Here’s how Vodafone, with the support of Google Cloud, rapidly mobilized the COVID-19 frontline, while respecting its customers’ privacy.

With the emergence of COVID-19 in early 2020, the European Commission—the executive branch of the European Union (EU)—knew that technology would be instrumental in its fight to control the pandemic. With various lockdowns imposed across its member states, the Commission was keen to predict and prevent the spread of COVID-19 and to manage the related social, political and financial impacts. 

Mobile network data helps track COVID-19 across the EU

Mobile networks produce location data, which can be turned into useful anonymous insights to understand population movement within a geographic area. The European Commission, working with mobile industry association GSMA (Groupe Speciale Mobile Association), asked Europe’s major mobile phone operators for help in producing insights to support the fight against COVID-19. As the largest mobile network operator within the EU, Vodafone saw this as a critical opportunity to participate. 

Vodafone had previous experience of using mobile network data to support pandemic research. For example, in 2019, Vodafone provided mobility pattern analysis to help track the spread of Malaria in Mozambique. And, during the early stages of the COVID-19 pandemic (prior to working with the European Commission), Vodafone assisted the Italian and Spanish governments in understanding their citizens’ mobility patterns. Vodafone had also previously offered anonymized and aggregated population mobility insights to support public transport and tourism authorities and retail organizations in a number of countries. Consequently, Vodafone was perfectly placed to play a greater role in supporting the European Commission’s response to the pandemic. 

When asked to assist the European Commission, Vodafone first considered how it could safely share its data with the governing body without providing details on the individual movements of its customers. It realized it could achieve this through an elaborate set of anonymization and aggregation techniques. Insights are aggregated from a minimum of 50 users and Vodafone only shared these anonymous insights and never the actual raw data with the Commission. As specified by the EU, these insights are then presented onto a large geographical region, typically a city or a county with thousands of people living in that area.

These insights illustrate how people move, helping to determine how lockdowns and self-isolation measures were impacting behaviors.

Using Google Cloud to collate and store population mobility data

In April 2020, Vodafone began migrating its operations, including its mobile data, to Google Cloud on servers in Europe and the UK with elaborate security safeguards, including encryption, building on a previous partnership. 

With the data residing in EU and UK data centers and not the United States, Vodafone could then retrieve anonymous insights from Google Cloud Storage instantaneously. Before supplying any information to the European Commission, however, Vodafone used Dataflow to validate the data and run a series of tests to ensure the database had accurate data, before ingesting and archiving the relevant metrics. For instant access, the data was then made available to the European Commission using a Redis database on Google Kubernetes Engine.

To ensure aggregate Vodafone customer data was always safe, secure, and anonymous, all entry points to the front-end were protected behind Google Cloud Armor, where only specific IP addresses were allowed. Using these tools, seamless data pipelines fed in predefined key performance indicators from each specified European market. While data quality measures ensured the definitions for metrics across markets were consistent and could be accurately compared.

The architecture (pictured below) shows how Vodafone integrated and anonymized its data on Google Cloud.

Live interactive dashboard shows population mobility in real-time

With its data integrated on Google Cloud, Vodafone created a live, interactive dashboard to track mobility patterns and share relevant information with the European Commission in real-time. 

The European Commission Joint Research Center (JRC) was able to gather valuable information from these insights, which enabled them to see where population mobility was aiding the spread of the disease, when cross-referenced with health data. It could also assess the implications of lockdowns on different populations and forecast cross-country spreading.

Mobile data aids disease modeling for multiple stakeholders

The Vodafone data became instrumental in modeling the likely course of the disease too. For example, the University of Southampton in the UK used it to predict the outcome of different coordinated COVID-19 exit strategies across Europe. This research was published in Science Magazine in September 2020. 

The Vodafone data dashboard continues to be used by individual governments, NGOs and organizations to further investigate the impacts of the pandemic and to measure the effectiveness of response strategies alongside the rollout of vaccination programs. The project also helped Vodafone win a DataIQ award for most effective stakeholder engagement. 

Using the learnings from this project, Vodafone has been able to adapt its own B2B solution, called Vodafone Analytics, by adaptIng and migrating the code to work in Google Cloud Platform. This solution has been rolled out across Germany, Greece, Portugal and South Africa, and new countries are being onboarded every day. Vodafone Analytics already has more than 100 customers leveraging it for a variety of use cases—Italian fashion retailer OVS, uses it for its smart retail operation, while global real estate company, JLL, uses it to understand the footfall passing through its properties. 

Working together, Vodafone and Google Cloud continue to help a range of organizations, governments, and NGOs navigate through the ongoing pandemic,  optimize their operations, and help the greater good, without infringing individuals’ fundamental rights to privacy.

To learn more about Google Cloud and Vodafone, watch our full interview here.

The partnership between Google Cloud and SAP is entering a new chapter. Over the years, our close partnership with SAP and our mutual dedication to customers has inspired us to do some of our most unique and innovative work—such as building Fast Restart and Memory Poisoning Recovery capabilities for S/4HANA workloads, offering real-time access to our advanced machine learning (ML) capabilities, and developing integrations with the industry’s best cloud security and network infrastructure.

With an expanded, strategic partnership, we are capitalizing on one of the cloud’s most valuable benefits: enabling customer choice without driving up cost or complexity. Customers have shared that they appreciate what Google Cloud has done over the years to support their ability to choose—from our support for multi-cloud environments to our track record with open-source technologies like Kubernetes, to name a couple of examples. 

“We chose Google Cloud to support our SAP implementation. Our decision had a lot to do with the relationship between Google Cloud and SAP and also for the applications and services that are offered by Google Cloud, like BigQuery, which are helping to enable data and analytics within our organization.”— Sam Moses, Vice President of Corporate Systems, The Home Depot

Now, we’re placing this same emphasis on customer choice, flexibility, and freedom to help SAP customers get greater ROI from their cloud investments in three ways:

• Execute seamless business transformations that deliver all of the benefits of modern, cloud-native applications and services—quickly, economically, and without disrupting day-to-day operations
• Migrate critical business systems to the cloud, in order to lock in OpEx savings and refocus their IT teams on strategic business technology initiatives
• Augment existing business systems with cloud capabilities in AI, ML, and analytics

The cornerstone of our expanded partnership is the RISE with SAP program—a comprehensive set of tools, technology, and expert support for a fast, predictable, cost-effective path to business transformation with SAP in the cloud. RISE is also designed to meet every SAP customer on their own terms, whether they’re undertaking an enterprise-wide cloud migration or pursuing an incremental or hybrid-cloud approach. 

To support customers taking advantage of RISE with SAP, Google Cloud will work even more closely with SAP to accelerate customers’ cloud migrations and business process modernization across several key areas. This includes ensuring global availability of multiple SAP services and products—such as SAP Business Technology Platform and SAP HANA Cloud—on Google Cloud’s reliable, scalable infrastructure and high-speed network. Google Cloud is also committed to bringing SAP’s solution for RPA in manufacturing to the Google Cloud Marketplace to support business process modernization. 

Additionally, integrating our AI, ML, and analytics capabilities with key SAP solutions will help companies augment their business systems with sophisticated capabilities and technologies so they can draw more value out of the data stored with them.

“We can deliver our 160 key business insights two or three orders of magnitude faster with BigQuery. When you think about our marketing campaigns and month-end finance processes, we’re talking about jobs that used to take five to eight hours to complete that are now running in seconds.”— Dan Semmens, Head of Data and AI, ATB Financial

Join us on the journey ahead

There’s so much more to come from SAP and Google Cloud. Learn more about our commitment to enabling digital transformation and hear more from customers about their SAP on Google Cloud deployments.

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.

Editor’s note: This post was originally published in TechPulse Belgium, where Jack Buser, Google Cloud’s Director of Game Industry Solutions, shared his trends for the industry this year.

The year 2022 will hold many surprises (with a few already dropping!), but there’s one near certainty: By this time next year there will be millions more gamers worldwide. It’s thanks to the industry’s little-noted technology growth drivers, which are getting stronger. 

Games are no longer a niche hobby; it’s global, mainstream entertainment. There were 3.1 billion gamers worldwide in 2020. It’s estimated that by 2024 there will be another 500 million. With a global population of 7.7 billion, that’s nearly half the planet. Revenues in 2021 may reach $175 billion—likely more than movies, music, and books combined. 

It’s a strength unique to the games industry, driven by the way its creators deploy new technology. Advances we see in everything from pricing and emerging markets, to backend computing and delivery suggests it will grow from here, on things like artificial intelligence and planet-scale networks.

Games forever, game better

It’s no surprise that videogames have become a huge business. Playing games is one of our deepest human expressions. There is a 4,500 year old board game that people still play, a testimony to the human love of testing cleverness, honing reactions, building trust, or just enlivening a day.

Video games do all that and for decades game makers have taken the most cutting-edge computing tech to make things better. We’ve seen electronic ping pong become shooting asteroids, to simple (yet iconic) 2D sprites evolve into believable, expressive 3D characters, all leading to today’s immersive worlds hosting tens of millions of players worldwide.

There’s more to come. I work at technology’s cutting edge for games, offering the world’s leading game creators access to powerful cloud computing, a low-latency global network, Machine Learning and AI, and much more. As usual, game innovators are taking up all these tools, building new narratives, new ways to play, new markets, and new jaw-dropping moments.

And game creators aren’t just thinking about players; they’re also thinking about viewers. More and more people are watching eSports, and creators are using the games medium to engage their viewers in increasingly immersive ways.

Streaming like never before 

AI and ML are sharpening games and attracting new players in other ways. These technologies increasingly power everything from network performance to global player matching. The delicate but essential work of encouraging newcomers and discouraging bad actors, optimizing monetization, or building and scaling up new player environments.

Another important area we’re seeing cloud tools being put to use is in coordinating the workflows of global teams of developers. It’s not just a question of building faster anymore, but of collaborating and responding more effectively. The distributed work trends that featured so strongly during the pandemic had already featured in much of the game industry, increasingly it’s an industry standard. This will likely mean even faster and more diverse game development, addressing the needs of new markets where we’re seeing double-digit growth.

Tools like cloud streaming, 5G, and edge computing are likely to accelerate the use of Game Streaming, Augmented Reality, and other types of immersive gameplay. If my metaverse doesn’t have games, count me out! We’re looking at some exciting new developments in coming months with some major industry players. Stay tuned.

A big year for the games industry

As a premier provider of solutions, tools and services to games companies, our engineering job is complex and global. Fortunately, our mission is simple: Help game companies transform to meet new global opportunities with planet scale solutions. Technology has brought the gaming industry to astonishing heights, and is the perfect compliment to great storytelling and creative ingenuity. 

As a decades-long industry veteran, I’ve never been more impressed with what technology can do for our industry. Looking past 2022, when we’ll see more compute, more AI, more AR, and more innovation around healthy and exciting game play, there is just one near certainty: More growth.