Transactional Query Processing in External Tables via DBFS
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database management systems (DBMS) struggle to provide transactional consistency for queries that access external tables, as implementation features needed for transactional processing cannot be applied to external data sources lacking usable transactional metadata.
Innovation Solution
The approach involves using a database file system (DBFS) to store external table files, allowing database processes to access these files directly within the DBMS software layer, thereby maintaining transactional consistency by bypassing the operating system layer.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If external tables are used to access external data sources, then data accessibility and flexibility are improved, but transactional consistency cannot be provided
Solution Approach 1:
The patent introduces an intermediary layer (the DBFS and its file system interface) between the query processing system and the external data source. This intermediary translates external data source operations into database-compatible operations, enabling transactional consistency to be provided for external tables by mediating access through the database file system, which supports transactional metadata and consistent-read operations.
2Reliability
If implementation features for transactional processing are applied to external data sources, then transactional consistency is improved, but device complexity increases
Solution Approach 1:
The patent extracts the transactional processing complexity from the external data source access path and relocates it to the database file system layer. By taking out the transactional metadata management and consistent-read operations from the external data source interaction and implementing them within the DBFS, the system provides transactional consistency without requiring changes to the external data sources themselves, thereby limiting the increase in complexity to the database layer only.
3Ease of operation
If external table files are accessed through the operating system layer, then ease of operation is improved, but transactional context access is lost
Solution Approach 1:
The patent merges the external data source access pathway with the database transactional processing pathway by routing external table file access through the database file system instead of the operating system layer. This combining of access paths ensures that external data reads occur within the transactional context of the database, maintaining consistency without sacrificing the simplicity of file access operations.
Data Source
AI summary
Consistent External Table Access maintains transactional consistency for queries that access external tables stored in a DBFS. This ability is achieved by bypassing the OS. One or more database processes executing a query that access an external table stored in a DBFS access the database-file table like other database tables in the DBMS that can be accessed to execute a query. Based on metadata stored in the DBMS regarding how an external table is stored in a DBFS, a DBMS is able to marshal database processes that access database-file tables directly to execute a query.