Versions 2.2.0 or Newer

Alation Cloud Service Applies to Alation Cloud Service instances of Alation

Customer Managed Applies to customer-managed instances of Alation

Query log ingestion (QLI) extracts and ingests the query history of a database and powers the lineage, popularity, top user, join and filter information in the catalog. You’ll be able to explore examples of ingested queries on schema and table catalog pages.

Query Log Ingestion (QLI) is available from connector version 2.0.2 or newer. The connector supports query history extraction from the system audit log table:

• System audit log table in Azure Databricks

• System audit log table in Databricks on AWS

• System audit log table in Databricks on GCP

Prerequisites

Before you can perform QLI from your data source in Alation, you will need to:

1. Enable the system.access schema:

   • Azure Databricks: Enable system tables

   • Databricks on AWS: Enable system tables

   • Databricks on GCP: Enable system tables

2. Configure audit log delivery on your Databricks account and workspace:

   • Azure Databricks: Configure log delivery

   • Databricks on AWS: Configure audit log delivery

   • Databricks on GCP: Configure audit log delivery

3. Enable verbose audit logging to capture additional events in the audit log table. Your logs will include the following additional events:

   • For notebooks: runCommand

   • For SQL warehouse: commandStart, commandFinish

   Alation requires these events to be available in the audit table to successfully extract and ingest query history:

   • Azure Databricks: Enable verbose audit logging

   • Databricks on AWS: Enable verbose audit logging

   • Databricks on GCP: Enable verbose audit logging

4. Grant Permissions for QLI to the Alation service account.

5. In Alation, ensure that you run metadata extraction (MDE) before running QLI. For information on MDE, refer to Configure Metadata Extraction.

QLI Query

Alation runs the following query on the system.access.audit table to retrieve query history:

SELECT request_event_time AS startAt,
  username,
  queryText,
  TIMESTAMPDIFF(SECOND, request_event_time, response_event_time) AS executionTime,
  statusCode,
  errorMessage,
  session_id
FROM (
  SELECT
    user_identity.email AS username,
    request_params.commandText AS queryText,
    event_time AS request_event_time,
    request_params.commandId
  FROM system.access.audit
  WHERE action_name = 'commandSubmit'
  AND request_params.commandText IS NOT NULL
  AND request_params.commandText <>  ''
  ) submitTable
  LEFT JOIN (
    SELECT
      response.status_code AS statusCode,
      response.error_message AS errorMessage,
      session_id,
      event_time AS response_event_time,
      request_params.commandId
    FROM system.access.audit
    WHERE action_name = 'commandFinish'
    ) finishTable
WHERE submitTable.commandId = finishTable.commandId
  AND submitTable.request_event_time >= "FROM_TIME"
  AND submitTable.request_event_time <="TO_TIME"
  AND finishTable.statusCode = 200
  AND finishTable.errorMessage IS NULL
UNION ALL
SELECT
  event_time AS startAt,
  user_identity.email AS username,
  request_params.commandText AS queryText,
  round(request_params.executionTime,0) AS executionTime,
  response.status_code AS statusCode,
  response.error_message AS errorMessage,
  session_id
FROM system.access.audit
WHERE action_name in ('runCommand')
  AND event_time >= "FROM_TIME"
  AND event_time <= "TO_TIME"
  AND request_params.commandText IS NOT NULL
  AND request_params.commandText <>  ''
  AND response.status_code = 200
  AND response.error_message IS NULL;

Performing QLI

You can either run QLI manually on demand or configure it to run automatically on a schedule.

Note

Alation supports a maximum SQL query length of 100K rows. There is also a 50 KB limitation on query file size. Queries with more than 100K rows or queries larger than 50 KB cannot be parsed and ingested by QLI jobs.

You configure QLI on the Query Log Ingestion tab of the data source settings page. Follow these steps:

• Test Access and Query History Size

• Preview Results (Optional)

• Run QLI

Test Access and Query History Size

Before you run QLI, you can validate that the user has access to the QLI tables and estimate the approximate size of the query history metadata. The size is estimated based on the average query volume of the last seven days.

To test the access and query history size:

Under the section of the user interface Step 1: Test access and query history size, click Test. A dialog box appears displaying the test progress. Upon successful validation, you’ll get a confirmation that the user has the required permissions to continue.

Preview Results (Optional)

Before running QLI, you can preview the queries to be ingested.

To preview queries:

1. Under the section Step 2: Preview Results (optional), enter the date range for which you want to generate a preview.

2. Click Preview.

3. Click View Results to view the generated report.

4. You can download the preview as a JSON file. Click Download Preview Results in the preview window to download it.

Run QLI

You can either run QLI manually on demand or configure it to run automatically on a schedule:

• Run QLI Manually

• Schedule QLI

Run QLI Manually

To run QLI on demand:

1. Under the section Step 3: Run QLI, specify the desired date range using the Date Range calendar widgets. You will need to specify the start date and the end date of the date range separately.

2. Click Import. This will initiate a QLI job.

3. You can monitor the status and history of your QLI jobs on the QLI Job History page. To open the QLI job history, click Go to QLI Job History.

Find more details about the QLI job history in View the QLI Job Status.

Schedule QLI

You can configure QLI to run automatically on a schedule that you select.

To schedule QLI:

1. Under the section Step 3: Run QLI, enable the Enable QLI Schedule toggle.

2. Specify values for the job recurrence and time. The values are set in your local time. The next QLI job for your data source will run on the schedule you have specified.

View the QLI Job Status

On the QLI Job History sub-tab, view the status of your QLI jobs.

To open the list of QLI jobs that were run on the data source, click the QLI Job History sub-tab on top of the page. The QLI jobs are displayed in the query log ingestion job status table.

A QLI job can have one of these statuses:

• Succeeded—The job was successful.

• Partial Success—The job was successful, but there are some warnings. For example, if Alation fails to ingest some objects, it skips them and proceeds to ingest other objects, with the QLI job resulting in partial success.

• Failed—The job failed due to errors.

For each of the QLI jobs listed in the ingestion job status table, you can view the detailed log messages. Click the status link or the View Details link to open a detailed report in a pop-up info box. If there were errors during QLI, the corresponding error messages are displayed in the Job errors table. Follow the instructions in the Hint column for troubleshooting tips.