预测优化系统表参考

本文概述预测优化操作历史记录表架构并提供示例查询。 预测优化可优化数据布局，实现最佳性能和成本效益。 系统表跟踪此功能的操作历史记录。 有关预测优化的信息，请参阅 Unity Catalog 托管表的预测性优化。

表路径：此系统表位于 system.storage.predictive_optimization_operations_history.

交付注意事项

• 预测优化系统表在两小时内更新。 但是，计费信息最多可能需要 24 小时才能填充数据。
• 预测优化可能在同一群集上运行多个操作。 如果是这样，则表示每个操作归因于每个操作的 DBU 共享是近似的。 这就是为什么 usage_unit 设置为 ESTIMATED_DBU 的原因。 不过，在群集上使用的 DBU 总数是准确的。

预测优化表架构

预测优化操作历史记录系统表使用以下架构：

操作指标

列中记录 operation_metrics 的指标因操作类型而异：

示例查询

以下部分包括可用于深入了解预测优化系统表的示例查询。 若要使这些查询正常工作，需要将参数值替换为自己的值。

本文包括以下示例查询：

• 估计过去 30 天内有多少 DBU 使用了预测优化？
• 在过去 30 天内，哪些表格上的预测优化花费最多（估计成本）？
• 预测优化在哪些表上执行的操作最多？
• 对于给定目录，总字节数压缩了多少？
• 哪些表清空的字节最多？
• 预测优化执行的操作成功率是多少？

估计过去 30 天内有多少 DBU 使用了预测优化？

SELECT SUM(usage_quantity)
  FROM system.storage.predictive_optimization_operations_history
  WHERE
    usage_unit = "ESTIMATED_DBU"
    AND timestampdiff(day, start_time, Now()) < 30;

若要查找特定 ETL 管道的相同值，可以先在该管道中找到表，然后搜索 DBU：

-- Find all full table names for the pipeline:
WITH pipeline_mapping AS (
  SELECT DISTINCT target_table_full_name AS target_table_name
  FROM system.access.table_lineage
  WHERE entity_type = 'PIPELINE' AND entity_id = :pipeline_id
)
-- Select all operations for any table in that pipeline:
SELECT SUM(usage_quantity)
  FROM system.storage.predictive_optimization_operations_history
  WHERE
    CONCAT_WS('.', catalog_name, schema_name, table_name)
      IN ( SELECT target_table_name FROM pipeline_mapping)
    AND usage_unit = "ESTIMATED_DBU"
    AND timestampdiff(day, start_time, Now()) < 30;

过去 30 天内哪些表的预测优化开支最多（估计成本）？

SELECT
  metastore_name,
  catalog_name,
  schema_name,
  table_name,
  SUM(usage_quantity) as totalDbus
FROM system.storage.predictive_optimization_operations_history
WHERE
  usage_unit = "ESTIMATED_DBU"
  AND timestampdiff(day, start_time, Now()) < 30
GROUP BY ALL
ORDER BY totalDbus DESC;

预测优化在哪些表上执行的操作最多？

SELECT
  metastore_name,
  catalog_name,
  schema_name,
  table_name,
  operation_type,
  COUNT(DISTINCT operation_id) as operations
FROM system.storage.predictive_optimization_operations_history
GROUP BY ALL
ORDER BY operations DESC;

对于给定目录，总字节数压缩了多少？

SELECT
  schema_name,
  table_name,
  SUM(operation_metrics["amount_of_data_compacted_bytes"]) as bytesCompacted
FROM system.storage.predictive_optimization_operations_history
WHERE
  metastore_name = :metastore_name
  AND catalog_name = :catalog_name
  AND operation_type = "COMPACTION"
GROUP BY ALL
ORDER BY bytesCompacted DESC;

哪些表清空的字节最多？

SELECT
  metastore_name,
  catalog_name,
  schema_name,
  table_name,
  SUM(operation_metrics["amount_of_data_deleted_bytes"]) as bytesVacuumed
FROM system.storage.predictive_optimization_operations_history
WHERE operation_type = "VACUUM"
GROUP BY ALL
ORDER BY bytesVacuumed DESC;

通过预测优化所执行的操作成功率是多少？

WITH operation_counts AS (
  SELECT
    COUNT(DISTINCT (CASE WHEN operation_status = "SUCCESSFUL" THEN operation_id END)) as successes,
    COUNT(DISTINCT operation_id) as total_operations
  FROM system.storage.predictive_optimization_operations_history
 )
SELECT successes / total_operations as success_rate
FROM operation_counts;