Openflow Connector for Oracle: Set up Snowflake¶

This topic describes how to set up your Snowflake environment for the Openflow Connector for Oracle.

As a Snowflake administrator, perform the following tasks:

1. Create a destination database in Snowflake to store the replicated data:

   CopyExpand

   CREATE DATABASE <destination_database>;
   

   Show lessSee more

   Scroll to top

2. Create a Snowflake service user:

   CopyExpand

   CREATE USER <openflow_user>
     TYPE = SERVICE
     COMMENT='Service user for automated access of Openflow';
   

   Show lessSee more

   Scroll to top

3. Create a Snowflake role for the connector and grant the required privileges:

   CopyExpand

   CREATE ROLE <openflow_role>;
   GRANT ROLE <openflow_role> TO USER <openflow_user>;
   GRANT USAGE ON DATABASE <destination_database> TO ROLE <openflow_role>;
   GRANT CREATE SCHEMA ON DATABASE <destination_database>
     TO ROLE <openflow_role>;
   

   Show lessSee more

   Scroll to top

   Use this role to manage the connector’s access to the Snowflake database.

   To create objects in the destination database, you must grant the USAGE and CREATE SCHEMA privileges on the database to the role used to manage access.

4. Create a Snowflake warehouse for the connector and grant the required privileges:

   CopyExpand

   CREATE WAREHOUSE <openflow_warehouse> WITH
     WAREHOUSE_SIZE = 'XSMALL'
     AUTO_SUSPEND = 300
     AUTO_RESUME = TRUE;
   GRANT USAGE, OPERATE ON WAREHOUSE <openflow_warehouse>
     TO ROLE <openflow_role>;
   

   Show lessSee more

   Scroll to top

   Snowflake recommends starting with a XSMALL warehouse size, then experimenting with size depending on the number of tables being replicated and the amount of data transferred. Large numbers of tables typically scale better with multi-cluster warehouses, rather than a larger warehouse size. For more information, see multi-cluster warehouses.

5. Set up the public and private keys for key pair authentication:

   1. Create a pair of secure keys (public and private).

   2. Store the private key for the user in a file to supply to the connector’s configuration.

   3. Assign the public key to the Snowflake service user:

      CopyExpand

      ALTER USER <openflow_user> SET RSA_PUBLIC_KEY = 'thekey';
      

      Show lessSee more

      Scroll to top

      For more information, see Key-pair authentication and key-pair rotation.