Configure Query Log Ingestion

Alation Cloud Service Applies to Alation Cloud Service instances of Alation

Customer Managed Applies to customer-managed instances of Alation

Query Log Ingestion (QLI) is a data job in Alation that processes database query logs to extract meaningful insights about database objects. QLI data is used to enrich catalog pages with key metrics such as:

  • Popularity

  • Top Users

  • Filter and JOIN information

Note

We recommend to have at least one week of query history for initial QLI.

QLI setup varies depending on the database type:

  • You may need to complete prerequisite steps on your database.

  • Required QLI parameters differ by database. For database-specific QLI instructions, refer to Adding Data Sources.

How QLI Works

Once metadata is extracted through MDE, QLI adds further insights to catalog objects by analyzing query logs.

During QLI, Alation:

  • Reads query logs from designated tables or views.

    Important

    Ensure the queries include qualified names to ingest joins and filters into Alation.

  • Parses SQL to determine:

    • Which users ran which queries

    • What objects were used, created, or updated

  • Calculates:

    • Top Users

    • Popularity

    • Lineage

    • JOIN and filter details

QLI is one of multiple pipelines used to calculate these metrics. Queries run in Compose also contribute.

../../_images/QLI.png

QLI Job Duration

To maintain performance:

  • A single QLI job can cover a maximum of 7 days.

  • For longer durations, submit multiple QLI jobs.

  • If a job exceeds 7 days, it will fail to run and show an error in the job submission UI and history page.

If you need an exception to avoid these restrictions, contact Alation Support.

Permissions

QLI permissions are specific to each data source. See the the prequisites of the data source for specific QLI permissions.

Configure QLI in Alation

Most QLI configuration options are available on the Query Log Ingestion tab of the data source settings. To configure and run QLI, you need access to the data source settings. You must have one of the following roles:

  • Server Admin

  • Data Source Admin

After adding the data source and performing metadata extraction (MDE), you can proceed with QLI. On the Query Log Ingestion tab of your data source, you can select the QLI options for your data source and schedule the QLI job if necessary.

../../_images/QLIUI_01.png

Table-Based QLI

Table-based QLI uses the view created from the external QLI table.

In the Table Name field under Connector Settings > Query Extraction, specify the name of the view in which the query logs are available. Make sure that the service account has permissions to select from this view. The table name format is specific to each data source, refer to QLI section of your data source, Adding Data Sources.

Custom Query-Based QLI

If you cannot create the view for QLI, for example, due to access restrictions, you can use a custom QLI query to extract query history into Alation. If you opt for custom query-based QLI, Alation will query the system table storing query history or the table you’ve created to enable QLI every time you manually run QLI or when the QLI job runs on schedule.

For custom query-based QLI to succeed, ensure that the service account has enough permissions to select from the QLI table.

The template for the QLI query is given below. You can customize it by adding, removing, or changing the filter, but the columns and their aliases must remain as is since the connector expects this query structure.

Note

When using the QLI query template, do not substitute the STARTTIME and ENDTIME parameters in the WHERE filter. These parameters are not actual column names and should stay as is. They are expected by the connector and will be substituted with the start and end date of the QLI range selected in the user interface when QLI is run manually or on schedule.

To configure query-based QLI:

  1. Go to the Query Log Ingestion tab of the Settings page of your OCF data source.

  2. Under Connector Settings > Query Extraction, in the Custom QLI Query field, provide the QLI query.

  3. Click Save.

Automated and Manual QLI

You can either perform QLI manually on demand or enable automated QLI:

  1. To perform manual QLI, under the Automated and Manual Query Log Ingestion section of the Query Log Ingestion tab, ensure that the Enable Automated Query Log Ingestion toggle is disabled.

    Note

    Metadata extraction must be completed first before running QLI.

  2. Click Preview to get a sample of the query history data to be ingested.

  3. Click the Import button to perform QLI on demand.

  4. To schedule QLI, enable the Enable Automated Query Log Ingestion toggle.

  5. Set a schedule under Automated Query Log Ingestion Time by specifying values in the week, day, and time fields. The next QLI job will run on the schedule you have specified.

    Note

    The hourly schedule for automated QLI is not supported.

QLI Job Status

The status of preview or QLI jobs will be logged in the Query Log Ingestion Job Status table at the bottom of the page. In this table, click the status link or the View Details link for a job to view the details on the progress of this job.