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

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

Caminho da tabela: Esta tabela do sistema encontra-se em system.query.history.

Usando a tabela de histórico de consultas

A tabela de histórico de consultas inclui registos para consultas executadas usando SQL warehouses ou computação sem servidor para blocos de anotações e trabalhos . A tabela inclui registros de 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, apenas 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 a criação de uma exibição dinâmica para cada usuário ou grupo. Consulte Criar uma vista dinâmica.

Esquema da tabela do sistema do 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 registo de interesse e, em seguida, copie o statement_id.
2. Faça referência ao registro workspace_id 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 espaço de trabalho.
4. No campo ID da declaração, cole o statement_id no registro.
5. Clique no nome de uma consulta. Uma visão geral das métricas de consulta é exibida.
6. Clique em Ver perfil de consulta.

Noções básicas sobre query_source coluna

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ários IDs, isso significa que a execução da instrução foi acionada por várias entidades. Por exemplo, um resultado de trabalho pode disparar um alerta que chama uma consulta SQL. Neste exemplo, todos os três IDs serão preenchidos em query_source. Os valores desta coluna não são ordenados por ordem de execução.

As possíveis fontes de consulta são:

• alert_id: Declaração acionada a partir de um alerta
• sql_query_id: Instrução executada a partir desta sessão do editor SQL
• dashboard_id: Declaração executada a partir do painel de controlo
• legacy_dashboard_id: Declaração executada a partir de um dashboard legado
• genie_space_id: Declaração executada a partir de um espaço Genie
• notebook_id: Declaração executada a partir de um notebook
• job_info.job_id: instrução executada dentro de um trabalho
• job_info.job_run_id: instrução executada a partir de uma execução de uma tarefa
• job_info.job_task_run_id: instrução executada dentro de uma tarefa de trabalho executada

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 uma estrutura preenchida 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
}

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

• Consultas dos alertas incluem 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 dos painéis incluem um dashboard_id, mas não 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 a partir do seu metastore

O código a seguir pode ser usado para agendar uma tarefa para executar 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 de acordo.

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

Analise a popularidade da tabela, incluindo consultas em cache

A tabela de linhagem do sistema não gera registos para resultados de consulta do cache de resultados. Por isso, você deve unir a tabela de histórico de consultas com a tabela do sistema de linhagem de tabela para incluir consultas em cache em 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;