As customers grow their analytical workloads and footprint on BigQuery, their monitoring and management requirements evolve – they want to be able to manage their environments at scale, take action in context. They also desire capacity management capabilities to optimize their BigQuery environments. With our BigQuery Administrator Hub capabilities, customers can now better manage BigQuery at scale. Two key features of BigQuery Administrator Hub are Resource Charts and Slot Estimator which help administrators understand their BQ environments like never before.
Resource Charts empowers Administrators with a native out-of-the-box experience to monitor their slot usage, manage capacity based on historical consumption, troubleshoot job performance, and self diagnose queries, and take corrective action as needed. They provide visibility into key metrics such as slot consumption, job performance, concurrency, bytes processed and failed jobs. Resource Charts are built and rendered using INFORMATION_SCHEMA tables, enabling customers to understand data through these purpose-built dashboards or query the data directly to build their own dashboards and monitoring processes.
BigQuery customer Snap is an early adopter of Resource Charts; “Admin Resource Charts is a great tool that helps us understand how we’re consuming our slots and which workloads/queries are driving usage. It has provided us better visibility into our BigQuery environment”—Muthu Hariharasubramanian, Engineering Manager, BigData Infrastructure, Snap, Inc.
Slot Estimator is an interactive capacity management tool that helps administrators estimate and optimize their BigQuery capacity based on performance. This tool helps customers make informed decisions on capacity planning based on historical usage. It also helps customers to estimate and optimize their capacity based on their workloads and performance.
Paypal is a preview customer of Slot Estimator; “Slots Estimator is just amazing and a differentiator for BigQuery. Our trials with this feature have shown very good results in predicting slot requirements for critical analytical workloads.”—Bala Natarajan, Sr. Director of Data Infrastructure and Cloud Engineering, PayPal
Let’s look at a day in the life of an BigQuery Administrator and see how these various features can help you: When you come in the morning and log into the BigQuery UI, you will see Administrator Hub as the central home to understand, manage, and monitor your queries, capacity, and BQ environments.
As you are monitoring your environment real time in Resource Charts, you see a decline in slot usage a couple of hours later and decide to investigate further.
You can look at the new Errors chart and see a sharp increase in permission denied and invalid errors. You can investigate these errors further using filters such as projects, reservations, users and job priorities to understand what changed since morning and fix them so you utilize the slots efficiently
Later in the day, a Data Analyst comes to you when they notice that their jobs have been running slower gradually over the week. Using Resource Charts, you see that slot utilization has hit the max capacity. On further drill down, you learn that ramping up a new workflow has caused a steady increase in slots and all the slots are fully utilized constantly.
You can now switch over to the Slot Estimator tab and see a similar full slot utilization view at 100% and how the slot usage has ramped up over the week. You can look at the reservation data and analyze how much you can improve performance by adding different amounts of slots. Once you decide to add slots, you can directly buy slots for a specific reservation in-context.
Resource Charts is generally available and Slot Estimator is available in preview for customers using Reservations. We hope these Administration capabilities help you better monitor and manage your BigQuery workloads at scale!
By: Vidya Shanmugam (Product Manager) and Alice Zhu (Software Engineer)
Source: Google Cloud Blog