Streamline Your Redshift Queries with Query Editor V2 Parameters

Amazon Redshift Query Editor V2 is a modern, browser-based tool that simplifies SQL query development and execution. As an upgrade to the original editor, it offers enhanced features and a more intuitive interface, allowing users to efficiently interact with their Redshift clusters directly from the AWS Management Console. To demonstrate how to use parameters, let's consider the following query:

select 1.3 * avg(cs_ext_discount_amt)
from tpcds.catalog_sales 
    ,tpcds.date_dim
where 
d_date between '1999-02-22' and
dateadd(day,90,cast('1999-02-22' as date))
and d_date_sk = cs_sold_date_sk;

Now, imagine we want to run this query multiple times with different date ranges. Manually changing the dates would be time-consuming, especially for larger queries with multiple columns we'd like to parameterize. This manual process is prone to errors if we forget to update all occurrences.

To use parameters, we need to replace the values with parameter names enclosed in ${} as follows:

select 1.3 * avg(cs_ext_discount_amt)
from tpcds.catalog_sales 
    ,tpcds.date_dim
where 
d_date between '${mydate}' and
dateadd(day, ${myrange},cast('${mydate}' as date))
and d_date_sk = cs_sold_date_sk;

When we use parameters, clicking "Run" will open a window where we can input values.

Simply fill in the values and click "Run now". To clear all values, use "Reset form".

The query will then run based on the values added.

You can view all the queries you've run by clicking on the three dots and selecting "Tab History".

After clicking on the 3 dots, the options available are:

• Copy query
• Open query in a new tab
• View query details

Let's explore the query details to see the query with the values we set.

To run another query, return to the editor and click "Run". The parameter window will appear again, allowing you to input new values.