Reference patterns

This page provides links to sample code and technical reference guides for common BigQuery use cases. Use these resources to learn, identify best practices, and leverage sample code to build the features that you need.

The reference patterns listed here are code-oriented and meant to get you quickly to implementation. To see a broader range of BigQuery solutions, review the list of BigQuery technical reference guides.

Anomaly detection

Solution Description Links
Building a Telecom network anomaly detection application using k-means clustering

This solution shows you how to build an ML-based network anomaly detection application for telecom networks to identify cyber security threats by using Dataflow, BigQuery ML and Cloud Data Loss Prevention.

Technical reference guide: Building a secure anomaly detection solution using Dataflow, BigQuery ML, and Cloud Data Loss Prevention

Sample code: Anomaly Detection in Netflow logs

Blog post: Anomaly detection using streaming analytics and AI

Overview video: Building a Secure Anomaly Detection Solution

Finding anomalies in financial transactions in real time using BoostedTrees

Use this reference implementation to learn how to write transaction data to BigQuery for analysis after getting predictions on it from a TensorFlow boosted tree model.

Technical reference guide: Detecting anomalies in financial transactions by using AI Platform, Dataflow, and BigQuery

Sample code: Anomaly Detection in Financial Transactions

General analytics

Solution Description Links
Building a pipeline to transcribe and analyze speech files

Learn how to transcribe and analyze uploaded speech files, then save that data to BigQuery for use in visualizations.

Sample code: Speech Analysis Framework

Log analytics

Solution Description Links
Capture Dialogflow interactions for analysis in BigQuery

Learn how to capture and store Dialogflow interactions in BigQuery for further analysis.

Sample code: Dialogflow log parser

Processing logs at scale using Dataflow and BigQuery

Learn to build analytical pipelines that process log entries from multiple sources, then combine the log data in ways that help you extract meaningful information.

Technical reference guide: Processing Logs at Scale Using Dataflow

Sample code: Processing Logs at Scale Using Dataflow

Pattern recognition

Solution Description Links
Detecting objects in video clips

This solution shows you how to build a real-time video clip analytics solution for object tracking, allowing you to process large volumes of unstructured data in near real time and then write it to BigQuery for analysis.

Sample code: Video Analytics Solution Using Dataflow and the Video Intelligence API

Apache Beam Ptransform for calling Video Intelligence API: apache_beam.ml.gcp.videointelligenceml module

Processing user-generated content using the Video Intelligence API and the Cloud Vision API This set of solutions describes the architecture for deploying a scalable system to filter image and video submissions by using Cloud Vision API and Video Intelligence API, then write the data to BigQuery for analysis.

Architecture: Processing User-Generated Content Using the Video Intelligence API and the Cloud Vision API

Tutorial: Processing User-Generated Content Using the Video Intelligence API and the Cloud Vision API

Sample code: Processing User-generated Content Using the Video Intelligence API and the Cloud Vision API

Apache Beam Ptransform for calling Cloud Vision API: apache_beam.ml.gcp.visionml module

Anonymize (de-identify) and re-identify PII data in your smart analytics pipeline This series of solutions shows you how to use Dataflow, Cloud Data Loss Prevention, BigQuery, and Pub/Sub to de-identify and re-identify personally identifiable information (PII) in a sample dataset.

Technical reference guides:

Sample code: Migrate Sensitive Data in BigQuery Using Dataflow and Cloud Data Loss Prevention

Predictive forecasting

Solution Description Links
Building an e-commerce recommendation system by using BigQuery ML

Learn how to build a recommendation system by using BigQuery ML to generate product or service recommendations from customer data in BigQuery. Then, learn how to make that data available to other production systems by exporting it to Google Analytics 360 or Cloud Storage, or programmatically reading it from the BigQuery table.

Technical reference guide: Building an e-commerce recommendation system by using BigQuery ML

Notebook: bqml_matrix_factorization_retail_ecommerce.ipynb

Building a propensity to purchase solution by using BigQuery ML

Learn how to build and deploy a propensity to purchase model, use it to get predictions about customer purchasing behavior, and then build a pipeline to automate the workflow.

Technical reference guide: Predicting customer propensity to buy by using BigQuery ML and AI Platform

Sample code: How to build an end-to-end propensity to purchase solution using BigQuery ML and Kubeflow Pipelines

Blog post: How to build an end-to-end propensity to purchase solution using BigQuery ML and Kubeflow Pipelines

Building new audiences based on current customer lifetime value

Learn how to identify your most valuable current customers and then use them to develop similar audiences in Google Ads.

Sample code: Activate on LTV predictions