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

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.

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;