Demand for spoken audio content such as podcasts remains robust despite the proliferation of video services and other entertainment options for consumers. Shibin Li, Co-founder of Castbox, credits growth of the global podcast platform to the following: speed and availability, market-leading features, the proliferation of smart devices to deliver audio content, and activities — such as driving and working around the house — that make consuming video difficult.

Founded in 2016 and headquartered in Beijing, China, Castbox enables users to locate, access, and create spoken audio content. Available on iOS and Android, Castbox supports 50 million podcasts, on-demand radio programs, and audiobooks in 70 languages from 175 countries. The platform hosts about 2 million users per day and is the largest podcast platform on Android.

Castbox includes a range of features that build on its core service to provide a high-quality user experience. These features include curated podcast recommendations and in-audio search.

A combination of services

At its inception, Castbox relied on a combination of a multinational cloud services, as well as Google Cloud Platform (GCP) services, including the Google BigQuery analytics data warehouse and Cloud APIs to provide programmatic interfaces with Google Cloud services, and a range of services from the Google mobile development platform Firebase.

However, as Castbox matured and its user base expanded, the business increased its reliance on Google Cloud Platform and Firebase.

“We needed to access stable cloud services as we could not tolerate long periods of downtime that would compromise the user experience,” says Li. “Furthermore, we had to support up to 50,000 concurrent connections, and potentially more in future, without disruption.”

“Based on our analysis of the data in Google BigQuery, we can determine what type of content users are listening to, how long they like to listen to it, and when they like to listen to it. This allows us to recommend similar podcasts to each user based on the preferences he or she expressed, encouraging activity on and return visits to our platform.”

—Shibin Li, Co-Founder, Castbox

Competitive differentiation

Castbox also found machine learning-powered Google Cloud Platform APIs could help deliver features, such as in-audio search, that differentiate the podcast platform from its competitors. In addition, Firebase SDKs and Firebase A/B Testing would enable Castbox to create and analyze new applications, as well as make adjustments based on user feedback. Firebase Realtime Database would allow the business to support tens of thousands of concurrent user connections.

The diligence of the Google Cloud team in advising Li and her team about forthcoming products and services also swayed Castbox towards Google technologies. The business gained the opportunity with Google to join several programs that offered early access to Google innovations.

Signature in-audio search service

Castbox now uses Google Cloud Platform services in the Tokyo, Japan, and U.S. East regions. Cloud Speech-to-Text API plays a key role in delivering Castbox’s signature in-audio search service. This service enables users to search transcriptions of audio content on the platform for words or phrases. The search results incorporate the title of the podcast and the search term in context (for example, within the sentence or sentence excerpt in which it appears). Each use of the word or phrase is time-stamped so it can easily be found. The API enables Castbox developers to apply neural network algorithms to achieve audio-to-text conversion accuracy rates of greater than 96%, while search queries typically experience latency of just 50 milliseconds.

In addition, the latency of comparison data, converting audio to text, is only about 250 milliseconds, contributing to the processing of about 12 minutes worth of audio to text in just 10 minutes. “We can process about 20 hours of audio files in one day,” Li says. “This enables us to transcribe and index all the new episodes of a podcast in that period.”

50,000 concurrent connections

With Firebase Realtime Database, Castbox now supports up to 50,000 concurrent connections to its platform with an average latency per connection of just 10 milliseconds. “Firebase Realtime Database also allows us to continue operating in offline mode, which is extremely helpful if we experience any network disruptions,” Li explains. “When we come back online again, any data is simply synchronized with the database.”

Google BigQuery and the analytics capabilities of Firebase SDKs also enable Castbox to monitor and analyze user behaviors. “Based on our analysis of the data in Google BigQuery, we can determine what type of content users are listening to, how long they like to listen to it, and when they like to listen to it,” Li explains. “This allows us to recommend similar podcasts to each user based on the preferences he or she expressed, encouraging activity on and return visits to our platform.”

“Given our queries may span up to 40 days of data, we may be analyzing up to 1,200 GB at one time. We have no problem doing this with Google BigQuery.”

—Shibin Li, Co-Founder, Castbox

Castbox also uses its analyses of Google BigQuery data to amend banners and summaries on its platform to encourage users to listen to additional content. Furthermore, the service is prepared to make surprise recommendations of content to users based on the preferences and reactions of users with similar tastes.

“We analyze a pool of data growing at up to 30 GB per day,” Li explains. “Given our queries may span up to 40 days of data, we may be analyzing up to 1,200 GB at one time. We have no problem doing this with Google BigQuery.” These analyses also support Castbox’s decision to start creating original content, such as finance and economic news, for its platform.

Checking weekly changes

Castbox does not rely only on analyzing user data to deliver a high-quality experience. The business aggregates user feedback from emails and Google Play reviews to make weekly changes to its platform. It then uses Firebase A/B Testing to check whether these changes are met with a positive user response.

“We are extremely pleased with Google Cloud Platform and Firebase. We have been able to differentiate ourselves from our competitors and provide an attractive option for users at a time when content and entertainment options are exploding. We have a great opportunity with Google to continue to improve the value of our offering to users and build engagement and loyalty.”

—Shibin Li, Co-Founder, Castbox

Castbox’s positive experiences with Google Cloud Platform are encouraging the business to grow its use of the product. “We are trying to move some more services to Google Cloud Platform because it is very stable and scalable,” Li says. The business is keen to explore the capabilities of Cloud Pub/Sub to provide low latency messaging between applications, Cloud Spanner to deliver a distributed relational database service, and Cloud Dataflow to transform and enrich data in stream and batch modes.

“We are extremely pleased with Google Cloud Platform and Firebase,” Li concludes. “We have been able to differentiate ourselves from our competitors and provide an attractive option for users at a time when content and entertainment options are exploding. We have a great opportunity with Google to continue to improve the value of our offering to users and build engagement and loyalty.”

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.

Global life science supply chains are lengthy and complex with many moving parts. One small disruption can create serious delays and affect your ability to deliver therapeutics for patients.

Supply chain disruptors

Over the last few years, healthcare organizations have encountered a range of obstacles, from both internal and external factors, that have resulted in supply networks failing to get drugs and medical devices to where they need to be on time. These obstacles include:

• Labor and supply shortages
• Rising material costs
• Raw material constraints
• Geo-political events
• Unpredictable weather

How do you overcome supply chain disruptors that are out of your control?

The intelligent healthcare supply chain

While many organizations have already implemented data-driven supply chains, organizations are still faced with the challenges of static, siloed, and different functional supply chain applications; limited data exchange with key trading partners across upstream and downstream operations; and the inability to effectively leverage relevant external data.

At Google Cloud, we believe the key to meaningful and effective change is a data-driven supply chain that allows you to achieve visibility, flexibility, and innovation.

Our solutions help you prepare for the unpredictable and enhance the value of your data. By unlocking AI-driven insights, you can strengthen distribution networks and optimize your workflows and supply chains to become more reliable, intelligent, and sustainable. Some of the business challenges we address include:

• Predicting demand with Vertex AI Forecast
• Visual inspection for quality and predictive maintenance with pre-built ML models
• Automating and optimizing pickup and delivery operations with Cloud Fleet Routing API
• Real time and holistic inventory visibility with Supply Chain Twin

Make sure you’re prepared for the unpredictable with real-time visibility over your distribution networks. Learn how you can harness the power of AI and analytics and gain actionable insights that enhance your supply chain.

Local governments are embracing more modern and scalable ways to support their communities. In an effort to save both time and money, Costa Mesa Sanitary District  (“CMSD”) used machine learning to automate and streamline manhole maintenance. Manhole maintenance is an essential part of the upkeep of cities. Manholes provide critical access points for underground public utilities, allowing inspection, maintenance, and system upgrades. But failure to upkeep manholes can cause a multitude of problems, from road hazards to sewer blockages, and can make it difficult for workers to access underground public utilities, which can lead to other safety issues. Manhole maintenance is an essential part of Costa Mesa Sanitary District maintenance, but this process requires the work of an outside consultant and costs the CMSD over $100,000.

ML to the rescue 

CMSD, in collaboration with SpringML and Google Cloud, developed a project to streamline manhole maintenance by leveraging the power of machine learning (ML) to detect sewer manholes and rate their conditions. This solution saves CMSD $40,000 every year, freeing up funds for other public service projects.

Every quarter, one member of the CMSD drives a car outfitted with a GoPro camera. This car travels through the entire District area, which includes the city of Costa Mesa and small portions of Newport Beach, CA, which is about 218 street miles, and records the roads to detect approximately 5,000 manholes. At the end of the recording day, CMSD members transfer images and videos from the GoPro SD card into a local server. Then these files are automatically ingested into Google Cloud Storage for processing. From here, the machine learning algorithms detect which manholes need repair.

Google Cloud products are used throughout this project. Once the images and videos are in Google Cloud Storage, a workflow with Cloud Scheduler spins up the VM every night to detect if there are new videos on Cloud Storage. If there are, this triggers cloud machine learning, which reviews the numerous images and videos, rates each manhole, and determines if any require maintenance. 

Machine learning to detect and grade manholes

SpringML applied a very systematic approach to detecting and grading the manholes. First, image processing ensures that the region of interest is only the section of the road in front of the vehicle to avoid any privacy concerns. Then SpringML applied a 5-step process using machine learning to detect and grade the manholes.

1. SpringML developed two separate custom TensorFlow-based Mask R-CNN models. Mask-R-CNN is a deep neural network that is used for image segmentation tasks, which means it can separate different objects in an image or a video. 
   
   The first Mask-R-CNN model was created to accurately detect if an image showed a manhole cover. This was a critical step because sewer manhole covers look similar to water main covers. To make the model accurate, it was important that there be no false positives. SpringML used around 50 images of sewer manholes and other images to train and validate that the algorithm could successfully detect sewer manhole covers. 
2. Once a manhole is accurately detected, the surrounding area is masked using image processing to focus on the manhole and then the cropped images are sent to the second model which is used to detect damage in the area around the manhole, called the apron. To reduce the processing time, the second model only does the inference if a manhole cover is detected.
3. The second Mask-R-CNN model uses CMSD Guidelines to decide what types of damage contributed to the rating system. For training purposes, SpringML uses a TensorFlow-Keras inside a Virtual Machine on Google Cloud. The initial model was trained and the Keras weights were saved on Google Cloud Storage. This helped create a versioned system of the models as the model gets refined over time.
4. Then, duplicated detections are cleaned up to have a unique detection for each manhole cover.
5. Finally, Manholes are graded from a 1-5 rating, with 5 representing high damage and 1 representing low damage. 

Once cloud machine learning has analyzed the new videos and images, the final scores are stored in BigQuery. Results are then served to members of CMSD via a simple web application where they can see which manholes need to be maintained. Two staff members review the ML results and determine priorities for repairs. One of the most interesting features of this project is that the model gets continually retrained based on feedback submitted in the web application. For example, if the model inaccurately detects a manhole, a member of CMSD can mark that in the web application and their feedback is immediately used to refine the model.

This solution shows how leveraging machine learning can streamline a necessary local government project and save money and labor while being highly scalable! Not only does this system streamline the manhole maintenance process, but it also allows for more frequent review of manhole conditions and provides a historical view of how the District’s manholes change over time.

Want to learn more about Google Cloud machine learning? Check out this tutorial to  learn TensorFlow and Keras and check out more machine learning tools in our AI Platform.

“Talk about tough times, right?”

That’s how Dan Stuart, Senior Vice President of IT Services at Southwire Company, refers to the months following a December 2019 ransomware event, and the COVID crisis that began in spring of 2020. Those events hit just as the company was preparing for an overhaul of their SAP environment. This comprehensive plan included three key elements. First, the company wanted to upgrade their SAP ECC environment to take advantage of the latest functionality available for this critical ERP system. Second, Southwire aimed to deploy SAP Business Warehouse on SAP HANA to accelerate vital reporting for all business users. Third, the company wanted to upgrade to the latest version of SAP Process Orchestration—an essential component that touches key manufacturing interfaces in all Southwire facilities. 

Southwire had looked at multiple options for the upgrades, including remaining entirely on-premises, colocation, and full cloud migration. “Going to the cloud seemed a lot more compelling,” says Joe Schleupner, Southwire’s Senior Director of PMO & ITS planning and implementation. “We were going to the cloud eventually, so why take these intermediary steps? Let’s just get it done.”

After looking at several options, Southwire decided to migrate to Google Cloud. “We wanted to be on a platform for SAP that was flexible, scalable, and secure; that we could count on to get up and running quickly,” says Stuart. “We chose Google Cloud not only for those reasons, but also because we recognize that Google has other assets that we may be able to take advantage of down the line, such as technologies like artificial intelligence (AI).” 

More stability, less worry

As one of the leading manufacturers of wire and cable used in the transmission and distribution of electricity, Southwire aids the delivery of power to millions of people worldwide. They have more than 30 manufacturing facilities across the United States running 24/7. Any downtime directly affects productivity and revenue. With help from Google Cloud and their implementation partner NIMBL, Southwire completed the SAP migration to Google Cloud over a planned maintenance weekend on July 4th.

The migration itself, while complex, went quickly and smoothly. “Just moving to the cloud was quite a feat because we were dealing with so much data, but in total the SAP system was down for only ~16 hours,” says Schleupner.

“As a project manager, I always felt that Google Cloud had my back” Schleupner says. The Process Orchestration (PO) migration was of particular concern, considering that it controlled all of Southwire’s manufacturing interfaces across the entire company. “Every critical piece of information that goes from SAP down to the manufacturing system goes through that system,” says Schleupner.

Even after migrating, Southwire discovered that making changes to the system was fast, easy, and resulted in no downtime. Normally, certain types of changes would have involved taking down SAP for at least an hour.

The Southwire team also appreciates the fact that the modern cloud architecture means spending less time on routine infrastructure maintenance. “It’s one less thing for me to worry about,” Stuart says, “I can focus on the business side of the house and move the technology and responsibilities to what we do within the Google Cloud Platform.”

What comes next?

While the cloud migration will increase stability, uptime, performance, and security, there is much more to come. Southwire is currently working on a disaster recovery implementation for their SAP environment on Google Cloud. Stuart and Schleupner are excited about where Google Cloud can further take Southwire. They are considering an SAP Hybris e-commerce implementation as well as connected factory and/or factory automation initiatives that can take advantage of artificial intelligence and machine learning. 

To Stuart and Schleupner, the migration of Southwire’s SAP environment to Google Cloud, as important as it was, really represents the first step in the company’s tech evolution. Now that much of the heavy lifting is complete, Southwire’s digital transformation can begin in earnest. “There’s no shortage of areas where I think Google Cloud will come into play,” Stuart says, “and we intend to look at these things with an open mind to understand how we can leverage current investments to take our organization where we want to go.”

Learn more about Southwire’s SAP on Google Cloud deployment and how Google Cloud can transform the way you work with your SAP enterprise applications. Visit cloud.google.com/solutions/sap.