Snowflake JavaScript stored procedures

• 24 Jun 2022
• Written by: Jason Kind
• Developer
• Snowflake

8-minute read

 
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)
);

 
--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;

 
--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;

 
--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');
*/

 
--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.
*/

 
--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
*/

 
--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;
*/