Direct access to Databricks query history database

[WWoman]

Hello,

I would like to know if there is direct access to the Databricks query history tables. For compliance issues, I would like to be able to create reports  for something like: who has accessed a particular column in a table in the past 6 months. The query history web interface is quite limited.  I would ideally like wo use SQL to query the history table. Is this possible?

[Kaniz]

Hi @WWoman, To address your compliance requirements and track who has accessed specific columns in a table over the past 6 months, you can follow these steps:

1. Databricks Table Access Control (TAC):

• Databricks provides an audit and control feature called Table Access Control (TAC). TAC allows you to monitor and manage access to tables within Databricks.
• By enabling TAC, you can audit table access and view logs related to table usage. These logs are stored in the “DatabricksTableAccessControl” log table.
• To set up TAC, follow these steps:
  • Create or use an existing Databricks workspace.
  • In the workspace, navigate to the “Admin Console” and click on the “Permissions” tab.
  • Enable Table Access Control and configure it according to your requirements.
  • Define rules specifying which users, groups, or roles have access to specific tables.
  • Once TAC is enabled, you’ll be able to see detailed logs related to table access, including who acce...¹.

2. Query History:
   • Databricks also maintains a query history that records executed SQL queries.
   • To access the query history:
     • Click on “Query History” in the sidebar of your Databricks workspace.
     • Optionally, you can sort the list by duration or other criteria.
     • Click on the name of a query to view details such as the SQL command and execution information.
     • You can filter the list by user, date range, SQL warehouse, and query status²³.

Remember that these features provide valuable insights into user activity, but they should be used responsibly and in accordance with your organization’s policies.

[WWoman]

I would like to be able to query the query history tables by running my own queries. I do not want to use the Query History interface supplied by Databricks;  I want to be able to create python scripts that access the underling tables/views for TAC and Query history.   From your response, it seems like this is not possible. Can you confirm that?

[josh_melton]

%pip install databricks-sdk
dbutils.library.restartPython()

warehouse_id = "bc1af43449227761"
hours_back_to_check = 2

from databricks.sdk import WorkspaceClient
from databricks.sdk.service import sql
from pyspark.sql.types import StructType, StructField, StringType, LongType
import time

w = WorkspaceClient()
current_time_ms = int(round(time.time() * 1000))
start_time = current_time_ms - (3600000 * hours_back_to_check)

# Filtering options:
# https://databricks-sdk-py.readthedocs.io/en/latest/dbdataclasses/sql.html#databricks.sdk.service.sql.QueryFilter
query_filter = sql.QueryFilter(
    query_start_time_range=sql.TimeRange(start_time_ms=start_time, end_time_ms=current_time_ms),
    warehouse_ids=[warehouse_id]
)
query_ls = [query for query in w.query_history.list(filter_by=query_filter, include_metrics=True)]

schema = StructType([
    StructField("duration", LongType(), True),
    StructField("query_start_time_ms", LongType(), True),
    StructField("query_end_time_ms", LongType(), True),
    StructField("executed_as_user_name", StringType(), True),
    StructField("query_text", StringType(), True),
])

# Extract the relevant fields from the BaseRun objects
df = spark.createDataFrame([
    (query.duration, query.query_start_time_ms, query.query_end_time_ms, query.executed_as_user_name, query.query_text) 
    for query in query_ls
    ], schema)
df.display()

print([query.metrics for query in query_ls])

[josh_melton]

This provides query history. You may also be interested in System Tables - for compliance purposes check out Audit Logs:

https://docs.databricks.com/en/administration-guide/system-tables/audit-logs.html

[josh_melton]

For posterity, there is a query history system table which contains all of this information which is in preview at the time of me writing this. If you're reading this later than May 2024, please check the documentation for the query metrics table.

[WWoman]

A quick question on this... (First of all thanks so much for the sample code!). I'm playing around with this and I would like to get the statement_type and status. I see that duration, query_start_time_ms and query_end_time_ms are int data type and defines as LongType(); executed_as_user_name and query_text are str datatype and defined as StringType. statement_type and status are listed as data types QueryStatementType and QueryStatus respectively. How would I define the StructType for these fields?

[ReiskaS]

Thanks @josh_melton ! I was wondering right now about this (one day after your post!) since I only found the UI and API in the documentation and was really puzzled that there is no equivalent in unity to the Snowflake query_history table.