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.

About Vimeo

Vimeo gives video creators the tools to host, share, and sell videos of the highest quality possible. It reaches viewers in over 150 countries who can watch content anytime, on nearly every Internet-connected device.

Industries: Media & Entertainment

Location: United States

About Fastly

Fastly helps the world’s most popular digital businesses keep pace with their customer expectations by delivering fast, secure, and scalable online experiences. Businesses trust the Fastly edge cloud platform to accelerate the pace of technical innovation, mitigate evolving threats, and scale on demand.

Industries:

Location:

Vimeo is a video-sharing platform that’s home to imaginative video creators and hundreds of millions of viewers. Sixty million people create, host, and sell high-quality videos on Vimeo, including more than 800,000 who subscribe to the service’s premium tools. Over 240 million people in more than 150 countries watch videos monthly.

Vimeo was using its own servers to allow users to upload videos to its service, a cloud storage platform for storing videos, and as an alternative solution for streaming. It was looking for a solution that would do away with its own servers for uploading. Vimeo built a new adaptive video-delivery service on Google Cloud Platform and the Fastly edge cloud that can scale on demand to meet Vimeo’s growing needs for video streaming.

“Our business is dependent on delivering high-quality video; that’s our competitive edge,” says Naren Venkataraman, Senior Director of Engineering at Vimeo. “Thanks to Fastly and Google Cloud Platform, we’re delivering more high-quality videos than ever at less cost, leading to our continuing success and growth.”

Tuning video delivery

Building a great video experience begins with a fast, reliable upload service. Vimeo replaced its servers for accepting video uploads with Google Cloud Storage, fronted by the Fastly edge cloud to help ensure regional routing and low-latency, high-throughput connections for Vimeo’s publishers. Multi-regional Google Cloud Storage offers fast, resumable upload capability that helps make for better user experience.

The video delivery service transcodes videos and streams them to users—videos are customized depending on network traffic and the devices to which the videos are delivered. The goal is to deliver the highest-quality, smooth playback experience across all platforms over varying network conditions and device capabilities.

Google Compute Engine packages the videos, which are stored on Google Cloud Storage. Google Compute Engine can automatically scale to allow Vimeo to deliver videos on the fly, even when demand spikes and many users stream videos simultaneously across a very diverse library. The low latency of Google Cloud Storage helps with fast startup times, while providing scalable storage to host millions of videos from Vimeo’s loyal community of content creators.

“Fastly and Google Cloud Platform enabled us to build a low-latency, highly scalable, on-the-fly adaptive video streaming packager in a short period of time with a small team,” says Naren.

High-quality video means more users

With Fastly and Google Cloud Platform, Vimeo is delivering more and higher-quality videos to its users because of the platform’s low latency, high bandwidth, and ability to scale. Because of the system’s reliability, fewer users stop watching videos because of delays and glitches. Vimeo engineers do not have to spend their time managing infrastructure and now focus on improving the video delivery service, leading to improved customer satisfaction. Costs are reduced because Vimeo does not have to manage the infrastructure in-house.

“We’ve chosen Google for Fastly’s Cloud Accelerator because at Google innovation happens faster, and Google Cloud Platform is driving cloud computing and cloud storage in the right direction,” says Lee Chen, Head of Strategic Partnerships at Fastly.

Editor’s note: In honor of Mother’s Day, we look at how 1-800-FLOWERS.COM, Inc. migrated to Google Cloud as part of its digital transformation to quickly deploy seamless and convenient customer experiences across multiple brands on Mother’s Day and every day. 

As a leading provider of gifts designed to help customers express, connect, and celebrate,  1-800-FLOWERS.COM, Inc. has embraced cloud technologies to grow and transform its business through constant innovation. As part of our digital transformation, we recently completed the migration of our ecommerce platform and other services to Google Cloud. We’ve transitioned from a monolithic to a microservices platform, moved many workloads from our on-premises data centers to our Google Cloud environment, and scaled both horizontally and vertically. 

Since our migration, we’ve developed efficient processes to launch new brands, improved the customer experience across all brands, and seen significantly increased site traffic. 

Nurturing a more delightful customer journey

Customer delight is at the core of everything we do. Whether it be with a flower bouquet, a sweet treat, or a personalized keepsake, our mission is to deliver smiles. With the rise of the COVID-19 pandemic, we’ve all been challenged to find unique and safe ways to continue honoring the special connections in our lives and celebrating occasions with loved ones. Our customers have adapted by doing things such as sending gifts to isolated loved ones, sharing the same meal together virtually, or using video to engage with others through group activities like flower arranging and building charcuterie boards. 

The customer experience is a top priority for us, and we constantly look for innovative ways to enhance the customer journey across our ecommerce platform of more than a dozen brands. As a result, we’ve continued to see a rise in demand as customers enjoy the ease and convenience of our site and discover our full family of brands. 

Migrating to a cloud-first mindset

As we’ve continued to innovate and iterate on the customer experience, we knew we wanted to evolve our platform. We wanted to shift to a microservices platform, which would allow our team the opportunity to release updates to our site more often and set up the right continuous integration/continuous deployment (CI/CD) practices. 

Working with Google Cloud, we were able to move our ecommerce platform to the cloud and standardize our site and brand deployment by building one release that could then be repeated across all of our brands. We built everything in a modular fashion, including microservices and      code libraries, so that sites could be easily constructed and replicated for each brand. And because of this, we were able to launch Shari’s Berries extremely quickly after we acquired the brand in 2019.   

Moving our platform to a completely homegrown solution of microservices was a daunting task. But our team handled it beautifully through load-testing, stress-testing, and building new monitoring tools. And with Google Cloud supporting us all along the way, managing the migration process was simple and easy from start to finish. 

Arranging a better bouquet of services

Currently, we’ve migrated every customer-facing touchpoint for all of our brands to Google Cloud—whether it’s on the web or mobile, our AI bots, or our chat interfaces. 

• We run on Google Kubernetes Engine and Istio.
• We have nearly 200 microservices built to help power our entire ecommerce stack across several cloud services running on Google Cloud. 
• We’re utilizing BigQuery for our offline intelligence.

Results are coming up roses

Our new stack on Google Cloud has benefits for both us and our customers. We moved from a session-based to a token-based system, which provides enhanced security as well as a consistent, convenient experience across all our brands. Using service workers and a single-page app, we are able to download all the relevant site content to the browser in under two seconds to create an instant-click experience for each and every customer. We also use Google Analytics to measure our user interactions and provide personalized results to each customer. Our hope is that with this new system, we can learn from customer behavior to offer gift givers a more personalized shopping experience during each visit. 

The benefits of our new tech stack have not only helped us enhance the solutions we offer to customers today, they’ve also enabled us to offer new ones at lightning speed. With our legacy system, we used to release new code once a week or once a month. Now, even during our peak periods, we’re able to release 10 to 15 times a day and can deploy and pivot quickly to create new microservices and microsites on the fly—often without having to touch any code. 

Efficiencies abound     

The benefits of moving our platform to Google Cloud have extended to our internal teams as well. Before the migration, we had only two environments for developing and testing, which made it time-consuming to test updates before they went into production. Now with Google Cloud, we have several different journey teams—which are made up of developers, product owners, and technical owners—all working in several different environments, solving problems, and creating new solutions together. 

Everyone is now empowered to be self-sufficient, developing and releasing microservices on their own when they’re ready. This has given our developers more time to take part in continued development and learning opportunities. For example, we offer lunch-and-learn sessions as well as other resources for everyone to take advantage of so they can continue to learn and refine their skills.

Planting the seeds for future growth

As we look to the future and think about how we help our customers express, connect, and celebrate, we’ll continue to collaborate across teams to deliver solutions that spread smiles. Specifically, we’re exploring additional use of AI to help us better serve our customers across all our brands. 

We’ve enjoyed the ongoing support we’ve received from the Google Cloud team as they help us build new solutions and design a road map for the future. Their support has helped the 1-800-FLOWERS.COM, Inc. team to realize the power of the cloud and bring the very best experience to our customers.

Learn more about 1-800-FLOWERS.COM, Inc., or check out our recent blog about cloud migration for the real world.

For a business to succeed in the long term, it needs to learn not just to adapt to inevitable change, but to harness it. South Africa-based PPS has been an insurance company since 1941 and today is the biggest mutual insurance provider in the country.

As a mutual company, PPS is owned by more than 200,000 members, making them shareholders. In recent years, PPS and other companies like it have been affected by a number of external factors.

“For one thing, technology platforms have brought in a new gig economy that has all kinds of implications for insurance,” says Avsharn Bachoo, CTO at PPS. “What we’ve been seeing is basically a disruption of the South African insurance industry. We chose to see that as an opportunity.”

“Our servers were at the end of their life cycle and we had to decide whether to refresh them or switch completely. To embrace the world of AI and machine learning effectively, we knew we needed a cloud-based infrastructure. We’ve found the answer in Google Cloud Platform.”

—Avsharn Bachoo, CTO, PPS

In early 2018, faced with an uncertain economic environment that was squeezing growth and profitability, PPS decided to transform itself from a traditional broker-based business into a digital insurance provider. A key pillar of this new strategy was to overhaul the company’s technology infrastructure. To turn the strategy into reality, Avsharn and his team chose Google Cloud Platform (GCP).

“Our servers were at the end of their life cycle and we had to decide whether to refresh them or switch completely,” says Avsharn. “To embrace the world of AI and machine learning (ML) effectively, we knew we needed a cloud-based infrastructure. We’ve found the answer in Google Cloud Platform.”

Power, speed, flexibility with Google Cloud Platform

Previously, PPS maintained an on-premises IT infrastructure, which worked for its traditional business but was unsuited for its new way of working. In early 2018, the company started working on new products for its members but this required large amounts of compute power that proved prohibitively expensive with on-premises servers. Even existing products were starting to require more than the infrastructure could deliver. Aging equipment meant that it’s testing and quality assurance environments bore little resemblance to the actual production environment.

“We had no pre-production environments at all,” says Avsharn, resulting in more work for developers after products had been released. Meanwhile, the capital required to buy and configure more servers for new projects meant fewer resources available for innovation, and left the company less able to react to changes in the market. PPS knew it had to find a cloud-based alternative.

Shortly after devising a new digital strategy, PPS engineers attended a training session on cloud infrastructure given by leading South African Google Cloud Partner Siatik. Impressed with the presentation, PPS engaged Siatik to help run a proof of concept for a cloud-based infrastructure, running on GCP. With on-site engineers and constant communication, Siatik formed a very close working relationship with PPS. “The team at Siatik was exemplary,” recalls Avsharn. “They were well-organized, with cutting-edge technical acumen and very creative solutions to our problems. They were real game-changers.”

“We wanted the platform to retrain its models in response to new data and improve its recommendations with more information. Normally this would be a manual process but Google Cloud ML Engine lets the models do this automatically.”

—Kimoon Kim, Lead Solution Architect and Data Engineer, Siatik

The proof of concept was successful, with GCP outperforming the existing infrastructure in terms of how it handled compute demands, databases, and storage.

“It’s the speed of GCP that really impresses us,” says Avsharn. PPS saw that GCP wasn’t just an opportunity to migrate its existing infrastructure to the cloud. With Siatik’s help, it redesigned its monolithic core architecture to one based around microservices using Google Kubernetes Engine (GKE). For data processing and storage, Cloud Dataflow and Cloud Datastore proved invaluable, while Stackdriver helped the IT team stay on top of logging and monitoring the system.

“Google Cloud makes migrations very easy,” says Brett St. Clair, CEO at Siatik. “It takes care of all the hard work with configurations and replications, so when we switch the machines on, everything is ready and working.”

The ease with which PPS migrated to GCP means that it can now tackle strategic goals much more quickly than before. The most ambitious of these is an AI-powered product recommendation platform. Information is collected from customers who opt in at a defined point in their journey, this database is queried using BigQuery, and the information is fed into the platform. The AI model then calculates the most appropriate products for each member, according to their personal history.

“Most of the product recommendation engines out there are based on clustering, where you’re offered products based on your peer groups,” explains Avsharn. “For the first time, we can make recommendations to members based on their individual preferences and historical behavior. That’s really powerful for us.”

Siatik helped PPS use TensorFlow and Cloud Machine Learning Engine to build the AI platform. For the engineers, these easy-to-use tools helped speed up the process considerably, allowing them to host the models locally without any fuss. Previously, it took one to three months to manually build the model and match an offer to a customer. With the AI platform, a match takes just a few minutes. Cloud ML Engine, in particular, helped the platform adapt to new information on the fly and easily make adjustments to its hyperparameters, that is, preset variables which define the model-training process.

“We wanted the platform to retrain its models in response to new data and improve its recommendations with more information,” says Kimoon Kim, Lead Solution Architect and Data Engineer at Siatik. “Normally this would be a manual process but Google Cloud ML Engine lets the models do this automatically.”

“Google Cloud helped us cancel out a lot of the noise around machine learning and AI. We don’t have to build new complicated algorithms or hire huge teams of data scientists to benefit. We just bring our data and use the right tools to focus on what’s really important.”

—Avsharn Bachoo, CTO, PPS

Harnessing artificial intelligence for real-world results

PPS deployed its new AI recommendation platform in December, 2018. Just a couple of months later, its impact was clear. “In around eight weeks, we saw a 5 percent growth in sales,” says Avsharn. “It’s been a direct result of building our recommendation platform with Google Cloud. We can offer the right products to the right members.”

For developers and engineers at PPS, working with Google Cloud gives them access to high performance technology and automation options with GKE. As a result, the infrastructure runs 70 percent faster than before with fewer cores and less memory. Developers can also work in mature testing environments, and for the first time, are able to build pre-production environments, leading to better quality products. More strategically, moving to a serverless, cloud-based infrastructure has helped PPS take control of its budget, moving away from intermittent, large capital spends to more manageable, project-to-project flows of operational expenditure. The company expects to see savings of around 50 percent, or $695,000.

“We have a lot more flexibility with our resources thanks to Google Cloud,” says Avsharn. “When we have a new idea, we don’t have to outlay new capital such as servers before we can even start working on it. We just spin up instances when we want and spin them back down when we’re done.”

With the AI platform deployed and working well, PPS is already looking at ways to improve it, including real-time updates and further automation. Soon, the company will integrate the platform with more sales campaigns for more effective targeting to boost sales even further. Meanwhile, it’s also experimenting with machine learning to spot patterns in data at scale for fraud analytics and risk assessment.

For PPS, working with Google Cloud has helped it transform quickly and effectively from disrupted to disruptor. The company is now looking to gain the same transformative effects by implementing G Suite for increased productivity and collaboration.

“Google Cloud helped us cancel out a lot of the noise around machine learning and AI,” says Avsharn. “We don’t have to build new complicated algorithms or hire huge teams of data scientists to benefit. We just bring our data and use the right tools to focus on what’s really important.”