Why do we need a BigQuery anti-pattern recognition tool?

BigQuery is a serverless and cost-effective enterprise data warehouse that works across cloud environments and scales with your data. As with any large scale data-intensive platform, following best practices and avoiding inefficient anti-patterns goes a long way in terms of performance and cost savings. 

Usually SQL optimization requires a significant time investment from engineers, who must read high-complexity queries, devise a variety of approaches to improve performance and efficiency, and test several optimization techniques. The best place to start is to fix anti-patterns, since this only requires easily applicable changes and provides significant performance improvements.

To facilitate the task of identifying and fixing said anti-pattern, Google Professional Services Organization (PSO) and Global Services Delivery (GSD) have developed a BigQuery anti-pattern recognition tool. This tool automates the process of scanning SQL queries, identifying antipatterns, and providing optimization recommendations.

What is the BigQuery anti-pattern recognition tool?

The BigQuery anti-pattern recognition tool let you easily identify performance impacting anti-patterns across a large number of SQL queries in a single go. 

It utilizes ZetaSQL to parse BigQuery SQL queries into abstract syntax trees (AST) and then traverses the tree nodes to detect the presence of anti-patterns. 

The tool takes a BigQuery SQL query as an input, such as:

SELECT
 t.dim1,
 t.metric1
FROM
 `dataset.table` t
WHERE
 t.id NOT IN (
 SELECT
   id
 FROM
   `dataset.table2`)

And produces the output as:

Subquery in filter without aggregation at line 8.

It examines potential optimizations, including:

• Selecting only the necessary columns
• Handling multiple WITH-clause references 
• Addressing subqueries in filters with aggregations
• Optimizing ORDER BY queries with LIMIT
• Enhancing string comparisons
• Improving JOIN patterns
• Avoiding subquery aggregation in the WHERE clause

The solution supports reading from various sources, such as:

• Command line 
• Local files
• Cloud Storage files
• Local folders
• Cloud Storage folders
• CSV (with one query per line)
• INFORMATION_SCHEMA

Additionally, the solution provides flexibility in writing output to different destinations, including:

• Printing to the terminal
• Exporting as CSV

Writing to a BigQuery table

Using the BigQuery anti-pattern recognition tool

The BigQuery anti-pattern recognition tool is hosted on GitHub. Below are the Quick Start steps on using the tool via command line for inline queries. You can also leverage Cloud Run to deploy it as a container on cloud. 

Prerequisites

• Linux OS
• JDK 11 or above is installed
• Maven
• Docker
• gcloud CLI

Quick start: – steps

1. Clone the repo into your local machine.

git clone git@github.com:GoogleCloudPlatform/bigquery-antipattern-recognition.git

2. Build the tool image inside the `bigquery-antipattern-recognition` folder.

mvn clean package jib:dockerBuild -DskipTests

3. Run the tool for a simple inline query.

docker run \
  -i bigquery-antipattern-recognition \
  --query "SELECT * FROM \`project.dataset.table1\`"

4. Below is the output result in the command-line interface:

Additionally, the above tool can read queries from Information Schema and load the output recommendations to a BigQuery table.

Below is an example of the BigQuery anti-pattern recognition tool results exported to a BigQuery table.

Getting started

Ready to start optimizing your BigQuery queries and cutting costs? Check out the tool here and contribute to the tool via GitHub.

Have questions or feedback?

We’re actively working on new features to make the tool as useful to our customers. Use it and tell us what you think! For product feedback/technical questions, reach out to us at bq-antipattern-eng@google.com. If you’re already a BigQuery customer and would like a briefing on the tool, please reach out, we’d be happy to talk.

With unemployment application submissions reaching record numbers over the past year, state and local agencies in the United States have faced the challenge of processing unprecedented numbers of claims per week. The digital infrastructure most agencies have in place is unable to handle this volume, resulting in constituents waiting longer, and bad actors taking advantage of vulnerable systems. The Department of Labor Inspector General estimates that $63 billion in claims distributed is either an improper payment or fraud. 

Validating claims also requires secure data sharing with other agencies for document and identity verification. Government leaders need a way to allow case adjudicators to quickly and confidently release backlogged claims, integrate with existing systems, and segment legitimate claims from potentially fraudulent ones — all within limited government budgets — securely and at scale.

Implementing a fraud detection solution on Google Cloud

States were under pressure to release payments, while also filtering out potentially fraudulent claims. SpringML and Google Cloud developed a framework to give adjudicators a reliable verification process that quickly filters potentially fraudulent claims, while processing the remaining claims so benefits reach citizens in a timely manner. SpringML and Google Cloud, applied AI-powered machine learning models to detect anomalous patterns in large datasets. Using Google Cloud tools, SpringML implemented a solution to streamline workflows, improve efficiencies, automate processes and identify potentially fraudulent claims.

SpringML used a variety of Google Cloud products to deliver a fraud detection solution, including:

• Google Cloud Storage to store and manage data
• BigQuery to store tabular data and BigQuery Machine Learning (BQML) to conduct machine learning on that data
• AutoML solutions to build predictive models and risk scoring
• Visualization tools such as Looker and Data Studio to present data and help government leaders make informed decisions.

Implementing machine learning to detect improper payments allows agencies to classify claims as “fraud” or “not fraud” based on the number of flags, as well as prioritize the most urgent claims. Deploying intelligent virtual agents to handle frequently asked questions meant that live agents could focus their time on more challenging cases. 

Even once the pandemic is behind us, there will be bad actors trying to take advantage of overwhelmed or legacy systems. We’ve identified a few best practices for agencies managing enormous case loads and looking to improve improper payment analytics: 

• Move your systems to the cloud. Many on-premises legacy systems can’t update their applications and scale to meet the volume of claims. Moving to a cloud environment enables rapid solution deployment and ingestion of large amounts of data without fear of overloading the system. The cloud scales with you–cost-effectively and securely. 
• Understand patterns in the data. The answer is always in the data — we used deep analysis to help uncover suspicious patterns in large data sets. We implemented unsupervised machine learning to learn behaviors and create configurable rules that adjust to new information that comes into the system. We can uncover patterns that are likely associated with fraud – ones that a human might have missed. 
• Use AI/ML tools to automate your existing systems and teams. These tools enable humans to work smarter and more efficiently. We automate anomaly detection and create dashboards for adjudicators to rapidly process claims. We are enabling the Wisconsin Department of Workforce Development by implementing automatic calculations and processing of recharge amounts, resulting in faster processing times and fewer human errors. Proactive fraud detection and timely calculation of recharge payment allowed DWD to ensure the benefits reached the right individuals.
• Build flexibility into your systems. We discovered that fraud patterns change over time. For instance,flags for fraud during March-May 2020 were vastly different from those we found in June-July 2020. Google Cloud tools make it easy to continually update algorithms to detect patterns and integrate external data sources.

Using Google Cloud tools, we can update digital infrastructure and incorporate machine learning best practices to help organizations efficiently process large volumes of claims and identify high probability fraudulent ones. SpringML provides consulting and implementation services and industry-specific analytics solutions that deliver high-impact business value to accelerate data-driven digital transformation. Learn more about fraud detection and how to improve improper payments analytics by watching our webinar. 

Want to know the latest from Google Cloud? Find it here in one handy location. Check back regularly for our newest updates, announcements, resources, events, learning opportunities, and more. 

Tip: Not sure where to find what you’re looking for on the Google Cloud blog? Start here: Google Cloud blog 101: Full list of topics, links, and resources.

Week of May 24-May 28 2021

• Google Cloud for financial services: driving your transformation cloud journey–As we welcome the industry to our Financial Services Summit, we’re sharing more on how Google Cloud accelerates a financial organization’s digital transformation through app and infrastructure modernization, data democratization, people connections, and trusted transactions. Read more or watch the summit on demand.
• Introducing Datashare solution for financial services–We announced the general availability of Datashare for financial services, a new Google Cloud solution that brings together the entire capital markets ecosystem—data publishers and data consumers—to exchange market data securely and easily. Read more.
• Announcing Datastream in Preview–Datastream, a serverless change data capture (CDC) and replication service, allows enterprises 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. Read more.
• Introducing Dataplex: An intelligent data fabric for analytics at scale–Dataplex provides a way to centrally manage, monitor, and govern your data across data lakes, data warehouses and data marts, and make this data securely accessible to a variety of analytics and data science tools. Read more. 
• Announcing Dataflow Prime–Available in Preview in Q3 2021, Dataflow Prime is a new platform based on a serverless, no-ops, auto-tuning architecture built to bring unparalleled resource utilization and radical operational simplicity to big data processing. Dataflow Prime builds on Dataflow and brings new user benefits with innovations in resource utilization and distributed diagnostics. The new capabilities in Dataflow significantly reduce the time spent on infrastructure sizing and tuning tasks, as well as time spent diagnosing data freshness problems. Read more.
• Secure and scalable sharing for data and analytics with Analytics Hub–With Analytics Hub, available in Preview in Q3, organizations get a rich data ecosystem by publishing and subscribing to analytics-ready datasets; control and monitoring over how their data is being used; a self-service way to access valuable and trusted data assets; and an easy way to monetize their data assets without the overhead of building and managing the infrastructure. Read more.
• Cloud Spanner trims entry cost by 90%–Coming soon to Preview, granular instance sizing in Spanner lets organizations run workloads at as low as 1/10th the cost of regular instances, equating to approximately $65/month. Read more.
• Cloud Bigtable lifts SLA and adds new security features for regulated industries–Bigtable instances with a multi-cluster routing policy across 3 or more regions are now covered by a 99.999% monthly uptime percentage under the new SLA. In addition, new Data Access audit logs can help determine whether sensitive customer information has been accessed in the event of a security incident, and if so, when, and by whom. Read more.
• Build a no-code journaling app–In honor of Mental Health Awareness Month, Google Cloud’s no-code application development platform, AppSheet, demonstrates how you can build a journaling app complete with titles, time stamps, mood entries, and more. Learn how with this blog and video here.
• New features in Security Command Center—On May 24th, Security Command Center Premium launched the general availability of granular access controls at project- and folder-level and Center for Internet Security (CIS) 1.1 benchmarks for Google Cloud Platform Foundation. These new capabilities enable organizations to improve their security posture and efficiently manage risk for their Google Cloud environment. Learn more.
• Simplified API operations with AI–Google Cloud’s API management platform Apigee applies Google’s industry leading ML and AI to your API metadata. Understand how it works with anomaly detection here.
• This week: Data Cloud and Financial Services Summits–Our Google Cloud Summit series begins this week with the Data Cloud Summit on Wednesday May 26 (Global). At this half-day event, you’ll learn how leading companies like PayPal, Workday, Equifax, and many others are driving competitive differentiation using Google Cloud technologies to build their data clouds and transform data into value that drives innovation. The following day, Thursday May 27 (Global & EMEA) at the Financial Services Summit, discover how Google Cloud is helping financial institutions such as PayPal, Global Payments, HSBC, Credit Suisse, AXA Switzerland and more unlock new possibilities and accelerate business through innovation. Read more and explore the entire summit series.
• Announcing the Google for Games Developer Summit 2021 on July 12th-13th–With a surge of new gamers and an increase in time spent playing games in the last year, it’s more important than ever for game developers to delight and engage players. To help developers with this opportunity, the games teams at Google are back to announce the return of the Google for Games Developer Summit 2021 on July 12th-13th. Hear from experts across Google about new game solutions they’re building to make it easier for you to continue creating great games, connecting with players and scaling your business. Registration is free and open to all game developers. Register for the free online event at g.co/gamedevsummit to get more details in the coming weeks. We can’t wait to share our latest innovations with the developer community. Learn more.

97 zettabytes was the estimated volume of data generated worldwide in 2022¹. This sort of explosion in data volume is happening in every enterprise. Now imagine being able to access all this data you own from anywhere, at any time, analyze it, and leverage its insights to innovate your products and services? One of the biggest barriers to fulfilling this vision is the complexity inherent in dealing with data trapped across silos in an enterprise. Google Data Cloud offers a unified, open, and intelligent platform for innovation, allowing you to integrate your data on a common architectural platform. Across industries, organizations are able to reimagine their data possibilities in entirely new ways and quickly build applications that delight their customers.

Siloed data: The barrier to speed and innovation

Digital technologies ranging from transaction processing to analytics and AI/ML use data to help enterprises understand their customers better. And the pace of innovation has naturally accelerated as organizations learn, adapt, and race to build the next generation of applications and services to compete for customers and meet their needs. At Next 2022, we made a prediction that the barriers between transactional and analytical workloads will mostly disappear.

Traditionally, data architectures have separated transactional and analytical systems—and that’s for good reason. Transactional databases are optimized for fast reads and writes, and analytical databases are optimized for analyzing and aggregating large data sets. This has siloed enterprise data systems, leaving many IT teams struggling to piece together solutions. The result has been time consuming, expensive, and complicated fixes to support intelligent, data-driven applications. 

But, with the introduction of new technologies, a more time-efficient and cost-effective approach is possible. Customers now expect to see personalized recommendations and tailored experiences from their applications. With hybrid systems that support both transactional and analytical processing on the same data, without impacting performance, these systems now work together to generate timely, actionable insights that can be used to create better experiences and accelerate business outcomes.   

According to a 2022 research paper from IDC, unifying data across silos via a data cloud is the foundational capability enterprises need to gain new insights on rapidly changing conditions and to enable operational intelligence. The modern data cloud provides unified, connected, scalable, secure, extensible, and open data, analytics, and AI/ML services. In this platform, everything is connected to everything else.

Why reducing data barriers delivers more value 

The primary benefit of a unified data cloud is that it provides an intuitive and timely way to represent data and allows easy access to related data points. By unifying their data, enterprises are able to: 

1. Ingest data faster – for operational intelligence
2. Unify data across silos – for new insights
3. Share data with partners – for collaborative problem solving
4. Change forecasting models – to understand and prepare for shifting markets
5. Iterate with decision-making scenarios – to ensure agile responses
6. Train models on historical data – to build smarter applications

As generative AI applications akin to Bard, an early experiment by Google, become available in the workplace, it will be more important than ever for organizations to have a unified data landscape to holistically train and validate their proprietary large language models. 

With these benefits enterprises can accelerate their digital transformation in order to thrive in our increasingly complex digital environment. A survey of more than 800 IT leaders indicated that using a data cloud enabled them to significantly improve employee productivity, operational efficiency, innovation, and customer experience, among others. 

Build modern apps with a unified and integrated data cloud

Google Cloud technologies and capabilities reduce the friction between transactional and analytical workloads and make it easier for developers to build applications, and to glean real-time insights. Here are a few examples. 

1. AlloyDB for PostgreSQL, a fully managed PostgreSQL-compatible database, and AlloyDB Omni, the recently launched downloadable edition of AlloyDB, can analyze transactional data in real time. AlloyDB is more than four times faster for transactional workloads and up to a 100 times faster for analytical queries compared to standard PostgreSQL, according to our performance tests. This kind of performance makes AlloyDB the ideal database for hybrid transactional and analytical processing (HTAP) workloads.
2. Datastream for BigQuery, a serverless change data capture and replication service, provides simple and easy real time data replication from transactional databases like AlloyDB, PostgreSQL, MySQL and Oracle directly into BigQuery, Google Cloud’s enterprise data warehouse.
3. And, query federation with Cloud Spanner, Cloud SQL, and Cloud Bigtable, make data available right from the BigQuery console allowing customers to analyze data in real-time in transactional databases. 

Speed up deployments and lower costs

By reducing data barriers, we’re taking a fundamentally different approach that allows organizations to be more innovative, efficient, and customer-focused by providing: 

• Built-in industry leading AI and ML that helps organizations not only build improved insights, but also automate core business processes and enable deep ML-driven product innovation. 
• Best-in-class flexibility. Integration with open source standards and APIs ensures portability and extensibility to prevent lock-in. Plus, choice of deployment options means easy interoperability with existing solutions and investments.  
• The most unified data platform with the ability to manage every stage of the data lifecycle, from running operational databases to managing analytics applications across data warehouses and lakes to rich data-driven experiences. 
• Fully managed database services that free up DBA/DevOps time to focus on high-value work that is more profitable to the business. Organizations that switch from self-managed databases eliminate manual work, realize significant cost savings, reduce risk from security breaches and downtime and increase productivity and innovation. In an IDC survey, for example, Cloud SQL customers achieve an average three-year ROI of 246% because of the value and efficiencies this fully managed service delivers. 

Google Data Cloud improves the efficiency and productivity of your teams, resulting in increased innovation across your organization. This is the unified, open approach to data-driven transformation bringing unmatched speed, scale, security, and with AI built in. 

In case you missed it: Check out our Data Cloud and AI Summit that happened on March 29th, to learn more about the latest innovations across databases, data analytics, BI and AI. In addition, learn more about the value of managed database services like Cloud SQL in this IDC study.

^{1. How Big is Big? – Statista}