Référence de table système d’optimisation prédictive

Cet article décrit le schéma de la table d’historique des opérations d’optimisation prédictive et fournit des exemples de requêtes. L’optimisation prédictive optimise votre disposition de données pour obtenir des performances optimales et une bonne rentabilité. La table système suit l’historique des opérations de cette fonctionnalité. Pour plus d’informations sur l’optimisation prédictive, consultez Optimisation prédictive pour les tables managées Unity Catalog.

Chemin d’accès de la table : cette table système se trouve à l’emplacement system.storage.predictive_optimization_operations_history.

Considérations relatives à la livraison

• Les données peuvent prendre jusqu’à 24 heures pour être alimentées.
• L’optimisation prédictive peut exécuter plusieurs opérations sur le même cluster. Si c’est le cas, le partage des DBU attribuées à chacune des opérations multiples est approximatif. C’est pourquoi le usage_unit est défini sur ESTIMATED_DBU. Toutefois, le nombre total d’unités de DBU utilisées dans le cluster sera exact.

Schéma de table d’optimisation prédictive

La table système de l’historique des opérations d’optimisation prédictive utilise le schéma suivant :

Métriques d’opération

Les métriques enregistrées dans la operation_metrics colonne varient en fonction du type d’opération :

• 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 (inclut la raison pour laquelle la sélection de colonne a ou n’a pas changé)

Exemples de requêtes

Les sections suivantes incluent des exemples de requêtes que vous pouvez utiliser pour obtenir des insights sur la table système d’optimisation prédictive. Pour que ces requêtes fonctionnent, vous devez remplacer les valeurs de paramètre par vos propres valeurs.

Vous trouverez dans cet article des requêtes telles que :

• Combien de DBU estimés utilisent l’optimisation prédictive au cours des 30 derniers jours ?
• Sur quelles tables l'optimisation prédictive a-t-elle dépensé le plus au cours des 30 derniers jours (coût estimé) ?
• Sur quelles tables l’optimisation prédictive effectue-t-elle le plus d’opérations ?
• Pour un catalogue donné, combien d’octets totaux ont été compactés ?
• Quelles sont les tables pour lesquelles le plus grand nombre d’octets a été aspiré ?
• Quel est le taux de réussite des opérations exécutées par des optimisations prédictives ?

Combien de DBU estimés utilisent l’optimisation prédictive au cours des 30 derniers jours ?

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

Pour trouver la même valeur pour un pipeline ETL spécifique, vous pouvez d'abord identifier les tables de ce pipeline, puis rechercher les DBUs :

-- 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;

Sur quelles tables l’optimisation prédictive est-elle intervenue le plus souvent au cours des 30 derniers jours (coût estimé) ?

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;

Sur quelles tables l’optimisation prédictive effectue-t-elle le plus d’opérations ?

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;

Pour un catalogue donné, combien d’octets totaux ont été compactés ?

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;

Quelles sont les tables pour lesquelles le plus grand nombre d’octets a été aspiré ?

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;

Quel est le taux de réussite des opérations exécutées par des optimisations prédictives ?

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;