将应用程序逻辑添加到应用程序包

本主题介绍如何将应用程序逻辑添加到应用程序包的安装脚本中。本主题还介绍了如何在应用程序包中使用外部代码文件。

See Add a Streamlit app for information about including a Streamlit in Snowflake app in an application package.

使用存储过程和函数的注意事项

The Snowflake Native App Framework allows you to include stored procedures, user-defined functions (UDFs), and external functions in an application package. These can be written in any of the languages Snowflake supports.

If you plan to publish your Snowflake Native App to the Snowflake Marketplace as a limited trial listing and want to limit the functionality of your application that is available to those trial consumers, see Preparing to offer a limited trial listing.

安全地添加应用程序代码

All stored procedures and UDFs within a Snowflake Native App run as the application and have access to all objects within the installed Snowflake Native App. This can lead to SQL injection attacks.

When developing procedures and functions for use within a Snowflake Native App, Snowflake recommends that all SQL commands requiring input from users be run using bound parameters. This includes input provided through procedure arguments.

See Creating a stored procedure for more information.

关于调用方权限和所有者权限

All procedures created by the setup script or that run within the installed Snowflake Native App must be run with the rights of the owner (EXECUTE AS OWNER).

This restriction exists because if the Snowflake Native App were to run with caller’s rights (EXECUTE AS CALLER) in a procedure that the Snowflake Native App does not own, the procedure would run as the Snowflake Native App itself and allow a consumer to create code to view or modify the contents of the Snowflake Native App and shared data content.

See Understanding caller’s rights and owner’s rights stored procedures for more information.

从安装脚本调用上下文函数时的限制

Context functions provide information about the context in which a statement is run. Within the context of the Snowflake Native App Framework, some context functions are not available. Context functions that are not available are either blocked and return an error or always return a null value.

Use caution when using context functions in policies applied to shared data content within a Snowflake Native App. Some functions, for example CURRENT_IP_ADDRESS, behave differently in the context of a Snowflake Native App.

在使用依赖于客户端组织内的命名空间的上下文函数时，可能会与其他命名空间中的函数发生冲突。例如，使用 CURRENT_USER 的行访问策略时，应注意同一用户名可以存在于多个账户中。

When using a Streamlit (https://streamlit.io/) app within a Snowflake Native App, context functions have additional constraints. For example, CURRENT_USER returns NULL when invoked from Streamlit in Snowflake.

The following table lists the context functions that are not supported by the Snowflake Native App Framework:

Supported versions of Python in a Snowflake Native App¶

For information on the versions of Python that Snowflake supports, see Snowflake Python Runtime Support.

作为提供商，您必须确保您的应用程序使用受支持的 Python 版本。应用程序无法创建使用已停用 Python 版本的函数。此外，您无法创建或发布尝试创建使用已停用 Python 版本的函数的应用程序的新版本。

无法安装使用已停用 Python 版本的应用程序的现有已发布版本。

在应用程序中使用 Snowpark 函数和过程¶

The Snowflake Native App Framework supports the Snowpark libraries for creating stored procedures in Java, Scala, and Python.

引用外部代码文件

应用程序包中可以包含两种类型的代码文件：

• Referenced files: include binaries, libraries and other code files. These files are specific to a version defined in the application package. These files must be located in the root directory of the stage when creating or adding a version.

引用的文件不同于用户定义的函数和存储过程，因为它们未在应用程序包的安装脚本中定义。这些文件由安装脚本中定义的存储过程和 UDFs 中的导入语句所引用。

• Resource files: include semi-structured data, structured data, and binaries, for example, a machine learning model. These files must be uploaded to a named stage that is accessible to the application package.

必须在安装脚本的版本化架构中创建引用这些类型的代码文件的存储过程、用户定义的函数或外部函数。在版本化架构中创建存储过程或函数时，必须引用相对于命名暂存区的根目录的代码文件。

For example, if the root directory of the named stage is /app_files/dev, this directory would contain the following files and directories:

• 清单文件。
• A directory containing the setup script, for example scripts/setup_version.sql.
• Referenced files that are imported when creating a stored procedure, UDF, or external function within the setup script, for example:
  • libraries/jars/lookup.jar
  • libraries/jars/log4j.jar
  • libraries/python/evaluate.py

在此场景中，目录结构如下所示：

@DEV_DB.DEV_SCHEMA.DEV_STAGE/V1:
└── app_files/
    └── dev
        ├── manifest.yml
        └── scripts/
            ├── setup_script.sql
            └── libraries/
                └── jars/
                    ├── lookup.jar
                    └── log4j.jar
            └── python
                └── evaluation.py

若要访问此目录结构中的 JAR 文件，安装脚本中定义的存储过程将引用这些文件，如以下示例所示：

CREATE PROCEDURE PROGRAMS.LOOKUP(...)
  RETURNS STRING
  LANGUAGE JAVA
  PACKAGES = ('com.snowflake:snowpark:latest')
  IMPORTS = ('/scripts/libraries/jar/lookup.jar',
             '/scripts/libraries/jar/log4j.jar')
  HANDLER = 'com.acme.programs.Lookup';

在此示例中，IMPORTS 语句有相对于用于创建版本的根目录的路径，例如，清单文件的位置。

在应用程序包中包含 Java 及 Scala 代码¶

The Snowflake Native App Framework supports using Java and Scala in stored procedures and in external code files.

以内联方式创建 Java 和 Scala UDFs¶

The Snowflake Native App Framework supports creating stored procedures containing Java and Scala. The code that defines the stored procedure must be added to the setup script.

以下示例显示了包含 Java 函数的存储过程：

CREATE OR ALTER VERSIONED SCHEMA app_code;
CREATE STAGE app_code.app_jars;

CREATE FUNCTION app_code.add(x INT, y INT)
  RETURNS INTEGER
  LANGUAGE JAVA
  HANDLER = 'TestAddFunc.add'
  TARGET_PATH = '@app_code.app_jars/TestAddFunc.jar'
  AS
  $$
  class TestAddFunc {
    public static int add(int x, int y) {
      Return x + y;
    }
  }
  $$;

导入外部 Java 及 Scala UDFs¶

创建预编译 UDFs 的语法要求将导入的 JARs 作为一组版本化工件的一部分包含在内。要引用预编译 JARs，请使用相对路径，而不是在 IMPORT 子句中指定整个暂存区的位置。

The path must be relative to the root directory containing the version starting with a single forward slash, for example IMPORTS = ('/path/to/JARs/from/version/root'). See 引用外部代码文件 for more information on relative paths.

以下示例显示了代码文件的示例目录结构。

@DEV_DB.DEV_SCHEMA.DEV_STAGE/V1:
└── V1/
    ├── manifest.yml
    ├── setup_script.sql
    └── JARs/
        ├── Java/
        │   └── TestAddFunc.jar
        └── Scala/
            └── TestMulFunc.jar

以下示例演示如何使用 JAR 文件创建 Java 函数：

CREATE FUNCTION app_code.add(x INTEGER, y INTEGER)
  RETURNS INTEGER
  LANGUAGE JAVA
  HANDLER = 'TestAddFunc.add'
  IMPORTS = ('/JARs/Java/TestAddFunc.jar');

对 Java 和 Scala UDFs 的限制¶

The Snowflake Native App Framework imposes the following restrictions when using Java and Scala:

• 只有在版本化架构中创建的 UDFs 才允许导入。
• 导入只能使用相对路径访问版本工件。
• UDFs 在版本化架构之外创建的架构只能以内联方式创建。
• TARGET_PATH 不支持相对路径。

将 Python 代码添加到应用程序包¶

The Snowflake Native App Framework supports using Python in stored procedures and in external code files.

在安装脚本中定义 Python 函数¶

The Snowflake Native App Framework supports creating stored procedures in Python.

以下示例显示了包含 Python 函数的存储过程：

CREATE FUNCTION app_code.py_echo_func(str STRING)
  RETURNS STRING
  LANGUAGE PYTHON
  HANDLER = 'echo'
AS
$$
def echo(str):
  return "ECHO: " + str
$$;

使用外部 Python 文件¶

以下示例演示如何在应用程序包中包含外部 Python 文件：

CREATE FUNCTION PY_PROCESS_DATA_FUNC()
  RETURNS STRING
  LANGUAGE PYTHON
  HANDLER = 'TestPythonFunc.process'
  IMPORTS = ('/python_modules/TestPythonFunc.py',
    '/python_modules/data.csv')

See to 引用外部代码文件 for more information on relative paths.

对 Python UDFs 的限制¶

Snowflake Native App Framework imposes the following restrictions on Python UDFs:

• 只有在版本化架构中创建的 UDFs 才允许导入。
• 导入只能使用相对路径访问版本工件。
• UDFs 在版本化架构之外创建的架构只能以内联方式创建。

向应用程序包添加 JavaScript 函数和过程¶

The Snowflake Native App Framework supports using JavaScript in stored procedures and user-defined functions using the JavaScript API.

处理 JavaScript 错误¶

在应用程序包中使用 JavaScript 时，Snowflake 建议您捕获并处理错误。如果不这么做，则错误返回的错误消息和堆栈跟踪对使用者可见。若要确保数据内容和应用程序逻辑保持私密，请在访问敏感对象或数据的情况下使用 try/catch 块。

以下示例演示捕获错误并返回消息的 JavaScript 存储过程：

CREATE OR REPLACE PROCEDURE APP_SCHEMA.ERROR_CATCH()
  RETURNS STRING
  LANGUAGE JAVASCRIPT
  EXECUTE AS OWNER
  AS $$
    try {
      let x = y.length;
    }
    catch(err){
      return "There is an error.";
    }
    return "Done";
  $$;

This example creates a JavaScript stored procedure that contains a try/catch block. If the stored procedure encounters an error when running the statement in the try block, it returns the message “There is an error” which is visible to the consumer.

如果没有 try/catch 块，存储过程将返回原始错误消息和使用者可见的完整堆栈跟踪。

将外部函数添加到应用程序包

External functions allow a Snowflake Native App to make calls to application code that is hosted outside of Snowflake. External functions require you to create an API Integration object.

Because API integrations allow connectivity outside of the consumer environment, the consumer must provide the method of integration to the Snowflake Native App.

以下示例演示由安装脚本创建的存储过程，该脚本接受集成并创建外部函数。此示例演示如何在应用程序包的安装脚本中创建外部函数：

CREATE OR REPLACE PROCEDURE calculator.create_external_function(integration_name STRING)
  RETURNS STRING
  LANGUAGE SQL
  EXECUTE AS OWNER
  AS
  DECLARE
    CREATE_STATEMENT VARCHAR;
  BEGIN
    CREATE_STATEMENT := 'CREATE OR REPLACE EXTERNAL FUNCTION EXTERNAL_ADD(NUM1 FLOAT, NUM2 FLOAT)
        RETURNS FLOAT API_INTEGRATION = ? AS ''https://xyz.execute-api.us-west-2.amazonaws.com/production/sum'';' ;
    EXECUTE IMMEDIATE :CREATE_STATEMENT USING (INTEGRATION_NAME);
    RETURN 'EXTERNAL FUNCTION CREATED';
  END;

GRANT USAGE ON PROCEDURE calculator.create_external_function(string) TO APPLICATION ROLE app_public;

This example defines a stored procedure, written in SQL, and creates an external function that references an application hosted on a system outside of Snowflake. The external function returns an API integration.

This example also grants USAGE on the stored procedure to an application role. The consumer must grant this privilege to the Snowflake Native App before invoking this procedure in the setup script.