Snowflake JavaScript stored procedures

Strengths

Limitations

Examples: setup

USE ROLE ACCOUNTADMIN;
USE WAREHOUSE COMPUTE_WH;
USE DATABASE SNOWFLAKE_SAMPLE_DATA;
USE SCHEMA TPCH_SF1;
CREATE DATABASE DEV_SANDBOX;
USE DATABASE DEV_SANDBOX;
CREATE SCHEMA TPCH_SF1;
USE SCHEMA TPCH_SF1;
CREATE TABLE DEV_SANDBOX.TPCH_SF1.CUSTOMER AS
SELECT *
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER;
--Create a logging table for later use.
CREATE OR REPLACE TABLE TPCH_SF1.NEGATIVE_BALANCE_CUSTOMERS (
ROW_ID NUMBER(38, 0) AUTOINCREMENT(1, 1)
,CUSTOMER_KEY NUMBER(38, 0)
,CUSTOMER_NAME VARCHAR(25)
,MARKET_SEGMENT VARCHAR(25)
,ACCOUNT_BALANCE NUMBER(12, 2)
,LOG_TIMESTAMP TIMESTAMP_NTZ(6) DEFAULT CURRENT_TIMESTAMP(6)
);

Starter query

--This starter query is the basis for some of the examples below.
SELECT
C_CUSTKEY
,C_NAME
,C_ADDRESS
,C_MKTSEGMENT
FROM TPCH_SF1.CUSTOMER
WHERE 1=1
AND C_NATIONKEY = 8
AND C_MKTSEGMENT IN('AUTOMOBILE', 'BUILDING', 'MACHINERY')
AND C_ACCTBAL > 9900;

Starter query — formatted as JSON

--This update to the starter query formats the data as a JSON object.
SELECT '[' || LISTAGG(CAST(D.CUSTOMER_ROWS AS VARCHAR), ',') || ']' AS CUSTOMER_DETAILS
FROM
(
SELECT OBJECT_CONSTRUCT
(
'customer_key', C.C_CUSTKEY
,'customer_name', C.C_NAME
,'customer_address', C.C_ADDRESS
,'market_segment', C.C_MKTSEGMENT
) AS CUSTOMER_ROWS
FROM TPCH_SF1.CUSTOMER AS C
WHERE 1=1
AND C_NATIONKEY = 8
AND C_MKTSEGMENT IN('AUTOMOBILE', 'BUILDING', 'MACHINERY')
AND C_ACCTBAL > 9900
) AS D;

Basic stored procedure

--DROP PROCEDURE TPCH_SF1.SP_BASIC_SQL_CALL_WITH_PARAMETER(VARCHAR)
CREATE OR REPLACE PROCEDURE TPCH_SF1.SP_BASIC_SQL_CALL_WITH_PARAMETER(MARKET_SEGMENT VARCHAR(10))
RETURNS FLOAT
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS
$$
/****************************************************************************
Project Name: Logic20/20 Training
Desc: This stored procedure runs a SQL statement and returns the number of rows in the CUSTOMER table for the specified market segment.Inputs: MARKET_SEGMENT - The market segment for which the row count should be returned. Examples: AUTOMOBILE, BUILDING, MACHINERY.Outputs: The number of rows returned by the SQL statement.Created Date: 06/10/2022
Created By: Jason Kind
*****************************************************************************
CHANGE HISTORY
*****************************************************************************
Date Author Description
********** ********* ************************************************
06/10/2022 Jason Kind Created stored procedure
*****************************************************************************/
let retVal = -1;
let sql = "SELECT * FROM TPCH_SF1.CUSTOMER WHERE C_MKTSEGMENT = '" + MARKET_SEGMENT + "';";
let rs = snowflake.execute({sqlText: sql});

retVal = rs.getRowCount(); //The Snowflake getRowCount() method returns the number of rows returned from a SQL query.
return retVal;
$$;
/*
CALL TPCH_SF1.SP_BASIC_SQL_CALL_WITH_PARAMETER('AUTOMOBILE');
CALL TPCH_SF1.SP_BASIC_SQL_CALL_WITH_PARAMETER('BUILDING');
*/

Stored procedure with JSON output

--DROP PROCEDURE TPCH_SF1.SP_BASIC_SQL_CALL_WITH_PARAMETER_JSON_OUTPUT(VARCHAR)
CREATE OR REPLACE PROCEDURE TPCH_SF1.SP_BASIC_SQL_CALL_WITH_PARAMETER_JSON_OUTPUT(MARKET_SEGMENT VARCHAR(10))
RETURNS VARIANT
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS
$$
/****************************************************************************
Project Name: Logic20/20 Training
Desc: This stored procedure runs a SQL statement and returns the output data
in JSON format.
Inputs: MARKET_SEGMENT - The market segment for which the JSON formatted data
should be returned. Examples: AUTOMOBILE, BUILDING, MACHINERY.
Outputs: The output data in JSON format.Created Date: 06/10/2022
Created By: Jason Kind
*****************************************************************************
CHANGE HISTORY
*****************************************************************************
Date Author Description
********** ********* ************************************************
06/10/2022 Jason Kind Created stored procedure
*****************************************************************************/
let retVal = "";

/*
Note: There are a few different options for handling complex, multi-line SQL statements. Using backticks is probably the cleanest approach. See the following for more details: https://docs.snowflake.com/en/sql-reference/stored-procedures-javascript.html#line-continuation
*/
let sql = `SELECT '[' || LISTAGG(CAST(D.CUSTOMER_ROWS AS VARCHAR), ',') || ']' AS CUSTOMER_DETAILS
FROM
(
SELECT OBJECT_CONSTRUCT
(
'customer_key', C.C_CUSTKEY
,'customer_name', C.C_NAME
,'customer_address', C.C_ADDRESS
,'market_segment', C.C_MKTSEGMENT
) AS CUSTOMER_ROWS
FROM TPCH_SF1.CUSTOMER AS C
WHERE 1=1
AND C_NATIONKEY = 8
AND C_MKTSEGMENT = '` + MARKET_SEGMENT + `'
AND C_ACCTBAL > 9900
) AS D;`

/*
Use the return statement temporarily throughout your code to output the results. In
this case, we want to see if the SQL string we're trying to execute is properly
formatted. This is a good debugging technique and becomes more important as the
SQL statements get more complex.
*/
//return sql;

let rs = snowflake.execute({sqlText: sql});
rs.next(); //Advance to the first and, in this case only, row in the recordset.

retVal = JSON.parse(rs.getColumnValue("CUSTOMER_DETAILS"));
return retVal;
$$;
/*
CALL TPCH_SF1.SP_BASIC_SQL_CALL_WITH_PARAMETER_JSON_OUTPUT('AUTOMOBILE');
CALL TPCH_SF1.SP_BASIC_SQL_CALL_WITH_PARAMETER_JSON_OUTPUT('MACHINERY');
CALL TPCH_SF1.SP_BASIC_SQL_CALL_WITH_PARAMETER_JSON_OUTPUT('MACHINERY '); //Invalid spaces in the MARKETING_SEGMENT argument - No data returned.
CALL TPCH_SF1.SP_BASIC_SQL_CALL_WITH_PARAMETER_JSON_OUTPUT('Machinery'); //Invalid casing in the MARKETING_SEGMENT argument - No data returned.
*/

Stored procedure with JSON output and internal functions

--DROP PROCEDURE TPCH_SF1.SP_BASIC_SQL_CALL_WITH_INTERNAL_FUNCTIONS(VARCHAR)
CREATE OR REPLACE PROCEDURE TPCH_SF1.SP_BASIC_SQL_CALL_WITH_INTERNAL_FUNCTIONS(MARKET_SEGMENT VARCHAR(10))
RETURNS VARIANT
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS
$$
/****************************************************************************
Project Name: Logic20/20 Training
Desc: This stored procedure runs a SQL statement and returns the output data
in JSON format.
Inputs: MARKET_SEGMENT - The market segment for which the JSON formatted data
should be returned. Examples: AUTOMOBILE, BUILDING,
MACHINERY.
Outputs: The output data in JSON format.Created Date: 06/10/2022
Created By: Jason Kind
*****************************************************************************
CHANGE HISTORY
*****************************************************************************
Date Author Description
********** ********* *************************************************
06/10/2022 Jason Kind Created stored procedure
*****************************************************************************/
/********** BEGIN - Global functions **********/
/*
Applies logic to prep inputs for use. Specifically, the following corrective
actions are applied:
- Trims whitespace from the beginning and/or end
- Converts input to uppercase if needed
*/
function cleanInputs(inputToClean, convertToUpper) {
let retVal = inputToClean.trim();

if(convertToUpper) {
retVal = retVal.toUpperCase();
}

return retVal;
}

/*
Retrieve the current system date as a formatted string.
*/
function getCurrentDate() {
let retVal = "";

let rs = snowflake.execute({sqlText: "SELECT TO_VARCHAR(CURRENT_TIMESTAMP(), 'YYYY-MM-DD') AS CURRENT_DATE;"});
rs.next();

retVal = rs.getColumnValue("CURRENT_DATE");

return retVal;
};
/********** END - Global functions **********/
let retVal = "";
let currentDate = getCurrentDate();
let sql = `SELECT '[' || LISTAGG(CAST(D.CUSTOMER_ROWS AS VARCHAR), ',') || ']' AS CUSTOMER_DETAILS
FROM
(
SELECT OBJECT_CONSTRUCT
(
'customer_key', C.C_CUSTKEY
,'customer_name', C.C_NAME
,'customer_address', C.C_ADDRESS
,'market_segment', C.C_MKTSEGMENT
,'system_date', '` + currentDate + `'
) AS CUSTOMER_ROWS
FROM TPCH_SF1.CUSTOMER AS C
WHERE 1=1
AND C_NATIONKEY = 8
AND C_MKTSEGMENT = '` + cleanInputs(MARKET_SEGMENT, true) + `'
AND C_ACCTBAL > 9900
) AS D;`
/*
Use the return statement temporarily throughout your code to output the results. In
this case, we want to see if the SQL string we're trying to execute is properly
formatted. This is a good debugging technique and becomes more important as the
SQL statements get more complex.
*/
//return sql;

let rs = snowflake.execute({sqlText: sql});
rs.next(); //Advance to the first and, in this case only, row in the recordset.

retVal = JSON.parse(rs.getColumnValue("CUSTOMER_DETAILS"));
return retVal;
$$;
/*
CALL TPCH_SF1.SP_BASIC_SQL_CALL_WITH_INTERNAL_FUNCTIONS('AUTOMOBILE');
CALL TPCH_SF1.SP_BASIC_SQL_CALL_WITH_INTERNAL_FUNCTIONS('MACHINERY');
CALL TPCH_SF1.SP_BASIC_SQL_CALL_WITH_INTERNAL_FUNCTIONS('MACHINERY '); //Invalid spaces in the MARKETING_SEGMENT argument
CALL TPCH_SF1.SP_BASIC_SQL_CALL_WITH_INTERNAL_FUNCTIONS('Machinery '); //Invalid casing in the MARKETING_SEGMENT argument
*/

Stored procedure with threshold logging

--DROP PROCEDURE TPCH_SF1.SP_JSON_PROCESSING(VARCHAR, FLOAT)
CREATE OR REPLACE PROCEDURE TPCH_SF1.SP_JSON_PROCESSING(MARKET_SEGMENT VARCHAR(10), MINIMUM_BALANCE_THRESHOLD FLOAT)
RETURNS VARIANT
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS
$$
/****************************************************************************
Project Name: Logic20/20 Training
Desc: This stored procedure runs a SQL statement to retrieve data in JSON format and then loops through it to add additional details.Inputs: MARKET_SEGMENT - The market segment for which the JSON formatted data
should be returned. Examples: AUTOMOBILE, BUILDING, MACHINERY.

MINIMUM_BALANCE_THRESHOLD - The minimum negative account balance allowed. Any accounts that are <= to this number should be logged. Note: The parameter expects a FLOAT data type rather than the NUMBER(12,2) data type. This is because JavaScript will ultimately be working with the value provided and JavaScript doesn't support the NUMBER data type. Attempting to set the parameter to NUMBER(12,2) will result in the following compilation error: SQL Error [90215] [42601]: Language JAVASCRIPT does not support type 'NUMBER(12,2)' for argument or return type.
Outputs: The output data in JSON format, with a new attribute named flag_account_balance added.Created Date: 06/10/2022
Created By: Jason Kind
*****************************************************************************
CHANGE HISTORY
*****************************************************************************
Date Author Description
********** ********* ************************************************
06/10/2022 Jason Kind Created stored procedure
*****************************************************************************/
/********** BEGIN - Global functions **********/
/*
Applies logic to prep inputs for use. Specifically, the following corrective
actions are applied:
- Trims whitespace from the beginning and/or end
- Converts input to uppercase if needed
*/
function cleanInputs(inputToClean, convertToUpper) {
let retVal = inputToClean.trim();

if(convertToUpper) {
retVal = retVal.toUpperCase();
}

return retVal;
}
/*
Wrapper function to log accounts with balances below threshold.
*/
function logNegativeBalanceBelowThreshold
(
customerKey
,customerName
,marketSegment
,accountBalance
) {
let sql = `INSERT INTO TPCH_SF1.NEGATIVE_BALANCE_CUSTOMERS (CUSTOMER_KEY, CUSTOMER_NAME, MARKET_SEGMENT, ACCOUNT_BALANCE)
VALUES (` + customerKey + `, '` + customerName + `', '` + marketSegment + `', ` + accountBalance + `);
`
snowflake.execute({sqlText: sql});
}
/********** END - Global functions **********/
let customerJSON = {}; //Initialize JSON container variables to an empty JSON object.
let sql = `SELECT '[' || LISTAGG(CAST(D.CUSTOMER_ROWS AS VARCHAR), ',') || ']' AS CUSTOMER_DETAILS
FROM
(
SELECT OBJECT_CONSTRUCT
(
'customer_key', C.C_CUSTKEY
,'customer_name', C.C_NAME
,'market_segment', C.C_MKTSEGMENT
,'account_balance', C.C_ACCTBAL
) AS CUSTOMER_ROWS
FROM TPCH_SF1.CUSTOMER AS C
WHERE 1=1
AND C_NATIONKEY = 8
AND C_MKTSEGMENT = '` + cleanInputs(MARKET_SEGMENT, true) + `'
AND C_ACCTBAL < 0
) AS D;
`
/*
Use the return statement temporarily throughout your code to output the results. In
this case, we want to see if the SQL string we're trying to execute is properly
formatted. This is a good debugging technique and becomes more important as the
SQL statements get more complex.
*/
//return sql;

let rs = snowflake.execute({sqlText: sql});
rs.next(); //Advance to the first and, in this case only, row in the recordset.

customerJSON = JSON.parse(rs.getColumnValue("CUSTOMER_DETAILS"));
/*
Note: flag_account_balance is not an attribute in the original JSON. JSON allows the addition or modification of attributes during runtime.
*/
for(customerRow in customerJSON) {
if(customerJSON[customerRow].account_balance < MINIMUM_BALANCE_THRESHOLD) {
customerJSON[customerRow].flag_account_balance = true;

logNegativeBalanceBelowThreshold
(
customerJSON[customerRow].customer_key
,customerJSON[customerRow].customer_name
,customerJSON[customerRow].market_segment
,customerJSON[customerRow].account_balance
);
}
else {
customerJSON[customerRow].flag_account_balance = false;
}

//Convert the account balance to a string for nicer formatting. Otherwise, it will be returned in scientific notation.
customerJSON[customerRow].account_balance = customerJSON[customerRow].account_balance.toString();
}
return customerJSON;
$$;
/*
CALL TPCH_SF1.SP_JSON_PROCESSING('AUTOMOBILE', -980);
CALL TPCH_SF1.SP_JSON_PROCESSING('MACHINERY', -980);
SELECT * FROM TPCH_SF1.NEGATIVE_BALANCE_CUSTOMERS ORDER BY ROW_ID;
*/

Helpful tools

  1. Snowflake free trial
  2. Snowflake stored procedure documentation
  3. Online JavaScript Editor: Useful for testing out your JavaScript code outside of Snowflake
  4. JavaScript tutorials:

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store