Open in app

Sign in

Write

Sign in

Building Snowflake Native Applications with external APIs

In this blog post, we’ll guide you through the process of building a Snowflake Native App that not only harnesses the power of The Snowflake Data Cloud, but also extends its capabilities by leveraging external APIs and encapsulating these APIs inside an app and allowing it to be shared by an application developer/provider with its internal enterprise users or with its external vendors/partners/customers. Get ready to dive into a world where Snowflakes Native Apps meet APIs, creating a synergy that unlocks a whole new level of data-driven possibilities.

Understanding the Snowflake Native App Frameworks:

Before we embark on our journey, let’s briefly understand how Snowflake Native Apps are created. The Snowflake Native App Framework allows you to create data applications that leverage core Snowflake functionality and distribute your app on the Snowflake Marketplace. The Snowflake Native App Framework allows you to:

  • Expand the capabilities of other Snowflake features by sharing data and related business logic with other Snowflake accounts. The business logic of an application can include a Streamlit app, stored procedures, and functions written using Snowpark API, JavaScript, and SQL.
  • Share an application with consumers through listings. A listing can be either free or paid. You can distribute and monetize your apps in the Snowflake Marketplace or distribute them to specific consumers using private listings.
  • Include rich visualizations in your application using Streamlit.

More on the Snowflake Native App Framework

Setting Up Your Snowflake Environment:

Snowflake Account:

  • Ensure you have a Snowflake account. If not, sign up for one here. In this example we are using two accounts, one that acts as an application provider account and another that acts as an application consumer account.

Snowflake Client:

  • Install the Snowflake client to connect your application with Snowflake. You can find the necessary resources in the Snowflake documentation.

Snowflake Web Interface:

Building the Snowflake Native App:

Now that your environment is set up, let’s start building our app:

1.Connect to Snowflake account

Using the Snowsight connect to your snowflake account that you will be using for creating and sharing an application with your app consumers/customers.

2. Building a Snowflake Native App

Building a Snowflake Native App requires you to prepare application files or any other reference files that are required by the application

  • readme.md ( optional )
  • manifest.yml
  • setup.sql
  • Streamlit_app.py ( optional )
  • Setup.sql — Setup.sql file contains the code that executes in the consumer account when an application is installed. Below is an example of a setup script that creates an external api access to a publicly available endpoint. This code creates an application role, schema in the application database , network rule to hit the endpoint, a stored procedure that creates a UDF in the consumer account to be able to call the api endpoints.

For this example we will be using a free api that requires no keys called Zippopotam . This free API returns information about a specific zip code.

https://api.zippopotam.us/us/33162

In order for us to use this API inside snowflake we will be creating an external function with a return type as table. This function will pull records from the API and allow it to be queried directly from SQL on a snowflake warehouse. We will take it further and include this function inside a SnowflakeNative App and share it with application consumers.

Creating network rules

Network rules are schema-level objects that allow access to an external network or location. These network rules are used by the stored procedure or UDF’s while accessing an external network location.

Note: A network rule does not define whether its identifiers should be allowed or blocked. The Snowflake feature such as UDF’s /SP’s that uses the network rule specifies whether the identifiers in the rule are permitted or prohibited.

Below is a sample snippet for creating a network rule that will allow access to the zippopotamus api. We are creating this using our native application’s setup.sql file.

Note: The role used to create a network rule should have CREATE NETWORK RULE privileges.

Setup.SQL

CREATE APPLICATION ROLE app_public;
CREATE OR ALTER VERSIONED SCHEMA core;
grant usage on schema core to APPLICATION role app_public;
create procedure core.create_netwrok_rule()
returns string
language javascript
execute as owner
as
'
try
{
var create_statement = "create or replace network rule api_network_rule mode = EGRESS TYPE = HOST_PORT VALUE_LIST = (\'api.zippopotam.us\') ;";
var stmt = snowflake.createStatement( {sqlText: create_statement } );
stmt.execute();
var grant_statement = "grant usage on network rule api_network_rule to application role app_public ;";
var stmt2 = snowflake.createStatement( {sqlText: grant_statement } );
stmt2.execute();
return ''Network rule created'';
}
catch (err)
{
var result = "";
result = "Failed: Code: " + err.code + "\\n State: " + err.state;
result += "\\n Message: " + err.message;
result += "\\nStack Trace:\\n" + err.stackTraceTxt;
return result;
}
return ''success'';
';
grant usage on procedure core.create_netwrok_rule() to application role app_public;
create procedure core.create_external_function(integration_name varchar)
returns string
language python
RUNTIME_VERSION = 3.10
packages = ('snowflake-snowpark-python')
handler='create_external_function'
execute as owner
as
$$
def create_external_function(session, integration_name):
base_statement = f"""CREATE FUNCTION core.ext_access_test(zipcode varchar)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = 3.8
HANDLER = 'test'
EXTERNAL_ACCESS_INTEGRATIONS = ({integration_name})
PACKAGES = ('urllib3')
AS
\'
import urllib3
def test(zipcode):
http = urllib3.PoolManager()
url = "https://api.zippopotam.us/us/"+zipcode
resp = http.request("GET", url )
return str(resp.data.decode())
\';
"""
return session.sql("begin " + base_statement + " end;").collect()
$$;
create procedure core.grant_usage()
returns string
language javascript
execute as owner
as
'
try
{
var grant_statement = "grant usage on function core.ext_access_test( varchar) to application role app_public ;";
var stmt2 = snowflake.createStatement( {sqlText: grant_statement } );
stmt2.execute();
return ''granted'';
}
catch (err)
{
var result = "";
result = "Failed: Code: " + err.code + "\\n State: " + err.state;
result += "\\n Message: " + err.message;
result += "\\nStack Trace:\\n" + err.stackTraceTxt;
return result;
}
return ''success'';
';
grant usage on procedure core.create_external_function(varchar) to application role app_public;
grant usage on procedure core.grant_usage() to application role app_public;
CREATE OR REPLACE PROCEDURE CORE.HELLO()
RETURNS STRING
LANGUAGE SQL
EXECUTE AS OWNER
AS
BEGIN
RETURN 'HELLO SNOWFLAKE!';
END;
GRANT USAGE ON PROCEDURE CORE.HELLO() TO APPLICATION ROLE APP_PUBLIC;
  • Application package db — A database that contains your application files and code.
create application package if not exists webAPI_application_package;
use application package webAPI_application_package;
create schema app_pkg_schema;
use schema webAPI_application_package.app_pkg_schema;
create or replace stage app_pkg_schema.appFiles;

--Below PUT commands to be executed in a CLI or upload files manually to the above created stage using snowsight.

PUT file://///Users/dev/setup_script.sql     @webAPI_application_package.app_pkg_schema.appfiles overwrite=true auto_compress=false;

PUT file://///Users/dev/manifest.yml     @webAPI_application_package.app_pkg_schema.appfiles overwrite=true auto_compress=false;

--List Files to verify upload
ls @webAPI_application_package.app_pkg_schema.appfiles ;

-- Create a version of the application using the application files you created in the stage 

ALTER APPLICATION PACKAGE webAPI_application_package
  ADD VERSION ver1
  USING '@webAPI_application_package.app_pkg_schema.appfiles';

-- Set a default release directive for application to be published.
ALTER APPLICATION PACKAGE webAPI_application_package
  SET DEFAULT RELEASE DIRECTIVE
    VERSION = ver1 patch = 0;
  • Creating a listing — A public or private listing that a provider shares with a consumer.
-- Using provider studio create a listing using the application package just created above
--Verify the listing has been created
 show listings like 'webAPI%' in data exchange snowflake_data_marketplace;

Installing and running a Snowflake Native App with external access

We have successfully built a native application and created a listing for the application, shared with a consumer account. Now let’s see what a consumer needs to do to be able to use the application and make calls to an external api’s.

  1. Getting an application/ installing an application — Once an application has been listed and shared with a consumer account the consumers can see that app in snowsight under apps tab. Clicking on the get button will start the install process. This step runs the setup.sql script in that account.

2. Setting up application — Application setup requires us to call the stored procedures created by the application to create a network rule in the account, create an external integration using the network rule and calling a stored procedure that will create the external UDF to call the API

— Assuming that the application was installed with a name “webapi”

use database webapi;
use schema webapi.core;
 - Grant the application role to the role that will be using the application and also has create integration privelleges on the account , in our example we are using the accoutnadmin role but it could be any role with the required permissions.
grant application role app_public to role accountadmin;
grant create integration on account to role accountadmin;
 - query current network rules in account
show network rules;
 - call the SP to create a network rule in the account
call create_netwrok_rule();
 - Validate the rule exists
show network rules;
 - Create an external access integration usign the network rule.
CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION apis_integration
ALLOWED_NETWORK_RULES = (API_NETWORK_RULE)
ENABLED = true;
 - GRANT usage on the integration to application to be able to create a UDF using the integration
grant usage on integration apis_integration to application webapi;
 - Call the SP to create the UDF to access external api
call core.create_external_function('apis_integration');
 - Below call grants usage permissions on the external UDF to the application
call grant_usage();

Let’s query the external UDF which is calling our API

select ext_access_test('94536') ;

We have now successfully created an application with external access and shared with a consumer and installed that application in a consumer account with external access UDF.

References

https://docs.snowflake.com/en/developer-guide/external-network-access/external-network-access-overview

Snowflake

--

--

Ajita Sharma
Snowflake Builders Blog: Data Engineers, App Developers, AI/ML, & Data Science

Written by Ajita Sharma

20 Followers
Writer for

Help

Status

About

Careers

Press

Blog

Privacy

Terms

Text to speech

Teams