User-Defined Functions

SQL Editor enables you to create "user-defined functions" (UDFs) based on your analysis of your data and your knowledge of your partners' needs. You can then share the results of your analyses with your partners rather than sharing its record-level data: they won’t see the underlying query or dataset used to define the analysis. Partners can then leverage the results for media planning and measurement.

For example, a publisher might share a UDF with an advertiser to expedite access and then work with them to customize the underlying queries as needed. The publisher might also include variables, such as purchased items, that the advertiser can use to specify different types of items without having to rewrite the underlying code.

Considerations:

UDFs can be run against multiple datasets based on the permissions applied by one or more data owners. This allows partners to integrate their data into the analysis while safeguarding their intellectual property.
When creating a UDF, avoid ambiguity by qualifying column and table references. This will help avoid errors if multiple tables contain the same column.
You cannot create multiple analyses with the same name but different parameters ("function overloading").
Arguments must not include symbols that are used in the scope of the function.
To protect row-level data, you can apply a query threshold to a user-defined function in the following ways:
- The UDF's shared view has a query threshold applied to it.
- Include a having clause in your function body to apply a minimal aggregation requirement. For example, having count(distinct(c.rampid)) <= 20
  Note
  Using a having clause doesn't support more complicated use cases such as two levels of aggregation or a union when joining two protected datasets.
- Use the qt function as follows when creating the UDF in SQL Editor: qt(table_name, column_name, threshold_value)
  Where:
  - table_name is replaced with the name of a table or a view in SQL Editor's All Assets pane.
  - column_name is replaced with the name of a column in your table or view.
  - threshold_value is replaced with a numeric value for the needed threshold.
  For example, you could include qt(brand, rampid, 5000) in your UDF creation statement to apply a minimum aggregation of 5000 results.

Create a User-Defined Function

You can use SQL Editor to write a user-defined function and then run it against a dataset to create it.

Note

If you have the Data Owner or Automation Engineer role, you can create UDFs. Otherwise, you will receive an error if you enter a UDF creation statement in SQL Editor.

To see your roles, click the user icon on the top-right-hand side of any screen in the Data Collaboration Platform and select My Profile.

UDFs appear in SQL Editor's All Assets pane under the icon and on the All Assets page, where you can grant permissions to a partner if you are the owner of the function.

Tip

To see your roles, click the user icon on the top right-hand side of any screen and select My Profile.

Procedure. To create a UDF:

Navigate to SQL Editor and enter a function using the following format:
```
CREATE [OR REPLACE] FUNCTION dataset_name ( [ parameter type [, parameter type ...] ] )
    AS SQL_expression;
```
Where:
- dataset_name is a valid dataset name that is not a reserved SQL function. For information, see Snowflake's "Identifier Requirements."
- parameter must be a literal value or an expression that can be evaluated to a single value (such as 3 * %). Functions can have zero or more parameters. Parameters allow you to change the scope of your analysis without revealing the implementation to your partner.
  Tip
  You can also specify a table or a view as a UDF parameter.
  You can specify suggested parameter values when you create your UDFs.
  You can create a "parameterless UDF" that does not require the user to enter a parameter value to allow it to run. For example, you could create a UDF that creates a view from data in multiple data warehouses and then grant a partner access to query it.
- SQL_expression is the function's body, which must return a table with zero or more rows, each of which has one or more columns.
Example tabular function:
```
create or replace function sum_of_payments(product_to_sum string, sale_month date) as
    select sum(pp.payment) 
    from product_payments pp 
    where pp.product_id = product_to_sum and month(sold_date) == sale_month;
```
Example single value function:
```
create or replace function pi() as select 3.1415;
```
Example parameterless function:
```
create or replace function exposure_protected() as select * from exposure with qt 25 on rampid
```
Click Run. The UDF is created and can be found on the All Assets page and in the "Functions" node of SQL Editor's All Assets pane.

Note

When creating a function, references in function definitions are not fully validated. Detailed errors are only shown when you run a UDF.

View a User-Defined Function in SQL Editor

Once you create a user-defined function, you can go to the All Assets page and open it in SQL Editor to see what your partner will see once you grant them permission to it. This allows you to verify that your partner won't see the details of the SQL that generates the results of your analysis.

Procedure. To view a user-defined function in SQL Editor:

Go to the All Assets page, find the function you want to share with a partner, click its More Options menu (), and select Insert in Editor.
SQL Editor opens and displays what your partner will see if you share the function with them and they select Insert in Editor. You can also view your organization's definition of the function by selecting Open definition from the More Options menu ().
If you are the owner of the function, you can open its definition to see its parameters and SQL expression: From the function's More Options menu () and select Open definition.
SQL Editor displays the UDF's definition.
If you want to view more details about a function, click its row to display its side panel, which includes information such as the organization that owns it, the date it was created, any applied tags, and its schema. If your partner granted you permission to use the function, you can see the permission dates. If your organization owns the UDF, the SQL tab displays the UDF creation statement.

Share a User-Defined Function

To create a data permission for a partner, they must first be configured for your account so that they are available to select from the Collaboration Partner list.

Before you grant permission to your UDF, you can see the results that your partner will see by going to the All Assets page and selecting Insert in Editor from the function's More Options menu (). Run the function in SQL Editor and verify that you want to share the results of the analysis with your partner.

If an organization's assets are referenced within the function, then they are the only organization that can be chosen as a collaboration partner. You cannot share your partner's assets with a different organization. For example, if org1 is the creator of the function, then the function can only be shared with org2.

CREATE OR REPLACE FUNCTION amount_by_age(sale_month DATE) AS
  SELECT u.age, avg(s.amount) AS amt
  FROM org1.sales s
  INNER JOIN org2.users u ON (rampid)
  WHERE month(s.sold_date) == sale_month
  GROUP BY u.age

Procedure. To share a user-defined function:

Go to the All Assets page, find the function you want to share with a partner, click its More Options menu () and select Create Permission.
The Create Permission page displays the Details step.
Enter a unique name for the data permission, select a partner from the Collaboration Partner list, and enter a start and end date.
Click Next. The Summary page displays the selected function, the partner, and the effective permission dates.
Click Confirm Permission. Once the permission is processed and its dates are in effect, your partner can see the function on their All Assets page. The dataset referenced by the function, such as a view, is displayed on the Permissions page with the name that you entered in the Details step.

Run a User-Defined Function

If your partner shared a user-defined function with you, you can insert it in SQL Editor and run it.

If your organization owns the function, you can view its definition and click "Open definition in the editor" to open the full text of the function in SQL Editor. Alternatively, you can insert it in the editor to run it to see the results that your partner will see once you grant them permission to use it.

Procedure. To run a user-defined function:

Go to the All Assets page:
From the function's More Options menu (), select Insert in Editor.
SQL Editor displays the function.
If your partner provided configurable parameters, you can update the values. For example, you can enter a date range for date parameters or enter the two-letter code for a U.S. state parameter. The parameters and their type is noted in the schema, which is viewable in the All Assets pane of SQL Editor or in a function's side panel on the All Assets page.
Click Run Query and view its results.

Delete a User-Defined Function

If your organization owns a user-defined function, you can delete it from the All Assets page. You cannot delete functions that have been shared with you by another organization.

Before deleting a function, verify that you have not shared it with any partners. You can view and revoke permissions on the Permissions page.

Procedure. To delete a user-defined function:

Go to the All Assets page, find the function you want to delete, click its More Options menu (), and select Delete.
A confirmation message reminds you that any permissions to your partners will be permanently revoked.
Click Delete.

Tip

You can also delete a function directly from SQL Editor by entering the following and then clicking Run:

DROP FUNCTION function_name

Where: function_name is the name of the function you want to delete.

In this section:

User-Defined Functions

Note

Create a User-Defined Function

Note

Tip

Tip

Note

View a User-Defined Function in SQL Editor

Share a User-Defined Function

Run a User-Defined Function

Delete a User-Defined Function

Tip

Search results