2.9.11 Customize Oracle SQL Access to Kafka Views

The Oracle SQL access to Kafka (OSaK) ORA_KAFKA.CREATE_VIEWS procedure creates one or more views which map to external tables that retrieve data from partitions of a Kafka topic. Each view retrieves data from one or more partitions in a topic.

The ability to create multiple views over multiple partitions allows an application to scale out and divide the workload across application instances that are running concurrently. Only one application instance should read an Oracle SQL access to Kafka (OSaK) view. Multiple readers may result in duplicate Kafka records being processed or Kafka records being skipped.

By default, the ORA_KAFKA.CREATE_VIEWS procedure creates one view per topic partition for a particular cluster/group/topic. For example, if a topic has eight partitions, the default is to create eight views. In some cases it may be useful to create one view over all partitions of a topic. In other cases, it may be useful to create multiple views, each one over multiple Kafka partitions. For example, one view for every 4 partitions. The ORA_KAFKA.CREATE_VIEWS procedure has optional parameters that allow the number of views to be specified.

By default, a Kafka topic record format that is specified as 'CSV' is considered to have fields delimited by a comma, and records terminated by new line. The ORA_KAFKA.CREATE_VIEWS procedure has an optional parameter, view_properties, that allows the field and record delimiters to be specified.

PROCEDURE CREATE_VIEWS (
           cluster_name         IN  VARCHAR2,
           group_name           IN  VARCHAR2,
           topic_name           IN  VARCHAR2,
           topic_record_format  IN  VARCHAR2,
           ref_table            IN  VARCHAR2,
           views_created        OUT INTEGER,
           application_id       OUT VARCHAR2,
           view_count           IN  INTEGER DEFAULT 0,
           force_view_count     IN  BOOLEAN DEFAULT FALSE,
           view_properties      IN  VARCHAR2 DEFAULT NULL
);

The view_count parameter allows the application to specify the number of views to create. Legal values are 0 to N, where N is the number of Kafka partitions in the topic. The default value is 0 which instructs CREATE_VIEWS to create 1 view per Kafka partition in the topic.

The force_view_count parameter can be TRUE or FALSE, the default value is FALSE. If force_view_count is set to TRUE, ORA_KAFKA.CREATE_VIEWS creates ‘view_count’ views even if that number could create unbalanced views, where different views read from different numbers of Kafka topic partitions. For example, setting a view count of 2 when there are 5 Kafka topic partitions causes ORA_KAFKA.CREATE_VIEWS to create one view that maps to 2 Kafka topic partitions and one view that maps to 3 Kafka topic partitions.

The view_properties parameter is an optional parameter that allows you to specify custom field and/or record delimiters. It is formatted as a JSON string in the JSON syntax described in the JSON developers guide: 5 SQL/JSON Conditions IS JSON and IS NOT JSON in JSON Developer's Guide.

The supported keys are:

• field_delim: field delimiter as a json value
• record_delim: record delimiter as a json value

Examples:

{"field_delim":\u0001","record_delim":"\r\n"}
{"record_delim":"\r\n"}
{"field_delim":"\u0001"}