System and method for evolving index on federated query engine using virtual partition

Abstract

Herein disclosed a system and method to optimize query engines by implementing virtual partitions and local in-partition indexes. The invention provides a fast-adaptable database and distributed query engine system, combined with use of reinforcement learning agent environment to find the best strategies to optimize wide range of queries from multi-users on large, centralized data lake system.

Description

Description Title of Invention SYSTEM AND METHOD FOR EVOLVING INDEX ON FEDERATED QUERY ENGINE USING VIRTUAL PARTITION Technical Field

[0001] The present invention relates generally as a system and method to optimize query engines by implementing virtual partitions and local in-partition indexes. The invention incorporates the use of reinforcement learning agent environment to train and create faster and efficient system for data lake. Therefore, this invention will create a fast-adaptable database and distributed query engine system. Background Art

[0002] Data is growing in size, exceeding 50% growth rate annually. This means conventional data analytics methods used to acquire business insights will become inefficient. Data lake is expected to help businesses identify problems and opportunities based on sources stored in the data lake. However, the rapid volume growth of information in the data lake will affect the performance of querying information from the data lake since the execution time for extracting large data increases along with the growth in data size.

[0003] Partitioning and bucketing were common approaches implemented in database solutions resulting in better query execution time. However, this technique requires manual intervention from the user to reorganize a portion of data into separate partitioned or bucketed columns. Additionally, when there are new streams of data, each partition needs maintenance, which will be costly for the long run.

[0004] Index is an essential component in a database and distributed query engines to accelerate query processing. However, finding the best index candidates to optimize query engine are a complex problem that requires proper domain knowledge in the database and distributed queryengine. The problem of selecting optimal indexes is a complex task that requires a deep understanding of data types and the number of data that were stored. If the chosen indexes were irrelevant, then the overhead computational cost in query execution will increase and cause harm, leading to performance degradation. While prior works on systems and methods that learned which optimal indexes to recommend have been implemented in existing database solutions, however, evolving indexes by continuously creating and maintaining indexes have not been found in distributed query engine solutions.

[0005] There are already several patents and public papers that explore various methods for improving performance using virtual partitioning and indexing. However, none of the inventions discussed methods on how to leverage all of these topics into a system and method to enable a fast and adaptable evolving index on a federated query engine.

[0006] According to the inventors’ knowledge there is no patent or public paper yet on providing a process for creating and implementing virtual partitions and in-partition indexes that are automated using reinforcement learning (RL) agent. However, there are some patents that are related with this invention.

[0007] First Prior Art is Patent US20070067261A1 discloses a system and method to identify index candidates for each partitioning scheme that are provided as a recommendation to user to modify the physical design of the database. While our invention applies index candidates on the fly without manual intervention from the user and without modifying the physical design of the database.

[0008] Second Prior Art is Patent US8543621B2 discloses a system and method to use virtual partitions to use virtual partitions to handle data growth. Partitioning a database virtually enables a more efficient, faster way of partitioning a table without the need to move the actual disk data. While our invention is creating a virtual partition meta-store that contains moreinformation such as Indexes, where the previous store data records only. The goal of this invention is to reduce the need of index maintenance for better efficient in-partition index use.

[0009] Third Prior Art is Patent US20100235348A1 discloses a system and method for a database to provide a partitioned table with a corresponding locally partitioned index. This prior art described the local partition index as a way to ease maintenance and to be used as a primary index. In comparison, our invention uses a local index in a partition as an in-partition index, a way to accelerate frequently used virtual partitions. In-partition index is a partial table index, and by design, the query engine does not need to maintain them for the whole table, as such in the previous patent example when using the local partition index as the primary index.

[0010] Fourth Prior Art is Costa et al, “Evaluating partitioning and bucketing strategies for Hive-based Big Data Warehousing systems”, May 2019. This paper discusses an implementation of data partitioning and bucketing in Hive-based systems, as well as the efficiency of these strategies in query performance. The paper additionally cover the decision- making process for choosing appropriate data modeling strategies. Our invention improves upon prior art by automating the process of selecting data modeling strategies and adding an in-partition index for query acceleration. Additionally, our invention eliminates the need for data reorganization through the creation of an additional layer that applies data partitioning virtually. This approach also enables the insertion of new data while minimizing maintenance costs.

[0011] Fifth Prior Art is Hilprecht et al, “Learning a Partitioning Advisor for Cloud Databases”, June 2020. The research covers a method in which Reinforcement Learning (RL) is used to automate partitioning decision schemes. Our invention extends a new idea by training RL Agent to automate in-partition indexing as a way to accelerate frequently accessed data. With the use of a virtual partition, unlike the previous RL-based indexing method that was expensiveto maintain, our database and evolving index combination by design is easy to maintain and allows horizontal scaling read, assigning multi workers for virtual partition.

[0012] Sixth Prior Art is Licks & Meneguzzi, “Automated Database Indexing using Model- free Reinforcement Learning”, July 2020. It discloses a method that automatically index a database by using reinforcement learning to optimize queries by creating and maintaining indexes on all frequently queried columns throughout the lifetime of a database. In comparison, our invention creates and maintain indexes corresponding to the partition of the indexed data, to reduce execution time when extracting the requested data instead of the whole data.

[0013] Most of the foregoing references above only focus on providing some part of this invention. Whereas, this invention proposes a novel method to facilitate a fast and adaptable evolving index on a federated query engine by creating and implementing virtual partitions and in-partition indexes automated on a RL agent environment. Summary of Invention

[0014] This invention proposes a novel method for the creation and implementation of virtual partitions and in-partition indexes, which enable a fast and adaptable evolving index on a federated query engine. To assist the discovery of the best database design for virtual partition and in-partition index settings, we design an automated process of formulating an RL agent environment and training the agent to explore and find the best parameters based on multi-user query workloads. The purpose of this invention is to create a system and method to process larger amount of data using fast adaptable database and distributed query engine system.

[0015] To perform all of the activities above, the proposed system consists of four main modules. The first module is Virtual Partition module, used to add data partitioning virtually which enable partition-like properties without the need to reorganize data and minimizing maintenance cost. This module will enable better horizontal scaling for the distributed system setup on demanding workload and local in-partition. The second module is In-partition Indexmodule, used to enable specific query acceleration and speed up frequently accessed data on virtual partition without the massive cost of maintaining indexes for all tables. The third module is Virtual Partition Schedule module, used to read and manage the virtual partition, evenly distributing tasks to available workers. The last module is Evolving Index module, used to provide index maintenance service in order to adapt to new data and new queries usage from users.

[0016] This invention has three main features, which are better than conventional scheme, to wit:

[0017] Firstly, the present invention will create and utilize virtual partition, by: - Transforming a large data lake into a manageable local partition iteratively while performing a full table scan when an initial query is executed - Computing and storing virtual partition mark information based on the scanning result - Reducing the number of data sent to the worker by referring to the information from the previous virtual partition layer when a subsequent query is executed

[0018] Secondly, the present invention will create and utilize local in-partition index, by: - Adding from the virtual partition, a new in-partition index to accelerate certain frequently accessed columns leading to reduced user query time - Implementing different index types where each index type is specialized to accelerate a certain query operation clause - Eliminating the need to update the entire table, data reorganization, and index update when adding new data due to in-partition index only within the local virtual partition - Reducing computation cost and execution time for workers by using the in-partition index for faster data lookup

[0019] Thirdly, the present invention will provide and implement evolving index for the creation of the virtual partition and in-partition index automatically based on custom-collected users queries, by training a reinforcement learning agent to: - Determine the optimal column and size on each table for the creation of virtual partitions - Find the best in-partition index placement locations from the created virtual partitions - Handle new data insertion and new queries from users Brief Description of Drawings

[0020] To understand the invention and to see how the present invention can be implemented in practice, some embodiments will be described with reference to the accompanying table and drawings, and wherein:

[0021] FIG.1 is the overall system scope of evolving index on federated query engine.

[0022] FIG.2 is the overview of how the system works when using the virtual partition with in-partition index and scheduler mechanism.

[0023] FIG.3 is the overview of how the system works for querying sorted data.

[0024] FIG.4 is the overview of how the system works for querying unsorted data.

[0025] FIG.5 is the sample use case scenario for Evolving Index Virtual Partition system for real-time fraud detection on customer loyalty platform, illustrating the promotion gift validation process.

[0026] FIG.6 is the example of fetching query for a promotion history from redeem_logs.

[0027] FIG.7 is the illustration of a fraud from a single user that created multiple user_id and fake device_id.

[0028] FIG.8 is the illustration of the discovery of fraudulent users when running data analysis.

[0029] FIG.9 is the illustration of real-time fraud detection system design.

[0030] FIG. 10 is the illustration of how the system can reduce read and write operation on fraud detection service.

[0031] FIG.11 is the illustration of the system implementation for query on demand horizontal scaling for high-demand workload.

[0032] FIG.12 is the illustration of a centralized data lake system in IoT City speed trap.

[0033] FIG.13 is the illustration of system requirement for large data insertion for IoT City speed trap.

[0034] FIG.14 is the illustration of large index maintenance problem.

[0035] FIG. 15 is the illustration of implementation of Evolving Index to improve index maintenance.

[0036] FIG.16 is the example of real-time analysis for road traffic busyness.

[0037] FIG.17 is the example of real-time analysis for traffic density estimation with new data.

[0038] FIG. 18 is the illustration of user interface when a user inserts queries for database optimization.

[0039] FIG. 19 is the illustration of user interface on AI model for database optimization training process.

[0040] FIG.20 is the illustration of user interface for deploying database mapping plan result.

[0041] FIG.21 is the illustration of user interface for database optimization maintenance.

[0042] FIG.22 is the illustration of outline for automated database optimization Software as a Service (SaaS).

[0043] FIG.23 is the flow diagram for virtual partition system.

[0044] FIG.24 is the illustration of virtual partition creation from table data.

[0045] FIG.25 is the illustration of MinMax Index creation for virtual partition.

[0046] FIG. 26 is the illustration of using MinMax Index to select partition to be sent to workers.

[0047] FIG.27 is the flow diagram for In-Partition Index system.

[0048] FIG.28 is the illustration for the first subsequent query for table scan and create hash index as In-partition Index.

[0049] FIG.29 is the illustration for the second subsequent query for table scan using Hash In- Partition Index.

[0050] FIG.30 is the illustration for the third subsequent query for table scan using MinMax Index.

[0051] FIG.31 is the illustration for the third subsequent query for table scan creating B-tree Index as In-partition Index.

[0052] FIG.32 is the illustration for the fourth subsequent query for table scan reusing B-Tree In-Partition Index.

[0053] FIG.33 is the Virtual Partition Scheduler module.

[0054] FIG.34 is the illustration for Virtual Partition and In-Partition Index tasks on existing data.

[0055] FIG.35 is the illustration for index maintenance and new data insertion task.

[0056] FIG. 36 is the process for RL Agent environment, policy, and task training and deployment.

[0057] FIG.37 is the outline for RL Agent training method diagram. Description of Embodiments

[0058] Preferred embodiments and their advantages are best understood by reference to FIGS. 1 through 37. Accordingly, it is to be understood that the embodiments of the invention herein described are merely illustrative of the application of the principles of the invention. Reference herein to details of the illustrated embodiments is not intended to limit the scope of the claims, which themselves recite those features regarded as essential to the invention.

[0059] Referring now to FIG. 1, describes the outline for Evolving Index using Virtual Partition on Federated Query Engine, hereinafter will be referred as Evolving Index and Virtual Partition System, in accordance with the present invention is shown. As shown in FIG.1, the proposed solution introduces four modules, which are the building block of this invention.

[0060] The first module is the Virtual Partition module, to create a virtual layer for data partitioning that allows partition-like properties without the need for data re-organization and minimizing further maintenance costs. The virtual partition utilizes Table Scan, Partition Writer, and Index Writer methods from the initial to the creation stage of the virtual partition layer. The virtual partition layer data are then stored in the metastore along with the indexstore that keeps related partition data, such as partition offset, size, and MinMax index. In the subsequent query, the partition data will allow efficient look-up for the Virtual Partition Scheduler.

[0061] The second module is the In-Partition Index module. The main objective of this module is to accelerate the most frequently accessed part of the table by indexing the table partially. The In-Partition Index Module mechanism relies on Virtual Partition to remove many complications, such as large index size or maintenance costs, that exist traditionally by creating an In-Partition Index only in certain partitions in table. To speed up a query is to select a virtual partition that frequently used with its respective column and mark the designated location. Whenever an initial query access and execute the marked partition location, then on the fly in- partition index, depending on the query clause, will create B-Tree, Hash Index to facilitate the subsequent future query.

[0062] The third module is the Virtual Partition Scheduler module. The main objective of this module is to utilize the new Virtual Partition and In-Partition Index designs horizontally scale the distributed system to workers with ease. The Virtual Partition Scheduler uses the virtual partition layer information to determine whether a partition contains the value needed for queryand sent to the worker. If the query value is not within a virtual partition, then the scheduler skips the partition and reduces the amount of data scan needed. Additionally, since In-Partition Index localized on a partition, this allows the Scheduler to pass a whole partition with its respective In-Partition Index to worker.

[0063] The fourth module is the Evolving Index module. The main objective of Evolving Index Module is to automate the finding of the best parameters for Virtual Partition, create the best In-Partition Indices for given collected queries workload, and automate updates on the In- Partition Index when there are new data. While Virtual Partition and In-Partition Index give a powerful ability to accelerate query by more granular local control of partition acceleration, this increases complexity and makes a difficult optimization problem. Evolving Index Module lays out and formulizes a method based on the Reinforcement Learning (RL) exploration approach to find the most optimal settings.

[0064] Referring now to FIG.2, shows the illustration of how the Evolving Index and Virtual Partition system works with one another. Step 1 is when the system never accessed a table and yet to create the Virtual Partitions. Step 2 is when the creation of virtual partitions begin when the query engine system execute the first query. The scheduler performs and schedules a full table scan by workers part by part while computing a heuristic index to mark and create virtual partitions. Then, depending on how frequently the query engine access a particular data in a virtual partition, the evolving index calculates where the query engine should create the in- partition indices to accelerate query performance. In detail on whether the query engine should create an in-partition index or not depends on RL Agent training results. However, illustrated in FIG. 2, the query engine creates an in-partition index for every partition on the column 'partkey' to provide clarity of the invention. Step 3 is when query engine system execute a subsequent query, with information from the previous virtual partition layer stored in metastore, the scheduler checks if the desired query exists within the virtual partitions, thus only sendingthe related partitions, reducing the number of data sent to the worker. The query engine additionally creates a hash index for the in-partition index even though the previous B-Tree index already exists since the new query operator is an equality clause. Step 4 is an illustrative example of a query that expands on a virtual partition where the query engine has not created an in-partition index yet. In this scenario, the Query Engine only has to update the virtual partition with a new in-partition index until the required query range is satisfied. The main advantage of this invention is to minimize the concern of maintaining index integrity on the query engine when there are new data by using a virtual partition. The in-partition index only exists within the local confines of the virtual partition. Step 5 shows when the table has new data inserted, virtual partitioning eliminates the need to update the entire table, reorganize data, and update indices. Instead, the system only needs to create a new virtual partition. This approach will enable faster performance when dealing with constant, large data inserts while optimizing real-time queries with many users. In conclusion, using the evolving index and virtual partition on a query engine can reduce the amount of data scheduled to send to workers, remember the data that it has scanned before, minimize redundancy, and accelerate frequently visited queries.

[0065] Referring now to FIG. 3, illustrates how evolving index and virtual partition system works on sorted column data with the MinMax index as the heuristic index. Step 1 highlights the Query Engine first initialization of the virtual partition. Step 2 shows when executing the first query, the query engine assigns workers to create the virtual partition by writing each partition data its MinMax index while simultaneously doing a full table scan. Due to the sorted column table data, the query engine does not need to read all the table rows and stop once the query engine found MinMax partitions that are within the desired query range. Similar to the previous section, in the illustration example, the engine additionally creates an in-partition index to accelerate every query. The query engine then creates the B-Tree in-partition indexwithin the respective partition due to the query being a range clause operation. In step 3, the query engine scheduler reads the metastore and finds where the value of the query is located in the virtual partition. However, due to different query operator equal clauses, the assigned worker creates a Hash In-Partition index to accelerate the equality type of query. Step 4 shows an example where the query extends to a never-visited part of the virtual partition. Similar to step 2, due to the already sorted column table data, the full table scan stops when the query range is within the partition. Since the query is a range clause, and the partitions within the 300-1010 range that were previously created by the query engine at step 2 for this query type already exist, the workers now reuse the in-partition indices to quicken the query speed. For the new partitions, workers execute a full table scan, while simultaneously creating the MinMax index with its respective in-partition index. Lastly, step 5 shows if the table inserted new data, the virtual partition prevents the need to update the whole table. Reorganization, updating and indexing is now independent from other virtual partitions.

[0066] Referring now to FIG. 4, illustrates how the system works on unsorted column data. Skipping initialization stage, the first step is when range query clause executed. Due to the column data not sorted, the scheduler at first distribute worker to do a full table scan from start to end creating MinMax partition along the way. If the designated query is within a virtual partition MinMax Index, then the worker will create an addition to the In-Partition Index. In this case, 4 partitions contain the desired queried data from 500 to 1000 and to accelerate future query the worker created in-partition index B-Tree for each. Although at first every partition was scheduled and sent to workers, after subsequent query, the scheduler now can easily find whether a partition need to be processed. In the second step, the query engine execute an equality clause query. Scheduler now can read the virtual partition metastore and find only 1 partition containing the desired query. Then, the partition sent to a worker, minimizing unnecessary read leading to a more efficient faster system. Additionally, after finding thedesired value worker created Hash In-partition Index to accelerate the future query. Step 3 is when the database was added new data and the query engine create a new virtual partition; the MinMax index will only update the affected virtual partition. Therefore, there is no need to update the MinMax value of the whole table after the initial run.

[0067] Referring now to FIG. 5 through FIG. 22, describes the various use cases in which this invention can be implemented. This invention could be used in various situations that requires processing large amount of data using Evolving Index and Virtual Partition system. As described previously, the system will initialize the virtual partition and create the virtual partition when the first query executes, and create in-partition indexes when new data is received. This invention would be able to provide faster query result and reduce costs when using the query engine.

[0068] Referring now to FIG. 5 to FIG. 11, describes the user scenario of using Evolving Index and Virtual Partition system for real-time fraud detection on customer loyalty platform. Customer Loyalty Platform is an application service designed for devoted customers within a region, giving them special daily offers or promotions as gifts. Customer Loyalty Platform expects and programmed for each valid Device and User pair to claim the promotion gift. As shown in FIG.5, users need to log in to their account to get their unique user_id to redeem a gift. The app will then determine the eligibility of the user before redeeming a gift. This validation process specifically checks the user's device_id with an existing database, where each device_id is distinct. Lastly, depending on certain gifts' term and condition a user may be eligible to claim multiple gifts. FIG.6 describes the example of redeem history logs analysis. Based on the data, there are customers who abused and defraud the system by claiming the same gift more than once. On the redeem history logs database, a highly suspected user with the same email claimed the same Promotion / Gift more than once. Depending on the terms given by the gift merchant, usually each User ID and Device pair can only redeem a gift once.Thus, this fraud behavior is violating the terms of service. In this example, it was found that a fraudulent user claims up to hundreds of gift undetected, thus reducing the possibility for authentic users to claim the gift as almost all gifts were redeemed by the fraudster.

[0069] As shown in FIG. 7, describes the two types of exploits that transpire as fraud. User IDs and Device IDs that are supposed to be unique were prone to be defrauded. First, fraudsters exploit bugs that exist on User Account's external social media API. The fraudster was able to generate a different user_id even with the same email and phone number. Second, the fraudster can simulate other Device and causes major security concern as they could use other valid users' Device ID. By emulating other devices, they bypass the single Device ID restriction. However, since the fraudster used the same email and phone numbers, the query engine system can prevent the fraud exploitation immediately by using Evolving Index and Virtual Partition system. The best way to detect fraud is by analyzing the Redeem History Logs of users. FIG. 8 shows the process when data analysts periodically run a query and analyze the data to detect suspected fraud. If a user is found to use the same email or phone number, multiple times outside of the promotion claim limits then the user will be suspected of fraud and suspended from Customer Loyalty Platform service. While it is best to run the analysis as quickly as possible, unfortunately current fraud detection method is inefficient due to the sheer large size of the user base congesting the app log. The current method of detecting by periodic checking only prevents fraud in the past where it already happened. Therefore, real time fraud detection is important to prevent more losses.

[0070] Fraud behavior happens quickly and frequently repeats in a sequential manner within a short time period. Using this information, the search query can be narrowed down to within the last hour and more importantly by narrowing the query this will reduce query lookup time. Ideally, the query engine should execute a fraud query check for every new redemption request to minimize fraudulent gift claims. The main difficulty of detecting fraud is the sheer amountof gift redemption history logs due to users' size and activity. As shown in FIG.9, illustrates the validation system in Validation Service. Fraud Detection should not be slower than device_id, and user_id validation. Thus, database system must have the ability to serve thousands of gift redemption every second, handle other hundreds of different gift promotion that exists simultaneously, fast large amount of new data insert, fast read query look-up time on desired period of time to reduce latency, and easily scalable to adjust with the growth of day-to-day active user base.

[0071] The implementation of Evolving Index and Virtual Partition system satisfies the ideal system design requirements for real-time Fraud Detection. The query engine reduces the need for two main operations in the database, reading and writing. FIG.10 shows the sectors of the database that are active when the system executes queries for fraud detection. First, in the read operation, with MinMax for virtual partition, the query engine reduces data accessed to only fetch redeem log data within the previous hour. Yesterdays or a few hour prior data are unnecessary and not fetched for fraud detection to reduce query time. The parameter of time additionally is dynamic modifiable for any period depending on the use case. Second, in the insertion operation, where the data maintenance was expensive, the implementation of a Virtual Partition reduces the index maintenance cost to only update the local current virtual partition and the query engine do not need to update other virtual partition. With better-reduced read and write operation, our implementations are able to handle other gifts promotion that exists simultaneously, a fast large amount of data insert, and reduced latency for fraud detection service. In the problem of the worst-case scenario, for example, a popular awaited gift redeem event spikes the server traffic load high, resulting in exponential workload increase. In current best practice, unfortunately, a query needs to check the whole redeem log and is done by one worker. Even if partitioned and the partitions were not accelerated and were slow to process. These causes tremendous lag and ruin the end-user experience when the database cannot keepup with crowded servers. In comparison, the virtual partition has the ability to scale horizontally depending on the current workload. If the demand is heavy, the proposed invention is able to distribute virtual partitions to different workers. Additionally, since each virtual partition has its own local in-partition index, as shown in FIG. 11, local on-demand accelerations are now possible. The combined ability to accelerate and distribute partition at the same time makes our patent implementation able to serve thousands of users every second with scale to ensure even with day-to-day users increase the query engine system stays fast, satisfying the ideal requirement.

[0072] Referring now to FIG. 12 to FIG. 17, describes the user scenario of using Evolving Index and Virtual Partition system for Internet of Things (IoT) traffic speed trap. As shown in FIG.12, a traffic speed trap system is set up in a city, consisting of individual speed traps that record activity on a certain city street. During peak traffic hours, from every distributed speed trap, the IoT devices sent new query entries to the centralized data lake every second. From the data lake, users can perform many analytic query tasks, such as speed limit enforcement to traffic business estimations. The core problem in IoT traffic speed trap is the sheer size and frequent insertion of many distributed devices into the centralized data lake. Illustrated in FIG. 13, as the Speed Trap devices increased in size, each second, N new data entries from different locations in the cities added to the data lake as the user does real-time analytics in the background. With the current best practice of using a traditional partitioned database, real-time analytics are not feasible. The reason is to ensure real-time analytics are possible; the query engine need to use index to accelerate queries. The current best practice of using Partitioning by date with indexing is not practical since the new data insertion cost could not keep up with the maintenance of the partition constantly updating the index.

[0073] FIG.14 and FIG.15 illustrate the difference between common index maintenance and the implementation of this proposed invention. FIG.14 shows the index maintenance problemfor constant insertion. In the example, ① shows a B-Tree index with no partition, resulting the size of the B-Tree index growing and will be costly to maintain. To illustrate the cost ineffectiveness of index maintenance, ② shows that for each new data inserted, the index needs to rebalance the tree sequentially. Rebalancing a tree costs numerous value swapping and expanding index size leading to a larger amount of index storage and slow insertion time. If the insertion operation exists occasionally, then the cost might be manageable, however, for large data insert operation that occurs each second the database system is unusable if not using our virtual partition. In this particular case, the Evolving Index and Virtual Partition system will be able to accelerate the database during data insertions. Virtual partition is significantly faster than traditional partitioning, and acceleration by creating additional indices allows it to satisfy real-time user requirements. The evolving index allows for users’ query optimization on the fly, allowing real-time analytics to be possible, and the virtual partition will reduce redundancy. As shown in FIG.15, the virtual partition with an evolving in-partition index will solve the index maintenance problem. First, ① shows the same data as the previous figure with the B-Tree index but is now within virtual partition settings. When new data inserted, as shown in ②, the virtual partition easily maintains the index by only calculating data within the new virtual partition in the new index. The use of virtual partitions makes massive data insertion possible within a reasonable computational budget, and the use of in-partition indexes meets the fast real-time analytics requirement for users. The proposed invention will insert the partition dynamically depending on the data activity. If there is no new data, then there is no need for a new partition. If there is a high amount of new data, then the virtual partition will create the partition accordingly. Lastly, to increase query performance, because the virtual partitions have the same size, the query engine was able to scale horizontally easily by adding new workers and executing the query in parallel. The system can learn and find the most optimal parameter size for the virtual partition using reinforcement learning.

[0074] Additionally, this proposed invention helps with the creation of a program that tracks traffic activity in real-time. Road traffic busyness computed by querying the average moving vehicle speed within a one-hour interval. If the average speed is high, then the related traffic road is clear, while if the average speed is low, then a traffic jam might occur. FIG.16 shows in detail the process of integrating from query request to final application. First, the application sends a query request, finding the speed average of every vehicle that passes certain queried roads within a one-hour interval. Second, the query engine with a virtual partition with a MinMax index optimizes to send partitions that contain vehicle data within one hour. Third, the result returned in a timely fashion in the form of a query table result. Finally, the query results used on the application, where the application denotes and draws from a numerical value of vehicle average speed to a color on a map, such as red when traffic is busy and green when it is clear. FIG.17 shows a case of new data insert into the database. The amount of data varies depending on the time of day. The query engine will create a new virtual partition only when the current partition is full. For example, more partitions are created during peak traffic hours to accommodate reliable, fast query speed since partitions can be read by additional workers in parallel, ensuring fast query speed even in high traffic.

[0075] Referring now to FIG. 18 to FIG. 22, describes the user scenario for Software as a Service (SaaS) for automated database optimization based on multi-user queries. The first process is to collect users’ queries. This proposed invention enables different users in Data Lake to optimize each of their custom query workloads. First, the user logs in to the automated optimization service, selects which database to optimize, and adds their own designated queries. FIG. 18 illustrates a production user selecting the 'tpch_100' example database and adding three queries they wish to optimize for the best customer experience. Additionally, each query has criteria designed to help with the training process. The created criteria are (but are not limited to): priority = "normal, prioritized, important"; computation cost = "light, moderate,expensive"; frequency = "normal, moderate, frequent”, where each criterion represents a value from 1–3. Finally, with the weighted sum model method, its result, weighted loss, used later to help model training. The second process is to do training with users’ queries workload input. After every user has inputted the queries they wish to accelerate, the Data Lake administrator may start the optimization process in the background while the databases served by the service and simultaneously running.

[0076] FIG. 19 shows the outline of the training steps. First, the administrator may see an overview of the whole query from every user, and depending on how many queries the organization has, optimization time may vary. Nevertheless, generally, the longer the Artificial Intelligence (AI) model is trained, the more optimization space it explores, leading to better results. The second subfigure shows a dashboard of the current optimization status; the service will show elapsed time and the current best result of the optimization. The third final subfigure is when the training process is finished and shows the optimization results, and the next step of deploying the training results may recommence. The third process is to deploy the database- mapping plan to the query engine. The deployment page shows the history of every optimization and model training that the service has done on the database. After training is finished, the administrator is able to select the AI training result database mapping plan and assign it to the query engine. Additionally, due to virtual partition module, the query engine can simply redeploy or rearrange virtual partitions in a quick and efficient manner. Therefore, in the future, when the database changes, modifications to the database planning will be a trivial task.

[0077] FIG.20 shows the previously mentioned steps: the first user sees the training result and chooses to deploy the mapping; the second subfigure shows the query engine currently creating related virtual partitions and then in-partition indexes; and lastly, the database mapping is successfully applied. The last process is database optimization maintenance. Changes in thedatabase, such as new data or a new business logic, and the need to analyze different queries are inevitable. In order to handle changes, whether the addition or removal of queries or new data inserts, our invention provides a way to maintain existing optimization. By fine-tuning the pre-existing AI model, it greatly reduces the optimization cost since even when there are new data or queries, the underlying dynamics, such as data type, optimal columns, partition, and in- partition index, are still very much alike. As shown in FIG.21, the first subfigure illustrates when user adds or removes their previous queries at any time. Once changes for all users are final, the administrator may start new training. In the second subfigure shows, if there has been previous optimization on the selected database, the bottom of the page will show an additional option to select an already pre-trained model as the starting point of training. The service reduced the optimization time for the AI model training, and similar to the previous step, once finished, the query engine can redeploy the fine-tuned, replacing the previous outdated database plan.

[0078] FIG. 22 illustrates the summary of all processes involved. The SaaS feature is categorized into collecting the workload for each data lake user, training AI model to optimize the best database design plan, deploying the database plan to the query engine, and maintain changes by fine-tuning a pre-trained model in case there are new data or a change in business logic.

[0079] Referring now to FIG. 23, describes the Virtual Partition module. Virtual Partition creates a virtual layer for data partitioning that allows partition-like properties without the need for data re-organization. FIG 23 shows the system flow outline of the virtual partition initialization algorithm. The first step is when the user submits a query request to the system. This first query initiates the creation of a virtual partition for faster subsequent queries. The partition count, size, and indexed column parameters can be obtained manually or using the automated database optimization process. If a partition is yet to exist, the scheduler starts toassign workers to create the virtual partitions needed to complete the query. The worker creates the partition by first finding the value for the query with a linear scan while simultaneously computing and storing the MinMax index for the new partition. If the virtual partitions already exist, the query engine on subsequent queries then verify the partitions, making sure the existing partitions cover all of the necessary partitions and up-to-date. If there is new data inserted into the table, the query engine will update and maintain the affected partition. If the partition is already full, the query engine will dynamically create a new partition sequentially to handle the new data growth. After ensuring existing partitions, the scheduler then inspects whether the partition contains the desired query data by using the MinMax index to find if the value exists within the MinMax range. If the value is out of the partition MinMax range, the scheduler skips and does not load the partition. When the queried value falls within the MinMax range of the partition, the scheduler then assigns workers to fetch the data. Following filtering the desired partitions, the query engine scheduler then distributes jobs to all existing workers with each worker assigned to only one partition for every work iteration, and the distribution of workers ends after all the partitions finish iterated. To sum up, the query engine system utilizes the Virtual Partition Layer to simulate data partitioning in an otherwise unpartitioned table. By simulating a data partition, the query engine now has two main benefits. First, it improves query performance by only processing the necessary data partition instead of the whole table. Second, it avoids the modification of the physical design of the data since the query engine is not creating a physical data partition.

[0080] Referring now to FIG. 24 to FIG. 26, illustrates the example of how the Virtual Partition module works. The partition process begins with the initial query statement. First, the query engine splits the table equally depending on the acquired parameters of the partition count, size, and selected column for the MinMax index. In FIG.24, the table with each element within as the selected column value, the virtual partition module equally divides the table into16 partitions. FIG. 25 shows when the query engine scanned all data from the table in the respective column in order to set the MinMax Index for each partition. The scheduler is now able to quicken subsequent queries by determining whether a worker needs to read or skipped particular partition by their MinMax index. An example of such a case in FIG.26 is where the user wishes to query on id = 1. During query execution, the scheduler reads each partition's MinMax index to filter out partitions that do not hold the value in the predicate WHERE clause. In the figure example, only partitions #0 and #3 are within the range of id=1 and the scheduler only need to assign both partitions to the worker.

[0081] Referring now to FIG.27, describes the In-Partition Index module. The goal of the in- partition index is to accelerate the most frequently accessed part of the table by indexing certain partition in the table. As shown in FIG.27, after receiving a job from the scheduler, the worker checks for an in-partition index to accelerate the work. There are two types of indexes used, each depending on the query operation type. For illustration purposes clauses with a range operator, the query engine uses the B-tree index, and for equality operator clauses, the hash index. However, other types of index, such as bitmaps, may also be used. In the first case, when the worker finds an in-partition index, the worker first must verify whether the index is the correct clause for the current query. If the index matches the query operator clause, the worker can then simply use the existing in-partition index and quickly find the queried value. However, if there is a clause mismatch, the worker then need to fetches the queried data while creates the correct index for the clause. In the second case, when the worker checks that no in-partition index exists and there is no need for them on the current queried partition column, the worker will simply fetches the data by linear filter row on the partition and return the queried value normally. However, if on a particular partition column there should be an in-partition index, then additionally, while finding the value, the worker creates an in-partition index according to the current query clause type for a faster subsequent query. The user of the query engine canmanually add the in-partition index by themselves, or another alternative is to use the database plan acquired from the reinforcement learning (RL) agent model. To train the RL agent in an in-partition index environment, the query engine provides the model with query statistics for rewards such as the Index creation time, Linear filter time, and Index lookup time. With this information, the RL agent trained to make optimal decisions on the best parameters for the query engine. The RL agent provides parameters such as partition count, size, column for the virtual partition, and database vector-mapping plan for the in-partition index.

[0081] Referring now to FIG. 28 to FIG. 32, illustrates how the In-Partition Index module works. For clarity, the worker creates an in-partition index for every partition on the column 'id' in the following illustration. In a query statement with an equality comparison, a hash index is the appropriate index type to use. FIG. 28 illustrates the creation of a hash index from a query. Continuing with the previous example on virtual partitions, workers receive jobs to query the #0 and #3 partitions, and the workers create hash indexes on these partitions. During this process, the worker performs a full data lookup in the partition to find the result expected by the query statement while simultaneously creating a hash index for all data stored in the partition. This approach ensures that subsequent query statements with a similar search condition use the created hash index and avoid the redundancy of performing a full data lookup again. Each #0 and #3 partition now has a hash index, which holds the key-value pair between the lookup value and its row position in the partition.

[0082] Next, for any equality clause query for the two partitions, the worker now uses the created hash index. Another example, shown in FIG. 29, is where the user wishes to query id=2. The scheduler filters the partitions #0 and #3 again with a virtual partition, and rather than perform a full data lookup, the assigned worker now skips this process and immediately uses the hash index. A key-value pair between the value and row position in the hash indexinstantly retrieves the data. In this particular case, the hash index quickly returns none for partition #0 and returns row 3 for partition #3.

[0083] In the case of a query statement with a range clause, a B-tree index is the appropriate index type to use. FIG.30 illustrates the use of a B-tree index in the query engine. When the query statement has predicates between upper and lower boundaries, the system does the following: ①. The scheduler determines which partitions to process for each predicate using the MinMax Index; ②. Scheduler filters out partitions that are outside the boundary of the searched value; and ③. The scheduler finds the partitions that intersect. In this illustration, the filtered results of the intersection between id greater than or equal to 100 and id less than 200 are the partitions #0, #1, #2, and #3. The scheduler then sends the partitions equally to the available workers.

[0084] The next step, illustrated in FIG.31, ④. The workers assigned to the filtered partitions to determine whether a particular partition already has an index that is relevant to the predicates in the WHERE clause. ⑤. The assigned worker performs a full data lookup in the partition to find the query statement result and simultaneously creates a B-tree index for all data stored in the related partitions. In the illustrated example, no partitions have the B-tree index yet, so the workers create the B-tree index in the partitions for future queries.

[0085] FIG.32 then shows an example of workers using the B-tree index.On subsequent query statements for a range query to a particular partition that already has a B-tree Index created, the system will skip performing a full data lookup in the partition and immediately use the B-tree Index. In the example case, the user wishes to query where id is within a range of greater than or equal to 50 and less than 150. The intersecting partitions sent to workers by the scheduler are #0, #1, #2, #3, and #4. In the previous example, however, the workers had already created B-tree indexes for partitions #0, #1, #2, and #3. Now workers on the assigned partition instantly retrieve the desired range data with the B-tree index. The worker only needs to do afull data lookup and create a B-tree index for the #4 partition for better subsequent query performance.

[0086] Referring now to FIG.33, describes the Scheduler module. The Query Engine's Virtual Partition Scheduler module handles the coordination and distribution of jobs and workers. Part ① shows an example of the Virtual Partition Index Reader reading a fully partitioned table. Inside virtual partition layer, there exist MinMax index values and in-partition indexes. The scheduler's task is to check with Partition Index Reader whether the desired query is within a virtual partition. In the illustration, the user wishes to query lineitem on column 'partkey' with a range greater than 695 and less than 1000. The Partition Index Reader then finds the desired query that are partitions #1, #2, #4, #5, and #8. The bolded Min / Max Index value indicates that the desired query is within that particular partition. Then, the gray table lineitem area shows the selected distributed partitions by scheduler to available workers and reducing the amount of table data scanned by the query engine.

[0087] In part ②, after the Virtual Partition Index Reader marks which virtual partition needs to be further processed, the Virtual Partition Scheduler distributes the virtual partition to an available worker. This ensures that workers can process table data faster in parallel. Depending on the number of workers, the scheduler starts iteratively sending the partition with its respective in-partition index information to available workers. Virtual partitions additionally are easily scalable horizontally with the scheduler module. This scalability assures that the optimized database with the necessary workers is able to execute queries in real-time even with a large amount of data and traffic.

[0088] Referring now to FIG.34 and FIG.35, describes the Reinforcement Learning model for Evolving Index module. To achieve faster read query time, the main optimization idea is to differentiate existing data states within a database into three distinct states, and depending on how frequently certain data is accessed by workers, more investment is put into acceleratingthe data elements to the next state where the investment leads to faster read query action. FIG. 34 outlines the existing data read query optimization problem formally in reinforcement learning settings. The Markov Decision Process (MDP) illustrates singular element states and actions, where the environment is the collection of all elements within a database, which consists of tables with their respective rows and columns. For optimizing read queries, there are two distinct tasks to optimize, which are finding the optimal policy ^^1parameters for virtual partition settings, and finding the optimal policy ^^2for in-partition index within the partitioned database. In order to find the optimal policy for a RL agent in a quick manner and with fast convergence, the agent is first trained to find the best policy ^^1for the best virtual partition parameter and then, the best policy ^^2for the in-partition index on which partitions workers frequently fetch and should be indexed.

[0089] Let ^^^^, ^^, ^^be the data state of an element accessed within the database, where ^^, ^^, ^^ be the data location depending on table, column, and row, respectively (note that ^^, ^^ size differs between tables ^^). Each element in the database table has a state as follows: ^^^^, Indicates the initial state, where data kept as is, without any acceleration. ^^^^ ^^, Indicates the current data state is in a virtual partition. ^^^^ ^^, Indicates the current data state is in an in-partition index.

[0090] In each state, there are two types of valid actions that can be taken by the query engine, with the exception of ^^^^ ^^, as the final state, which are: ^^^^, Read query action, where the query engine executes a read query on the database and depending on the state of the data and the query operations, the resulting speed may vary. ^^^^, Promote or accelerate database action where the query engine move the data state in order to accelerate frequently accessed data for better performance.

[0091] The reward function after an executed action is dependent on current data states and the workload queries in environment training. In order to reward RL agents, we value how particular actions have effects over the initial baseline state. An example of such reward that commonly used and satisfied patent condition is to normalize reward value based on the initial ^^ ^^ ^^ ^^( ^^^^), which is the normal cost when query engine accessed data without acceleration. The main idea is that when the read ^^ ^^ ^^ ^^( ^^^^ ^^) or ^^ ^^ ^^ ^^( ^^^^ ^^) is lower (therefore runtime and query speed are faster), the bigger the reward with respect to the initial ^^ ^^ ^^ ^^( ^^^^). Thus, we define the reward function as follows:Where the following expected to be true,

[0092] So when the action of reading a query is executed, with optimal policy, the cost of reading data within virtual partition are expected to be must faster than without, and similar with in-partition index, the runtime are also expected to be even faster compared to virtual partition. However, in order to accelerate and move from one state to the next the query engine need an initial investment cost of negative reward − ^^ ^^ ^^ ^^_ ^^ ^^ ^^ ^^(^^). In other words, the reward value when the query engine decide to promotes data are:

[0093] Thus, our reinforcement learning agent's goal is to train and learn the best optimal policy ^^∗in order to select the best set of actions, maximizing reward on the finite workload query from the environment ^^ = { ^^1, … , ^^^^}.

[0094] The arduous part, nonetheless, is that each data state cannot act independently; i.e., to move data from one state to another, the query engine needs to partition a whole table at once, or an in-partition index can only be applied on every row of a virtual partition column. Additionally, read speed is also dependent on query selection; therefore, RL Agent's best policy is also constrained within valid database operation dynamics enforced at training.

[0095] After finding the best policy for partitioning and in-partition indexing ^^1& ^^2, the next step is to train the reinforcement-learning agent to maintain an existing, optimized database to handle a new task where new changes might occur such as new data insert or changes in queries workload. FIG.35 shows the new MDP states and actions pair for the new inserted data element. The main task of the RL agent now is to learn the best policy of ^^3to maintain the database changes by adding new in-partition indexes based on the new query workload or new data insertion.

[0096] For data insertion query tasks, the initial state ^^^^is when the query engine receives an insert query for a new data element. The write query action ^^^^indicates a data element sequentially inserted into the database, where the query engine will immediately write into a virtual partitioned data block with its existing additional in-partition index. For promotion action reward and changes in query workload, the ^^3policy uses the same states and actions from previous read optimization policies. Lastly, the new reward values for write states and actions are

[0097] The RL agent training is to find optimal policy ^^3to decide when the best time is to promote the new in-partition index. The agent needs to consider that if the query engine applies an in-partition index to either an active or filled virtual partition, since subsequent insertionwith negative rewards will cost more in an active partition, in order to maintain the in-partition index. Too many in-partition indexes within virtual partition are not only costly but may also harm future performance.

[0098] Referring now to FIG.36, describes the overall policy training of the RL agents in their respective environments. This invention includes four methods, first is for RL agents to find optimal virtual partition parameters on an already existing database. Second, given an already virtually partitioned database already exist, RL agents will find the optimal parameters for the in-partition index (partition table, column) to be additionally indexed. Third, given an already optimized mapping of an existing database, RL agents will find the optimal policy for maintaining the virtual partition and in-partition index by training a given set of new data insertion queries. Lastly, RL agents will deploy the trained model with the training result plan to production. Once the RL agent finishes training with a satisfying result, the Query Engine will then deploy the necessary virtual partitions and in-partition indexes based on the RL agent's database planning results.

[0099] Referring now to FIG. 37, describes the training method outline for RL agents. The provided RL agent model follows their respective environment model description. The environment dynamics and relationships between data in row, column, and table are complex. The query engine RL agent cannot simply access or optimize a singular data ^^^^, ^^, ^^when read query executed, but a collection of data such as creating a virtual partition. Same as data insertion, the cost of index maintenance, future query usage dynamics are also complex due to the cost increase proportional along with the amount of existing data within the partition that need to be re-arrange, sorted, to insert new data. Thus in order to find the best policy for these complex tasks, one may approximate best solutions with neural network based model-free value-based RL agent.

[0100] For every policy, the RL agent network outputs two values. The first output value represents whether the database should modify the database scheme design to improve future queries or execute and finish the query workload. In the first case of continuing the query workload, we pop the current query from the workload and benchmark its runtime as the immediate reward feedback to the model. However, if the second case of modify database output is true, from the second output value, which contains the normalized vector location of the database, the RL agent creates a modification query for initial investment on accelerating future query. Depending on which current policy the agent is training, the vector value contains different parameter information.

[0101] Specifically, in Policy ^^1vector value contains information on virtual partition parameters, which are Partition Table Location, Column Location and Partition Size. Meanwhile, the value in policy ^^2and ^^3contains information on location of in-partition, which are Table Location, Column Location, and Partition Location.

[0102] If the RL agent networks create a valid action based, the output vector de-normalized, and the query engine will run related table modification queries based on the current policy. The agent then saved the normalized second output vectors to the database mapping vector history, where the query engine could look up the agent mapping plan result later in the deployment stage. Similar to the read action, when the query engine execute the query, its runtime is benchmarked and used as the immediate reward for the promote action state.

[0103] The neural network-based RL agent is trained to approximate the following formal function: ^^ ^^ ^^ ^^ ^^ × ^^ ^^ ^^ ^^ ^^ ^^ → ℝ that maps a state-action pair to a real value. The agent follows the Bellman equation and Q- learning method, where ^^^^represents the policy of task ^^, ^^ is a discount reward factor within the range of [0,1], and ( ^^, ^^) is the state-action pair with their respective reward ^^:

[0104] To train neural network ^^^^, where ^^ is the iteration step, is to use stochastic gradient descent method with the loss function as following:

[0105] FIG. 37 shows the outline of the training method. The loss function is a specific temporal difference algorithm over the entire finite workload query set. Additionally, training methods create termination states to ensure the RL agent state, transition, and action are valid. When RL agent neural networks take invalid actions, such as repeating actions while training, the agent will terminate, penalize, and restart. Other desirable termination state is when all queries within the workload environment finish execution without prior invalid action.

[0106] Preprocess data input are also needed while training especially in high amount of data environment. Sub sampling helps reduce computational requirements due to utilizing every data point in the database is too costly and impractical. Next, the database sampled states representation transformed and normalized into tensor vectors to accommodate the varying columns and rows size in each table. This normalization also facilitates better and faster training for neural networks. During the later stage of output, the data will be de-normalized.

Claims

Claims 1. A Federated Query Engine system and method to accelerate users' on-demand queries on big data environments with fast performance. The engine is adaptable to changes such as newly inserted data and new queries from users that they wish to optimize. The engine also uses Reinforcement-learning agents to learn the best parameters for a database. This invention will speed up queries while having the flexibility to accommodate changes in the future, further enhancing users’ business workflow from a given database and queries. The components involved inside the said system are: Virtual Partition to add data partitioning virtually and enable partition-like properties without the actual cost of physical data reorganization, thus minimizing maintenance costs. Virtual Partition also allows local In-partition index to the database to minimize reading the whole partition for specific searches; In-partition Index to enable specific query acceleration and speed up frequently accessed data within a virtual partition by using index locally in certain partition, removing the constraint of full table indexing; Virtual Partition Scheduler to utilize the virtual partition with respective in-partition index by filtering only needed partition for query, reducing overall read operation on database and managing task distribution to available workers; Evolving Index to provide automation on optimization for partition parameters and database design. Evolving Index also maintain services to adapt to new data and new query pattern from users. This automated mechanism are using reinforcement learning based.

2. The process component of Claim 1, wherein the system will apply Virtual Partition to already existing data, comprises of Virtual Partition Layer abstraction with the utilization of metastore to save partitions information including partition number, physical addresslocation, size, and index which improve data retrieval by informing scheduler whether desired queried data exists within the partition. Deployment of virtual partition comprises of the following steps: On the first query execution, compute the Virtual Partitions mapping split with the predetermined size and count from received parameters; After the system defines virtual partitions, while executing the initial query, for each partition, the system simultaneously creates and computes an index on a certain column; Then, for subsequent queries, the system will check if the partition needs an update if there is newly inserted data; Finally, the scheduler can reduce read redundancy by fetching the created virtual partition index to filter partitions within our desired range.

3. The process component of Claim 1, wherein the system will apply an In-Partition Index to certain partitions in a virtually partitioned table. In-Partition Index will speed up frequently accessed data on certain virtual partitions without the impractical cost of maintaining indexes for the whole table. The making of In-Partition Index comprises the following steps: Look up if the in-partition index on the queried column exists on the virtual partition layer and is suitable for the query clause, if not the system will use a different type of index; If the existing in-partition index is available and suitable for the query clause, then the assigned partition worker uses the in-partition index to speed up query processes; If the in-partition index is available but not suitable for the current query clause, then the worker creates a new in-partition index for the correct query clause; When the queried partition column does not have an in-partition index, the RL agent will consider whether one should exist based on the database design;If the RL agent decide an in-partition index should exist, the worker process current query data while creating and computing a new in-partition index for the current query clause for subsequent queries; If the RL agent decide an in-partition index is not within the database design, then the worker will scan the desired query data normally.

4. The process component of Claim 1, wherein the system will apply the Virtual Partition Scheduler to utilize the already partitioned and indexed database, filtering them into necessary partitions only for the query to distribute to workers. The scheduler processes comprise of: The scheduler reads the virtual partition index to determine whether a partition contains the data, the user wishes to query; The scheduler filters for all partitions in the table and only distributes the necessary partitions with their respective in-partition index information to available workers; The addition of virtual partitions with a partially indexed table allows the scheduler to easily accelerate and easily scale horizontally to workers.

5. The process component of Claim 1, wherein the system has the ability of Evolving Index to a given database and query collection. Evolving Index provides increment speed up for certain patterns of queries. Evolving Index also provides future index maintenance to adapt to new data or user query patterns. The Evolving Index comprises reinforcement learning- based optimization on: Virtual Partition, to find the best parameters, the size and count of the partitions, and which column to use as the index for each table in the database based on the queries the user desired to optimize;In-Partition Index, to find the best partitions and columns used to index for each table on the virtual partitioned database from the previous optimization process; Maintain changes, to improve the system and maintained the system when users add new read and write queries.

6. The optimization method of Claim 5, wherein the method will apply optimization for Virtual Partition parameter selection defined formally by the Markov Decision Processes (MDP) framework, comprises of the following steps: Categorize elements in the database into two states, un-partitioned and partitioned data. Train RL agents to learn and find the best policy by maximizing rewards from the training environment. The reward received is from the agent's action on promoting database tables using virtual partitions, which resulted in an initial cost of negative rewards. Another reward is from the agent's action on reading a query from the query collection, where if the agent has previously promoted or partitioned a table, the query speed will be faster and the agent receive a reward based on the improvement over baseline.

7. The optimization method of Claim 5, wherein the method will apply optimization for In- Partition Index database design defined formally by MDP, comprises of the following steps: Categorize elements in the partitioned database into two states, partitioned and additional partially indexed table. Train RL agents to learn and find the best policy by maximizing rewards from the training environment. The reward received is from the agent's action of promoting columns within a partition using the in-partition index, which resulted in an initial cost of negative rewards.Another reward is from the agent's action of reading a query from the user query collection, where once promoted to an in-partition index, the query speed will be faster than only partitioned, and the agent receives a reward based on the improvement over the baseline.

8. The optimization method of Claim 5, wherein the method will apply optimization for new changes on the database design defined formally by MDP, comprises of the following steps: To handle new changes by new data insert, the agent's new action handle writing insert query, creating a new element into an existing partitioned state or an in-partition index state; In other cases of handling new changes on a new query collection to optimize, the agent uses the action of reading a query on the new query collection. Train RL agents to learn and find the best policy by maximizing rewards from the training environment. The reward received is from the agent's action of reading the new query collection, where with a new in-partition index, the query is faster, thus leading to a larger reward. Another reward is from the agent's action of promoting a certain column adding in- partition index within a partition with an initial cost of negative rewards. The last reward is from the agent's action of inserting data, and dependent on the current insert partition state, the agent need the costs of negative rewards to update and maintain the virtual partition with its respective in-partition index on the newly inserted row.

Images