How To Draw A Quick Snowflake

Dynamic PIVOTs in SQL with Snowflake

Easy to utilise dynamic pivots in Snowflake: How to use and create a JavaScript stored procedure within.

There's something well-nigh SQL and PIVOTs: Anybody wants to Pivot their results, merely there never seems to be an easy way for information technology. Fortunately, Snowflake users have a native style to perform pivots in SQL, with the function PIVOT():

          select *
from (
            select * from table(result_scan(last_query_id(-1)))
)
pivot(max(pivot_value)
for pivot_column in ('Spider-Man', 'Wonder Woman', 'Iron Human'))        

All the same, PIVOT() in Snowflake has i limitation: Users demand to explicitly phone call out the values for the columns to be pivoted into. Then users and so get to Stack Overflow to enquire how to get Snowflake to deliver a PIVOT() with dynamic columns, and there doesn't seem to exist a straightforward answer.

That's why I want to offering here a absurd mode to become dynamic pivots in Snowflake, by using a unproblematic to call JavaScript stored procedure. First let me introduce how to apply the solution, so nosotros'll talk about the cool tricks in Snowflake I used to pull this off:

How to get dynamic pivots in Snowflake

1. Write a query that aggregates the data you want to pin. Make sure to ascertain a cavalcade with the pivot_values, and a column with the pivot_columns:

          select proper name
            , date_trunc(quarter, month) pivot_column
            , sum(month_views) pivot_value            
from hero_views
group by 1,ii        

ii. Phone call the stored procedure pivot_prev_results():

          phone call pivot_prev_results();        

three. At present find your pivoted results by scanning the output of that stored procedure:

          select *
from table(result_scan(last_query_id(-2)));        

That'due south it!

Now, if you want to pivot by their names instead — just echo the process, switching the names of the columns in the first query:

1. Query

          select name pivot_column
            , date_trunc(quarter, month) month
            , sum(month_views) pivot_value
from hero_views
group by 1,two        

2. Call stored procedure

          call pivot_prev_results();        

iii. Discover the results:

          select *
from table(result_scan(last_query_id(-2)));        

Creating the stored procedure

If you want to call pivot_prev_results(), first you need to create information technology. This is the source lawmaking:

Discover the full source lawmaking on GitHub

Cool tricks that brand pivot_prev_results() work:

Allow's examine the stored process, to telephone call out some absurd ideas in Snowflake:

          create or supplant procedure pivot_prev_results()        

Yep. You can ascertain stored procedures in Snowflake.

          returns string        

We don't need to return anything for this stored procedure to work, but we might too use this later.

          linguistic communication javascript        

This is absurd: Stored procedures in Snowflake can be defined in JavaScript, opening up a world of possibilities.

          execute as caller        

A stored procedure tin be executed with it'southward own set of permissions and context, or within the context of the person calling it. In this example executing in the caller'due south context will allow us to expect at their previous query results, and leave the results of this process in their query history.

          equally
$$        

We use '$$' to circumscribe the JavaScript lawmaking. $$ allows us to avoid more complicated escaping within.

          var cols_query = `
select '\\''
|| listagg(distinct pivot_column, '\\',\\'') within group (order by pivot_column)
|| '\\''
from table(result_scan(last_query_id(-1)))`;        

This is the query that will allow u.s.a. to determine the columns in the resulting pivot.

Note the employ of result_scan(last_query_id(-1) — with this we can wait at the results of the previous query executed — which nosotros await to have the values to be pivoted (per the instructions before calling this stored process).

Primal Snowflake concept: Snowflake queries are executed within a session — which allows you to refer to previous queries, results, and fifty-fifty fix session variables.

To get all the unlike values in the pivot_column column we apply listagg(distinct pivot_column) and to make sure they expect sorted in the last result nosotros add together the within group(order by pivot_column).

And so nosotros execute the query inside the process. What'southward cool here is that Snowflake provides the JavaScript UDF environment an API and so the JS code can inquire Snowflake to execute queries:

          var stmt1 = snowflake.createStatement({sqlText: cols_query});
var results1 = stmt1.execute();
results1.side by side();        

The results of this query are the list of columns we are going to employ to ask Snowflake for a pivot:

          var col_list = results1.getColumnValue(1);        

So we but need to create a straightforward Pivot() query in Snowflake, using the values of the columns nosotros just figured out:

          pivot_query = `
select *
from (select * from tabular array(result_scan(last_query_id(-2))))
pivot(max(pivot_value) for pivot_column in (${col_list}))
`
var stmt2 = snowflake.createStatement({sqlText: pivot_query});
stmt2.execute();        

Note that this query uses `max(pivot_value)`. The idea is that the previous query ran past the user will aggregate the results in any style they desire to, and in this step we should be picking at almost one value to be aggregated.

That's it. Once that query is executed, the pivoted results live in the caller'southward query history. Since we don't need to return anything, we tin use that infinite to give the caller a hint of ii ways of accessing the results of the query we just executed: Either by its id, or by looking back into their history:

          render `select * from table(result_scan('${stmt2.getQueryId()}'));\n select * from tabular array(result_scan(last_query_id(-two)));`;
$$;        

Note that users volition detect the results with last_query_id(-ii), considering -1 are the results of calling the stored process.

Happy pivots!

Desire more?

I'm Felipe Hoffa, Information Cloud Abet for Snowflake. Thanks for joining me on this hazard. You can follow me on Twitter, and check reddit.com/r/snowflake for the near interesting Snowflake news.

Source: https://medium.com/snowflake/dynamic-pivots-in-sql-with-snowflake-c763933987c

Posted by: pantonemenim69.blogspot.com

Share this post