Using Requests to Access External Endpoints in Snowflake Native Apps

				
					CREATE OR ALTER VERSIONED SCHEMA CONFIG;
GRANT USAGE ON SCHEMA CONFIG TO APPLICATION ROLE EXAMPLE_APPLICATION_ROLE;


CREATE OR REPLACE PROCEDURE config.register_reference(ref_name STRING, operation STRING, ref_or_alias STRING)
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
 CASE (operation)
   WHEN 'ADD' THEN
     SELECT SYSTEM$SET_REFERENCE(:ref_name, :ref_or_alias);
   WHEN 'REMOVE' THEN
     SELECT SYSTEM$REMOVE_REFERENCE(:ref_name);
   WHEN 'CLEAR' THEN
     SELECT SYSTEM$REMOVE_REFERENCE(:ref_name);
   ELSE
     RETURN 'unknown operation: ' || operation;
 END CASE;
 RETURN '';
END;
$$;


GRANT USAGE ON PROCEDURE config.register_reference(STRING, STRING, STRING) TO APPLICATION ROLE EXAMPLE_APPLICATION_ROLE;


-- Table to dynamically set the EAI url
CREATE OR REPLACE TABLE CONFIG.URL (
 REF_NAME VARCHAR,
 URL VARCHAR
);


-- Create configuration callback function for external access integrations and secrets
CREATE OR REPLACE PROCEDURE config.get_config_for_ref(ref_name STRING)
returns string
language python
runtime_version=3.11
packages=('snowflake-snowpark-python')
handler='main'
as $$


from snowflake.snowpark import Session
from snowflake.snowpark.functions import col
import json


def main(session:Session,ref_name:str):


 try:
   ref_type = ref_name.split('_')[0].lower()


   if ref_type == 'secret':
     return get_secret_config()
   elif ref_type == 'eai':
     return get_eai_config(session,ref_name)
   else:
     return get_error_config('No reference type found in config callback function.')
 except Exception as e:
   return get_error_config(str(e))


def get_error_config(message):
  config = {
     "type": "ERROR",
     "payload":{
         "message": message
     }
 }


 return json.dumps(config)


def get_secret_config():
  config = {
         "type": "CONFIGURATION",
         "payload":{
           "type" : "GENERIC_STRING" # Value may be replaced with "PASSWORD" for a username and password type secret
         }
 }


 return json.dumps(config)


def get_eai_config(session,ref_name):
  url = session.table('config.url').filter(col('ref_name')==ref_name.upper()).select(col('url')).collect()[0][0]


 config = {
     "type": "CONFIGURATION",
     "payload": {
         "host_ports": [url],
         "allowed_secrets": "ALL"
     }
 }


 return json.dumps(config)
$$;


GRANT USAGE ON PROCEDURE config.get_config_for_ref(STRING) TO APPLICATION ROLE EXAMPLE_APPLICATION_ROLE;