Snowflake Native Applications

Note

The Private Preview program for Snowflake Native Apps is closed to new participants.

This topic describes how to use the initial private preview implementation of the Snowflake Native App Framework. For documentation on the current supported version of the Snowflake Native App Framework, see About the Snowflake Native App Framework

This topic explains how to create a Snowflake Native Application and share the app in the Snowflake Marketplace.

Introduction to Snowflake Native Applications

In your Snowflake account, you can write and provide Native Apps to share your data and application logic with other accounts.

Snowflake Native Applications enhance secure data sharing by allowing providers to create local state objects (eg: tables) and local compute objects (e.g. stored procedures, external functions, tasks) in addition to sharing objects representing the application logic in the consumer account.

Native Apps also allow providers to control the accessibility of objects with their consumers. Native Apps are installed in the consumer accounts as databases similar to secure data sharing.

For example, you can create a Native App that consists of stored procedures and external functions that analyze and enrich data in the consumer account. A consumer can install your Native App in their account as a database and call stored procedures in your Native App that provide the application functionality.

You make a Native App available to other accounts through Secure Data Sharing, as explained in the next section.

How Snowflake Native Applications Enhance Secure Data Sharing

Although Native Apps leverage secure data sharing, some important differences in functionality between them include:

  • Your Native App can write to the database in the consumer account.

  • You can create local objects such as stored procedures, external functions, tasks, and tables in the consumer account through an installer script or stored procedure.

  • You can also share the following objects from the provider account:

    • Table

    • External table

    • Secure view

    • Secure materialized view

    • User defined function

    • Tabular user defined function

    • Stored procedure (only for Native Apps)

    • External function (only for Native Apps)

    • Database roles

    Note

    Objects not listed above can only be created in consumer accounts using the installer script or a separate stored procedure. Objects that require additional privileges (e.g. tasks) can only be created from a separate stored procedure.

  • You can determine which objects in the database are visible in the consumer accounts.

How a Snowflake Native Application is Created and Installed

A Native App is created by a provider and shared with consumers. This is similar to the concepts of data providers and data consumers in Secure Data Sharing.

To make a Native App available to other accounts, you (as the application provider) share your application through Secure Data Sharing:

  1. Create the database for the database objects used by your application, then create one or more schemas in that database.

  2. Create objects such as stored procedures and UDFs that define your application in that database.

  3. Create database role(s) and grant permissions on these objects to them.

  4. Write an installer script to create database objects local to the consumer accounts if required. You also use the installer script to expose the objects to the consumers via the database roles created above.

  5. Create a listing specifying the installer script that should run whenever a database is created from that share in the consumer account.

  6. Add your database, schema(s), installer script, and objects from your account including tables, external tables, secure views, and secure materialized views to this share.

  7. As the application provider, create a listing for your application and share it with specific consumers.

To install a Native App in another account:

  1. Your consumers create a database from the listing. The process of creating a database from the listing automatically runs the installer script, which can optimally create stored procedures, external functions, and other objects needed for the data application in your account.

  2. The installer script sets up and configures the Native App in your account.

Note

  • Native Applications must always be distributed to consumer accounts through listings.

  • In Snowflake Marketplace, you can create free, paid, or private listings for a Native Application.

  • You might encounter unexpected errors if you install a Native Application not associated with a listing (e.g. via a direct share).

How a Snowflake Native Application Runs

After a user in a consumer account installs your application, the user can execute SQL queries against the application.

The stored procedures of the Native App execute in the context of the consumer account with the owner’s rights of the application. The application owns all objects in the application database. Additionally, the actions an application is authorized to perform in the consumer account is determined by the privileges granted by the consumer to the application database.

Key Concepts and Components

This section describes important concepts and components you must understand to create a Native App.

Installer Script

The installer script is a mandatory stored procedure executing with owner rights that lets you leverage the enhancements provided by applications.

You can only use JavaScript or Snowflake Scripting as the language for the installer script.

An installer script is bound to the share for your application and is invoked when a consumer of your application creates an application instance by importing the share for your application in their accounts.

Two primary functions of the installer script are:

  • To create local objects in the consumer account.

  • To control the visibility of objects in your application with the consumers.

For example, to create an installer script and bind it with a share, run the following commands:

CREATE PROCEDURE my_app.app_schema.installer()
RETURNS STRING
LANGUAGE SQL
EXECUTE AS OWNER
AS $$
  begin
    ...
    ...
  end;
$$;

CREATE SHARE my_app_share installer = my_app.app_schema.installer();
Copy

Database Roles

A database role is a special type of role, defined within a database, that allows aggregation of privileges on the objects in that database.

Note

  • You cannot grant privileges on an object inside a database to a database role defined outside that database.

  • Granting shared database roles to other consumer roles yet is not supported.

For example, to create a database role:

CREATE DATABASE ROLE data_app.shared_db_role;
Copy

You can grant permissions on objects in the database representing your application to a database role associated with that database. For example:

GRANT USAGE ON PROCEDURE sproc() TO DATABASE ROLE data_app.shared_db_role;
Copy

The database role has to be granted to a share for your application so that it can use the database role. For example:

GRANT DATABASE ROLE data_app.shared_db_role to SHARE app_share;
Copy

Roles in the consumer account can then be given access to objects in the Native App by granting imported privileges on the application database to those roles. See Granting privileges on a shared database.

Shared Stored Procedures

Stored Procedures can be part of your applications. By default, these stored procedures are inaccessible by roles in consumer accounts.

Note

You can control access for stored procedures and other objects by granting the necessary privileges on them first to a database role and then granting that database role to a special database role called APP_EXPORTER.

To make the stored procedure visible to the consumers as part of your application:

  • Create a database role

  • Grant the appropriate permissions on the stored procedure to the database role.

  • Grant the database role to the APP_EXPORTER role as part of the installer script.

For example, a stored_proc_in_data_app() in your account can be made visible to the consumers of your application by running the following commands:

CREATE DATABASE ROLE my_app.shared_db_role;

GRANT USAGE ON PROCEDURE stored_proc_in_data_app() TO
DATABASE ROLE my_app.shared_db_role;

CREATE PROCEDURE my_app.app_schema.installer()
RETURNS STRING
LANGUAGE SQL
EXECUTE AS OWNER
AS $$
  begin
    ...
    GRANT DATABASE ROLE shared_db_role TO DATABASE ROLE APP_EXPORTER;
    ...
  end;
$$;
Copy

Note

Create two DB roles: one for objects visible to the consumers and a different one for those not visible to the consumer. This makes it easy to track shared objects and prevents accidentally sharing of objects with the consumers.

Do not forget to grant the DB role aggregating privileges on objects exposed to the consumers to the APP_EXPORTER role.

Shared External Functions

External functions are user-defined functions that are stored and run outside of Snowflake. You can include external functions in a Native App. However, there are two requirements:

  • The application database in the consumer account that contains the external function must be created from a Native Apps share.

  • The consumer must set the FIREWALL_CONFIGURATION property on the application database. This data base property allows consumers of Native Apps to control the external endpoints that an application can connect to.

When a consumer installs a Native App containing an external function, they must set the FIREWALL_CONFIGURATION property on the application database:

ALTER DATABASE <database> SET FIREWALL_CONFIGURATION = ( <url> [, <url>...] )
Copy

To set this property, the consumer must have the MANAGE FIREWALL_CONFIGURATION privilege on their account.

See Example: Snowflake Native Application with Shared External Functions for information on using an external function within a Native App.

Shared Java and Python Code

You can use Java UDFs, UDTFs and Stored Procedures for building your applications from all deployments in AWS and Azure in GA. Python Stored procedures, UDFs, UDTFs are currently in PrPr and can be made available upon request.

Example: Using Python and Java in a Native App shows how to use stored procedures in these languages within a Native App.

Logging

In a Native App, you can log error messages, warning messages, and messages at other severity levels from UDFs, UDTFs, and stored procedures written in Java, Javascript, and Snowflake Scripting. Python logging is currently in development. Logs from your Native Applications are stored in the event table created in the consumer account.

See Logging Messages from a Java UDF for more details.

Upgrading a Snowflake Native Application

This section describes how to update a Native App after a consumer has installed it in their account.

Local Objects Created in Consumer Accounts

If your Native App creates local objects in the consumer account as part of the installer script, updates require a consumer to reinstall your application in their account.

Objects Shared from Provider Accounts

This section describes the upgrades for objects shared from the provider account.

Updating the Logic of Existing Objects

Shared stored procedures can be updated without customers needing to reinstall the share. As the updates will be immediately reflected in the consumer account providers need to ensure backward compatibility so that consumers’ workflows would not be impacted by the upgrades.

Add New Objects to the Snowflake Native Application

To add a new object (e.g. table, procedure, etc) to your application in the provider account:

  1. Create a new object.

  2. If the new object should be visible to your consumers, the object should be granted to a database role granted to APP_EXPORTER.

  3. The new object will be immediately reflected in the consumer account.

Removing Objects from the Snowflake Native Application

To remove objects from your application in the provider account, revoke the grant of the object from the database role granted to the APP_EXPORTER.

Limitations of Snowflake Native Applications

Snowflake Native Applications have the following limitations:

  • You can only create owner’s rights stored procedures in the consumer account via the installer script for your Native App.

  • You cannot share or create Java UDFs that use the Snowpark library.

  • A database role cannot be granted to another role.

  • Your application cannot create or share external stages in a consumer account.

  • The supported CURRENT_* functions from the stored procedures are limited to:

    • CURRENT_SESSION()

    • CURRENT_ROLE()

    • CURRENT_ACCOUNT()

    • CURRENT_DATABASE()

    • CURRENT_SCHEMA()

    • CURRENT_STATEMENT()

    Due to a known bug, CURRENT_ROLE() returns NULL.

  • Your application cannot call the following:

    • CURRENT_USER()

    • CURRENT_WAREHOUSE()

    • CURRENT_IP_ADDRESS()

    • CURRENT_AVAILABLE_ROLES()

    • CURRENT_SECONDARY_ROLES()

    If you encounter a CURRENT_* function that is not working and is not listed here, please contact us so we can evaluate the use case.

  • Your application cannot set parameters at either account or session level.

  • Your application cannot create temporary objects.

Providers: Creating a Snowflake Native Application

This section describes how to create a Native App. For information on sharing your application in the Snowflake Marketplace see Providers: Sharing a Snowflake Native Application with Consumers.

To create a Native App, follow these steps in the next sections:

These sections describe how to create a basic Native App. See Examples of Snowflake Native Applications for information on creating more complex applications.

Setting up Accounts for Development and Testing

In order to develop a Native App, you must create two accounts:

  • A developer account in which you will do your development work (e.g. create the database for your application, write the installer script for your installer script, etc.)

  • A testing account where you install the application as a consumer and test the application before publishing it with the consumers.

Note

  • Both accounts must be in the same region.

  • Do not use existing production or staging accounts. Create a new developer account and a new testing account for your applications.

Creating the Database and Schema for Your Snowflake Native Application

In your developer account, run the CREATE DATABASE and CREATE SCHEMA commands to create the database and one or more schemas. For example:

CREATE DATABASE time_app;
CREATE SCHEMA time_app.app_schema;
Copy

Creating Stored Procedures For Your Snowflake Native Application

The stored procedure for your application must be run with owner’s rights.

In the example below, two stored procedures are created, one will not be exposed to the consumer and the other one will be exposed to the consumer.

-- Internal procedure that will not be exposed to the consumer

CREATE PROCEDURE time_app.app_schema.record_time()
RETURNS STRING
LANGUAGE SQL
EXECUTE AS OWNER
AS $$
  begin
      INSERT INTO time_schema.time_table VALUES(CURRENT_TIMESTAMP);
      return 'CURRENT TIMESTAMP RECORDED';
  end;

$$;
Copy
-- Procedure that will be exposed to the consumer
CREATE PROCEDURE time_app.app_schema.start_service(wh_name string)

RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS $$

var create_task_cmd =
     "CREATE TASK IF NOT EXISTS time_schema.insert_time warehouse = "
        + WH_NAME
        + " SCHEDULE = '5 MINUTE'"
        + " as call app_schema.record_time()";

snowflake.execute({ sqlText: create_task_cmd });

snowflake.execute({ sqlText: `ALTER TASK time_schema.insert_time resume` });

return "SERVICE STARTED";

$$;
Copy

Creating DB Roles for Your Snowflake Native Application

Create two database roles. GRANT USAGE privileges for the stored procedures and other objects exposed to consumers to one database role, and GRANT USAGE privileges for the procedures and other objects not exposed to consumers to another database role. For example:

-- database role will be granted to APP_EXPORTER in installer script
CREATE DATABASE ROLE time_app.shared_db_role;

GRANT USAGE ON DATABASE time_app TO
DATABASE ROLE time_app.shared_db_role;

GRANT USAGE ON SCHEMA time_app.app_schema TO
DATABASE ROLE time_app.shared_db_role;

GRANT USAGE ON PROCEDURE time_app.app_schema.start_service(string)
TO DATABASE ROLE time_app.shared_db_role;

-- database role will not be granted to APP_EXPORTER in installer script
CREATE DATABASE ROLE time_app.hidden_db_role;

GRANT USAGE ON DATABASE time_app TO
DATABASE ROLE time_app.hidden_db_role;

GRANT USAGE ON SCHEMA time_app.app_schema TO
DATABASE ROLE time_app.hidden_db_role;

GRANT USAGE ON PROCEDURE time_app.app_schema.record_time() TO
DATABASE ROLE time_app.hidden_db_role;
Copy

Creating the Installer Script

In one of the schemas that you created in the previous step, write a mandatory stored procedure that represents an installer script, executing with owner rights.

  • If your application needs to create local objects in the consumer account as part of the installer script, it should be created with owner rights.

  • To make an object created in the previous step accessible to the roles in the consumer accounts, grant the database role associated with that shared procedure to the APP_EXPORTER database role.

For example:

-- Example of an installer script

CREATE PROCEDURE time_app.app_schema.installer()
RETURNS STRING
LANGUAGE SQL
EXECUTE AS OWNER
AS $$
  begin
    CREATE SCHEMA time_schema;
    CREATE TABLE time_schema.time_table(ts timestamp);
    GRANT USAGE ON schema time_schema TO DATABASE ROLE APP_EXPORTER;
    GRANT SELECT ON TABLE time_schema.time_table TO
    DATABASE ROLE APP_EXPORTER;
    GRANT DATABASE ROLE shared_db_role TO DATABASE ROLE APP_EXPORTER;
    return 'installer script Done';
  end;
$$;
Copy

Setting Up the Share for Your Snowflake Native Application

Next, set up the share for your application:

  1. Create the share by running the CREATE SHARE command. In this command, set the installer script to the fully qualified name of the stored procedure for your installer script. For example:

    CREATE SHARE time_app_share installer = time_app.app_schema.installer();
    Copy

  2. Add the objects to this share by running the GRANT <privilege> ... TO SHARE command. For example you can add the following:

    • The database for your application.

    • The schema for your installer script.

    • The stored procedure for your installer script to the share.

    • The stored procedures for your application functionality to the share via a database role.

    • Database roles in your applications.

    For example:

    GRANT USAGE ON DATABASE time_app TO SHARE time_app_share;
GRANT USAGE ON SCHEMA time_app.app_schema TO SHARE time_app_share;
GRANT USAGE ON PROCEDURE time_app.app_schema.installer() TO SHARE time_app_share;
GRANT DATABASE ROLE time_app.hidden_db_role TO SHARE time_app_share;
GRANT DATABASE ROLE time_app.shared_db_role TO SHARE time_app_share;
    Copy

Providers: Sharing a Snowflake Native Application with Consumers

After creating your Native App, you can share it with consumers in the Snowflake Marketplace by creating and publishing a listing for your application. Listings enable you to define how your application is shared and consumed.

Note

The ability to share via listing is only available to customers who have agreed to the Snowflake Provider and Consumer Terms of Service made available at https://www.snowflake.com/legal. Before publishing your application as a private listing, providers need to follow the steps in Onboarding.

Snowflake Marketplace Listings

Snowflake Native Applications support the following types of listings:

  • Marketplace Listings: Marketplace listings are discoverable in the Snowflake Marketplace. A marketplace listing can be one of the following:

    • Free Listing

    • Personalized Listing

    • Paid Listing

    See Types of Listings for more information.

  • Private Listings: Private listings let you share data from your Native Applications with a list of other Snowflake accounts. Private listings cannot be discovered in the Snowflake Marketplace and are only available to Snowflake accounts that you specify. A private listing can be one of the following:

    • Free Listing

    • Paid Listing

    See Types of Listings for more information.

    Note

    The private listings feature is currently available in Public Preview.

Types of Listings

For general information about listings, see About listings.

  • Free Listing: Consumers who are participating in the Native Apps private preview have instant access to the data product you provide. This type of listing is best for providing access to Native Applications containing generic, aggregated, or non-customer-specific data.

  • Personalized Listing: A personalized listing allows customers to request a Native Application that contains a specific data set or application logic.

  • Paid Listing: Paid listings enable you to monetize the Native App listings you create and publish in the Snowflake Marketplace.

For information about creating and publishing listings, see Create and publish a listing.

Monetizing Your Native Application

When listing your application in the Snowflake Marketplace, you can specify a pricing model for your application using Paid listings pricing models.

Native Applications support a query-based pricing option that enables you to charge by the number of queries performed or by a fixed price for an unlimited number of queries.

Accepting the Snowflake Provider and Consumer Terms of Service

To publish a Native App as a private listing, the organization needs to accept Snowflake Provider and Consumer Terms of Service:

  • The organization administrator (ORGADMIN) role is required to complete the steps in this section. For information about organizations, see Managing Your Snowflake Organization.

  • To accept the terms, a user must have the following user properties set:

    • First name

    • Last name

    • Email address

  • If the user properties are not set, Snowflake displays a user error. A user administrator (i.e. user with the USERADMIN role) or a higher role, or another role with the OWNERSHIP privilege on your Snowflake user object, can add these details to your user profile.

To review and accept the Snowflake Provider and Consumer Terms of Service:

  1. Log in using an account with the ORGADMIN role.

  2. Click the dropdown menu next to your login name, then click Switch Role » ORGADMIN to change to the organization administrator role.

  3. In the Snowflake UI, click Admin » Billing & Terms.

  4. In the Snowflake Marketplace section, click Review.

  5. Review the terms, check the box to accept them, then click Review Snowflake Provider and Consumer Terms of Service.

  6. Click Accept Terms & Conditions.

  7. Click View Terms & Billing Info to close the dialog.

Considerations When Creating and Publishing a Listing

When creating a listing for your Native Application, note the following considerations related to secure shares:

  • Secure shares can only be attached to one listing.

  • After a listing is published, you cannot attach a different share.

  • You can only see shares owned by your current role.

  • Until a listing is published, it can only be associated with a share in the local/primary account. After the listing is published, it can be associated with shares created in accounts in other regions.

Creating and Publishing a Free Marketplace Listing

To create and publish a free Marketplace listing:

  1. In Snowsight, select Data Products » Provider Studio.

  2. Select + Listing.

  3. Enter the title of your listing.

  4. Select Anyone on the Marketplace.

    Note

    Only accounts enrolled in the Consumer Private Preview are able to discover Native Application listings on the marketplace.

  5. Select Free, then select Next.

    The listing is created in draft mode. Your can save the draft at any time to finish it later. You must provide data for each field of each section.

  6. Select a share for your listing:

    1. Under the Data Product area of the page, click Add next to Secure Share.

    2. Navigate to the share for your Native Application and select it.

      If your share does not appear, see Considerations When Creating and Publishing a Listing.

    3. Click Done.

    4. (Optional) You can change the default name for the secure share.

    5. Click Save.

  7. Add information for the remaining areas:

    • Basic information

    • Details

    • Business Needs

    • Sample SQL Queries

    • Region Availability

    To publish your listing, you must provide information for each area. For a description of each section and related fields, see Basic information.

  8. After you complete all of the sections, the Submit for Approval button is activated, and you can submit the listing for approval.

    Note

    The Submit for Approval button is not activated if you are not the share owner.

  9. After the listing is reviewed by Snowflake, the state changes to Approved or Denied. If the listing has been denied, update the listing based on the feedback provided in comments, and resubmit it for approval.

    Snowflake data providers are notified when their listing is approved or denied. An email notification is sent to both Business Contact and Technical Contact email addresses in the provider profile associated with the listing.

Creating and Publishing a Paid Marketplace Listing

To create and publish a paid marketplace listing:

  1. In Snowsight, select Data Products » Provider Studio.

  2. Select + Listing.

  3. Enter the title of your listing.

  4. Select Anyone on the Marketplace.

    Note

    Only accounts enrolled in the Consumer Private Preview are able to discover Native Application listings on the marketplace.

  5. Select Paid, then select Next.

    The listing is created in draft mode. Your can save the draft at any time to finish it later. You must provide data for each field of each section.

  6. Select a share for your listing:

    1. Under the Data Product area of the page, select Add next to Secure Share.

    2. Navigate to the share for your Native Application and select it.

      If your share does not appear, see Considerations When Creating and Publishing a Listing.

    3. Click Done.

    4. (Optional) You can change the default name for the secure share.

    5. Click Save.

  7. Select a pricing model for your listing:

    1. Under the Data Product area of the page, click Add next to Pricing.

    2. Select Query Based, then select one of the following options:

      • Per query

      • Per month

      • Per month and query

      Enter the pricing information for the option you select.

  8. Add information for the remaining areas:

    • Basic information

    • Details

    • Business Needs

    • Sample SQL Queries

    • Region Availability

    To publish your listing, you must provide information for each area. For a description of each section and related fields, see Basic information.

  9. After you complete all of the sections, the Submit for Approval button is activated, and you can submit the listing for approval.

    Note

    The Submit for Approval button is not activated if you are not the share owner.

  10. After the listing is reviewed by Snowflake, the state changes to Approved or Denied. If the listing has been denied, update the listing based on the feedback provided in comments, and resubmit it for approval.

    Snowflake data providers are notified when their listing is approved or denied. An email notification is sent to both Business Contact and Technical Contact email addresses in the provider profile associated with the listing.

Creating and Publishing a Free Private Listing

Free private listings can be completely free, or listed as free on Snowflake but paid for off-platform.

Note

When creating a private listing, the provider must create the listing in an account that is in the same regions as the consumer account. Creating shares across regions is not supported.

To create a free private listing:

  1. In Snowsight, select Data Products » Provider Studio.

  2. Select + Listing.

  3. Enter the title of your listing.

  4. Select Only Specified Consumers.

  5. Select Free, then click Next.

    The listing is created in draft mode. Your can save the draft at any time to finish it later. You must provide data for each field of each section.

  6. Select a share for your listing:

    1. Under the Data Product area of the page, select Add next to Secure Share.

    2. Navigate to the share for your Native Application and select it.

      If your share does not appear, see Considerations When Creating and Publishing a Listing.

    3. Select Done.

    4. (Optional) You can change the default name for the secure share.

    5. Select Save.

  7. Select consumer accounts you want to share:

    1. Under the Consumer Accounts area of the page, click Add.

    2. Enter the consumer account with whom you want to share the listing.

    3. Click Save.

  8. Add information for the remaining areas:

    • Basic information

    • Details

    • Business Needs

    • Sample SQL Queries

    • Region Availability

    To publish your listing, you must provide information for each area. For a description of each section and related fields, see Basic information.

  9. After you complete all of the sections, the Submit for Approval button is activated, and you can submit the listing for approval.

    Note

    The Submit for Approval button is not activated if you are not the share owner.

  10. After the listing is reviewed by Snowflake, the state changes to Approved or Denied. If the listing has been denied, update the listing based on the feedback provided in comments, and resubmit it for approval.

    Snowflake data providers are notified when their listing is approved or denied. An email notification is sent to both Business Contact and Technical Contact email addresses in the provider profile associated with the listing.

Creating and Publishing a Paid Private Listing

Note

When creating a private listing, the provider must create the listing in an account that is in the same regions as the consumer account. Creating shares across regions is not supported.

To create a paid private listing:

  1. In Snowsight, select Data Products » Provider Studio.

  2. Select + Listing.

  3. Enter the title of your listing.

  4. Select Only Specified Consumers.

  5. Select Paid, then click Next.

    The listing is created in draft mode. Your can save the draft at any time to finish it later. You must provide data for each field of each section.

  6. Select a share for your listing:

    1. Under the Data Product area of the page, select Add next to Secure Share.

    2. Navigate to the share for your Native Application and select it.

      If your share does not appear, see Considerations When Creating and Publishing a Listing.

    3. Select Done.

    4. (Optional) You can change the default name for the secure share.

    5. Select Save.

  7. Select a pricing model for your listing:

    1. Under the Data Product area of the page, click Add next to Pricing.

    2. Select Query Based, then select one of the following options:

      • Per query

      • Per month

      • Per month and query

      Enter the pricing information for the option you select.

  8. Select consumer accounts you want to share:

    1. Under the Consumer Accounts area of the page, click Add.

    2. sEnter the consumer account with whom you want to share the listing.

    3. Click Save.

  9. Add information for the remaining areas:

    • Basic information

    • Details

    • Business Needs

    • Sample SQL Queries

    • Region Availability

    To publish your listing, you must provide information for each area. For a description of each section and related fields, see Basic information.

  10. After you complete all of the sections, the Submit for Approval button is activated, and you can submit the listing for approval.

    Note

    The Submit for Approval button is not activated if you are not the share owner.

  11. After the listing is reviewed by Snowflake, the state changes to Approved or Denied. If the listing has been denied, update the listing based on the feedback provided in comments, and resubmit it for approval.

    Snowflake data providers are notified when their listing is approved or denied. An email notification is sent to both Business Contact and Technical Contact email addresses in the provider profile associated with the listing.

Adding Additional Consumers to Snowflake Native Applications

As a provider, follow these steps to add additional consumers after you have published your application.

  1. In Snowsight, select Data Products » Provider Studio.

  2. Select the Listings tab.

  3. Locate and click the listing to open.

  4. Under Consumer Accounts, select the Add button.

  5. In the Add Consumers window, under Consumer accounts, add the Snowflake accounts that you want to share your private listing with.

Revoking Access to Shares of Snowflake Native Applications

As a reminder, if you plan to drop a share, remove a consumer, or delete a listing, you should provide at least 30 days notice to the affected Marketplace Consumers. Alternatively, if you have a pre-existing agreement with a consumer, follow the removal terms governing that agreement.

As a provider, if you want to revoke access to a Native App for a specific consumer account, follow these steps to remove that account from the private listing:

  1. In Snowsight, select Data Products » Provider Studio.

  2. Select the Listings tab.

  3. Locate and select the listing.

  4. Under Consumer Accounts, locate the account to revoke, select the ellipsis to the right, and select Remove consumer.

As a provider, if you want to revoke access to a Native App for all consumer accounts, follow these steps to delete the listing:

  1. In Snowsight, select Data Products » Provider Studio.

  2. Select the Listings tab.

  3. Locate and select the listing.

  4. In the upper-right corner, select the Live dropdown list, and select Unpublish.

  5. In the upper-right corner, select the Delete (trash can) icon.

Protecting Provider’s Confidential Info

As Native Apps run in the consumer accounts, Snowflake redacts information that may contain provider’s confidential information from the following areas. Providers should reach out if you have specific feedback.

Information that is redacted from the consumers is described below.

  • The Definition Body of:

    • Views

    • Tasks

    • User Defined Functions (UDFs)

    • User Defined Table Functions (UDTFs)

    • Stored Procedures

    • Masking Policies

    • Row Access Policies

  • Definition and pattern of pipes

  • Query text and error message in query history

  • Condition, return value, and error message of tasks and task history

Note

To aid the debugging of your applications, providers will continue to see the information above that is redacted from the consumers otherwise.

Provider Best Practices

All objects in the application database are included in the ACCOUNT_USAGE schema, including those that are not exposed through APP_EXPORTER. Other than fields mentioned above, all other fields are visible to the consumer account. Providers should not include information that should remain confidential in any visible fields such as COMMENT.

Some objects, for example pipes and tasks, support a notification channel to receive errors, but sometimes the error message may contain information that should remain confidential. Providers should be cautious when adding consumer created notification channels to these objects.

Consumers: Installing and Using a Native App

To use a Native App as a consumer, you must use an account with a role (e.g. ACCOUNTADMIN) that has the following privileges at the account level:

  • IMPORT SHARE

  • CREATE DATABASE

Accepting Snowflake Provider and Consumer Terms of Service

To be able to install a Native App, the organization needs to accept Snowflake Provider and Consumer Terms of Service:

  • The organization administrator (ORGADMIN) role is required to complete the steps in this section. For information about organizations, see Managing Your Snowflake Organization.

  • To accept the terms, a user must have the following user properties set:

    • First name

    • Last name

    • Email address

  • If the user properties are not set, Snowflake displays a user error. A user administrator (i.e. user with the USERADMIN role) or a higher role, or another role with the OWNERSHIP privilege on your Snowflake user object, can add these details to your user profile.

  1. Log in using an account with the ORGADMIN role.

  2. Select the dropdown menu next to your login name, then click Switch Role » ORGADMIN to change to the organization administrator role.

  3. In Snowsight, select Admin » Billing & Terms.

  4. In the Snowflake Marketplace section, select Review.

  5. Review and check the box for provider and consumer terms.

  6. Select Accept Terms & Conditions.

  7. Select View Terms & Billing Info to close the dialog.

Installing a Native Application from Snowflake Marketplace

This section describes how to install a Native App from a marketplace listing in the Snowflake Marketplace UI.

  1. In Snowsight, select Data Products » Marketplace.

    A list of available listings is displayed.

  2. Browse the available listings or enter the name of your listing in the search box.

  3. Select the name of the listing you want to install.

  4. Select Get or Buy if the listing is a paid listing.

  5. In Create Database, type a name for your application.

  6. In Select Warehouse, select a warehouse used to install the application.

  7. Select Get.

Snowflake runs the installer script of your application to create a database with the name you specified.

Installing a Native Application from a Private Listing

This section describes how to install a Native App from a private listing in the Snowflake Marketplace UI. Consumers see the shared Native App as a private listing.

To install a shared Native App:

  1. In Snowsight, select Data Products » Private Sharing.

  2. In the Privately Shared Listings section, click the application that you want to use.

  3. Select Get or Buy if the listing is a paid listing.

  4. In Create Database, type a name for your application.

  5. In Select Warehouse, select a warehouse used to install the application.

  6. Select Get.

Snowflake runs the installer script of your application to create a database with the name you specified.

Using a Snowflake Native Application

After the database is created successfully:

  1. Select the installed application using one of the following options:

    • Select the Manage button after the installation step, or

    • In Snowsight, select Data Products » Private Sharing, and then locate and select the application under Privately Shared Listings

  2. Select the Open button to open a new worksheet.

    • If the provider has included sample queries in the listing, then the worksheet will be populated with these samples.

    • Alternatively, hover over one of the Usage Examples, and click Open in Worksheets to only include the select example.

Granting Access on Objects to a Snowflake Native Application

If you need to allow a Native App to access an object in a consumer account or need to authorize the application to perform a specific operation in the consumer account, you have to grant the necessary privileges to the application through a role.

For example:

CREATE ROLE my_app_role;
GRANT SELECT ON TABLE consumer_db.consumer_schema.consumer_table TO ROLE my_app_role;
GRANT ROLE my_app_role TO DATABASE my_db_for_data_app;
Copy

Uninstalling a Snowflake Native Application from a Consumer Account

To uninstall a Native App from a consumer account, drop the database for the application by executing the DROP DATABASE command. For example:

DROP DATABASE my_db_for_data_app;
Copy

Snowflake Native Application Examples

The following sections provide examples of Native Apps:

Example: Snowflake Native Application with Simple Stored Procedures

The following example is a Native App that exposes a stored procedure to add two numbers to the consumers. The actual logic is implemented in a shared stored procedure and users in consumer accounts can use this stored procedure once they have installed this application in their accounts.

This is our “Hello World” example.

Providers: Creating a Snowflake Native Application

The provider needs to create the database, schema, shared stored procedures and the installer script for the Native App.

  1. Create a database named math_app for your application and a schema named app_schema.

    CREATE DATABASE math_app;
CREATE SCHEMA math_app.app_schema;
    Copy

  2. Create the stored procedure for your application. Note the use of Snowflake Scripting language for this stored procedure.

    CREATE PROCEDURE math_app.app_schema.sum(num1 float, num2 float)
RETURNS FLOAT
LANGUAGE SQL
EXECUTE AS OWNER
AS $$
  begin
    return :NUM1 + :NUM2;
  end;

$$;
    Copy

  3. Create a database role and grant USAGE privilege for the stored procedure to this database role.

    CREATE DATABASE ROLE shared_db_role;

GRANT USAGE ON DATABASE math_app TO
DATABASE ROLE shared_db_role;

GRANT USAGE ON SCHEMA math_app.app_schema TO
DATABASE ROLE shared_db_role;

GRANT USAGE ON PROCEDURE math_app.app_schema.sum(float, float) TO
DATABASE ROLE shared_db_role;
    Copy

  4. Create the stored procedure for the installer script.

    In the installer script, simply grant the shared database role to APP_EXPORTER which would expose the stored procedure to the consumer. Note the use of Snowflake Scripting language used for the installer script.

    CREATE PROCEDURE math_app.app_schema.installer()
RETURNS STRING
LANGUAGE SQL
EXECUTE AS OWNER
AS $$
  begin
    GRANT DATABASE ROLE shared_db_role TO DATABASE ROLE APP_EXPORTER;
    return 'installer script Done';
  end;
$$;
    Copy

  5. Create a share for your application, and specify that the stored procedure named math_app.app_schema.installer should be used as the installer script for this application:

    CREATE SHARE math_share installer = math_app.app_schema.installer();
    Copy

  6. Add the database, schema, installer script and the database role to the share:

    GRANT USAGE ON DATABASE math_app TO SHARE math_share;
GRANT USAGE ON SCHEMA math_app.app_schema TO SHARE math_share;
GRANT USAGE ON PROCEDURE math_app.app_schema.installer() TO SHARE math_share;
GRANT DATABASE ROLE shared_db_role to SHARE math_share;
    Copy

  7. Publish the app.

Consumers: Using a Snowflake Native Application

After a consumer installs the Native App, they can call the stored procedure sum in the application to add two numbers:

CALL math_share.app_schema.sum(2, 3);

+-------------+
|     SUM     |
|-------------|
|     5.0     |
+-------------+
Copy

Example: Using a Snowflake Native Application with Tasks

The following example is a Native App that consists of tasks that run in the background. Users in consumer accounts can configure this application to specify the warehouse that should be used.

Providers: Creating a Snowflake Native Application with Tasks

To create the Native App in the provider account:

  1. Create a database named time_app for your application and a schema named app_schema.

    CREATE DATABASE time_app;
CREATE SCHEMA time_app.app_schema;
    Copy

  2. Create the stored procedures that is shared from the provider account.

    • A procedure named record_time that stores the current time in the table time_table. This procedure is not exposed to the consumer but can be accessed within the application on the consumer side.

    • A procedure named start_service for creating a task that runs the record_time stored procedure. This procedure is exposed to the consumer via a shared database role.

    CREATE PROCEDURE time_app.app_schema.record_time()
RETURNS STRING
LANGUAGE SQL
EXECUTE AS OWNER
AS $$
   begin
      INSERT INTO time_schema.time_table VALUES(CURRENT_TIMESTAMP);
      return 'CURRENT TIMESTAMP RECORDED';
   end;
$$;

-- Procedure that will be exposed to the consumer
CREATE PROCEDURE time_app.app_schema.start_service(WH_NAME string)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS $$

var create_task_cmd = "CREATE TASK IF NOT EXISTS time_schema.insert_time warehouse = ?"
  + " SCHEDULE = '5 MINUTE'"
  + " as call app_schema.record_time()";

snowflake.execute({ sqlText: create_task_cmd, binds: [WH_NAME] });

  var create_task_cmd = "create task if not exists time_schema.insert_time warehouse = "
    + WH_NAME
    + " SCHEDULE = '5 MINUTE'"
    + " as call app_schema.record_time()";

  snowflake.execute({ sqlText: create_task_cmd });
  snowflake.execute({ sqlText: `ALTER TASK time_schema.insert_time resume` });
  return "SERVICE STARTED";

$$;
    Copy

  3. Create two database roles. Grant USAGE privilege for the stored procedure exposed to consumers to one database role shared_db_role that is granted to APP_EXPORTER, and grant USAGE privilege for the stored procedure not exposed to consumers to another database role hidden_db_role.

    CREATE DATABASE ROLE shared_db_role;
GRANT USAGE ON DATABASE time_app TO DATABASE ROLE shared_db_role;
GRANT USAGE ON SCHEMA time_app.app_schema TO DATABASE ROLE shared_db_role;
GRANT USAGE ON PROCEDURE time_app.app_schema.start_service(string) TO DATABASE ROLE shared_db_role;
CREATE DATABASE ROLE hidden_db_role;
GRANT USAGE ON DATABASE time_app TO DATABASE ROLE hidden_db_role;
GRANT USAGE ON SCHEMA time_app.app_schema TO DATABASE ROLE hidden_db_role;
GRANT USAGE ON PROCEDURE time_app.app_schema.record_time() TO DATABASE ROLE hidden_db_role;
    Copy

  4. Create the stored procedure for the installer script in app_schema.

    In this stored procedure, add code that will create the following.

    • A schema named time_schema for the application

    • A table named time_table to store the data for the application.

    To make these objects accessible to the roles in consumer accounts, you must grant the USAGE privilege on these objects to the APP_EXPORTER database role. Note the use of Snowflake Scripting language for this stored procedure.

    CREATE PROCEDURE time_app.app_schema.installer()
RETURNS STRING
LANGUAGE SQL
EXECUTE AS OWNER
AS $$
  begin

    CREATE SCHEMA time_schema;
    CREATE TABLE time_schema.time_table(ts timestamp);
    GRANT USAGE ON SCHEMA time_schema TO DATABASE ROLE APP_EXPORTER;

    GRANT SELECT ON TABLE time_schema.time_table TO DATABASE ROLE APP_EXPORTER;

    -- DON'T grant hidden_db_role to app_exporter
    -- since we don't want to expose the the procedure
    -- record_time to consumer

    GRANT DATABASE ROLE shared_db_role TO DATABASE ROLE APP_EXPORTER;

    return 'installer script Done';

  end;
$$;
    Copy

  5. Create a share for the application, and specify that the stored procedure named time_app.app_schema.installer should be used as the installer script for this application:

    CREATE SHARE time_share installer = time_app.app_schema.installer();
    Copy

  6. Add the database, schema, installer script and database role to the share:

    GRANT USAGE ON DATABASE time_app TO SHARE time_share;
GRANT USAGE ON SCHEMA time_app.app_schema TO SHARE time_share;
GRANT USAGE ON PROCEDURE time_app.app_schema.installer() TO SHARE time_share;
GRANT DATABASE ROLE hidden_db_role TO SHARE time_share;
GRANT DATABASE ROLE shared_db_role TO SHARE time_share;
    Copy

  7. Publish the app.

Consumers: Using a Snowflake Native Application with Tasks

To use the Native App with tasks in consumer accounts:

  1. Grant the privileges needed to the application. These include:

    • The privilege to run the task on your account.

    • The privilege to use your warehouse to run the task.

    To grant these privileges to the application, first grant them to an account role, then grant the account role to the application database:

    CREATE ROLE app_role;
GRANT EXECUTE TASK ON ACCOUNT to ROLE app_role;
GRANT USAGE ON WAREHOUSE my_warehouse TO ROLE app_role;
GRANT ROLE app_role TO DATABASE time_db;
    Copy

  2. Call the stored procedure to initialize the service and start running the service in the background.

    CALL time_db.app_schema.start_service('my_warehouse');
    Copy

  3. After the application begins writing timestamps to the table named time_table, query the table to verify that the data application is working as expected.

    SELECT * FROM time_db.time_schema.time_table;
    Copy

Example: Snowflake Native Application with Shared External Functions

This example shows how a provider can share external functions within a Native App. A provider can share an external function when all consumers of their app use the same external function and API gateways. This example uses AWS Lambda as the remote service for the external function in this example.

In this example, the provider directly shares an external function with the consumer. The function runs using the integration defined at the provider side, utilizing credentials defined by the provider.

Providers: Creating a Snowflake Native Application with External Functions

This Native App example assumes the following:

  • An AWS Lambda function echo is available at the endpoint. For example https://xyz.execute-api.us-west-2.amazonaws.com/production/echo takes a string as input and returns the value back as its result.

  • An Amazon API Gateway has been set up.

  • AWS IAM role with ARN arn:aws:iam::123456789012:role/echo_role has been set up and approved to access Lambda.

To create the Native App in the provider account:

  1. Create an integration for accessing the echo endpoint:

    CREATE API INTEGRATION aws_echo_api_integration
  API_PROVIDER = aws_api_gateway
  API_AWS_ROLE_ARN = 'arn:aws:iam::123456789012:role/echo_role'
  API_ALLOWED_PREFIXES = ('https://xyz.execute-api.us-west-2.amazonaws.com/')
  ENABLED = true;
    Copy

  2. Create a database named aws_echo_app for your application and a schema named app_schema.

    CREATE DATABASE aws_echo_app;
CREATE SCHEMA aws_echo_app.app_schema;
    Copy

  3. Create the external function:

    CREATE SECURE EXTERNAL FUNCTION aws_echo_app.app_schema.ext_echo(s STRING)
  RETURNS STRING
  API_INTEGRATION = aws_echo_api_integration
  AS 'https://xyz.execute-api.us-west-2.amazonaws.com/echo';
    Copy

  4. Create a database role that enables the external function to be shared:

    CREATE DATABASE ROLE aws_echo_app.app_share_role;
GRANT USAGE ON DATABASE aws_echo_app
  TO DATABASE ROLE aws_echo_app.app_share_role;
GRANT USAGE ON SCHEMA aws_echo_app.app_schema
  TO DATABASE ROLE aws_echo_app.app_share_role;
GRANT USAGE ON FUNCTION aws_echo_app.app_schema.ext_echo(STRING)
  TO DATABASE ROLE aws_echo_app.app_share_role;
    Copy

  5. Create the installer exposing the database role:

    CREATE OR REPLACE PROCEDURE aws_echo_app.app_schema.installer()
RETURNS STRING
LANGUAGE SQL
AS $$
BEGIN
  GRANT DATABASE ROLE app_share_role TO DATABASE ROLE APP_EXPORTER;
  RETURN 'Done!';
END;
$$;
    Copy

  6. Create a share for the application and specify that the stored procedure named aws_echo_app.app_schema.installer should be used as the installation script for this application:

    CREATE SHARE echo_share INSTALLER = aws_echo_app.app_schema.installer();
    Copy

  7. Add the database, database role, and installation script to the share:

    GRANT USAGE ON DATABASE aws_echo_app TO SHARE echo_share;
GRANT DATABASE ROLE aws_echo_app.app_share_role TO SHARE echo_share;
GRANT USAGE ON PROCEDURE aws_echo_app.app_schema.installer() TO SHARE echo_share;
    Copy

  8. Publish the Snowflake Native Application

    To make the background Native App available to a consumer account, add the consumer account to the share from the provider account:

    ALTER SHARE echo_share ADD ACCOUNTS = my_consumer_account;
    Copy

Consumers: Install the Native Application

This example assumes that the application database name used during the installation process is echo_db.

  1. Create a FIREWALL_CONFIGURATION rule that allows access by the application to the endpoint hosting the echo function.

    Note

    This operation must be performed by either ACCOUNTADMIN or a user with MANAGE FIREWALL_CONFIGURATION.

    ALTER DATABASE echo_db SET FIREWALL_CONFIGURATION = ('https://xyz.execute-api.us-west-2.amazonaws.com');
    Copy

  2. Use the Snowflake Native Application

    To use the Native App with an external function shared to the consumer account:

    SELECT echo_db.app_schema.ext_echo('hello!') AS OUTPUT;
    Copy
    +----------+
| OUTPUT   |
|----------|
| hello!   |
+----------+

Example: Using Installed External Functions in a Snowflake Native Application

This example shows how to use an installed external function within a Native App. In this case, the application creates a local instance of an external application in the installer for every consumer installing this application.

The installed external function utilizes an API INTEGRATION supplied by the consumer, allowing the consumer control over the accessed endpoint as well as the credentials utilized when invoking the function.

Providers: Creating a Snowflake Native Application with External Functions

This Native App example assumes the following:

  • An AWS Lambda function sum is available at the endpoint. For example https://xyz.execute-api.us-west-2.amazonaws.com/production/sum that adds two numbers and returns the sum.

  • An Amazon API Gateway has been set up.

  • An AWS IAM role with ARN arn:aws:iam::123456789012:role/math_app_role has been set up and approved to access Lambda.

The details about setting up AWS Lambda and/or API Gateway can be found in the step-by-step guide here.

  1. Create a database named aws_math_app for your application and a schema named app_schema.

    CREATE DATABASE aws_math_app;
CREATE SCHEMA aws_math_app.app_schema;
    Copy

  2. Create the stored procedure that is shared to the application in app_schema.

    • A procedure named setup for creating the external function. This procedure will be exposed to the consumer.

    • A procedure named sum that calls the external function to compute the sum. This procedure will be exposed to the consumer.

    CREATE PROCEDURE aws_math_app.app_schema.setup(integration_name string)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS $$
var endpoint = "https://xyz.execute-api.us-west-2.amazonaws.com/production/sum";

    var create_ext_func_cmd = "create external function math_schema.ext_sum(num1 float, num2 float)"
        + " returns float"
        + " api_integration = " + INTEGRATION_NAME
        + " as '" + endpoint + "'";
    snowflake.execute({ sqlText: create_ext_func_cmd });

    return "SETUP COMPLETED";
$$;
    Copy
    CREATE PROCEDURE aws_math_app.app_schema.sum(NUM1 FLOAT, NUM2 FLOAT)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS $$
var call_ext_func_cmd = "select math_schema.ext_sum(?, ?)";
var res = snowflake.execute({ sqlText: call_ext_func_cmd, binds: [NUM1, NUM2] });
res.next();

return res.getColumnValue(1);
$$;
    Copy

  3. Create a database role and grant USAGE privilege for the stored procedures to this database role.

    CREATE DATABASE ROLE shared_db_role;
GRANT USAGE ON DATABASE aws_math_app to DATABASE ROLE shared_db_role;
GRANT USAGE ON SCHEMA aws_math_app.app_schema TO DATABASE ROLE shared_db_role;
GRANT USAGE ON PROCEDURE aws_math_app.app_schema.setup(string) TO DATABASE ROLE shared_db_role;
GRANT USAGE ON PROCEDURE aws_math_app.app_schema.sum(string) TO DATABASE ROLE shared_db_role;
    Copy

  4. Create the stored procedure for the installer script in app_schema and grant the shared database role to APP_EXPORTER. Note the use of Snowflake Scripting language for this stored procedure.

    CREATE PROCEDURE aws_math_app.app_schema.installer()
RETURNS STRING
LANGUAGE SQL
EXECUTE AS OWNER
AS $$
  begin
    CREATE SCHEMA math_schema;
    GRANT DATABASE ROLE shared_db_role TO DATABASE ROLE APP_EXPORTER;
    return 'installer script Done';
  end;
$$;
    Copy

  5. Create a share for the application, and specify that the stored procedure named aws_math_app.app_schema.installer should be used as the installer script for this application:

    CREATE SHARE math_share installer = aws_math_app.app_schema.installer();
    Copy

  6. Add the database, schema, installer script and database role to the share:

    GRANT USAGE ON DATABASE aws_math_app TO SHARE math_share;
GRANT USAGE ON SCHEMA aws_math_app.app_schema TO SHARE math_share;
GRANT DATABASE ROLE shared_db_role TO SHARE math_share;
GRANT USAGE ON PROCEDURE aws_math_app.app_schema.installer() TO SHARE math_share;
    Copy

  7. Publish the app

Consumers: Using a Snowflake Native Application with External Functions

To use the Native App with an external function in the consumer account:

  1. Create an API integration with the prefix of the endpoint specified.

    CREATE API INTEGRATION math_app_integration
  API_PROVIDER=aws_api_gateway
  API_AWS_ROLE_ARN='arn:aws:iam::123456789012:role/math_app_role'
  API_ALLOWED_PREFIXES=('https://xyz.execute-api.us-west-2.amazonaws.com/production/')
  ENABLED=true;
    Copy

  2. Grant the privilege needed to the application, which is the privilege to use the API integration created.

    Privileges cannot be granted to the application directly. They need to be granted to a role first, then the role is granted to the application, using the GRANT ROLE <role_name> TO DATABASE <data_app_db_name> command:

    CREATE ROLE app_role;
GRANT USAGE ON INTEGRATION math_app_integration to ROLE app_role;
GRANT ROLE app_role TO DATABASE math_db;
    Copy

  3. Call the stored procedure to set up the service.

    CALL math_db.app_schema.setup('math_app_integration');
    Copy

  4. Call the stored procedure to calculate the sum of two numbers

    CALL math_db.app_schema.sum(2, 3);

  +-------------+
  |     SUM     |
  |-------------|
  |     5.0     |
  +-------------+
    Copy

Example: Snowflake Native Application with Reference Data from the Provider Account

In this example, a Native App uses a reference dataset from the provider account to return matching phone numbers for names supplied by the consumer(s) of this application. The reference dataset used by the stored procedures exposed to the consumers is private to the application and is NOT exposed to the consumer. This is a commonly used pattern by providers of enrichment data applications who have proprietary datasets in their Snowflake accounts.

Provider: Creating a Snowflake Native Application with Reference Data from the Provider Account

To create the Native App in the provider account:

  1. Prepare the reference data of phone numbers and names.

    CREATE DATABASE ref_db;
CREATE SCHEMA ref_db.ref_schema;
CREATE TABLE ref_db.ref_schema.ref_data (name varchar(100), phone varchar(100) );

INSERT INTO ref_data (name, phone) VALUES
    ('John', '666-333-7777'),
    ('Doe',  '666-444-7777'),
    ('Jane', '666-555-7777'),
    ('Jane', '666-666-7777');
    Copy

  2. Create a database for the application. Within the database, create an install_schema schema that contains an installer script, and a share_schema that contains a view accessing the reference dataset.

    CREATE DATABASE app_db;
CREATE SCHEMA app_db.install_schema;
CREATE SCHEMA app_db.share_schema;
    Copy

  3. Create a view over reference dataset that is used by the application.

    CREATE OR REPLACE SECURE VIEW share_schema.ref_view AS SELECT * FROM ref_db.ref_schema.ref_data;
    Copy

  4. Create a shared stored procedures that is shared to the application in share_schema.

    This example includes two procedures named enrich_name and enrich_names which take name(s) and return matching phone numbers from the reference dataset. These two procedures are exposed to the consumer.

    CREATE PROCEDURE app_db.share_schema.enrich_name(NAME VARCHAR)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS $$

  var sqlcmd = "select phone from share_schema.ref_view where name = ?";
  var rset = snowflake.execute({ sqlText: sqlcmd, binds: [NAME] });
  var phones = "";

  while(rset.next())
    phones = phones + rset.getColumnValue(1) + " ";
  return phones;
$$;
    Copy
    CREATE PROCEDURE app_db.share_schema.enrich_names(fqtn VARCHAR, namecol VARCHAR, phonecol VARCHAR)
RETURNS FLOAT
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS $$
sqlcmd = "select * from "+FQTN+" ";
var rset = snowflake.execute({ sqlText: sqlcmd });
var count = 0;

while(rset.next()) {
  var name = rset.getColumnValue(2);
  sqlcmd = "call share_schema.enrich_name('" + name + "')";
  var rset1 = snowflake.execute({ sqlText: sqlcmd });
  rset1.next();

    var phones = rset1.getColumnValue(1);

    if (phones.length > 0) {
      sqlcmd = "update " + FQTN + " set " + PHONECOL + " = '" + phones +
      "' where " + NAMECOL + " = '" + name + "'";
      count++;
    }
    snowflake.execute({ sqlText: sqlcmd });
}

return count;

$$;
    Copy

  5. Create two database roles. Grant USAGE privilege for the stored procedures exposed to consumers to one database role. Grant SELECT privilege for the view not exposed to consumers to another role.

    CREATE DATABASE ROLE shared_db_role;
GRANT USAGE ON DATABASE app_db TO DATABASE ROLE shared_db_role;
GRANT USAGE ON SCHEMA app_db.share_schema TO DATABASE ROLE shared_db_role;
GRANT USAGE ON PROCEDURE app_db.share_schema.enrich_name(string) TO DATABASE ROLE shared_db_role;
GRANT USAGE ON PROCEDURE app_db.share_schema.enrich_names (string, string, string) to DATABASE ROLE shared_db_role;
CREATE DATABASE ROLE hidden_db_role;
GRANT SELECT on VIEW app_db.share_schema.ref_view TO DATABASE ROLE hidden_db_role;
    Copy

  6. Create the installer script that grants the shared database role to APP_EXPORTER.

    Note that the hidden_database_role that the reference dataset and the view are granted to is not granted to the APP_EXPORTER role. Note the use of Snowflake Scripting language used for the installer script.

    CREATE PROCEDURE app_db.install_schema.installer()
RETURNS STRING
LANGUAGE SQL
EXECUTE AS OWNER
AS $$
  begin
    GRANT DATABASE ROLE shared_db_role TO DATABASE ROLE APP_EXPORTER;
    return 'installer script Done';
  end;
$$;
    Copy

  7. Create a share for the application, and specify that the stored procedure named app_db.install_schema.installer should be used as the installer script for this application

    CREATE OR REPLACE SHARE app_share installer = app_db.install_schema.installer();
    Copy

  8. Add the application database, schema, installer script and database role to the share. Note the use of granting reference_usage on the reference dataset to the share. The reference_usage needs to be granted to the share directly and before granting the database roles to the share. See Sharing Data from Multiple Databases for more information.

    GRANT USAGE ON DATABASE app_db to SHARE app_share;
GRANT USAGE ON SCHEMA app_db.install_schema to SHARE app_share;
GRANT USAGE ON PROCEDURE app_db.install_schema.installer() TO SHARE app_share;
GRANT REFERENCE_USAGE on database ref_db to SHARE app_share;
GRANT DATABASE ROLE shared_db_role to SHARE app_share;
GRANT DATABASE ROLE hidden_db_role to SHARE app_share;
    Copy

  9. Publish the app.

Consumer: Using a Snowflake Native Application with Reference Data from the Provider Account

To use this Native App in the consumer account:

  1. Call enrich_name with a single name to get matching phone numbers. The application uses the reference datasets to return matching phone numbers.

    CALL myapp.share_schema.enrich_name('John');
CALL myapp.share_schema.enrich_name('Doe');
CALL myapp.share_schema.enrich_name('Jane');
    Copy

  2. Create an enrichment dataset to enrich phone numbers from names. This dataset is missing phone numbers.

    CREATE OR REPLACE DATABASE cdb;
CREATE OR REPLACE SCHEMA cdb.csch;

CREATE OR REPLACE TABLE ctbl (id number, name varchar(100), phone varchar2(100), city varchar2(20));

INSERT INTO ctbl (id, name, city) VALUES (1, 'John', 'TORONTO');
INSERT INTO ctbl (id, name, city) VALUES (2, 'Doe', 'TORONTO');
INSERT INTO ctbl (id, name, city) VALUES (3, 'Mark', 'NYC');
INSERT INTO ctbl (id, name, city) VALUES (4, 'Michael', 'SEATTLE');
INSERT INTO ctbl (id, name, city) VALUES (1, 'Jane', 'SAN MATEO');
    Copy

  3. Assign usage on the enrichment dataset to the application.

    CREATE OR REPLACE ROLE app_role;
GRANT USAGE ON DATABASE cdb TO ROLE app_role;
GRANT USAGE ON SCHEMA cdb.csch TO ROLE app_role;
GRANT SELECT ON TABLE cdb.csch.ctbl TO ROLE app_role;
GRANT UPDATE on TABLE cdb.csch.ctbl TO ROLE app_role;
GRANT ROLE app_role to DATABASE myapp;
    Copy

  4. Call enrich_names() and pass the enrichment dataset from the consumer account as a parameter to the config table.

    CALL myapp.share_schema.enrich_names('cdb.csch.ctbl','name','phone');
SELECT * FROM ctbl;
    Copy

Example: Using Python and Java in a Native App

In this example, a Native App creates a series of java and python functions and procedures and shares them directly with the consumer. This example includes the sharing of very simple “echo” functions and procedures (that returns the value that was passed to them) as well as examples of procedures that access data from a shared table.

Note

For simplicity, these examples do not include uploading of external code (e.g. Python modules and JAR files.) Documentation for this functionality is available at the following links:

Providers: Create the Native Application

To create the application in the provider account:

  1. Create the database to be shared, as well as reference data to be accessed by the shared stored procedures. We also create the stage java_py_stage which will be used to hold the Java jar that is generated when the Java functions and procedures are created.

    CREATE DATABASE java_py_app;
CREATE SCHEMA java_py_app.app_schema;

CREATE STAGE java_py_stage;

CREATE TABLE data_table (x INT);
INSERT INTO data_table
  SELECT SEQ4() FROM TABLE(GENERATOR(ROWCOUNT=>10));
    Copy

  2. Create an echo function and procedure in python, as well as a procedure that sums the value of a given column of a given table.

    CREATE SECURE FUNCTION inline_py_echo_func(str STRING)
  RETURNS STRING
  LANGUAGE PYTHON
  RUNTIME_VERSION=3.8
  HANDLER='run'
  AS
$$

def run(str):
  return 'inline_py_echo_func: ' + str
$$;

CREATE SECURE PROCEDURE inline_py_echo_proc(str STRING)
  RETURNS STRING
  LANGUAGE PYTHON
  RUNTIME_VERSION=3.8
  PACKAGES=('snowflake-snowpark-python')
  HANDLER='run'
  AS
$$

def run(session, str):
  return 'inline_py_echo_proc: ' + str
$$;

CREATE SECURE PROCEDURE inline_py_sum_proc(table_name STRING, col_name STRING)
  RETURNS NUMBER
  LANGUAGE PYTHON
  RUNTIME_VERSION=3.8
  PACKAGES=('snowflake-snowpark-python')
  HANDLER='run'
  AS
$$

def run(session, table_name, col_name):
  return session.sql(f"select sum({col_name}) from {table_name}").collect()[0][0];
$$;
    Copy

  3. Create identical functions using Java

    CREATE SECURE FUNCTION inline_java_echo_func(str STRING)
  RETURNS STRING
  LANGUAGE JAVA
  RUNTIME_VERSION=11
  HANDLER='InlineJavaFunc.run'
  TARGET_PATH='@java_py_stage/InlineJavaFunc.jar'
  AS
$$

class InlineJavaFunc {
  public static String run(String str) {
    return "inline_java_echo_func: " + str;
  }
}
$$;

CREATE SECURE PROCEDURE inline_java_echo_proc(str STRING)
  RETURNS STRING
  LANGUAGE JAVA
  RUNTIME_VERSION=11
  HANDLER='InlineJavaProc.run'
  PACKAGES = ('com.snowflake:snowpark:latest')
  TARGET_PATH='@java_py_stage/InlineJavaProc.jar'
  AS
$$
import com.snowflake.snowpark_java.*;

class InlineJavaProc {
  public static String run(Session session, String str) {
    return "inline_java_echo_proc: " + str;
  }
}
$$;

CREATE SECURE PROCEDURE inline_java_sum_proc(tableName STRING, colName STRING)
  RETURNS NUMBER
  LANGUAGE JAVA
  RUNTIME_VERSION=11
  HANDLER='InlineJavaSumProc.run'
  PACKAGES = ('com.snowflake:snowpark:latest')
  TARGET_PATH='@java_py_stage/InlineJavaSumProc3.jar'
  AS
$$
import com.snowflake.snowpark_java.*;

class InlineJavaSumProc {
  public static long run(Session session, String tableName,
      String colName) {
    return session.sql("select sum(" + colName + ") from "
      + tableName).collect()[0].getLong(0);
  }
}
$$;
    Copy

  4. Create the database role app_share_role through which the application will share all of the functions as well as the table that is accessed by the sum functions:

    CREATE OR REPLACE DATABASE ROLE app_share_role;
GRANT USAGE ON DATABASE java_py_app
  TO DATABASE ROLE app_share_role;
GRANT USAGE ON SCHEMA java_py_app.app_schema
  TO DATABASE ROLE app_share_role;
GRANT USAGE ON FUNCTION inline_py_echo_func(string)
  TO DATABASE ROLE app_share_role;
GRANT USAGE ON PROCEDURE inline_py_echo_proc(string)
  TO DATABASE ROLE app_share_role;
GRANT USAGE ON PROCEDURE inline_py_sum_proc(string, string)
  TO DATABASE ROLE app_share_role;
GRANT USAGE ON FUNCTION inline_java_echo_func(string)
  TO DATABASE ROLE app_share_role;
GRANT USAGE ON PROCEDURE inline_java_echo_proc(string)
  TO DATABASE ROLE app_share_role;
GRANT USAGE ON PROCEDURE inline_java_sum_proc(string, string)
  TO DATABASE ROLE app_share_role;
GRANT SELECT ON TABLE data_table
  TO DATABASE ROLE app_share_role;
    Copy

  5. Create the installer procedure that exposes the app_share_role to the consumer.

    CREATE PROCEDURE installer()
  RETURNS STRING
  LANGUAGE SQL
AS
$$
BEGIN
  GRANT DATABASE ROLE app_share_role TO DATABASE ROLE APP_EXPORTER;
  RETURN 'Done!';
END;
$$;
    Copy

  6. Create the share, exposing the installer and database role to the share.

    CREATE SHARE java_py_share INSTALLER = installer();
GRANT USAGE ON DATABASE java_py_app TO SHARE java_py_share;
GRANT USAGE ON SCHEMA java_py_app.app_schema TO SHARE java_py_share;
GRANT DATABASE ROLE app_share_role TO SHARE java_py_share;
GRANT USAGE ON PROCEDURE installer() TO SHARE java_py_share;
    Copy

  7. Publish the Snowflake Native Application

    To make the background application available to a consumer account, add the consumer account to the share from the provider account:

    ALTER SHARE java_py_share ADD ACCOUNTS = my_consumer_account;
    Copy

Consumers: Install the Snowflake Native Application

To use the application named java_py_db using a shared external function:

create database java_py_db from share provider.java_py_app;
select java_py_db.app_schema.inline_py_echo_func('hello!') as output;
call java_py_db.app_schema.inline_py_echo_proc('hello!');
call java_py_db.app_schema.inline_py_sum_proc('data_table', 'x');
select java_py_db.app_schema.inline_java_echo_func('hello!');
call java_py_db.app_schema.inline_java_echo_proc('hello!');
call java_py_db.app_schema.inline_java_sum_proc('data_table', 'x');
Copy

Reference

MANAGE FIREWALL_CONFIGURATION

The MANAGE FIREWALL_CONFIGURATION privilege enables users to set the FIREWALL_CONFIGURATION database property. This property determines whether a Native App can connect to shared external functions. The MANAGE FIREWALL_CONFIGURATION privilege enables a user to set this property in the following contexts:

  • Create a new database that includes the FIREWALL_CONFIGURATION property

  • Alter their existing database and specify or remove the FIREWALL_CONFIGURATION property

By default, users with account admin privileges are assigned this privilege. Database owners do not have this privilege by default.

FIREWALL_CONFIGURATION

The FIREWALL_CONFIGURATION database property enables you to specify URL prefixes that are approved to connect to a shared external function. To set this property, you must have the MANAGE FIREWALL_CONFIGURATION privilege.

Note

You can only set this property on a database created from a Snowflake Native Application share.

This property specifies one or more URLs that determine which domains can connect to the shared external function. Each URL must conform to the following requirements:

  • The URL must be in a valid, parsable, URL format.

  • The protocol specified must be HTTPS. All other protocols are considered invalid.

  • A hostname must be provided, and must not contain the ‘*’ wildcard character

  • References must not be specified (e.g. http://www.example.com#reference)

  • Queries must not be specified (e.g. http://www.example.com?a=b&c=d)

Snowflake Native Applications Onboarding

Note

The Private Preview program for Snowflake Native Apps is closed to new participants.

Existing providers and new providers in PrPr are required to confirm the following information with Snowflake before publishing the Native App.

  • The region where the application will be published

  • The organization name to which the provider account belongs

  • The account locator for the account used to publish the application

  • The test account locator used by the provider to test application installation

Note

If the application is expected to be used by consumers from multiple regions, then the provider must supply the above information for each such region.

Onboarding Consumers to Your Applications

Before adding consumers to your application, providers should first provide Snowflake the List of consumers who will install the application including the consumer’s:

  • Org name

  • Account name

  • Region that they are in.

After Snowflake receives this information, we’ll enable these customer accounts to participate in the PrPr. Once this is done, providers can use private listings to publish their applications.

Application Reviews

All Snowflake applications will undergo a review process that includes reviewing the provider and the application.

Provider Review Process

Note

The ability to share applications via listing is only available to customers who have agreed to the Snowflake Provider and Consumer Terms of Service made available at https://www.snowflake.com/legal.

During PrPr you will be able to publish under your org name and account identifier, but we strongly recommend that you create a provider profile to make the transition from PrPr to PuPr easier.

Provider Profiles contain general information about you: company logo, description, customer contact email, etc.. You will also be able to enter a business and technical contact that Snowflake can use to reach you.

To create a profile in Snowsight, navigate to Data Products » Provider Studio » Profiles » Add Profile. Select Submit for Approval after you finish inputting your information. See a Video Tutorial or see About listing providers.

Snowflake reviews your Provider Profile to ensure that you meet the application provider requirements. Profile reviews occur within 3 business days (often faster).

Application Review Process

In addition to the above information, please submit a marketplace case to get into contact with the marketplace operations team. This case will create an email thread that you use to communicate with the Marketplace Operations team at Snowflake.

The Operations team will conduct several reviews to ensure your application adheres to the provider policies and consumer security expectations. Please publish your application to your targeted consumers through a Private Listing (in UI Only Specified Consumers). In parallel we’ll conduct these reviews, which can take up to two weeks. We’ll follow up on any open questions or missing requirements during this period of time.

Language: English