Until recently it was not possible to make network requests in a Snowflake user defined function (UDF) or stored procedure. However, there a many use cases which require access to the internet and therefore Snowflake developed a concept which allows to explicitly grant access to defined network locations.
This blogpost outlines a simple example of the steps which are required to interact with the internet from a Python UDF. We will create a function which retrieves a generated list of Todo items from JSON placeholder, a fake API for testing and prototyping. Let’s get started.
The first Snowflake object we need is a network rule which allows access to jsonplaceholder.typicode.com :
use role accountadmin;
create or replace network rule typicode_rule
mode = egress
type = host_port
value_list = ('jsonplaceholder.typicode.com');
We use this network rule in a new external access integration object:
create or replace external access integration typicode_int
allowed_network_rules = (typicode_rule)
enabled = true;
We now have the ability to reference this external access integration in our UDF definition:
create or replace function fetch_todos()
returns table (userId varchar, id varchar, title varchar, completed boolean)
runtime_version = 3.8
handler = 'fetchTodos'
external_access_integrations = (typicode_int)
packages = ('snowflake-snowpark-python','requests')
resp = requests.get(url)
jData = json.loads(resp.content)
for todo in jData:
That’s it. We can now invoke this UDF to get Todo items from our test API:
select * from table(fetch_todos());
This feature is the moment of writing this blog available to specific regions in AWS only. Please reach out to your account team to get more information.