Multi-statement SQL execution on single Snowflake node

[Keith_Khazae]

Describe the problem/error/question

Just confirming that it’s NOT possible to execute a multi-statement SQL query in a single Snowflake node?

I see in the Snowflake REST API docs that you can specify the number of statements in a request but don’t see that as a feature/option on the node.

Can anyone confirm if this is or isn’t possible in n8n currently?

Much appreciated!

What is the error message (if any)?

ERROR: Actual statement count 11 did not match the desired statement count 1.

Information on your n8n setup

• n8n version: 1.0.5
• Database (default: SQLite): PostgreSQL
• n8n EXECUTIONS_PROCESS setting (default: own, main):
• Running n8n via (Docker, npm, n8n cloud, desktop app): Docker
• Operating system: MacOS Ventura 13.5

[EmeraldHerald]

Hi @Keith_Khazae Welcome to the community

Can you provide an example of the multi-statement query you were trying to run? Obviously don’t include any sensitive data

[Keith_Khazae]

@EmeraldHerald Thank you for your reply, sorry for the delay. See code below.

SET DOMAINS = 3;
SET DAY1LISTSIZE = 100;
CREATE OR REPLACE TEMPORARY TABLE  REV_SENDING AS
SELECT 
    *, 
    ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY ListBreaks ASC) AS RW
FROM (
    SELECT email AS EMAILADDRESS
    ,sum(score) score
    ,id as recipientId
    ,dense_rank() OVER (PARTITION BY CASE 
        WHEN lower(EMAILADDRESS) LIKE ('%@gmail%') then 1
        WHEN lower(EMAILADDRESS) LIKE ('%@yahoo%') then 2
        WHEN lower(EMAILADDRESS) LIKE ('%@hotmail%') then 3
        WHEN lower(EMAILADDRESS) LIKE ('%@aol%') then 4
        ELSE 5 END 
        ORDER BY sum(score) desc
) ListBreaks
FROM (
    select a.email
        ,b.message_id
        ,div0(1,(current_date - max(cast(b.created_at as date)))) as score
        ,a.id
    from TABLE1 a
    left join TABLE2 b
    on trim(lower(a.email))=trim(lower(b.email))
    left join TABLE3 c
    on trim(lower(a.email))=trim(lower(c.email))
    left join TABLE4 d
    on trim(lower(a.email))=trim(lower(d.email))
    left join TABLE5 e
    on trim(lower(a.email))=trim(lower(e.email))
    left join TABLE6 f
    on trim(lower(a.id))=trim(lower(f.recipient_id))
    where 
        c.email is null
        and d.email is null
        and e.email is null
        and f.recipient_id is null
    group by 
        a.email,
        b.message_id,
        a.id
)
where 
    score is not null 
    and email not like '%+%'
group by 
    email,
    id
) a
;
ALTER TABLE REV_SENDING CLUSTER BY (RW);
SET FULLLIST = (SELECT COUNT(DISTINCT EMAILADDRESS) FROM REV_SENDING);
SET WARMUPDAYS = (SELECT ROUND(((LN(COUNT(DISTINCT EMAILADDRESS)) - LN($DAY1LISTSIZE * $DOMAINS))/LN(2)) + 0.5) FROM REV_SENDING);
SET LISTS = (SELECT $WarmupDays * $Domains);
CREATE OR REPLACE PROCEDURE CreateDynamicRowsTable(NUM_ROWS INT)
RETURNS STRING
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
    CREATE OR REPLACE TEMPORARY TABLE DynamicRowsTable AS
    SELECT
        ROW_NUMBER() OVER (ORDER BY 1) AS Day,
        'Data for row ' || ROW_NUMBER() OVER (ORDER BY 1) AS Data
    FROM TABLE(GENERATOR(ROWCOUNT => :NUM_ROWS));
    RETURN 'DynamicRowsTable created with ' || NUM_ROWS || ' rows.';
END;
$$;
CALL CreateDynamicRowsTable($WARMUPDAYS);
CREATE OR REPLACE TEMPORARY TABLE REV_SENDING_FULL AS
SELECT a.*, b.DAY
FROM REV_SENDING a
LEFT JOIN DynamicRowsTable b on 1 = 1;
SELECT EMAILADDRESS, DAY, NTILE($DOMAINS) OVER (PARTITION BY DAY ORDER BY RW_DOMAIN) DOMAIN, SCORE
FROM (
SELECT EMAILADDRESS, DAY, ListBreaks, ROW_NUMBER() OVER (PARTITION BY DAY, LISTBREAKS ORDER BY RANDOM()) RW_DOMAIN, SCORE
FROM (
SELECT EMAILADDRESS, MIN(DAY_FINAL) DAY, SCORE, ListBreaks
from (
SELECT *, CASE WHEN RW <= $DOMAINS * $DAY1LISTSIZE THEN 0 ELSE DAY END AS DAY_FINAL
FROM
REV_SENDING_FULL
) a
WHERE RW <= $DOMAINS * $DAY1LISTSIZE * POW(2,DAY)
GROUP BY EMAILADDRESS, LISTBREAKS, SCORE
) b
) c
ORDER BY DAY asc, DOMAIN asc;

[EmeraldHerald]

Thanks for that @Keith_Khazae With a little bit more digging it does indeed look like this isn’t supported at the moment on the Snowflake node. I’ve moved this over to the feature request section of our forum so our product team can get eyes on it, and others can upvote