Dynamic pivot in Amazon Redshift. Don’t blame databases for disappointing you, blame yourself for expecting too much from them.

SQLblog.com - The SQL Server blog spot on the web

Dynamic pivot in Amazon Redshift. Don’t blame databases for disappointing you, blame yourself for expecting too much from them.

 

Several days ago I have spent a few hours of my life figuring out how to do dynamic pivot in Amazon Redshift. To tell you the truth, I have expected much more from this DBMS SQL language.

Redshift is based on Postgre SQL 8.0.2 ( Release day in 2005 !!!! )

Anything you would want for this, not too difficult task,  does not exits.  No stored procedures. No JSON datatype. No variables outside of UDF, no queries inside UDFs. “UDF can be used to calculate values but cannot be used to call SQL functions”. Python UDFs also cannot query the data, only perform calculations.

Finally I have found one useful function LISTAGG that helped me to get distinct values of all pivoted columns.

For each group in a query, the LISTAGG aggregate function orders the rows for that group according to the ORDER BY expression, then concatenates the values into a single string.

Syntax

Copy

LISTAGG( [DISTINCT] aggregate_expression [, 'delimiter' ] ) [ WITHIN GROUP (ORDER BY order_list) ]

As a next step, I have used Python UDF to dynamically build pivoting query ( I love Python more and more, this is such a nice language :-) . In addition, I have used Common Table Expression CTE ( yes, they have it! ) to pass a list of pivoted columns to Python UDF.

 

The last step, execution the SQL query, I am still trying to figure out – there is no dynamic query execution in Redshift SQL dialect. But my customer can execute this query from reporting tool.

 

Here is a setup and all queries.

 

Creating a table: 

create table maria_deleteme ( id int, year int, customer varchar, amount int);

 

Loading some test values:

insert into maria_deleteme values

( 12, 2010, 'customer1', 4),

( 2, 2010,'customer1', 80),

( 3, 2010,'customer2', 41),

( 4, 2011,'customer1', 45),

( 5, 2011,'customer2', 15),

( 6, 2011,'customer3', 18),

( 7, 2012,'customer1', 23),

( 8, 2012,'customer1', 1),

( 9, 2012,'customer1', 8),

( 10, 2012,'customer3', 98),

( 11, 2013,'customer2', 1);

 

Query that generates dynamic pivot query:

with vars

       as (

       select listagg(distinct year, ',') as years

       from maria_deleteme

       )

select maria_pivoting ('maria_deleteme',years,'year','customer','amount')

from vars;

 

Python UDF: 

DROP FUNCTION maria_pivoting (varchar,varchar,varchar,varchar)

 

CREATE FUNCTION maria_pivoting(tablename varchar, list_vals varchar, pivot_col varchar, groupby_col varchar, counted_col varchar )

RETURNS varchar(4000) IMMUTABLE AS $$

      

       vals = list_vals.split(",")

       pivot_query = " select  "

 

       for value in vals:

           pivot_query = pivot_query + ' sum (case when {} = {}  then {} else 0 end) as "{}" ,'.format(pivot_col,str(value),counted_col,str(value))

      

       pivot_query = pivot_query + ' {} from {} group by {} ;'.format(groupby_col,tablename,groupby_col)

      

       return pivot_query

$$ LANGUAGE plpythonu;

 

I will be glad to hear what do you think about Redshift SQL language ( Of course I know that this is very powerful database for data processing. Although VERY expensive.)

Yours, Maria

more info...