BigQuery Jobs Explorer: Track And Troubleshoot Job Queries

BigQuery jobs explorer

Jobs Explorer

BigQuery jobs explorer: Your go-to place for tracking and resolving issues with BigQuery tasks

Have you ever been so overwhelmed by the sheer volume of SQL queries that are active in your company? The day-to-day tasks include identifying costly queries, monitoring who is executing them, and identifying error spikes. To keep the system functioning well and performing well, effective query activity management and monitoring are crucial.

Google Cloud is presenting BigQuery Jobs Explorer, your one-stop shop for all query-related needs. BigQuery jobs explorer, which is now widely accessible, facilitates troubleshooting and maximizes resource utilization while giving you detailed insight into your organization’s query activity.

Explorer Jobs

Using a single tool, address several problems

With the help of the flexible BigQuery Jobs Explorer, you can handle a variety of use cases on a single platform.

Gain an overview of every inquiry activity

A thorough, up-to-date picture of every SQL activity taking place within your company is offered by Jobs Explorer. You can see everything in one pane of glass, including what’s happening, when, and where, so there’s no need to piece together information from disparate sources.

• Monitoring in real time: Keep tabs on the state, development, and utilization of resources of any given task.
• Quick overview of key metrics: Utilize metrics such as TotalSlotMS, bytes processed, and others to sort and examine traffic.
• Visualize the execution of queries: Perceptive graphs facilitate the understanding of query performance trends.

You are able to anticipate problems, recognize patterns, and allocate resources wisely when you have this degree of visibility.

Troubleshooting: Identifying and fixing issues fast

Jobs Explorer makes it easy to identify the source of an issue quickly when something goes wrong.

• No further difficult questions: Get essential work details without having to create INFORMATION_SCHEMA queries.
• Strong filtering and sorting: Easily reduce the number of jobs by owner, project, status, priority, and other factors.
• Do something: To reduce expenses and recover resources, eliminate erroneous queries straight from the Jobs Explorer.
• Detailed exploration of the inquiry details: To view an execution graph and further important execution details, click on any job.
• Jobs Explorer streamlines the troubleshooting process so you can concentrate on maintaining the seamless operation of your BigQuery environment.

Optimize: Boost output and keep expenses in control

Jobs Explorer is a proactive tool for BigQuery optimization, not merely for problem-solving.

• Find the origins of performance bottlenecks: Identify the queries that are using too many resources or taking too long to finish.
• Query performance insights: Locate and utilize BigQuery-tagged queries to get useful performance information.
• Control expenses by recognizing and resolving ineffective queries to prevent overpaying. Most of your optimization gains are often attributed to a tiny percentage of your queries!

Jobs Explorer helps you maximize your BigQuery expenditure by providing you with a more comprehensive knowledge of your query activity.

Make use of the administrative jobs explorer

The administrative jobs explorer is a tool that BigQuery administrators can use to keep an eye on job activity within their organization. A variety of filters and sorting options are also available in Jobs Explorer to help troubleshoot and discover problematic jobs. With Jobs Explorer, you can access job details like owner, project, slot utilization, duration, and more without having to make INFORMATION_SCHEMA queries. This eliminates the requirement for you to have a thorough understanding of INFORMATION_SCHEMA.

BigQuery uses the following INFORMATION_SCHEMA views to deliver job details and insights:

• INFORMATION_SCHEMA.JOBS_BY_PROJECT
• INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
• INFORMATION_SCHEMA.JOBS_BY_USER

Essential roles

Ask your administrator to grant you the BigQuery Resource Viewer (roles/bigquery.resourceViewer) IAM role on your project or organization so that you may obtain the permissions required to use Administrative Jobs Explorer. See Manage access to projects, folders, and organizations for further details on assigning responsibilities.

The permissions needed to use Administrative Jobs Explorer are contained in this preset role. Expand the Required permissions section to view the precise permissions needed:

Permissions that are necessary

With custom roles or other preset roles, you might also be able to obtain certain permissions.

Sort jobs

Use the following steps to filter jobs for queries found in the INFORMATION_SCHEMA.JOBS* views:

1. Navigate to the BigQuery page under the Google Cloud console.
2. Choose Jobs Explorer under Administration in the navigation panel.
3. Choose the location for which you wish to browse the jobs from the Location list.
4. As necessary, use the optional filters:
   • Job scope: like the ongoing project, the company, and your employment.
   • Status: finished, erroneous, active, queued, etc.
   • Job priority. like batch or interactive tasks.
   • Job ID.
   • Owner. The job owner’s email address (relevant only if the task scope involves a project or organization).
   • Project ID. (Applicable only when the organization is the job scope.)
   • Reservation ID. (Applicable only when the task involves an organization.)
   • Slot time more than. tasks that took longer than the allotted time window.
   • Duration more than. projects that took longer than anticipated.
   • Bytes processed more than. Jobs that processed a greater number of bytes than the allotted amount.
   • Query insights of the following kinds: data input scale change, memory shuffle capacity exceeded, and slot contention.
   • Query Hash. The query hashes are contained in the query hash. It is a hexadecimal STRING hash that does not take literals, UDFs, comments, or parameter values into account. When a GoogleSQL query runs successfully and returns no cache hits, this column is displayed.
   • Labels: key:value pairs that are work-assignable. A key, value, or key:value combination can be used to filter.

View query execution information

Follow these steps to examine the query execution details of jobs:

• Navigate to Jobs Explorer.
• Click Jobs Explorer to view jobs.
• Sort the jobs to see only a select few.
• To view the query execution information, click the task that you wish to view.
• To view the execution details of your job, click the Execution graph tab in the Query results pane.

Pricing

There is no extra charge to use Jobs Explorer. The queries that are used to generate these charts don’t require user-owned reservation slots and aren’t invoiced. Over processing queries result in timeouts.

Next, what?

This is only the beginning using BigQuery Jobs Explorer. It pledge to keep enhancing and extending its capabilities to satisfy your changing requirements.