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

Section 3: Routing Queries to Queues Based on User Groups and Query Groups

Now that you have your cluster associated with a new parameter group, and you have configured WLM, you’ll run some queries to see how Amazon Redshift routes queries into queues for processing.

Step 1: View Query Queue Configuration in the Database

First, verify that the database has the WLM configuration that you expect.

To View the Query Queue Configuration

  1. Open psql and run the following query. The query uses the WLM_QUEUE_STATE_VW view you created in Step 1: Create the WLM_QUEUE_STATE_VW View. If you already had a session connected to the database prior to the cluster reboot, you’ll need to reconnect.

    select * from wlm_queue_state_vw;

    The following is an example result.

    Compare these results to the results you received in Step 1: Create the WLM_QUEUE_STATE_VW View. Notice that there are now two additional queues. Queue 1 is now the queue for the test query group, and queue 2 is the queue for the admin user group.

    Queue 3 is now the default queue. The last queue in the list is always the default queue, and that is the queue to which queries are routed by default if no user group or query group is specified in a query.

  2. Run the following query to confirm that your query now runs in queue 3.

    select * from wlm_query_state_vw;

    The following is an example result.

Step 2: Run a Query Using the Query Group Queue

To Run a Query Using the Query Group Queue

  1. Run the following query to route it to the test query group.

    set query_group to test; select avg(l.priceperticket*s.qtysold) from listing l, sales s where l.listid <40000;
  2. From the other psql window, run the following query.

    select * from wlm_query_state_vw;

    The following is an example result.

    The query was routed to the test query group, which is queue 1 now.

  3. Select all from the queue state view.

    select * from wlm_queue_state_vw;

    You'll see a result similar to the following.

  4. Now, reset the query group and run the long query again:

    reset query_group; select avg(l.priceperticket*s.qtysold) from listing l, sales s where l.listid <40000;
  5. Run the queries against the views to see the results.

    select * from wlm_queue_state_vw; select * from wlm_query_state_vw;

    The following are example results.

    The result should be that the query is now running in queue 3 again.

Step 3: Create a Database User and Group

In Step 1: Create a Parameter Group, you configured one of your query queues with a user group named admin. Before you can run any queries in this queue, you need to create the user group in the database and add a user to the group. Then you’ll log on with psql using the new user’s credentials and run queries. You need to run queries as a superuser, such as the masteruser, to create database users.

To Create a New Database User and User Group

  1. In the database, create a new database user named adminwlm by running the following command in a psql window.

    create user adminwlm createuser password '123Admin';
  2. Then, run the following commands to create the new user group and add your new adminwlm user to it.

    create group admin; alter group admin add user adminwlm;

Step 4: Run a Query Using the User Group Queue

Next you’ll run a query and route it to the user group queue. You do this when you want to route your query to a queue that is configured to handle the type of query you want to run.

To Run a Query Using the User Group Queue

  1. In psql window 2, run the following queries to switch to the adminwlm account and run a query as that user.

    set session authorization 'adminwlm'; select avg(l.priceperticket*s.qtysold) from listing l, sales s where l.listid <40000;
  2. In psql window 1, run the following query to see the query queue that the queries are routed to.

    select * from wlm_query_state_vw; select * from wlm_queue_state_vw;

    The following are example results.

    Note that the queue this query ran in is queue 2, the admin user queue. Any time you run queries logged in as this user, they will run in queue 2 unless you specify a different query group to use.

  3. Now run the following query from psql window 2.

    set query_group to test; select avg(l.priceperticket*s.qtysold) from listing l, sales s where l.listid <40000;
  4. In psql window 1, run the following query to see the query queue that the queries are routed to.

    select * from wlm_queue_state_vw; select * from wlm_query_state_vw;

    The following are example results.

  5. When you’re done, reset the query group.

    reset query_group;