Simplifying Unstructured Data Analytics with BigQuery ML and Vertex AI: A Comprehensive Guide
1529
Of your peers have already read this article.
6:30 Minutes
The most insightful time you'll spend 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
- We will define our pre-trained models for Vision AI, Translation AI and NLP AI in BigQuery ML.
- We’ll then use Vision AI to detect the text from some classic movie posters images.
- Next, we’ll use Translation AI to detect any foreign posters and translate them to a language of our choosing – English in this case.
- 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.
| API | Model Name |
| Vision AI API | Cloud_ai_vision_v1 |
| Translation AI API | Cloud_ai_translate_v3 |
| NLP AI API | Cloud_ai_natural_language_v1 |
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.
How One Company Uses AI and Data Analysis to Boost Revenue
9435
Of your peers have already read this article.
4:30 Minutes
The most insightful time you'll spend today!
AI, deep learning, and image recognition is transforming the shopping experience. These technologies enable consumers to use product images or screenshots rather than text to search for similar products. This improves the customer experience and enables retailers with online and offline outlets to provide a genuine omnichannel experience.
The lack of complexity and the ease of use of BigQuery has enabled ViSenze to reduce its data infrastructure and management costs by 30%–50%, and scale up without incurring downtime.
—Renjie Yao, Data Platform Lead, ViSenze
Visual commerce provider ViSenze is helping some of the world’s leading retailers improve conversion rates through image-based search.
The business’s products also enable media companies to use the platform to turn images and videos into engagement opportunities—driving new and incremental revenues.
Created through NExT—a research center established by the National University of Singapore and Tsinghua University of China—ViSenze now operates in the United States, United Kingdom, India, China, and Singapore. The business is backed by Japan-based internet and ecommerce company Rakuten and cross-border investment specialist WI Harper Group.
Growth in SMB and Mobiles
Renjie Yao, Data Platform Lead at ViSenze, sees opportunities for growth in the small-to-medium business sector, where companies do not have the resources to build similar technologies, and with mobile device OEMs to integrate ViSenze natively on smartphones.
Phone owners can activate a “shopping lens” on camera and gallery apps to capture an image of a product. They then receive matching results from more than 800 partner merchants and retailers and can then click through to product pages on partner apps or mobile websites. Alternatively, they may use a photo to compare products sold on different sites or shop matching styles.
“Our research found Google Cloud provided a complete, integrated ecosystem rather than a disparate collection of tools and components, and so was ideal for our needs.”
—Renjie Yao, Data Platform Lead, ViSenze
The ViSenze API analyzes the contents of a selected or clicked image and sends the information back to the organization’s visual commerce platform. The platform feeds back similar results based on that information.
The ViSenze offering also extends to image analysis for the tagging of product attributes—such as a white turtleneck cardigan with full sleeves—to provide an improved search experience.
Data Vital to ViSenze
Capturing and analyzing large volumes of data is integral to ViSenze. “We have to understand how consumers interact with our customers’ ecommerce websites and apps,” says Yao. “For example, we need to know who has looked at a particular pair of jeans on a website and whether that visit led to a conversion. We can then tell that customer whether they need to make more stock available.”
ViSenze also relies on data to provide high-quality training for its image recognition models and its domain-specific models for online retail.
Protect Customer Data
Data is vital to ViSenze—but customer privacy is most important. “All the data we collect is transparent to our customers, meaning they can decide what they do not want us to collect. In addition, all personal data processing complies with privacy protection regulations in each region, such as the General Data Protection Regulation in Europe.”
A Quick Move to the Cloud
ViSenze started operations using servers, storage, networking, and associated systems in an on-premises data center operated by NExT.
However, to support rapid growth, the business decided to move its workloads to the cloud. ViSenze opted for a multi-cloud architecture, using in part a Google Cloud data infrastructure.
“Our research found Google Cloud provided a complete, integrated ecosystem rather than a disparate collection of tools and components, and so was ideal for our needs,” says Yao. “We could connect different components with the click of a mouse.” Further, the business found it could easily configure rules and pipelines to route data logs to relevant Google Cloud services.
The review found Google Cloud’s extensive managed services would also remove administration and maintenance tasks from ViSenze’s in-house technology team—freeing team members to focus on more valuable tasks.
In addition, Google Cloud provided the security features—including custom hardware running hardened operating systems and file systems and encryption of data at rest and in transit—needed to protect sensitive information. Finally, the location of Google Cloud regions in several countries would enable the business to meet regulatory and data sovereignty requirements.
A Three-Month Implementation
ViSenze opted to move to Google Cloud in mid-2017 and completed a three-month implementation using internal resources. “The process was very smooth and intuitive, and we had no problems building our entire data platform within Google Cloud,” says Yao.
The business now uses an architecture comprising Google Kubernetes Engine to manage and orchestrate Docker containers running in Google Cloud Platform; BigQuery to provide an analytics data warehouse, with Google Data Studio providing customizable visualization and reports; Stackdriver to monitor and manage virtual machine instances and services inside Google Cloud; Cloud SQL to manage its relational databases for real-time analytics; Compute Engine to provide compute resources; Cloud Storage to store files and objects; Cloud Pub/Sub to provide real-time messaging between applications; and Cloud Functions to build event-driven applications.
After collecting the request logs of users in virtual machine instances and Docker containers, ViSenze distributes them in three directions. “We export raw logs into Cloud Pub/Sub for indexing inside an Elasticsearch search engine, and to a BigQuery data warehouse for further analytics,” explains Yao. “We also use Cloud Functions-created applications to obtain the logs from Cloud Pub/Sub to perform some real-time calculations.”
“We are currently using Airflow workflow management on Compute Engine as our hosted ETL platform, but are likely to move to Cloud Composer in future.”
500 Million Records Per Day
With Google Cloud providing its data infrastructure, ViSenze is well positioned to meet internal and customer demands for more granular insights. The business is now processing 500 million records per day through BigQuery and saves up to one year’s aggregated data—excluding any personal data—in the data warehouse for analysis.
The nature of ViSenze’s business means most reports are generated for data processed on an hourly, daily, or monthly basis. “BigQuery is extremely stable and performance optimized, regardless of the volume of data it processes,” says Yao. “Across BigQuery and other Google Cloud Platform services, we’ve recorded 99.99% availability over the past year.”
The lack of complexity and the ease of use of BigQuery has enabled ViSenze to reduce its data infrastructure and management costs by 30%–50%, and scale up without incurring downtime.
“With BigQuery, we have saved the equivalent of two full-time engineers and now need only half of one person’s time to maintain our whole data platform,” says Yao.
“In addition, BigQuery integrates closely with Data Studio, enabling non-technical people in our product and business teams to create dynamic, detailed analysis dashboards. We now use Data Studio to create nearly 50 separate reports.”
The business has now grown to offer access to more than 1 billion users and a listing of more than 400 million purchasable products.
Next Steps
ViSenze is now researching the potential of the Cloud AutoML suite of machine learning products to improve the training of its models and run a fully managed NoSQL database through Cloud Datastore.
“A NoSQL database service is the only missing piece of our architecture for now, and using Cloud Datastore would enable us to focus almost exclusively on our business,” says Yao. “With Google Cloud Platform, we are ideally positioned to continue providing support to our business team and help them continue expanding into new markets.
“In addition, we can help retailers and consumers to unlock the potential of the web and apps to transform the purchasing experience.”
Google Cloud’s Data Analytics May Recap
6891
Of your peers have already read this article.
5:00 Minutes
The most insightful time you'll spend today!
May was a very busy month for data analytics product innovation. If you didn’t have the chance to attend our inaugural Data Cloud Summit, video replays of all our sessions are now available so feel free to watch them at your own pace.
In this blog, I’d like to share some background behind the innovations we released in May, why we built them the way we did, and the type of value they can bring your company and your team.
But first, a huge thank you!
This week, we had the honor to announce that Google has been named a Leader in The Forrester Wave™: Streaming Analytics, Q2 2021 report. Forrester gave Dataflow a score of 5 out of 5 across 12 different criteria, stating: “Google Cloud Dataflow has strengths in data sequencing, advanced analytics, performance, and high-availability”.
Google has more than a decade of experience in building real-time and internet-scale systems for its own needs, and we are excited to see that our ability to provide customers with a reliable, scalable, and performant platform is bearing fruit.
This announcement comes on the back of the release of The Forrester Wave™: Cloud Data Warehouse, Q1 2021 report, which also named Google Cloud as a Leader.
We couldn’t be more excited about the recognition and appreciate all your feedback and trust in the work that we do to support your goal in accelerating data-powered innovation.
Innovation galore
Your feedback and your passion is the fuel that drives our ambition to deliver more and better services to you. That’s why, this year, we didn’t want to wait until Google Cloud Next to share some great products we have been working on. On May 26, our team announced a slew of new products, services and programs. Watch a quick summary below:
https://youtube.com/watch?v=DG1mOPMXJvw%3Fenablejsapi%3D1%26
Meeting you where you are
An important design principle behind all of our services is “meeting you where you are”. This means we aim to provide you with the tools and software you need to innovate on your own terms. Here are three new services that will help you do just that:
Datastream
Datastream, our new serverless change data capture (CDC) and replication service, allows your company to synchronize data across heterogeneous databases, storage systems, and applications reliably and with minimal latency to support real-time analytics, database replication, and event-driven architectures. Datastream delivers change streams from Oracle and MySQL databases into Google Cloud services such as BigQuery, Cloud SQL, Cloud Storage, and Cloud Spanner, saving time and resources while ensuring your data is accurate and up-to-date.
- Under the hood, Datastream reads CDC events (inserts, updates, and deletes) from source databases, and writes those events with minimal latency to a data destination. It leverages the fact that each database source has its own CDC log—binlog for MySQL and LogMiner for Oracle—which it uses for its own internal replication and consistency purposes.
- Datastream integrates with purpose-built and extensible Dataflow templates to pull the change streams written to Cloud Storage, and create up-to-date replicated tables in BigQuery for analytics. It also leverages Dataflow templates to replicate and synchronize databases into Cloud SQL or Cloud Spanner for database migrations and hybrid cloud configurations.
- Datastream also powers a Google-native Oracle connector in Cloud Data Fusion’s new replication feature for easy ETL/ELT pipelining. By delivering change streams directly into Cloud Storage, customers can leverage Datastream to implement modern, event-driven architectures.
Looker and BigQuery Omni on Microsoft Azure
Research on multi cloud adoption is unequivocal — 92% of businesses in 2021 report having a multi cloud strategy. We want to continue supporting your choice by providing the flexibility you need to see your strategy through.
- This past month, we introduced Looker, hosted on Microsoft Azure. For the first time, you can now choose Azure, Google Cloud, or AWS for your Looker instance. You can also self-host your Looker instance on-premises.
- We also introduced BigQuery Omni for Azure, which along with last year’s introduction of BigQuery Omni for AWS, will help you access and securely analyze data across Google Cloud, AWS, and Azure.
The cost of moving data between cloud providers isn’t sustainable for many, and it’s still difficult to seamlessly work across clouds. BigQuery Omni represents a new way of analyzing data stored in multiple public clouds, which is made possible by BigQuery’s separation of compute and storage. By decoupling these two, BigQuery provides scalable storage that can reside in Google Cloud or other public clouds, and stateless resilient compute that executes standard SQL queries.
- Unlike competitors, BigQuery Omni doesn’t require you to move or copy your data from one public cloud to another, where you might incur egress costs. You also benefit from the same BigQuery interface on Google Cloud, enabling you to query data stored in Google Cloud, AWS, and Azure without any cross-cloud movement or copies of data.
- BigQuery Omni’s query engine runs the necessary compute on clusters in the same region where your data resides. For example, you can query Google Analytics 360 Ads data stored in Google Cloud and query logs data from your ecommerce platform and applications that are stored in AWS S3 and/or Microsoft Azure.
Then, using Looker, you can build a dashboard that allows you to visualize your audience behavior and purchases alongside your advertising spend.
Dataplex
We understand that most organizations still struggle to make high-quality data easily discoverable and accessible for analytics, across multiple silos, to a growing number of people and tools within their organization.
They are often forced to make tradeoffs. For instance, moving and duplicating data across silos to enable diverse analytics use cases or leaving their data distributed but limiting the agility of decisions.
- Dataplex provides an intelligent data fabric that enables you to centrally manage, monitor, and govern your data across data lakes, data warehouses, and data marts, while also ensuring data is securely accessible to a variety of analytics and data science tools.
- One of the core tenets of Dataplex is letting you organize and manage your data in a way that makes sense for your business, without data movement or duplication. For that, we provide logical constructs like lakes, data zones, and assets. These constructs enable you to abstract away the underlying storage systems and become the foundation for setting policies around data access, security, lifecycle management, and so on.
- For example, you can create a lake per department within your organization (e.g. Retail, Sales, Finance, etc.) and create data zones that map to data readiness and usage (e.g. landing, raw, curated_data_analytics, curated_data_science, etc.).
Once you have your lakes and zones setup, you can attach data to these zones as assets. You can add data from different types of storage (e.g. GCS Bucket and BigQuery dataset) under the same zone. You can also attach data across multiple projects under the same zone. You can ingest data into your lakes and zones using the tools of your choice, including services such as Dataflow, Data Fusion, Dataproc, Pub/Sub, or choose from one of our partner products. Dataplex comes with built-in 1-click templates for common data management tasks.
To find out more about Dataplex, head to cloud.google.com/dataplex or watch the video below:
https://youtube.com/watch?v=bbFeAt7cw1g%3Fenablejsapi%3D1%26
Helping you innovate everyday
Sharing data is hard. Traditional data sharing techniques use batch data pipelines that are expensive to run, create late arriving data, and can break with any changes to the source data. These techniques also create multiple copies of data, which brings unnecessary costs and can bypass data governance processes. They also fail to offer features for data monetization, such as managing subscriptions and entitlements. Altogether, these challenges mean that organizations are unable to realize the full potential of transforming their business with shared data.
Analytics Hub
To address these limitations, we are introducing Analytics Hub, a new fully managed service that helps organizations unlock the value of data sharing, leading to new insights and increased business value.
This new service is built on the tremendous experience and feedback we have received over the years. For example, BigQuery has had cross-organizational, in-place data sharing capabilities since its inception in 2010—and the functionality is very popular. Over a 7-day period in April, we had over 3,000 different organizations sharing over 200 petabytes of data. These numbers don’t include data sharing between departments within the same organization.
Analytics Hub takes sharing to the next level, making it easy for you to publish, discover, and subscribe to valuable datasets that you can combine with your own data to derive unique insights.
This includes:
- Shared datasets: As a data publisher, you create shared datasets that contain the views of data that you want to deliver to your subscribers. Data subscribers can search through the datasets that are available across all exchanges for which they have access and subscribe to relevant datasets. In addition, the publisher can track subscribers, disable subscriptions, and see aggregated usage information for the shared data.
- Curated, self-service data exchanges: Exchanges are collections used to organize and secure shared datasets. By default, exchanges are completely private, but granular roles and permissions make it easy to deliver data to the right audience—whether internal or public.
This is just the beginning for Analytics Hub. Please sign up for the preview, which is scheduled to be available in the third quarter of 2021.
Dataflow Prime
At Google Cloud, we have the great privilege of working with some of the most innovative organizations in the world. And this work provides us with a unique perspective into the future of big data processing. Dataflow Prime is a new platform based on a serverless, no-ops, and auto-tuning architecture that brings unparalleled resource utilization and radical operational simplicity to big data processing. This new service introduces a large number of exciting capabilities but I’d like to highlight three key aspects of the product:
- Vertical Autoscaling: Dataflow Prime dynamically adjusts the compute capacity allocated to each worker based on utilization, detecting when jobs are limited by worker resources and automatically adding more resources. Vertical Autoscaling works hand in hand with Horizontal Autoscaling to seamlessly scale workers to best fit the needs of the pipeline. As a result, it no longer takes hours or days to determine the perfect worker configuration to maximize utilization.
- Right Fitting: Each stage of a pipeline typically has a different resource requirement than the others. Until now, either all workers in the pipeline would have had the higher memory and GPU, or none of them would. Pipelines either had to waste resources or suffer slower workloads. Right Fitting solves this problem by creating stage-specific pools of resources, optimized for each stage.
- Smart Recommendations: Smart Recommendations automatically detects problems in your pipeline and shows potential fixes. For example, if your pipeline is running into permissions issues, a Smart Recommendation will detect which IAM permissions you need to enable to unblock your job. If you are using an inefficient coder in your job, Smart Recommendations will surface more performant coder implementations that can help you save on costs.
What’s next
We’re excited to hear your thoughts and feedback about all these exciting new services. I would also highly recommend that you connect with members of the community to learn more about their story and journey. A good example to start with is the Data To Value customer panel we produced at our inaugural Data Cloud Summit with the Chief Data Officers of Keybank and Rackspace. You can watch it for free below:
https://youtube.com/watch?v=ITI2Q3MkxuA%3Fenablejsapi%3D1%26
Using Google Cloud’s Backup and DR Service with Logging and Monitoring Tools
1090
Of your peers have already read this article.
2:30 Minutes
The most insightful time you'll spend today!
Backup and DR data is a valuable business asset, and ensuring that it’s safe and accessible is essential. In particular, you want to be able to monitor your backups to ensure that the data is indeed protected and that you can quickly recover it in the event of a disaster, user error, or failed upgrade.
Users of Google Cloud’s Backup and DR service often ask:
- What are the best practices for monitoring my backup and recovery jobs?
- How can I proactively identify issues and troubleshoot?
- How can I create alerts and be notified about important events?
The good news is that Google Cloud Backup and DR now integrates with Cloud Logging and Cloud Monitoring tools. Now, you can monitor backup events, jobs, appliance health, resource consumption and user actions in the same way you monitor other Google Cloud workloads and services.
With this integration, you can now:
- Monitor events related to backup and restore – With detailed Google Cloud Backup and DR service event logs in Cloud Logging, you can now use log based alerts to create a customisable notification, in near-real time. This lets you monitor important events such as backup job failures, jobs that did not run, local backup storage saturation, and network failures.
- Configure fine grained alerting – You can write custom event queries on a wide variety of dimensions such as event severity, application type, application name, job type, job name, error message and many more. This gives a lot of flexibility to define alerts for specific events or conditions within a system, thus reducing noise and helping you identify and fix issues easily.
- Get notified on your preferred notification channel – Google Cloud offers seven pre-built notification channels that let you receive customisable notifications directly over email, SMS, Slack or the Google Cloud mobile app. Alternatively, you can integrate with your own monitoring and event management tools using webhooks or Pub/Sub.
- Derive useful insights to troubleshoot issues – With Log Analytics, you can use BigQuery to query your data using SQL queries and generate operational insights, which can help you reduce time spent troubleshooting. For example, you can analyze the key reasons that backup and restore jobs fail for a given application or application type.
The integration also lets you proactively discover deeper issues related to backup and recovery, such as capacity-related issues or certain job failures that happen periodically, by monitoring recurring events in the logs over time. With log-based metrics, you can:
- Create trend charts to track important metrics such as the number of backup or restore jobs that failed during a day/week/month
- Receive a notification when the number of occurrences crosses a threshold, for example receive a notification if a snapshot pool saturates more than five times a week
- Monitor trends in data, such as latency values in logs, and receive a notification if the values change in an unacceptable way.
Getting started
The time to learn that your backup failed should never be when you go to restore. By integrating our Backup and DR service with Cloud Monitoring and Cloud Logging, you can get valuable assurances about the health of your backup and business continuity processes with the same tools that you use to manage other Google Cloud workloads. To get started, check out the Backup and DR event logs page. You can also watch a video that shows you how to set up and use the service and configure some custom alerts.
Migrating From Oracle OLTP System to Cloud Spanner
3969
Of your peers have already read this article.
8:30 Minutes
The most insightful time you'll spend today!
Spanner uses certain concepts differently from other enterprise database management tools, so you might need to adjust your application to take full advantage of its capabilities. You might also need to supplement Spanner with other services from Google Cloud to meet your needs.
Migration constraints
When you migrate your application to Spanner, you must take into account the different features available. You probably need to redesign your application architecture to fit with Spanner’s feature set and to integrate with additional Google Cloud services.
Stored procedures and triggers
Spanner does not support running user code in the database level, so as part of the migration, you must move business logic implemented by database-level stored procedures and triggers into the application.
Sequences
Spanner does not implement a sequence generator, and as explained below, using monotonically increasing numbers as primary keys is an anti-pattern in Spanner. An alternative way to generate a unique primary key is to use a random UUID.
If sequences are required for external reasons, then you must implement them in the application layer.
Access controls
Spanner supports only database-level access controls using IAM access permissions and roles. Predefined roles can give read-write or read-only access to the database.
If you require finer grained permissions, you must implement them at the application layer. In a normal scenario, only the application should be allowed to read and write to the database.
If you need to expose your database to users for reporting, and want to use fine-grained security permissions (such as table- and view-level permissions), you should export your database to BigQuery.
Read the full article for more, including
- Data validation constraints
- Supported data types
- Migration process
- Transferring your data from Oracle to Spanner
- Maintaining consistency between both databases
- Verifying data consistency
- and more.
Cart.com to Transform e-Commerce for Brands Globally
8746
Of your peers have already read this article.
1:30 Minutes
The most insightful time you'll spend today!
The ecommerce playing field has been hard to navigate for most retailers, and Cart.com is on a mission to change that. Traditionally, retailers needing to run their online store, order fulfillment, customer service, marketing, and other essential activities have had to cobble together systems to get the capabilities they need – much less having access to analytics across these functions. The result is costly, siloed ecommerce operations that are difficult to manage and scale.
It’s clearly not a formula for success, yet that’s the reality facing most retailers. Cart.com, in contrast, has set out to democratize ecommerce by giving brands of all sizes the full capabilities they need to take on the world’s largest online retailers. Our end-to-end environment empowers retailers to keep more of their revenue, set up proven strategies for managing all aspects of their business, and act on valuable insights from customer data every step of the way.
Together with our talented team, we’re building a unified ecommerce platform that already provides value to many leading or up and coming brands including Whataburger, GUESS, Dr. Scholl’s, Rowing Blazers, and Howler Bros.
We’re excited about the opportunity ahead as we reimagine traditional approaches to online sales, fulfillment, marketing, accessing growth capital, providing a unified view of all ecommerce and marketing analytics, and other activities. Expectations for Cart.com are high, and we are building a company that can scale to $100B in revenue and beyond. Supported by the Startup Program by Google Cloud and Google Cloud solutions, we’re establishing a technology platform to transform all aspects of ecommerce for brands worldwide.
Partner in disruption
At Cart.com, we’re currently targeting an underserved market. Our ideal customer is beyond demonstrating product-market-fit and is now at an inflection point seeking a growth opportunity. Typically, those companies are generating between $1M and $100M in annual revenue. We’ve seen an enthusiastic response from brands and retailers as well as investors, with backing from investors in just over a year totaling $143 million in three funding rounds.
Our strategy is to build an integrated ecommerce model that combines best-of-breed solutions, many of which we gain through acquisitions and then build upon to provide a streamlined and fully integrated experience for our brands. We’ve made seven acquisitions so far to round out our online store, order fulfillment, marketing services, customer service, and we have launched some integral partnerships including easy access to growth capital through our relationship with Clearco and product protection for customers on every purchase with Extend. Instead of acquiring a data company, we’re building our data platform on Google Cloud, across each operating function for a single-view for brands to harness actionable data. We see Google Cloud as the leader for data management, analytics, machine learning (ML) and artificial intelligence (AI).
Other reasons why we’re building our business on Google Cloud include scalability, excellence, security, reach, and data analytics that are far superior to other environments.
We also feel a cultural and mission alignment with Google Cloud and envision leaning into a long-term partnership of marketing, selling, and disrupting the disruptors together. Equally important to us are the investments Google Cloud is willing to make in early-stage companies like ours. The support through the Google Cloud for Startups program has been outstanding.
Built on Google Cloud
A wide range of Google Cloud solutions provide the foundation for our platform. For instance, Cloud Pub/Sub keeps our services communicating with one another. We rely on fully managed relational databases, like Cloud SQL and Cloud Spanner, to securely handle the huge volume of brand and shopper data generated every day.
Cloud Run allowed us to develop inside of containers before our Kubernetes infrastructure was ready to go. Now, we are taking advantage of all the capabilities in Google Kubernetes Engine. BigQuery integrates with all Google Cloud solutions and offers true data streaming natively out of the box, along with Dataflow for advanced analytics. We also use Container Registry to store and manage our Docker container images. Right now, we’re testing Cloud Composer to evaluate using it for data workflow orchestration instead of Apache Airflow.
The openness of the Google Cloud environment is further enabled by Anthos, which we may deploy soon to perform data integrations quickly as we acquire more companies over the next year. For example, if we acquire a company using Azure, we can easily align it with our Google Cloud ecosystem.
Enabling ecommerce 2.0
Recently, our team has been experimenting with Google Cloud Vertex AI and the fully managed services of AI deployment and ML operations. The capabilities would save us substantial time in the management of the ML lifecycle which allows us to focus more on developing proprietary AI that will transform commerce at scale.
Because Google Cloud is so far ahead in data science, our teams benefit from deep Google Cloud expertise as we look to provide brands with unmatched insights into customers to improve services and revenue. We’re also planning to test Recommendations AI among other tools to deploy customer product recommendations and personalization as turnkey productized offerings. Moving forward, we will likely use Bigtable to aid in serving machine learning to hundreds of thousands of brands due to its low latency and scalability.
Fanatical about brand success
We know that our work with Google Cloud for Startups and use of Google Cloud solutions for best-in-class data management, analytics, ML, and AI will enable us to offer even more transformative services to brands.
We also see the opportunity to use our platform and customer insights to break down barriers between brands, enabling retailers to share information and work better together when it’s in their best interests. What we’re building today on Google Cloud is fundamentally changing what’s possible for retailers of any size everywhere.
As a startup, when recruiting talent or working with prospective customers, it helps to share our success with Google Cloud. We view them as an extension of the Cart.com team. It also validates our business as we continue building a more integrated, holistic approach to commerce that opens new opportunities and drives growth for brands worldwide.
For more details about Cart.com’s vision for unified ecommerce, check out our video.
If you want to learn more about how Google Cloud can help your startup, visit our page here to get more information about our program, and sign up for our communications to get a look at our community activities, digital events, special offers, and more.
More Relevant Stories for Your Company
Key Highlights on Data Analytics to Smooth Your Organization’s Data Journey
As the Olympics kicked off in Tokyo at the end of July, we found ourselves reflecting on the beauty of diverse countries and cultures coming together to celebrate greatness and sportsmanship. For this month’s blog, we’d like to highlight some key data and analytics performances that should help inspire you
Bigtable’s High Performance and Low Opex is Great for Building Personalization
Customer expectations have shifted as a result of evolving needs. Across industries, customers expect that you treat them as individuals, and demonstrate how well you understand and serve their unique needs. This concept—personalization—is the idea that you’re delivering a tailored experience to each customer corresponding to their needs and preferences;
How BigQuery’s Unique Features Support Your Data at Petabyte-scale
Organizations rely on data warehouses to aggregate data from disparate sources, process it, and make it available for data analysis in support of strategic decision-making. BigQuery is the Google Cloud enterprise data warehouse designed to help organizations to run large scale analytics with ease and quickly unlock actionable insights. You
HarbourBridge Schema Assistant Allows Quick, Bulk Migration to Cloud Spanner
Today we’re announcing the HarbourBridge Schema Assistant, which provides a guided schema-design workflow for migrating from MySQL or PostgreSQL to Spanner. HarbourBridge imports dump files (from mysqldump or pg_dump) or directly connects to your source database, and converts the source database schema to an equivalent Spanner schema. The new Schema Assistant
