External Tables for Kafka Stream Offset Tracking

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Existing database systems face inefficiencies in processing large volumes of data-in-motion, particularly in integrating non-transactional data streams with transactional databases, leading to high processing overhead and challenges in maintaining data consistency across distributed systems.

Innovation Solution

The implementation of external tables within a database management system (DBMS) that map to external data sources, such as message streams, allowing for efficient data analysis and processing by tracking message stream offsets and managing offsets within ACID transactions, thereby reducing the need for frequent database queries and ensuring data consistency.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Measurement precision

If the application queries the database to look up device information for each message processed, then the device information can be accurately retrieved, but the processing overhead becomes substantial when large numbers of messages are processed

Engineering Contradiction:

Improvedevice information accuracyVSAvoidmessage processing throughput

Core Design Contradiction:

Measurement precisionVSProductivity

Solution Approach 1:

The patent applies preliminary action by pre-fetching device information from the database in batches before message processing begins, rather than querying for each individual message. This allows the application to have device information readily available in memory during high-volume message processing, eliminating the need for repeated database queries and substantially reducing processing overhead while maintaining accurate device information retrieval

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

Inventive Principle:

Principle #10Preliminary action

2Productivity

If tables are exported from the database into the application space for caching, then lookup traffic is reduced, but maintaining coherency between cached tables and master copy becomes complex

Engineering Contradiction:

Improvelookup operation speedVSAvoidcache synchronization complexity

Core Design Contradiction:

ProductivityVSDevice complexity

Solution Approach 1:

The patent introduces an intermediary component (such as a message queue or event stream) that sits between the database and the application's cached tables. When database updates occur, they are published to this intermediary, which then notifies the application to update its local cache. This eliminates the need for complex bidirectional synchronization logic in the application, reducing cache synchronization complexity while maintaining coherency between cached and master data

Inventive Principle:

Principle #24Intermediary (Mediator)

Solution Approach 2:

The patent implements a feedback mechanism where the system monitors database update events and automatically triggers cache invalidation or update operations in response. This event-driven feedback loop ensures that cached tables remain coherent with the master database copy without requiring complex proactive synchronization logic, as the cache is updated reactively in response to actual database changes

Inventive Principle:

Principle #23Feedback

3Adaptability or versatility

If distributed caching is implemented to scale horizontally, then system capacity increases, but keeping multiple caches on different machines up-to-date and consistent becomes increasingly difficult

Engineering Contradiction:

Improvesystem scalabilityVSAvoiddistributed cache coherence management

Core Design Contradiction:

Adaptability or versatilityVSDevice complexity

Solution Approach 1:

The patent uses an intermediary event stream or message queue as a centralized coordination point for the distributed caching system. Each node in the distributed system subscribes to this intermediary and receives notifications about database updates. This allows all caches across multiple machines to be updated consistently from a single source of truth, simplifying distributed cache coherence management while maintaining horizontal scalability

Inventive Principle:

Principle #24Intermediary (Mediator)

Solution Approach 2:

The patent applies segmentation by dividing the distributed caching system into independent, autonomous nodes that each maintain their own local cache. Rather than requiring complex centralized coordination, each node independently subscribes to database update events and updates its local cache accordingly. This segmentation approach allows the system to scale horizontally by simply adding more independent cache nodes without increasing the complexity of cache coherence management

Inventive Principle:

Principle #1Segmentation

Data Source

PatentUS11170002B2Integrating Kafka data-in-motion with data-at-rest tables

Publication Date: 2021.11.09 ORACLE INT CORP

AI summary

Techniques are described herein for analyzing data streams in conjunction with relational database data in a DBMS. A database dictionary defines one or more columns for an external table and a data source for said external table that comprises an external message stream. Executing a query that targets the external table includes generating one or more rows from the external table, each row corresponding to a message in the external message stream that was retrieved from the external message stream after a message stream offset.