TLS/SSL encryption with Data Loader for Kafka

You can use TLS/SSL encryption between OpenText™ Analytics Database and your data loader for Kakfa. This encryption prevents others from accessing the data that is sent between Kafka and the database. It can also verify the identity of all parties involved in data streaming, so no impostor can pose as your database cluster or a Kafka broker.

1. Provide Kafka user-defined session parameters.

ALTER SESSION SET MaxSessionUDParameterSize = 100000;
ALTER SESSION SET UDPARAMETER kafka_SSL_Certificate ='<client.pem file content>';
ALTER SESSION SET UDPARAMETER kafka_SSL_PrivateKey_secret = '<client.key file content>';
ALTER SESSION SET UDPARAMETER kafka_SSL_PrivateKeyPassword_secret = 'abcdefgh';
ALTER SESSION SET UDPARAMETER kafka_SSL_CA = '<ca-cert file content>';
ALTER SESSION SET UDPARAMETER kafka_Enable_SSL = 1;

2. Create data loader for Kafka.

For more information about creating data loader for kafka, see CREATE DATA LOADER.

Example

CREATE DATA LOADER testloader AS COPY testtable SOURCE\
KafkaSource(stream='topic0',brokers='localhost:9093',stop_on_eof=true)\
PARSER KafkaJSONParser() REJECTED DATA AS TABLE "testrejects";

3. Create a stored procedure with the following structure.

CREATE PROCEDURE <procedure name>()
LANGUAGE PLvSQL
AS $$
DECLARE
     cert varchar;
     cert_pem varchar;
     cert_key varchar;
BEGIN
     PERFORM ALTER SESSION SET MaxSessionUDParameterSize = 100000;
	 PERFORM ALTER SESSION SET UDPARAMETER kafka_SSL_Certificate ='<client.pem file content>';
     PERFORM ALTER SESSION SET UDPARAMETER kafka_SSL_PrivateKey_secret = '<client.key file content>';
     PERFORM ALTER SESSION SET UDPARAMETER kafka_SSL_PrivateKeyPassword_secret = 'abcdefgh';
     PERFORM ALTER SESSION SET UDPARAMETER kafka_SSL_CA = '<ca-cert file content>';
     PERFORM ALTER SESSION SET UDPARAMETER kafka_Enable_SSL = 1;
    
     EXECUTE 'EXECUTE DATA LOADER <data loader name>';
 END;
 $$;

4. Create a schedule.

CREATE SCHEDULE <schedule name> USING CRON '<duration interval>';

Example

CREATE SCHEDULE kafkaschedule USING CRON '*/5 * * * *';

5. Create a trigger to execute the procedure on the schedule.

CREATE TRIGGER <procedure name> ON SCHEDULE <schedule name> EXECUTE PROCEDURE <procedure name> () AS DEFINER;

Example

CREATE TRIGGER mykafkaprocedure ON SCHEDULE kafkaschedule EXECUTE PROCEDURE mykafkaprocedure() AS DEFINER;