Snowflake Native Apps framework: Porting an existing API to Snowflake

What is the Snowflake Native Apps framework?

The Snowflake native app framework (currently in preview for AWS-accounts) allows users to create and share data-applications with other snowflake accounts. The applications can be as simple as a custom stored procedure or an entire app with a Streamlit interface and custom tables.

Building a Snowflake Native App with external access table of contents:

1. External access in Snowflake

2. Building the app

3. Putting it all together

💡 You currently have to have a snowflake account with AWS as a cloud provider in a non gov-region.

External access in Snowflake:

• Creating a secret in snowflake

• Setting up a network rule in snowflake

• Setting up an external access integration in snowflake

• Setting up an external function in snowflake

Creating a secret in snowflake (Optional)

CREATE SECRET MY_API_SECRET
    TYPE = GENERIC_STRING
    SECRET_STRING = '<your-api-key>';

Setting up a network rule in snowflake

CREATE NETWORK RULE MY_NETWORK_RULE
    MODE = EGRESS
    TYPE = HOST_PORT
    VALUE_LIST = ('<external-domain>');

Setting up an external access integration in snowflake

CREATE EXTERNAL ACCESS INTEGRATION MY_EXTERNAL_INTEGRATION
    ALLOWED_NETWORK_RULES = (MY_NETWORK_RULE)
    ALLOWED_AUTHENTICATION_SECRETS = (MY_API_SECRET)
    ENABLED = true;

Setting up an external function in snowflake

//Example function of a get-request with a single argument and the api-key as bearer token
CREATE SECURE FUNCTION CALL_YOUR_API(argument varchar)
    RETURNS STRING
    LANGUAGE PYTHON
    RUNTIME_VERSION = 3.9
    HANDLER = 'call_api'
    EXTERNAL_ACCESS_INTEGRATIONS = (MY_EXTERNAL_INTEGRATION)
    SECRETS = ('API_KEY' = MY_API_SECRET)
    PACKAGES = ('requests','urllib3')
    AS
$$
import _snowflake
import requests
from urllib.parse import urlencode
def call_api(argument):
    my_api_key = _snowflake.get_generic_secret_string('API_KEY')
    request_url = "<url-to-request>"
    headers = {
        "accept": "application/json",
        "Content-Type": "application/json",
        "Authorization": f"Bearer {my_api_key}",
    }
    response = requests.get(request_url + "?" + urlencode({"argument":argument}),headers=headers)
    return response.text
$$;

Calling the function from snowflake

select CALL_YOUR_API('my argument');

Building the app

App hierarchy

readme.md
manifest.yml
streamlit
|-- app.py
|-- environment.yml
scripts
|-- setup.sql

Creating a manifest-file

manifest_version: 1
artifacts:
  setup_script: scripts/setup.sql
  readme: readme.md
  extension_code: true

Creating a setup-script

CREATE APPLICATION ROLE app_public;

CREATE OR ALTER VERSIONED SCHEMA code_schema;
GRANT USAGE ON SCHEMA code_schema TO APPLICATION ROLE app_public;

CREATE STREAMLIT code_schema.your_streamlit_app
  FROM '/streamlit'
  MAIN_FILE = '/app.py'
;

GRANT USAGE ON STREAMLIT code_schema.your_streamlit_app TO APPLICATION ROLE app_public;

Creating a streamlit app

import streamlit as st
from snowflake.snowpark.context import get_active_session
import snowflake.permissions as permission
import json

st.title("Hello world")

# Get the current credentials
session = get_active_session()

Set the environment for the streamlit app

name: sf_env
channels:
- snowflake
dependencies:
- snowflake-native-apps-permission

Putting it all together

Setting your function-reference

💡 You have to add a version to the app to be able to set and use references

Adding the reference to the manifest:

references:
  - function_reference:
      label: "External function"
      description: "Function to external resource"
      privileges:
        - USAGE
      object_type: FUNCTION
      register_callback: code_schema.update_reference

Setting the reference in the streamlit app

request_attribute = "Some attribute"
# Fetching the function reference
function_reference = permission.get_reference_associations("function_reference")
if len(function_reference) == 0:
    permission.request_reference("function_reference")
else:
    results = session.sql(f"SELECT reference('function_reference')('{request_attribute}')")
		st.write("Success!")
		st.write(results)

Adding a reference setter in the setup-script

create or replace procedure code_schema.update_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, :ref_or_alias);
    when 'CLEAR' then
       select system$remove_all_references();
    else
       return 'Unknown operation: ' || operation;
  end case;
  return 'Success';
end;
$$;

grant usage on procedure code_schema.update_reference(string, string, string) to application role app_public;

Adding a CI-script to run as a github-action

name: CI

on:
  # Triggers the workflow on main
  push:
    branches:
      - "main"
    tags:
      - "v*"

  workflow_dispatch:

jobs:
  deploy:
    runs-on: ubuntu-latest

    env:
      SNOWSQL_PWD: ${{ secrets.SNOWFLAKE_PASSWORD }}
      SNOWSQL_ACCOUNT: ${{ secrets.SNOWFLAKE_ACCOUNT }}
      SNOWSQL_USER: ${{ secrets.SNOWFLAKE_USERNAME }}
      SNOWSQL_DATABASE: ${{ secrets.SNOWFLAKE_DATABASE }}
      SNOWSQL_SCHEMA: ${{ secrets.SNOWFLAKE_SCHEMA }}
      SNOWSQL_ROLE: ${{ secrets.SNOWFLAKE_ROLE }}
      SNOWSQL_WAREHOUSE: ${{ secrets.SNOWFLAKE_WAREHOUSE }}

    steps:
      - uses: actions/checkout@v3

      - name: Install SnowSQL
        run: |
          curl -O <https://sfc-repo.snowflakecomputing.com/snowsql/bootstrap/1.2/linux_x86_64/snowsql-1.2.9-linux_x86_64.bash>
          SNOWSQL_DEST=~/bin SNOWSQL_LOGIN_SHELL=~/.profile bash snowsql-1.2.9-linux_x86_64.bash
      - name: Upload artifact to Snowflake
        env:
          GITHUB_ACTION_PATH: ${{ github.action_path }}
        run: |
          ~/bin/snowsql -q 'put file://'$(pwd)'/manifest.yml @<your-application-package>.<your-stage-schema>.<your-stage> overwrite=true auto_compress=false;'
          ~/bin/snowsql -q 'put file://'$(pwd)'/scripts/setup.sql @<your-application-package>.<your-stage-schema>.<your-stage>/scripts overwrite=true auto_compress=false;'
          ~/bin/snowsql -q 'put file://'$(pwd)'/readme.md @<your-application-package>.<your-stage-schema>.<your-stage> overwrite=true auto_compress=false;'
          ~/bin/snowsql -q 'put file://'$(pwd)'/streamlit/app.py @<your-application-package>.<your-stage-schema>.<your-stage>/streamlit overwrite=true auto_compress=false;'
          ~/bin/snowsql -q 'put file://'$(pwd)'/streamlit/environment.yml @<your-application-package>.<your-stage-schema>.<your-stage>/streamlit overwrite=true auto_compress=false;'
      - name: Create or update Application
        run: |
          ~/bin/snowsql -q 'ALTER APPLICATION PACKAGE <your_application-package> DROP VERSION V1_0;
                            ALTER APPLICATION PACKAGE <your_application-package>
                              ADD VERSION V1_0 USING '@<your-application-package>.<your-stage-schema>.<your-stage>';
                            DROP APPLICATION IF EXISTS <your-application>;
                            CREATE APPLICATION <your-application>
                              FROM APPLICATION PACKAGE <your_application-package>
                              USING VERSION V1_0;'

Now you are all done!

Feel free to reach out if you have any questions.