Versions Prior to 1.3.0

Alation Cloud Service Applies to Alation Cloud Service instances of Alation

Customer Managed Applies to customer-managed instances of Alation

You can choose to create a table or a view on your database to store the query history data or to provide a custom query to retrieve the query history data.

Table-Based QLI

Use the query format to create a new view and flush the query history from the data source. You can create the view in a schema of your choice. In the query below, substitute the placeholder value <schema_name.view_name> with an actual value.

CREATE VIEW <schema_name.view_name> AS
SELECT
    s.ProcID,
    s.CollectTimeStamp,
    s.QueryID,
    UserID,
    AcctString,
    ExpandAcctString,
    SessionID,
    LogicalHostID,
    RequestNum,
    InternalRequestNum,
    LogonDateTime,
    AcctStringTime,
    AcctStringHour,
    AcctStringDate,
    LogonSource,
    AppID,
    ClientID,
    ClientAddr,
    QueryBand,
    ProfileID,
    StartTime,
    FirstStepTime,
    FirstRespTime,
    LastStateChange,
    NumSteps,
    NumStepswPar,
    MaxStepsInPar,
    NumResultRows,
    TotalIOCount,
    AMPCPUTime,
    ParserCPUTime,
    UtilityByteCount,
    UtilityRowCount,
    ErrorCode,
    ErrorText,
    WarningOnly,
    ((firstresptime - starttime) hour(4) to second) (Named ElapsedTime),
        DelayTime,
    AbortFlag,
    CacheFlag,
    StatementType,
    StatementGroup,
    sqltextinfo AS QueryText,
    NumOfActiveAMPs,
    MaxAMPCPUTime,
    MaxCPUAmpNumber,
    MinAmpCPUTime,
    MaxAmpIO,
    MaxIOAmpNumber,
    MinAmpIO,
    SpoolUsage,
    WDID,
    OpEnvID,
    SysConID,
    LSN,
    NoClassification,
    WDOverride,
    ResponseTimeMet,
    ExceptionValue,
    FinalWDID,
    TDWMEstMaxRows,
    TDWMEstLastRows,
    TDWMEstTotalTime,
    TDWMAllAmpFlag,
    TDWMConfLevelUsed,
    TDWMRuleID,
    UserName,
    DefaultDatabase,
    AMPCPUTimeNorm,
    ParserCPUTimeNorm,
    MaxAMPCPUTimeNorm,
    MaxCPUAmpNumberNorm,
    MinAmpCPUTimeNorm,
    EstResultRows,
    EstProcTime,
    EstMaxRowCount,
    ProxyUser,
    ProxyRole,
    SessionTemporalQualifier,
    CalendarName,
    SessionWDID,
    DataCollectAlg,
    ParserExpReq,
    CallNestingLevel,
    NumRequestCtx,
    KeepFlag,
    QueryRedriven,
    ReDriveKind,
    CPUDecayLevel,
    IODecayLevel,
    TacticalCPUException,
    TacticalIOException,
    SeqRespTime,
    ReqIOKB,
    ReqPhysIO,
    ReqPhysIOKB,
    r.sqlrowno
FROM dbc.dbqlsqltbl r,
    dbc.dbqlogtbl s
WHERE r.queryid = s.queryid;

Grant the service account permissions to access this view.

In the Table Name field on the Query Log Ingestion tab of the Settings, specify the name of the view in the following format: schema_name.view_name.

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.

QLI Query Template

SELECT
  SessionID AS "sessionId",
  UserName AS "userName",
  StartTime AS "startTime",
  sqltextinfo AS "queryString",
  ((firstresptime - starttime) hour(4) to second) AS "milliSeconds",
  DefaultDatabase AS "defaultDatabases",
  AbortFlag AS "cancelled",
  LogonDateTime AS "sessionStartTime"
FROM dbc.dbqlsqltbl r,
  dbc.dbqlogtbl s
WHERE r.queryid = s.queryid
  AND r.procid = s.procid
  AND sqltextinfo IS NOT null
  AND StartTime >= STARTTIME
  AND StartTime < ENDTIME

Perform 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.