Organizations are pressed with the need to engage, retain, and upskill employees with many positions staying fully or partially remote as the pandemic continues. The Center for Digital Government (CDG) reports that 74% of state and local governments believe they will continue hybrid operations for the long-term. This transition is the latest in a long list of reasons agencies are looking for solutions to help train their workforce digitally. Some have found Google Classroom to be the perfect solution.

The State of Iowa knew about the advantages of digital classrooms even before the pandemic. They were already successfully using Google Classroom for in-person training, making the transition to digital seamless when the state closed down. Jessica Van Heuveln, a Google support specialist for the State of Iowa CIO’s Office, says the switch to digital doesn’t deprive workers of hands-on training. “You can use Google Classroom for many different environments, so if you’re doing self-taught, in-person or virtual training, it’s going to work with all those approaches.”

The logistics of a digital classroom

Iowa uses Google Classroom to onboard and upskill employees as well as train volunteers. When the pandemic shifted the state’s workforce to 70% working from home, they needed a  platform that could not only handle a vast range of topics and teaching styles to support remote workers but also scale to meet their demand. Van Heuveln notes that Google Classroom was particularly helpful for handling compliance training, especially with its integration with Google Meet. These trainings often require an entire sector of the workforce to attend a single class with more than 100 attendees, according to Van Heuveln. The logistics of these larger sessions cuts down on the number of total sessions the state needs to host. Iowa also found that employees were less apprehensive about learning when they could do it from the comfort of their own homes.

Iowa follows a train-the-trainer model to train employees expected to instruct using Google Classroom. Trainers working in departments across the state learn to use Google Classroom to create and host virtual learning experiences specific to their own departments. 

Collaborative classrooms made simple

Google Classroom meets many needs for Iowa, such as setting up training, managing attendance, reviewing uploaded coursework, and communicating with attendees. Both course attendees and trainers have everything they need in one place, and robust engagement tools help attendees stay focused while giving trainers the feedback they need from their lessons. Built-in survey options let trainers gauge attendee knowledge, and the chat function ensures no one ever misses a question. The reporting dashboard gives trainers access to analytics to track engagement and attendance, providing insights that can improve future training sessions.

Google Classroom also makes it easier to ensure attendees stay on board throughout the training program. Coursework can be uploaded directly into Google Classroom, and training sessions can be recorded and archived. This feature allows employees to work at their own pace or lets them catch up when a scheduling conflict means they might miss a session.

The enterprise version of Google Classroom Iowa uses interfaces with other Google Workspace tools such as Google Meet, Google Calendar, Google Drive, and Gmail, which streamlines training and leads to more collaboration between employees and trainers. It can also interface with applications from other providers, further boosting its utility as an effective virtual classroom. Google Drive stores training materials securely. Lastly, Google Classroom enterprise comes with 24/7 support to make sure agencies always have everything they need for success.

Helping employees be better prepared to serve

Google Classroom can be a powerful part of any training program, whether it be digital or in-person. Building virtual connections empowers organizations to deliver better experiences. Google Classroom is helping the State of Iowa accomplish this for their constituents. Designed to scale and be accessible from anywhere, the platform helps public employees better serve the public. Virtual training strategies are essential for agencies that are looking to grow their workforce and build employee skill levels. The most important thing, however, is that training programs enable employees to further your agency’s mission. To learn more about Google Classroom and see more solutions designed with government in mind, check out the Google Cloud for government page.

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.

Founded in 2006, PaGaLGuY started as a forum that enabled students, typically aged between 20 and 30 to discuss and seek advice on academic issues. By 2011, PaGaLGuY had increased its traffic to about 250,000 page views per month. The business is now one of India’s largest education networks and provides an app that users can download to their Android and iOS devices.

Over the past six years, PaGaLGuY has extended its service to include video advice from experts on education topics and grown the number of views of its pages to 1.5 million per day. As Head of Technology for the business, Sandeep Kalidindi has played a key role in ensuring PaGaLGuY is as engaging as possible to users. “Because the product is advertising based, the greater the user engagement, the greater the advertising revenue,” Kalidindi explains.

Google Cloud Platform Results

• Supported growth to 1.5 million page views per day and demand spikes that see requests increase from about 90 per second to about 1,200 per second
• Reduced API latency from about 1 second to about 40 milliseconds
• Reduced system administration time from three to four days per week to 30 minutes every two weeks

In 2015, PaGaLGuY’s senior management team decided to deliver an even more relevant experience for users of the education network. “The core thing we had to do was personalise the experience for each and every student that visited PaGaLGuY,” Kalidindi says. “So we had to capture each student’s data to customise what they see when they open the site.”

The business also found traffic to the network was straining its infrastructure. During demand peaks, created by exams involving as many as 5 million students, PaGaLGuY would be inaccessible for periods of 30 minutes to one hour. Furthermore, average API latency had climbed to an unacceptable 1 second, compromising performance.

PaGaLGuY needed to access extensive compute resources to undertake its planned change. Had the business relied on a physical technology architecture to undertake the transformation, it would have had to purchase capacity equivalent to 16 new servers. “There was no way with a small team we could grow to that extent in a short time,” Kalidindi says. “This was the right moment for us to explore cloud services.”

The business established two primary requirements the selected cloud service needed to meet. First, PaGaLGuY had to be able to scale the platform with costs rising only in proportion to the increase in resources consumed. Accordingly, the business would have to minimise the number of employees required to manage the cloud environment. Second, the platform had to give PaGaLGuY easy access to student data and the ability to undertake prompt, granular analysis.

PaGaLGuY reviewed available public cloud services and determined that Google Cloud Platform (GCP) was the best fit for its business. “Google Cloud Platform was considerably more mature than the alternatives, with a high degree of automation and a suite of managed services,” Kalidindi says. PaGaLGuY management then discussed with Google how to optimise cost, performance and availability of its personalised education network on GCP.

With assistance from Google and business transformation specialists Searce, PaGaLGuY was able to deliver the platform into production on GCP in 10 months. “Searce was very proactive in ensuring the environment met our needs and allowing us to gain priority access to Google services in development,” Kalidindi says. “Their team was integral to the success of the migration.”

PaGaLGuY has been running in production in GCP for two years. The education network’s GCP architecture comprises a scalable back-end built on Google App Engine; a managed environment for its containerised applications in Google Kubernetes Engine; messaging-oriented middleware through Google Cloud Pub/Sub; a relational database in Google Cloud SQL; a managed data analytics warehouse running in Google BigQuery; stream and batch data processing through Google Cloud Dataflow; and object storage in Google Cloud Storage.

PaGaLGuY has leveraged GCP services to break down its platform application from a monolithic build to a series of microservices running in Google App Engine that enable independent deployment cycles, minimise test and quality assurance overheads and provide clearer monitoring and logging.

Running on GCP has enabled PaGaLGuY to add new personalisation features and grow fourfold without having to add any new engineers or administrators to accommodate the increased traffic. The business has also used the platform to seamlessly collect and aggregate students’ data for analysis, reporting and delivering a more targeted user experience. Furthermore, PaGaLGuY has been able to provide its management team with direct access to Google BigQuery to scrutinise data rather than require them to wait at least a day to view reports created by the product or technology teams.

Support demand peaks of 1,200 requests per second

“Thanks to Google Cloud Platform, we can easily support demand peaks that see requests per second rise from an average 90 per second to about 1,200 per second for as long as 45 minutes,” Kalidindi says. Due to GCP’s scalability, PaGaLGuY can ensure its education network remains available and performance remains consistent during those periods.

Latency cut to 40 milliseconds

The business has also reduced average API latency from 1 second to about 40 milliseconds. Furthermore, using GCP has enabled PaGaLGuY to automate most of its processes and reduce system administration requirements from three to four days a week across its team members to about half an hour per week.

The performance of GCP has transformed PaGaLGuY’s culture and processes. “Once our team was exposed to Google Cloud Platform and understood the superiority of the platform, our mindset changed from ‘let us do everything on our own’ to ‘let us do what we do best’ and delegate the remainder,” Kalidindi says. The quality of the service provided by GCP means PaGaLGuY effectively considers the cloud provider as part of its team. “We are always eager to see what new services are being launched and are extremely excited about what Google Cloud Platform can provide as part of its roadmap.” he concludes.

As we enter the holiday season, retailers are working behind the scenes to ensure they can provide the best experiences for customers, in store and online. Challenges in retail do not begin or end during the holiday season as sudden shifts in customer preferences, supply chain nuances, and overall demand ebbs and flows take place year round and retailers must be prepared to adapt swiftly.

Google Cloud’s retail customers globally, in total, saw more online traffic in the first six months of 2022 than all of 2019. This year, retailers can expect an early launch to holiday shopping activities, as 50% of consumers plan to start purchasing goods before the traditional Black Friday kick-off.

The very same improvements made to automate and improve retail infrastructure can prepare it for holiday surges and support year-round innovation. Let’s take a look at how Ulta Beauty, the largest beauty retailer in the U.S., is partnering with Google Cloud, MongoDB Atlas, commercetools, and HCLTech to cover these two areas and more.

Architecting for innovation

Creating personalized shopping experiences in stores and online is key to Ulta Beauty’s success. This commitment is best demonstrated through Ulta Beauty’s Virtual Beauty Advisor. Built on Google Cloud, this tool enhances shoppers’ experiences with personalized recommendations in addition to the ability to try on makeup virtually with GLAMLab.

As innovators in support of the best possible guest experience, Ulta Beauty needed to re-architect its infrastructure for greater agility and stability.

To start, Ulta Beauty chose to use Google Kubernetes Engine (GKE) as the backbone and orchestrator to build and deploy cloud-native applications. The Google Cloud deployments coincided with an organizational move from end-to-end application development to one that focuses on individual features, specific modules, and micro-applications.

This strategic change allowed Ulta Beauty to fix bugs, experiment with new offerings, and drive customer experiences faster and more efficiently. Thanks to the transformation and GKE, Ulta Beauty’s developer team now accelerates time to market for new products and services, and delivers new ways to engage with customers more quickly. These efforts all ladder to create ‘WOW’ experiences for the retailers’ guests who have emotional and personal connections to beauty and wellness. They can now discover and experience products that are served to them based on individual preferences.

Adapting to the new environment comes with its own set of challenges. “Microservices are not a silver bullet,” says Sethu Madhav Vure, IT Architect, Ulta Beauty. “For Ulta Beauty, the biggest challenge was how to break up a monolithic environment into multiple applications. We had to evolve our core systems—without impacting today’s services—and address what was needed for the future.”

Google Cloud partner HCLTech provided expert guidance throughout the re-architecting process, defining the solution blueprint and cloud-native deployment architecture through cross-functional workshops. HCLTech then assisted with the actual migration and platform setup, paving the way for fully automated, continuous integration and continuous delivery (CI/CD) pipelines to support faster rollouts and deployment architecture to drive higher availability and scalability.

Ulta Beauty took a domain-driven design approach to identify operations that could be grouped together to reduce complexity and improve scalability. Now, the applications are based on multiple domains, such as Commerce, Promotions, Catalog, Order, Customer, and Inventory. The new architecture prompted a fresh look at storage requirements to scale dynamically alongside its modernized applications.

For Ulta Beauty, MongoDB Atlas proved to be the best database solution for dynamic scaling, ease-of-use, and integrations with Google Cloud. The company also leveraged an entry-level plan to prove the value of MongoDB Atlas before investing in the technology.

“MongoDB Atlas offers a free tier that gave us an opportunity to quickly demonstrate tangible benefits of a proof of concept,” says Vure. “Once we proved the value of MongoDB Atlas, we benefited from the straightforward resource allocation supported by Google Cloud and MongoDB.”

Integrations between MongoDB Atlas and Google Cloud allow Ulta Beauty to take an iterative approach to new projects. The company creates new clusters in an existing project, then piggybacks them onto an existing Private Service Connect setup between a MongoDB project and Google Cloud project.

By removing complexities within infrastructure management, Ulta Beauty can manage its incredible amount of data, such as member preferences and purchases, that fuels its event-driven architecture. The much more agile infrastructure enables Ulta Beauty to deploy and scale offerings faster than ever.

“We recently had an unplanned traffic surge that impacted our domain services. It took less than an hour for MongoDB Atlas to scale up to the next level of the cluster and manage that traffic,” says Vure. “The on-demand, dynamic scaling, plus GKE, has saved the day more than once.”

Preparing for a happy holiday season

This holiday season, Ulta Beauty has a stronger technical foundation to manage demand surges and provide customers seamless shopping experiences. Previously, the company used 50 pods in a cluster, each with 6 GB of RAM without domain stores, to handle about 100 transactions each second. With domain stores, the same 6 GB of RAM with just 20 GKE pods was able to scale up to 2,400 transactions per second.

With Google Cloud as its technology foundation, Ulta Beauty partnered with Google Cloud partner commercetools to evolve its application APIs as products and properly separate interfaces and capabilities.

Ulta Beauty uses event-based integrations within commercetools to identify how best to leverage Cloud Pub/Sub middleware on top of MongoDB Atlas integrations. Patterns established here were extended into MongoDB change streams and in turn improved business processes.

“Working with the right technology partners has helped us to avoid analysis paralysis that can happen when developer teams spend a lot of time trying to understand and manage every detail,” says Vure. “Instead, we convert a proof of concept into a working solution, and quickly bring it to market. It’s been a major shift in our IT culture as we try out new things weekly and see incredible support from leadership.”

The improvements enable Ulta Beauty to maintain a high level of innovation, performance, and customer service year-round. Now, when the holiday shopping season begins, Ulta Beauty is prepared to handle surges in traffic through auto-scaling with Google Cloud and MongoDB Atlas. Customers get what they want, when they want, free from the frustrations of outages.

“With these changes, we are ready for a holiday season that everyone–even those of us in IT—gets to enjoy. We’re positioned to continuously focus on new, better ways to serve our guests,” says Vure.

Check out MongoDB and commercetools on Google Cloud Marketplace to learn more about what these partners can do for your business.