Configure Observe for Snowflake Application

Configure Observe for Snowflake App - Via UI

Snowflake for Observe native application needs some configuration before being able to send your Snowflake data to Observe.

  • Log into your Snowflake instance in Snowsight console

  • Change to the ACCOUNTADMIN role

  • Navigate to Data Products\Apps

  • Click Observe for Snowflake app

  • Click Settings gear icon in top right

  • If prompted, turn on All events to be shared with application provider and click Save button

Observe For Snowflake - Configure Event Sharing

  • If prompted, grant requested IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE, EXECUTE TASK and EXECUTE MANAGED TASK privileges by clicking Grant Privileges button

Observe For Snowflake - Configure Requested Permissions

  • Click Privileges tab

  • Under Object access privileges, click Add button

  • Select virtual warehouse you’ve created before (O4S_WH)

  • Click Save

Observe For Snowflake - Configure Warehouse Reference

  • The final configuration on Privileges tab should look like that:

Observe For Snowflake - Configuration Summary - Privileges

  • The final configuration on App Events tab should look like that:

Observe For Snowflake - Configuration Summary - Event Sharing

  • Click Setup Instructions on left navigation bar

  • Follow instructions, which do require running some code in worksheet, which we document here

  • In another browser tab, log into your Snowflake instance in Snowsight console

  • Navigate to Project\Worksheets

  • Create new Worksheet and name is o4s UI install

  • Run step “2.1 Assume SYSADMIN Role for Object Creation”

USE ROLE SYSADMIN;

  • Run step “2.2 Create Database to Hold Secrets”, assuming database name to be SEND_TO_OBSERVE

CREATE DATABASE IF NOT EXISTS SEND_TO_OBSERVE;

  • Run “2.3 Create Schema to Hold Secrets”, assuming schema to be O4S

CREATE OR REPLACE SCHEMA SEND_TO_OBSERVE.O4S;

  • Run “2.4 Create Secrets”, putting in the value of the ingest token created in Observe earlier

CREATE OR REPLACE SECRET
    <secret_db>.<secret_schema>.OBSERVE_TOKEN
    TYPE = GENERIC_STRING
    SECRET_STRING = 'ds<trimmed>:w7<trimmed>Hc';

  • Run “2.4 Create Secrets”, putting in the correct ingest endpoint for your account, typically <youraccount######>.collect.observeinc.com

CREATE OR REPLACE SECRET
    SEND_TO_OBSERVE.O4S.OBSERVE_ENDPOINT
    TYPE = GENERIC_STRING
    SECRET_STRING = '<youraccount######>.collect.observeinc.com';

  • Run “2.5 Create Network Rule” with the same ingest endpoint value from previous step

CREATE OR REPLACE NETWORK RULE
    SEND_TO_OBSERVE.O4S.OBSERVE_INGEST_NETWORK_RULE
    MODE = EGRESS
    TYPE = HOST_PORT
    VALUE_LIST = ('<youraccount######>.collect.observeinc.com');

  • Run “2.6 Create External Integration” to bind network rules and secrets to external access integration

USE ROLE ACCOUNTADMIN;

CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION
OBSERVE_INGEST_ACCESS_INTEGRATION
ALLOWED_NETWORK_RULES = (SEND_TO_OBSERVE.O4S.OBSERVE_INGEST_NETWORK_RULE)
ALLOWED_AUTHENTICATION_SECRETS = (SEND_TO_OBSERVE.O4S.OBSERVE_TOKEN, SEND_TO_OBSERVE.O4S.OBSERVE_ENDPOINT)
ENABLED = TRUE;

  • Run “2.7 Grant Access to Secrets and External Integration” to allow Observe for Snowflake application access to the secrets and to external access integration

USE ROLE SECURITYADMIN;

GRANT USAGE ON DATABASE SEND_TO_OBSERVE TO APPLICATION OBSERVE_FOR_SNOWFLAKE;
GRANT USAGE ON SCHEMA SEND_TO_OBSERVE.O4S TO APPLICATION OBSERVE_FOR_SNOWFLAKE;
GRANT READ ON SECRET SEND_TO_OBSERVE.O4S.OBSERVE_TOKEN TO APPLICATION OBSERVE_FOR_SNOWFLAKE;
GRANT READ ON SECRET SEND_TO_OBSERVE.O4S.OBSERVE_ENDPOINT TO APPLICATION OBSERVE_FOR_SNOWFLAKE;
GRANT USAGE ON INTEGRATION OBSERVE_INGEST_ACCESS_INTEGRATION TO APPLICATION OBSERVE_FOR_SNOWFLAKE;

  • Run “3.1 Access to Default Event table” to allow access to default event table

USE ROLE SECURITYADMIN;

GRANT APPLICATION ROLE SNOWFLAKE.EVENTS_ADMIN TO APPLICATION OBSERVE_FOR_SNOWFLAKE;

  • Run “3.2 Access to Custom Event table” if you have a custom event table, replacing <event_table_db> , <event_table_schema> , <event_table_name> with correct names

USE ROLE SECURITYADMIN;

GRANT USAGE ON DATABASE <event_table_db> TO APPLICATION OBSERVE_FOR_SNOWFLAKE;
GRANT USAGE ON SCHEMA <event_table_db>.<event_table_schema> TO APPLICATION OBSERVE_FOR_SNOWFLAKE;
GRANT SELECT ON TABLE <event_table_db>.<event_table_schema>.<event_table_name> TO APPLICATION OBSERVE_FOR_SNOWFLAKE;

  • Run “4. Modify the Custom Event Table to Enable CHANGE_TRACKING” if you have custom event table

USE ROLE SYSADMIN;

ALTER TABLE <event_table_db>.<event_table_schema>.<event_table_name> SET CHANGE_TRACKING = TRUE;

  • Click app tab on the navigation to switch back to main screen

  • in Configure Observe Connection on the left, specify the previously created objects:

    • OBSERVE_INGEST_ACCESS_INTEGRATION for EXTERNAL ACCESS INTEGRATION

    • SEND_TO_OBSERVE.O4S.OBSERVE_TOKEN for OBSERVE TOKEN SECRET

    • SEND_TO_OBSERVE.O4S.OBSERVE_ENDPOINT for OBSERVE ENDPOINT SECRET

  • Click Configure

Observe For Snowflake - Supply EAI and Secrets

  • Results of successful configuration should look like that

Observe For Snowflake - Configuration Summary - App Ready

Configure Observe for Snowflake App - Developer Tools

O4S provides a Developer Tools area that you can reach via the left-nav. Developer tools provides a UI to interact with the data collection tasks that O4S runs in your Snowflake account. These are advanced configuration options that you can use to fine tune and troubleshoot the behavior of O4S.

Consult with Observe support before using these tools.

TASK Details

This provides an overview of the various data collection task names, schedules and states. The NAME column can be used to target changes to task properties.

Alter TASK Properties

This provides a mechanism to change the configuration for task suspensions, based on error responses from the Observe ingest endpoint (default is 0, which will be infinite retries). It also allows you to set the timeout for task execution (default is 24 hours or 86400000 milliseconds).

Clear STREAM

Clearing the stream is only intended in emergency cases, and will cause all current data to be clears and unrecoverable. Consult with Observe support if you believe you need to clear the O4S stream.

Configure Observe for Snowflake App - Via Console

  • Log into your Snowflake instance in Snowsight console

  • Navigate to Project\Worksheets

  • Use previously created o4s scripted install worksheet

  • Run:

    • Replace <secret_db> with your preferred database name. We recommend SEND_TO_OBSERVE

    • Replace <secret_schema> with your preferred database name. We recommend O4S

    • Replace <token_string> with the value of the ingest token created in Observe earlier

    • Replace <endpoint_string> with the correct ingest endpoint for your account, typically <youraccount######>.collect.observeinc.com

    • If you have a custom event table, replace <event_table_db> , <event_table_schema> , <event_table_name> with correct names and uncomment relevant sections to grant permission and turn on CDC stream support on it

    • If you have a custom table or a view, replace <table_or_view_db>, <table_or_view_schema>, <table_or_view_name> with correct names and uncomment relevant sections to grant permission and turn on CDC stream support on it

--------------------------------------------------------------------------------
-- [ Streamlit UI ]
-- Step 2: Accept event sharing to provider
-- UI in Streamlit app does this if you accept the popup
--------------------------------------------------------------------------------
USE ROLE ACCOUNTADMIN;
ALTER APPLICATION OBSERVE_FOR_SNOWFLAKE SET AUTHORIZE_TELEMETRY_EVENT_SHARING=true;
ALTER APPLICATION OBSERVE_FOR_SNOWFLAKE SET SHARED TELEMETRY EVENTS ('SNOWFLAKE$ALL');

--------------------------------------------------------------------------------
-- [ Streamlit UI ]
-- Step 3: Grant the app access to ACCOUNT_USAGE views and TASK execution
-- UI in Streamlit app does this if you accept the popup
--------------------------------------------------------------------------------
USE ROLE ACCOUNTADMIN;
GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO APPLICATION OBSERVE_FOR_SNOWFLAKE;
GRANT EXECUTE TASK ON ACCOUNT TO APPLICATION OBSERVE_FOR_SNOWFLAKE;
GRANT EXECUTE MANAGED TASK ON ACCOUNT TO APPLICATION OBSERVE_FOR_SNOWFLAKE;

--------------------------------------------------------------------------------
-- [ manual ]
-- Step 4: Create secrets, network rule, and external access integration
-- variables to replace below: <secret_db>, <secret_schema>, <token_string>, <endpoint_string>
--------------------------------------------------------------------------------
-- Assume a regular SYSADMIN role for object creation
USE ROLE SYSADMIN;

-- Create database to hold secrets. For example, SEND_TO_OBSERVE
CREATE DATABASE IF NOT EXISTS <secret_db>;

-- Create Schema to hold secrets. for example, SEND_TO_OBSERVE.O4S
CREATE OR REPLACE SCHEMA <secret_db>.<secret_schema>;

-- <token_string> looks like ds1u2GzisdVhTjFNulN7:<trimmed>...
CREATE OR REPLACE SECRET
    <secret_db>.<secret_schema>.OBSERVE_TOKEN
    TYPE = GENERIC_STRING
    SECRET_STRING = '<token_string>';

-- <endpoint_string> looks like 152194748953.collect.observe-eng.com or 141741533462.collect.observeinc.com in production
CREATE OR REPLACE SECRET
    <secret_db>.<secret_schema>.OBSERVE_ENDPOINT
    TYPE = GENERIC_STRING
    SECRET_STRING = '<endpoint_string>';

CREATE OR REPLACE NETWORK RULE
  <secret_db>.<secret_schema>.OBSERVE_INGEST_NETWORK_RULE
  MODE = EGRESS
  TYPE = HOST_PORT
  VALUE_LIST = ('<endpoint_string>');

-- Assume a high privileges ACCOUNTADMIN role for external integration creation because that's the only way it could happen
USE ROLE ACCOUNTADMIN;

-- This allows Snowflake to talk to the Observe via network rule and use the secrets
CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION
  OBSERVE_INGEST_ACCESS_INTEGRATION
  ALLOWED_NETWORK_RULES = (<secret_db>.<secret_schema>.OBSERVE_INGEST_NETWORK_RULE)
  ALLOWED_AUTHENTICATION_SECRETS = (<secret_db>.<secret_schema>.OBSERVE_TOKEN, <secret_db>.<secret_schema>.OBSERVE_ENDPOINT)
  ENABLED = TRUE;

-- Assume a SECURITYADMIN role for grants per best practices
USE ROLE SECURITYADMIN;

-- Grants access to the database holding secrets and to the external access integration
GRANT USAGE ON DATABASE <secret_db> TO APPLICATION OBSERVE_FOR_SNOWFLAKE;
GRANT USAGE ON SCHEMA <secret_db>.<secret_schema> TO APPLICATION OBSERVE_FOR_SNOWFLAKE;
GRANT READ ON SECRET <secret_db>.<secret_schema>.OBSERVE_TOKEN TO APPLICATION OBSERVE_FOR_SNOWFLAKE;
GRANT READ ON SECRET <secret_db>.<secret_schema>.OBSERVE_ENDPOINT TO APPLICATION OBSERVE_FOR_SNOWFLAKE;
GRANT USAGE ON INTEGRATION OBSERVE_INGEST_ACCESS_INTEGRATION TO APPLICATION OBSERVE_FOR_SNOWFLAKE;

--------------------------------------------------------------------------------
-- [ manual ]
-- Step 5: Grant the app access to specific event table.
-- Note that Snowflake now provides a Default Event Table (SNOWFLAKE.TELEMETRY.EVENTS), which you can read more about here:
-- https://docs.snowflake.com/en/developer-guide/logging-tracing/event-table-setting-up#default-event-table
-- You can use this if you do not already have a custom Event Table configured.
-- variables to replace below: <event_table_db>, <event_table_schema>, <event_table_name>
--------------------------------------------------------------------------------
USE ROLE SECURITYADMIN;

-- Grant permissions to read default event table SNOWFLAKE.TELEMETRY.EVENTS
GRANT APPLICATION ROLE SNOWFLAKE.EVENTS_ADMIN TO APPLICATION OBSERVE_FOR_SNOWFLAKE;

-- Grant permissions to read non-default event table of your choice
-- GRANT USAGE ON DATABASE <event_table_db> TO APPLICATION OBSERVE_FOR_SNOWFLAKE;
-- GRANT USAGE ON SCHEMA <event_table_db>.<event_table_schema> TO APPLICATION OBSERVE_FOR_SNOWFLAKE;
-- GRANT SELECT ON TABLE <event_table_db>.<event_table_schema>.<event_table_name> TO APPLICATION OBSERVE_FOR_SNOWFLAKE;

USE ROLE SYSADMIN;

-- Change your custom event table to support change tracking so we can create a CDC stream on it
-- ALTER TABLE <event_table_db>.<event_table_schema>.<event_table_name> SET CHANGE_TRACKING=TRUE;

--------------------------------------------------------------------------------
-- [ manual ]
-- Step 6: Grant the app access to any custom table and event.
-- -- variables to replace below: <table_or_view_db>, <table_or_view_schema>, <table_or_view_name>
--------------------------------------------------------------------------------
USE ROLE SECURITYADMIN;

-- Grant permissions to read and use the custom table or view
-- GRANT USAGE ON DATABASE <table_or_view_db> TO APPLICATION OBSERVE_FOR_SNOWFLAKE;
-- GRANT USAGE ON SCHEMA <table_or_view_db>.<table_or_view_schema> TO APPLICATION OBSERVE_FOR_SNOWFLAKE;
-- Choose the right statement based on the object type
-- GRANT SELECT ON TABLE <table_or_view_db>.<table_or_view_schema>.<table_or_view_name> TO APPLICATION OBSERVE_FOR_SNOWFLAKE;
-- GRANT SELECT ON VIEW <table_or_view_db>.<table_or_view_schema>.<table_or_view_name> TO APPLICATION OBSERVE_FOR_SNOWFLAKE;

USE ROLE SYSADMIN;

-- Change your custom table or view to support change tracking so we can create a CDC stream on it
-- Choose the right statement based on the object type
-- ALTER TABLE <table_or_view_db>.<table_or_view_schema>.<table_or_view_name> SET CHANGE_TRACKING = TRUE;
-- ALTER VIEW <table_or_view_db>.<table_or_view_schema>.<table_or_view_name> SET CHANGE_TRACKING = TRUE;

--------------------------------------------------------------------------------
-- [ Streamlit UI ]
-- Step 7: Grant access to a virtual warehouse in the account.
-- variables to replace below: <warehouse_name>
-- UI in Streamlit app does this if you accept a popup
--------------------------------------------------------------------------------
USE ROLE ACCOUNTADMIN;

USE APPLICATION OBSERVE_FOR_SNOWFLAKE;
CALL OBSERVE_FOR_SNOWFLAKE.CONFIG.REGISTER_SINGLE_REFERENCE(
  'warehouse' , 'ADD', SYSTEM$REFERENCE('WAREHOUSE', '<warehouse_name>', 'PERSISTENT', 'USAGE'));

--------------------------------------------------------------------------------
-- [ Streamlit UI ]
-- Step 8: Configure the connection to send to observe via external access integration
-- variables to replace below: <secret_db>, <secret_schema>, <token_string>, <endpoint_string>
--------------------------------------------------------------------------------
USE ROLE ACCOUNTADMIN;

USE APPLICATION OBSERVE_FOR_SNOWFLAKE;
CALL OBSERVE_FOR_SNOWFLAKE.PUBLIC.PROVISION_CONNECTOR(
  PARSE_JSON('{"observe_token": "<secret_db>.<secret_schema>.OBSERVE_TOKEN",
             "observe_endpoint": "<secret_db>.<secret_schema>.OBSERVE_ENDPOINT",
             "external_access_integration": "OBSERVE_INGEST_ACCESS_INTEGRATION"}')
);