Unstructured data such as images, speech and textual data can be notoriously difficult to manage, and even harder to analyze. The analysis of unstructured data includes use cases such as extracting text from images using OCR, sentiment analysis on customer reviews and simplifying translation for analytics. All of this data needs to be stored, managed and made available for machine learning.

The new BigQuery ML inference engine empowers practitioners to run inferences on unstructured data using pre-trained AI models. The results of these inferences can be analyzed to extract insights and improve decision making. This can all be done in BigQuery, using just a few lines of SQL.

In this blog, we’ll explore how the new BigQuery ML inference engine can be used to run inferences against unstructured data in BigQuery. We’ll demonstrate how to detect and translate text from movie poster images, and run sentiment analysis against movie reviews.

BigQuery ML’s new inference engine

Google Cloud is home to a suite of pre-trained AI models and APIs. The BigQuery ML inference engine can call these APIs and manage the responses on your behalf. All you have to do is define the model you want to use and run inferences against your data. All of this is done in BigQuery using SQL. The inference results are returned in JSON format and stored in BigQuery for analysis.

Why run your inferences in BigQuery?

Traditionally, working with AI models to run inferences required expertise in programming languages like Python. The ability to run inferences in BigQuery using just SQL can make generating insights from your data using AI simple and accessible. BigQuery is also serverless, so you can focus on analyzing your data without worrying about scalability and infrastructure.

The inference results are stored in BigQuery, which allows you to analyze your unstructured data immediately, without the need to move or copy your data. A key advantage here is that this analysis can also be joined with structured data stored in BigQuery, giving you the opportunity to deepen your insights. This can simplify data management and minimize the amount of data movement and duplication required.

Which models are supported?

For now, the BigQuery ML inference engine can be used with these pre-trained Vertex AI models:

• Vision AI API: This model can be used to extract features from images managed by BigQuery Object Tables and stored on Cloud Storage. For example, Vision AI can detect and classify objects, or read handwritten text.
• Translation AI API: This model can be used to translate text in BigQuery tables into over one hundred languages.
• Natural Language Processing API: This model can be used to derive meaning from textual data stored in BigQuery tables. For example, features like sentiment analysis can be used to determine whether the emotional tone of text is positive or negative.

So, how does this work in practice? Let’s look at an example using images of movie posters

1. We will define our pre-trained models for Vision AI, Translation AI and NLP AI in BigQuery ML.
2. We’ll then use Vision AI to detect the text from some classic movie posters images. 
3. Next, we’ll use Translation AI to detect any foreign posters and translate them to a language of our choosing – English in this case. 
4. Finally, we’ll combine our unstructured data with structured data in BigQuery.
   We’ll use the extracted movie titles from our movie posters to look up the viewer reviews from the BigQuery IMDB public dataset. We can then run sentiment analysis against these reviews using NLP AI.

Note: The BigQuery ML inference engine is currently in Preview. You will need to complete this enrollment form to have your project allowlisted for use with the BQML Inference Engine.

We’ll give examples of the BigQuery SQL needed to define your models and run your inferences. You’ll want to check out our notebook for a detailed guide on how to get this up and running in your Google Cloud project.

1. Define your AI Models in BigQuery

You will need to enable the APIs listed below, and also create a Cloud resource connection to enable BigQuery to interact with these services.

You can then run the CREATE MODEL query for each AI service to create your pretrained models, replacing the model_name as required.

CREATE OR REPLACE MODEL 
`{PROJECT_ID}.{DATASET_ID}.{VISION_MODEL_NAME}`
REMOTE WITH 
CONNECTION `{PROJECT_ID}.{REGION}.{CONN_NAME}`
OPTIONS ( remote_service_type = '<model_name>' );

2. Use the Vision AI API to detect text in images stored in Cloud Storage

You will need to create an object table for your images in Cloud Storage. This read-only object table provides metadata for images stored in Cloud Storage:

CREATE OR REPLACE EXTERNAL TABLE
`{PROJECT_ID}.{DATASET_ID}.{OBJECT_TABLE_NAME}`
WITH
CONNECTION `{REGION}.{CONN_NAME}`
OPTIONS (object_metadata = 'SIMPLE', uris = ['{BUCKET_LOCATION}/*']);

To detect the text from our posters, you can then use ML.ANNOTATE_IMAGE and specify the text_detection feature.

SELECT
       ml_annotate_image_result.full_text_annotation.text AS text_content,
       *
FROM
       ML.ANNOTATE_IMAGE(
         MODEL `{PROJECT_ID}.{DATASET_ID}.{VISION_MODEL_NAME}`,
         TABLE `{DATASET_ID}.{OBJECT_TABLE_NAME}`,
         STRUCT(['TEXT_DETECTION'] AS vision_features));

A JSON response will be returned to BigQuery that includes the text content and language code of the text. You can parse the JSON to a scalar result using the dot annotation highlighted above.

3. Use the Translation AI API to translate foreign movie titles 

ML.TRANSLATE can now be used to translate the foreign titles we’ve extracted from our images into English. You just need to specify the target language and the table of the movie posters for translation:

SELECT
text_content,
STRING(ml_translate_result.translations[0].detected_language_code)
as original_language,
STRING(ml_translate_result.translations[0].translated_text)
as translated_title
FROM
  ML.TRANSLATE(
MODEL `{PROJECT_ID}.{DATASET_ID}.{TRANSLATE_MODEL_NAME}`,
TABLE `{DATASET_ID}.image_results`,
STRUCT('TRANSLATE_TEXT' as translate_mode, "en" as target_language_code));

Note: The table column with the text you want to translate must be named text_content:

The table of results will include json that can be parsed to extract both the original language and the translated text. In this case, the model has detected that title text is in French and has translated it to English:

4. Finally, use natural language processing (NLP) to run sentiment analysis against movie reviews

You can easily join inference results from your unstructured data with other BigQuery datasets to bolster your analysis. For example, we can now join the movie titles we extracted from our posters with thousands of movie reviews stored in BigQuery’s IMDB public dataset `bigquery-public-data.imdb.reviews`.

You can use ML.UNDERSTAND_TEXT with the analyze_sentiment feature to run sentiment analysis against some of these reviews to determine whether they are positive or negative:

SELECT
   primary_title, start_year, text_content AS review,
   FLOAT64(ml_understand_text_result.document_sentiment.score) AS score,
   FLOAT64(ml_understand_text_result.document_sentiment.magnitude) AS magnitude,
FROM
   ML.UNDERSTAND_TEXT(
     MODEL `{PROJECT_ID}.{DATASET_ID}.{NLP_MODEL_NAME}`,
           (
           SELECT
             primary_title, start_year, review AS text_content
           FROM
             `bigquery-public-data.imdb.title_basics` titles
           JOIN
             `bigquery-public-data.imdb.reviews` reviews
           ON
             reviews.movie_id = titles.tconst
           WHERE
             UPPER(titles.primary_title) = 'THE LOST WORLD' AND
             start_year = 1925
           ),
      STRUCT("analyze_sentiment" AS nlu_option)) ;

Note: The table column with the text you want to analyze must be named text_content:

The JSON response will include a score and magnitude. The score indicates the overall emotion of the text while the magnitude indicates how much emotional content is present:

So, how did the Lost World compare with other movies that year?

To wrap up, we’ll compare the average review score of the 1925 Lost World movie to other movies released that year to see which was more popular. This can be done using familiar SQL analysis:

SELECT
 primary_title, start_year,
 AVG(FLOAT64(ml_understand_text_result.document_sentiment.score))AS av_score,
 AVG(FLOAT64(ml_understand_text_result.document_sentiment.magnitude)) AS av_magnitude
FROM
 ML.UNDERSTAND_TEXT(
   MODEL `{PROJECT_ID}.{DATASET_ID}.{NLP_MODEL_NAME}`,
         (
         SELECT
           primary_title, start_year, movie_id, review AS text_content
         FROM
           `bigquery-public-data.imdb.title_basics` titles
         JOIN
           `bigquery-public-data.imdb.reviews` reviews
         ON
           reviews.movie_id = titles.tconst
         WHERE
           start_year = 1925
         ),
   STRUCT("analyze_sentiment" AS nlu_option))
GROUP BY
   primary_title, start_year
ORDER BY
   av_score DESC;

It looks like The Lost World narrowly missed out on the top spot to Sally of the Sawdust!

Want to learn more?

Check out our notebook for a step by step guide on using the BQML inference engine for unstructured data in Google Cloud. You can also check out our Cloud AI service table-valued functions overview page for more details. Curious about pricing? The BQML Pricing page gives a breakdown of how costs are applied across these services.

The app economy has enabled a huge range of unique business models to flourish. One such model is online food ordering and delivery services, in which apps leverage geo-location data to aggregate local food choices and offer personalized options to consumers.

A leading company in this space is Just Eat. Launched in the UK in 2001 with a vision of ‘serving the world’s greatest menu. Brilliantly.’ The company has capitalized on the popularity of online food delivery and grown its presence across 12 markets. 

Just Eat acts as an intermediary between take-out food outlets and hungry customers, giving local restaurants access to a broader base of potential diners, while providing consumers with an easy and secure way to order and pay for food from their favourite restaurants.

Today the company helps 27 million customers find food from more than 112,000 restaurants—everything from homemade Italian pasta, to Chinese noodle bowls, to fish-and-chips. 

Data is the fuel of Just Eat’s rapid growth, but it wasn’t always looked at that way. In its early days, Just Eat struggled with the deluge of information and faced fragmentation across its systems. In fact, the company realized its legacy data vendor wasn’t capable of ingesting 90 percent of the data produced by its food platform. This was incredibly frustrating for Just Eat’s analysts and data scientists, who had to waste time cleaning up sources instead of leveraging the data to create a better user experience. 

Just Eat turned to Google Cloud, and now uses machine learning (ML) to power sophisticated consumer recommendations on both its app and website. It also makes heavy use of features offered by Google Cloud Platform, including BigQuery for running analytics on its customer data set and Cloud Pub/Sub for messaging app users with relevant offers in real-time. 

Having all of Just Eat’s data in one platform has translated into real value for its customers. With Google Cloud tools, Just Eat has created its own proprietary Customer Ontology framework, which today contains 5.5 billion features that better understand consumers’ behavior and food habits, and provides insights into previous visits.

Just Eat recently created an “Adventurous Index” to map its customers according to their ordering habits, enabling them to tailor their marketing and user experiences. For example, mid-adventurous customers are shown a choice of restaurants that serve their most ordered cuisine, while adventurous customers can choose from restaurants that serve a wider variety. This not only has prompted consumers to be more adventurous with their choices, but also has led to more business at a more diverse set of restaurants.

Matt Cresswell, Director of Customer Platforms at Just Eat said that Google Cloud has become integral to its product delivery: “Consumer food choice is a hugely nuanced topic. We know that individuals have their own unique journeys when they use Just Eat. We’ve sought to create a truly one-to-one relationship with every customer. The changes we’ve made to the platform mean they can access the dishes they enjoy at the touch of a fingertip, and find inspiration to discover new dishes they’ll love. We’re grateful to Google Cloud for helping us support our customers on their culinary explorations.”

Scaling Virtual Support in the Pandemic Era: The AI Connection

Since the early days of the pandemic, we’ve partnered with government organizations and academic institutions to serve communities at scale with Contact Center Artificial Intelligence (CCAI). I sat down with Bill MacKenzie, IT liaison for the Upper Grand School District in Ontario, and Marco Palermo, director of digital government and modernization, to discuss how they embraced CCAI to introduce scalable service delivery to residents and students alike. I’m sharing more on their stories below, and for the full overview, check out our Google Cloud Public Sector Summit session, Scaling Virtual Support in the Pandemic Era: The AI Connection.

Upper Grand School District: Answering questions with speed and accuracy

Bill MacKenzie described the Upper Grand School District’s struggles at the beginning of the pandemic, particularly helping parents with IT issues. The staff-oriented help desk was not equipped to assist parents trying to securely login for students as young as kindergarten. Without sufficient support for parents, the District was struggling to handle thousands of phone calls a day.

To alleviate the manual strain, they turned to Quantiphi, a Google Cloud partner, to implement Google Cloud Dialogflow. They were fully functional within a few weeks. The new website provided real-time responses as well as clear documentation to help parents get up and running quickly.

“In the first 10 days, we had over 5,000 hits, and the accuracy rate was 92%,” MacKenzie said. 

The district doesn’t know what the future holds, but now that they have been through the process, they are confident they now understand how to create their own bots to meet critical needs.

City of Toronto: Getting critical information to the community

Marco Palermo explained how the city of Toronto was facing a very rapid and fluid situation at the beginning of the pandemic. Getting information to constituents was extremely important, and they needed alternative channels to deliver that information.

Toronto has been committed to workforce equity and inclusivity in order to best represent the diversity of its residents. As a result, solutions had to be accessible to all. The bot handled 25,330 unique users and addressed 20,174 total questions with an 80% accurate response rate in the first four months. It’s been a huge success for the city, with plans to expand its capabilities.

Personalized response to the pandemic challenge

I noted that even before the pandemic, government leaders were asking for a way to provide more flexible personal experiences and better support outside of normal business hours. Around 60% of constituents want more self-service options and 75% would happily interact with digital resources if they could get the answers they needed.

Google Cloud CCAI  addresses these needs with a single core of intelligence that provides a consistently high-quality conversational experience—human or virtual—across all channels and platforms. It can be deployed on legacy infrastructure without upgrades in an average of two weeks.

CCAI operates with a conversational core that centralizes the ability to talk, understand, and interact, orchestrating high-quality conversations at scale. It provides services in three different ways:

• Virtual Agent AI allows natural conversation with customers to identify and address their issues effectively
• Agent Assist AI helps human agents by providing real-time turn-by-turn guidance so they can better serve constituents
• Insight AI determines metrics and trends in real-time to enable faster and more accurate insights

CCAI provides consistency across every application. It can go off-script when answering complex questions, adjusting human conversation with the capacity to handle unexpected stops and starts, odd word choices, or implied meanings. It can also handle multiple use cases for the customer, such as taking payments, updating information, providing information, and more. By allowing agencies to automate routine tasks and reduce the amount of time employees need to dedicate to answering calls, the solution created cost savings for the agency.

Explore more on this session by visiting the Public Sector Summit on-demand video.