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.

Today we’re announcing the HarbourBridge Schema Assistant, which provides a guided schema-design workflow for migrating from MySQL or PostgreSQL to Spanner. HarbourBridge imports dump files (from mysqldump or pg_dump) or directly connects to your source database, and converts the source database schema to an equivalent Spanner schema. The new Schema Assistant capability displays the source schema and Spanner schema side-by-side, highlights errors and walks you through a series of steps to validate and optimize your Spanner schema. It also produces a browsable assessment report with an overall migration-fitness score for Spanner, a table-by-table detailed analysis of type mappings and a list of features used in the source database that aren’t supported by Spanner. It supports editing of table and column names, column types, primary keys and constraints, as well as dropping of tables, columns, foreign keys and secondary indexes.

The new Schema Assistant complements HarbourBridge’s existing data and schema migration capabilities and is a critical step towards our goal of building a complete open-source migration toolkit. HarbourBridge continues to support command-line schema and data migration and turn-key Spanner evaluation.

Complementing the bulk data migration capabilities of HarbourBridge, we are also announcing the ability to migrate change events from MySQL to Cloud Spanner.

Supported Features in Schema Assistant

1. Global type mapping. Users can customize the global mapping for how types should be mapped to Spanner consistently across the schema. For example, mapping large integers in source schema to Spanner’s NUMERIC.
2. Local type mapping. Users can override the custom type mapping for a given table/column.
3. Session management. A session keeps track of all the changes made to the schema mapping.
4. Customization of secondary indexes. Users can add, edit and delete secondary indexes to optimize their Spanner performance.
5. Customization of foreign keys and interleaved tables. Table interleaving is an important design consideration when migrating to Cloud Spanner as explained in more detail in this blog post.

Features in the pipeline

We are already working to further expand the supported set of schema editing features and welcome your feedback. We are particularly excited to expand the Schema Assistant’s design recommendations for optimizing Spanner schemas e.g. in-depth recommendations for primary key design.

HarbourBridge is open source and we gladly accept contributions from the wider community.

Budget concerns and inflation shock are causing noticeable shifts in consumer behavior, a head-spinning turnabout since the pandemic days of 2020. We went from not having access to enough goods to now not having the right goods at the right price points. Heading into the holiday season, retailers will need to look sharp and be able to deliver value to the increasingly cost-conscious consumer — with frictionless product discovery and digital shopping experiences, relevant inventory, and personalization.

Those are a few of the insights gleaned from a recent eMarketer webcast on how retailers can better combat the effects of inflation, with pragmatic recommendations from experts including Amy Eschliman, managing director of retail solutions strategy and industry engagement at Google Cloud; Alexis Hoopes, vice president and head of e-commerce and direct-to-consumer (DTC) at Mattel Inc; and Elissa Quinby, head of retail insights at Quantum Metric. We’d like to share five key takeaways that retailers can adopt.

1. Be prepared for earlier holiday planning. Budget-conscious buyers are planning their purchases earlier than ever. According to our partner Quantum Metric’s latest Retail Benchmarks research, 40% of U.S. consumers and 30% in the UK have already started their holiday shopping1. At Mattel, Alexis noted, “We’ve learned to be nimble and early for our consumers, and to have products available for them when they’re ready to shop.” With higher prices, she added, “Consumers are preplanning and researching more, viewing product detail pages multiple times to check a higher-price item before adding it to their basket.”

Quantum Metric’s same research also shows that while average cart values grew between January to July 2022 — larger than they were at the same time last year1 — consumers were shopping less frequently. Because of higher prices, 37% of shoppers are now pre-planning and purchasing items all at once to help keep to their budget1. Amy observed. “That means retailers need to make it super easy for customers to find what they want to avoid shopping-cart abandonment.”

Retailers who make it easier for shoppers to find the right products, by providing Google-quality search and recommendations, can help reduce cart abandonment and increase conversions.

2. Get a handle on out-of-stock issues. While many of the supply-chain issues that surfaced during the pandemic have cleared up, inventory continues to be a challenge in retail. During the 2021 holiday season, Google/Ipsos research shows that consumers saw a 253% increase in “out of stock” messages versus pre-pandemic2. Elissa further pointed out that a great majority of both U.S. and UK consumers experience out-of-stock issues several times a month. It’s a fluid situation, however, with retailers facing bloated inventory as consumer demands quickly pivot from, for example, branded goods to generic or white-label items. “In the next six months or so, you really need to make sure you have the right inventory to meet consumer demands,” she advised.

The need for end-to-end visibility and the ability to act in real-time across the supply chain has never been more pressing. Google Cloud and our partners are tackling the top supply chain issues head on with solutions that enable end-to-end visibility, analytics and alert-driven event management as well as AI solutions and automation to streamline processes like procurement, fulfillment, and delivery.

3. Introduce value: communicate the quality of the product and the experience. While rising prices are paramount for many shoppers, it’s a common misconception that consumers are making purchase decisions based solely on price. Alexis remarked, “In e-commerce, we talk about price, product, service, and experience. Price is only one of the pillars for consumers. At Mattel, we are fully focused on product and experience. What makes this special? How do we connect directly in new ways to the consumer? Create ‘wow’ moments and deeper connections. When we think about value for our consumers, it’s the strength of the products that will drive the purchase.”

Elissa also noted that while consumers are doing a lot of comparison shopping, it’s really about value, whether a high-quality product or a high-quality experience. Quantum Metric’s Continuous Product Design solution, which is built with BigQuery, ingests data across multiple digital touchpoints, including from mobile and web applications, and connects customer signals to every stage of the product lifecycle to help deliver the products and experiences that customers actually want.

4. Ensure consistent experiences across channels. Today, consumers crave the cross-channel shopping experience. “The channel experience has gone from online to in-store to now everywhere,” Amy commented. “We call it ubiquitous digital shopping.” Elissa added that 75% of consumers do most of their shopping digitally. However, mobile drives 67% of digital traffic, but just 49% of sales.1 “Recent trends in traffic and conversion rates by device show that we can expect the most traffic for the big sale days on mobile,” she said. “People are looking for discovery and awareness, maybe even adding items to the cart as a placeholder or reminder. But they prefer to complete a sale on a desktop. It will be critical for retailers to offer a consistent experience, especially on major sale days like Black Friday and Cyber Monday.” Elissa also advised wrapping up any experiments with product and site design early, making sure to understand the customer experience holistically across the organization and prioritizing efforts to eliminate friction.

Consumers now expect to be able to shop wherever and whenever best fits their needs, whether in a store, on your website, through your app, or from within a social media ad, and have it be a consistently good experience no matter how they first entered or exited your commerce site. Google’s 2022 Retail Marketing Guide provides useful insights and tips on how to grow your online and in-app sales.

5. Personalize touchpoints to build loyalty. Connecting with your customer base and making sure they understand the value of your offering is essential. Alexis noted that the key is to keep your messages fresh as the holiday shopping season expands, providing new messages as they keep coming back to your store. “We need to engage them by helping them find what they are looking for at the right time,” she said. “Recognize that they are doing more planning and wish-list building early, then buying last-minute gifts at lower prices towards the end. Make sure those are front and center.”

Alexis also pointed out that today, consumers are providing more data points with the products they view or the items sitting in their carts. “What’s so great about e-commerce is that we can use all of this to create more personalized, direct messages targeted to those consumers,” she commented.

Amy recommended continuing to focus on conversion with product discovery and personalization, harnessing customer data to drive insights and action. “Any company’s biggest asset is their data. Using it in as many ways possible and activating it across the company is incredibly important,” she remarked. “Take advantage of your first-party data to activate everything from marketing campaigns to a more efficient supply chain. For every customer who comes to one of your digital properties, make sure your product discovery is as easy as possible. Pay attention to your recommendations, driving personalization to make that experience as unique and fruitful for the customer as possible.”

Achieving these objectives requires a modern cloud data warehouse and activation of a customer data platform. Retailers can explore how Google Cloud’s advanced data capabilities and our ecosystem of partners can power a customer data platform that supports more personalized marketing, shopping experience, and customer service.

While these are our key takeaways, we invite you to register to watch the on-demand webinar, “5 Ways Retailers Can Combat the Effects of Inflation,” for even more insights.

1. Quantum Metric Retail Benchmarks, “Adjusting for Inflation.” The report is based on aggregated browsing behavior from January to July 2022, paired with a survey of 3,400 consumers in the U.S. and UK.
2. Google/Ipsos, Holiday Shopping Study, Oct 2021 – Jan 2022, Online survey, US, n=7,253, Americans 18+ who conducted holiday shopping activities in past two days

The city of South Bend, Indiana has announced they are working with Google Cloud to connect 500 local job seekers with no-cost access to Google Cloud Skills Boost through Upskill SB. This initiative will train, upskill, and certify residents in cloud computing skills to create pathways to technical jobs.

As companies, nonprofits, and governments increasingly leverage cloud computing technology to strengthen their organizations, skilled cloud talent is in high demand both nationally, in Indiana, and in South Bend. According to Lightcast, there were 27,180 unique cloud roles open in the U.S. as of October 2022. Cloud skills are the most in-demand skill set by IT departments: more than one-third of tech learners said professionals with cloud skills are the most challenging to find.

“This partnership with Google Cloud will help residents get on a no-cost path to a cloud computing career at a time when the field is rapidly growing,” said Caleb Bauer, Executive Director of Community Investment for the City of South Bend. “This will help residents prepare to take the next step in their career with Google Cloud Skills Boost and will pave the way to build in-demand skills.”

Google Cloud Skills Boost is the destination for all Google Cloud learning and training opportunities. Local colleges and universities are also able to offer free access to Google Cloud Skills Boost for students through  the Google Cloud Higher Education program. Students can request access to free online labs, quests, and courses. Faculty can take advantage of free Google Cloud Computing Foundations curriculum and coach their students to prepare for Google Cloud certifications.

“Google is proud to offer no-cost access to Google Cloud Skills Boost for jobseekers in South Bend,” said Alice Kamens, Workforce Development lead at Google Cloud. “Through this partnership, we’re advancing our work to ensure everyone has access to the technical training needed to pursue cloud computing careers.”

Those interested in learning more about the program or applying for Upskill SB should visit the Google Cloud page on Upskill SB. Scholarship recipients need access to a computer, hand-held device or smartphone and the internet.