- 07 Dec 2022
- 5 Minutes to read
- Print
- DarkLight
Snowflake Connection Configuration
- Updated on 07 Dec 2022
- 5 Minutes to read
- Print
- DarkLight
This is a machine-translated version of the original Japanese article.
Please understand that some of the information contained on this page may be inaccurate.
summary
This page provides help for configuring Snowflake connection information.
Input field
Item nameRequired | content | |
---|---|---|
name | Yes | Enter the name of the connection information to be used inside TROCCO. |
memorandum | No | Enter a memo of the connection information used inside TROCCO. |
Destination host | Yes | Enter the Snowflake host. Please refer to the image below for a specific example. ![]() |
user | Yes | Please enter your Snowflake username. |
Authentication method | Yes | Select User Password Authentication for the forwarding settings and Key Pair Authentication for the datamart settings. |
password | Yes | Enter your Snowflake password. |
Private key | No | Enter your Snowflake private key. * Required for key pair authentication. |
supplement
About permissions
- To use Snowflake, grant the required privileges to the role and grant the role to the user.
Permissions required to use forwarding settings
Permissions required if you select Snowflake as the transfer source
Item | Permission | Content Remarks | |
---|---|---|---|
Virtual warehouse permissions | OPERATE | Modifies the state of the warehouse (stopped, started, paused, resumed) so that running queries can be stopped. | |
Virtual warehouse permissions | USAGE | Use a virtual warehouse and, as a result, query the warehouse. | |
Database permissions | USAGE | Make the database available. Additional permissions are required to view or perform actions on objects in the database. | |
Schema permissions | USAGE | Make the schema available. | |
Table permissions | SELECT | Enables the execution of a SELECT statement on a table. |
Sample query
- Role to create
TROCCO_INPUT_ROLE
- Who to grant the role to
TROCCO_USER
- Target Warehouse
TROCCO_WAREHOUSE
- Target database
TROCCO_DATABASE
- Target Schema
PUBLIC
- Target table
TROCCO_TABLE
- * Please rewrite the roles, users, target warehouses, databases, schemas, and table names specified in the sample to the contents that are actually used.
-- Switch to roles that allow you to create roles
USE ROLE ACCOUNTADMIN;
-- Create a new role
CREATE ROLE TROCCO_INPUT_ROLE;
-- Make the role available to specified users
GRANT ROLE TROCCO_INPUT_ROLE TO USER TROCCO_USER;
-- Make the created role the default role for the user
ALTER USER TROCCO_USER SET DEFAULT_ROLE = TROCCO_INPUT_ROLE;
-- Grant warehouse usage rights to roles
GRANT USAGE ON WAREHOUSE TROCCO_WAREHOUSE TO ROLE TROCCO_INPUT_ROLE;
GRANT OPERATE ON WAREHOUSE TROCCO_WAREHOUSE TO ROLE TROCCO_INPUT_ROLE;
-- Grant database usage permissions to roles
GRANT USAGE ON DATABASE TROCCO_DATABASE TO ROLE TROCCO_INPUT_ROLE;
-- Grant schema usage permissions to roles
GRANT USAGE ON SCHEMA TROCCO_DATABASE.PUBLIC TO ROLE TROCCO_INPUT_ROLE;
-- Grant the role permission to execute SELECT queries on all tables in the schema
GRANT SELECT ON ALL TABLES IN SCHEMA TROCCO_DATABASE.PUBLIC TO ROLE TROCCO_INPUT_ROLE;
-- * When granting the role permission to enable SELECT queries to be executed only on individual tables
GRANT SELECT on TABLE TROCCO_DATABASE.PUBLIC.TROCCO_TABLE TO ROLE TROCCO_INPUT_ROLE;
Permissions required if you selected Snowflake as the destination
Item | Permission | Content Remarks | |
---|---|---|---|
Virtual warehouse permissions | OPERATE | Modifies the state of the warehouse (stopped, started, paused, resumed) so that running queries can be stopped. | |
Virtual warehouse permissions | USAGE | Use a virtual warehouse and, as a result, query the warehouse. | |
Database permissions | USAGE | Make the database available. Additional permissions are required to view or perform actions on objects in the database. | |
Schema permissions | USAGE | Make the schema available. | |
Schema permissions | CREATE TABLE | Enables the schema to create new tables, such as cloning tables. | |
Schema permissions | CREATE STAGE | Enables you to create a new stage in the schema, such as cloning a stage. | |
Table permissions | OWNERSHIP | Transfer ownership of the table. This gives you full control over the table. | If the transfer mote is replace, Required to execute DROP TABLE queries. * To use the DROP TABLE query, you must use a role that owns the table. Snowflake Documentation - Usage Notes * Required only when executing on an existing table. |
Sample query
- Role to create
TROCCO_OUTPUT_ROLE
- Who to grant the role to
TROCCO_USER
- Target Warehouse
TROCCO_WAREHOUSE
- Target database
TROCCO_DATABASE
- Target Schema
PUBLIC
- Target table
TROCCO_TABLE
- * Please rewrite the roles, users, target warehouses, databases, schemas, and table names specified in the sample to the contents that are actually used.
-- Switch to roles that allow you to create roles
USE ROLE ACCOUNTADMIN;
-- Create a new role
CREATE ROLE TROCCO_OUTPUT_ROLE;
-- Make the role available to specified users
GRANT ROLE TROCCO_OUTPUT_ROLE TO USER TROCCO_USER;
-- Make the created role the default role for the user
ALTER USER TROCCO_USER SET DEFAULT_ROLE = TROCCO_OUTPUT_ROLE;
-- Grant warehouse usage rights to roles
GRANT USAGE ON WAREHOUSE TROCCO_WAREHOUSE TO ROLE TROCCO_OUTPUT_ROLE;
GRANT OPERATE ON WAREHOUSE TROCCO_WAREHOUSE TO ROLE TROCCO_OUTPUT_ROLE;
-- Grant database usage permissions to roles
GRANT USAGE ON DATABASE TROCCO_DATABASE TO ROLE TROCCO_OUTPUT_ROLE;
-- Grant schema usage permissions to roles
GRANT USAGE ON SCHEMA TROCCO_DATABASE.PUBLIC TO ROLE TROCCO_OUTPUT_ROLE;
-- Grant the role permission to create tables
GRANT CREATE TABLE ON SCHEMA TROCCO_DATABASE.PUBLIC TO ROLE TROCCO_OUTPUT_ROLE;
-- Grant privileges to a role to operate stages
GRANT CREATE STAGE on SCHEMA TROCCO_DATABASE.PUBLIC to ROLE TROCCO_OUTPUT_ROLE;
-- Transfer table ownership to a role
-- * Required when executing against an existing table
GRANT OWNERSHIP ON TABLE TROCCO_DATABASE.PUBLIC.TROCCO_TABLE TO ROLE TROCCO_OUTPUT_ROLE;
Permissions required to use datamarts
Item | Permission | Content Remarks | |
---|---|---|---|
Virtual warehouse permissions | OPERATE | Modifies the state of the warehouse (stopped, started, paused, resumed) so that running queries can be stopped. | |
Virtual warehouse permissions | USAGE | Use a virtual warehouse and, as a result, query the warehouse. | |
Database permissions | USAGE | Make the database available. Additional permissions are required to view or perform actions on objects in the database. | |
Schema permissions | USAGE | Make the schema available. | |
Schema permissions | CREATE TABLE | Enables the schema to create new tables, such as cloning tables. | |
Table permissions | SELECT | Enables the execution of a SELECT statement on a table. | |
Table permissions | INSERT | Enables INSERT statements to be executed on tables. | If the write mode is append, it is required to register data in the output destination table. |
Table permissions | OWNERSHIP | Transfer ownership of the table. This gives you full control over the table. | If the write mode is Wash All, this is required to delete the destination table. * Required only when executing on an existing table. |
Sample query
- Role to create
TROCCO_DATAMART_ROLE
- Who to grant the role to
TROCCO_USER
- Target Warehouse
TROCCO_WAREHOUSE
- Target database
TROCCO_DATABASE
- Target Schema
PUBLIC
- Target table
TROCCO_TABLE
- * Please rewrite the roles, users, target warehouses, databases, schemas, and table names specified in the sample to the contents that are actually used.
-- Switch to roles that allow you to create roles
USE ROLE ACCOUNTADMIN;
-- Create a new role
CREATE ROLE TROCCO_DATAMART_ROLE;
-- Make the role available to specified users
GRANT ROLE TROCCO_DATAMART_ROLE TO USER TROCCO_USER;
-- Make the created role the default role for the user
ALTER USER TROCCO_USER SET DEFAULT_ROLE = TROCCO_DATAMART_ROLE;
-- Grant warehouse usage rights to roles
GRANT USAGE ON WAREHOUSE TROCCO_WAREHOUSE TO ROLE TROCCO_DATAMART_ROLE;
GRANT OPERATE ON WAREHOUSE TROCCO_WAREHOUSE TO ROLE TROCCO_DATAMART_ROLE;
-- Grant database usage permissions to roles
GRANT USAGE ON DATABASE TROCCO_DATABASE TO ROLE TROCCO_DATAMART_ROLE;
-- Grant schema usage permissions to roles
GRANT USAGE ON SCHEMA TROCCO_DATABASE.PUBLIC TO ROLE TROCCO_DATAMART_ROLE;
-- Grant the role permission to create tables
GRANT CREATE TABLE ON SCHEMA TROCCO_DATABASE.PUBLIC TO ROLE TROCCO_DATAMART_ROLE;
-- Grant the role permission to execute INSERT queries to the table
GRANT INSERT ON TABLE TROCCO_DATABASE.PUBLIC.TROCCO_TABLE TO ROLE TROCCO_DATAMART_ROLE;
-- Transfer table ownership to a role
-- * Required when executing against an existing table.
GRANT OWNERSHIP ON TABLE TROCCO_DATABASE.PUBLIC.TROCCO_TABLE TO ROLE TROCCO_DATAMART_ROLE;
About Key Pair Authentication
Creating a Key Pair
Execute the following command in the terminal
- Create a private key
$ openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
- Generating a public key
$ openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
- Output the public key for setting (remove line breaks and unnecessary parts)
$ cat rsa_key.pub | \
tr -d "\n" | \
sed "s/-----BEGIN PUBLIC KEY-----//g" | \
sed "s/-----END PUBLIC KEY-----//g"
Setting the Public Key to Snowflake
-- Link the public key to any user
alter user [username] set rsa_public_key='[設定用の公開鍵の出力結果]'