Unifying Data and AI: Bringing Unstructured Data Analytics to BigQuery
4182
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.
4956
Of your peers have already watched this video.
49:30 Minutes
The most insightful time you'll spend today!
How to Migrate an Oracle Database to Google Cloud
As more enterprises migrate a growing number of workloads to the cloud, migrating databases, too, has become a key focus.
But migrating databases is also really tough. This is due to a number of different reasons.
The first one is that there’s a lot of proprietary technology and functionality that’s built into a lot of the legacy database technologies.
With Oracle, for example, that may be things like stored procedure, custom functions, Oracle RAC, so on and so forth.
This makes it really hard for database administrators to migrate to an equivalent cloud-native technology.
The second challenge is that historical on-prem databases are all quite monolithic.
These are humongous servers which don’t really fit into the cloud way of doing things.
So often, database administrators have to split these up into multiple servers in the cloud.
The third challenge, which a lot of companies underestimate, is the reliance of applications on databases.
Say, you are a company that’s been around for 10 to 15 years. As the business has grown, it’s added tens to hundreds of apps, both internal- and external-facing, that are all relying on the database.
So as you’re thinking about migration, it’s really important to consider the risks that are associated of migrating to different cloud technologies. Obviously, you would like to minimize your company’s exposure.
With that background, here are the steps, procedures, best practices, and how to solve the challenges associated with migrating on-premises Oracle databases to Google Cloud SQL PostgreSQL.
From source database migration assessment to schema conversion, data replication, and performance tuning, we will cover all of the basics required to get you started with your first Oracle to Cloud SQL migration project.
Key Highlights on Data Analytics to Smooth Your Organization’s Data Journey
4859
Of your peers have already read this article.
2:00 Minutes
The most insightful time you'll spend today!
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 to reach new heights in your data journey.
Let’s review the highlights!
BigQuery ML Anomaly Detection: A perfect 10 for augmented analytics
Identifying anomalous behavior at scale is a critical component of any analytics strategy. Whether you want to work with a single frame of data or a time series progression, BigQuery ML allows you to bring the power of machine learning to your data warehouse.
In this blog released at the beginning of last month, our team walked through both non-time series and time-series approaches to anomaly detection in BigQuery ML:
- Non-time series anomaly detection
- Autoencoder model (now in Public Preview)
- K-means model (already in GA)
- Time-series anomaly detection
- ARIMA_PLUS time series model (already in GA)
These approaches make it easy for your team to quickly experiment with data stored in BigQuery to identify what works best for your particular anomaly detection needs. Once a model has been identified as the right fit, you can easily port that model into the Vertex AI platform for real-time analysis or schedule it in BigQuery for continued batch processing.
App Analytics: Winning the team event
Google provides a broad ecosystem of technologies and services aimed at solving modern day challenges. Some of the best solutions come when those technologies are combined with our data analytics offerings to surface additional insights and provide new opportunities.
Firebase has deep adoption in the app development community and provides the technology backbone for many organization’s app strategy. This month we launched a design pattern that shows Firebase customers how to use Crashlytics data, CRM, issue tracking, and support data in BigQuery and Looker to identify opportunities to improve app quality and enhance customer experiences.
Crux on BigQuery: Taking gold in the all-around data competition
Crux Informatics provides data services to many large companies to help their customers make smarter business decisions. While they were already operating on a modern stack and not on the hunt for a modern data warehouse, BigQuery became an enticing option due to performance and a more optimal pricing model. Crux also found advantages with lower-cost ingestion and processing engines like Dataflow that allow for streaming analytics.… when it came to building a centralized large-scale data cloud, we needed to invest in a solution that would not only suit our current data storage needs but also enable us to tackle what’s coming, supporting a massive ecosystem of data delivery and operations for thousands of companies.
Mark Etherington
Chief Technology Office, Crux Informatics
Technology is a team sport, and Crux found our support team responsive and ready to help. This decision to more deeply adopt Google Cloud’s data analytics offerings provides Crux with the flexibility to manage a constantly evolving data ecosystem and stay competitive.
You can read more about Crux’s decision to adopt BigQuery in this blog.
Google Trends: A classic emerges a champion
Following up on the launch of our Google Trends dataset in June, we delivered some examples of how to use that data to augment your decision making.
As a quick recap of that dataset, Google Cloud, and in particular BigQuery, provide access to the top 25 trending terms by Nielsen’s Designated Market Area® (DMA) with a weekly granularity. These trending terms are based on search patterns and have historically only been available on the Google Trends website.https://www.youtube.com/embed/9FJAXMF0ASc?enablejsapi=1&
The Google Trends design pattern addresses some common business needs, such as identifying what’s trending geographically near your stores and how to match trending terms to products to identify potential campaigns.
Dataflow GPU: More power than ever for those streaming sprints
Dataflow is our fully-managed data processing platform that supports both batch and streaming workloads. The ability of Dataflow to scale and easily manage unbounded data has made it the streaming solution of choice for large workloads with high-speed needs in Google Cloud.
But what if we could take that speed and provide even more processing power for advanced use cases? Our team, in partnership with NVIDIA, did just that by adding GPU support to Dataflow. This allows our customers to easily accelerate compute-intensive processing like image analysis and predictive forecasting with amazing increases in efficiency and speed.
Take a look at the times below:
Data Fusion: A play-by-play for data integration’s winning performance
Data Fusion provides Google Cloud customers with a single place to perform all kinds of data integration activities. Whether it’s ETL, ELT, or simply integrating with a cloud application, Data Fusion provides a clean UI and streamlined experience with deep integrations to other Google Cloud data systems. Check out our team’s review of this tool and the capabilities it can bring to your organization.
5542
Of your peers have already watched this video.
1:30 Minutes
The most insightful time you'll spend today!
Pega Systems Migrates SAP Servers to Google Cloud in Just 9 Weeks!
Pega Systems’ financial data on SAP environs were on a hosting platform that lacked agility. By moving nearly 30 SAP servers to Google Cloud in just 9 weeks, Pega Systems was able to unlock data and integrate BigQuery into SAP HANA to deliver personalization for clients and embark on an exciting journey with Google Cloud! Watch now.
Transforming Software Development Education with CourseMatix
1482
Of your peers have already read this article.
3:00 Minutes
The most insightful time you'll spend today!
As the world increasingly relies on software, businesses have struggled to find enough developer talent to build and maintain their applications. IDC estimates the global shortage of software developers reached 1.4 million in 2021 and expects that number to balloon to 4 million by 2025.
Higher education is a great starting point to solve the software development skills shortage. One institution — Spokane Community College — has made strides in enhancing student learning and serving more students with the right mix of software development and IT education while saving educators time.
Building a modern IT education platform
As a first step to improve computer science education and scale the number of students it could teach, Spokane Community College needed a platform that could help instructors more efficiently manage the teaching process. That’s where CourseMatix came in.
CourseMatix is an education platform that delivers the required tools, platforms, and frameworks such as automated grading, assignment tracking, and testing to save teachers time and enhance students’ learning.
CourseMatix is entirely made up of microservices, with significant compute demands because it creates and runs a unique dedicated environment for each student. The platform is complex by nature as it supports a range of technologies that computer science professors need to teach varying coursework. This led to issues with scalability, reliability, and uptime when running on legacy infrastructure.
“We really wanted to no longer manage our infrastructure, leaving us to focus entirely on our platform,” says Aleks Korn, chief executive officer of CourseMatix. “We engaged Google Cloud partner CDW to see how we could simplify and improve our IT environment. The decision was quickly made to migrate to Google Kubernetes Engine (GKE).”
GKE provides CourseMatix with automatic horizontal and vertical scaling to minimize costs and downtime, while removing management and maintenance requirements. This enables CourseMatix to expand the reach of its platform at the lowest possible price and maintain a lean team. CDW helped CourseMatix make this decision to migrate to GKE, managed the migration, and provided guidance on a storage solution.
Because CourseMatix has high data storage volume requirements (given the size of software development assignments) and needs to be fully reliable to ensure work is not lost, it also chose to work with NetApp. NetApp Cloud Volumes Service for Google Cloud perfectly complements GKE in its highly expandable, reliable, and user-friendly capabilities. The platform was now ready for its U.S. launch at Spokane Community College.
Transforming software development education
Spokane Community College needed a solution to address the challenges facing its professors, particularly with respect to time management.
“Each software development assignment can take an hour to grade,” says Bret Dickey, professor of software development at Spokane Community College. “When you have 100 students, you’re talking weeks of turnaround time to grade those projects. CourseMatix provides immediate feedback, which is a game changer for us as professors and our students.”
Students now complete and submit their assignments with real-world tools, using source control, creating branches to complete their work, and merging those branches to push up to the server where pipelines run the applications.
“It’s making our teaching better overall, including using real-world concepts to enhance teaching and outcomes,” says Dickey.
CourseMatix also provides the tooling to upskill on various programming languages and software packages required for coursework, helping Spokane Community College manage server-side frameworks and client-side languages that each have their own tools.
“Students can access their cloud-based coding environment and all the tools they may need for an assignment are ready to go,” says Karmen Blake, another software development professor at Spokane Community College. “Having a central system for tooling within CourseMatix has been a huge time saver, meaning more time for learning and practice that prepares students for careers as professional software developers.”
Karmen and Dickey have seen marked improvements in their ability to teach and in student learning by using CourseMatix. For example, the platform provides automated hints and varying error messages based on the number of attempts a student has made to solve a problem to help each student along without making the assignment too easy or too difficult.
As a cloud-based platform, CourseMatix also empowers students from a wide range of socioeconomic backgrounds to learn computer science.
“As long as students can access the internet on their laptops, they have everything to complete their assignments,” says Karmen. “The cloud-based CourseMatix offering improves efficiency and equity across all students in our computer science programs.”
With a more fluid experience for students and immense amounts of time saved on grading, Spokane Community College is in a better position to increase enrollment in software development classes.
“Now that we have automated our grading and optimized our assignment processes, I am beginning to take on more students and teach more classes,” says Dickey. “I could not have imagined this outcome only a couple of years ago, and it’s all possible thanks to CourseMatix.”
To learn more about how partners help organizations make the most of Google Cloud, you can visit our partner page.
Turning the Tide: How PrestaShop Regained Trust in Data
4280
Of your peers have already read this article.
3:30 Minutes
The most insightful time you'll spend today!
Since 2007, PrestaShop has helped companies unlock the power of e-commerce through its open-source platform. Over 300,000 merchants worldwide use the PrestaShop platform to grow their business and serve online shoppers.
“Our open-source strategy to ecommerce enablement sets us apart,” says Rémi Paulin, Ph.D., Data Architect at PrestaShop. “Customization is becoming more crucial to retailers, and our open-source platform allows companies to continually evolve their sites and services to stand out from competitors.”
As PrestaShop grew, it wished to derive more value from its data, but the company ran into issues caused by a legacy, siloed architecture that negatively impacted data consistency and accessibility.
Let’s look at how PrestaShop works with Google Cloud and partners Fivetran and Hightouch to gain more control over data, enable a beyond-BI data strategy, and increase employee engagement from less than 10% to more than 40%.
Improving trust in data
Core systems at PrestaShop, including SQL and NoSQL databases, and SaaS Applications, were siloed; each presenting its own data, often captured from different sources such as support tickets, marketing engagement, purchase activity, and product usage. This setup made data overall inconsistent as no single system would contain a source of truth, resulting in many inefficiencies, poor collaboration across teams, and a reluctance to use data to support key decisions.
“Not long ago, less than 10% of the company regularly relied on data, so we were missing opportunities to make more data-driven decisions,” says Paulin. “Data was underutilized, and people were rapidly losing trust in data.”
PrestaShop set out to design a new architecture to address past challenges, such as lack of data consistency, and improve data accessibility.
“Google Cloud, along with Hightouch and Fivetran, allowed us to build a modern stack to solve these challenges and support our beyond-BI data strategy.”
Building a modern data stack
The first step was to build a robust data ingestion pipeline. After considering several vendors, PrestaShop chose to work with Fivetran to extract data from SaaS applications, including Zendesk, HubSpot, and GitHub, to load into BigQuery. They also use Datastream to stream Change Data Capture (CDC) data from transactional databases into BigQuery in real-time.
“Fivetran and Datastream are no-ops, efficient and highly reliable, and relieve our Data Engineers of management tasks. This brings us a high degree of confidence to build the rest of the stack atop these services,” says Paulin.
PrestaShop relies on several Google Cloud solutions, including Dataflow, and a managed Spark service by Ascend.io, for data transformation. It also uses Looker for its semantic modeling capacities and as a self-serve data platform.
As the company continued on its journey to transform how it manages and benefits from data, it engaged Hightouch to enable data accessibility through activation. Sitting on top of Looker, Hightouch unlocks all data models for operational intelligence. For example, in just a few days, the team built a customer knowledge model combining data from multiple sources and used Hightouch to sync data from the semantic layer to Zendesk via Reverse ETL. This allowed the care team to make more data-informed decisions, speeding up the time to resolve support tickets submitted through Zendesk by 33%.
“Hightouch feels like a natural extension of Looker and reinforces the position of the semantic data model as the single source of truth,” says Paulin. “It powers a variety of Data Activation use cases, supporting our beyond-BI strategy by providing teams with access to data when and where they need it to improve everyday operations. This has a big impact on the company, bolstering employee trust in available data.”
Becoming data-driven
In less than six months, PrestaShop managed to get the entire data stack up and running, build over 30 data models and engage over 120 employees with a small team of only two Data Engineers.
“Data is now accessible to every stakeholder within the company, regardless of their technical abilities,” says Paulin.
PrestaShop has already seen much progress in its shift to a more data-driven company and is excited to roll out more self-service intelligence capabilities in the future.
“Google Cloud drives home a culture of simplicity around our data stack, which is essential for us, especially given the small size of our engineering team,” says Paulin. “Fivetran and Hightouch share this culture of simplicity. Together, they offer strong foundations to support our data needs.”
Dashboards, which the company had always had an appetite for, are seamlessly created today. Before moving to Looker, a full-fledged dashboard would take an average of six weeks to develop. Now, it takes less than two days – and a simple dashboard can be created autonomously by business users in as little as 15 minutes.
Furthermore, data usage goes beyond dashboards. Thanks to Looker’s self-service exploration capabilities, many stakeholders can now glean insights surrounding product issues and business opportunities. Thanks to Hightouch, teams can activate their data to make better and smarter operational decisions.
“This is a big leap forward and one of many to come as we continue to add new models, activate our data, and onboard more users,” says Paulin. “Given our global reach and unique approach to e-commerce enablement, we know this is just the start of the great things we can accomplish with Google Cloud, Fivetran, and Hightouch.”
Check out Fivetran on Google Cloud Marketplace, or sign up for a free Hightouch workspace to learn more about what partners can do for your business.
More Relevant Stories for Your Company
Real-time analytics for on-time delivery: Mercado Libre
Iteration and innovation fuel the data-driven culture at Mercado Libre. In our first post, we presented our continuous intelligence approach, which leverages BigQuery and Looker to create a data ecosystem on which people can build their own models and processes. Using this framework, the Shipping Operations team was able to
What’s Google Cloud Firestore Database and What are it’s Benefits for Business and Developers?
Cloud Firestore is a NoSQL document database that simplifies storing, syncing, and querying data for your mobile and web apps at global scale. Cloud Firestore is a fast, fully managed, serverless, cloud-native NoSQL document database that simplifies storing, syncing, and querying data for your mobile, web, and IoT apps at
BigQuery Now Supports Multi-statement Transactions
Mercury, the Roman god of commerce, is often depicted carrying a purse, symbolic of business transactions, wearing winged sandals, illustrating his abilities to move at great speeds. Transactions power the world’s business systems today, ranging from millions of packages moving worldwide tracked in real time by logistics companies to global
How One Company Uses AI and Data Analysis to Boost Revenue
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
