Introduction

We recently announced BigQuery support for sparse features which help users to store and process the sparse features efficiently while working with them. That functionality enables users to represent sparse tensors and train machine learning models directly in the BigQuery environment. Being able to represent sparse tensors is a useful feature because sparse tensors are used extensively in encoding schemes like TF-IDF as part of data pre-processing in NLP applications and for pre-processing images with a lot of dark pixels in computer vision applications.

There are numerous applications of sparse features such as text generation and sentiment analysis. In this blog, we’ll demonstrate how to perform sentiment analysis with the space features in BigQuery ML by training and inferencing machine learning models using a public dataset. This blog also highlights how easy it is to work with unstructured text data on BigQuery, an environment traditionally used for structured data.

Using sample IMDb dataset

Let’s say you want to conduct a sentiment analysis on movie reviews from the IMDb website. For the benefit of readers who want to follow along, we will be using the IMDb reviews dataset from BigQuery public datasets. Let’s look at the top 2 rows of the dataset.

Although the reviews table has 7 columns, we only use reviews and label columns to perform sentiment analysis for this case. Also, we are only considering negative and positive values in the label columns. The following query can be used to select only the required information from the dataset.

SELECT
 review,
 label,
FROM 
 `bigquery-public-data.imdb.reviews`
WHERE
 label IN ('Negative', 'Positive')

The top 2 rows of the result is as follows:

Methodology

Based on the dataset that we have, the following steps will be carried out:

1. Build a vocabulary list using the review column
2. Convert the review column into sparse tensors
3. Train a classification model using the sparse tensors to predict the label (“positive” or “negative”)
4. Make predictions on new test data to classify reviews as positive or negative.

Feature engineering

In this section, we will convert the text from the reviews column to numerical features so that we can feed them into a machine learning model. One of the ways is the bag-of-words approach where we build a vocabulary using the  words from the reviews and select the most common words to build numerical features for model training. But first, we must extract the words from each review. The following code creates a dataset and a table with row numbers and extracted words from reviews.

-- Create a dataset named `sparse_features_demo` if doesn’t exist
CREATE SCHEMA IF NOT EXISTS sparse_features_demo;




-- Select unique reviews with only negative and positive labels
CREATE OR REPLACE TABLE sparse_features_demo.processed_reviews AS (
 SELECT
   ROW_NUMBER() OVER () AS review_number,
   review,
   REGEXP_EXTRACT_ALL(LOWER(review), '[a-z]{2,}') AS words,
   label,
   split
 FROM (
   SELECT
     DISTINCT review,
     label,
     split
   FROM
     `bigquery-public-data.imdb.reviews`
   WHERE
     label IN ('Negative', 'Positive')
 )
);

The output table from the query above should look like this:

The next step is to build a vocabulary using the extracted words. The following code creates a vocabulary including word frequency and word index from reviews. For this case, we are going to select only the top 20,000 words to reduce the computation time.

-- Create a vocabulary using train dataset and select only top 20,000 words based on frequency
CREATE OR REPLACE TABLE sparse_features_demo.vocabulary AS (
 SELECT
   word,
   word_frequency,
   word_index
 FROM (
   SELECT
     word,
     word_frequency,
     ROW_NUMBER() OVER (ORDER BY word_frequency DESC) - 1 AS word_index
   FROM (
     SELECT
       word,
       COUNT(word) AS word_frequency
     FROM
       sparse_features_demo.processed_reviews,
       UNNEST(words) AS word
     WHERE
       split = "train"
     GROUP BY
       word
   )
 )
 WHERE
   word_index < 20000 # Select top 20,000 words based on word count
);

The following shows the top 10 words based on frequency and their respective index from the resulting table of the query above.

Creating a sparse feature

Now we will use the newly added feature to create a sparse feature in BigQuery. For this case, we aggregate word_index and word_frequency in each review, which generates a column as ARRAY[STRUCT] type. Now, each review is represented as ARRAY[(word_index, word_frequency)].

-- Generate a sparse feature by aggregating word_index and word_frequency in each review.
CREATE OR REPLACE TABLE sparse_features_demo.sparse_feature AS (
 SELECT
   review_number,
   review,
   ARRAY_AGG(STRUCT(word_index, word_frequency)) AS feature,
   label,
   split
 FROM (
   SELECT
     DISTINCT review_number,
     review,
     word,
     label,
     split
   FROM
     sparse_features_demo.processed_reviews,
     UNNEST(words) AS word
   WHERE
     word IN (SELECT word FROM sparse_features_demo.vocabulary)
 ) AS word_list
 LEFT JOIN
   sparse_features_demo.vocabulary AS topk_words
   ON
     word_list.word = topk_words.word
 GROUP BY
   review_number,
   review,
   label,
   split
);

Once the query is executed, a sparse feature named `feature` will be created. That `feature` column is an `ARRAY of STRUCT` column which is made of `word_index` and `word_frequency` columns. The picture below displays the resulting table at a glance.

Training a BigQuery ML model 

We just created a dataset with a sparse feature in BigQuery. Let’s see how we can use that dataset to train with a machine learning model with BigQuery ML. In the following query, we will train a logistic regression model using the review_number, review, and feature to predict the label:

-- Train a logistic regression classifier using the data with sparse feature
CREATE OR REPLACE MODEL sparse_features_demo.logistic_reg_classifier
 TRANSFORM (
   * EXCEPT (
       review_number,
       review
     )
 )
 OPTIONS(
   MODEL_TYPE='LOGISTIC_REG',
   INPUT_LABEL_COLS = ['label']
 ) AS
 SELECT
   review_number,
   review,
   feature,
   label
 FROM
    sparse_features_demo.sparse_feature
 WHERE
   split = "train"
;

Now that we have trained a BigQuery ML Model using a sparse feature, we evaluate the model and tune it as needed.

-- Evaluate the trained logistic regression classifier
SELECT * FROM ML.EVALUATE(MODEL sparse_features_demo.logistic_reg_classifier);

The score looks like a decent starting point, so let’s go ahead and test the model with the test dataset.

-- Evaluate the trained logistic regression classifier using test data
SELECT * FROM ML.EVALUATE(MODEL sparse_features_demo.logistic_reg_classifier,
 (
   SELECT
     review_number,
     review,
     feature,
     label
   FROM
     sparse_features_demo.sparse_feature
   WHERE
     split = "test"
 )
);

The model performance for the test dataset looks satisfactory and it can now be used for inference. One thing to note here is that since the model is trained on the numerical features, the model will only accept numeral features as input. Hence, the new reviews have to go through the same transformation steps before they can be used for inference. The next step shows how the transformation can be applied to a user-defined dataset.

Sentiment predictions from the BigQuery ML model

All we have left to do now is to create a user-defined dataset, apply the same transformations to the reviews, and use the user-defined sparse features to perform model inference. It can be achieved using a WITH statement as shown below.

WITH
 -- Create a user defined reviews
 user_defined_reviews AS (
   SELECT
     ROW_NUMBER() OVER () AS review_number,
     review,
     REGEXP_EXTRACT_ALL(LOWER(review), '[a-z]{2,}') AS words
   FROM (
     SELECT "What a boring movie" AS review UNION ALL
     SELECT "I don't like this movie" AS review UNION ALL
     SELECT "The best movie ever" AS review
   )
 ),


 -- Create a sparse feature from user defined reviews
 user_defined_sparse_feature AS (
   SELECT
     review_number,
     review,
     ARRAY_AGG(STRUCT(word_index, word_frequency)) AS feature
   FROM (
     SELECT
       DISTINCT review_number,
       review,
       word
     FROM
       user_defined_reviews,
       UNNEST(words) as word
     WHERE
       word IN (SELECT word FROM sparse_features_demo.vocabulary)
   ) AS word_list
   LEFT JOIN
     sparse_features_demo.vocabulary AS topk_words
     ON
       word_list.word = topk_words.word
   GROUP BY
     review_number,
     review
 )


-- Evaluate the trained model using user defined data
SELECT review, predicted_label FROM ML.PREDICT(MODEL sparse_features_demo.logistic_reg_classifier,
 (
   SELECT
     *
   FROM
     user_defined_sparse_feature
 )
);

Here is what you would get for executing the query above:

And that’s it! We just performed a sentiment analysis on the IMDb dataset from a BigQuery Public Dataset using only SQL statements and BigQuery ML. Now that we have demonstrated how sparse features can be used with BigQuery ML models, we can’t wait to see all the amazing projects that you would create by harnessing this functionality. 

If you’re just getting started with BigQuery, check out our interactive tutorial to begin exploring.

Large Machine Learning (ML) models – such as large language models, generative AI, and vision models – are dramatically increasing the number of trainable parameters and are achieving state-of-the-art results. Increasing the number of parameters results in the model being too large to fit on a single VM instance thus demands distributed compute to spread the model across multiple nodes. Google Kubernetes Engine (GKE) has built-in support for NCCL Fast Socket, to help improve the time to train large ML models with distributed, multi-node clusters.

Enterprises are looking for faster and cheaper performance to train their ML models. With distributed training, communicating gradients across nodes is a performance bottleneck. Optimizing inter-node latency is critical to reduce training time and costs. Distributed training uses collective communication as a transport layer over the network between the multiple hosts. Collective communication primitives such as all-gather, all-reduce, broadcast, reduce, reduce-scatter, and point-to-point send and receive are used in distributed training in Machine Learning. 

The NVIDIA Collective Communication Library (NCCL) is commonly used by popular ML frameworks such as TensorFlow and PyTorch. It is a highly optimized implementation for high bandwidth and low latency between NVIDIA GPUs. Google developed a proprietary version of NCCL called NCCL Fast Socket to optimize performance for deep learning on Google Cloud.

NCCL Fast Socket uses a number of techniques to achieve better and more consistent NCCL performance.

• Use of multiple network flows to attain maximum throughput. NCCL Fast Socket introduces additional optimizations over NCCL’s built-in multi-stream support, including better overlapping of multiple communication requests.
• Dynamic load balancing of multiple network flows. NCCL can adapt to changing network and host conditions. With this optimization, straggler network flows will not significantly slow down the entire NCCL collective operation.
• Integration with Google Cloud’s Andromeda virtual network stack.This increases overall network throughput by avoiding contentions in virtual machines (VMs).

We tested (NVIDIA NCCL tests) the performance of NCCL Fast Socket vs NCCL on various machine shapes with 2 node GKE clusters.

The following chart shows the results. For each machine shape, the NCCL performance without Fast Socket is normalized to 1. In each case, using NCCL Fast Socket demonstrated increased performance in a range of 1.3 to 2.6 times faster internetwork communication speed.

As a built-in feature, GKE users can take advantage of NCCL Fast Socket without changing or recompiling their applications, ML frameworks (such as TensorFlow or PyTorch), or even the NCCL library itself. To start using NCCL Fast Socket, create a node pool that uses the plugin with the --enable-fast-socket and --enable-gvnic flags. You can also update an existing node pool using gcloud container node-pools update.

gcloud container node-pools create NODEPOOL_NAME \
    --accelerator type=ACCELERATOR_TYPE, count=ACCELERATOR_COUNT \
    --machine-type=MACHINE_TYPE \
    --cluster=CLUSTER_NAME \
    --enable-fast-socket \
    --enable-gvnic

To achieve better network throughput with NCCL, Google Virtual NICs (gVNICs) must be enabled when creating VM instances. For detailed instructions on how to use gVNICs, please refer to the gVNIC guide. 

To verify that NCCL Fast Socket has been enabled, view the kube-system pods:

kubectl get pods -n kube-system

And the output should b similar to:

NAME                         READY   STATUS    RESTARTS   AGE
fast-socket-installer-qvfdw  2/2     Running   0          10m
fast-socket-installer-rtjs4  2/2     Running   0          10m
fast-socket-installer-tm294  2/2     Running   0          10m

To learn more visit GKE NCCL Fast Socket documentation. We look forward to hearing how NCCL Fast Socket improves your ML Training experience on GKE.

Enterprises are struggling to make high quality data easily discoverable and accessible for analytics, across multiple silos, to a growing number of people and tools within their organization. They are often forced to make tradeoffs—to move and duplicate data across silos to enable diverse analytics use cases or leave their data distributed but limit the agility of decisions.  

Today we are excited to announce Dataplex, an intelligent data fabric that provides a way to centrally manage, monitor, and govern your data across data lakes, data warehouses and data marts, and make this data securely accessible to a variety of analytics and data science tools.  

Dataplex provides an integrated analytics experience, bringing together the best of Google Cloud and open source tools, so you can rapidly curate, secure, integrate, and analyze data at scale. With built-in data intelligence using Google Artificial Intelligence (AI) and machine learning (ML) capabilities and a flexible consumption model, you can now spend less time wrestling with infrastructure and more time focused on driving business outcomes.

Dataplex enables you to:

• Achieve freedom of choice to store data wherever you want for the right price/performance and choose the best analytics tools for the job, including Google Cloud and open source analytics technologies such as Apache Spark and Presto.
• Enforce consistent controls across your data to ensure unified security and governance
• Take advantage of the built-in data intelligence using Google’s best in class AI/ML capabilities to automate much of the manual toil around data management and get access to higher quality data.

Early customers like Equifax, Loblaw, and ANZ are excited about using Dataplex to address data management complexity.

“Dataplex will greatly simplify the existing analytics workflows within Equifax with its unified data fabric and single interface for policy management and governance across all our analytics data. Its built-in data discovery and data quality features will ensure that our data scientists and analysts always have access to high quality data that they can trust. Dataplex aligns well with our enterprise data strategy and we are excited to partner with Google Cloud on this.”
—Kumar Menon, SVP, Data Fabric & Decision Science Technology, Equifax.

“Loblaw is Canada’s food and pharmacy leader, and we are excited to be an early adopter of Dataplex. We could significantly benefit from Dataplex as it provides a single pane of glass for end-to-end data management and governance. We are particularly interested in improving platform resilience and data quality by detecting anomalies as early as possible in the data pipeline with the help of Dataplex.”
—Elton Martins, Senior Director of Data Insights & Analytics, Loblaw

“We are undergoing a major data transformation at ANZ, bringing together our various data assets and building a cohesive data ecosystem for customer benefit. Dataplex’s vision and capabilities align well with our current data strategy to build a unified data fabric for all our analytics and AI/ML use cases. We are excited to partner with GCP on Dataplex and test the product in private preview.”
—Ashish Shekhar, Head of Technology – Enterprise Analytics & Applied AI, ANZ 

Dataplex is built for distributed data. We are starting with data stored in Google Cloud Storage and BigQuery, with support for other data sources coming soon. It provides a workflow-driven experience helping you build an open data platform and make data easily accessible to your end users while ensuring your policies and best practices are consistently enforced.

Organizing and curating your data

One of the core tenets of Dataplex is letting you organize and manage your data in a way that makes sense for your business, without data movement or duplication. For that, we are providing logical constructs like lakes, data zones and assets. Those constructs enable you to abstract away the underlying storage systems and become the foundation for setting policies around data access, security, lifecycle management, and so on. 

For example, you can create a lake per department within your organization (Retail, Sales, Finance, etc.) and create data zones that map to data readiness and usage (landing, raw, curated_data_analytics, curated_data_science, etc.). 

Once you have your lakes and zones setup, you can now attach data to these zones as assets. You can add data from different types of storage (e.g. GCS Bucket and BigQuery dataset) under the same zone. You can also attach data across multiple projects under the same zone.

You can ingest data into your lakes and zones using the tools of your choice including services such as Dataflow, Data Fusion, Dataproc, Pub/Sub or choose from one of our partner products. Dataplex provides built-in 1-click templates for common data management tasks. 

Securing your data 

Dataplex enables you to define and enforce consistent policies across your data, irrespective of where it physically resides. Data owners can easily set up policies for specific data domains based on business needs, without thinking about where the data is stored while data stewards get global visibility into governance policies and permissions across their data. 

You can apply security and governance policies for your entire lake, a specific zone, or an asset. Dataplex maps the policies to the underlying storage and pushes down permissions to the storage layer to provide end-to-end secure data access. Additionally, you can secure not just data but also related artifacts like notebooks, scripts, and models using the same set of access policies.

Making high quality data available for analytics and data science

One of the biggest differentiators for Dataplex is our data intelligence capabilities using Google’s best in class AI/ML technologies.  As you bring the data under management, Dataplex will automatically harvest the metadata for both structured and unstructured data, with built-in data quality checks. All of the metadata is automatically registered in a unified metastore, and made available for search and discovery. It is also published to Bigquery, Dataproc Metastore, and Data Catalog – ensuring that you have the same consistent data context and access across your tools.

For example, when you write parquet files to a Google Cloud Storage bucket – Dataplex will automatically extract metadata of these files, detect a tabular schema, including hive-style partitions, run data quality checks, and make this data queryable in BigQuery as an external table and from any open source or partner application – with the same consistent security and access policies you defined at the logical data layer. 

Your data scientists and analysts now have secured access to this data that meets your quality bar and governance rules via the tools of their choice, without needing any additional processing.

One-click access to collaborative analytics

Dataplex provides fully managed, one-click analytics environments enabling you to use the power of Apache Spark and BigQuery with support for other engines coming in the future. 

As data administrators, you now have the flexibility to pre-configure these environments with the right cost and financial governance measures without taking on the overhead of managing and maintaining the infrastructure required to power these environments. You can easily configure different environments for different types of workloads and share it with multiple users using their IAM credentials. Dataplex manages the provisioning, monitoring, scaling, and shutdown of these environments. 

As data scientists, analysts, and engineers, you now have a turn-key experience to run your analysis using notebooks and a SQL workbench. You can search for notebooks and scripts alongside data, save and share your work with other users, and schedule your notebooks or scripts for recurring workloads – all using the same integrated experience within Dataplex.

Building an open platform with industry leaders

We are partnering with industry leaders such as Accenture, Collibra, Confluent, Informatica, HCL, Starburst, NVIDIA, Trifacta, and others to build an open platform to power analytics at scale. Our partners are excited about the capabilities that Dataplex will provide:

“Collibra is excited to partner with Dataplex to provide data governance and data quality for consistent controls across distributed data. Pairing Collibra’s multi-cloud and hybrid solution with Dataplex allows enterprises to securely open up access to more, higher quality data for users and analytics using a single unified view.”
—Jim Cushman, Chief Product Officer, Collibra

“Dataplex builds on Google Cloud’s commitment to open source by integrating with Apache Kafka®, a leading open source platform for event streaming. We at Confluent, the platform for data in motion that completes Apache Kafka® to be enterprise-ready, are excited to partner with Dataplex to enable customers to bring together distributed, real-time data and build a unified data fabric for end to end analytics.”
—Paul Mac Farland, VP, Head of Customer Solutions and Innovation, Confluent

“We are excited to partner with Google Cloud’s Dataplex team as we look to provide our joint customers an integrated and open data fabric for analytics at scale. Extending Dataplex’s data management and data quality capabilities with Starburst Enterprise will accelerate time to value for enterprises looking to connect distributed data without having to move data.”
—Justin Borgman, CEO and Co-founder, Starburst

Next Steps

Dataplex is now available in preview for a select number of customers. For more information, visit our website or watch the recording. If you would like to sign up, please click here.

In 2013, long before the world was discussing clean energy and sustainable practices, two IIT Madras graduates — Swapnil Jain and Tarun Mehta — had an idea to develop India’s first-ever electrical scooter.

This was at a time when auto manufacturers were still focusing on fossil-fuel-driven vehicles and ‘eco-friendly’ mobility solutions were more a trendy alternative catering to a niche market.

The duo founded Ather Energy in 2013 and launched their first fully-electric scooter, the Ather S340, in Bengaluru in 2016. Since then, the company has released several new models into the market and is planning to expand to eight more cities by the end of the year.

To support the smooth running of their vehicles, lower costs, improve time to market, and create great customer experience, Ather turned to Google Cloud.

Read the Full Story on YourStory

Providing best-in-class customer service is crucial for the success of your business. Contact centers are a critical touch point, as they have to balance between representing your brand and prioritizing customer care. When your customers seek help and support, they expect efficient service that is accessible through modern voice and digital channels. In short, customer expectations are increasing—and that’s a problem if your contact center infrastructure and solutions are becoming outdated.

All of these factors are why today, we’re announcing Google Cloud Contact Center AI Platform, an expansion to Contact Center AI that offers an out-of-box, end-to-end solution for the contact center. It brings together the advantages of AI, cloud scalability, multi-experience capabilities, and tight integration with customer relationship management (CRM) platforms to unify sales, marketing, and support teams around data across the customer journey.

Improving customer experiences from all angles

Google Cloud’s Contact Center AI helps you leverage AI to scale your contact center interactions while maintaining a high level of customer satisfaction. Over the last two years, we have built a large group of partners, including the largest contact center and customer experience ISVs and our system integrator ecosystem, to bring Contact Center AI to customers. Today, we are helping enterprises across industries and geographies to cost-effectively reimagine contact center experiences. For example, Marks & Spencer reduced in-store call volume by 50%, and similarly, The Home Depot improved call containment by 185%, all while significantly increasing customer self-service engagement.

Adding to our Contact Center AI capabilities, Contact Center AI Platform is purpose-built for customer relationship management, extending your ability to offer personalized customer experiences that are consistent across your brand, whether delivered through a virtual agent, a human agent, or a combination of both. It eliminates many long-running pain points, from managing data fragmentation to replacing rigid customer experience flows with more engaging, personalized, and flexible support. With this addition, Contact Center AI now lets you:

• Orchestrate the customer journey by creating modern experiences that can be embedded in their chosen channels with mobile/web software developer kits (SDKs), compatible with iOS and Android;
• Leverage CRM as a single source of insight into the customer experience, to unify content, increase personalization, and automate processing with CRM data unification;
• Manage multiple channels without pivoting across voice, SMS, and chat support;
• Predict customer needs and route calls appropriately with AI-driven routing, based on both historical CRM data and real-time interactions;
• Automate scheduling, schedule adherence monitoring, and manage employee scheduling preferences with Workforce Optimization (WFO) integration;
• Provide customers with self-service via web or mobile interfaces using Visual Interactive Voice Response (IVR).

Helping you do more with contact centers

The addition of Contact Center AI Platform provides your partners the ability to integrate with Contact Center AI, so you can enjoy a more seamless experience operating your customer service center, with a complete view of the customer in a single workspace that includes real-time AI intelligence, native agent call controls, and real-time call transcription. For example, we are expanding our partnership with Salesforce to integrate Contact Center AI with Service Cloud Voice to deliver a unified Service Cloud agent console and Customer 360.

“Customers are continually raising their service expectations, and our research tells us 79% of consumers believe the experience a company provides is as important as its products and services,” said Ryan Nichols, SVP & GM, Contact Center, for Salesforce Service Cloud. “Through intelligence, workflows, and a deeper understanding of the customer, Salesforce’s Service Cloud Voice paired with Google’s Contact Center AI will empower agents with a seamless experience to help them wow customers.”

We are also excited to partner with UJET, an innovative and experienced Contact Center as a Service (CCaaS) provider. UJET offers secure user-centric design, scalability, and mobile-focused solution, with turnkey implementation, strong omnichannel capabilities, and best-in-class user experience, making their product a natural fit into Google’s contact center vision. To learn more about the partnership, see here.

Delivering impact for customers

Contact Center AI is already making a difference for our customers such as OneUnited Bank, the largest Black-owned bank in the U.S. “OneUnited Bank has been in partnership with Google Cloud and UJET, as well as a long-standing customer of Salesforce. The expansion and enhancements of Google Cloud’s Contact Center AI, along with its deeper integration with Salesforce, means better return on investment as we drive towards evolving our contact center to deliver exceptional client experiences,” said Teri Williams, President and Chief Operating Officer at OneUnited Bank.

Fitbit, which boasts more than 29 million active users, is also reaping the benefits. “Fitbit relies on Google Cloud and UJET to provide support to our customers with a mobile-first approach. This collaboration, in combination with a strong Salesforce integration, has helped us modernize our entire customer support experience,” stated Cassandra Johnson, VP, Devices & Services Customer Care & Vendor Management Office, at Google.

According to industry analyst Sheila McGee-Smith of McGee-Smith Analytics, “Google Cloud’s Contact Center AI is already a force in the contact center industry thanks to its early focus on AI for customer experience.” She continued, “Through their partnerships with UJET and Salesforce, as well as these expanded capabilities, Google Cloud’s Contact Center AI Platform will help define the future of customer service by powering more secure, engaging, and personalized customer experiences.”

Contact Center AI Platform is supported by a host of integration partners, including Accenture, CDW, Cognizant, Deloitte, HCL, IBM, Infosys, Quantiphi, Tata Consultancy Services, and Wipro. We will also continue to partner closely with the contact center and customer experience (CX) ISVs that our customers already rely on. If you already have a contact center solution provider, you can still integrate Google Cloud’s Contact Center AI into your existing environment.

To learn more about how you can leverage the power of AI to reimagine your contact center experience, visit our Contact Center AI page.