애플리케이션 패키지에 청구 가능한 이벤트 추가하기¶

 CREATE OR REPLACE PROCEDURE db_1.public.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;
        }
    }
$$;

...
  //
  // 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 ...
$$;

...
  // 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 ...
$$;

...
    // 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 ...
$$;

...
    //
    // 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 ...
$$;

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>;

SELECT
    listing_display_name,
    listing_global_name,
    be.value:display_name::varchar as class_name,
    be.value:class::varchar as class,
    units,
    be.value:billing_unit::varchar as billing_unit,
    unit_price,
    charge,
    be.value:billing_quantity::float as pp_billing_quantity
FROM
    SNOWFLAKE.DATA_SHARING_USAGE.MARKETPLACE_PAID_USAGE_DAILY,
    LATERAL FLATTEN(INPUT => pricing_plan:billing_events) be
WHERE
    charge_type = 'MONETIZABLE_BILLING_EVENTS'
    AND PROVIDER_ACCOUNT_NAME = <account_name>
    AND PROVIDER_ORGANIZATION_NAME= <organization_name>;