Variables in SQL Runner

For data modelers & consumers Redshift
1

Hi Snowplowers,

Can we use variable in SQL Runner. We are doing some clean up activity and we want to log some information, thus want to use variable.

Thanks!
Deepak Bhatt

2

Hi @deepak,

Do you mean populating your SQL via variables?

We have options to template noted here: https://github.com/snowplow/sql-runner/wiki/Guide-for-analysts#templates

You can pass in variables to use in the template from:

These variables can then be referenced in the SQL like so: https://github.com/snowplow/sql-runner/blob/master/integration/resources/postgres-sql/good/3.sql

Hope this helps,

Josh

3

Hi @josh

Appreciate your quick reply. But I want to store the result of my previous query in some variable and insert the same variable into table. Is this possible.

Thanks!
Deepak Bhatt

4

Hi @deepak,

That’s not possible at the moment. You can store the result in a temporary table though (not as clean a solution, but it works).

5

Hi @christophe

I want to do the same but I am not able to achieve it i.e. I want to store some audit data into an temp table but I am not able to find a way how can I store the result of previous query into temp table.Can you please share some sample query, which I can follow.
Appreciate your help.

Thanks!
Deepak Bhatt

6

To create a temporary table (that is destroyed at the end of the session) you can do something similar to:

CREATE TEMPORARY TABLE example AS (select 1)

Where example is the name of your temporary table and the select 1 statement is the query that should populate the table with the given data. The AWS documentation has more detail about CTAS statements here.

7

Hi @mike

Thanks for your quick reply.

What I want: I want to cleanup my redshift DB and want to audit the data. For that I will create an temp table in which I will insert precount, deleted records, post count, table name & date. So I want a way to insert records with the previous result.

Regards!
Deepak Bhatt