Using temporary tables in SQL query recipes

antonstam
antonstam Partner, Registered Posts: 25 Partner
edited July 16 in Using Dataiku

I'm struggling with using temp tables in a SQL query recipe (script recipes work fine, but I'd prefer to use queries). According to the docs, query recipes support "Pre statements that create temporary tables, used by the main SELECT", but I can't figure out how.

My best attempt looks like this:

SELECT *
INTO #temptable
FROM sys.tables

-- DKU_END_STATEMENT

SELECT *
FROM #temptable

I'm using Microsoft SQL Server. I see no reason why this query shouldn't work, but Dataiku throws the error that 'Query does not return results'. How could I get this query to work without errors?

· Share on FacebookShare on Twitter

Best Answer

  • fchataigner2
    fchataigner2 Dataiker Posts: 355 Dataiker
    Answer ✓

    Hi,

    the cause is the use of the `SELECT ... INTO ...` syntax, to create the temporary table. During the validation, DSS won't play any DDL statements, only DML ones, so this exclude CTAS constructs like `SELECT ... INTO ...`. Since the first part of the query is not run, the `#temptable` doesn't exist when DSS considers the `SELECT * FROM #temptable` to infer the output schema.

    You need to rewrite the query using `CREATE TABLE` and `INSERT INTO`.

    Regards,

    Frederic

    ps: you don't need the `-- DKU_END_STATEMENT`, and can use semi-colons to mark the end of a statement

    · Share on FacebookShare on Twitter

Answers

  • antonstam
    antonstam Partner, Registered Posts: 25 Partner
    edited July 17

    It's unfortunate that the SELECT INTO syntax is not supported, as it's an easy and flexible way to define a table. I can confirm that it works with a CREATE TABLE method:

    CREATE TABLE #temptable (id INT, value INT);

INSERT INTO #temptable VALUES (1,1), (2,2);

SELECT * FROM #temptable
    · Share on FacebookShare on Twitter
  • Tanguy
    Tanguy Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Neuron, Dataiku DSS Adv Designer, Registered, Dataiku DSS Developer, Neuron 2023 Posts: 119 Neuron

    CTE work fine for me (using Spark SQL recipes or SQL queries ; note : SQL queries run with Athena in our organization) :
    """
    WITH my_tmp_table AS (

    SELECT * FROM X)

    SELECT * FROM my_tmp_table

    """

    · Share on FacebookShare on Twitter
  • Jo
    Jo Dataiku DSS Core Designer, Registered Posts: 2 ✭✭✭

    Hi. I'm new to Dataiku and was wondering if this same syntax will work for temporary tables:

    CREATE TEMP TABLE temp1
    (x STRING, y STRING, z STRING);

    INSERT INTO temp1
    ( SELECT DISTINCT * FROM DATABASETABLE );
    SELECT * FROM temp1 ;
    · Share on FacebookShare on Twitter
Bookmark this topic

Categories

Setup Info
    Tags
      Help me…