Snowflake Connection Configuration
  • 07 Dec 2022
  • PDF

Snowflake Connection Configuration

  • PDF

Article Summary

Note

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
nameYesEnter the name of the connection information to be used inside TROCCO.
memorandumNoEnter a memo of the connection information used inside TROCCO.
Destination hostYesEnter the Snowflake host.
Please refer to the image below for a specific example.
image.png
userYesPlease enter your Snowflake username.
Authentication methodYesSelect User Password Authentication for the forwarding settings and Key Pair Authentication for the datamart settings.
passwordYesEnter your Snowflake password.
Private keyNoEnter 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

ItemPermission
Content Remarks
Virtual warehouse permissionsOPERATEModifies the state of the warehouse (stopped, started, paused, resumed) so that running queries can be stopped.
Virtual warehouse permissionsUSAGEUse a virtual warehouse and, as a result, query the warehouse.
Database permissionsUSAGEMake the database available. Additional permissions are required to view or perform actions on objects in the database.
Schema permissionsUSAGEMake the schema available.
Table permissionsSELECTEnables 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

ItemPermission
Content Remarks
Virtual warehouse permissionsOPERATEModifies the state of the warehouse (stopped, started, paused, resumed) so that running queries can be stopped.
Virtual warehouse permissionsUSAGEUse a virtual warehouse and, as a result, query the warehouse.
Database permissionsUSAGEMake the database available. Additional permissions are required to view or perform actions on objects in the database.
Schema permissionsUSAGEMake the schema available.
Schema permissionsCREATE TABLEEnables the schema to create new tables, such as cloning tables.
Schema permissionsCREATE STAGEEnables you to create a new stage in the schema, such as cloning a stage.
Table permissionsOWNERSHIPTransfer 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

ItemPermission
Content Remarks
Virtual warehouse permissionsOPERATEModifies the state of the warehouse (stopped, started, paused, resumed) so that running queries can be stopped.
Virtual warehouse permissionsUSAGEUse a virtual warehouse and, as a result, query the warehouse.
Database permissionsUSAGEMake the database available. Additional permissions are required to view or perform actions on objects in the database.
Schema permissionsUSAGEMake the schema available.
Schema permissionsCREATE TABLEEnables the schema to create new tables, such as cloning tables.
Table permissionsSELECTEnables the execution of a SELECT statement on a table.
Table permissionsINSERTEnables 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 permissionsOWNERSHIPTransfer 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='[設定用の公開鍵の出力結果]'

Was this article helpful?