Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

Assigning Queries to Queues

The following examples assign queries to queues according to user groups and query groups.

Assigning Queries to Queues Based on User Groups

If a user group name is listed in a queue definition, queries run by members of that user group will be assigned to the corresponding queue. The following example creates user groups and adds users to groups by using the SQL commands CREATE USER, CREATE GROUP, and ALTER GROUP.

Copy
create group admin_group with user admin246, admin135, sec555; create user vp1234 in group ad_hoc_group password 'vpPass1234'; alter group admin_group add user analyst44, analyst45, analyst46;

Assigning a Query to a Query Group

You can assign a query to a queue at run time by assigning your query to the appropriate query group. Use the SET command to begin a query group.

Copy
SET query_group TO group_label

Here, group_label is a query group label that is listed in the WLM configuration.

All queries that you run after the SET query_group command will run as members of the specified query group until you either reset the query group or end your current login session. For information about setting and resetting Amazon Redshift objects, see SET and RESET in the SQL Command Reference.

The query group labels that you specify must be included in the current WLM configuration; otherwise, the SET query_group command has no effect on query queues.

The label defined in the TO clause is captured in the query logs so that you can use the label for troubleshooting. For information about the query_group configuration parameter, see query_group in the Configuration Reference.

The following example runs two queries as part of the query group 'priority' and then resets the query group.

Copy
set query_group to 'priority'; select count(*)from stv_blocklist; select query, elapsed, substring from svl_qlog order by query desc limit 5; reset query_group;

Assigning Queries to the Superuser Queue

To assign a query to the Superuser queue, log on to Amazon Redshift as a superuser and then run the query in the superuser group. When you are done, reset the query group so that subsequent queries do not run in the Superuser queue.

The following example assigns two commands to run in the Superuser queue.

Copy
set query_group to 'superuser'; analyze; vacuum; reset query_group;

To view a list of superusers, query the PG_USER system catalog table.

Copy
select * from pg_user where usesuper = 'true';