Openflow Connector for Oracle: Set up Snowflake¶

As an Openflow administrator, perform the following tasks for this connector. With the default SNOWFLAKE_MANAGED authentication strategy, the runtime’s execute-as role is the identity the connector uses to access Snowflake, so you grant these privileges to that role.

1. Create a database to store the replicated data, and grant the execute-as role USAGE and CREATE SCHEMA on it. The connector creates destination schemas automatically. Snowflake recommends a dedicated destination database per connector, to avoid collisions with other data sources including other connectors.

   Keep this destination database separate from the database that holds your Openflow infrastructure objects, such as the runtime, the connector, and any secrets. A connector creates destination objects based on the source schema and table names, so those names aren’t under your control and can change as the source changes.

   Copy code

   CREATE DATABASE IF NOT EXISTS <destination_database>;
   
   GRANT USAGE ON DATABASE <destination_database> TO ROLE OPENFLOW_<RUNTIME_NAME>_EXECUTE_AS_RL;
   GRANT CREATE SCHEMA ON DATABASE <destination_database> TO ROLE OPENFLOW_<RUNTIME_NAME>_EXECUTE_AS_RL;
   

2. Designate a warehouse for the connector to use, and grant the execute-as role USAGE and OPERATE on it. Start with the XSMALL warehouse size, then experiment with size depending on the number of tables being replicated, and the amount of data transferred. Large table numbers typically scale better with multi-cluster warehouses, rather than the warehouse size.

   Copy code

   CREATE WAREHOUSE <ingest_warehouse>
     WITH
       WAREHOUSE_SIZE = 'XSMALL'
       AUTO_SUSPEND = 300
       AUTO_RESUME = TRUE;
   
   GRANT USAGE, OPERATE ON WAREHOUSE <ingest_warehouse> TO ROLE OPENFLOW_<RUNTIME_NAME>_EXECUTE_AS_RL;
   

3. Snowflake deployments only: Make sure this connector’s source host and port are permitted by a network rule that your runtime’s external access integration (EAI) allows.

   The EAI itself belongs to the runtime, not to this connector. You create it once, attach it to the runtime, and grant the execute-as role USAGE on it. For those steps, see Creating network rules and external access integrations. What is specific to this connector is getting its source host into a rule that EAI references.

   The rule takes the source’s host and port as a single value, such as db.example.com:<port>. That’s the host and port from the connector’s connection URL, without the jdbc: scheme, the driver name, or the database path.

   BYOC deployments handle outbound connectivity in the cloud environment and don’t use EAIs or network rules.