Automatic upgrades system table reference

Preview

This system table is in Public Preview.

This page describes the automatic upgrades operation history table schema and shows sample queries. Automatic upgrades automatically turn on best-practice features on your Unity Catalog managed tables without requiring code changes. The system table tracks the history of these operations. For information on automatic upgrades, see Automatic upgrades.

Table path: This system table is located at system.storage.table_auto_upgrade_operations_history.

Automatic upgrades table schema

The automatic upgrades operation history system table uses the following schema:

Column name	Data type	Description	Example
account_id	string	The ID of the account.	11e22ba4-87b9-4cc2-9770-d10b894b7118
metastore_name	string	The name of the metastore to which the upgraded table belongs.	metastore1
metastore_id	string	The ID of the metastore to which the upgraded table belongs.	5a31ba44-bbf4-4174-bf33-e1fa078e6765
catalog_name	string	The name of the catalog to which the upgraded table belongs.	catalog
schema_name	string	The name of the schema to which the upgraded table belongs.	schema
table_name	string	The name of the upgraded table.	table1
table_id	string	The ID of the upgraded table.	138ebb4b-3757-41bb-9e18-52b38d3d2836
feature_name	string	The table feature that automatic upgrades attempted to turn on, such as deletionVectors or rowTracking.	rowTracking
operation_id	string	The unique ID for the operation.	4dad1136-6a8f-418f-8234-6855cfaff18f
operation	string	The type of operation. Values include ENABLE or DISABLE.	ENABLE
operation_result	string	The result of an attempted upgrade operation. Values include SUCCESSFUL or FAILED: INTERNAL_ERROR. See operation_result values.	SUCCESSFUL
start_time	timestamp	The time at which the operation started. Timezone information is recorded at the end of the value with +00:00 representing UTC.	2023-01-09 10:00:00.000+00:00
end_time	timestamp	The time at which the operation ended. Timezone information is recorded at the end of the value with +00:00 representing UTC.	2023-01-09 11:00:00.000+00:00

operation_result values

The operation_result column shows whether an attempted upgrade operation succeeded or failed to turn on a feature, recorded in feature_name, on an eligible table. If failed (message FAILED: INTERNAL_ERROR), you can enable the feature manually.

Example queries

The following sections include sample queries you can use to gain insights into the automatic upgrades system table. You must replace the parameter values with your own values.

Query automatic upgrade operations for all features on a specific table

SQL

SELECT
  feature_name,
  operation_result,
  start_time,
  end_time
FROM system.storage.table_auto_upgrade_operations_history
WHERE
  catalog_name = :catalog_name
  AND schema_name = :schema_name
  AND table_name = :table_name
ORDER BY start_time DESC;

Query the number of automatic upgrade operations in the last 30 days

SQL

SELECT
  feature_name,
  operation_result,
  COUNT(DISTINCT operation_id) AS operations
FROM system.storage.table_auto_upgrade_operations_history
WHERE timestampdiff(day, start_time, NOW()) < 30
GROUP BY ALL
ORDER BY operations DESC;

Query the the success rate for automatic upgrades operations

SQL

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

Query for automatic upgrade operations that failed

SQL

SELECT
  catalog_name,
  schema_name,
  table_name,
  feature_name,
  start_time
FROM system.storage.table_auto_upgrade_operations_history
WHERE operation_result != "SUCCESSFUL"
ORDER BY start_time DESC;