Référence sur la table système de l’historique des requêtes

Cet article contient des informations sur la table système de l’historique des requêtes, y compris un plan du schéma de la table.

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

Utilisation de la table de l’historique des requêtes

La table de l’historique des requêtes inclut les enregistrements des requêtes exécutées à l’aide d’entrepôts SQL ou de calcul serverless pour des notebooks et des tâches. La table inclut des enregistrements à l’échelle du compte de tous les espaces de travail de la région à partir de laquelle vous accédez à la table.

Par défaut, seuls les administrateurs ont accès à la table système. Si vous souhaitez partager les données de la table avec un utilisateur ou un groupe, Databricks recommande de créer une vue dynamique pour chaque utilisateur ou groupe. Consultez Créer une vue dynamique.

Schéma de la table système de l’historique des requêtes

La table d’historique des requêtes utilise le schéma suivant :

Afficher le profil de requête d’un enregistrement

Pour accéder au profil de requête d’une requête en fonction d’un enregistrement dans la table d’historique des requêtes, procédez comme suit :

1. Identifiez l’enregistrement souhaité, puis copiez l’enregistrement statement_id.
2. Référez-vous à l'enregistrement workspace_id pour vous assurer que vous êtes connecté au même espace de travail que cet enregistrement.
3. Cliquez sur Historique des requêtes dans la barre latérale de l’espace de travail.
4. Dans le champ ID d’instruction, collez le statement_id sur l’enregistrement.
5. Cliquez sur le nom d’une requête. Une vue d’ensemble des mesures de requête s’affiche.
6. Cliquez sur Afficher le profil de requête.

Compréhension de la colonne query_source

La query_source colonne contient un ensemble d’identificateurs uniques d’entités Azure Databricks impliquées dans l’exécution de l’instruction.

Si la query_source colonne contient plusieurs ID, cela signifie que l’exécution de l’instruction a été déclenchée par plusieurs entités. Par exemple, un résultat de travail peut déclencher une alerte qui appelle une requête SQL. Dans cet exemple, les trois ID seront renseignés dans query_source. Les valeurs de cette colonne ne sont pas triées par ordre d’exécution.

Les sources de requête possibles sont les suivantes :

• alert_id : instruction déclenchée à partir d’une alerte
• sql_query_id : instruction exécutée à partir de cette session d’éditeur SQL
• dashboard_id : instruction exécutée à partir d’un tableau de bord
• legacy_dashboard_id : instruction exécutée à partir d’un tableau de bord hérité
• genie_space_id : instruction exécutée à partir d’un espace Génie
• notebook_id : instruction exécutée à partir d’un notebook
• job_info.job_id : instruction exécutée dans un travail
• job_info.job_run_id : instruction exécutée à partir d’une exécution de tâche
• job_info.job_task_run_id : instruction exécutée dans une tâche de travail

Combinaisons valides de query_source

Les exemples suivants montrent comment la query_source colonne est remplie en fonction de la façon dont la requête est exécutée :

• Les requêtes exécutées au cours d'une exécution de tâche incluent un struct job_info rempli.

  {
alert_id: null,
sql_query_id: null,
dashboard_id: null,
notebook_id: null,
job_info: {
job_id: 64361233243479,
job_run_id: null,
job_task_run_id: 110378410199121
},
legacy_dashboard_id: null,
genie_space_id: null
}

• Les requêtes provenant de tableaux de bord hérités incluent un sql_query_id et un legacy_dashboard_id :

  {
alert_id: null,
sql_query_id: 7336ab80-1a3d-46d4-9c79-e27c45ce9a15,
dashboard_id: null,
notebook_id: null,
job_info: null,
legacy_dashboard_id: 1a735c96-4e9c-4370-8cd7-5814295d534c,
genie_space_id: null
}

• Les requêtes provenant d’alertes incluent un sql_query_id et alert_id:

  {
alert_id: e906c0c6-2bcc-473a-a5d7-f18b2aee6e34,
sql_query_id: 7336ab80-1a3d-46d4-9c79-e27c45ce9a15,
dashboard_id: null,
notebook_id: null,
job_info: null,
legacy_dashboard_id: null,
genie_space_id: null
}

• Les requêtes à partir de tableaux de bord incluent un dashboard_id, mais pas job_info:

  {
alert_id: null,
sql_query_id: null,
dashboard_id: 887406461287882,
notebook_id: null,
job_info: null,
legacy_dashboard_id: null,
genie_space_id: null
}

Matérialiser l’historique des requêtes à partir de votre metastore

Le code suivant peut être utilisé pour créer un travail s’exécutant toutes les heures, tous les jours ou chaque semaine pour matérialiser l’historique des requêtes à partir d’un metastore. Ajustez les variables HISTORY_TABLE_PATH et LOOKUP_PERIOD_DAYS en conséquence.

from delta.tables import *
from pyspark.sql.functions import *
from pyspark.sql.types import *

HISTORY_TABLE_PATH = "jacek.default.history"
# Adjust the lookup period according to your job schedule
LOOKUP_PERIOD_DAYS = 1

def table_exists(table_name):
    try:
        spark.sql(f"describe table {table_name}")
        return True
    except Exception:
        return False

def save_as_table(table_path, df, schema, pk_columns):
    deltaTable = (
        DeltaTable.createIfNotExists(spark)
        .tableName(table_path)
        .addColumns(schema)
        .execute()
    )

    merge_statement = " AND ".join([f"logs.{col}=newLogs.{col}" for col in pk_columns])

    result = (
        deltaTable.alias("logs")
        .merge(
            df.alias("newLogs"),
            f"{merge_statement}",
        )
        .whenNotMatchedInsertAll()
        .whenMatchedUpdateAll()
        .execute()
    )
    result.show()

def main():
    df = spark.read.table("system.query.history")
    if table_exists(HISTORY_TABLE_PATH):
        df = df.filter(f"update_time >= CURRENT_DATE() - INTERVAL {LOOKUP_PERIOD_DAYS} days")
    else:
        print(f"Table {HISTORY_TABLE_PATH} does not exist. Proceeding to copy the whole source table.")

    save_as_table(
        HISTORY_TABLE_PATH,
        df,
        df.schema,
        ["workspace_id", "statement_id"]
    )

main()

Analyser la popularité des tables, y compris les requêtes mises en cache

La table du système de lignage de table n’émet pas d’entrées pour les résultats de requête provenant du cache de résultats. En raison de cela, vous devez joindre la table d’historique des requêtes à la table système de traçabilité de table pour inclure des requêtes mises en cache dans votre analyse.

Par exemple, la requête suivante récupère les tables avec des requêtes dépassant une heure d’exécution au cours des 7 derniers jours :

SELECT
    t.source_table_full_name,
    COUNT(*) AS num_of_queries_over_hour
FROM
    system.query.history h
    INNER JOIN system.access.table_lineage t
        ON t.statement_id = h.cache_origin_statement_id
WHERE
    h.total_duration_ms > 3600000
    AND t.event_time > CURRENT_TIMESTAMP() - INTERVAL 7 DAYS
    AND t.source_table_full_name IS NOT NULL
GROUP BY t.source_table_full_name
ORDER BY 2 DESC;