Additional Streamlit in Snowflake features¶

Example: Access the OpenAI API¶

The following example shows how to create an external access integration for an outbound request to the OpenAI API. This example includes setting up the security integration and granting the required privileges.

1. To create a network rule that represents the external network’s location and restrictions for access, use the CREATE NETWORK RULE command:

   CREATE OR REPLACE NETWORK RULE network_rules
     MODE = EGRESS
     TYPE = HOST_PORT
     VALUE_LIST = ('api.openai.com');
   

   Copy

   For more information, see Creating a network rule to represent the external network location.

2. To create a secret that represents credentials required to authenticate with the external network location, use the CREATE SECRET command:

   CREATE OR REPLACE SECRET openai_key
     TYPE = GENERIC_STRING
     SECRET_STRING = '<any_string>';
   

   Copy

   For more information, see Creating a secret to represent credentials.

3. To create an external access integration, run the CREATE EXTERNAL ACCESS INTEGRATION command, setting ALLOWED_NETWORK_RULES to the network rule you created and ALLOWED_AUTHENTICATION_SECRETS to the secret you created:

   CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION openai_access_int
     ALLOWED_NETWORK_RULES = (network_rules)
     ALLOWED_AUTHENTICATION_SECRETS = (openai_key)
     ENABLED = TRUE;
   

   Copy

4. To grant the required privileges to use the SECRET and INTEGRATION objects for external access to the Streamlit app creator, use the GRANT <privileges> command:

   GRANT READ ON SECRET openai_key TO ROLE streamlit_app_creator_role;
   GRANT USAGE ON INTEGRATION openai_access_int TO ROLE streamlit_app_creator_role;
   

   Copy

5. To enable the Streamlit app to use the integration, run the ALTER STREAMLIT command, setting the EXTERNAL_ACCESS_INTEGRATIONS property to the integration:

   USE ROLE streamlit_app_creator_role;
   
   ALTER STREAMLIT streamlit_db.streamlit_schema.streamlit_app
     SET EXTERNAL_ACCESS_INTEGRATIONS = (openai_access_int)
     SECRETS = ('my_openai_key' = streamlit_db.streamlit_schema.openai_key);
   

   Copy

   Note

   You can also set up a new Streamlit object to use an external access integration by specifying the EXTERNAL_ACCESS_INTEGRATIONS parameter when you run the CREATE STREAMLIT command:

   CREATE STREAMLIT streamlit_db.streamlit_schema.streamlit_app
     ROOT_LOCATION = '<stage_path_and_root_directory>'
     MAIN_FILE = '<path_to_main_file_in_root_directory>'
     EXTERNAL_ACCESS_INTEGRATIONS = (openai_access_int)
     SECRETS = ('my_openai_key' = streamlit_db.streamlit_schema.openai_key);
   

   Copy

6. In your Streamlit app code, call the external API:

   from openai import OpenAI
   import streamlit as st
   import _snowflake
   
   st.title(":speech_balloon: Simple chat app using an external LLM")
   st.write("This app shows how to call an external LLM to build a simple chat application.")
   
   # Use the _snowflake library to access secrets
   secret = _snowflake.get_generic_secret_string('my_openai_key')
   client = OpenAI(api_key=secret)
   
   # ...
   # code to use API
   # ...
   

   Copy

Example: Access data in a table with row access policy using CURRENT_USER¶

You can use a Streamlit in Snowflake app to govern access to rows in a table protected by a row access policy. Specify the CURRENT_USER function in the body of the row access policy and add the row access policy to the table.

The following example demonstrates how to govern access to a table that is protected by a row access policy in a Streamlit in Snowflake app.

1. Create a table and insert data:

   CREATE TABLE row_access_policy_test_table (
       id INT,
       some_data VARCHAR(100),
       the_owner VARCHAR(50)
   );
   
   INSERT INTO row_access_policy_test_table (id, some_data, the_owner)
   VALUES
       (4, 'Some information 4', 'ALICE'),
       (5, 'Some information 5', 'FRANK'),
       (6, 'Some information 6', 'ALICE');
   

   Copy

2. Create a row access policy:

   CREATE OR REPLACE ROW ACCESS POLICY st_schema.row_access_policy
   AS (the_owner VARCHAR) RETURNS BOOLEAN ->
       the_owner = CURRENT_USER();
   

   Copy

3. Add the row access policy to the table:

   ALTER TABLE row_access_policy_test_table ADD ROW ACCESS POLICY st_schema.row_access_policy ON (the_owner);
   

   Copy

4. Create a Streamlit app.

5. Grant the global READ SESSION privilege to the Streamlit app owner role:

   GRANT READ SESSION ON ACCOUNT TO ROLE streamlit_owner_role;
   

   Copy

6. Add the following code to your Streamlit app:

   # Import Python packages
   import streamlit as st
   from snowflake.snowpark.context import get_active_session
   
   st.title("CURRENT_USER() + Row Access Policy in SiS Demo :balloon:")
   st.write(
           """You can access `CURRENT_USER()` and data from tables with row access policies
           in Streamlit in Snowflake apps
           """)
   
   # Get the current credentials
   session = get_active_session()
   
   st.header('Demo')
   
   st.subheader('Credentials')
   sql = "SELECT CURRENT_USER();"
   df = session.sql(sql).collect()
   st.write(df)
   
   st.subheader('Row Access on a Table')
   sql = "SELECT * FROM st_db.st_schema.row_access_policy_test_table;"
   df = session.sql(sql).collect()
   
   st.write(df)
   

   Copy