Snowflake Snowpark JAVA UDF with JAR — Streamlit bonus — dbaonTap

4 min readAug 3, 2022

“How can I manage my Snowflake JAVA UDFs without needing to re-create the UDF every time I make a change to the logic? And by the way can you show me how to invoke that from Streamlit?”
The first thing that popped in my head was, “Streamlit is a Python based library, so how do we ‘call’ a JAVA program?” This was short lived, but yeah.
The steps below will walk you through creating a basic Java program to add two numbers which will be referenced from a Streamlit application leveraging a Snowflake Java UDF.

edit (09/13/2022):
Read how@stewartbryson makes the process of deploying JAVA UDFs to Snowflake much cooler for developers.

public class AddNumbers
{
  public String addNum(int num1, int num2) {
    try {
      int sum = num1 + num2;
      return ("Sum is: " + sum);
    } catch (Exception e) {
      return e.toString();
      }
    }

  public static void main(String[] args){
      System.out.println("Done");
  }
}

Create Class file:

To avoid errors when creating the UDF, you must specify the JAVA version by using the appropriate -source and -target values.

javac -source 11 -target 11 AddNumbers.java

I am running openJDK 16 and did get a warning message when executing this step. I did not research it as it ultimately didn’t impact the outcome of this exercise. This should result in a file called AddNumbers.class in the same directory.

Create Manifest file:

Create a file with following text (note there are 3 lines. 2 with text and the last is a blank)

>Manifest-Version: 1.0 
>Main-Class: AddNumbers.class 
>

Create JAR file:

Once your CLASS and MANIFEST files have been created the final step from here is to generate the Java ARchive (JAR) file.

jar cmf AddNumbers.manifest ./AddNumbers.jar AddNumbers.class

The resulting file, AddNumbers.jar will be used in subsequent steps.

The simplest way to execute the following steps is to log into Snowflake using the CLI, SnowSQL.

Create internal stage:

CREATE or REPLACE STAGE myjars;

Upload JAR to internal stage:

You will note the syntax is different based upon your OS. Only one of these is necessary

Using the JAR file definitely simplifies the construction of the UDF as we no longer have to include the JAVA code. Since we have our JAR file in a Snowflake STAGE, importing that file and referencing the HANDLER appropriately.

CREATE or REPLACE FUNCTION AddMyNumbers (a integer, b integer) returns string 
language JAVA 
imports = ('@myjars/AddNumbers.jar') 
handler = 'AddNumbers.addNum';

Let’s test the AddMyNumbers UDF by executing the following SQL in SnowSQL:

SELECT AddMyNumbers(1,2);

If everything was completed successfully, you should get the following output:

+-------------------+
| ADDMYNUMBERS(1,2) |
|-------------------|
| Sum is: 3         |
+-------------------+        
1 Row(s) produced. Time Elapsed: 1.032s

Finally, let’s take a quick look at how we can run this in Streamlit? With the Snowflake announcements at Summit about , the accompanying code will be written in Snowpark Python .

import streamlit as st
import pandas as pd
from snowflake.snowpark import *
from snowflake.snowpark import Session, version, Window, Row
from snowflake.snowpark.functions import col, call_builtin

# init connection
@st.experimental_singleton
def init_connection(username,password,acctname):
   SF_USER = username
   SF_PASSWORD = password
   SF_ACCOUNT = acctname
   connection_parameters = {
           "account"     : SF_ACCOUNT,
           "user"        : SF_USER,
           "password"    : SF_PASSWORD,
                      }

   session = Session.builder.configs(connection_parameters).create()
   return (session)

def main():
   """Simple Login App"""
   st.sidebar.title("Login")
   st.sidebar.subheader("Snowflake Login")


   username = st.sidebar.text_input("User Name")
   password = st.sidebar.text_input("Password",type='password')
   acctname = st.sidebar.text_input("Account URL")

   if st.sidebar.button("Login"):

# Call to pass parameters to the login function
       result = init_connection(username,password,acctname)
       if result:    
           # Display connection information upon successful login
           env = (result.sql('SELECT current_version(), current_user(), current_account(), current_role()').collect())
           st.success("Logged In as {}".format(env)) 

    # Execute query to invoke JAVA UDF AddMyNumbers
           sum = (result.sql('select ADDMYNUMBERS (1,2)')).collect()
           sum = str(sum[0][0])
           st.write(sum)     
       else:
           st.warning("Incorrect Username/Password")

if __name__ == '__main__':
   main()

Save this file with a .py extension and if you have Streamlit and Snowpark Python installed correctly, you can execute this code by entering the following on the command line:

streamlit run <filename.py>

Streamlit will automatically launch a browser. Enter the appropriate credentials based upon where you created your UDF and you should see the following: