在 Clean Room 中上传和运行自定义函数¶

概述¶

您可以将自定义 Python UDFs 和 UDTFs 上传到您的 Clean Room 中，然后通过模板运行它们来执行复杂的数据操作。这些操作包括机器学习或查询中的自定义数据操作，是单步或多步 流程 的一部分。Python 是唯一支持自定义 UDFs 的编码语言。

您上传的代码可以导入和使用 经批准的 Python 包捆绑包 (https://repo.anaconda.com/pkgs/snowflake/) 和 Snowpark API 中的包。

Both providers and consumers can upload custom Python code into a clean room, although the process is different for providers and consumers.

This topic shows how to upload and run custom Python UDFs and UDTFs as either a provider or a consumer.

SELECT cleanroom.simple_add({{ price | sqlsafe | int }}, {{ tax | sqlsafe | int }}) ...

SELECT
  cleanroom.simple_add(
    {{ price | sqlsafe | int }}, {{ tax | sqlsafe | int }}
    ) AS TOTAL_ITEM_COST
...

提供商提交的代码¶

提供商提交的函数可以作为内联代码上传，也可以从 Snowflake 暂存区上传。本文会介绍这两种技术。

您上传的代码可以原生导入并使用 一组经批准的 Python 包 (https://repo.anaconda.com/pkgs/snowflake/) 中的包。如果您需要非默认包，则必须 在 Clean Room 中使用 Snowpark Container Services 来托管代码。

You can't view uploaded provider code, even your own code, so be sure to include a copy of exactly what you upload into a clean room.

下面简要介绍了提供商如何向 Clean Room 添加代码：

1. 提供商以正常方式创建和配置 Clean Room。

2. 提供商通过调用 provider.load_python_into_cleanroom 上传代码。您可以直接在该过程中 内联上传代码，也可以 将代码文件上传到某个暂存区，然后将暂存区位置提供给该过程。

   Although your code can include multiple functions, only one handler is exposed for each upload. To expose multiple functions to templates, upload each handler by calling provider.load_python_into_cleanroom.

3. 每次成功上传代码后，都会生成一个 新的 Clean Room 补丁版本。然后，您必须使用新的补丁号调用 provider.set_default_release_directive，以递增默认版本号。如果 Clean Room 对外公开，则在安装代码之前会运行安全检查，并且在递增默认版本号之前，您必须调用 provider.view_cleanroom_scan_status 以确认安全检查已通过。

   • If you want to upload multiple functions in a single patch, you can bulk upload your code. However, bulk uploads can make it more challenging to debug if the upload has a security scan issue, because the file that caused the problem isn't reported in the error response.

4. You create and upload a custom template that calls your code. The template must call the handler function using the cleanroom scope, that is: cleanroom.my_function(...).

5. 使用者按照运行其他任何模板的相同方式运行您的模板。

   小技巧

   如果使用者在安装包含自定义代码的 Clean Room 时遇到挂载错误，这可能表明代码中存在语法错误。

您可以在 提供商编写的代码示例部分 中找到演示此流程的代码示例。

CALL samooha_by_snowflake_local_db.provider.load_python_into_cleanroom(
$cleanroom_name,
'simple_add',                         -- Name used to call the UDF from a template.
['first INTEGER', 'second INTEGER'],  -- Arguments of the UDF, specified as '<variable_name> <SQL type>' pairs.
['numpy', 'pandas'],                  -- Packages imported by the UDF.
'INTEGER',                            -- SQL return type of UDF.
'add_two',                            -- Handler function in your code called when external name is called.
$$
import numpy as np   # Not used, but you can load supported packages.
import pandas as pd

def add_two(first, second):
    return first + second
$$
);

-- Save the following code as reverser.py:
--import numpy as np
--def main(some_string):
--  '''Return the reverse of a string plus a random number 1-10'''
--  return some_string[::-1] + str(np.random.randint(1,10))

-- Get the stage for your clean room.
CALL samooha_by_snowflake_local_db.provider.get_stage_for_python_files($cleanroom_name);

-- Save the file to the stage. Here is how to do it by using the Snowflake CLI
PUT file://~/reverser.py <STAGE_NAME> overwrite=True auto_compress=False;

-- Load the code from the stage into the clean room.
CALL samooha_by_snowflake_local_db.provider.load_python_into_cleanroom(
    $cleanroom_name,
    'reverse', -- Name used to call the function
    ['some_string  STRING'], -- Arguments and SQL types
    ['numpy'],               -- Any required packages
    ['/reverser.py'],        -- Relative path to file on stage
    'STRING',                -- Return type
    'reverser.main'          -- <FILE_NAME>.<FUNCTION_NAME>
);

-- Uploading code, even from a stage, increases the patch number.
CALL samooha_by_snowflake_local_db.provider.set_default_release_directive(
  $cleanroom_name, 'V1_0', <NEW_PATCH_NUMBER>);

-- Upload a template that calls the function.
CALL samooha_by_snowflake_local_db.provider.add_custom_sql_template(
    $cleanroom_name,
    $udf_template_name,
    $$
    SELECT
      p.status,
      cleanroom.reverse(p.status)
    FROM SAMOOHA_SAMPLE_DATABASE.DEMO.CUSTOMERS AS p
    LIMIT 100;
    $$
);

-- Switch to the consumer account and run the template to see the results.

---- Add custom inline UDF ----
CALL samooha_by_snowflake_local_db.provider.prepare_python_for_cleanroom(
    $cleanroom_name,
    'get_next_status',  -- Name of the UDF. Can be different from the handler.
    ['status VARCHAR'], -- Arguments of the UDF, specified as (variable name, SQL type).
    ['numpy'],          -- Packages needed by UDF.
    [],                 -- When providing the code inline, this is an empty array.
    'VARCHAR',          -- Return type of UDF.
    'get_next_status',  -- Handler.
    $$
import numpy as np
def get_next_status(status):
  """Return the next higher status, or a random status
  if no matching status found or at the top of the list."""

  statuses = ['MEMBER', 'SILVER', 'GOLD', 'PLATINUM', 'DIAMOND']
  try:
    return statuses[statuses.index(status.upper()) + 1]
  except:
    return 'NO MATCH'
    $$
);

---- Add custom inline UDTF. ----
CALL samooha_by_snowflake_local_db.provider.prepare_python_for_cleanroom(
    $cleanroom_name,
    'get_info',  -- Name of the UDTF. Can be different from the handler.
    ['hashed_email VARCHAR', 'days_active INT', 'status VARCHAR', 'income VARCHAR'],   -- Name/Type arguments of the UDTF.
    ['numpy'],         -- Packages used by UDTF.
    [],                -- When providing the code inline, this is an empty array.
    'TABLE(hashed_email VARCHAR, months_active INT, level VARCHAR)',  -- Return type of UDTF.
    'GetSomeVals',     -- Handler class name.
$$
class GetSomeVals:
  def __init__(self):
    self.month_days = 30

  def process(self, hashed_email, days_active, status, income):
    '''Change days into rough months, and also return whether we
    think the user's membership status is lower, higher, or equal to
    what is expected, based on their income.'''

    months_active = days_active // self.month_days
    brackets = ['0-50K', '50K-100K', '100K-250K', '250K+']
    statuses = ['MEMBER', 'SILVER', 'GOLD', 'PLATINUM']
    if(statuses.index(status) < brackets.index(income)):
      level = 'low'
    elif(statuses.index(status) > brackets.index(income)):
      level = 'high'
    else:
      level = 'equal'

    yield(hashed_email, months_active, level)
$$
);

-- Upload all stored procedures.
-- Note the new patch number returned by this procedure. Keep this number for later use.
CALL samooha_by_snowflake_local_db.provider.load_prepared_python_into_cleanroom($cleanroom_name);

-- Set the release directive specified by the last load_python_into_cleanroom call.
CALL samooha_by_snowflake_local_db.provider.set_default_release_directive($cleanroom_name, 'V1_0', <PATCH_NUMBER>);

使用者提交的代码¶

Consumer-uploaded code is bundled and uploaded with a custom template using the consumer template upload flow. The uploaded code can be called by any template in the clean room.

To upload code as a consumer, you should understand custom template syntax.

Note that any code uploaded by a consumer can be seen by the provider when they request permission to upload. The consumer code is also visible whenever a provider or consumer examines the template.

以下是上传自定义使用者代码的步骤概述：

1. 提供商以标准方式创建 Clean Room，然后邀请使用者。

2. 使用者以标准方式安装和配置 Clean Room。

3. The consumer prepares a template that calls the UDF or UDTF within the cleanroom namespace. For example, to call the consumer-defined calculate_tax function, a simple template might look like the following snippet:

   SELECT {{ cleanroom.calculate_tax(p.cost) }} AS Tax FROM my_db.my_sch.sales AS p;
   

   Copy

4. The consumer prepares their Python code. We recommend using double quotation marks (" ") rather than single quotation marks (' ') in your code to avoid extra escaping needed later. Your code can reference these supported Python libraries (https://repo.anaconda.com/pkgs/snowflake/).

5. 使用者将其 Python 代码传递到 consumer.generate_python_request_template 中。该过程以存储过程的形式返回 Python 代码，其中包含自定义 JinjaSQL 模板的占位符。模板中有几个将 $$ 用作多行分隔符的多行字符串。

6. The consumer replaces the template placeholder in the output from generate_python_request_template with their JinjaSQL template.

7. In the combined template, escape any single quotes like this: \'. This is because single quotes will be used as the outermost delimiter for the entire multi-line procedure string when you upload it to the clean room. Here is an example of a stored procedure that includes the consumer Python code and custom template, with character escaping:

     BEGIN
   
     CREATE OR REPLACE FUNCTION CLEANROOM.custom_compare(min_status STRING, max_status STRING, this_status STRING)
     RETURNS boolean
     LANGUAGE PYTHON
     RUNTIME_VERSION = 3.10
     PACKAGES = (\'numpy\')
   
     HANDLER = \'custom_compare\'
     AS $$
     import numpy as np
   
     def custom_compare(min_status:str, max_status:str, this_status:str):
       statuses = [\'MEMBER\', \'SILVER\', \'GOLD\', \'PLATINUM\']
       return ((statuses.index(this_status) >= statuses.index(min_status)) &
               (statuses.index(this_status) <= statuses.index(max_status)))
     $$;
   
     -- Custom template
     LET SQL_TEXT varchar := $$
     SELECT
       c.status,
       c.hashed_email
     FROM IDENTIFIER( {{ my_table[0] }} ) as c
     WHERE cleanroom.custom_compare({{ min_status }}, {{ max_status }}, c.status);
     $$;
   
     LET RES resultset := (EXECUTE IMMEDIATE :SQL_TEXT);
     RETURN TABLE(RES);
   
     END;
   

   Copy

8. The consumer calls consumer.create_template_request with the combined template. Use single quotation marks (' ') instead of double dollar sign delimiters ($$...$$) around the code you provide for stored procedure in the template_definition argument. For example:

   CALL samooha_by_snowflake_local_db.consumer.create_template_request(
     $cleanroom_name,
     $template_name,
     '
   BEGIN
   
   -- First, define the Python UDF.
   CREATE OR REPLACE FUNCTION CLEANROOM.custom_compare(min_status STRING, max_status STRING, this_status STRING)
   RETURNS boolean
   LANGUAGE PYTHON
   RUNTIME_VERSION = 3.10
   PACKAGES = (\'numpy\')
   
   HANDLER = \'custom_compare\'
   AS $$
   import numpy as np
   
   def custom_compare(min_status:str, max_status:str, this_status:str):
     statuses = [\'MEMBER\', \'SILVER\', \'GOLD\', \'PLATINUM\']
     return ((statuses.index(this_status) >= statuses.index(min_status)) &
             (statuses.index(this_status) <= statuses.index(max_status)))
       $$;
   
   -- Then define and execute the SQL query.
   LET SQL_TEXT varchar := $$
   SELECT
     c.status,
     c.hashed_email
   FROM IDENTIFIER( {{ my_table[0] }} ) as c
   WHERE cleanroom.custom_compare({{ min_status }}, {{ max_status }}, c.status);
   $$;
   
   -- Execute the query and then return the result.
   LET RES resultset := (EXECUTE IMMEDIATE :SQL_TEXT);
   RETURN TABLE(RES);
   
   END;
   ');
   

   Copy

9. 使用者和提供商继续使用标准 使用者定义的模板流程：

   1. The provider views the template request (provider.list_pending_template_requests) and then approves it by calling approve_template_request. In the request, the provider can see the template and the bundled code.

   2. 使用者检查请求状态 (consumer.list_template_requests)，当状态为 APPROVED 时，运行模板 (consumer.run_analysis)。