2.9.7 Explore Kafka Data from a Specific Offset

Oracle SQL access to Kafka (OSaK) allows you to read a specified number of records from a specific offset. This type of access is restricted to applications that create one view per single Kafka topic/partition

The following example creates a view for a new application, name SEEKAPP, to query the ‘sensor’ topic with records in CSV format. The example uses the SEEK_OFFSET procedure to specify offset 100393 and then query 1000 records. If there are fewer than 1000 records available, all available records will be queried.

-- First create views for the seek application
DECLARE
  views_created INTEGER;
  application_id VARCHAR2(128);
BEGIN
  ORA_KAFKA.CREATE_VIEWS
    ('MA1',                     -- The name of the cluster
    'SEEKAPP',                  -- The name of the Kafka group
    'sensor',                   -- The name of the Kafka topic
    'CSV',                      -- The format of the topic record
    'SENSOR_RECORD_SHAPE',      -- The name of the database reference table
    views_created,              -- Output: number of views created
    application_id,             -- Output: the application id of the set of views
                                -- created that uniquely identifies the view
                                -- objects
    0);                         -- The number of views to create.  0, the default,
                                -- requests the creation of 1 view per
                                -- Kafka partition
	dbms_output.put_line(‘views created = ‘ || views_created);
	dbms_output.put_line(‘application id = ‘ || application_id);
END;
/

-- Next we seek to offset 100393
SQL> execute ORA_KAFKA.SEEK_OFFSET
  ('KV_MA1_SEEKAPP_SENSOR_0',    -- The name of the OSAK view that maps to a
                                 -- single cluster/topic/partition
  100393,                        -- The offset to which to seek
  1000);                         -- The number of Kafka rows starting from the
                                 -- offset to be retrieved

-- Now query for at most 1000 rows starting at offset 100393
SQL> SELECT max(temperature) from KV_MA1_SEEKAPP_SENSOR_0;