Tutorial: Developing an Application with the Native Apps Framework¶

What You Will Learn¶

In this tutorial, you will learn how to:

• Create an application package that contains the data and business logic of your application.

• Share data with an application package.

• Add business logic to an application package.

• Test the application locally.

• View and test the application in Snowsight.

• Publish your application by creating a private listing.

• Install the application from a private listing.

About Providers and Consumers¶

Within the context of the Native Apps Framework, providers are the roles and organizations who have data and business logic that they want to share with other Snowflake users, who are the consumers. A consumer can be another account within your organization, a different organization within your company, or a Snowflake user in another company.

Within the context of this tutorial, most of the tasks you will perform are those typically performed by providers, but these include tasks that may be performed by multiple roles within your organization including application developers and database administrators.

In this tutorial, you will also perform tasks that mimic the actions performed by consumers to install an application.

Prerequisites¶

• You must run all of the SQL commands in the same SQL command session because the session context is required.

  To do this in Snowsight, for example, paste all of your code into the same worksheet as you go along. As you progress from section to section, each section builds on the previous.

• You must be able to use the ACCOUNTADMIN role.

  In this tutorial, you will perform all the steps using the ACCOUNTADMIN role. In general practice, however, you would use roles with privileges specifically defined for the action you’re performing. For example, you might have separate roles for developers who create UDFs and stored procedures, for database administrators who manage roles and permissions, and for administrators who manage listings using Snowflake Collaboration.

• To install your application from a private listing, you must have access to a second Snowflake account. You will use this account to mimic how consumers would install an application.

  Note

  Although the Native Apps Framework supports sharing applications with accounts in different organizations, for the purposes of this tutorial, both accounts must be in the same organization.

• You must set a current warehouse before performing the tutorial. A warehouse must be specified for a session and the warehouse must be running before queries and other DML statements can be executed in the session. Refer to USE WAREHOUSE for details.

Create the Setup Script¶

To create the setup script, do the following:

1. On your local file system create a folder named tutorial. This will be the root directory for the external files of your application.

   Note

   You will add additional files and subfolders to this folder in later sections.

2. In the tutorial folder create a subfolder named scripts.

3. In this folder create a new file named setup.sql.

   Note

   This tutorial will refer to this structure and filename for the setup script. However, when building your own application you can choose your own name and directory structure for this file.

4. Add the following SQL statement to this file:

   -- Setup script for the Hello Snowflake! application.
   

   Copy

You are adding this line as a placeholder because the setup script cannot be empty.

Create a README File for Your Application¶

A readme file provides a description of what your application does. You will see the readme when you view your application in Snowsight.

To create the readme.md file for your application:

1. In the tutorial folder, create a file named readme.md.

2. Add the following content to this file:

   This is the readme file for the Hello Snowflake Application!
   

   Copy

Create the Manifest File¶

The Native Apps Framework requires a manifest file for each application. The manifest file contains metadata and configuration parameters for an application.

To create the manifest file:

1. In the tutorial folder, create a file named manifest.yml.

   Note

   This file must be named manifest.yml and it must exist at the root level of your project. Paths to other files, including the setup script are relative to the location of this file.

2. Add the following content to this file:

   manifest_version: 1
   artifacts:
     setup_script: scripts/setup.sql
     readme: readme.md
   

   Copy

   The setup_script property specifies the location of the setup script relative to the location of the manifest file. The path and file name specified here must be the same as the location of the setup script you created above.

   Note

   The manifest_version, artifacts, and setup_script properties are required. The readme property is optional.

Review What You Learned in This Section¶

After performing the steps in this section, you should now have a directory structure that looks like the following:

/tutorial
  manifest.yml
  readme.md
  /scripts/
    setup.sql

In this section you learned how to create the setup script and manifest files that are required by the Native Apps Framework. Although the content you added to both of these files is basic, all applications must have these files.

You also added a readme file that is displayed when viewing your application in Snowsight or when publishing your application as a listing.

Create an Application Package¶

At its core, an application package is a Snowflake database that is extended to include additional information about an application. In that sense, it is a container for an application that includes:

• Shared data content

• Application files

To create an application package:

1. To grant the CREATE APPLICATION PACKAGE privilege to your role, run the following command:

   GRANT CREATE APPLICATION PACKAGE ON ACCOUNT TO ROLE accountadmin;
   

   Copy

   Note

   Although the ACCOUNTADMIN role has this privilege by default, to be able to create an application package, you must ensure that your role has been granted this privilege.

2. To create the application package, run the following command:

   CREATE APPLICATION PACKAGE hello_snowflake_package;
   

   Copy

   Note

   After running this command, the current context changes to HELLO_SNOWFLAKE_PACKAGE.

3. To verify the application package was successfully created, run the following command:

   SHOW APPLICATION PACKAGES;
   

   Copy

   You should see HELLO_SNOWFLAKE_PACKAGE in the name column of the output. Refer to SHOW APPLICATION PACKAGES for additional information on this command.

In this section you learned that an application package is a container for the resources used by an application. You also learned how to create an application package.

Create a Named Stage¶

In this section you will create a named stage to store files required by the Native Apps Framework. A named stage is also required to store any external code files you want to include in your application. Uploading these files to a named stage makes them available when creating your application.

To create a named stage, do the following:

1. To set the context to the application package you created in the previous section, run the following command:

   USE APPLICATION PACKAGE hello_snowflake_package;
   

   Copy

2. To create the required schema for the named stage, run the following command:

   CREATE SCHEMA stage_content;
   

   Copy

3. To create the named stage, run the following command.

   CREATE OR REPLACE STAGE hello_snowflake_package.stage_content.hello_snowflake_stage
     FILE_FORMAT = (TYPE = 'csv' FIELD_DELIMITER = '|' SKIP_HEADER = 1);
   

   Copy

   This command creates the named stage in the database and schema you created in the previous steps.

   Note

   You must include FILE_FORMAT = (TYPE = 'CSV' FIELD_DELIMITER = '|' SKIP_HEADER = 1); as part of this command. These are not optional.

You now have a named stage within the application package where you can upload the files you will use to build your application.

Add a Stored Procedure to the Setup Script¶

In the previous section, you created an application package. However, the application package doesn’t yet contain any data content or application files.

In this section, you will add a stored procedure to the application by adding the code for the stored procedure to the setup script on your local file system.

To add a stored procedure to the setup script:

1. Add the following SQL statements at the end of the setup.sql file you created in an earlier section:

   CREATE APPLICATION ROLE app_public;
   CREATE SCHEMA IF NOT EXISTS core;
   GRANT USAGE ON SCHEMA core TO APPLICATION ROLE app_public;
   

   Copy

   When the setup script is run during installation, these statements create an application role named app_public. Application roles are similar to database roles, but they can only be used within the context of an application. They are used to grant access to objects within the application.

   This example also creates a schema to contain the stored procedure and grants the USAGE privilege on the schema to the application role. Creating an application role and granting privileges on an object, for example a schema, to the application role is a common pattern within the setup script.

2. Add the code for the stored procedure at the end of the setup.sql file:

   CREATE OR REPLACE PROCEDURE CORE.HELLO()
     RETURNS STRING
     LANGUAGE SQL
     EXECUTE AS OWNER
     AS
     BEGIN
       RETURN 'Hello Snowflake!';
     END;
   

   Copy

   This example creates a stored procedure that outputs the string “Hello Snowflake!”.

3. Add the following the following statement to the end of the setup.sql file:

   GRANT USAGE ON PROCEDURE core.hello() TO APPLICATION ROLE app_public;
   

   Copy

   This example grants the USAGE privilege on the stored procedure to the application role.

4. Upload the revised setup script to the named stage.

   Refer to the previous section Upload the Application Files to the Named Stage to upload the revised setup script, then return here to continue the tutorial.

In this section you added a stored procedure to the setup script. You also created an application role and granted USAGE privileges to this role. This allows the setup script to create the stored procedure when the application is created. It also give the application permission to run the stored procedure.

Install the Application¶

In the previous section you modified the setup script to include a stored procedure.

In this section, you will install the application and run the stored procedure using Snowsight.

To install an application, do the following:

1. To create the application, run the following command:

   CREATE APPLICATION HELLO_SNOWFLAKE_APP
     FROM APPLICATION PACKAGE HELLO_SNOWFLAKE_PACKAGE
     USING '@hello_snowflake_package.stage_content.hello_snowflake_stage';
   

   Copy

2. To verify that the application was created successfully, run the following command:

   SHOW APPLICATIONS;
   

   Copy

   You should see the HELLO_SNOWFLAKE_APP listed under the name column of the output.

3. To run the HELLO stored procedure that you added to setup.sql in a previous section, run the following command:

   CALL core.hello();
   

   Copy

   You should see the following output after running this command:

   +------------------+
   | HELLO            |
   |------------------|
   | HELLO SNOWFLAKE! |
   +------------------+
   

   Copy

Review What You Learned in This Section¶

Congratulations! You have created, installed, and tested your first application using the Native Apps Framework! Although the application only has basic functionality, the components you used to build the application are the same for more complex applications.

In this section you completed the following:

• Added a stored procedure to the setup script. The setup script specifies how your application is installed in the consumer account. In later sections you will add data content and other types of application logic to your application.

• Installed and tested your application by running the stored procedure. In later sections you will learn about other ways to view and test your application.

Create a Database to Share with an Application¶

In this section you will learn how to share data content with an application. Specifically, you will share a table in the application package with.

To create a table and insert the sample data in the application package:

1. To set the context to the application package, run the following command:

   USE APPLICATION PACKAGE hello_snowflake_package;
   

   Copy

2. To create a table and add the shared data content, run the following commands:

   CREATE SCHEMA IF NOT EXISTS shared_data;
   CREATE TABLE IF NOT EXISTS accounts (ID INT, NAME VARCHAR, VALUE VARCHAR);
   INSERT INTO accounts VALUES
     (1, 'Nihar', 'Snowflake'),
     (2, 'Frank', 'Snowflake'),
     (3, 'Benoit', 'Snowflake'),
     (4, 'Steven', 'Acme');
   

   Copy

3. To verify that the data content was correctly inserted into the table, run the following command:

   SELECT * FROM accounts;
   

   Copy

   In the output of this command you should see three columns named ID, NAME, and VALUE. You should also see the data for each row as shown in the following output example:

   +----+----------+-----------+
   | ID | NAME     | VALUE     |
   |----+----------+-----------|
   |  1 | Nihar    | Snowflake |
   |  2 | Frank    | Snowflake |
   |  3 | Benoit   | Snowflake |
   |  4 | Steven   | Acme      |
   +----+----------+-----------+
   

   Copy

4. To grant usage on the ACCOUNTS table, run the following commands:

   GRANT USAGE ON SCHEMA shared_data TO SHARE IN APPLICATION PACKAGE hello_snowflake_package;
   GRANT SELECT ON TABLE accounts TO SHARE IN APPLICATION PACKAGE hello_snowflake_package;
   

   Copy

   Granting these privileges on the objects within the application package makes the accounts table available to all applications installed from the application package.

   Note

   You must grant the USAGE privilege on each object within an application package that you want to share with a consumer in an application.

Add a View to Access Data Content¶

In this section you will update the setup script to add a view that allows the application to access the data in the ACCOUNTS table you created in the previous subsection.

To add a view to access data content:

1. To create a schema for the view, add the following to the setup script:

   CREATE OR ALTER VERSIONED SCHEMA code_schema;
   GRANT USAGE ON SCHEMA code_schema TO APPLICATION ROLE app_public;
   

   Copy

   These statements create a versioned schema to contain the view and grant the USAGE privilege on the schema. The Native Apps Framework uses versioned schema to handle different versions of stored procedures and functions.

2. To create the view, add the following to the setup script:

   CREATE VIEW IF NOT EXISTS code_schema.accounts_view
     AS SELECT ID, NAME, VALUE
     FROM shared_data.accounts;
   GRANT SELECT ON VIEW code_schema.accounts_view TO APPLICATION ROLE app_public;
   

   Copy

   These statements create the view in the code_schema schema and grant the required privilege on the view to the application role.

3. Upload the revised setup script to the named stage.

   Refer to the previous section Upload the Application Files to the Named Stage to upload the files you updated in this section, then return here to continue the tutorial.

Test the Updated Application¶

In this subsection, you will reinstall the application and query the example table using the view within the installed application.

To test the updated application, do the following:

1. To delete the existing application, run the following command:

   DROP APPLICATION hello_snowflake_app;
   

   Copy

2. To create a new version of the application, run the following commands:

   CREATE APPLICATION hello_snowflake_app
     FROM APPLICATION PACKAGE hello_snowflake_package
     USING '@hello_snowflake_package.stage_content.hello_snowflake_stage';
   

   Copy

3. To verify that the view functions correctly, run the following command:

   SELECT * FROM code_schema.accounts_view;
   

   Copy

   The output of this command is identical to the SELECT command you ran when setting up the example data:

   +----+----------+-----------+
   | ID | NAME     | VALUE     |
   |----+----------+-----------|
   |  1 | Nihar    | Snowflake |
   |  2 | Frank    | Snowflake |
   |  3 | Benoit   | Snowflake |
   |  4 | Steven   | Acme      |
   +----+----------+-----------+
   

   Copy

Review What You Learned in This Section¶

In this section you learned how to include shared data content in your application by performing the following tasks:

• Create the ACCOUNTS table within the application package and inserted data into the table.

• Grant reference usage on the ACCOUNTS table to the application package.

• Create a schema and view that reference the ACCOUNTS table in the application package.

• Grant usage on the schema to the application role.

• Grant select on the view to the application role.

You also updated the setup script to perform the following when the application is installed:

• Create a schema and view that the application uses to access the example data.

• Grant usage on the schema to the application role.

• Grant select on the view to the application role.

Note that the commands you ran to set up the HELLO_SNOWFLAKE_DATA database have parallels in the setup script.

Add an Inline Python Function as a User-defined Function (UDF)¶

In this section you will add a Python function as a UDF.

To include a Python UDF in your application, add the following code to your setup file.

CREATE OR REPLACE FUNCTION code_schema.addone(i int)
RETURNS INT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.8'
HANDLER = 'addone_py'
AS
$$
def addone_py(i):
  return i+1
$$;

GRANT USAGE ON FUNCTION code_schema.addone(int) TO APPLICATION ROLE app_public;

Copy

These commands perform the following tasks when the application is installed:

• Create a versioned schema named code_schema.

• Grant the usage privilege on the schema to the APP_PUBLIC application role.

• Create the ADDONE() UDF in the code_schema schema.

• Grant the usage privilege on the function to the APP_PUBLIC application role.

Note that the schema created in the code sample above is a versioned schema. User-defined functions and stored procedures must be defined in a versioned schema instead of a normal schema.

Add an External Python Module¶

To add an external python module to your application:

1. Add the following Python function to your setup script:

   CREATE or REPLACE FUNCTION code_schema.multiply(num1 float, num2 float)
     RETURNS float
     LANGUAGE PYTHON
     RUNTIME_VERSION=3.8
     IMPORTS = ('/python/hello_python.py')
     HANDLER='hello_python.multiply';
   
   GRANT USAGE ON FUNCTION code_schema.multiply(FLOAT, FLOAT) TO APPLICATION ROLE app_public;
   

   Copy

   Similar to the previous example, these statement create a Python UDF in a schema and grant privileges on the function to the application role. However, this example contains an IMPORTS clause that refers to an external Python file that you will create.

2. In the tutorial folder create a subfolder named python.

3. In the python subfolder, create a file named hello_python.py.

4. Add the following to the hello_python.py file:

   def multiply(num1, num2):
     return num1*num2
   

   Copy

   The function defined in this external file matches the inline function defined in the setup script.

In this section, you added a Python UDF to your application. This UDF refers to an external Python module that can be referenced by your application package.

Install and Test the Updated Application¶

1. Upload the new and revised files to the named stage.

   Refer to the section Upload the Application Files to the Named Stage to upload the external Python file, then return here to continue the tutorial.

   If you are using SnowSQL to upload the file, run the following commands:

   PUT file:///<path_to_your_root_folder>/tutorial/scripts/setup.sql @hello_snowflake_package.stage_content.hello_snowflake_stage/scripts overwrite=true auto_compress=false;
   PUT file:///<path_to_your_root_folder>/tutorial/python/hello_python.py @hello_snowflake_package.stage_content.hello_snowflake_stage/python overwrite=true auto_compress=false;
   

   Copy

2. To remove the existing application, run the following command:

   DROP APPLICATION hello_snowflake_app;
   

   Copy

3. To create a new version of the application, run the following commands:

   CREATE APPLICATION hello_snowflake_app
     FROM APPLICATION PACKAGE hello_snowflake_package
     USING '@hello_snowflake_package.stage_content.hello_snowflake_stage';
   

   Copy

4. To test the Python stored procedure, run the following command:

   SELECT code_schema.addone(1);
   

   Copy

5. To test the referenced Python function, run the following command:

   SELECT code_schema.multiply(1,2);
   

   Copy

Review What You Learned in This Section¶

In this section, you added the following new functionality to your application:

• A Python function defined as an inline UDF.

• A Python function defined as a UDF that references external code.

You also tested each of these examples by installing an updated version of your application and running each of the functions.

Create the Streamlit App File¶

To create a Streamlit app, do the following:

1. In the tutorial folder, create a subfolder named streamlit.

2. In the streamlit folder, create a file named hello_snowflake.py.

3. Add the following code to this file:

   # Import python packages
   import streamlit as st
   from snowflake.snowpark.context import get_active_session
   
   # Write directly to the app
   st.title("Hello Snowflake - Streamlit Edition")
   st.write(
      """The following data is from the accounts table in the application package.
         However, the Streamlit app queries this data from a view called
         code_schema.accounts_view.
      """
   )
   
   # Get the current credentials
   session = get_active_session()
   
   #  Create an example data frame
   data_frame = session.sql("SELECT * FROM code_schema.accounts_view;")
   
   # Execute the query and convert it into a Pandas data frame
   queried_data = data_frame.to_pandas()
   
   # Display the Pandas data frame as a Streamlit data frame.
   st.dataframe(queried_data, use_container_width=True)
   

   Copy

Add the Streamlit Object to the Setup Script¶

To create the Streamlit object in the application, do the following:

1. Add the following statement at the end of the setup.sql file to create the Streamlit object:

   CREATE STREAMLIT code_schema.hello_snowflake_streamlit
     FROM '/streamlit'
     MAIN_FILE = '/hello_snowflake.py'
   ;
   

   Copy

   This statement creates a STREAMLIT object in the core schema.

2. Add the following statement at the end of the setup.sql file to allow the APP_PUBLIC role to access the Streamlit object:

   GRANT USAGE ON STREAMLIT code_schema.hello_snowflake_streamlit TO APPLICATION ROLE app_public;
   

   Copy

3. Upload the new and updated application files to the named stage:

   Refer to the previous section Upload the Application Files to the Named Stage to upload the Streamlit file you just created, then return here to continue the tutorial.

   If you are using SnowSQL to upload the file, run the following command:

   PUT file:///<path_to_your_root_folder>/tutorial/scripts/setup.sql @hello_snowflake_package.stage_content.hello_snowflake_stage/scripts overwrite=true auto_compress=false;
   PUT file:///<path_to_your_root_folder>/tutorial/streamlit/hello_snowflake.py @hello_snowflake_package.stage_content.hello_snowflake_stage/streamlit overwrite=true auto_compress=false;
   

   Copy

Install the Updated Application¶

1. Upload the revised setup script to the named stage.

   Refer to the previous section Upload the Application Files to the Named Stage to upload the revised setup script, then return here to continue the tutorial.

2. To remove the previous application, run the following command:

   DROP APPLICATION hello_snowflake_app;
   

   Copy

3. To create a new version of the application, run the following command:

   CREATE APPLICATION hello_snowflake_app
     FROM APPLICATION PACKAGE hello_snowflake_package
     USING '@hello_snowflake_package.stage_content.hello_snowflake_stage';
   

   Copy

Review What You Learned in This Section¶

In this section you added a Streamlit app to your application by doing the following:

• Create a Streamlit app.

Set the Default Release Directive¶

Before you can create a listing for your application application package, you must set a release directive. A release directive specifies which version of your application is available to a consumers.

In this tutorial you will set the default release directive using the version you added in a previous section.

To set the default release directive for your application package, do the following:

1. To view the versions and patches defined for your application package, run the following command:

   SHOW VERSIONS IN APPLICATION PACKAGE hello_snowflake_package;
   

   Copy

   This command displays the versions and patches defined for the application package.

2. To set the default release directive to version v1_0 and patch 0, run the following command:

   ALTER APPLICATION PACKAGE hello_snowflake_package
     SET DEFAULT RELEASE DIRECTIVE
     VERSION = v1_0
     PATCH = 0;
   

   Copy

   The output of this command is shown in the following example:

   +-----------------------------------------------------------+
   | status                                                    |
   |-----------------------------------------------------------|
   | Default release directive set to version 'V1_0', patch 2. |
   +-----------------------------------------------------------+
   

   Copy

In this section, you verified what versions and patches exist in your application package. Using this information, you defined the default release directive for the application package.

Create a Listing for Your Application¶

Now that you have specified a release directive for your application package, you will create a listing and add the application package as the data content of the listing. This lets you to share your application with other Snowflake users and allows them to install and use the application in their account.

To create a listing for your application:

1. Sign in to Snowsight.

2. In the left navigation bar, select Data » Provider Studio.

3. Select + Listing. The Create Listing window opens.

4. Enter a name for your listing.

5. In the Who can discover the listing section, select Only specified consumers to privately share the listing with specific accounts.

6. Click + Select to select the application package for the listing.

7. Enter a description for your listing.

8. In the Add consumer accounts section, add the account identifier for the account you are using to test the consumer experience of installing the application from a listing.

In this section you created a private listing containiing your application package as the shared data content.

Install the Application¶

In this section you will install the application associated with the listing you created in the previous section. You will install the listing in a different account which will mimic how a consumer would install the application in their account.

To install your application from the listing, do the following:

1. Sign in to Snowsight.

2. In the left navigation bar, select Apps.

3. Select the tile for the listing under Recently shared with you.

4. Select Get.

5. Enter a customer-facing name for the application. For this tutorial, use “Hello Snowflake App”.

6. Select the warehouse where you want to install the application.

7. Select Get.

8. Select Open to view your listing or Done to finish.

In this section you learned how to publish and install a listing that allows you to share your application with other Snowflake users.