Replicate data from Snowflake to Autonomous AI Database

Learn to replicate data from Snowflake to Autonomous AI Database in OCI GoldenGate.

Before you begin

To successfully complete this quickstart, you must have:

Task 1: Create the OCI GoldenGate resources

  1. Create an OCI GoldenGate Big Data deployment.
  2. Create an Oracle AI Database deployment.
  3. Create a Snowflake connection.
  4. Assign the Snowflake connection to the Big Data deployment.
  5. Create an Oracle Autonomous AI Database connection.
  6. Assign the Autonomous AI Transaction Processing connection to the Oracle deployment.
  7. Create a GoldenGate server connection, and assign it to the Oracle deployment.

Task 2: Create a source Snowflake table

For this quickstart, you can use existing Snowflake tables. If you use existing Snowflake tables, then go to Task 3.

In the source Snowflake account, create a table:

CREATE TABLE IF NOT EXISTS <your-database>.<your-schema>.CAPTURE_TEST (
    ID          NUMBER(38,0) NOT NULL,
    DESCRIPTION VARCHAR(200),
    STATUS      VARCHAR(30),
    AMOUNT      NUMBER(12,2),
    CREATED_AT  TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(),
    CONSTRAINT PK_CAPTURE_TEST PRIMARY KEY (ID)
);

Task 3: Prepare the source Snowflake table for change data capture

In Snowflake, enable CHANGE_TRACKING on every table before starting the OCI GoldenGate Snowflake Extract, and set DATA_RETENTION_TIME_IN_DAYS. Change tracking lets the OCI GoldenGate Snowflake Extract read row changes through the Snowflake CHANGES clause. Time Travel retention keeps that history available long enough for the Extract to catch up after an outage.

  1. Check the table’s current effective retention and change tracking:

    SHOW TABLES LIKE 'CAPTURE_TEST' IN SCHEMA <your-database>.<your-schema>;
  2. Enable change tracking and set retention to seven days:

    ALTER TABLE <your-database>.<your-schema>.CAPTURE_TEST
        SET CHANGE_TRACKING = TRUE;
    
    ALTER TABLE <your-database>.<your-schema>.CAPTURE_TEST
        SET DATA_RETENTION_TIME_IN_DAYS = 7;
  3. Check the table’s current effective retention and change tracking again, and observe the changes:

    SHOW TABLES LIKE 'CAPTURE_TEST' IN SCHEMA <your-database>.<your-schema>;

Note: If you use your own Snowflake tables, then update the table names when you run the commands.

Task 4: Create a OCI GoldenGate Snowflake Extract

  1. In the OCI GoldenGate deployment console navigation menu, select Extracts.
  2. On the Extracts page, select Add Extract.
  3. In the Add Extract panel, on the Extract Information page, complete the following fields, and then select Next:

    • Enter a Process Name that is no more than eight characters long.
    • Enter an optional Description to help you distinguish this process from others.
    • From the Source list, select Snowflake.
    • For Extract Type, select Change Data Capture Extract.
  4. On the Extract Options page, complete the following fields, and then select Next:

    • Select Source Credentials.
    • For Extract Trail, complete the following fields:

      1. Enter a Name for the Extract process.
      2. Enter Trail Sequence to set the starting number for trail files.
      3. Enter Trail Size to set the maximum size for the generated trail file.
      4. For Begin, select when to begin the Extract. For this quickstart, leave the default value, Now.
      5. Select an Encryption Profile. If you haven’t created an encryption profile, then Local Wallet is selected by default.
      6. Select an Encryption Algorithm.
  5. On the Managed Options page, complete the following optional fields, and then select Next:

    • Profile Name
    • Critical to deployment health
    • Auto Start
    • Auto Restart
  6. On the Parameter File page, update the source mapping. For example, enter TABLE <your-database>.<your-schema>.CAPTURE_TEST;.
  7. Select Create and Run to create and start the Extract. If you select Create, then you can manually start the Extract later from the Extracts page.

Task 5: Add and run the Distribution Path

To send captured change data records to the Oracle Autonomous AI Database Replicat, configure a Distribution Path from the source OCI GoldenGate Big Data deployment to the target OCI GoldenGate Oracle deployment.

  1. If you use the GoldenGate credential store, then create a user for the Distribution Path in the target Oracle AI Database deployment. Otherwise, go to Step 3.
  2. In the source Big Data deployment console, add a Path Connection for the user created in Step 1:

    1. In the navigation menu, select Path Connections.
    2. Select Add Path Connection (the plus icon), and then complete the following fields:

      • For Credential Alias, enter GGSNetwork.
      • For User ID, enter the name of the user created in Step 1.
      • Enter the user’s password twice for verification.
    3. Select Submit.

    The path connection appears in the Path Connections list.

  3. In the source Big Data deployment console, add a Distribution Path with the following values:

    1. On the Source Options page, for Source Extract, select the Extract created in Task 4.
    2. On the Target Options page, complete the following fields:

      • For Target Host, enter the host domain of the target deployment.
      • For Port Number, enter 443.
      • For Trail Name, enter a two-character name, such as E1.
      • For Alias, enter the Credential Alias created in Step 2.
  4. In the target Oracle AI Database deployment console, review the Receiver Path created from the Distribution Path:

    1. Select Receiver Service.
    2. Review the path details.

Task 6: Add an Oracle AI Database Replicat

Add and run a Non-integrated Replicat.

Task 7: Perform inserts in the source database

Run the following statement in the source database:

INSERT INTO DSTEST.PUBLIC.GG_CAPTURE_TEST (ID, NAME, EMAIL, AMOUNT)
VALUES
    (1,  'Alice Smith', 'alice.smith@example.com', 125.50),
    (2,  'Bob Jones',   'bob.jones@example.com',    89.99),
    (3,  'Carol White', 'carol.white@example.com', 240.00),
    (4,  'David Brown', 'david.brown@example.com',  15.75),
    (5,  'Eva Green',   'eva.green@example.com',   310.25),
    (6,  'Frank Black', 'frank.black@example.com',  72.00),
    (7,  'Grace Lee',   'grace.lee@example.com',   199.95),
    (8,  'Henry King',  'henry.king@example.com',   45.50),
    (9,  'Ivy Clark',   'ivy.clark@example.com',   520.00),
    (10, 'Jack Young',  'jack.young@example.com',   33.33);