Replicate data from HeatWave to Amazon Kinesis

Learn how to use OCI GoldenGate to replicate data from HeatWave to Amazon Kinesis.

Before you begin

To successfully complete this quickstart, you must have:

• OCI Bastion, to connect to HeatWave, load the sample database, and perform inserts.

• A HeatWave database to serve as the source database.

• Amazon S3 Access Key & Secret

• IAM Policies for Amazon Kinesis

Environment set up: HeatWave

To set up the environment for this Quickstart:

1. Download the sample data script, and then run the script on the HeatWave on OCI database to create the database and load the data.

2. Create a ggadmin user using the following script. Remember to replace <ggadmin-password> with a valid password:

   CREATE USER 'ggadmin' IDENTIFIED BY '<ggadmin-password>';
   GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT, CREATE,CREATE VIEW, EVENT, INSERT, UPDATE, DROP,EXECUTE, DELETE ON *.* TO 'ggadmin';

3. Open port 3306, through which OCI GoldenGate can connect.

   1. In the Oracle Cloud console, locate the subnet that the HeatWave on OCI database uses.

   2. In the security list of the subnet, create an Ingress rule for port TCP/3306.

Task 1: Create the OCI GoldenGate resources

1. Create a MySQL deployment for the source HeatWave database.

2. Create a Big Data deployment for Amazon Kinesis target.

3. Create a connection to the source Heatwave Database.

4. Connect to Amazon Kinesis.

5. Create a connection to GoldenGate, and then assign this connection to the source MySQL deployment.

6. Assign the source connection to the source MySQL deployment.

7. Assign the target connection to the target Big Data deployment.

Task 2: Create the Extract

1. On the Deployments page, select the MySQL deployment created in Task 1.

2. On the deployment details page, select Launch Console.

3. Sign in to the source MySQL deployment console using the Administrator credentials specified when you created the deployment in Task 1.

4. Add a Change Data Capture Extract with the following values:

   • Extract Type: Change Data Capture Extract

   • Process Name: ECDC

   • Extract Trail Name: C1

   • Parameter File: Replace MAP *.*, TARGET *.*; with TABLE SRC_OCIGGLL.*;

Task 3: Create the Distribution Path to target Big Data deployment

1. If using IAM credential store, proceed Step 3. If using GoldenGate credential store, create a user with which the source GoldenGate uses to connect to the target GoldenGate.

   1. On the Deployments page, select the target Big Data deployment.

   2. On the deployment details page, select Launch Console, and then log in as the admin user.

   3. In the deployment console navigation menu, select User Administration.

   4. Select Add New User (plus icon), complete the fields as follows, and then select Submit:

      • For Username, enter ggsnet.

      • For Role, select Operator.

      • Enter the password twice for verification.

2. In the source MySQL deployment console, create a Path Connection for the user created in the previous step.

   1. Open the navigation menu, and then select Path Connection.

   2. Select Add Path Connection (plus icon), complete the fields as follows, and then select Submit:

      • For Credential Alias, enter dpuser.

      • For User ID, enter ggsnet

      • For Password, enter the same password used in the previous step.

3. In the source MySQL 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 2.

      • For Trail Name, enter a two-character name, such as C1.

   2. On the Target Options page:

      • 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 C1.

      • For Alias, enter the Credential Alias created in Step 2.

4. In the target OCI GoldenGate Big Data deployment console, review the Receiver path created as a result of the Distribution Path:

   1. Select Receiver Service.

   2. Review the Receiver path details.

Task 4: Add a Replicat for Amazon Kinesis

1. In Big Data deployment, select Replicats, and then select Add Replicat (plus icon).

2. Add a Replicat with the following values:

   • For Replicat type, select Classic Replicat

   • For Process Name, enter a name.

   • For Target Trail Name, enter the name of the Trail from Task 2.

   • For Target, select Amazon Kinesis.

   • For Available Aliases for Amazon Kinesis, select your alias from the dropdown.

   • On the Properties File page, edit the fields marked #TODO:

     • gg.handler.kinesis.region: provide the Amazon Web Services (AWS) region for the target Kinesis stream.

     • gg.handler.kinesis.streamMappingTemplate: by default, it is set to ${tableName} which will map the streams based on source table name. If you want to map to an existing data stream, you can provide static stream names or you can use Template Keywords to assign stream names dynamically.

Task 5: Verify Data Replication from HeatWave to Amazon Kinesis

Perform updates to the source HeatWave database to verify replication to Amazon Kinesis.

1. In OCI Bastion, create an SSH port forwarding session using MySQL IP and port 3306. Add your public SSH key.

2. Connect to MySQL in Cloud Shell using your private key and port 3306.

3. After connecting successfully, run the following command:

   mysqlsh admin@localhost:3306 --sql

4. Run the following script to perform inserts into the HeatWave database:

   use SRC_OCIGGLL;
   Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1000,'Houston',20,743113);
   Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1001,'Dallas',20,822416);
   Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1002,'San Francisco',21,157574);
   Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1003,'Los Angeles',21,743878);
   Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1004,'San Diego',21,840689);
   Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1005,'Chicago',23,616472);
   Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1006,'Memphis',23,580075);
   Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1007,'New York City',22,124434);
   Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1008,'Boston',22,275581);
   Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1009,'Washington D.C.',22,688002);
   commit;

5. In the source MySQL deployment console, select the Change Data Capture Extract name (ECDC), and then select Statistics. Verify that SRC_OCIGGLL.SRC_CITY has 10 inserts.

6. In target Big Data deployment console, select the Replicat Name and then select Statistics. Verify that SRC_OCIGGLL.SRC_CITY has 10 inserts.

7. In Amazon Kinesis, you can check the messages in target data stream/data viewer.