Python UDF handler examples¶

Prerequisites¶

Before your Python handler code can read a file on a stage, you must do the following to make the file available to the code:

1. Create a stage that is available to your handler.

   You can use an external stage or internal stage. If you use an internal stage, it can be a user stage when you plan to create a caller’s rights stored procedure. Otherwise, you must use a named stage. Snowflake does not currently support using a table stage for UDF dependencies.

   For more on creating a stage, see CREATE STAGE. For more on choosing an internal stage type, see Choosing an internal stage for local files.

   Adequate privileges on the stage must be assigned to the following role, depending on your use case:

   Use case	Role
   UDF or owner’s rights stored procedure	The role that owns the executing UDF or stored procedure.
   Caller’s rights stored procedure	The user role.

   For more information, see Granting privileges for user-defined functions.

2. Copy the file that your code will read to the stage.

   You can copy the file from a local drive to an internal stage using the PUT command. For information on staging files with PUT, see Staging data files from a local file system.

   You can copy the file from a local drive to an external stage location using any of the tools provided by your cloud storage service. For help, see the documentation for your cloud storage service.

Calculating the perceptual hash of an image with an in-line Python handler¶

This example uses SnowflakeFile to read a pair of staged image files and use the perceptual hash (pHash) of each file to determine how similar the images are to each other.

Create a UDF that returns the phash value of an image, specifying the input mode as binary by passing rb for the mode argument:

CREATE OR REPLACE FUNCTION calc_phash(file_path string)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = '3.9'
PACKAGES = ('snowflake-snowpark-python','imagehash','pillow')
HANDLER = 'run'
AS
$$
from PIL import Image
import imagehash
from snowflake.snowpark.files import SnowflakeFile

def run(file_path):
  with SnowflakeFile.open(file_path, 'rb') as f:
  return imagehash.average_hash(Image.open(f))
$$;

Create a second UDF that calculates the distance between the phash values of two images:

CREATE OR REPLACE FUNCTION calc_phash_distance(h1 string, h2 string)
RETURNS INT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.9'
PACKAGES = ('imagehash')
HANDLER = 'run'
as
$$
import imagehash

def run(h1, h2):
  return imagehash.hex_to_hash(h1) - imagehash.hex_to_hash(h2)
$$;

Stage the image files and refresh the directory table:

PUT file:///tmp/image1.jpg @images AUTO_COMPRESS=FALSE;
PUT file:///tmp/image2.jpg @images AUTO_COMPRESS=FALSE;

ALTER STAGE images REFRESH;

Call the UDFs:

SELECT
  calc_phash_distance(
    calc_phash(build_scoped_file_url(@images, 'image1.jpg')),
    calc_phash(build_scoped_file_url(@images, 'image2.jpg'))
  ) ;

Processing a CSV file with a UDTF¶

This example uses SnowflakeFile to create a UDTF that extracts the contents of a CSV file and returns the rows in a table.

Create the UDTF with an in-line handler:

CREATE FUNCTION parse_csv(file_path string)
RETURNS TABLE (col1 string, col2 string, col3 string)
LANGUAGE PYTHON
RUNTIME_VERSION = '3.9'
PACKAGES = ('snowflake-snowpark-python')
HANDLER = 'csvparser'
AS
$$
from snowflake.snowpark.files import SnowflakeFile

class csvparser:
  def process(self, stagefile):
    with SnowflakeFile.open(stagefile) as f:
      for line in f.readlines():
        lineStr = line.strip()
        row = lineStr.split(",")
        try:
          # Read the columns from the line.
          yield (row[1], row[0], row[2], )
        except:
          pass
$$;

Stage the CSV file and refresh the directory table:

PUT file:///tmp/sample.csv @data_stage AUTO_COMPRESS=FALSE;

ALTER STAGE data_stage REFRESH;

Call the UDTF, passing a file URL:

SELECT * FROM TABLE(PARSE_CSV(build_scoped_file_url(@data_stage, 'sample.csv')));

Processing multiple files¶

You can read and process multiple files by passing the RELATIVE_PATH column of a directory table to your handler. For more information on the RELATIVE_PATH column, see the output from a directory table query.

Call a UDF to process multiple files:

The following example calls a UDF within a CREATE TABLE statement to process each file on a stage and then store the results in a new table.

For demonstration purposes, the example assumes the following:

• There are multiple text files on a stage named my_stage.

• There is an existing UDF named get_sentiment that performs sentiment analysis on unstructured text. The UDF takes a path to a text file as input and returns a value that represents sentiment.

CREATE OR REPLACE TABLE sentiment_results AS
SELECT
  relative_path
  , get_sentiment(build_scoped_file_url(@my_stage, relative_path)) AS sentiment
FROM directory(@my_stage);

Copy

Call a UDTF to process multiple files:

This next example calls a UDTF named parse_excel_udtf. The example passes the relative_path from the directory table on the stage named my_excel_stage.

SELECT t.*
FROM directory(@my_stage) d,
table(parse_excel_udtf(build_scoped_file_url(@my_excel_stage, relative_path)) t;

Copy

Reading files with stage URIs and URLs¶

File access with SnowflakeFile requires scoped URLs by default. This makes your code resilient to file injection attacks. However, you can refer to a file location using a stage URI or a stage URL instead. To do so, you must call the SnowflakeFile.open method with the keyword argument require_scoped_url = False.

This option is useful when you want to let a caller provide a URI that is accessible only to the UDF owner. For example, you might use a stage URI for file access if you own a UDF and you want to read in your configuration files or machine learning models. We do not recommend this option when you work with files that have unpredictable names, such as files that are created based on user input.

This example reads a machine learning model from a file and uses the model in a function to perform natural language processing for sentiment analysis. The example calls the open with require_scoped_url = False. In both file location formats (stage URI and stage URL), the UDF owner must have access to the model file.

Create the UDF with an in-line handler:

CREATE OR REPLACE FUNCTION extract_sentiment(input_data string)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = '3.9'
PACKAGES = ('snowflake-snowpark-python','scikit-learn')
HANDLER = 'run'
AS
$$
from snowflake.snowpark.files import SnowflakeFile
from sklearn.linear_model import SGDClassifier
import pickle

def run(input_data):
  model_file = '@models/NLP_model.pickle'
  # Specify 'mode = rb' to open the file in binary mode.
  with SnowflakeFile.open(model_file, 'rb', require_scoped_url = False) as f:
    model = pickle.load(f)
    return model.predict([input_data])[0]
$$;

Stage the model file and refresh the directory table:

PUT file:///tmp/NLP_model.pickle @models AUTO_COMPRESS=FALSE;

ALTER STAGE models REFRESH;

Alternatively, you can specify the UDF with the model’s stage URL to extract the sentiment.

For example, create a UDF with an in-line handler that specifies a file using a stage URL:

CREATE OR REPLACE FUNCTION extract_sentiment(input_data string)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = '3.9'
PACKAGES = ('snowflake-snowpark-python','scikit-learn')
HANDLER = 'run'
AS
$$
from snowflake.snowpark.files import SnowflakeFile
from sklearn.linear_model import SGDClassifier
import pickle

def run(input_data):
  model_file = 'https://my_account/api/files/my_db/my_schema/models/NLP_model.pickle'
  # Specify 'rb' to open the file in binary mode.
  with SnowflakeFile.open(model_file, 'rb', require_scoped_url = False) as f:
    model = pickle.load(f)
    return model.predict([input_data])[0]
$$;

Call the UDF with the input data:

SELECT extract_sentiment('I am writing to express my interest in a recent posting made.');

Copy