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.

Oden Technologies is using the Internet of Things (IoT) to improve the factories of today. The giant network of “things” (including people) connected to each other via the Internet has the potential to reduce waste, increase efficiency, and improve safety across all walks of life. Oden is leading IoT innovation in manufacturing by combining wireless connectivity, big data, and cloud computing.

The use of data to improve manufacturing is practically as old as manufacturing itself. But the computerization of manufacturing has resulted in broad and rapid changes to the way data is collected and processed, as well as the sheer volume of data available.

Oden’s goal is to help manufacturers tap into this data to quickly identify process trends and even warning signs of machine breakdown. Such visibility can reveal opportunities to improve manufacturing and maintenance processes that reduce waste and increase profit margins.

Oden designs and develops data collection devices that can plug into almost any kind of machine and can wirelessly transmit data with minimal complexity and setup time.

Once devices are installed, the Oden technology platform processes data to give manufacturers cutting-edge analytics that are easy to comprehend. Analysis produced by the platform provides factory engineers with data points such as detailed root-cause analysis down to the second, factory-wide performance in real-time, and trend analysis.

Improving cloud delivery

Oden’s previous cloud platform performed satisfactorily, but the company evaluated alternatives in search of potential reductions in cost and complexity and increases in performance.

When evaluating Google Cloud Platform, Oden discovered it would require fewer virtual machine (VM) instances for equivalent performance, which would cut costs. Furthermore, Oden could gain more sophisticated data analytics and machine learning capabilities compared with its existing cloud provider.

Today, Oden runs its entire platform on Google Cloud Platform including Google Compute Engine, Google Cloud Pub/Sub, Google Cloud Bigtable, Google Stackdriver, and Google Kubernetes Engine.

“In order to serve our customers, we need a cloud platform that can scale reliably while keeping costs low, perform under heavy loads, and consistently deliver sophisticated features such as machine learning,” says Willem Sundblad, CEO and Founder at Oden Technologies. “Google Cloud Platform is way ahead in all of these areas compared to our previous cloud provider.”

Capturing tens of millions of metrics a day

Using Google Cloud Platform, Oden can help an average factory capture and store approximately 10 million metrics on a single manufacturing line every day.

Metrics can include extremely granular detail, such as the amount of electricity going to machines, the amount of raw material consumed, and the volume of material produced. Sensors can also capture and transmit environmental information such as temperature, humidity, and dew point so that manufacturers can identify weather-related and seasonal impacts on production.

The updated Oden Cloud Platform uses Kubernetes Engine—powered by the open source Kubernetes system—to run application program interfaces (APIs) that capture data from Oden’s wireless devices on the factory floor.

Google Cloud Pub/Sub then sends the data in real time to Google Cloud Bigtable, where data is processed using Oden’s proprietary analytics tools. Google Stackdriver supports Google Cloud Platform monitoring, logging, and diagnostics, which help Oden deliver its cloud platform with confidence.

Oden Technologies builds dashboards powered by Kubernetes Engine, which pull analyzed data from Google Cloud Bigtable. The dashboards provide customers with real-time visibility into their manufacturing lines. Oden Factory Cloud dashboards allow customers to delve deeper into their data to fine-tune production processes or discover the root causes of production issues.

With the previous cloud provider, Oden required 80 VM instances to run the dashboards. With Google Cloud Platform that number has been cut to 45, which dramatically reduces costs and complexity.

“We migrated from our previous cloud provider to Google Cloud Platform in just one month,” says Willem. “Further, our storage and data analytics costs have decreased by 30%. Cost savings like these allow us to protect customers from rising expenses, keeping us focused on bringing the best products possible to market.”

Faster data access; more efficient factories

With Google Cloud Platform, Oden can now deliver a complete factory analytics picture to manufacturers. In environments where thousands of variables affect the bottom line, businesses can now automatically and perpetually record machine and performance measurement. Oden Factory Cloud gives customers access to comprehensive data insights and can eliminate reliance on onsite infrastructure investments to run their own analytics.

Because manufacturers have access to live data and can analyze production data quickly, they can troubleshoot and resolve problems in minutes rather than months. Such information helps improve product quality, minimize unplanned downtime, cut costs, and improve profitability.

“With the help of Google Cloud Platform, we are helping our customers to be data-driven, which wasn’t possible before,” adds Willem. “They now understand that data is their most important asset. That allows them to be more innovative and continually improve their production processes.”

In-memory data stores are a fundamental infrastructure for building scalable, high-performance applications. Whether it is building a highly responsive ecommerce website, creating multiplayer games with thousands of users, or doing real-time analysis on data pipelines with millions of events, an in-memory store helps provide low latency and scale for millions of transactions. Redis is a popular in-memory data store for use cases like session stores, gaming leaderboards, stream analytics, API rate limiting, threat detection, and more. Another in-memory data store, open source Memcached, continues to be a very popular choice as a caching layer for databases and is used for its speed and simplicity.

We’re announcing Memorystore for Memcached in beta, a fully managed, highly scalable service that’s compatible with the open source Memcached protocol. We launched Memorystore for Redis in 2018 to let you use the power of open source Redis easily without the burden of management. This announcement brings even more flexibility and choice for your caching layer. 

Highlights of Memorystore for Memcached

Memcached offers a simple but powerful in-memory key value store and is popular as a front-end cache for databases. Using Memcached as a front-end store not only provides an in-memory caching layer for faster query processing, but it can also help save costs by reducing the load on your back-end databases.

Using Memorystore for Memcached provides several important benefits:

• Memorystore for Memcached is fully open source protocol compatible. If you are migrating applications using self-deployed Memcached or other cloud providers, you can simply migrate your application with zero code changes.
• Memorystore for Memcached is fully managed. All the common tasks that you spend time on, like deployment, scaling, managing node configuration on the client, setting up monitoring, and patching, are all taken care of. You can focus on building your applications.
• Right-sizing a cache is a common challenge with distributed caches. The scaling feature of Memorystore for Memcached, along with detailed open source Memcached monitoring metrics, allows you to scale your instance up and down easily to optimize for your cache-hit ratio and price. With Memorystore for Memcached, you can scale your cluster up to 5 TB per instance.
• Auto-discovery protocol lets clients adapt to changes programmatically, making it easy to deal with changes to the number of nodes during scaling. This drastically reduces manageability overhead and code complexity.
• You can monitor your Memorystore for Memcached instances with built-in dashboards in the Cloud Console and rich metrics in Cloud Monitoring.

Memorystore for Memcached can be accessed from applications running on Compute Engine, Google Kubernetes Engine (GKE), App Engine Flex, App Engine Standard, and Cloud Functions.

The beta launch is available in major regions across the U.S., Asia, and Europe, and will be available globally soon.

Getting started with Memorystore for Memcached

To get started with Memorystore for Memcached, check out the quick start guide. Sign up for a $300 credit to try Memorystore and the rest of Google Cloud. You can start with the smallest instance and when you’re ready, you can easily scale up to serve performance-intensive applications. Enjoy your exploration of Google Cloud and Memorystore for Memcached.

Customer expectations have shifted as a result of evolving needs. Across industries, customers expect that you treat them as individuals, and demonstrate how well you understand and serve their unique needs. This concept—personalization—is the idea that you’re delivering a tailored experience to each customer corresponding to their needs and preferences; you’re setting up a process to create individualized interactions that improves the customer’s experience. According to Salesforce, 84% of consumers say being treated like a person, not a number, is very important to winning their business. 

Entire industries are undergoing digital transformation to better serve their customers through personalized experiences. For example, retailers are improving engagement and conversion with personalized content, offers, and product recommendations. Advertising technology organizations are increasing the relevance and effectiveness of their ads using customer insights like their specific interests, purchasing intent, and buying behavior. Digital music services are helping their customers discover and enjoy new music, playlists, and podcasts based on their listening behavior and interests. 

As customers want more and more personalization, modern technology is making it possible for many more businesses to achieve this. In this post, we’ll look at some common challenges to implementing personalization capabilities and how to solve them with transformative database technologies like Google Cloud’s Bigtable. Bigtable powers core Google services such as Google Maps that supports more than a billion users, and its petabyte scale, high availability, high throughput, and price-performance advantages help you deliver personalization at scale.  

Challenges with personalization 

Data is at the heart of personalization. To deliver personalization at scale, an application needs to store, manage, and access large volumes of data (a combination of customer-specific data and anonymized aggregate data across customers) to develop a deep understanding of the behavior, needs, and preferences of each customer. Your database needs to very quickly write large volumes of data concurrently for all active customers. You need to continuously capture data on customer behavior because each step potentially informs the next, e.g., adding an item to a shopping cart can be used to trigger new recommendations for related or complementary products. Much of this data needed for personalization is semi-structured and sparse, and therefore requires a database with a flexible data model. 

Personalization at scale requires large volumes of data to be read in near real-time so that it can be in the critical serving path to deliver a seamless user experience, often with a total application latency of less than 100ms. This means your requests to the database need to return results with latencies of single-digit milliseconds. You need to ensure that application latencies do not degrade as you onboard more customers. Data needs to be organized efficiently and integrated with other tools so that you can run deep analytical queries and use machine learning (ML) models to develop personalized recommendations, and store the aggregates in your operational database for serving your customers. You also need the ability to run large batch reads for analytics without affecting the serving performance of your application. 

In addition, you need to ensure that your database costs do not explode with the popularity of your application. Your database needs to consistently deliver low total cost of ownership (TCO), and high price-performance as your data volumes and throughput needs grow. Your database needs to scale seamlessly and linearly to deliver consistent, predictable performance to all users around the world. Additionally, your database needs to be easy to manage, so that you can focus on your application instead of managing the complexity of your database.

Why a NoSQL database is the right fit for personalization 

Every database reflects a set of engineering tradeoffs. When relational databases were designed 40 years ago, storage, compute, and memory were thousands of times more expensive than they are today. Databases were deployed on a single server to a relatively small number of concurrent users, whose access to the systems tended to be during normal business hours when users had network access. Relational databases were designed with these resources, costs, and use in mind. They work very hard to be storage and memory efficient, and assume a single server for deployments. 

As the costs of storage, memory, and compute decreased, and as data and workloads grew to exceed the capacity of commodity hardware, engineers began to reconsider these tradeoffs with different goals in mind. New types of databases later emerged that assumed distributed architectures so they could be easier to scale, especially with cloud infrastructure. With this approach the tradeoff in turn was to forego the sophistication of SQL and much of the data integrity and transactional capabilities developed in relational systems. These systems are commonly called NoSQL databases.

Traditional relational databases assume a fixed schema that will change infrequently over time. While this predictability of data structure allows for many optimizations, it also makes it difficult and cumbersome to add new and varying data elements in your application. NoSQL databases, such as key value stores and document databases, relax the rigidity of the schema and allow for data structures to evolve much more easily over time. Flexible data models speed the pace of innovation in your application, and increase your ability to iterate on your ML models, which is essential for personalization. In addition, the scalability of systems like Cloud Bigtable allow you to deliver personalization to millions of concurrent users while you continue to evolve how you personalize experiences for your customers.

How Cloud Bigtable enables personalization at scale

Cloud Bigtable supports personalization at scale with its ability to handle millions of requests per second, cost-effectively store petabytes of data¹, and deliver consistent single-digit millisecond latencies for reads and writes. Bigtable delivers a unique mix of high performance and low operating cost to reduce your TCO. 

We’ve heard from Spotify, Segment, and Algolia about how they’ve built personalized experiences for their customers with Bigtable. Check out this presentation to hear Peter Sobot of Spotify describe how they use Bigtable for personalization. 

Let’s imagine a scenario where your application takes off like a rocketship, and grows to 250 million users. Let’s assume a peak 1.75 million concurrent users of your application², with each user sending two requests per minute to your database. This will drive 3.5 million requests per minute to your database, or approximately 58.3K requests per second. Pricing for Bigtable to run this workload will start at under $400 per day³.

Bigtable scales throughput linearly with additional nodes. With separation of compute and storage, Bigtable automatically configures throughput by adjusting the association of nodes and data to provide consistent performance. When a node is experiencing heavy load, Bigtable automatically moves some of the traffic to a node with lower load to improve the overall performance. Bigtable also supports cross-region replication, with local writes in each region. This allows you to manage your data near your customers’ geographic locations, reducing network latency and bringing predictable, low-latency reads and writes to your customers in different regions around the world. 

Bigtable is a NoSQL database developed and operated by Google Cloud. Bigtable provides a column family data model that allows you to flexibly store varying data elements for customers associated with their behavior and preferences, store a very large number of such data elements across your customers, and quickly iterate on your application. Bigtable supports trillions of rows with millions of columns. Each row in Bigtable supports up to 256 MB of data, so that you can easily store all personalized data for a customer in a single row. Bigtable tables are sparse, and there is no storage penalty for a column that is not used in a row; you only pay for the columns that store values.

BigQuery ML allows you to create and run ML models directly in BigQuery to develop personalization recommendations that you can bring back to Bigtable. You can easily pipe Bigtable data into BigQuery to run deep analytical queries and develop recommendations. These aggregates, like computed recommendations, are brought back to Bigtable so your application can serve those recommendations to users with low latency and massive scale. 

Bigtable integrates with the Apache Beam ecosystem and Dataflow to make it easier for you to process and analyze your data. With application profiles and replication in Bigtable, you can isolate your workloads so that batch reads do not slow down your serving workload that has a mix of reads and writes. This enables your application to perform near real-time reads at scale to develop and train machine learning models in TensorFlow for personalization. Bigtable gives you the right operational data platform to develop personalization recommendations offline or in real-time, and serve them to your customers.

Here’s a look at conceptual schema examples for personalization in ecommerce:

And here’s a quick overview of what personalization use cases require, and how Bigtable addresses them.

Bigtable is fully managed to free you from the complexity of managing your database, so that you can focus on delivering a deeply personalized experience to your customers. Learn more about Bigtable.

<sup>1. Storage pricing (HDD) starts at $0.026 per GB/mo (us-central1)
2. Assumes application is used 24 hours a day, average user session is 5 minutes (Android app average), and daily peak is 2x the average. (250 million / (24 hours / 5 minutes) *2 = 1,736,111 peak concurrent users (us-central1 region)
3. Cloud Bigtable pricing for us-central1 region. Assumes 25 TB SSD storage (100 KB per user, for 250 million users) per month, 10 compute nodes per month (with no replication), includes data backup. Bigtable pricing details.</sup>