No Of Queries Hitting Redshift tables in a Time Frame

Question

How can I know the number of queries hitting a table in a particular time frame and what are those queries

Is it possible to get those stats for live tables hitting a redshift table?

Answer 1

This will give you the number of queries hitting a redshift table in a certain time frame:

SELECT
count(*)
FROM stl_wlm_query w
  LEFT JOIN stl_query q
         ON q.query = w.query
        AND q.userid = w.userid
  join pg_user u  on u.usesysid = w.userid
-- Adjust your time frame accordignly
WHERE w.queue_start_time >=  '2022-04-04 10:00:00.000000'
AND   w.queue_start_time <=  '2022-04-05 22:00:00.000000'
AND   w.userid > 1
-- Set the table name here:
AND querytxt like '%my_main_table%';

If you need the actual queries text hitting the table in a certain timeframe, plus the queue and execution time and the user (remove if not needed):

SELECT
       u.usename,
       q.querytxt,
       w.queue_start_time,
       w.total_queue_time / 1000000 AS queue_seconds,
       w.total_exec_time / 1000000 exec_seconds
FROM stl_wlm_query w
  LEFT JOIN stl_query q
         ON q.query = w.query
        AND q.userid = w.userid
  join pg_user u  on u.usesysid = w.userid
-- Adjust your time frame accordignly
WHERE w.queue_start_time >=  '2022-04-04 10:00:00.000000'
AND   w.queue_start_time <=  '2022-04-05 22:00:00.000000'
AND   w.userid > 1
-- Set the table name here:
AND querytxt like '%my_main_table%'
ORDER BY w.queue_start_time;

Answer 2

If by "hitting a table you mean scan then they system table stl_scan lists all the accesses to a table and lists the query number that causes this scan. By writing a query to aggregate the information in stl_scan you can look at it by time interval and/or originating query. If this isn't what you mean you will need to clarify.

I don't understand what is meant by 'stats for live tables hitting a redshift table?'. What is meant by a table hitting a table?