Computational Storage Hash Join for Database Analytics
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current hash JOIN algorithms in database and data analytics require significant storage I/O traffic and CPU usage due to the need to load entire tables into host memory for processing, which is inefficient and resource-intensive.
Innovation Solution
Utilizing computational storage devices to store hash tables or Bloom filters in memory, allowing for in-device processing of hash JOIN operations, reducing the need for host CPU involvement and minimizing I/O traffic by filtering out non-matching rows within the device before sending relevant data to the host system.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If the entire table S is loaded into host memory and scanned by CPU, then the hash JOIN operation can be completed, but storage I/O traffic and CPU usage become linearly proportional to the size of table S
Solution Approach 1:
The patent segments the hash JOIN operation into two parts: (1) hash table construction for the smaller table R is performed in advance and stored in computational storage device, and (2) only the necessary look-up operations for table S are performed in the computational storage device. This segmentation allows avoiding loading the entire table S into host memory, thus reducing I/O traffic and CPU usage while maintaining JOIN operation correctness.
Solution Approach 2:
The patent applies preliminary action by pre-building the hash table for the smaller table R and storing it in the computational storage device before the actual hash JOIN operation. This preliminary preparation enables the computational storage device to perform efficient look-up operations directly on table S without requiring host CPU involvement, thereby reducing both I/O traffic and CPU usage during the main JOIN process.
2Loss of energy
If computational storage device is used to perform hash JOIN operations, then I/O traffic and CPU usage are reduced, but additional computational resources are required in the storage device
Solution Approach 1:
The patent enables the computational storage device to serve itself by performing hash table construction and look-up operations autonomously without requiring host CPU intervention. The computational storage device uses its own integrated computation resources to build hash tables from table R and perform look-ups for table S, thus reducing the need for host system resources while minimizing I/O traffic.
Solution Approach 2:
The patent merges the computation and storage functions into a single computational storage device. By combining the hash table construction and look-up operations within the same device that stores the data, the system eliminates the need for separate host CPU processing and reduces data movement between storage and computation resources, thereby reducing I/O traffic while utilizing integrated computational resources.
Data Source
AI summary
A method according to embodiments includes: storing an entire hash table of a table R in memory of a computational storage device; storing a second table S in storage media of the computational storage device, the table R being smaller than the table S, wherein the hash JOIN operation is directed to combining a cs-th column in the table S and a cr-th column in the table R; wherein, for each row of the table S, the computational storage device configured to perform a method, including: applying a hash function to a value of the cs-th column to provide a hash result; looking up the hash result in the hash table stored in the memory of the computational storage device; and if the hash result is found, sending the row of the table S and a corresponding row index of the table R to the host computing system.