Customers love Cloud Spanner because it gives them the benefits of relational semantics and SQL while also delivering the scale and availability of non-relational databases. Many of these customers want to move even more of their work to Spanner, and have requested smaller instance sizes to support development, testing and small production workloads. We’re happy to announce that they’ll soon get what they asked for: more granular instance sizing is coming to Spanner.

Granular instance sizing will be available in Public Preview soon. With this feature, you can run workloads on Spanner at as low as 1/10th the cost of regular instances, equating to approximately $65/month.

In addition, we are launching new enterprise capabilities to break down operational silos for real-time insights and provide greater database observability to developers: 

• Datastream, now in public preview, is a change data capture (CDC) service that allows enterprises to synchronize data across heterogeneous databases and applications. With Spanner support in Datastream, users will be able to stream data from MySQL or Oracle to Spanner reliably and with minimal latency. 
• BigQuery federation to Spanner (coming soon) lets users query transactional data residing in Spanner, from BigQuery, without moving or copying data. 
• Key Visualizer, available now in public preview, provides interactive monitoring so developers can quickly identify trends and usage patterns in Spanner. 

Democratizing access with more granular instance sizing 

Today, customers provision Spanner instances by specifying the number of nodes they need to run their workloads. Each node can provide up to 10,000 queries per second (QPS) of reads or 2,000 QPS of writes (writing single rows at 1 KB of data per row) and 2 TB of storage. A Spanner node is replicated across 3 zones for regional instances, and 5 or more zones for multi-regional instances. The choice of node count determines the amount of serving and storage resources that are available to the databases in a given instance. 

Historically, the most granular unit for provisioning resources on Spanner has been one node. To enable more granular control, we are introducing Processing Units (PUs); one Spanner node is equal to 1,000 PUs. Customers can now provision in batches of 100 PUs, and get a proportionate amount of compute and storage resources. This will allow teams to run smaller workloads on Spanner at much lower cost. With this feature, customers can start at 100 PUs and scale up as needed in batches of 100 PUs, to up to 1,000 PUs (1 node), all with zero downtime. Subsequently, customers can continue to scale up by adding more nodes, just like what they do today. Customers do have the choice of using either PUs or nodes to provision resources within workloads, when those workloads occupy multiple nodes of capacity. 

To illustrate with an example, let’s say a game developer creates a Spanner instance called “baseball-game” in us-central1, with 100 PU compute capacity at $65/month price.

As you can see below, proportional maximum storage of 205 GB is assigned to the 100 PU instance.

Once the game starts becoming popular and resource demands increase, the user edits the instance to increase the compute capacity to 500 PUs with proportional maximum 1 TB of storage.

The game grows in popularity, and the gaming company prepares to launch a much-awaited capability in the game. Anticipating a sharp increase in usage at launch day, the game developers increase the compute capacity to 3,000 PUs. Compute capacity assigned to the instance over a period of time can be viewed in Cloud Monitoring graphs:

Request Access 
You can request early access to granular instance sizing feature by filling this form.

Breaking down operational silos with Datastream CDC and BigQuery federation

BigQuery Federation. BigQuery has made analytics easy by bringing together data from multiple sources for seamless analysis. Soon you’ll also be able to analyze data in Spanner directly in BigQuery. With  Spanner’s BigQuery federation, you’ll be able to instantly query data residing in Spanner in real-time without moving or copying the data. Simply set up the Spanner as an external data source in BigQuery, as shown below.

Change Data Capture ingest. Now in public preview, Datastream lets you stream change data into Google Cloud from MySQL and Oracle databases. As of today, you can ingest this change data directly into Spanner using a built-in Dataflow template. This lets you migrate data from MySQL and Oracle databases into Spanner in near-real time.

Understanding performance and resource usage with Key Visualizer

Key Visualizer is a new interactive monitoring tool that lets developers and administrators analyze usage patterns in Spanner. It reveals trends and outliers in key performance and resource metrics for databases of any size, helping to optimize queries and reduce infrastructure costs. Designed for performance tuning and instance sizing, Key Visualizer is available today in public preview in the web-based Cloud Console for all Spanner databases at no additional cost. Learn more in the Key Visualizer blog. 

Learn more

• To request early access to granular instance sizing in Spanner, fill out this form.
• To get started with Spanner today, create an instance or try it out with a Spanner Qwiklab.

For years, retailers have been told that they must embrace a litany of new technologies, trends, and imperatives like online shopping, mobile apps, omnichannel, and digital transformation. In search of growth and stability, retailers adopted many of these, only to realize that for every box they ticked, there was another one waiting.

And then the pandemic hit, along with rising social movements and increasingly harsh weather. Some retailers were more prepared to take on these disruptions than others, which crystallized a new universal truth across the industry: the ability to adapt on the fly became the most important trait to survive and thrive.

Today’s retail landscape has surfaced both existing and new challenges for specialty and department store retailers. Approximately 88% of purchases previously occurred within a store environment. Now, it’s closer to 59%, with the remainder done online or through other omni methods. 

With such constant change and upheaval, it can feel like the mantra now is ABP: always be pivoting. 

The big question isn’t just how to maintain constant momentum and agility—it’s how to do it without sapping your workforce, your inventory, or your profits in the process. The pivot is now a given. What matters is how you do it.

Adapting requires a flexible base of technology that allows retailers to shift and scale seamlessly with the needs of the moment. 

They need to be able to leverage real-time insights and enhance customer experiences rapidly, online and in the real world (not to mention the growing hybridization that’s AR and VR). They need to modernize their stores to power engaging consumer and associate experiences. They need to enhance operations for rapid scaling between full operations and digital-only offerings.

To help retailers achieve these goals and more, Google Cloud is honing a trio of essential innovations: demand forecasting that harnesses the power of data analytics and artificial intelligence; enhanced product discovery to improve conversion across channels; and the tools to help create the modern store experience.

In other words, here’s some of the biggest ways we’re ready to help you pivot.

Pivot point 1: Harnessing data and AI for demand forecasting with Vertex AI

One of the greatest challenges for retailers when building organizational flexibility is managing inventory and the supply chain. 

We are in the midst of one of the worst global supply chain crises, stemming from soaring demand and logistics issues brought on by the pandemic. This crisis has only heightened the challenge retailers face when assessing demand and product availability. Even in normal times, mismanagement of inventory can add up to a trillion-dollar problem, according to IHL Group (costing $634 billion in lost sales worldwide each year, while overstocks result in $472 billion in lost revenues due to markdowns). 

On the flipside, optimizing your supply chain can lead to greater profits. For instance, McKinsey predicts that a 10% to 20% improvement in retail supply chain forecasting accuracy is likely to produce a 5% reduction in inventory costs and a 2% to 3% increase in revenues.

Some of the challenges related to demand forecasting include:

• Low accuracy leads to excess inventory, missed sales, and pressure on fragile supply chains.
• Real drivers of product demand are not included, because large datasets are hard to model using traditional methods.
• Poor accuracy for new product launches and products that have sparse or intermittent demand.
• Complex models are hard to understand, leading to poor product allocation and low return on investment on promotions.
• Different departments use different methods, leading to miscommunication and costly reconciliation errors.

AI-based demand forecasting techniques can help. Vertex AI Forecast supports retailers in maintaining greater inventory flexibility by infusing machine learning into their existing systems. Machine learning and AI-based forecasting models like Vertex AI are able to digest large sets of disparate data, drive analytics and automatically adjust when provided with new information. 

With these machine learning models, retailers can not only incorporate historical sales data, but also use close to real-time data such as marketing campaigns, web actions like a customer clicking the “add to cart” button on a website, local weather forecasts, and much more. 

Pivot point 2: Enhanced product discovery through AI-powered search and recommendations

If customers can’t easily find what they are looking for, whether online or at the store, they will turn to someone else. That’s a simple statement, but one with profound impacts.

In research conducted by The Harris Poll and Google Cloud, we found that over a six month period, 95% of consumers received search results that were not relevant to what they were searching for on a retail website. And roughly 85% of consumers view a brand differently after an unsuccessful search, while 74% say they avoid websites where they’ve experienced search difficulties in the past.

Each year, retailers lose more than $300 billion dollars from search abandonment, or when a consumer searches for a product on a retailer’s website but does not find what they are looking for. Our product discovery solutions help you surface the right products, to the right customers, at the right time. These solutions include: 

• Vision Product Search, which is like bringing the augmented reality of Google Lens to a retailer’s own branded mobile app experience. Both shoppers and retail store associates can search for products using an image they’ve photographed or found online and receive a ranked list of similar items.
• Recommendations AI, which enables retailers to deliver highly personalized recommendations at scale across channels.
• Retail Search, which provides Google-quality search results on a retailer’s own website and mobile applications.

All three are powered by Google Cloud, leveraging Google’s advanced understanding of user context and intent, utilizing technology to deliver a seamless experience to every shopper. With these combined capabilities, retailers are able to reduce search abandonment and improve conversions across their digital properties. 

Pivot point 3: Building the modern store

Stores are no longer places for just browsing and buying. They must be flexible operation centers, ready to pivot to address changing circumstances. The modern store must be multiple things at once: a mini-fulfillment and return center, a recommendation engine, a shopping destination, a fun place to work, and more. 

Just as retail companies had to embrace omnichannel, stores are now becoming omnichannel centers on their own, mixing the digital and physical into a single location. Retailers can use physical stores as a vehicle to deliver superior customer experiences. This will demand heightened levels of collaboration and cooperation between stores, digital, and tech infrastructure teams, building on the agile ways they have worked together.

In many ways, it’s about allowing our physical spaces to function more like digital ones. Google Cloud can help by bringing the scalability, security, and reliability of the cloud to the store, allowing physical locations to upgrade infrastructure and modernize their internal and customer-facing applications. 

Think of it as when a new OS gets released for your phone. It’s the same small, hard box, but the experience can feel radically different. Now, extend that same idea to a digitally enabled store. With the right displays, interfaces, and tools at a given retail location, the team only needs to send an over-the-air update to create radically fresh experiences, ranging from sales displays to fulfillment or employee engagement.

Such an approach can enable streamlined experiences for both customers and store associates. For instance, when it comes to the modern store’s evolving role as a fulfillment or return center, cloud solutions can help drive efficiency in stores through automation of ordering, replenishment, and fulfillment of omnichannel order selection.  

Similar tools for personalized product discovery online can be applied to customers in the store, helping them to browse and explore, or even create a tailored shopping experience. 

The impact of store associates can be maximized by equipping them with technology to provide expertise that drives value-added customer service, as well as increasing productivity in stores by streamlining operations, thus lowering overhead cost. At the register, customers should be able to enjoy frictionless checkout while ensuring reliable, accurate, secure transactions.

Google Cloud can help retailers transform

The ability to adapt and pivot to meet today’s changing consumer needs requires that retailers rely on modern tools to obtain operational flexibility. We believe that every company can be a tech company. That every decision is data driven. That every store is physical and digital all at once. That every worker is a tech worker. 

Google Cloud works with retailers to help them solve their most challenging problems. We have the unique ability to handle massive amounts of unstructured data, in addition to advanced capabilities in AI and ML. Our products and solutions help retailers focus on what’s most important—from improving operations to capturing digital and omnichannel revenue.

In the competitive world of consumer goods, sustainability matters more than ever. In recent Google survey, 82% of consumers said sustainability is more top of mind now than it was before COVID-19¹ and 78% said that big businesses have a role to play in helping to fight climate change.² As well as delivering on customers’ heightened expectations, sustainable business practices can help organizations reduce waste and lower operational costs and even help attract top talent.

Not only is sustainability good for business performance and the bottom line, it’s great for the planet, too. And it’s something we care deeply about at Google. Since our earliest days, we have focused on developing services that significantly improve the lives of billions of people while operating our business in an environmentally sustainable way. In 2007, we were the first major company to become carbon neutral, and in 2017 we were the first to achieve 100% renewable energy. Today, we proudly operate the cleanest cloud in the industry. We’ve also been powering humanitarian, scientific, and environmental initiatives studying geospatial information and making it available for analysis via Google Earth Engine. 

Powering the future of responsible sourcing 

How does all this relate to CPG supply chains? The world relies on raw materials like palm oil, soy, and cocoa to produce the items we consume every day—such as coffee, chocolate, frozen foods, shampoo, toothpaste, cosmetics, and even household cleaning products. Yet as demand for these materials continues to grow, forests are under threat. Change is needed.

As sustainability moves further into the spotlight, in-demand crops face increased supplier scrutiny. Environmentally conscious companies want to know what percentage of their raw materials is sourced from deforestation-free suppliers—and how they can improve that number. Until recently, many CPG brands have found it hard to get real-time, reliable visibility into operations at a local supplier level, globally. 

Google Cloud, in partnership with NGIS, is helping brands gain a deeper understanding of raw material sourcing practices across supplier networks, so they can improve supplier performance and compliance in the fight against deforestation. The TraceMark solution, developed by NGIS, uses Google Earth Engine and BigQuery to analyze and visualize how suppliers behave over time. 

Google Earth Engine is the world’s largest archive of open Earth data. It combines a multi-petabyte catalog of satellite imagery and geospatial datasets with planetary-scale analysis capabilities to help people detect changes, map trends, and quantify differences on the Earth’s surface.With Earth Engine, organizations can analyze the potential social, and environmental impacts of the decision they make. 

Together, TraceMark, Google Earth Engine and BigQuery help companies visualize, monitor, and measure the impact of suppliers’ farming practices. The process starts with Earth Engine, which aggregates and harmonizes planetary data into images that it has been collecting for decades. Then, boundary maps for different suppliers are created and NGIS applies climate data science and machine learning to turn pixels into insights. Finally, BigQuery and Vertex AI -Google Cloud’s unified artificial intelligence platform are used to produce supplier scoring, which is integrated into downstream ERP and procurement systems to support more informed decision-making.

These efforts support the responsible sourcing of raw materials by making supply chains more agile, transparent and traceable.

“NGIS is thrilled to work with Google Cloud and its partners to enable business accountability for sustainable practices at all levels of their supply chain,” said Nathan Eaton, Executive Director, NGIS. “Google Earth Engine provides unique geospatial capability that [gives] leaders visibility and control over their environmental footprint and that of their suppliers in a way that was not previously possible.” 

Unilever commits to working towards a deforestation-free supply chain 

Unilever is a great example of a company committed to using technology in the quest to become more sustainable. Since 2020, Unilever has partnered with Google Cloud to use data for eco-friendly decision-making, particularly when it comes to sustainable commodity sourcing. 

By combining the power of cloud computing with satellite imagery and AI, we’re helping Unilever build a more holistic view of the forests, water cycles, and biodiversity that intersect its supply chain. By gaining a complete picture of these ecosystems, Unilever can detect deforestation while simultaneously prioritizing critical areas of forest and habitats in need of protection.

The cleanest cloud for your sustainable transformation

We are excited to bring Google Earth Engine and NGIS to our CPG customers to help them meet their sustainability goals. Looking ahead, we’re committed to furthering our own ambitious sustainability goals and empowering CPG brands with the technology to do more for our environment and our shared future. 

Contact your cloud seller to learn more about Tracemark sustainable sourcing solutions and how Google Cloud can help you advance your sustainability initiatives.

Explore TraceMark on the Google Cloud Marketplace

To know more about how we are helping CPGs transform digitally read this ebook

<sup>1. Google/C Space, BR, FR, DE, IN, MX, U.K., U.S., qualitative survey activity, n = 528, Nov. 24–Nov. 26, 2020.
2. Google/Ipsos, Google Sustainability, BR, FR, DE, IN, JP, U.K., U.S., n=16,959 online population 18–70, July 2021.</sup>

Editor’s note: In Today’s guest post we hear from beauty leader L’Oréal about their approach to building a modern data platform on fully managed services: managing the ingest of diverse datasets into BigQuery with Cloud Run, and orchestrating transformations into relevant business domain representations for stakeholders across the organization. Learn more about how businesses have benefited from Cloud Run in Forrester’s report on Total Economic Impact.

L’Oréal was born out of science. For over 100 years, we have always shaped the future of beauty, and taken its eternal quest to new horizons. This has earned us our current position as the world’s uncontested beauty leader (~€ 32 B annual sales in 2021), present in 150 countries with over 85,000 employees.

Today, with the power of our game-changing science, multiplied by cutting-edge technologies, we continue our lifelong journey of shaping the future of beauty.

As a Beauty Tech company, we leverage our decades-long heritage of rich data assets to empower our decision-making with instant, sophisticated analysis.

Because we oversee global brands, which must adapt to local requirements, we need to maintain a deep understanding of what a brands’ data represents, while managing disparate legal and regulatory requirements for different countries. Our end goal is to run a safe, compliant and sustainable data warehouse as efficiently and effectively as possible.

We sync and aggregate internal and external data from a wide variety of sources across organizations and retail stores. This made the management of our data warehouse infrastructure used to be very complex and hard to manage before Google Cloud. L’Oréal’s footprint was so large that we once found it impossible to have a standardized method to handle data. Every process was vendor-specific, and the infrastructure was brittle. We went looking for a solution to our complex data infrastructure needs, and defined the following non-negotiable principles:

• No Ops: The job of a developer at L’Oréal is not to manage servers. We need an elastic infrastructure that scales on demand, so that our developers can focus on delivering customized and inclusive beauty experiences to all consumers, rather than focusing on managing servers.
• Secure: We have strict security and compliance requirements which vary by country, and we employ a zero-trust security strategy. We must keep both our own internal data and customer data safe and encrypted.
• Sustainable : Our data lives in multiple environments, including on-prem data centers and public cloud services. We must be able to securely access and analyze this data while minimizing the complexity and environmental impact of moving and duplicating data.
• End-to-end supervision: Because developers shouldn’t be managing servers, we need a “single pane of glass” dashboard to monitor and triage the system if something goes wrong.
• Easy-to-deploy: Deploying code safely should not compromise velocity. We are constantly developing innovations that push the boundaries of science and reinvent beauty rituals. We need integrated tools to make our code deployment process seamless and safe.
• Event-driven architecture: Our data is used globally by research, product, business and engineering teams with high expectations on data quality and timeliness. Many of our internal processes and analysis are based on near real-time data.
• Data products delivered “as a service”: We want to empower our employees to drive business value at record speed. To that end, we need solutions that enable us to remove the developers from the critical path of solution delivery as much as possible.
• Extract-load-transform (ELT): Our goal is to implement the pattern to load data as soon as possible into the data warehouse to take advantage of SQL transformations.

After considering multiple vendors on the market, with these principles in mind, we landed on end-to-end Google Cloud serverless and data tooling. We were already using Google Cloud for a few processes, including BigQuery, and loved the experience.

We’ve now expanded our use of Google Cloud to fully support the L’Oréal Beauty Tech Data Platform.

L’Oréal’s Beauty Tech Data Platform incorporates data from two types of sources: directly via API, which is data that adapts easily to our schema and is inserted directly into BigQuery, and bulk data from integrations, which require event-driven transformations using Eventarc mechanisms. These transformations are performed in Cloud Run and Cloud Functions (2nd gen), or directly in SQL. With Google Cloud, we can adapt very quickly.

Today, we currently have 8500 flows for ~5000 users using the native zero-trust capabilities offered by Google Cloud. Indeed, the flows come from Google Cloud and other third-party services.

BigQuery enabled us to adopt standard SQL as our universal language in our data warehouse and meet all expectations for queries and reporting. We were also able to load original data using features like federated queries, and efficiently transitioned from ETL to ELT data ingestion by handling semi-structured data with SQL. This approach of loading original data from sources into BigQuery with non-destructive transformations allows us to reprocess data for new use-cases easily, directly within BigQuery.

Our applications are hosted on multiple environments – on-premises, in Google Cloud, and in other public clouds. This made it difficult for our data engineers and analysts to natively analyze data across clouds until we started using BigQuery Omni. This capability of BigQuery allowed us to globally access and analyze data across clouds through a single pane of glass using the native BigQuery user interface itself. Without BigQuery Omni, it would’ve been impossible for our teams to natively do cross-cloud analytics. Moreover, it eliminated the need for us to move sensitive data, which is not only expensive because of local tax and subsea transport, but also incredibly risky – sometimes even forbidden – because of local regulations.

Today Google Cloud powers our Beauty Tech Data Platform, which stores 100TB of production data in BigQuery and processes 20TB of data each month. We have more than 8000 governed datasets, and 2 millions of BigQuery tables coming from multiple data sources such as Salesforce, SAP, Microsoft, and Google Ads.

For more complex transformations where custom and specific libraries are required, Cloud Workflows help us to manage the complexity very efficiently by orchestrating steps in containers through Cloud Run, Cloud Functions and even BigQuery jobs — the most used way to transform and add value to the L’Oréal data.

Additionally, by using BigQuery and Google Cloud’s serverless compute for API ingestion, bulk data loading, and post-loading transformations, we can keep the entire system in a single boundary of trust at a fraction of the cost. With ingest, queries, and transformations all being fully elastic and on-demand, we no longer have to perform capacity planning for either the compute or analytics components of the system. And of course these services’ pay-as-you-go model perfectly aligns with L’Oréal’s strategy of only paying for something when you use it.

Google Cloud fulfilled the requirements of our Beauty Tech Data Platform. And as if offering us a no-ops, secure, easy-to-deploy, custom-development free, event-based platform with end-to-end supervision wasn’t enough, Google Cloud also helped us with our sustainability efforts.

Being able to measure and understand the environmental footprint of our public cloud usage is also a key part of our sustainable tech roadmap. With Google Cloud Carbon Footprint, we can easily see the impact of our sustainable infrastructure approach and architecture principles. Our Beauty Tech platform is a strategic ambition for L’Oréal: inventing the beauty products of the future while becoming the company of the future.

Sustainable tech is an imperative and a very important step towards this ambition of creating responsible beauty for our consumers, and sustainable-by-design tech services for our employees. We all have a role to play, and by joining forces, we can have a positive impact.

Google Cloud’s data ecosystem and serverless tools are highly complementary, and made it possible to build a next-generation data analytics platform that met all our needs.

Get started using serverless and BigQuery together on Google Cloud today.

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.