Forum Discussion

rashmi's avatar
rashmi
New Contributor III
3 years ago

Redshift queries with multiple create and 1 select

Hi All,
Is it possible to run multiple create statement first to create multiple temp tables and select statement (redshift query) which has to then fetch columns from those temp tables and need to run in one session i tried executing using Redshift - Execute
but it was not giving me column name as result set but instead giving me [{“status”:0,“message”:“success”}] as resultset which is resultset of just creating temp tables and not giving the select statement resultset i also tried to use Redshift - Multi Execute but it has was failing at select statement as it doesnt support
Any help is appreciated
Thanks

4 Replies

  • smudassir's avatar
    smudassir
    Employee
    3 years ago

    Hi @rashmi ,
    The execute snap may be able to run all the create statements followed by the select statement. However, it gives the output based on the first query. In your case, its a create statement so it gives the output with success message.
    If you have all the create statements first followed by a select statement at the end, then I suggest you to try one of these two approaches:
    - Approach1:
    a) Use two snaps. The first snap has to be the Execute snap. In this first snap, put only the create statements.
    b) The second snap can be an Execute snap with the select statement, or the Select snap itself.
    - Approach2:
    a) Use a multi-execute snap followed by a Execute/Select snap.
    b) Put all the create statements in the multi execute snap.
    c) And, use Execute snap with select, or the Select snap itself.

    Let me know if this helped.

  • koryknick's avatar
    koryknick
    Employee
    3 years ago

    Here is an example pipeline that performs as I described above. It can create the temporary tables, then query from them. Note that you may want to drop the tables as I have done in my example in case they are still present in your session due to connection pooling.

    Redshift script execute_2023_03_20.slp (8.8 KB)

    I hope this helps!

  • koryknick's avatar
    koryknick
    Employee
    3 years ago

    I have not tested with Redshift, but I know it works for other databases. You could try to use a Redshift account with Auto Commit disabled and pass the statements into a Redshift Execute snap one at a time. You will need a Router or Filter after the Execute to ignore the “success” records from the Create Table commands vs. the Select output.

    If that doesn’t work, can you use CTE (with clause) to sub-query your data rather than build temporary tables?