Error calling JavaScript based Stored Procedure in Snowflake

Question

I am trying to read an API using JavaScript. Since Snowflake Stored Procedures are JavaScript based, I tried the following Stored Procedure.

create or replace procedure myschema.stproc1()
    returns string
    language javascript
    as
    $$
        // Create a request variable and assign a new XMLHttpRequest object to it.
        var request = new XMLHttpRequest();
        
        // Open a new connection, using the GET request on the URL endpoint
        request.open('GET', 'https://myappi.application.com/external/api/rest/v2/rooms/?includeCancelled=true&includePastReservations=true&limit=500', true);
        
        request.onload = function () {
          // Begin accessing JSON data here
        }
        
        // Send request
        request.send();
    $$
    ;

And, I am getting the below error when trying to call the Stored Procedure:

JavaScript execution error: Uncaught ReferenceError: XMLHttpRequest is not defined in STPROC1 at ' var request = new XMLHttpRequest();' position 22 stackstrace: STPROC1 line: 3

I am fairly new to both JavaScript and Procedures in Snowflake. So, couldn't figure out what the issue is. I am using this code based out of reference from here.

You cannot call things outside of Snowflake. XMLHttpRequest is doing a call outside Snowflake, but that is not allowed. Check the available functionality of Javascript API: docs.snowflake.com/en/sql-reference/stored-procedures-api.html — Sergiu
Thanks @Sergiu! I've gone through this earlier and didn't quite understand. So, is it not possible to read an API from Snowflake? — Julaayi

Felipe Hoffa Felipe Hoffa · Accepted Answer · 2021-02-08T22:00:57

To expand on Sergiu's comment:

If you want to call an external API from within Snowflake, you need to go through an external function setup:

https://docs.snowflake.com/en/sql-reference/external-functions-introduction.html

For the simplest deploy, use the Serverless plugin:

https://github.com/starschema/serverless-snowflake-external-function-plugin

Error calling JavaScript based Stored Procedure in Snowflake

1 Answers