New in Snowflake : external network access

Photo by Stephen Phillips - Hostreviews.co.uk on Unsplash

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)
language python
runtime_version = 3.8
handler = 'fetchTodos'
external_access_integrations = (typicode_int)
packages = ('snowflake-snowpark-python','requests')
AS
$$
import requests
import json

class fetchTodos:
    def process(self):
    
        url='https://jsonplaceholder.typicode.com/todos'
        resp = requests.get(url)

        if(resp.ok):
            jData = json.loads(resp.content)

            for todo in jData:
                yield(todo['userId'],todo['id'],todo['title'],todo['completed'])
           
$$;

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.

Update: This feature is also available on Azure since early November except for the Government region.