Tutorial 1: Create a basic Snowflake Native App¶

What you learn in this tutorial¶

In this tutorial, you learn how to:

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

• Share data with an application package.

• Add business logic to an application package.

• Test the app locally.

• View and test the app in Snowsight.

• Publish your app by creating a private listing.

• Install the app from a private listing.

• Use Snowflake CLI to perform many of the steps above.

About providers and consumers¶

Within the context of the Snowflake Native App 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 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 also perform tasks that mimic the actions performed by consumers to install an app.

Prerequisites¶

• You must have Snowflake CLI version 3.0.0 or greater installed on your machine.

• 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 to perform the following tasks:

  • Create the role used in this tutorial, which is the tutorial1_role role.

  • Grant the required privileges to the tutorial1_role role.

  • Create a listing for your app.

  In this tutorial, you perform the steps to create your basic Snowflake Native App by using the tutorial1_role 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 the following users:

  • Developers who create UDFs and stored procedures

  • Database administrators who manage roles and permissions

  • Administrators who manage listings using Snowflake Collaboration

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

  Note

  Although the Snowflake Native App Framework supports sharing apps 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. See USE WAREHOUSE.

Initialize a new project folder¶

You use Snowflake CLI to initialize a new Snowflake Native App project in your local filesystem.

To do this:

1. Execute the following command:

   snow init --template app_basic tutorial
   

   Copy

2. Enter a value for the project identifier.

   This value is used as a base name for the entities that snow app commands will generate. For example, if you enter foo, the application package is foo_pkg and the application entity is foo. However, in this getting started tutorial, you will replace the contents of the project definition file (snowflake.yml), which overrides the value that you specify for the project identifier.

This command creates a folder named tutorial inside the current working directory and populates it with a basic Snowflake Native App project based on a basic template. This is the root directory for all of your application files.

Create the setup script¶

Modify or replace the contents of the app/setup_script.sql file as shown in the following example:

-- Setup script for the Hello Snowflake! app.

This line is a placeholder because the setup script cannot be empty.

Create a README file for your app¶

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

Modify or replace the contents of app/README.md with the following:

This is the readme file for the Hello Snowflake app.

Create the manifest file¶

The Snowflake Native App Framework requires a manifest file for each app. The manifest file contains metadata and configuration parameters for an app and influences the run-time behavior of your app.

Modify or replace the contents of the app/manifest.yml with the following:

manifest_version: 1
artifacts:
   setup_script: setup_script.sql
   readme: README.md

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 relative location of the setup script you modified above. The readme property follows the same rules.

Create the project definition file¶

Snowflake CLI uses a project definition file to describe objects that can be deployed to Snowflake. This file must be named snowflake.yml. This file controls the name of the deployed application package and object, as well as which files are uploaded to the project stage.

Modify or replace the contents of the snowflake.yml with the following:

definition_version: 2
entities:
   hello_snowflake_package:
      type: application package
      stage: stage_content.hello_snowflake_stage
      manifest: app/manifest.yml
      identifier: hello_snowflake_package
      artifacts:
         - src: app/*
           dest: ./
   hello_snowflake_app:
      type: application
      from:
         target: hello_snowflake_package
      debug: false

The next section of this tutorial describes how to use each of these properties.

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
  snowflake.yml
  README.md
  /app/
    manifest.yml
    README.md
    setup_script.sql

In this section you learned how to create the setup script and manifest files that are required by the Snowflake Native App Framework and the project definition file that is required by the Snowflake CLI.

Although the content you added to both the setup script and manifest file is basic, all apps must have these files.

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

Add a stored procedure to the setup script¶

In this section, you add a stored procedure to the app 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_script.sql file that you created in an earlier section of this tutorial:

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

   Copy

   When the setup script runs during app 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 app. They are used to grant access to objects within the application object that is created in the consumer account.

   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_script.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 statement to the end of the setup_script.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.

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

Install and test the app in stage development mode¶

You are now ready to create the application package, the app and all the other entities you specified in the project definition file.

To perform these tasks:

1. In a terminal, change to the tutorial folder.

2. Run the following Snowflake CLI command:

   snow app run -c tut1-connection
   

   Copy

This command performs the following tasks:

1. Create an application package name hello_snowflake_package with schema stage_content and stage hello_snowflake_stage.

2. Upload all required files to the named stage.

3. Create or upgrade the app hello_snowflake_app using files from this stage.

If the command runs successfully, it outputs a URL where you can see your app in Snowsight.

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

snow sql -q "call hello_snowflake_app.core.hello()" -c tut1-connection

You should see the following output after running this command:

+------------------+
| HELLO            |
|------------------|
| Hello Snowflake! |
+------------------+

Review what you learned in this section¶

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

In this section you completed the following:

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

• Deployed your app for the first time using Snowflake CLI.

• Tested your installed app by running a stored procedure.

In later sections you learn about other ways to view and test your app.

Create a table to share with an app¶

In this section you learn how to share data content with an app. Specifically, you share a table in the provider account by granting privileges on the schema and table to the application package.

1. To create a table and insert the sample data in the application package, create a folder tutorial/scripts, then a file shared_content.sql inside the folder. Add the following contents to this file:

   USE APPLICATION PACKAGE <% ctx.entities.hello_snowflake_package.identifier %>;
   
   CREATE SCHEMA IF NOT EXISTS shared_data;
   USE SCHEMA shared_data;
   CREATE TABLE IF NOT EXISTS accounts (ID INT, NAME VARCHAR, VALUE VARCHAR);
   TRUNCATE TABLE accounts;
   INSERT INTO accounts VALUES
     (1, 'Joe', 'Snowflake'),
     (2, 'Nima', 'Snowflake'),
     (3, 'Sally', 'Snowflake'),
     (4, 'Juan', 'Acme');
   -- grant usage on the ``ACCOUNTS`` table
   GRANT USAGE ON SCHEMA shared_data TO SHARE IN APPLICATION PACKAGE <% ctx.entities.hello_snowflake_package.identifier %>;
   GRANT SELECT ON TABLE accounts TO SHARE IN APPLICATION PACKAGE <% ctx.entities.hello_snowflake_package.identifier %>;
   

   Copy

   In this example, <% ctx.entities.hello_snowflake_package.identifier %> is a template that is replaced by the resolved identifier of your application package from the snowflake.yml file when you execute a Snowflake CLI command.

   Granting these privileges on the objects within the application package makes the shared_data.accounts table available to all objects created from this application package. This sharing takes place due to the privileges GRANT TO SHARE command at the end of the script.

   Note

   You must grant the USAGE privilege on each schema to an application package for each schema you want to share with a consumer in an app. You must then grant the SELECT privilege on the objects within the schema that you want to share.

2. Add an entry to the project definition file to ensure that this script runs when you update your application package. The final project definition file (snowflake.yml) should be:

   definition_version: 2
   entities:
      hello_snowflake_package:
         type: application package
         stage: stage_content.hello_snowflake_stage
         manifest: app/manifest.yml
         identifier: hello_snowflake_package
         artifacts:
            - src: app/*
              dest: ./
         meta:
            post_deploy:
               - sql_script: app/scripts/shared_content.sql
      hello_snowflake_app:
         type: application
         from:
            target: hello_snowflake_package
         debug: false
   

   Copy

Add a view to access data content¶

In this section you update the setup script to add a view that allows the consumer who installed the app to access the data in the ACCOUNTS table that you created in the previous section.

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 Snowflake Native App 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.

   This updated setup script is also uploaded to the stage the next time you deploy your app using Snowflake CLI.

Test the updated app¶

In this subsection, you upgrade the app and query the example table using the view within the installed app.

To test the updated app, follow these steps:

1. To update the application package and the application object installed in the consumer account, run the following command:

   snow app run -c tut1-connection
   

   Copy

   This uploads all the edited files to the stage, runs the scripts/shared_content.sql script, and upgrade the app using those files on the stage.

2. To verify that the view works correctly, run the following command:

   snow sql -q "SELECT * FROM hello_snowflake_app.code_schema.accounts_view" -c tut1-connection
   

   Copy

   The output of this command should be:

   +----+----------+-----------+
   | ID | NAME     | VALUE     |
   |----+----------+-----------|
   |  1 | Joe      | Snowflake |
   |  2 | Nima     | Snowflake |
   |  3 | Sally    | Snowflake |
   |  4 | Juan     | Acme      |
   +----+----------+-----------+
   

Review what you learned in this section¶

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

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

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

• Created a schema and view that references the ACCOUNTS table in the application package.

• Granted usage on the schema to the application role.

• Granted select on the view to the application role.

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

• Created a schema and view that the app uses to access the example data.

• Granted usage on the schema to the application role.

• Granted select on the view to the application role.

Add an inline python function as a user-defined function (UDF)¶

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

To include a Python UDF in your app, add the following code to your setup script (setup_script.sql).

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

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

These commands perform the following tasks when the app 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 example above is a versioned schema. User-defined functions and stored procedures must be defined in a versioned schema instead of a normal schema. This prevents app upgrades from interfering with concurrent code execution.

Add an external python module¶

To add an external python module to your app:

1. Add the following Python function to your setup script (setup_script.sql):

   CREATE or REPLACE FUNCTION code_schema.multiply(num1 float, num2 float)
     RETURNS float
     LANGUAGE PYTHON
     RUNTIME_VERSION = 3.9
     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 grants privileges on the function to the application role. However, this example contains an IMPORTS clause that refers to an external Python file that you create and include in your named stage.

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.

5. Add the following to the existing artifacts section of the project definition file (snowflake.yml):

   - python/hello_python.py
   

   Copy

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

Install and test the updated app¶

To install and test an app:

1. To update the application package and the app, run the following command:

   snow app run -c tut1-connection
   

   Copy

   This command uploads the edited and new files to the stage and upgrades your app using the files on the stage.

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

   snow sql -q "SELECT hello_snowflake_app.code_schema.addone(1)" -c tut1-connection
   

   Copy

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

   snow sql -q "SELECT hello_snowflake_app.code_schema.multiply(1,2)" -c tut1-connection
   

   Copy

Review what you learned in this section¶

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

• 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 app and running each of the functions.

Create the streamlit app file¶

To create a Streamlit app, follow these steps:

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 import 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 = Session.builder.getOrCreate()
   
   #  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

4. Add the following to the existing artifacts section of the project definition file (snowflake.yml):

   - streamlit/hello_snowflake.py
   

   Copy

Add the streamlit object to the setup script¶

To create the Streamlit object in the app, follow these steps:

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

   CREATE STREAMLIT IF NOT EXISTS 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_script.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

Install the updated app¶

1. To update the application package and the app, run the following command:

   snow app run -c tut1-connection
   

   Copy

   This command uploads the edited and new files to the stage and upgrades your app using those files on the stage. You can then navigate to the URL this command prints out to see your new Streamlit in action; once you are there, click on the tab named HELLO_SNOWFLAKE_STREAMLIT that appears beside the name of your application.

Review what you learned in this section¶

In this section you added a Streamlit app to your Snowflake Native App by doing the following:

• Created a python file that uses the Streamlit library to render a user interface.

• Created a Streamlit app in your Snowflake Native App that displays shared data.

Set the default release directive¶

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

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

To set the default release directive on the application package, follow these steps:

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

   snow app version list -c tut1-connection
   

   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:

   snow sql -q "ALTER APPLICATION PACKAGE hello_snowflake_package SET DEFAULT RELEASE DIRECTIVE VERSION = v1_0 PATCH = 0" -c tut1-connection
   

   Copy

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

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

   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 create a listing and add the application package as the data content of the listing. This allows you to share your app with other Snowflake users and allows them to install and use the app in their account.

To create a listing for your app:

1. Sign in to Snowsight.

2. In the navigation menu, select Data Products » Provider Studio.

3. Select + Create Listing and then Specified consumers to privately share the listing with specific accounts.

4. From the Select role dropdown, select ACCOUNTADMIN.

5. Enter a name for your listing.

6. Select Next.

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

8. Enter a description for your listing.

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

10. Select Publish.

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

Install the app in a consumer account¶

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

To install your app from the listing, follow these steps:

1. Sign in to Snowsight.

2. In the navigation menu, select Data Products » Apps.

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

4. Select Get.

5. Select Options and enter a customer-facing name for the app. For this tutorial, use “HelloSnowflakeApp”.

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

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 app with other Snowflake users.