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.

AI researchers and engineers need better data to enable better AI solutions. The quality of an AI solution is determined by both the learning algorithm (such as a deep-neural network model) and the datasets used to train and evaluate that algorithm. Historically, AI research has focused much more on algorithms than datasets, despite their vital importance. As a result, many algorithms are freely available as starting points, but many important problems lack large, high-quality open datasets. Further, creating new datasets is expensive and error-prone.

Recently, the data-centric AI movement has emerged, which aims to develop new methodologies and tools for constructing better datasets to fix this problem. Conferences, workshops,  challenges, and platforms are being launched to support improving data quality and to foster data excellence. Thought leaders such as Andrew Ng at Landing.AI and Chris Re at Stanford University are encouraging AI developers to focus more on iterative data engineering than they do tuning their learning algorithms. Our CHI-best-paper-award-winning paper, “Everyone wants to do the model work, not the data work” highlighted the significance of data quality in the practice of ML. 

At Google, we are excited to contribute to data-centric AI. Today, Google Cloud is adding a new high value dataset to the Public Dataset Program, and Google researchers are announcing DataPerf, a new multi-organizational effort to develop benchmarks for data quality and data centric algorithms.

Google Cloud is committed to helping users improve their data quality, starting with supporting better public data. The Public Datasets program provides high quality datasets pre-configured on GCP for easy access. Google Cloud is adding a new high-value dataset developed by the MLCommons™ Association (which Google co-founded) to the Public Datasets program: The Multilingual Spoken Words Corpus: a rich audio speech dataset with more than 340,000 keywords in 50 languages with upwards of 23.4 million examples.

This new public dataset is aligned with the MLCommons Association vision for “open” datasets – accessible by all – that are “living” – continually being improved to raise quality and increase representation and diversity.

Google researchers, in collaboration with multiple organizations, are announcing the DataPerf effort at the NeurIPS Data-Centric AI workshop today, to develop benchmarks to improve data quality. Much like the the MLPerf™ benchmarking effort which is now the industry standard for machine learning hardware/software speed, DataPerf brings together the originators of prior efforts including: CATS4ML, Data-Centric AI Competition, DCBench, Dynabench, and the MLPerf benchmarks to define clear metrics that catalyze rapid innovation. DataPerf will measure the utility of training and test data for common problems, and algorithms for working with datasets such as: selecting core sets, correcting errors, identifying under-optimized data slices, and valuing datasets prior to labeling.

Together, supporting open, living datasets for core ML tasks, and the development of benchmarks to direct the rapid evolution of those datasets will empower the researchers and engineers who use Google Cloud to do even more amazing things – and we can’t wait to see what they create!

Acknowledgements: In collaboration with Lora Aroyo and Praveen Paritosh.

Given the May 1 deadline for students to enroll, colleges and universities have been carefully watching the numbers of students who put down deposits and commit to a school. Like nearly every other sector in the U.S., colleges and universities have been hit hard by the pandemic and economic downturn, making enrollment numbers more important than ever to maintain financial health and meet student body goals. Technology and AI-based models are providing a way for institutions to manage their admissions data efficiently and cost-effectively.

For example, an admissions officer may want to offset drops in international or out-of-state enrollment by attracting local talent so they can achieve their target class profile. Additionally, institutions have sought to increase the availability and access of higher ed and are intentionally recruiting more low-income and underrepresented populations. New technologies allow admissions officers to have a stronger grasp of the incoming class’ details. With the press of a button, users can switch between overall, in-state, out-of-state, and international enrollment figures. The “How Do Regions Compare to Last Year” tile lets users view the percentage of change and the total number of candidates by region, comparing 2020 and 2021 side-by-side. With access to data, regional recruiters can focus on individual candidates and develop a plan to change the numbers.

An innovative enrollment strategy at MSU

Michigan State University was looking to improve how they managed the recruiting process. They implemented an enrollment strategy that saw their out-of-state enrollment increase by over 20% and generate $5M in additional net tuition revenue in a single year. “We knew we needed better real-time and predictive insights about our enrollment pipeline, and we understood the value of bringing in external data on prospects, but we couldn’t do that by ourselves. 

The solution really helped us be innovative with our analytics and improve our enrollment outcomes,” said someone close to the project at Michigan State University.

Data-driven insights support informed enrollment decisions

Student admissions and marketing groups are turning to Google Cloud and Deloitte to help support their enrollment decisions with predictive, actionable insights across recruiting and admissions processes. The partnership resulted in Candidate360, a solution that helps institutions process and analyze large amounts of data and develop meaningful insights to support their enrollment goals and mission. Part of Google’s Student Success Services offerings, Candidate360 uses artificial intelligence and predictive analytics to help higher education institutions improve enrollment and matriculation, as well as optimize financial aid decisions.

With nine AI/ML models, Candidate360’s capabilities help university leaders, enrollment teams and recruiters do things like:

• Identify regions with clusters of candidates or see dips in application numbers
• Deploy marketing resources and admissions staff to the right geographic areas 
• Understand applicants’ needs in terms of academics, safety, and social activities
• Make intelligent financial aid decisions to recruit and retain talented students from underrepresented communities
• Respond faster to the students who are most likely to be accepted and then go on to enroll, stay enrolled, and graduate
• Work towards the ideal class composition and increase competitiveness with peer institutions

Enhancing the student experience

Enrollment is the first key touchpoint with incoming students, and tools like Candidate360 can help institutions build a student preferences profile throughout the application process. They can understand each student’s prospective major, areas of interest, dream job, and more, allowing colleges and universities to personalize recommendations and advising for enrolled students.

With integrated toolsets, Candidate360 and Google Cloud’s Student Success Services can help colleges and universities emerge from the pandemic stronger than ever. 

To see a demonstration of Candidate360 in action, contact our sales team. 

In our conversations with customers, few generative AI use cases have driven as much enthusiasm as generative search. Leaders at enterprises know the limits of traditional enterprise search, with queries producing a list of links based on pattern matching, and significant manual investigation required to find the more relevant answers. In generative AI, these leaders see an opportunity to leverage their data more effectively and deeply, including applying it to conversational apps that can answer complex questions, produce accurate summaries that synthesize many sources, and help people get the information they need, faster. 

Enterprise Search on Generative AI App Builder (Gen App Builder) lets organizations create custom chatbots and semantic search applications in as little as a few minutes, with minimal coding needed to get started and enterprise-grade management and security built in. Customers can combine their internal data with the power of Google’s search technologies and generative foundation models, delivering relevant, personalized search experiences for enterprise applications or consumer-facing websites. 

To date, most approaches to combining generative AI and search technology have been inadequate for the scale and reliability needed for enterprise use. For example, building search by breaking long documents into chunks and feeding each segment into an AI assistant typically isn’t scalable and doesn’t effectively provide insights across multiple sources. Likewise, many solutions are limited in the data types they can handle, prone to errors, and susceptible to data leakage. Bespoke, do-it-yourself approaches are generally no easier, with production-grade solutions often requiring complex tasks like integrating embeddings data with foundation models and significant use case data testing. Even when organizations make these efforts, the resulting solutions still tend to lack feature completeness and reliability, with significant investments of time and resources required to achieve high-quality results.

These challenges demonstrate that to effectively implement generative search, organizations typically need more than access to powerful foundation models. They also likely need the ability to ground model outputs in specific data, so that outputs are more relevant and less likely to include mistakes or “hallucinations.” They generally need safeguards that protect their data, how it is accessed, and how it is used. And they generally need the process to be high-performant and scalable out of the box, making the functionality easy to use even if the organization lacks data science and machine learning expertise.

Let’s look at how Enterprise Search on Gen App Builder helps customers bypass these scale and reliability challenges, so they can start leveraging generative search quickly. 

The intersection of generative AI and enterprise data 

Enterprise Search on Gen App Builder lets developers create search engines that help ground outputs in specific data sources for accuracy and relevance, can handle multimodal data such as images, and include controls over how answer summaries are generated. Multi-turn conversations are supported so that users can ask follow up questions as they peruse outputs, and customers have control over their data—including the ability to support HIPAA compliance for healthcare use cases. All of this is available as a fully managed service, so developers can focus on building rather than cloud complexity. 

Gen App Builder’s out-of-box capabilities can remove the need for data chunking, generating embeddings, or managing indexes, hiding complexity behind a straightforward interface that lets developers build apps in minutes with little or no coding and no prior machine learning experience. With the ability to ingest large volumes of documents and support for both unstructured and structured data, apps built with Gen App Builder help customers solve the long-standing headache of finding relevant information across the organization, turning tasks that used to take hours into quick searches or conversational explorations with an app. 

These capabilities are underpinned by both Google’s foundation models and a variety of Google Search technologies, including:

• Semantic search, which helps deliver more relevant results than traditional keyword-based search techniques by using natural language processing and machine learning techniques to infer relationships within the content and intent from the user’s query input. 
• Google’s understanding of how users search for information. 
• Google’s expertise in understanding relevance, which considers factors such as content popularity and user personalization of content when determining the order in which search results are displayed. 

For more advanced use cases, Gen App Builder can be easily integrated with Vertex AI for in-depth foundation model tuning, enabling flexible input and output search formats. 

As always with our AI products, Gen App Builder has been evaluated for alignment with our AI Principles, which is reflected by the product’s many safeguards against bias, toxic content, and unhelpful outputs. Whether for prototypes built in minutes or apps with many custom components, Enterprise Search on Gen App Builder offers a robust suite of user-friendly tools for customers across industries and levels of expertise. 

How customers are innovating with Enterprise Search 

After gaining access to Enterprise Search on Gen App Builder via our trusted tester program, a number of customers are already leveraging the product for novel use cases. 

Priceline is harnessing Gen App Builder and Vertex AI for a range of projects, including internal search engines for employees and a new chatbot to assist customers as they make travel plans. Slated to be available across both desktop and mobile experiences, Priceline’s chatbot will help customers find the right information faster via always-on, personalized experiences, including answering nuanced questions like, “What are the best 4-star hotel options in midtown Manhattan within walking distance to Central Park?” and “Can you help me extend my hotel reservation for an additional night?”

“Priceline is charting a course to transform the novelty of generative AI into lasting value for our customers and our business. We believe it’s not just about having the latest technology; it’s also about practically targeting innovation to the right challenges and opportunities,” said Marty Brodbeck, Chief Technology Officer, Priceline. “With Google Cloud as our AI innovation partner, we’re doubling down on our commitment to delivering the fastest, most seamless and informative booking experience for our customers, from personalized planning and travel inspiration to customer service.”

Vodafone is experimenting with Enterprise Search and foundation models on Gen App Builder to build a tool that can rapidly and securely query documents, search, and understand specific commercial terms and conditions. Vodafone Voice and Roaming Services has over 10,000 contracts with other telecommunications companies worldwide, in a variety of formats such as PDFs, images, and complex tables. Searching this document repository is often a time consuming process for employees. 

“Every day we introduce and manage new services like 5G to a roaming footprint comprising more than 700 operators in 210 countries to ensure both Vodafone and our partners’ business customers, holidaymakers, and Internet of Things devices stay connected when abroad. With Enterprise Search on Gen App Builder, we are building an intelligent assistant to securely and quickly search contracts.” said Sherif Bakir, CEO of Vodafone Voice and Roaming Services. “With generative AI, we’re accelerating otherwise protracted processes, increasing productivity and operational efficiency.”

Software startup Trender.ai, which creates customer monitoring and intelligence solutions for B2B relationships, is using Gen App Builder to build a product that can synthesize information from social media, public sources, and CRM data so that users can form more productive, personal relationships with prospects and customers.

“With Enterprise Search on Gen App Builder, we have been able to do things in the last month that we had projected to take 12-18 months on our prior roadmap,” said Betsy Bilhorn, co-founder at Trender.ai. “We had expected it would take at least 12 months to build and train models from an individual’s public social, web, and other data, and to then be able to ask questions like ‘What things are most important to this person?’ or ‘When and how is the best way to make an introduction to this prospect that they’ll respond to?’ Last year, we thought getting to this vision was a bit of a moonshot for a startup our size — but now we were able to achieve this in under a month.” 

Stop searching for solutions — start discovering insights

We’re excited to see how customers use Enterprise Search on Gen App Builder to leverage data in powerful ways, discover new insights, and create useful, personal, and efficient experiences. 

Today, we’re pleased to help our customers accomplish these goals, with the general availability of Enterprise Search on Gen App Builder for customers on the allowlist (i.e., approved for access). Please contact your Google Cloud sales team for access and pricing details. We are also launching two new features within Enterprise Search on Gen App Builder that are available today in our preview offering: multi-turn search, which supports asking follow-up questions, and content recommendations to find semantically relevant content. Access to the preview offering is available via Google Cloud’s trusted tester program. Read more about Enterprise Search on Gen App Builder and sign up for access on our webpage. 

We’re also bringing generative AI features of Enterprise Search to our existing solutions like Contact Center AI and Document AI. As an example, starting this month, customers can preview generative AI-powered search in Document AI Warehouse. To keep up with our latest generative AI news, don’t miss The Prompt or our generative AI primer for executives on Transform with Google Cloud.