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.

    Copy
    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.

    Copy
    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.

    Copy
    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.

    Copy
    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 other view as shown following, and you’ll see a result similar to the following.

    Copy
    select * from wlm_queue_state_vw;

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

    Copy
    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.

    Copy
    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 into 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.

    Copy
    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.

    Copy
    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.

    Copy
    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.

    Copy
    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.

    Copy
    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.

    Copy
    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.

    Copy
    reset query_group;