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

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