Amazon Redshift Query Log Table¶
Automated daily Query Log Ingestion of Amazon Redshift queries requires an ETL to maintain a rolling Query Log having queries executed in the last 3 days. If Query Log table is small then we recommend maintaining queries for a longer period like the last seven days.
This needs to be a table, not a view, to allow the Alation user to see queries created by other people. If the Alation user queried the view, it would only see its own queries; if a process with full access to everyone’s queries creates and updates a table the Alation user queries, the Alation user can see all the queries in that table.
The following two queries can be used to create a rolling query log. The sample queries use public.alation_qlog as the Query Log table however the table can be named anything and can be placed in any schema as long as Alation DB account has SELECT, INSERT, and DELETE privileges to the table.
Delete queries older than last three days.
DELETE FROM PUBLIC.ALATION_QLOG WHERE START_TIME < (CURRENT_DATE - INTERVAL '3 days');
Append queries executed in the last 24 hours to the Query Log. The following queries need to run as an admin user else the Query Log will not have queries by all users.
Use the following query for Amazon Redshift Serverless and Amazon Redshift Provisioned (Recommended):
SELECT cast(sqt.text AS VARCHAR) as text, sqt.sequence as seq, CASE when sqh.status in ('failed' , 'canceled') then 'ABORTED' else '' end as canceled, datediff (millisecond, sqh.start_time, sqh.end_time) / 1000.0 as seconds_taken, cast(pui.usename AS VARCHAR) as user_name, cast(ssh.database_name AS VARCHAR) as default_database, cast(sqh.session_id AS VARCHAR) || '/' || to_char(ssh.start_time,'YYYY-MM-DD HH:MI:SS.US') as session_id, sqt.start_time as start_time, sqh.transaction_id as transaction_id INTO public.alation_qlog FROM sys_query_history sqh JOIN sys_query_text sqt ON sqh.query_id=sqt.query_id JOIN pg_user_info pui ON pui.usesysid=sqh.user_id JOIN sys_session_history ssh ON ssh.session_id=sqh.session_id WHERE sqh.user_id > 1 AND sqt.start_time >= (LOCALTIMESTAMP - interval '3 day') ;
Alternative SQL¶
Note
The query mentioned in this section does not automatically create table.
INSERT INTO
public.alation_qlog (
SELECT
cast(sqt.text AS VARCHAR) as text,
sqt.sequence as seq,
CASE
when sqh.status in ('failed' , 'canceled') then 'ABORTED'
else ''
end as canceled,
datediff (millisecond, sqh.start_time, sqh.end_time) / 1000.0 as seconds_taken,
cast(pui.usename AS VARCHAR) as user_name,
cast(ssh.database_name AS VARCHAR) as default_database,
cast(sqh.session_id AS VARCHAR) || '/' || to_char(ssh.start_time,'YYYY-MM-DD HH:MI:SS.US') as session_id,
sqt.start_time as start_time,
sqh.transaction_id as transaction_id
FROM
sys_query_history sqh
JOIN
sys_query_text sqt ON sqh.query_id=sqt.query_id
JOIN
pg_user_info pui ON pui.usesysid=sqh.user_id
JOIN
sys_session_history ssh ON ssh.session_id=sqh.session_id
WHERE
sqh.user_id > 1
AND sqt.start_time >= (LOCALTIMESTAMP - interval '3 day')
);