Versions Before 1.7.0

Alation Cloud Service Applies to Alation Cloud Service instances of Alation

Customer Managed Applies to customer-managed instances of Alation

Important

This section is applicable to all Alation versions and Amazon Redshift OCF Connector versions prior to 1.7.0.

You can either create a table for Alation to pull the query logs from or use a custom query to query the logs from the Amazon Redshift data source.

Before performing query log ingestion (QLI), perform the QLI setup. Refer to Redshift QLI Setup.

Note

Alation doesn’t ingest query statements with more than 100,000 characters. If the character count of a query exceeds this limit, the query string will be automatically truncated to 100,000 characters before it is ingested into the catalog.

Connector Settings

Table-Based QLI

In the Table Name field, provide the name of the table in which the query logs are available. The table name must be provided in the following format: schema.table.

Custom Query-Based QLI

When you cannot create a table or view, you can use a Custom QLI Query to perform QLI. Provide the expected query structure as shown below and click Save:

SELECT
    user_name AS username,
    to_char(start_time, 'YYYY-MM-DD HH:MI:SS.US') AS startTime,
    text AS queryString,
    session_id AS sessionId,
    seconds_taken AS seconds,
    'false' AS cancelled,
    default_database AS defaultDatabases,
    split_part(session_id, '/', 2) AS sessionStartTime,
    seq
FROM
    public.alation_qlog
WHERE
  starttime BETWEEN STARTTIME AND ENDTIME
  AND queryString IS NOT NULL
  AND queryString <> ''
  AND username != 'rdsdb'
  AND canceled = ''
ORDER BY startTime, username, seq;

Automated and Manual Query Log Ingestion

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.