CODEWITHSUNDEEP
sqlstored-proceduresazure-blob-storagesnowflake-cloud-data-platform
marcin2x4
marcin2x4

Reputation: 1429

Snowflake procedure error (dropping data into blob storage)

My procedure is to drop data into csv file on azure blob. SQL code works ok but when implemented into procedure I get following error:

JavaScript compilation error: Uncaught SyntaxError: Invalid or unexpected token in TST_30102020 at ' sqlText:'COPY INTO @storage/folder/file_1.csv' position 19

I can't find the odd symbol that breaks the whole thing.

Procedure goes below:

CREATE or REPLACE PROCEDURE TST_30102020()
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
var stmt = snowflake.createStatement({
           sqlText:'COPY INTO @storage/folder/file_1.csv

                     FROM (SELECT t1.ATTR,t1.ATTR, t2.ATTR, t2.ATTR, t1.ATTR, t1.MIASTO, t1.ATTR, t1.ATTR,
                                    t1.ATTR, t1.ATTR, t1.ATTR, t1.ATTR, t1.ATTR, t1.ATTR,
                                    t1.ATTR, t1.ATTR, t1.ATTR, t1.ATTR, t1.ATTR, t1.KONCESJA_A, t1.ATTR, t1.ATTR
                           FROM t1

                           LEFT JOIN (SELECT * FROM table WHERE ATTR >= '2020-09-01') AS t2
                                  ON t1.ATTR= t2.ATTR AND t1.ATTR= t2.ATTR

                           LEFT JOIN (SELECT * FROM table WHERE ATTR>= '2020-09-01') AS t3
                                  ON t2.ATTR= t3.ATTR
                                  AND t2.ATTR= t3.ATTR
                                  AND t2.ATTR= t3.ATTR

                           WHERE t2.ATTR> 0
                           ORDER BY t2.ATTR, t1.ATTR)

                    FILE_FORMAT = (TYPE = 'CSV')
                    OVERWRITE = TRUE
                    SINGLE = TRUE 
                    MAX_FILE_SIZE = 170000000'});
    
try {
    stmt.execute();
    return "Succeeded.";
  }

catch (err) {
    return "Failed: " + err;
  }

$$;

Upvotes: 0

Views: 146

Answers (1)

Greg Pavlik
Greg Pavlik

Reputation: 11046

The problem is you're opening the JavaScript string with single quotes and your string contains single quotes. It's also a multi-line literal. The good news is JavaScript provides a nice way to deal with this. Change your opening and closing quotes on the string from single quotes to back ticks. The back tick is on the upper left of US keyboards under the tilda ~ character.

Using back ticks will allow multi-line strings including SQL statements. It also allows token replacement of any JavaScript variable by adding a dollar sign before it and wrapping it with curly braces. In other words, if you have a variable called myVariable, you can replace it with the value by placing ${myVariable} anywhere in the string provided you open and close the string literal with back ticks.

CREATE or REPLACE PROCEDURE TST_30102020()
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
var stmt = snowflake.createStatement({
           sqlText:`COPY INTO @storage/folder/file_1.csv

                     FROM (SELECT t1.ATTR,t1.ATTR, t2.ATTR, t2.ATTR, t1.ATTR, t1.MIASTO, t1.ATTR, t1.ATTR,
                                    t1.ATTR, t1.ATTR, t1.ATTR, t1.ATTR, t1.ATTR, t1.ATTR,
                                    t1.ATTR, t1.ATTR, t1.ATTR, t1.ATTR, t1.ATTR, t1.KONCESJA_A, t1.ATTR, t1.ATTR
                           FROM t1

                           LEFT JOIN (SELECT * FROM table WHERE ATTR >= '2020-09-01') AS t2
                                  ON t1.ATTR= t2.ATTR AND t1.ATTR= t2.ATTR

                           LEFT JOIN (SELECT * FROM table WHERE ATTR>= '2020-09-01') AS t3
                                  ON t2.ATTR= t3.ATTR
                                  AND t2.ATTR= t3.ATTR
                                  AND t2.ATTR= t3.ATTR

                           WHERE t2.ATTR> 0
                           ORDER BY t2.ATTR, t1.ATTR)

                    FILE_FORMAT = (TYPE = 'CSV')
                    OVERWRITE = TRUE
                    SINGLE = TRUE 
                    MAX_FILE_SIZE = 170000000`});
    
try {
    stmt.execute();
    return "Succeeded.";
  }

catch (err) {
    return "Failed: " + err;
  }

$$;

Final note... Check the color coding for the code samples right here on Stackoverflow. You'll notice the one you provided changes color when it encounters the date because it has a single quote character there. It stays the same color though when the quote is opened and closed with back ticks in the modified code (only change made to it).

Upvotes: 1

Related Questions