Add AllocatedQueryCostByTime to the warehouse_query_history materialized view

[yegorski]

We're refactoring our monitoring dashboards to use the materialized views created in https://github.com/CodyAustinDavis/dbsql_sme/blob/main/Observability%20Dashboards%20and%20DBA%20Resources/Observability%20Lakeview%20Dashboard%20Templates/DBSQL%20Warehouse%20Advisor%20With%20Data%20Model/DBSQL%20Warehouse%20Advisor%20Data%20Model.sql

We're doing this so that our other query lookup code that sits on top is 1) DRY and 2) fast. (2) is the more important because right now the direct system.query.history lookups take up minutes to finish.

I was sad to discover that the AllocatedQueryCostByTime column is not part of warehouse_query_history. We end up still needing to write this query (coped from an older version of the dashboard here) below. This extra query takes a long time to run as well.

Anyway, hope this makes sense. Happy to chat yegor.ius@enigma.com

PS: I was trying (still am) to add these extra filters to the materialized view. The issue is that the filters use date params (e.g. here timestampdiff(SECOND, usage_start_time, :param_start_time) :: float AS seconds_before_start_range) which are not allowed in materialized view creation. Any way around this?

The specific filters that seem to be needed in order to add AllocatedQueryCostByTime are:

WITH
history AS (
  SELECT
    *,
    SUM(TotalResourceTimeUsedForAllocation) OVER (PARTITION BY warehouse_id) AS TotalUsedTimeInWarehouse,
    -- Remove listing commands in time - this are essentially "free"
    SUM(COALESCE(TotalResourceTimeUsedForAllocation, 0)) OVER (PARTITION BY statement_text) AS TotalStatementTextTimeUsed,
    -- for query level aggregates
    try_divide(
      TotalResourceTimeUsedForAllocation,
      TotalUsedTimeInWarehouse
    ) AS ProportionOfWarehouseTimeUsedByQuery -- Remove listing commands in time - this are essentially "free"
  FROM
    operations.warehouse_monitoring.warehouse_query_history
),
filtered_warehouse_usage AS (
  -- This is to properly handle attributing partial hours/seconds and match it to the seleted query window
  WITH trimmed_usage AS (
    SELECT
      -- Start Time Range Trimming
      timestampdiff(SECOND, usage_start_time, :param_start_time) :: float AS seconds_before_start_range,
      timestampdiff(SECOND, usage_start_time, usage_end_time) :: float AS full_start_range_length,
      full_start_range_length - seconds_before_start_range AS seconds_remaining_in_original_start_range,
      greatest(:param_start_time, usage_start_time) AS trimmed_start_time,
      -- End Time Range Trimming
      least(:param_end_time, usage_end_time) AS trimmed_end_time,
      timestampdiff(SECOND, :param_end_time, usage_end_time) :: float AS seconds_after_end_range,
      timestampdiff(SECOND, usage_start_time, usage_end_time) :: float AS full_end_range_length,
      full_end_range_length - seconds_after_end_range AS seconds_remaining_in_original_end_range,
      -- Calculate trim proportion to multiple DBUs by proportion of the full hour in the active window
      try_divide(
        CASE
          WHEN seconds_before_start_range > 0 THEN (
            full_start_range_length - seconds_before_start_range
          )
          ELSE NULL
        END,
        full_start_range_length
      ) AS StartTrimProportion,
      try_divide(
        CASE
          WHEN seconds_after_end_range > 0 THEN (
            full_start_range_length - seconds_after_end_range
          )
          ELSE NULL
        END,
        full_start_range_length
      ) AS EndTrimProportion,
      COALESCE(
        COALESCE(EndTrimProportion, StartTrimProportion),
        1
      ) AS TrimProportion,
      usage_start_time,
      usage_end_time,
      usage_quantity AS dbus,
      usage_metadata.warehouse_id AS warehouse_id
    FROM
      system.billing.usage
    WHERE
      usage_unit = 'DBU'
      AND (
        (
          usage_start_time BETWEEN :param_start_time :: timestamp
          AND :param_end_time :: timestamp
        )
        OR (
          usage_end_time BETWEEN :param_start_time :: timestamp
          AND :param_end_time :: timestamp
        )
      )
  )
  SELECT
    warehouse_id,
    SUM(dbus * TrimProportion) AS Total_Warehouse_Period_DBUs,
    SUM(
      dbus * TrimProportion * 0.546 :: float
    ) AS Total_Warehouse_Period_Dollars
  FROM
    trimmed_usage
  GROUP BY
    warehouse_id
),
/*
WAREHOUSE LEVEL SKEW / EFFICIENCY METRICS
-- Calculate the warehouse average task times to execution time to get a basedline to compare relative skew/efficiency across warehouse
NOTE: We do this because we do not yet have the "warehouses" table, which would give us the size/rough cores of the cluster, 
so we do NOT programmatically have the ability to know what a proper ratio is. If we knew the number of cores on the cluster, our skew ratio would look like this: 

SKEW RATIO = (TotalQueryTaskTime / NumberOfCores) / ExecutionTime = 1 in a perfect/avg scenario. This means that ALL cores were perfectly fully utilize during the entire execution
Ratio ~1 means that there is lots of paralleliszation (high tasks time per unit of time).
Ratio < 1 means that the exeuction time was not fully filled by task time across all cores. The smaller this number is, the more likely it is to be skewed. 
Should never really have a ratio > 1 (that would mean there were more total task time than cores could theoretically processs) when we KNOW the number of cores. 
-- Is the query getting the warehouse fully?
-- Good ratio (is .5 reasonable?)
-- Closer to zero is a problem (<0.05) -- only matters when queyies are large (filter num tasks / cores)

Now since we do NOTE automatically know the number of cores, we have to make this ratio relative and create a PROXY metric. 
To do that, we will take the average ratio of Total Task Time / Execution Time across the whole warehouse for ALL queries that had task time (exclude metadata operations)
Ideal Ratio = 1 = ((Task Time)/ Cores ) / Execution Time
So Execution Time = Task Time / Cores
So Cores = Task Time / Execution Time in a perfect / avg scenario. 

This is NOT perfect, but helps as a proxy for now. Since this metric is to identify relatively inefficient/skewed queryes, it should work well enough. 

*/
warehouse_average_ratio_metrics AS (
  SELECT
    warehouse_id,
    try_divide(
      AVG(CPUTotalExecutionTime) :: float,
      AVG(QueryRuntimeSeconds) :: float
    ) :: float AS WarehouseAvgTaskSkewRatio,
    try_divide(
      MAX(CPUTotalExecutionTime),
      MAX(QueryRuntimeSeconds)
    ) :: float AS WarehouseMaxTaskSkewRatio,
    AVG(ReadDataAmountInGB) :: float AS WarehouseAvgReadDataInGBPerQuery,
    stddev(ReadDataAmountInGB) :: float AS WarehouseStdReadDataInGBPerQuery,
    AVG(read_io_cache_percent) :: float AS WarehouseAvgDeltaCachePercent,
    stddev(read_io_cache_percent) :: float AS WarehouseStdDeltaCachePercent,
    AVG(spilled_local_bytes) :: float AS WarehouseAvgDiskSpillAmount,
    stddev(spilled_local_bytes) :: float AS WarehouseStdDiskSpillAmount
  FROM
    history
  WHERE
    CPUTotalExecutionTime > 0
    AND ReadDataAmountInGB > 0 -- Means that the query read data as well as used cpu task time in workers
    AND execution_status NOT IN ("FAILED")
  GROUP BY
    warehouse_id
)
SELECT
  qh.*,
  w.WarehouseAvgTaskSkewRatio AS WarehouseAvgTaskToExecutionTimeRatio,
  -- For the query efficiency ratio, we invert the above ratio to use the warehouse proxy metric
  CASE
    WHEN CPUTotalExecutionTime > 0
    AND ReadDataAmountInGB > 0 -- Means that the query read data as well as used cpu task time in workers
    AND execution_status NOT IN ("FAILED") THEN try_divide(
      QueryRuntimeSeconds,
      try_divide(
        qh.CPUTotalExecutionTime,
        w.WarehouseAvgTaskSkewRatio
      )
    )
    ELSE NULL
  END AS QueryEfficiencyRatio,
  -- WarehouseSkewRatio is the proxy for number of cores/level of parallelism in the cluster
  w.WarehouseAvgReadDataInGBPerQuery,
  w.WarehouseStdReadDataInGBPerQuery,
  w.WarehouseAvgDeltaCachePercent,
  w.WarehouseStdDeltaCachePercent,
  w.WarehouseAvgDiskSpillAmount,
  w.WarehouseStdDiskSpillAmount,
  u.Total_Warehouse_Period_Dollars,
  (
    u.Total_Warehouse_Period_Dollars :: float * ProportionOfWarehouseTimeUsedByQuery :: float
  ) AS AllocatedQueryCostByTime
FROM
  history AS qh
  LEFT JOIN warehouse_average_ratio_metrics AS w ON w.warehouse_id = qh.warehouse_id
  LEFT JOIN filtered_warehouse_usage AS u ON u.warehouse_id = qh.warehouse_id

[yegorski]

Actually ignore the SQL file I provided. It's redundant to your new Query History With Ratios graph that uses the warehouse_query_history materialized view. But my point still stands -- would be great to have cost number in the view so that that graph doesn't have to compute it on every load.