Referência da tabela do sistema de histórico de consultas

Este artigo inclui informações sobre a tabela do sistema de histórico de consultas, incluindo um esboço do esquema da tabela.

Localização da tabela: Esta tabela do sistema está localizada em system.query.history.

Como usar a tabela de histórico de consultas

A tabela de histórico de consultas inclui registros de consultas executadas usando warehouses SQL ou computação sem servidor para notebooks e trabalhos. A tabela inclui registros em toda a conta de todos os espaços de trabalho na mesma região a partir da qual você acessa a tabela.

Por padrão, somente os administradores têm acesso à tabela do sistema. Se você quiser compartilhar os dados da tabela com um usuário ou grupo, o Databricks recomenda criar uma exibição dinâmica para cada usuário ou grupo. Confira Criar uma exibição dinâmica.

Esquema de tabela do sistema de histórico de consultas

A tabela de histórico de consultas usa o seguinte esquema:

Exibir o perfil de consulta para um registro

Para navegar até o perfil de consulta de uma consulta com base em um registro na tabela de histórico de consultas, faça o seguinte:

1. Identifique o registro de interesse e, em seguida, copie a parte do registro indicada por statement_id.
2. Referencie workspace_id do registro para garantir que você esteja conectado ao mesmo espaço de trabalho que o registro.
3. Clique no Histórico de consultas na barra lateral do workspace.
4. No campo ID da Declaração, cole o statement_id no registro.
5. Clique no nome de uma consulta. É exibida uma visão geral das métricas de consulta.
6. Clique em Exibir perfil de consulta.

Entender a coluna query_source

A query_source coluna contém um conjunto de identificadores exclusivos de entidades do Azure Databricks envolvidas na execução da instrução.

Se a query_source coluna contiver várias IDs, isso significa que a execução da instrução foi disparada por várias entidades. Por exemplo, um resultado de trabalho pode disparar um alerta que chama uma consulta SQL. Neste exemplo, todas as três IDs serão preenchidas dentro query_source. Os valores dessa coluna não são classificados por ordem de execução.

As possíveis fontes de consulta são:

• alert_id: instrução disparada por um alerta
• sql_query_id: Instrução executada de dentro desta sessão do editor do SQL
• dashboard_id: demonstrativo executado de um painel
• legacy_dashboard_id: demonstrativo executado de um painel herdado
• genie_space_id: demonstrativo executado de um espaço Genie
• notebook_id: instrução executada a partir de um notebook
• job_info.job_id: instrução executada em uma tarefa
• job_info.job_run_id: demonstrativo executado de uma execução de trabalho
• job_info.job_task_run_id: demonstrativo executado de uma execução de tarefa de trabalho

Combinações válidas de query_source

Os exemplos a seguir mostram como a query_source coluna é preenchida dependendo de como a consulta é executada:

• As consultas executadas durante uma execução de trabalho incluem um struct job_info preenchido:

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

• As consultas de painéis herdados incluem um sql_query_id e 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
}

• As consultas de alertas incluem um sql_query_id e 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
}

• As consultas de dados dos painéis incluem um dashboard_id, mas não um 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
}

Materializar o histórico de consultas do metastore

O código a seguir pode ser usado para criar uma tarefa executada a cada hora, diariamente ou semanalmente para materializar o histórico de consultas de um metastore. Ajuste as variáveis HISTORY_TABLE_PATH e LOOKUP_PERIOD_DAYS adequadamente.

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

Analisar a popularidade da tabela, incluindo consultas armazenadas em cache

A tabela do sistema de linhagem de tabelas não emite entradas para os resultados da consulta do cache de resultados. Por isso, você deve unir a tabela de histórico de consultas com a tabela do sistema de linhagem para incluir consultas armazenadas em cache na sua análise.

Por exemplo, a consulta a seguir recupera tabelas com consultas que excedem uma hora de tempo de execução nos últimos 7 dias:

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;