[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

posta un anno fa706 visualizzazioni
2 Risposte
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
ESPERTO
con risposta 10 mesi fa
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;
con risposta 2 mesi fa

Accesso non effettuato. Accedi per postare una risposta.

Una buona risposta soddisfa chiaramente la domanda, fornisce un feedback costruttivo e incoraggia la crescita professionale del richiedente.

Linee guida per rispondere alle domande