2.9.10 Load Kafka Data into Temporary Tables

Oracle SQL Access to Kafka (OSaK) views are Kafka applications which are not transactional within Oracle. Each scan of a view will likely yield new results since the view typically scans Kafka records from an offset to a topic's high water mark (latest record available) which is continually advancing.

This becomes problematic if one wants consistency across several SQL queries of the same data set retrieved from an OSaK view. It also becomes problematic if one is executing complicated joins with OSaK views without careful hinting in a SQL query that ensures the OSaK view is the outermost table in a join.

OSaK procedures (ORA_KAFKA.LOAD_PRIVATE_TEMP_TABLE and ORA_KAFKA.LOAD_GLOBAL_TEMP_TABLE) solve these problems by creating a temporary table from a SELECT * FROM <view_name>, where view name is an OSaK view. This materializes the data retrieved by a single query into a temporary table. The ORA_KAFKA.LOAD_[PRIVATE|GLOBAL]_TEMP_TABLE procedure is typically called immediately after calling ORA_KAFKA.NEXT_OFFSET, or ORA_KAFKA.SEEK_OFFSET/ORA_KAFKA.SEEK_OFFSET_TS. Application logic then queries against the contents of a temporary table rather then directly querying the OSaK view.

Two types of temporary tables are useful: private temporary tables and global temporary tables, which are created by calling the ORA_KAFKA.LOAD_PRIVATE_TEMP_TABLE or ORA_KAFKA.LOAD_GLOBAL_TEMP_TABLE procedure respectively. A key difference between global temporary tables and private temporary tables is that global temporary tables are more functional and support indexes and triggers, while private temporary tables are lighter weight and don't support indexes or triggers.