2.9.5 Create Views to Access JSON Data in a Kafka Topic

In order to query data from a Kafka topic containing JSON data, Oracle SQL access to Kafka (OSaK) views must be created specifying JSON_VARCHAR2 as the format of the topic record.

When the Kafka record format is JSON_VARCHAR2, a reference table is not used and should be passed as NULL.

The following example creates one view for the single partition in the topic ‘sensorj’ where the record format is JSON:

DECLARE
    views_created INTEGER;
    application_id VARCHAR2(128);
BEGIN
    ORA_KAFKA.CREATE_VIEWS
      ('MA1',                    –- The name of the cluster
       'QUERYAPP_JSON',          -- The name of the Kafka group
       'sensorj',                –- The name of the Kafka topic
       ‘JSON_VARCHAR2’,           -- The format of the topic record
       NULL,                     -- No reference table used for JSON
       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
    dbms_output.put_line(‘views created = ‘ || views_created);
    dbms_output.put_line(‘application id = ‘ || application_id);
  END;
/

The above example causes the following view to be created:

SQL> describe KV_MA1_QUERYAPP_JSON_SENSORJ_0;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 KAFKA$PARTITION                                    NUMBER(38)
 KAFKA$OFFSET                                       NUMBER(38)
 KAFKA$EPOCH_TIMESTAMP                              NUMBER(38)
 KEY                                                VARCHAR2(32767)
 VALUE                                              VARCHAR2(32767)

Where KAFKA$PARTITION is the Kafka partition id, KAFKA$OFFSET is the offset of the Kafka record, KAFKA$EPOCH_TIMESTAMP is the timestamp of the Kafka record. The KEY and VALUE columns contain the JSON data.