Unifying Data and AI: Bringing Unstructured Data Analytics to BigQuery
4180
Of your peers have already read this article.
2:30 Minutes
The most insightful time you'll spend today!
Over one third of organizations believe that data analytics and machine learning have the most potential to significantly alter the way they run business over the next 3 to 5 years. However, only 26% of organizations are data driven. One of the biggest reasons for this gap is that a major portion of the data generated today is unstructured, which includes images, documents, and videos. It is estimated to cover roughly up to 80% of all data, which has so far remained untapped by organizations.
One of the goals of Google’s data cloud is to help customers realize value from data of all types and formats. Earlier this year, we announced BigLake, which unifies data lakes and warehouses under a single management framework, enabling you to analyze, search, secure, govern and share unstructured data using BigQuery.
At Next ‘22, we announced the preview of object tables, a new table type in BigQuery that provides a structured record interface for unstructured data stored in Google Cloud Storage. This enables you to directly run analytics and machine learning on images, audio, documents and other file types using existing frameworks like SQL and remote functions natively in BigQuery itself. Object tables also extend our best practices of securing, sharing and governing structured data to unstructured, without needing to learn or deploy new tools.
Directly process unstructured data using BigQuery ML
Object tables contain metadata such as URI (Uniform Resource Identifier), content type, and size that can be queried just like other BigQuery tables. You can then derive inferences using machine learning models on unstructured data with BigQuery ML. As part of preview, you can import open source TensorFlow Hub image models, or your own custom models to annotate the images. Very soon, we plan to enable this for audio, video, text and many other formats, and pre-trained models to enable out-of-the box analysis. Check out this video to learn more and watch a demo.
Create an object table
CREATE EXTERNAL TABLE my_dataset.object_table
WITH CONNECTION us.my_connection
OPTIONS(uris=["gs://mybucket/images/*.jpg"],
object_metadata="SIMPLE", metadata_cache_mode="AUTOMATIC");
# Generate inferences with BQML
SELECT * FROM ML.PREDICT(
MODEL my_dataset.vision_model,
(SELECT ML.DECODE_IMAGE(data) AS img FROM my_dataset.object_table)
);By analyzing unstructured data natively in BigQuery, businesses can
- Eliminate manual effort as pre-processing steps such as tuning image sizes to model requirements are automated
- Leverage the simple and familiar SQL interface to quickly gain insights
- Save costs by utilizing existing BigQuery slots without needing to provision new forms of compute
Adswerve is a leading Google Marketing, Analytics and Cloud partner on a mission to humanize data. Twiddy & Co. is Adswerve’s client – a vacation rental company in North Carolina. By combining structured and unstructured data, Twiddy and Adswerve used BigQuery ML to analyze images of rental listings and predict the click-through rate, enabling data-driven photo editorial decisions.
“Twiddy now has the capability to use advanced image analysis to stay competitive in an ever changing landscape of vacation rental providers – and can do this using their in-house SQL skills.” said Pat Grady, Technology Evangelist, Adswerve
Process unstructured data using remote functions
Customers today use remote functions (UDFs) to process structured data for languages and libraries that are not supported in BigQuery. We are extending this capability to process unstructured data using object tables.
Object tables provide signed URLs to allow remote UDFs running on Cloud Functions or Cloud Run to process the object table content. This is particularly useful for running Google’s pre-trained AI models, including Vision AI, Speech-to-Text, Document AI, open source libraries such as Apache Tika, or deploying your own custom models where performance SLAs are important.
Here’s an example of an object table being created over PDF files that are parsed using an open source library running as a remote UDF.
SELECT uri, extract_title(samples.parse_tika(signed_url)) AS title<br>FROM EXTERNAL_OBJECT_TRANSFORM(TABLE pdf_files_object_table,<br>["SIGNED_URL"]);
Extending more BigQuery capabilities to unstructured data
Business intelligence – The results of analyzing unstructured data either directly in BigQuery ML or via UDFs can be combined with your structured data to build unified reports using Looker Studio (at no charge), Looker or any of your preferred BI solutions. This allows you to gain more comprehensive business insights. For example, online retailers can analyze product return rates by correlating them with the images of defective products. Similarly, digital advertisers can correlate ad performance with various attributes of ad creatives to make more informed decisions.
BigQuery search index – Customers are increasingly using the search functionality of BigQuery to power search use cases. These capabilities now extend to unstructured data analytics as well. Whether you use BigQueryML to produce inference on images or use remote UDFs with Doc AI to produce document extraction, the results can now be search indexed and used to support search access patterns.
Here’s an example of search index on data that is parsed from PDF files:
CREATE SEARCH INDEX my_index ON pdf_text_extract(ALL COLUMNS);
SELECT * FROM pdf_text_extract WHERE SEARCH(pdf_text, "Google");Security and governance – We are extending BigQuery’s row-level security capabilities to help you secure objects in Google Cloud Storage. By securing specific rows in an object table, you can restrict the ability of end users to retrieve the signed URLs of corresponding URIs present in the table. This is a shared responsibility security model, for which administrators need to ensure that end users don’t have direct access to Google Cloud Storage, and use signed URLs from object tables as the only access mechanism.
Here’s an example of a policy for PII images that are secured to be first processed through a blur pipeline:
CREATE ROW ACCESS POLICY pii_data ON object_table_images
GRANT TO ("group:admin@example.com")
FILTER USING (ARRAY_LENGTH(metadata)=1 AND
metadata[OFFSET(0)].name="face_detected")Soon, Dataplex will support object tables, allowing you to automatically create object tables in BigQuery and manage and govern unstructured data at scale.
Data sharing – You can now use Analytics Hub to share unstructured data with partners, customers and suppliers while not compromising on security and governance. Subscribers can consume the rows of object tables that are shared with them, and use signed URLs for unstructured data objects.
Getting Started
Submit this form to try these new capabilities that unlock the power of your unstructured data in BigQuery. Watch this demo to learn more about these new capabilities.
Special thanks to engineering leaders Amir Hormati, Justin Levandoski and Yuri Volobuev for contributing to this post.
Empower Your Firm’s Data Decisions with Dataplex Data Lineage
1512
Of your peers have already read this article.
2:30 Minutes
The most insightful time you'll spend today!
Today, we are excited to announce the general availability of Dataplex data lineage — a fully managed Dataplex capability that helps you understand how data is sourced and transformed within the organization. Dataplex data lineage automatically tracks data movement across BigQuery, BigLake, Cloud Data Fusion (Preview), and Cloud Composer (Preview), eliminating operational hassles around manual curation of lineage metadata.
With rising data volume spread across data silos, it can be challenging for organizations to ensure users have a self-service mechanism to discover, understand and trust the data. Organizations constantly struggle with questions such as:
- Is the data extracted from an authoritative source?
- What is the impact if I drop this table?
- The data in this table seems corrupted – where did this data come from, and when was it last refreshed?
- How is sensitive information being moved or copied? Is it in adherence to data governance practices?
To answer the above questions, organizations need to track how data is sourced and transformed, which can be complex and requires significant effort.
Dataplex data lineage describes each lineage relationship by detailing what happened and when it happened in an interactable lineage graph, providing data observability.
Data analysts who want to know if a table originates from an authoritative source can now answer this in a self-service manner with a simple look-up of lineage for the concerned table — available in Dataplex and in BigQuery for in-context analysis.
Data engineers can reduce time to identify and resolve data issues through root cause analysis using the operational metadata trace asserting a lineage relationship. Data lineage also aids deterministic change management by providing the ability to evaluate the impact of a change and collaborate with the corresponding stakeholders to minimize any adverse impact.
Finally, data lineage provides a map of data movement which can become the foundation for data governance practice. It enables data stewards and owners to evaluate and enforce adherence to governance requirements, especially when tracking the movement of sensitive information.
Dataplex data lineage provides APIs for extensibility so that organizations can report lineage from various systems and have a single map of how data entries are related.
What our customers are saying
L’Oréal, the world’s largest cosmetics company, is on a mission to ‘create the beauty that moves the world.’ “Dataplex data lineage helps us understand how data moves across our organization,” said Sébastien Morand, Head of Data Engineering team, L’Oréal. “As a fully managed solution, it becomes the main entry point to diagnose data issues and evaluate the impact of a change or incident — providing insight on what happened and when it happened, including reference to the execution metadata. Directly integrated into our beauty tech data platform, data lineage helps us reduce data issues and also enables us to mitigate issues faster when it does happen.”
“At Wayfair, we treat data-as-a-product and are building a robust data platform that provides self-service access and compliance constructs,” said Vinit Rajopadhye, Associate Director on Data Infrastructure & Data Enablement at Wayfair. “We are excited about Dataplex data lineage as it helps our data consumers trust data based on where it originates and the transformations applied.”
Hurb is an online travel agency in Brazil with a mission to optimize travel through technology. “Hurb has a rapidly growing data platform, with new data assets created and registered daily to support business decision-making and Machine Learning models,” said Vinícius dos Santos Mello, Senior Data Engineer. “Thanks to Dataplex data lineage features, we have end-to-end data observability across data in BigQuery. We can proactively address schema changes, data quality issues, and asset depreciation that could otherwise negatively affect the business.”
“As a company with many business domains and services, we handle a large volume of data and use it to power our decision making, so it is crucial to ensure data quality. Dataplex data lineage provides a visual understanding of the flow of data across our organization, improving efficiency of impact investigations when problems occur and increasing the reliability of the data.” said Mitsunori Fukase, Data Platform Department Group Manager, DeNA.
Get started with Dataplex data lineage
You can get started with Dataplex data lineage by enabling the Data Lineage API on your project. You can learn more here.
Additional Resources:
Google Introduces BigQuery Connector for SAP to Power Customers’ Data Analytics Strategy
5473
Of your peers have already read this article.
2:00 Minutes
The most insightful time you'll spend today!
Google Cloud has a genuine passion for solving technology problems that make a difference for our customers. With the release of our BigQuery Connector for SAP, we’re taking a another big step towards solving a major challenge for SAP customers with a quick, easy, and inexpensive way to integrate SAP data with BigQuery, our serverless, highly scalable, and cost-effective multi cloud data warehouse designed for business agility.
Solving for simplified data integration
Like most businesses today, SAP customers are eager to unlock the immediate insights and opportunities within their ever-growing stores of business data. However, many are discovering just how hard it can be to take the first step in any modern, cloud-enabled data analytics strategy: combining SAP data with other cloud-native, and enterprise data sets in real-time and at scale. According to a 2020 SAPInsider study, more than half of SAP customers surveyed said data integration was their top analytics pain point. These companies urgently need a rapid, sustainable, cost-effective and scalable way to integrate SAP data with modern cloud data analytics solutions.
The BigQuery Connector for SAP gives our customers a solution: a fast, simple, cost-effective and massively scalable way to make SAP data fully accessible within BigQuery by leveraging customers’ existing SAP Landscape Transformation Replication Server (SLT) tooling and skill sets. It’s the first SAP SLT direct near real-time connector for BigQuery without the need to set up additional infrastructure or third-party middleware, and can be deployed using a variety of embedded or stand-alone deployment options. In fact, most customers can install the BigQuery Connector for SAP in less than an hour—a remarkably easy way to start working with our industry-leading analytics solution that delivers proven and quantifiable business advantages for customers. Additionally, the BigQuery Connector for SAP is not restricted to customers who have deployed their SAP applications on Google Cloud. Customer’s who are running their SAP applications on-premise, or on any cloud, can also deploy and realize the analytical benefits of the solution.
Designing a solution with customer requirements and investments in mind
When the Google Cloud team started work on an analytics data integration tool for our SAP customers, we began with a set of requirements designed to root out the usual sources of cost and complexity. These included:
- The need for real-time performance with deltas replicated in milliseconds
- The ability to integrate data from almost any SAP Netweaver based application running today, regardless of its deployment location (on premises, any cloud, Google Cloud)
- Automatic BigQuery data type mapping with minimal transformation required
- Generation of target tables in BigQuery directly from source, if required
- Application layer integration that avoids the issues of direct database access
- Leveraging customers’ existing SAP skillsets, change data capture, and infrastructure
An important step towards meeting these requirements came when Alphabet, Google’s parent company, decided to leverage SAP SLT as a foundation for developing direct data replication between SAP and BigQuery for its internal corporate landscape. SLT as part of SAP’s strategic Business Technology Platform, supports real-time replication of data from SAP or third-party systems to SAP HANA, however, one of its limitations was direct integration with targets like BigQuery.
SAP SLT was a logical foundation for developing the connector for several reasons:
- It’s widely adopted among SAP customers who likely already leverage SLT for SAP analytics data integration
- It works with almost every non-SaaS SAP application environment running today
- It supports real-time replication performance at massive scale
It was an obvious choice for the Alphabet engineering team who saw immediate value from integrating SAP with BigQuery.
“The BigQuery Connector for SAP has enabled fast, low latency data replication for billions of records from 500+ tables of our most critical financial and supply chain data. Now in one cost-effective BigQuery data lake, this ERP data can be combined with other data sources for previously impossible real-time analytics and ML use cases. This allows us to drive much deeper strategic insights that support business and operational excellence, management and P&L reporting and more.”—Anil Nagalla, Sr. Engineering Director, Financial Systems, Google
SAP data integration with BigQuery enables new value
By leveraging SAP SLT, the BigQuery Connector for SAP can integrate real-time data streams from any SAP system—while also taking advantage of customers’ existing SAP investments and skillsets.
At the same time, the BigQuery Connector for SAP does a lot of heavy lifting on its own. For example, it automatically handles the complex, multi-step process of transforming SAP data types for use in BigQuery—mapping data-type transitions between the SAP and BigQuery environments, creating a target table schema on BigQuery for the transformed data types, building the target BigQuery table, and even adapting as new data types appear in your SAP environment.
For teams that want to fine-tune the BigQuery Connector for SAP’s automated recommendations, the connector supports additional levels of customization and choice. But if you simply want to get the job done and give your data analytics team greater support for their high-value work, then you’ll love just how quickly and easily the BigQuery Connector for SAP turns the complicated work of data integration and performance to process large volumes of data into a done deal. By integrating enterprise data sets in real time, customers can drive differentiated value and unlock new insights and actions that drive a competitive advantage.
The BigQuery Connector for SAP really shines as an enabling tool that transports and transforms your SAP data to power analytics solutions enabled by accelerators like the Google Cloud Cortex Framework: a comprehensive set of reference architectures, deployment accelerators, and integration services designed to give SAP customers a fast and seamless path to value with their data analytics investments. Simply put, the more SAP data you make available within Google Cloud, the easier it is to get meaningful—and often game-changing—insights from these solutions.
Learn more about the BigQuery Connector for SAP
Ready to get started with your own SAP data analytics strategy on Google Cloud? Install the Google Cloud BigQuery Connector for SAP, and discover a faster, simpler, more sustainable way to power your company’s data analytics strategy.
3503
Of your peers have already downloaded this article
19:30 Minutes
The most insightful time you'll spend today!
While data has influenced major business decisions throughout the ages, the term “data warehouse” isn’t even half a century old. But it’s an important concept; by centralizing data from many disparate sources, analysts can make more informed decisions. Traditionally, this has meant collecting data in on-premises infrastructure. But as data volumes have grown, these legacy systems have not been able to keep up with changing demands: They are difficult to scale, offer no support for machine learning and artificial intelligence initiatives, and are simply too expensive to maintain.
With increased advances in technology, the introduction of more cost-effective compute and storage options have changed the data warehouse landscape. Now more than ever, businesses need an agile and efficient analytics infrastructure that allows them to derive insights at a fraction of the cost of legacy systems. Modern data warehouses can give organizations the competitive edge, offering customers fast and easy access to business intelligence while seamlessly scaling to meet increased demands.
In this ebook, based on the keynote presentation featuring Forrester Analyst Michele Goetz at the 2019 Future of Your Data Warehouse digital conference, we’ll look at the evolution of data warehousing and how a cloud infrastructure can offer immense benefits for the future.
3979
Of your peers have already listened to this podcast
23:38 Minutes
The most insightful time you'll spend today!
How Apna is using data and AI to drive the gig economy in India
Over one third of organizations believe that data analytics and machine learning have the most potential to significantly alter the way they run business over the next 3 to 5 years. However, only 26% of organizations are data driven. One of the biggest reasons for this gap is that a major portion of the data generated today is unstructured, which includes images, documents, and videos. It is estimated to cover roughly up to 80% of all data, which has so far remained untapped by organizations.
One of the goals of Google’s data cloud is to help customers realize value from data of all types and formats. Earlier this year, we announced BigLake, which unifies data lakes and warehouses under a single management framework, enabling you to analyze, search, secure, govern and share unstructured data using BigQuery.
At Next ‘22, we announced the preview of object tables, a new table type in BigQuery that provides a structured record interface for unstructured data stored in Google Cloud Storage. This enables you to directly run analytics and machine learning on images, audio, documents and other file types using existing frameworks like SQL and remote functions natively in BigQuery itself. Object tables also extend our best practices of securing, sharing and governing structured data to unstructured, without needing to learn or deploy new tools.
Directly process unstructured data using BigQuery ML
Object tables contain metadata such as URI (Uniform Resource Identifier), content type, and size that can be queried just like other BigQuery tables. You can then derive inferences using machine learning models on unstructured data with BigQuery ML. As part of preview, you can import open source TensorFlow Hub image models, or your own custom models to annotate the images. Very soon, we plan to enable this for audio, video, text and many other formats, and pre-trained models to enable out-of-the box analysis. Check out this video to learn more and watch a demo.
Create an object table
CREATE EXTERNAL TABLE my_dataset.object_table
WITH CONNECTION us.my_connection
OPTIONS(uris=["gs://mybucket/images/*.jpg"],
object_metadata="SIMPLE", metadata_cache_mode="AUTOMATIC");
# Generate inferences with BQML
SELECT * FROM ML.PREDICT(
MODEL my_dataset.vision_model,
(SELECT ML.DECODE_IMAGE(data) AS img FROM my_dataset.object_table)
);By analyzing unstructured data natively in BigQuery, businesses can
- Eliminate manual effort as pre-processing steps such as tuning image sizes to model requirements are automated
- Leverage the simple and familiar SQL interface to quickly gain insights
- Save costs by utilizing existing BigQuery slots without needing to provision new forms of compute
Adswerve is a leading Google Marketing, Analytics and Cloud partner on a mission to humanize data. Twiddy & Co. is Adswerve’s client – a vacation rental company in North Carolina. By combining structured and unstructured data, Twiddy and Adswerve used BigQuery ML to analyze images of rental listings and predict the click-through rate, enabling data-driven photo editorial decisions.
“Twiddy now has the capability to use advanced image analysis to stay competitive in an ever changing landscape of vacation rental providers – and can do this using their in-house SQL skills.” said Pat Grady, Technology Evangelist, Adswerve
Process unstructured data using remote functions
Customers today use remote functions (UDFs) to process structured data for languages and libraries that are not supported in BigQuery. We are extending this capability to process unstructured data using object tables.
Object tables provide signed URLs to allow remote UDFs running on Cloud Functions or Cloud Run to process the object table content. This is particularly useful for running Google’s pre-trained AI models, including Vision AI, Speech-to-Text, Document AI, open source libraries such as Apache Tika, or deploying your own custom models where performance SLAs are important.
Here’s an example of an object table being created over PDF files that are parsed using an open source library running as a remote UDF.
SELECT uri, extract_title(samples.parse_tika(signed_url)) AS title<br>FROM EXTERNAL_OBJECT_TRANSFORM(TABLE pdf_files_object_table,<br>["SIGNED_URL"]);
Extending more BigQuery capabilities to unstructured data
Business intelligence – The results of analyzing unstructured data either directly in BigQuery ML or via UDFs can be combined with your structured data to build unified reports using Looker Studio (at no charge), Looker or any of your preferred BI solutions. This allows you to gain more comprehensive business insights. For example, online retailers can analyze product return rates by correlating them with the images of defective products. Similarly, digital advertisers can correlate ad performance with various attributes of ad creatives to make more informed decisions.
BigQuery search index – Customers are increasingly using the search functionality of BigQuery to power search use cases. These capabilities now extend to unstructured data analytics as well. Whether you use BigQueryML to produce inference on images or use remote UDFs with Doc AI to produce document extraction, the results can now be search indexed and used to support search access patterns.
Here’s an example of search index on data that is parsed from PDF files:
CREATE SEARCH INDEX my_index ON pdf_text_extract(ALL COLUMNS);
SELECT * FROM pdf_text_extract WHERE SEARCH(pdf_text, "Google");Security and governance – We are extending BigQuery’s row-level security capabilities to help you secure objects in Google Cloud Storage. By securing specific rows in an object table, you can restrict the ability of end users to retrieve the signed URLs of corresponding URIs present in the table. This is a shared responsibility security model, for which administrators need to ensure that end users don’t have direct access to Google Cloud Storage, and use signed URLs from object tables as the only access mechanism.
Here’s an example of a policy for PII images that are secured to be first processed through a blur pipeline:
CREATE ROW ACCESS POLICY pii_data ON object_table_images
GRANT TO ("group:admin@example.com")
FILTER USING (ARRAY_LENGTH(metadata)=1 AND
metadata[OFFSET(0)].name="face_detected")Soon, Dataplex will support object tables, allowing you to automatically create object tables in BigQuery and manage and govern unstructured data at scale.
Data sharing – You can now use Analytics Hub to share unstructured data with partners, customers and suppliers while not compromising on security and governance. Subscribers can consume the rows of object tables that are shared with them, and use signed URLs for unstructured data objects.
Getting Started
Submit this form to try these new capabilities that unlock the power of your unstructured data in BigQuery. Watch this demo to learn more about these new capabilities.
Special thanks to engineering leaders Amir Hormati, Justin Levandoski and Yuri Volobuev for contributing to this post.
5925
Of your peers have already watched this video.
33:30 Minutes
The most insightful time you'll spend today!
How Spotify Serves Personalized Music Recommendations
Music for everyone. That’s Spotify’s promise. And it lives up to it by serving over 140 million music lovers across the world. Every day it renders personalized music recommendations to hundreds of millions of happy customers.
“Spotify is a global business with a global user base. Being able to provide a great audio experience to our users is a key priority for us,” said Niklas Gustavsson, chief architect at Spotify.
To be able to do that, Spotify needed a system that would scale and enable personalization and provide a seamless customer experience. That’s why it moved its infrastructure to Google Cloud and Cloud Bigtable. This allows Spotify to deliver recommendations at scale, roll out experiments quickly, and ingest terabytes every day via Cloud Dataflow.
“With Cloud Bigtable clusters in Asia, Europe, and the United States, we’re able to get low-latency data access all over the world, enabling Spotify to provide a seamless experience for our users. We’re also pleased with the continuous collaboration and deep engagement with Google’s product teams to accelerate both our businesses, ” says Gustavsson.
In this video, Peter Sobot, Senior Engineer, Personalization, Spotify talks about how Cloud Bigtable is helping Cloud Bigtable personalize recommendations, including tips about how to design a good schema, how to avoid latency when ingesting new data, and effective caching strategies to scale to tens of millions of data points per second.
More Relevant Stories for Your Company
Behind the Scenes: How eBay Provides its Customers New Shopping Experiences
“If it exists in the world, you are likely to find it on eBay.” So they say. With 180 million buyers and a global presence in over 190 markets, it’s probably true. A company that emerged out of the ashes of the dot-com bubble, eBay today is one of the
5 Best Practices for Cloud Cost Optimization
When customers migrate to Google Cloud Platform (GCP), their first step is often to adopt Compute Engine, which makes it easy to procure and set up virtual machines (VMs) in the cloud that provide large amounts of computing power. Launched in 2012, Compute Engine offers multiple machine types, many innovative features, and is available in
Transform Your Customer Experience with Latest Database Innovations
We’re all accustomed to Google magic in our daily lives. When we need information like the time a store closes, the best route to a destination, help cooking a favorite recipe — we just ask Google. Google is known for providing useful information everywhere, wherever you are. But for business,
Discover Effingo: Google’s Solution to Moving Data at Massive Scale
Every hour of every day, Google moves a lot of data around the world. But how? With the Effingo (Latin for ‘to duplicate’ or ‘copy’) data copying service, a Google-only service that moves data internally and on Google Cloud customers’ behalf. As a Google Cloud customer perhaps you move data
