如何在Redshift中查询访问指定视图的用户及相关统计信息

Hey there! Let's figure out how to track which users are querying your specific views and pull those useful stats you're after. The solution varies a bit depending on your database system, so I'll break down the most common ones with actionable queries:

PostgreSQL #

First, make sure the pg_stat_statements extension is enabled (it's great for query tracking). Here's a query that maps to your desired output:

SELECT
  s.usename AS userid,
  c.relname AS view_name,
  q.queryid,
  q.query_start AS starttime,
  NOW() - q.query_duration AS endtime,
  q.cpu_time / 1000 AS query_cpu_time, -- Convert microseconds to seconds
  q.blocks_read AS query_blocks_read,
  q.total_time / 1000 AS query_execution_time, -- Total time in seconds
  q.rows AS return_row_count
FROM pg_stat_statements q
JOIN pg_stat_activity s ON q.pid = s.pid
JOIN pg_class c 
  ON c.oid = regexp_match(q.query, 'FROM\s+(?:[^.]+\.)?([^\s;]+)')::regclass
WHERE 
  c.relname IN ('user_activity_last_6_months', 'user_compliance_last_month')
  AND c.relkind = 'v' -- Ensure we're only matching views
ORDER BY q.query_start DESC;

Notes: #

• The regex handles views with or without schema prefixes (like schema.view_name).
• You might need to adjust the regex if your queries use complex FROM clauses (e.g., subqueries, joins).

SQL Server #

SQL Server uses dynamic management views (DMVs) to track query activity. This query leverages execution plans to accurately identify view references:

WITH QueryDetails AS (
  SELECT
    s.login_name AS userid,
    t.text AS query_text,
    qs.sql_handle,
    qs.creation_time AS starttime,
    qs.last_execution_time AS endtime,
    qs.total_worker_time / 1000 AS query_cpu_time, -- Milliseconds to seconds
    qs.total_logical_reads AS query_blocks_read,
    qs.total_elapsed_time / 1000 AS query_execution_time,
    qs.total_rows AS return_row_count,
    qs.plan_handle
  FROM sys.dm_exec_query_stats qs
  JOIN sys.dm_exec_sessions s ON qs.session_id = s.session_id
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t
  WHERE 
    t.text LIKE '%user_activity_last_6_months%' 
    OR t.text LIKE '%user_compliance_last_month%'
)
SELECT
  qd.userid,
  v.name AS view_name,
  CONCAT(qd.session_id, '-', req.request_id) AS queryid, -- Unique query identifier
  qd.starttime,
  qd.endtime,
  qd.query_cpu_time,
  qd.query_blocks_read,
  qd.query_execution_time,
  qd.return_row_count
FROM QueryDetails qd
JOIN sys.dm_exec_query_plan(qd.plan_handle) p ON qd.plan_handle = p.plan_handle
JOIN sys.views v 
  ON v.object_id = p.query_plan.value(
    'declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; 
    //p:Object[@ObjectType="View"]/@ObjectID', 'int'
  )
WHERE v.name IN ('user_activity_last_6_months', 'user_compliance_last_month')
ORDER BY qd.starttime DESC;

Notes: #

• Requires VIEW SERVER STATE permission to access DMVs.
• Using execution plans avoids false positives from view names appearing in query text (e.g., string literals).

MySQL #

For MySQL, we'll use the Performance Schema to track query history:

SELECT
  t.processlist_user AS userid,
  v.table_name AS view_name,
  e.event_id AS queryid,
  FROM_UNIXTIME(e.timer_start / 1000000000) AS starttime,
  FROM_UNIXTIME(e.timer_end / 1000000000) AS endtime,
  e.cpu_time / 1000000000 AS query_cpu_time, -- Nanoseconds to seconds
  e.rows_examined AS query_blocks_read,
  (e.timer_end - e.timer_start) / 1000000000 AS query_execution_time,
  e.rows_sent AS return_row_count
FROM performance_schema.events_statements_history e
JOIN performance_schema.threads t ON e.thread_id = t.thread_id
JOIN information_schema.views v 
  ON e.sql_text LIKE CONCAT('%', v.table_name, '%')
WHERE v.table_name IN ('user_activity_last_6_months', 'user_compliance_last_month')
ORDER BY e.timer_start DESC;

Notes: #

• Ensure the Performance Schema is enabled (check performance_schema=ON in your my.cnf).
• Adjust the events_statements_history size if you need to retain more historical data.

General Tips #

• Permissions: You'll need elevated permissions to access system tracking tables/views in all databases.
• Historical Data: Some systems only retain recent query activity (e.g., PostgreSQL's pg_stat_statements has a configurable limit). For long-term tracking, consider setting up a scheduled job to log this data to a custom table.
• Complex Queries: If users run queries with subqueries or multiple joins, you may need to refine the regex/plan parsing logic to accurately capture view references.

内容的提问来源于stack exchange，提问作者Nambu14