Connect Kafka Streams Data to Oracle Autonomous Database

Oracle GoldenGate Stream Analytics allows users to build scalable data transformation and analytics pipelines for agile real-time business insights.

This section assumes you have already installed Oracle Autonomous Database from the Before You Begin section. You will now learn to do the following:

• Get access to the GGSA Console.
• Configure Kafka producer to ingest data.
• Connect GGSA for Kafka to Autonomous Database.

Get Access to the GGSA Console

1. In the OCI console, under Compute, select Instances. The GGSA marketplace instance should be up and running.
2. Copy the Public IP Address.
3. Log in to the instance using your private key and check the README.txt under /home/opc.
4. Copy the OSA UI password.
5. Open a browser and enter https://<Public IP Address>/osa.
6. Enter the Username as osaadmin and use the password copied from README.txt.

Configure Kafka Producer to Ingest Data

Follow these steps to create a Kafka stream.

1. On the Catalog page, click Create New Item.
2. Hover the mouse over Connection and select Kafka from the submenu.
3. On the Type Properties screen, enter a Name and select Connection Type as Kafka. For this example, we will use the Kafka that is installed in the GGSA instance.
4. In the Connection Details screen, enter localhost:2181 in the Zookeepers field.
5. Click Test Connection. You should see a Successful message.
6. Click Save.

   Note:

   Ensure port 2181 is opened in your ingress.

Follow these steps to start the Kafka topic to ingest data.

1. SSH to you GGSA instance and go to the /u01/app/osa/utilities/kafka-utils folder.
2. You'll use complex.json as the incoming data.
3. Execute the following command to loop the data feed as a Kafka topic:

   opc@ggsanew kafka-utils]$ ./loop-file.sh ./complex.json | ./sampler.sh 1 1 | ./kafka.sh feed complex

   The complex Kafka topic starts producing data and gets ready for ingestion.
4. On the Catalog page, click Create New Item, to create a stream using the Kafka connection.
5. Hover the mouse over Stream and select Kafka from the submenu.
6. On the Type Properties screen, enter a name and select the Stream Type as Kafka.
7. Click Next.
8. On the Source Details screen, and select the Kafka connection you created in Connections.
   1. Select the Topic Name as complex.
   2. Select the Data Format as JSON.
9. Click Next.
10. On the Data Format screen, leave in the default values.
11. Click Next.
12. On the Shape screen, the incoming JSON shape is inferred from Stream.
13. Click Save. The Kafka stream is successfully created.

Connect GGSA for Kafka to Autonomous Database

Follow these steps to create a connection to Oracle Autonomous Database from GGSA.

1. On the Catalog page, click Create New Item.
2. Hover the mouse over Connection and select Oracle Database from the submenu.
3. On the Type Properties screen, enter a Name and select the Connection Type as Oracle Database.
4. Click Next.
5. On the Connection Details, Type: Oracle Database screen, enter the Autonomous Database connection details.
   • Select Wallet in Connect Using and upload the wallet file.
   • Select the Service Name/SID from the drop-down list.
   • Enter the Username as admin.
   • Enter the database admin password.
6. Click Save. The Autonomous Database connection is created successfully.
7. Log in to a schema in Autonomous Database and create a table to receive the data.

   CREATE TABLE COMPLEX
           (  BOOLEANFIELD VARCHAR2(20) , NUMBERFIELD NUMBER
           , STRINGFIELD VARCHAR2(20)
           , OBJECTFIELD_A_KEY NUMBER
           , OBJECTFIELD_A_VALUE NUMBER
           , OBJECTFIELD_C VARCHAR2(20)
           , OBJECTFIELD_E VARCHAR2(20)
           , ARRAYFIELD_0 NUMBER
           , ARRAYFIELD_1 NUMBER
           
       ); 

Follow these steps to create a pipeline in GGSA and set up the source and target:

1. On the Catalog page, click Create New Item, and select Pipeline from the drop-down list.
2. Hover the mouse over the Target and select Database Table from the submenu.
3. On the Type Properties screen, enter a Name for the Target , select Target Type as Database Table.
4. Click Next.
5. On the Target Details screen, select the Autonomous Database table that you created earlier from the drop-down list.
6. Click Next.
7. On the Shape screen, select the Table Name as complex from the drop-down list.
8. Click Next.
9. Infer Shape and click Save.

Follow these steps to set the target:

1. On the Catalog page, click Create New Item, and select Pipeline from the drop-down list.
2. Hover the mouse over the Target and select Kafka Stream from the submenu.
3. Click Save.
4. In the Pipeline, right-click on the stream, select Add Stage, and then select Target.
5. In the Create Target Stage window, enter a name and click Save.
6. Select the Target table that you created earlier.
7. Click Publish to publish the pipeline and make the data available in the target table.

8. Log in to the database to see the data getting loaded into the complex table in Autonomous Database.