Snowflake
Integrate Teleskope with Snowflake
Requirements
In order to integrate Teleskope with Snowflake, you must make sure the following prerequisites are met:
Within Snowflake: You must have a Snowflake user with the ACCOUNTADMIN role
Within Teleskope: A Teleskope admin account to complete the setup process.
Integration
Create a Teleskope Snowflake Role
-- Create role & user under ACCOUNTADMIN
USE ROLE ACCOUNTADMIN;
CREATE ROLE IF NOT EXISTS TELESKOPE;
CREATE USER IF NOT EXISTS TELESKOPE_USER
PASSWORD = '<PASSWORD>' -- replace with a secure password
DEFAULT_WAREHOUSE = '<WAREHOUSE>' -- replace with your warehouse
DEFAULT_ROLE = TELESKOPE
COMMENT = 'Teleskope integration user';
GRANT ROLE TELESKOPE TO USER TELESKOPE_USER;
Run the Below Script to:
Grant Read Access to the Teleskope Role for each database
Grant Access History Access (optional)
-- 1. Grant SELECT on all current + future objects
DECLARE
-- Cursor selecting all non-system databases
c1 CURSOR FOR
SELECT database_name
FROM information_schema.databases
WHERE database_name NOT IN (
'exampleDB1',
'exampleDB2'
);
warehouse STRING := 'WAREHOUSE'; -- same warehouse as above
role_name STRING := 'TELESKOPE';
sql_cmd STRING;
BEGIN
-- Loop through each database and grant privileges
FOR rec IN c1 DO
-- Warehouse usage
sql_cmd :=
'GRANT USAGE ON WAREHOUSE "' || warehouse || '" TO ROLE "' || role_name || '"';
EXECUTE IMMEDIATE sql_cmd;
-- Database usage
sql_cmd :=
'GRANT USAGE ON DATABASE "' || rec.database_name || '" TO ROLE "' || role_name || '"';
EXECUTE IMMEDIATE sql_cmd;
-- Schemas
sql_cmd :=
'GRANT USAGE ON ALL SCHEMAS IN DATABASE "' || rec.database_name || '" TO ROLE "' || role_name || '"';
EXECUTE IMMEDIATE sql_cmd;
sql_cmd :=
'GRANT USAGE ON FUTURE SCHEMAS IN DATABASE "' || rec.database_name || '" TO ROLE "' || role_name || '"';
EXECUTE IMMEDIATE sql_cmd;
-- Tables
sql_cmd :=
'GRANT SELECT ON ALL TABLES IN DATABASE "' || rec.database_name || '" TO ROLE "' || role_name || '"';
EXECUTE IMMEDIATE sql_cmd;
sql_cmd :=
'GRANT SELECT ON FUTURE TABLES IN DATABASE "' || rec.database_name || '" TO ROLE "' || role_name || '"';
EXECUTE IMMEDIATE sql_cmd;
-- Views
sql_cmd :=
'GRANT SELECT ON ALL VIEWS IN DATABASE "' || rec.database_name || '" TO ROLE "' || role_name || '"';
EXECUTE IMMEDIATE sql_cmd;
sql_cmd :=
'GRANT SELECT ON FUTURE VIEWS IN DATABASE "' || rec.database_name || '" TO ROLE "' || role_name || '"';
EXECUTE IMMEDIATE sql_cmd;
-- 2. Optional: account-wide monitoring
sql_cmd :=
'GRANT MONITOR USAGE ON ACCOUNT TO ROLE "' || role_name || '"';
EXECUTE IMMEDIATE sql_cmd;
END FOR;
END;
SHOW GRANTS TO USER TELESKOPE_USER;
SHOW GRANTS TO ROLE TELESKOPE;
Grant write access to the Teleskope Role (optional)
grant delete on future tables in database identifier($database) to role identifier($role);
grant delete on all tables in database identifier($database) to role identifier($role);
grant delete on future views in database identifier($database) to role identifier($role);
grant delete on all views in database identifier($database) to role identifier($role);
Configure Key Pair Authentication (optional)
If you would like to setup key pair authentication instead of using a password, follow Snowflake's documentation
Enrollment
To enroll your Snowflake instance:
Navigate to Settings-> Connector Settings -> Snowflake -> + Enroll New Account
Enter your account information and click Next
Choose your Authentication Type.
Enter your user credentials or keypair and click Enroll
Last updated
Was this helpful?