Referencia de la tabla del sistema de optimización predictiva

En este artículo, se describe el esquema de la tabla del historial de operaciones de optimización predictiva y se proporcionan consultas de ejemplo. La optimización predictiva optimiza el diseño de los datos para lograr un rendimiento máximo y una eficiencia de costos. La tabla del sistema realiza un seguimiento del historial de operaciones de esta característica. Para obtener información sobre la optimización predictiva, consulte Optimización predictiva para tablas administradas de Unity Catalog.

Ruta de acceso de tabla: esta tabla del sistema se encuentra en system.storage.predictive_optimization_operations_history.

Consideraciones de entrega

• Los datos pueden tardar hasta 24 horas en rellenarse.
• La optimización predictiva puede ejecutar varias operaciones en el mismo clúster. Si es así, se estima el recurso compartido de DBU que se atribuye a cada una de las varias operaciones. Este es el motivo por el que usage_unit se establece en ESTIMATED_DBU. Sin embargo, el número total de DBU usados en el clúster será preciso.

Esquema de tabla de optimización predictiva

La tabla del sistema del historial de operaciones de optimización predictiva usa el esquema siguiente:

Métricas de operación

Las métricas registradas en la operation_metrics columna varían en función del tipo de operación:

• COMPACTION: number_of_compacted_files, amount_of_data_compacted_bytes, number_of_output_files, amount_of_output_data_bytes
• VACUUM: number_of_deleted_files, amount_of_data_deleted_bytes
• ANALYZE: amount_of_scanned_bytes, number_of_scanned_files, staleness_percentage_reduced
• CLUSTERING: number_of_removed_files, number_of_clustered_files, amount_of_data_removed_bytes, amount_of_clustered_data_bytes
• AUTO_CLUSTERING_COLUMN_SELECTION: old_clustering_columns, new_clustering_columns, has_column_selection_changed, additional_reason (incluye el motivo por el que la selección de columnas tiene o no ha cambiado)

Consultas de ejemplo

En las secciones siguientes se incluyen consultas de ejemplo que puede usar para obtener información de la tabla del sistema de optimización predictiva. Para que estas consultas funcionen, debes reemplazar los valores del parámetro por tus propios valores.

En este artículo se incluyen las siguientes consultas de ejemplo:

• ¿Cuántas DBU estimadas ha usado la optimización predictiva en los últimos 30 días?
• ¿En qué tablas invirtió más la optimización predictiva en los últimos 30 días (estimación de costos)?
• ¿En qué tablas la optimización predictiva realiza la mayoría de las operaciones?
• Para un catálogo determinado, ¿cuál es el total de bytes que se ha compactado?
• ¿En qué tablas se quitaron la mayor cantidad de bytes?
• ¿Cuál es la tasa de éxito de las operaciones ejecutadas por las optimizaciones predictivas?

¿Cuántas DBU estimadas tiene la optimización predictiva usada en los últimos 30 días?

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

Para buscar el mismo valor para una canalización de ETL específica, primero puede encontrar las tablas de esa canalización y, a continuación, buscar las 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;

¿En qué tablas la optimización predictiva pasó más tiempo en los últimos 30 días (coste estimado)?

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;

¿En qué tablas la optimización predictiva realiza la mayoría de las operaciones?

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;

Para un catálogo determinado, ¿cuál es el total de bytes que se ha compactado?

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;

¿En qué tablas se quitaron la mayor cantidad de bytes?

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;

¿Cuál es la tasa de éxito de las operaciones ejecutadas por las optimizaciones predictivas?

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;