向应用程序包添加可计费事件

When you use Custom Event Billing for a Snowflake Native App, you can charge for specific types of application usage in addition to the existing usage-based pricing plans. To set it up, you must perform two high-level steps:

  1. 按照本主题中的步骤设置应用程序包，以发出可计费事件。
  2. Select a usage-based pricing plan with billable events for the listing you use to publish your Snowflake Native App to consumers.

This topic describes how to set up your application package to emit billable events using the SYSTEM$CREATE_BILLING_EVENT and SYSTEM$CREATE_BILLING_EVENTS system functions.

应用程序包中的可计费事件概述

You can set up your application package to emit billable events in response to specific usage events so that you can charge consumers based on how much they use your Snowflake Native App.

For example, you can add a billable event to charge a consumer a specific amount for each call to a stored procedure in your Snowflake Native App.

要向应用程序包添加可计费事件，请执行以下操作：

  1. Create stored procedures to define which usage events trigger calls to the SYSTEM$CREATE_BILLING_EVENT and SYSTEM$CREATE_BILLING_EVENTS system functions.

    Note

    You cannot test the output of the system function at this stage. This system function can only be called from a Snowflake Native App installed in a consumer account.

  2. 将这些存储过程添加到应用程序包的安装脚本中。

Important

Snowflake 支持通过在应用程序的存储过程中调用系统函数发出的计费事件，如本主题中的示例所示。

Snowflake 不支持通过其他方法来计算可计费事件的基本费用，例如使用表输出或输出使用者活动的用户定义的函数的方法，或使用在事件表中记录日志的遥测的方法。

如果不确定拟议的实施方案是否受支持，请联系 Snowflake 客户代表。

可计费事件示例

The examples in this section show how to create stored procedures to emit billable events for common billing scenarios. Each of these examples calls the createBillingEvent function.

调用 SYSTEM$CREATE_BILLING_EVENT 系统函数

The following example shows how to create a wrapper function in a stored procedure to call the SYSTEM$CREATE_BILLING_EVENT system function.

Note

可在使用 JavaScript、Python 或 Java 编写的存储过程中调用此系统函数。

This example creates a JavaScript stored procedure named custom_event_billing in the schema version that is accessible to the procedures that emit billing. The stored procedure creates a helper function called createBillingEvent which takes arguments that correspond to the typed parameters expected by the SYSTEM$CREATE_BILLING_EVENT system function.

For more details about the parameters and the required types, see SYSTEM$CREATE_BILLING_EVENT.

 CREATE OR REPLACE PROCEDURE <schema_name>.custom_event_billing()
 RETURNS NULL
 LANGUAGE JAVASCRIPT
 AS
 $$
   /**
    * Helper method to add a billable event
    * Format timestamps as Unix timestamps in milliseconds
    */

   function createBillingEvent(className, subclassName, startTimestampVal, timestampVal, baseCharge, objects, additionalInfo) {
        try {
            var res = snowflake.createStatement({
            sqlText: `SELECT SYSTEM$CREATE_BILLING_EVENT('${className}',
                                                      '${subclassName}',
                                                      ${startTimestampVal},
                                                      ${timestampVal},
                                                      ${baseCharge},
                                                      '${objects}',
                                                      '${additionalInfo}')`
            }).execute();

            res.next();

            return res.getColumnValue(1);
        } catch(err) {
            return err.message;
        }
    }
$$;

本主题中的示例调用了该 helper 函数。

使用 SYSTEM$CREATE_BILLING_EVENTS 系统函数批量处理多个计费事件

以下存储过程示例展示了如何使用 SYSTEM$CREATE_BILLING_EVENTS 系统函数批量处理多个 Snowflake Native App 计费事件。通过批量处理，您可以节省时间，减少超出调用限制的可能性，并确保正确设置计费事件。

For more details about the parameters and the required types, see SYSTEM$CREATE_BILLING_EVENTS.

 CREATE OR REPLACE PROCEDURE <app_provider_db_1><app_provider_schema_1>.external_proc_batch()
 RETURNS STRING
 LANGUAGE JAVASCRIPT
 EXECUTE AS OWNER
 AS
 $$
   function createBillingEventsBulk(events) {
     try {
       var res = snowflake.execute({
                    sqlText: `call SYSTEM$CREATE_BILLING_EVENTS('${events}')`
                 });
       res.next();
       return res.getColumnValueAsString(1);
     } catch (err) {
       return err.message;
     }
   }

   return createBillingEventsBulk(`
                                   [
                                     {
                                       "class": "class_1",
                                       "subclass": "subclass_1",
                                       "start_timestamp": ${Date.now()},
                                       "timestamp": ${Date.now()},
                                       "base_charge": 6.1,
                                       "objects": "obj1",
                                       "additional_info": "info1"
                                     },
                                     {
                                       "class": "class_2",
                                       "subclass": "subclass_2",
                                       "start_timestamp": ${Date.now()},
                                       "timestamp": ${Date.now()},
                                       "base_charge": 9.1,
                                       "objects": "obj2",
                                       "additional_info": "info2"
                                     }
                                   ]
                                 `);
$$;

示例：基于对存储过程的调用计费

The following example shows how to create a stored procedure to emit a billable event when a consumer calls that stored procedure in a Snowflake Native App.

在定义 helper 函数的同一存储过程中，将此示例代码添加到设置脚本中：

...
  //
  // Send a billable event when a stored procedure is called.
  //
  var event_ts = Date.now();
  var billing_quantity = 1.0;
  var base_charge = billing_quantity;
  var objects = "[ \"db_1.public.procedure_1\" ]";
  var retVal = createBillingEvent("PROCEDURE_CALL", "", event_ts, event_ts, base_charge, objects, "");
  // Run the rest of the procedure ...
$$;

This example code creates a stored procedure that calls the createBillingEvent function to emit a billable event with the class name PROCEDURE_CALL and a base charge of 1.0.

Note

The types of the arguments passed to the createBillingEvent function must correspond to the typed parameters expected by the SYSTEM$CREATE_BILLING_EVENT system function.

示例：基于 Snowflake Native App 使用的行数计费

以下示例说明了如何创建存储过程，以根据使用者账户中对一个表内的行的使用情况发出可计费事件。

在定义 helper 函数的同一存储过程中，将此示例代码添加到设置脚本中：

...
  // Run a query and get the number of rows in the result
  var select_query = "select i from db_1.public.t1";
  res = snowflake.execute ({sqlText: select_query});
  res.next();
  //
  // Send a billable event for rows returned from the select query
  //
  var event_ts = Date.now();
  var billing_quantity = 2.5;
  var base_charge = res.getRowcount() * billing_quantity;
  var objects = "[ \"db_1.public.t1\" ]";
  createBillingEvent("ROWS_CONSUMED", "", event_ts, event_ts, base_charge, objects, "");
  // Run the rest of the procedure ...
$$;

This example code creates a stored procedure that calls the createBillingEvent function to emit a billable event with the class name ROWS_CONSUMED and a calculated base charge of 2.5 multiplied by the number of rows in the db_1.public.t1 table in the consumer account.

Note

The types of the arguments passed to the createBillingEvent function must correspond to the typed parameters expected by the SYSTEM$CREATE_BILLING_EVENT system function.

示例：基于引入的行数计费

以下示例说明了如何创建存储过程，以根据表中接收的行数发出可计费事件。

在定义 helper 函数的同一存储过程中，将此示例代码添加到设置脚本中：

...
    // Run the merge query
    var merge_query = "MERGE INTO target_table USING source_table ON target_table.i = source_table.i
        WHEN MATCHED THEN UPDATE SET target_table.j = source_table.j
        WHEN NOT MATCHED
        THEN INSERT (i, j)
        VALUES (source_table.i, source_table.j)";
    res = snowflake.execute ({sqlText: merge_query});
    res.next();
    // rows ingested = rows inserted + rows updated
    var numRowsIngested = res.getColumnValue(1) + res.getColumnValue(2);

    //
    // Send a billable event for rows changed by the merge query
    //
    var event_ts = Date.now();
    var billing_quantity = 2.5;
    var base_charge = numRowsIngested * billing_quantity;
    var objects = "[ \"db_1.public.target_table\" ]";
    createBillingEvent("ROWS_CHANGED", "", event_ts, event_ts, base_charge, objects, "");
    // Run the rest of the procedure ...
$$;

This example code creates a stored procedure that calls the createBillingEvent function to emit a billable event with the class name ROWS_CHANGED and a calculated base charge of 2.5 multiplied by the number of rows ingested in the db_1.target_table table.

Note

The types of the arguments passed to the createBillingEvent function must correspond to the typed parameters expected by the SYSTEM$CREATE_BILLING_EVENT system function.

示例：基于每月活跃行数计费

每月活跃行数是指在一个日历月内首次插入或更新的行数。有些提供商使用此指标，仅按一个月内更新的不重复行数向使用者收费。您可以修改此示例，改为统计唯一身份用户，或者确定不重复数据加载位置，从而确定基本费用。

以下示例说明了如何创建存储过程，以根据每月活跃行数发出可计费事件。在定义 helper 函数的同一存储过程中，将此示例代码添加到设置脚本中：

...
    //
    // Get monthly active rows
    //
    var monthly_active_rows_query = "
     SELECT
         count(*)
     FROM
         source_table
     WHERE
         source_table.i not in
         (
           SELECT
             i
           FROM
             target_table
           WHERE
             updated_on >= DATE_TRUNC('MONTH', CURRENT_TIMESTAMP)
         )";
    res = snowflake.execute ({sqlText: monthly_active_rows_query});
    res.next();
    var monthlyActiveRows = parseInt(res.getColumnValue(1));
    //
    // Run the merge query and update the updated_on values for the rows
    //
    var merge_query = "
        MERGE INTO
            target_table
        USING
            source_table
        ON
            target_table.i = source_table.i
        WHEN MATCHED THEN
         UPDATE SET target_table.j = source_table.j
                    ,target_table.updated_on = current_timestamp
        WHEN NOT MATCHED THEN
            INSERT (i, j, updated_on) VALUES (source_table.i, source_table.j, current_timestamp)";
    res = snowflake.execute ({sqlText: merge_query});
    res.next();
    //
    // Emit a billable event for monthly active rows changed by the merge query
    //
    var event_ts = Date.now();
    var billing_quantity = 0.02
    var base_charge = monthlyActiveRows * billing_quantity;
    var objects = "[ \"db_1.public.target_table\" ]";
    createBillingEvent("MONTHLY_ACTIVE_ROWS", "", event_ts, event_ts, base_charge, objects, "");
    // Run the rest of the procedure ...
$$;

This example code creates a stored procedure that determines the number of monthly active rows using a merge query to identify unique rows. The example then calculates the base charge using the value of the monthlyActiveRows variable and the billing_quantity. The base charge is then passed to the createBillingEvent function.

Note

The types of the arguments passed to the createBillingEvent function must correspond to the typed parameters expected by the SYSTEM$CREATE_BILLING_EVENT system function.

In your setup script, add this stored procedure after the stored procedure that calls the SYSTEM$CREATE_BILLING_EVENT system function.

Snowpark Python 示例：基于使用的行数计费

To write your stored procedure in Snowpark Python to bill based on rows consumed by your Snowflake Native App, use the following example:

CREATE OR REPLACE PROCEDURE app_schema.billing_event_rows()
   RETURNS STRING
   LANGUAGE PYTHON
   RUNTIME_VERSION = '3.11'
   PACKAGES = ('snowflake-snowpark-python')
   HANDLER = 'run'
   EXECUTE AS OWNER
   AS $$
import time

# Helper method that calls the system function for billing
def createBillingEvent(session, class_name, subclass_name, start_timestamp, timestamp, base_charge, objects, additional_info):
   session.sql(f"SELECT SYSTEM$CREATE_BILLING_EVENT('{class_name}', '{subclass_name}', {start_timestamp}, {timestamp}, {base_charge}, '{objects}', '{additional_info}')").collect()
   return "Success"

# Handler function for the stored procedure
def run(session):
   # insert code to identify monthly active rows and calculate a charge
   try:

      # Run a query to select rows from a table
      query =  "select i from db_1.public.t1"
      res = session.sql(query).collect()

      # Define the price to charge per row
      billing_quantity = 2.5

      # Calculate the base charge based on number of rows in the result
      charge = len(res) * billing_quantity

      # Current time in Unix timestamp (epoch) time in milliseconds
      current_time_epoch = int(time.time() * 1000)

      return createBillingEvent(session, 'ROWS_CONSUMED', '', current_time_epoch, current_time_epoch, charge, '["billing_event_rows"]', '')
   except Exception as ex:
      return "Error " + ex
$$;

This example code creates a stored procedure that defines a helper method that calls the SYSTEM$CREATE_BILLING_EVENT system function, as well as a method that calls that helper method, createBillingEvent, to emit a billable event with the class name ROWS_CONSUMED and a base charge calculated by multiplying a price of 2.5 US dollars by the number of rows in the db_1.public.t1 table in the consumer account.

Note

The types of the arguments passed to the createBillingEvent function must correspond to the typed parameters expected by the SYSTEM$CREATE_BILLING_EVENT system function.

测试自定义事件计费

为确保正确设置自定义事件计费，并确保按预期为使用量事件发出可计费事件，请执行以下操作：

  1. 更新应用程序包：

    1. 更新安装脚本，包含发出可计费事件的存储过程。
    2. 使用新的安装脚本更新应用程序包。
    3. 更新应用程序包的版本和发布指令。

  2. 与组织中您有拥有以下权限的使用者账户共享应用程序包：

    1. Create a private listing.
    2. Add Custom Event Billing as the pricing plan for the listing.
    3. 将其与使用者账户共享。
    4. Sign in to the consumer account using Snowsight.
    5. Install the Snowflake Native App.

  3. Confirm that the stored procedures successfully emit billable events.

  4. Confirm that the listing is set up properly.

Note

When you test Custom Event Billing, you must set up a payment method but you will not be charged for usage within your organization.

验证存储过程是否会发出可计费事件

While signed in to the consumer account with which you shared your listing, call the stored procedures that you added to your Snowflake Native App.

For example, to test the stored procedure created for billing based on monthly active rows, do the following:

  1. Sign in to the consumer account in Snowsight.

  2. Open a worksheet and set the context to db_1.public.

  3. 运行以下 SQL 语句：

    CALL merge_procedure()

    If the stored procedure returns Success, your code is working.

Note

如果在用于创建应用程序包的提供商账户中运行这些 SQL 命令，则会收到错误。

验证自定义事件计费定价方案

To validate the consumer experience of a Snowflake Native App and confirm that the listing and application package are set up properly, you can query the MARKETPLACE_PAID_USAGE_DAILY View in the DATA_SHARING_USAGE schema of the shared SNOWFLAKE database.

Note

Due to latency in the view, run these queries at least two days after first using the Snowflake Native App.

To confirm that billable events are successfully generated by a Snowflake Native App and listing, run the following SQL statement in the consumer account that you shared the listing with:

Note

将 PROVIDER_ACCOUNT_NAME 和 PROVIDER_ORGANIZATION_NAME 值替换为提供商账户的对应值。

SELECT listing_global_name,
   listing_display_name,
   charge_type,
   charge
FROM SNOWFLAKE.DATA_SHARING_USAGE.MARKETPLACE_PAID_USAGE_DAILY
WHERE charge_type='MONETIZABLE_BILLING_EVENTS'
      AND PROVIDER_ACCOUNT_NAME = <account_name>
      AND PROVIDER_ORGANIZATION_NAME= <organization_name>;
+---------------------+------------------------+----------------------------+--------+
| LISTING_GLOBAL_NAME |  LISTING_DISPLAY_NAME  |        CHARGE_TYPE         | CHARGE |
+---------------------+------------------------+----------------------------+--------+
| AAAA0BBB1CC         | Snowy Mountain Listing | MONETIZABLE_BILLING_EVENTS |   18.6 |
+---------------------+------------------------+----------------------------+--------+