Making Dependencies Available to Your Code

When your user-defined function (UDF) or stored procedure depends on code or files that are external to the UDF or procedure, you can make the dependency available to the UDF or procedure by uploading it to a stage, then referencing it at that location.

For example, you might want your UDF or procedure to have access to the following:

  • Python handler code in a module.

  • Java or Scala handler code compiled and packaged in a JAR.

  • Dependency code written in Java, Python, or Scala.

  • Files to be read by your handler code and whose name and location is known when you create the UDF. This can be useful with configuration files, for example.

Note

You can also use the PACKAGES clause of CREATE FUNCTION or CREATE PROCEDURE to import libraries that are included in Snowflake.

High-Level Steps

Follow these steps to make dependencies available to your function or procedure.

  1. Choose or create a stage that’s available to your handler.

  2. Upload the dependency to the stage.

  3. Reference the dependency with IMPORTS when you create the function or procedure.

Choosing or Creating a Stage for Dependency Files

To make your dependency file available to a function or procedure, you’ll need to copy the dependency file to a stage where it can be reached at runtime. The owner of the function or procedure must have the READ privilege to the stage.

Typically, you upload the dependency to a named internal stage using the PUT command. For more about creating stages, see CREATE STAGE.

Note

You can’t execute the PUT command through the Snowflake GUI; you can use SnowSQL to execute PUT. For an example PUT command to copy a .jar file to a stage, see Uploading the Dependency to the Stage in this topic.

Choose or create one of the following kinds of stage for your dependency:

  • A user or named internal stage.

    If you plan to use the PUT command to upload the files, use a named internal stage. For more on choosing an internal stage type, see Choosing an Internal Stage for Local Files.

  • An external stage.

    External stages are locations associated with external storage services, as described in CREATE STAGE. The PUT command does not support uploading files to external stages.

You can use an existing stage or you can create a new stage by executing CREATE STAGE. For example, the following command creates a new internal stage named mystage:

CREATE STAGE mystage;
Copy

Note

Snowflake does not currently support using a table stage to store handler code.

Uploading the Dependency to the Stage

Upload the files required for your stored procedure to a stage.

If you’re using an external stage, use that storage service’s means for uploading files. If you’re using an internal stage, you can copy the file from a local drive to the stage by using the PUT command. For command reference, see PUT. For information on staging files with PUT, see Staging Data Files from a Local File System.

Use the PUT command to upload files to the stage.

Code in the following example uploads myjar.jar to a stage called mystage, overwriting an existing file of the same name if it exists.

PUT file:///Users/MyUserName/MyCompiledJavaCode.jar
  @mystage
  AUTO_COMPRESS = FALSE
  OVERWRITE = TRUE
  ;
Copy

Note

If you omit AUTO_COMPRESS = FALSE, the PUT command automatically compresses the file. The name of the compressed file on the stage will be myjar.jar.gz. Later, when you execute a command such as CREATE PROCEDURE, you will need to specify the filename with this .gz extension in the command’s IMPORTS clause.

Note

The PUT command does not support uploading files to external stages. To upload files to external stages, use the utilities provided by the cloud service.

Referencing the Dependency

To make a function or procedure you’re creating aware of the dependency’s location, specify the dependency’s location in the IMPORTS clause of the SQL you use to create the function or procedure.

If you have multiple dependency files, such as when you have third-party libraries on which a handler depends, you can specify the stage location and file path-and-name of all dependency files as values of the IMPORTS clause.

Code in the following example creates a procedure called MYPROC, specifying that the file MyCompiledJavaCode.jar (on the mystage stage) should be included in the procedure’s execution environment. In this case, MyCompiledJavaCode.jar contains the procedure’s handler – the compiled code for MyJavaClass.run.

CREATE OR REPLACE PROCEDURE MYPROC(value INT, fromTable STRING, toTable STRING, count INT)
  RETURNS INT
  LANGUAGE JAVA
  RUNTIME_VERSION = '11'
  PACKAGES = ('com.snowflake:snowpark:latest')
  IMPORTS = ('@mystage/MyCompiledJavaCode.jar')
  HANDLER = 'MyJavaClass.run';
Copy
Language: English