[Redshift] How to get number of queries executed by each IAM account

0

Hi, I am trying to get the number of queries executed against Redshift but I found it very difficult to find it for each IAM user in our organization.

The problem is that most of the queries on Redshift are executed under the master user(awsuser) and the system tables only shows the count for the master user, not for each individual IAM user.

I have looked all the Redshift system tables and checked cloudtrail/cloudwatch logs. But it seems like I am stuck.

One more question. On CloudTrail event history, whenever I execute a query using redshift query editor v2, I can see several "DriverExecute" events coming up at once. Is there anyother event that I should target to get the number of queries executed by each IAM user?

Thanks

2개 답변
1

To get the number of queries executed by each IAM account in Amazon Redshift, you can query the STL_QUERY table. This table contains information about queries, including the user ID of the account that executed the query. By using the STL_QUERY table, you can track the number of queries executed by each IAM account. Here is an example SQL query to retrieve the number of queries executed by each IAM account:

SELECT userid, COUNT(*) AS num_queries FROM stl_query GROUP BY userid;

This query will provide the count of queries executed by each IAM account.

profile pictureAWS
전문가
답변함 10달 전
0

Understanding the pg_catalog Schema in Amazon Redshift

In Amazon Redshift, the pg_catalog schema is a special schema that contains system tables and views. These system objects provide information about the Redshift database, its configuration, and the objects stored within it.

The pg_catalog schema is part of the Redshift database itself, and it's not stored in a specific database. This means that when you query the pg_catalog schema, you don't need to specify a particular database in your query. The Redshift Query Editor automatically connects to the database you're currently using.

Querying the pg_catalog Schema

To query the pg_catalog schema, you can use the same SQL syntax as you would for any other schema in your Redshift database. Here's an example query that retrieves information about the system tables and views in the pg_catalog schema:

SELECT schemaname, tablename
FROM pg_tables
WHERE schemaname = 'pg_catalog';

This query will return the schema name and table name for all the tables in the pg_catalog schema.

Querying System Tables in pg_catalog

The pg_catalog schema contains a wealth of information about your Redshift database, including system tables that track query history, user information, and more. Here's an example query that retrieves information about the query history and the corresponding user details:

SELECT user_id,
       usr.usename, 
       count(1) as counter
FROM pg_catalog.sys_query_history as q_hst
LEFT JOIN pg_catalog.pg_user as usr
    on usr.usesysid = q_hst.user_id
GROUP BY 1, 2;
답변함 2달 전

로그인하지 않았습니다. 로그인해야 답변을 게시할 수 있습니다.

좋은 답변은 질문에 명확하게 답하고 건설적인 피드백을 제공하며 질문자의 전문적인 성장을 장려합니다.

질문 답변하기에 대한 가이드라인

관련 콘텐츠