Referencia de la tabla del sistema del historial de consultas

En este artículo se incluye información sobre la tabla del sistema del historial de consultas, incluido un esquema del esquema de la tabla.

Ruta de acceso de tabla: esta tabla del sistema se encuentra en system.query.history.

Uso de la tabla del historial de consultas

La tabla de historial de consultas incluye registros para las consultas que se ejecutan mediante almacenes SQL o proceso sin servidor para cuadernos y trabajos. La tabla incluye registros de toda la cuenta de todas las áreas de trabajo de la misma región desde la que se accede a la tabla.

De manera predeterminada, solo los administradores tienen acceso a la tabla del sistema. Si desea compartir los datos de la tabla con un usuario o grupo, Databricks recomienda crear una vista dinámica para cada usuario o grupo. Consulte Creación de una vista dinámica.

Esquema de la tabla del sistema del historial de consultas

La tabla del historial de consultas usa el esquema siguiente:

Visualización del perfil de consulta de un registro

Para ir al perfil de consulta de una consulta en función de un registro de la tabla del historial de consultas, haga lo siguiente:

1. Identifique el registro de interés y, a continuación, copie el statement_id del registro.
2. Consulte el identificador del registro workspace_id para asegurarse de que ha iniciado sesión en el mismo entorno de trabajo que el registro.
3. Haga clic en Historial de consultas en la barra lateral del área de trabajo.
4. En el campo Id. de instrucción, pegue el statement_id en el registro.
5. Haga clic en el nombre de una consulta. Aparece información general de las métricas de consulta.
6. Haga clic en Ver perfil de consulta.

Comprender la columna query_source

La query_source columna contiene un conjunto de identificadores únicos de las entidades de Azure Databricks implicadas en la ejecución de la instrucción.

Si la query_source columna contiene varios identificadores, significa que la ejecución de la instrucción se desencadenó mediante varias entidades. Por ejemplo, un resultado de trabajo puede desencadenar una alerta que llama a una consulta SQL. En este ejemplo, los tres identificadores se rellenarán dentro de query_source. Los valores de esta columna no se ordenan por orden de ejecución.

Los posibles orígenes de consulta son:

• alert_id: instrucción activada desde una alerta
• sql_query_id: instrucción ejecutada desde esta sesión del editor de SQL
• dashboard_id: instrucción ejecutada desde un panel
• legacy_dashboard_id: instrucción ejecutada desde un panel heredado
• genie_space_id: instrucción ejecutada desde un espacio de Genie
• notebook_id: instrucción ejecutada desde un cuaderno
• job_info.job_id: instrucción ejecutada dentro de un trabajo
• job_info.job_run_id: instrucción ejecutada desde una ejecución de trabajo
• job_info.job_task_run_id: instrucción ejecutada dentro de una ejecución de tarea de trabajo

Combinaciones válidas de query_source

En los ejemplos siguientes se muestra cómo se rellena la query_source columna en función de cómo se ejecuta la consulta:

• Las consultas ejecutadas durante una ejecución de trabajo incluyen una estructura rellenada job_info :

  {
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
}

• Las consultas de los paneles heredados incluyen un sql_query_id y 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
}

• Las consultas de alertas incluyen un sql_query_id y un 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
}

• Las consultas de los paneles incluyen dashboard_id, pero no 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
}

Materialización del historial de consultas desde el metastore

El código siguiente se puede usar para crear un trabajo que se ejecuta cada hora, diariamente, o semanalmente para materializar el historial de consultas desde un metastore. Ajuste las variables HISTORY_TABLE_PATH y LOOKUP_PERIOD_DAYS en consecuencia.

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()

Análisis de la popularidad de las tablas, incluidas las consultas almacenadas en caché

La tabla del sistema de linaje de tablas no emite entradas para los resultados de la consulta de la caché de resultados. Por este motivo, debe combinar la tabla del historial de consultas con la tabla del sistema de linaje de tablas para incluir consultas almacenadas en caché en el análisis.

Por ejemplo, la consulta siguiente recupera tablas con consultas que superan una hora de tiempo de ejecución en los últimos 7 días:

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;