Adding Partition to Distributed Table Without Downtime
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing distributed file systems, such as Apache Hadoop, do not allow for the creation of new partitions in tables after they have been created, requiring a complex and time-consuming process to add new columns and partitions, which disrupts database operations and is not transparent to users.
Innovation Solution
A method to add a new column to an existing table as a partition in a distributed file system by creating a new table with the new column, renaming the original table, and transferring data to the new table without downtime, using atomic operations and views to manage queries and data transfer.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If a new partition is added to an existing table in a distributed file system, then the database functionality is improved, but the database system must be taken offline causing downtime
Solution Approach 1:
The patent creates a new table with the new partition structure before the old table is renamed. This preliminary creation of the new table structure allows the system to have the partition addition ready in advance, so when the rename operation occurs, the new structure is already prepared and no downtime is needed.
Solution Approach 2:
The patent introduces a view as an intermediary layer between the user and the underlying table structure. The view allows queries to be routed to the appropriate table (old or new) based on whether the query includes the new partition column, enabling seamless transitions during the partition addition process without requiring the database to be offline.
2Adaptability or versatility
If data is transferred from the renamed original table to the new table, then the new partition structure is populated, but the process is complex and time-consuming
Solution Approach 1:
The patent creates a new table that is initially empty, then transfers data from the renamed original table to this new table. This copying approach allows the new partition structure to be established first, and data to be migrated in a controlled manner using INSERT statements with SELECT queries, enabling parallel processing and reducing overall transfer time.
Solution Approach 2:
The new table with the complete partition structure is created before data transfer begins. This preliminary setup of the destination table structure allows for optimized data insertion operations and enables the system to prepare storage allocations and indexing structures in advance, reducing the actual data transfer time.
3Adaptability or versatility
If the original table is renamed to facilitate the new partition structure, then the partition addition is enabled, but user queries must be transparently handled during the transition
Solution Approach 1:
The view serves as an intermediary that abstracts the complexity of having two tables (the renamed original table and the new table) from the user. The view's WHERE clause automatically routes queries to the appropriate underlying table based on whether they reference the new partition column, making the complex dual-table structure transparent to users while enabling the partition addition capability.
4Reliability
If atomic operations are used for renaming tables, then the operation is reliable and consistent, but the process requires careful coordination to avoid downtime
Solution Approach 1:
The new table is created with the desired partition structure before the rename operation on the original table. This preliminary creation ensures that if the rename operation fails or needs to be rolled back, the new table structure is already in place and can be cleanly removed, providing reliability while managing the complexity of coordinated operations.
Data Source
AI summary
Systems and methods are described for registering a new column in an existing table on a distributed file system as a partition. In response to receiving a request to add a new column to a selected table, a new table may be created that includes all fields of the selected table, the new column, and a new partition, and the selected table may be renamed within the distributed file system. A view may then be created that points to data in the new table and the renamed selected table, the created view being a virtual table that receives and processes queries. All data from the renamed selected table may be transferred the new table. When the transfer is complete, the new table may be renamed to an original name of the selected table, and any data received after the renaming may be included in the renamed new table.