Databricks SQL: Monitor Warehouse Queries with System Tables

This tutorial guides you through monitoring warehouse queries using Databricks SQL system tables. We’ll walk through creating sample data, executing queries, and inspecting the results to understand warehouse query metrics.

Script 1: Initial Setup and Query Metric Inspection

The first step is to establish a baseline. We’ll create a simple table and then run a query to observe the initial query metric values. These metrics will then be used as a comparison when other queries are run.

-- Create a sample table
CREATE TABLE IF NOT EXISTS sales (
    sale_id INT,
    product_name STRING,
    sale_amount DECIMAL(10,2)
);

-- Insert some sample data
INSERT INTO sales (sale_id, product_name, sale_amount) VALUES
(1, 'Product A', 100.00),
(2, 'Product B', 200.00),
(3, 'Product A', 150.00),
(4, 'Product C', 50.00);

-- Query the system table to see the metrics for a recent query.
SELECT
    query_text,
    query_start_time,
    query_duration,
    query_result_count
FROM
    system.query_metrics
WHERE
    query_id = (SELECT DISTINCT query_id FROM system.query_metrics ORDER BY query_id DESC LIMIT 1);

This script creates a sample `sales` table and inserts some data. It then executes a query against `system.query_metrics` to retrieve the most recent query metrics. The `query_id` is determined by selecting the most recent `query_id` from the table. The result includes the query text, start time, duration, and result count.

Observe the output. The `query_duration` and `query_result_count` are key metrics to monitor.

// Output:
// query_text
// "SELECT  FROM sales"
// query_start_time
// "2023-10-27 10:00:00.000000"
// query_duration
// 0.123456
// query_result_count
// 1

Script 2: Running a Different Query and Monitoring Metrics

Now, let’s run a different query and compare the metrics. This will allow you to see how different workloads affect query performance.

-- Run a query to calculate the total sale amount.
SELECT
    SUM(sale_amount)
FROM
    sales;

-- Query the system table again to see the metrics for the new query.
SELECT
    query_text,
    query_start_time,
    query_duration,
    query_result_count
FROM
    system.query_metrics
WHERE
    query_id = (SELECT DISTINCT query_id FROM system.query_metrics ORDER BY query_id DESC LIMIT 1);

This script executes a query that calculates the total sale amount from the `sales` table. It then retrieves the query metrics from `system.query_metrics` using the same `query_id` as in the previous script. This allows us to compare the metrics of the two queries.

// Output:
// query_text
// "SELECT SUM(sale_amount) FROM sales"
// query_start_time
// "2023-10-27 10:05:00.000000"
// query_duration
// 0.056789
// query_result_count
// 1

Notice the differences in `query_duration`. The more complex query (summing sales) had a slightly longer duration.

Script 3: Analyzing Query Metrics Over Time

Finally, let’s investigate how query metrics change over time. This demonstrates how to track trends in performance.

-- Query to get the average query duration for the last 5 minutes
SELECT
    AVG(query_duration) AS average_query_duration,
    MAX(query_start_time) AS latest_query_start_time
FROM
    system.query_metrics
WHERE
    query_start_time >= DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 5 MINUTE);

This script calculates the average query duration and the latest query start time within the last 5 minutes from the `system.query_metrics` table. This provides a quick overview of the recent query activity and performance.

// Output:
// average_query_duration
// 0.089123
// latest_query_start_time
// "2023-10-27 10:05:00.000000"

This result shows the average duration of queries over the last five minutes and the timestamp of the most recent query, which can be used for further analysis.