2.9.9 Load Kafka Data into Tables Stored in Oracle Database
The Oracle SQL access to Kafka (OSaK) ORA_KAFKA.LOAD_TABLE
procedure loads data from a Kafka topic into a database table.
ORA_KAFKA.LOAD_TABLE
creates a view which is used internally and maps to all
partitions of the Kafka topic.
The view is not deleted at the end of the ORA_KAFKA.LOAD_TABLE
execution. This means that subsequent calls to ORA_KAFKA.LOAD_TABLE
with the
same cluster, group, and topic arguments as passed previously, will start loading where the
previous ORA_KAFKA.LOAD_TABLE
left off, using the same view.
To continuously load Kafka data into the database, the
ORA_KAFKA.LOAD_TABLE
procedure can be called in a loop.
The following example illustrates a single call to the
ORA_KAFKA.LOAD_TABLE
procedure which loads data from the sensor topic into
the Oracle database table sensortab.
DECLARE
num_records_loaded INTEGER;
BEGIN
ORA_KAFKA.LOAD_TABLE
('MA1', -- The name of the cluster
'LOADAPP', -- The name of the Kafka group
'sensor', -- The name of the topic
'CSV', -- The format of the Kafka record
'sensortab', -- The name of the target table in Oracle.
-- This table must reflect the shape of the rows
-- retrieved from Kafka
num_records_loaded); -- The number of Kafka records loaded
dbms_output.put_line(‘Kafka records loaded = ‘ || num_records_loaded);
COMMIT;
END;
/