San José is one of the most diverse U.S. cities, with residents speaking more than 100 languages. Several years ago, we set out to improve city community interactions through more equitable service management and delivery. This demanded a new approach to automating the intake of requests from a majority population whose first language is not English.

We first created the 311 portal and app, which was an important step as we effectively separated resident service requests from emergencies. The way we describe it to our citizens, you call 311 for a burning question, and 911 for a burning building. In the last fiscal year, San José 311 received nearly 210,000 contacts by phone and an additional 211,000 service requests through the SJ 311 app. 

Through the portal and app, we gave citizens an omnichannel experience enabling them to interact with the city to request improvements, access useful information, and get emergency help when they need it. 

In order to truly serve our diverse communities, we recognized language translation services would be required to offer truly equitable services to everyone. That’s when we started working closely with SpringML, Google Cloud, and other partners with involvement from our Mayor and City CIO.

Building public services with community engagement in mind

When we first rolled out the My San José website and mobile app, we used an out-of-the-box translation service that ended up not working. It had poor accuracy and did not meet our needs to provide all citizens with coherent services. After looking at many other options, we decided to partner with SpringML and Google Cloud to leverage the AutoML Translation with other technologies such as our virtual agent.

SpringML was selected through an open RFP process, and helped us to build and optimize our integrations, interfaces, and more between several systems, making the app and website more intuitive to manage. SpringML delivered the product we needed on time and up to specifications, and additional value came from the training sessions they provided to our team. This enabled us to understand everything we could do with AutoML and opened the door to other enhancements such as simplifying the vernacular used with our residents, making government access easier to navigate regardless of natural language spoken. 

After establishing the My San José app’s translation capabilities using AutoML, SpringML also helped us incorporate Dialogflow virtual agents. Dialogflow also positions us to make modifications with our own staffing practices – something that has become increasingly important amid the frequent changes in service levels from COVID-19 response in the past year.

Responding to community needs

With the app up-and-running, our next step was to bring in community members to help with testing, improvements, and more. We wanted the app and the website to not just be something we provided to the community, but rather something they helped us build so they would readily adopt it.

Thanks to the greater accuracy of translation supported by Google Cloud services, we were able to leverage the expertise of a small pool of community members to evaluate translations. AutoML Translation and Glossary proved to be a powerful combination that pushed us closer to our goals. 

Our primary targets were Spanish and Vietnamese translations. We are now seeing 90 percent accuracy in automated Spanish translations while Vietnamese translations continue to improve. We continue to work to simplify the language used in these services, which makes a big difference in terms of ensuring optimal language accessibility.

This work includes best serving our community members who primarily use phones to get in touch with us through 311 services. Using Google Cloud Contact Center AI, we have been able to effectively manage the calls we receive 24×7 and communicate with residents who speak Spanish as well as English. No matter which channel one of our residents choose to use to reach out, we can serve them efficiently. 

A well-timed release

We’re proud of the work we’ve done. We’ve made many government services available to our community 24 hours a day, 7 days a week — accessible through many channels. Regardless of a person’s native language, the consistency of experiences enjoyed by everyone is improving every day thanks to AI. We’re also actively incorporating more language translation capabilities to better serve more people.

While we began this process several years ago, the recent integration of machine learning language translation with our customer relationship management system in late 2020 was very well-timed because we were able to incorporate this into our COVID-19 pandemic response. 

We’re also beginning to work with other municipalities across the U.S. to share some of the lessons we’ve learned and success we’ve seen in hopes of furthering more equitable citizen services far beyond our City limits. 

We are excited to continue working with SpringML, Google Cloud, and other partners to improve our city and the equity and quality of services that our residents enjoy.

Learn more about how you can work with a Google Cloud Partner here.

Azimut Group operates an international network of companies handling investment and asset management, mutual funds, hedge funds, and insurance. Founded in Milan, Italy in 1988, Azimut Group today has branches in fifteen countries, including Brazil, China, and the USA.

“We have subsidiaries and manage funds all over the world,” explains Simone Bertolotti, IT Manager at Azimut Holding S.p.a. “That means that any technology that we put in place has to cover needs from many different countries.”

“When complicated analysis has to be executed, we have to increase our table space in a couple of minutes so that the AI can drill down into the data and deliver the information we need.”

—Simone Bertolotti, IT Manager, Azimut Holding S.p.a.

Azimut manages its funds with investment advisors who use information sourced from Bloomberg, Reuters and others. “They use a huge amount of data,” says Simone. “They work with spreadsheets, algorithms, formulae and they analyse data in minutes.” In finance, every second is crucial, which is why Azimut decided to develop a risk management dashboard that can process information even more quickly, then distribute it worldwide.

“When an advisor manages data, that data is used to make immediate decisions on funds, capital movements or whether to sell stock,” says Simone. “They have to be ready to make recommendations for any amount of data that comes to them. For our dashboard, that means that when additional information arrives or complicated analysis has to be executed, we have to increase our table space in a couple of minutes so that the AI can drill down into the data and deliver the information we need.”

Generating insights at speed

Investors and investment managers make decisions based on the most accurate, up-to-date information possible. For Azimut Group, information sourced through financial data vendors such as Bloomberg and Reuters provided only part of the data that the group required.

“We looked to collect information from a range of different providers,” explains Simone, “then analyse it to develop a predictive algorithm that could work faster than an advisor stationed at the terminal. We set ourselves the challenge to try to manipulate that data to add new insights into our matrix, so that every one of our branches across the world can see risk information about the funds in real-time.”

“We compared Google Cloud Platform’s performance with our previous cloud provider, and saw huge benefits of switching to Google. For me, the key performance issue is scaling. With Google Cloud Platform I know that I can increase and decrease my infrastructure quickly, when I need it.

—Simone Bertolotti, IT Manager, Azimut Holding S.p.a.

The first cloud provider Azimut used to build its system struggled to scale quickly to meet different kinds of data challenges. “If we wanted to add more cores, that was fine,” says Simone. “But the previous cloud provider made it complicated to raise the amount of space in a database infrastructure and scale up to demand. Scaling up for more in-depth analysis would take a day, and our need was immediate.”

That’s why Azimut switched one year ago to Google Cloud Platform to run the 150 VMs on its risk analysis platform. “We compared Google Cloud Platform’s performance with our previous cloud provider, and saw huge benefits of switching to Google. For me, the key performance issue is scaling,” says Simone. “With Google Cloud Platform I know that I can increase and decrease my infrastructure quickly, when I need it. Instead of waiting a day to scale up infrastructure, we can request and add space to our database in a couple of minutes.”

The infrastructure of Azimut’s solution handles around 800TB of data per month, and Google’s global network of servers and high-speed connections ensure that it gets to where it’s most needed by the most direct route. Impressed by the speed, security and availability of Google Cloud Platform, Azimut has moved its intranet on to Google Cloud Platform, too, eliminating the need for staff to login with VPNs.

“Instead of waiting a day to scale up infrastructure, we can request and add space to our database in a couple of minutes.”

—Simone Bertolotti, IT Manager, Azimut Holding S.p.a.

Driving ahead with Noovle

For Azimut, migrating the risk management dashboard is the latest of many Google product collaborations with cloud consultancy Noovle. “Everything started five years ago,” says Simone, “when Noovle assisted us in migrating to Gmail from our on-premise email solution. From G Suite to Google Cloud Platform, we’ve had a great relationship. Noovle provides consultancy services, support for mobility, and external advisors who work on our premises, such as when they trained us how to broadcast our meetings on Google Hangouts. As an independent company, we know we can trust them for transparent advice. All they care about is the best way to get a job done and to help us reach our goals.”

New app, new customers

In a business case comparison, Google Cloud Platform cost Azimut 35% less to run than the previous cloud provider. Now the group is building a major new mobile application on Google App Engine to be released in 2018.

“The new mobile application will allow customers to trade directly, without human advisors, by proposing different investment solutions depending on targets the customers set,” says Simone. “So if a customer aims to make money with investments, they enter their relevant personal information and we carry out the necessary regulatory checks and suggest what they could buy. The entire project will be based on Google Cloud Platform, so customers can control their investments through the app while we manage the fund, using Google Cloud Spanner on the backend.”

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.

For over two decades, Google has helped lead the invention of modern cloud systems—defining, designing and deploying warehouse-scale computing as the foundation for reliable, performant, and secure global-scale information services delivered to billions of users around the world. This leadership involves significant innovation across a broad range of systems technologies, including distributed systems, storage systems, databases, analytics, operating systems, wide area and data center networking, cluster computing, ML, video acceleration and more.

Today, we are announcing a significant step in continuing Google’s tradition of innovation and charting its path into the future: the formation of SystemsResearch@Google (SRG). SRG will be a new research team, positioned in the heart of Google’s Cloud and Infrastructure engineering organization, with the mission of shaping the future of hyperscaler systems design for Google and its ecosystem. It is focused on inventing, incubating, and infusing new concepts, designs, and technologies into Google’s applications, systems, and data centers. The team’s position will allow seamless engagement with engineering and product teams, enabling joint exploration in concert with transformative workloads. Beyond Google, the SRG team will look to forge strong relationships with external research communities working on the most pressing systems-research problems.

Critical research at a pivotal time

We are at a time of enormous transition and opportunity, as nearly all large-scale computing is moving to cloud infrastructure, classical technology trends are hitting limits, new programming paradigms and usage patterns are taking hold, and most levels of systems design are being restructured. We are seeing wholesale change with the introduction of new applications around ML training and real-time inference to massive-scale data analytics and processing workloads fed by globally connected edge and cellular devices. This is all happening while the performance and efficiency gains we’ve relied on for decades are slowing dramatically from generation to generation. And while reliability is more important than ever as we deploy societally-critical infrastructure, we are challenged by increasing hardware entropy as underlying components approach angstrom scale manufacturing processes and trillions of transistors.

In the last twenty years, much of the world’s population has gained real-time access to the world’s information and to one another in ways that were previously the stuff of science fiction. The next decade will see computing and associated capabilities undergo an even more profound transformation, bringing real-time insights, sensing, and actuation to trillions of network-connected devices spanning all of the world’s population. Doing so will require fundamental advances in security, reliability, programming models, data analysis, systems for machine learning, networking, storage systems, hardware architecture, and software systems.

SystemsResearch@Google will be co-led by David Culler and Hank Levy, who bring a combination of academic and industrial experience, plus a long history of successful and impactful research in computer systems. Culler is the former Chair of EECS at UC Berkeley, where he worked to create the Division of Data Sciences and became its founding Dean. His research has focused on parallel architectures, clusters, embedded wireless networks, planetary-scale internet services, and sustainability design. He was the founding faculty director of Intel Research Berkeley, co-founded two startups, and worked with Sun Microsystems for a decade. Levy is the former Chair of Computer Science & Engineering at University of Washington, where he worked to create the Paul G. Allen School and became its founding Director. His research has focused on operating systems, distributed systems, computer architecture, and hardware multithreading. Before UW, Levy spent a decade at Digital Equipment Corporation (DEC), where he worked on operating systems and early-generation clustered computer systems; he has also co-founded two startups. Culler and Levy are both Members of the National Academy of Engineering and Fellows of the IEEE and the ACM.

SRG will be located across sites in Google’s Bay Area and Seattle facilities. We are currently building the SRG team, bringing together leading networked systems thinkers from around the world and inside Google. If you are interested in learning more please reach out to us at systemsresearch@google.com.