Merge statement and stored procedure

Question

I am trying to write a stored procedure with a Merge statement. It looks something like this:

create or replace procedure database.events.insert_groupss()
returns string
LANGUAGE JAVASCRIPT
as
$$
var sql_cmd = 'Merge into database.events.groups et using
        (select lower(f.VALUE:id::string) as id,
                min(f.VALUE:time_timestamp::timestamp) as event_timestamp,
                f.VALUE:service_string::string as service,
                f.VALUE:title_string::string as title,
                min(X.VALUE:created_at::timestamp) as title_ts ,
                X.VALUE:group::string as group
         from \'@database.sources.s3stage/version=3/stream=live/year=2019/month=12/\'
         (file_format => \'oak.public.JSON_FORMAT\' ), lateral flatten (input => $1:group_events, RECURSIVE => TRUE) f
         , LATERAL FLATTEN(parse_json(replace(replace(replace(replace(f.value:groups ,\'"[\', \'[\'),\'=>\',\':\'),\'\\\\\',\'\'),\']"\',\']\'))) x
          where id is not null and group is not null
          group by id,group,service,title)a
         on et.id=a.id and et.group = a.group
         when not matched then Insert (id , event_timestamp , service , title , title_ts ,group  )
                               values (a.id , a.event_timestamp , a.service , a.title , a.title_ts ,a.group  );';
try {

    snowflake.execute ({sqlText: sql_cmd});

    return "Succeeded";

    }

catch (err) {

    return "Failed: " + err;  // Return a success/error indicator.

    }
$$;

The stored procedure was created, but if I run it, I am getting some syntax error. The escaping characters were escaped and the query was getting executed when I run it manually (with using the stored procedure).

Here is the error I am getting

JavaScript compilation error: Uncaught SyntaxError: Invalid or unexpected token in INSERT_GROUPS at 'var sql_cmd = 'Merge into database.events.groups et using' position 14

score 3 · Accepted Answer · edited May 30 '20 at 17:17

The problem is sql_cmd is in muti line so you need to concatenate or it should be in single line.

create or replace procedure  database.events.insert_groupss()
returns string
LANGUAGE JAVASCRIPT
as
$$
var sql_cmd  = 'Merge into database.events.groups et using'
    sql_cmd += ' (select lower(f.VALUE:id::string) as id,'
    sql_cmd += ' min(f.VALUE:time_timestamp::timestamp) as event_timestamp,'
    sql_cmd += ' f.VALUE:service_string::string as service,'
    sql_cmd += ' f.VALUE:title_string::string as title,'
    sql_cmd += ' min(X.VALUE:created_at::timestamp) as title_ts ,'
    sql_cmd += ' X.VALUE:group::string as group'
    sql_cmd += ' from \'@database.sources.s3stage/version=3/stream=live/year=2019/month=12/\''
    sql_cmd += ' (file_format => \'oak.public.JSON_FORMAT\' ), lateral flatten (input => $1:group_events, RECURSIVE => TRUE) f'
    sql_cmd += ' , LATERAL FLATTEN(parse_json(replace(replace(replace(replace(f.value:groups ,\'"[\', \'[\'),\'=>\',\':\'),\'\\\\\',\'\'),\']"\',\']\'))) x'
    sql_cmd += ' where id is not null and group is not null'
    sql_cmd += ' group by id,group,service,title)a'
    sql_cmd += ' on et.id=a.id and et.group = a.group'
    sql_cmd += ' when not matched then Insert (id , event_timestamp , service , title , title_ts ,group  )'
    sql_cmd += ' values (a.id , a.event_timestamp , a.service , a.title , a.title_ts ,a.group  );";'
try {

    snowflake.execute ({sqlText: sql_cmd});

    return "Succeeded";

    }

catch (err) {

    return "Failed: " + err;  // Return a success/error indicator.

    }
$$;

Actually snowflake SP support line continuation with a backquote as you've done and is documented here: https://docs.snowflake.net/manuals/sql-reference/stored-procedures-usage.html#line-continuation , and that makes it easier to maintain. When you do that, you don't need to escape the other quotes. I found when pasting your code into the web gui that there were some mistakes in the replace section. get the code running interactively and then paste back into the SP and you'll be running fine. — Erick Roesch, Dec 11 '19 at 12:30
@sriga, Thanks yes it's because of the multiline code. Appending it using `+=` is kind of painful process I used backquote `\`` as per @erick reference. — Ravi Satya Yenugula, Dec 11 '19 at 21:30

score 0 · Answer 2 · edited May 30 '20 at 17:17

0

Issue is in try clause , incorrect SQL variable name. Replace with below

snowflake.execute ({sqlText: sql_cmd})

edited May 30 '20 at 17:17

Dharman

30,962
25
85
135

answered Dec 11 '19 at 02:30

Sriga

1,165
4
11

Thanks for the finding updated. Still, it's failing with the same error. ```JavaScript compilation error: Uncaught SyntaxError: Invalid or unexpected token in insert_groupss at 'var sql_cmd = 'Merge into database.events.groups et using' position 14 ``` – Ravi Satya Yenugula Dec 11 '19 at 02:43
@RaviSatyaYenugula try with my new answer, before executing the stoted procedure make sure merge statement is working. – Sriga Dec 11 '19 at 09:19

Merge statement and stored procedure

2 Answers2