A system and method for transferring data from a data warehouse to a data lake.

Abstract

This invention relates to a system for transferring data from a data warehouse to a data lake, comprising a data warehouse and a data lake. The data warehouse includes a custom algorithm server and an intelligent preloading engine. The custom algorithm server calculates the preloading method. The intelligent preloading engine preloads data from the data warehouse server into the data lake according to the preloading method calculated by the custom algorithm server and records log information for each call to the data warehouse. The data lake includes a data warehouse preloading data service and a data warehouse indexing service. The data warehouse preloading data service stores data preloaded from the data warehouse server into the data lake. The data warehouse indexing service provides index queries and retrieves data from the data warehouse preloading service or the data warehouse server for transmission to the data lake server. This invention uses intelligent preloading to preload data, enabling rapid data transmission.

Description

Technical Field

[0001] This invention relates to the field of data transmission technology, and specifically to a system and method for transmitting data from a data warehouse to a data lake. Background Technology

[0002] A data lake is a storage facility for all types of raw, unprocessed data, including structured, semi-structured, and unstructured data of any size, requiring convenient access for all users. A data warehouse is a suitable database choice for general analytics, including reporting, dashboards, interactive analytics, and other high-performance analytics.

[0003] With the development of the information age, many big data organizations use both data warehouse and data lake technologies. However, sometimes data lake users want to access and synchronize data from the data warehouse, but with hundreds of billions of data points in the data warehouse, it's impractical to synchronize everything to the data lake. Furthermore, due to historical reasons, the original data warehouse and data lake were built separately, rather than adopting a unified lake-warehouse approach. In this situation, the data synchronization and transmission process becomes a bottleneck in the use of big data. Summary of the Invention

[0004] To address the problems existing in the prior art, the purpose of this invention is to provide a method for transmitting data from a data warehouse to a data lake, which uses intelligent preloading to load data in advance to achieve rapid data transmission.

[0005] To achieve the above objectives, the technical solution adopted by the present invention is as follows:

[0006] A system for transmitting data from a data warehouse to a data lake includes a data warehouse and a data lake. The data warehouse includes a data warehouse server, and the data lake includes a data lake server. Both the data warehouse server and the data lake server are used to store data.

[0007] The data warehouse also includes a custom algorithm server and an intelligent preloading engine. The custom algorithm server is used to calculate the preloading method. The intelligent preloading engine preloads the data in the data warehouse server into the data lake according to the preloading method calculated by the custom algorithm server, and records the log information of each call to the data warehouse.

[0008] The data lake also includes a data warehouse preloading data service and a data warehouse indexing service. The data warehouse preloading data service is used to store data preloaded from the data warehouse server into the data lake. The data warehouse indexing service is used to provide index queries and retrieve data from the data warehouse preloading service or the data warehouse server to transmit to the data lake server. Each index in the data warehouse indexing service points to the actual data in the data warehouse preloading data service or the actual data in the data warehouse server.

[0009] The custom algorithm server calculation preloading method is as follows:

[0010] Retrieve log information recorded by the intelligent preloading engine, and query which indexes are called most frequently in the corresponding time period based on the current system time; then extract the frequently called data and process it as follows:

[0011] When the extracted data is being preloaded for the first time, or when the extracted data is called no more than N times, the extracted data is directly preloaded into the data warehouse preloading service of the data lake, and then the data is cleared.

[0012] When the extracted data is not being preloaded for the first time and is called more than N times, the index weight of the data is adjusted according to its hit rate, and a weight ranking list is generated. Then, it is determined whether the extracted data is in the top X of the ranking list. If it is in the top X of the ranking list, the extracted data is preloaded into the data warehouse preloading service of the data lake and then the data is cleared. If it is outside the top X of the ranking list, the data is cleared directly.

[0013] Data cleanup process: Clear data from the data warehouse preload service that is outside the top X in the leaderboard.

[0014] The data warehouse also includes a machine learning model server. This machine learning model server trains a model based on the data hits in the data warehouse preload data service of the data lake, combined with time periods, and simulates the calculation of the success rate. When the model is perfected, it simulates the calculation of the predicted hit data in the background. When the prediction success rate reaches M% or more, the intelligent preloading engine preloads the data in the data warehouse server into the data lake based on the prediction results of the machine learning model server.

[0015] A method for transferring data from a data warehouse to a data lake, the method being implemented using the aforementioned system, the method comprising the following steps:

[0016] Step 1: The custom algorithm server periodically calculates the preloading method and guides the intelligent preloading engine to preload the data from the data warehouse server into the data warehouse preloading data service according to the preloading method calculated by the custom algorithm server.

[0017] Step 2: When the caller uses data lake data and needs data warehouse data, it uses the data warehouse index service to query the required data index. The data warehouse index service first queries and retrieves data from the data warehouse preloaded data service. If the data warehouse preloaded data service does not have the data, it queries and retrieves the data from the data warehouse server through the intelligent preload engine. The data retrieved from the data warehouse server will be cached in the data warehouse preloaded data service at the same time.

[0018] The specific preloading method for the custom algorithm server calculation is as follows:

[0019] Retrieve log information recorded by the intelligent preloading engine, and query which indexes are called most frequently in the corresponding time period based on the current system time; then extract the frequently called data and process it as follows:

[0020] When the extracted data is being preloaded for the first time, or when the extracted data is called no more than N times, the extracted data is directly preloaded into the data warehouse preloading service of the data lake, and then the data is cleared.

[0021] When the extracted data is not being preloaded for the first time and is called more than N times, the index weight of the data is adjusted according to its hit rate, and a weight ranking list is generated. Then, it is determined whether the extracted data is in the top X of the ranking list. If it is in the top X of the ranking list, the extracted data is preloaded into the data warehouse preloading service of the data lake and then the data is cleared. If it is outside the top X of the ranking list, the data is cleared directly.

[0022] Data cleanup process: Clear data from the data warehouse preload service that is outside the top X in the leaderboard.

[0023] The data warehouse also has a machine learning model server. During the execution of steps 1 and 2, the machine learning model server trains the model based on the data hit in the data warehouse preload data service of the data lake and the time period, and simulates the calculation of the success rate. When the model is perfected, it simulates the calculation of the predicted hit data in the background. When the prediction success rate reaches M% or more, the intelligent preloading engine preloads the data in the data warehouse server into the data lake based on the prediction results of the machine learning model server.

[0024] After adopting the above scheme, the present invention uses a custom algorithm to calculate which data is likely to be preloaded by the data index and the actual data associated with the index, thereby preloading it into the data lake cache in advance to achieve fast transmission.

[0025] In addition, the present invention also sets up a machine learning model server to learn data and predict the probability of data being loaded, thereby increasing the weight of frequently used data transmissions and preloading them into the data lake cache to achieve fast transmission. Attached Figure Description

[0026] Figure 1 This is a schematic diagram of the principle of the present invention;

[0027] Figure 2 This is a flowchart of the custom server loading method of the present invention. Detailed Implementation

[0028] like Figure 1 As shown, the present invention discloses a system for transmitting data from a data warehouse to a data lake, which includes a data warehouse and a data lake.

[0029] The data warehouse consists of a data warehouse server, a custom algorithm server, and an intelligent preloading engine. The data warehouse server stores data; the custom algorithm server calculates the preloading method; and the intelligent preloading engine preloads the data from the data warehouse server into the data lake according to the preloading method calculated by the custom algorithm server, and records log information for each call to the data warehouse, including what data, indexes, and time period were called.

[0030] The data lake consists of a data warehouse preload data service, a data warehouse index service, and a data lake server. The data lake server stores data, and the data warehouse preload data service stores data preloaded from the data warehouse server into the data lake. The data warehouse index service provides index queries and retrieves data from the data warehouse preload service or the data warehouse server to be transmitted to the data lake server. Each index in the data warehouse index service points to the actual data in the data warehouse preload data service or the actual data in the data warehouse server.

[0031] like Figure 2 As shown, the specific preloading method for the custom algorithm server calculation is as follows:

[0032] Retrieve log information recorded by the intelligent preloading engine, and query which indexes are called most frequently in the corresponding time period based on the current system time; then extract the frequently called data and process it as follows:

[0033] When the extracted data is being preloaded for the first time, or when the extracted data is called no more than N times (N is 5 in this embodiment), the extracted data is directly preloaded into the data warehouse preloading service of the data lake, and then the data is cleared.

[0034] When the extracted data is not being preloaded for the first time and is called more than N times, the index weight of the data is adjusted according to its hit rate, and a weight ranking list is generated. Then, it is determined whether the extracted data is in the top X of the ranking list. If it is in the top X of the ranking list, the extracted data is preloaded into the data warehouse preloading service of the data lake and then the data is cleared. If it is outside the top X of the ranking list, the data is cleared directly.

[0035] Data cleanup process: After a set period of time, clear the data in the data warehouse preload service that is not in the top X positions of the leaderboard.

[0036] In this embodiment, when the data hit rate is greater than 50%, the data index is increased by one unit; when the data hit rate is less than or equal to 50%, the data index is decreased by one unit. The hit rate refers to the probability that the stored data is the data currently needed.

[0037] The aforementioned data warehouse also includes a machine learning model server. This server trains a model based on the data preloaded from the data warehouse in the data lake, considering factors such as time periods, and simulates the success rate. As the model improves, it simulates and calculates predictions of the hit data in the background. When the prediction success rate reaches M% (e.g., 80%) or higher, it notifies the intelligent preloading engine to switch over and take over the prediction engine's background service. The intelligent preloading engine then preloads the data from the data warehouse server into the data lake based on the prediction results from the machine learning model server.

[0038] Based on the above system, the present invention also discloses a method for transferring data from a data warehouse to a data lake, which includes the following steps:

[0039] Step 1: The custom algorithm server periodically calculates the preloading method and guides the intelligent preloading engine to preload the data from the data warehouse server into the data warehouse preloading data service according to the preloading method calculated by the custom algorithm server.

[0040] The specific preloading method for custom algorithm server computation is as follows:

[0041] Retrieve log information recorded by the intelligent preloading engine, and query which indexes are called most frequently in the corresponding time period based on the current system time; then extract the frequently called data and process it as follows:

[0042] When the extracted data is being preloaded for the first time, or when the extracted data is called no more than N times (N is 5 in this embodiment), the extracted data is directly preloaded into the data warehouse preloading service of the data lake, and then the data is cleared.

[0043] When the extracted data is not being preloaded for the first time and is called more than N times, the index weight of the data is adjusted according to its hit rate, and a weight ranking list is generated. Then, it is determined whether the extracted data is in the top X of the ranking list. If it is in the top X of the ranking list, the extracted data is preloaded into the data warehouse preloading service of the data lake and then the data is cleared. If it is outside the top X of the ranking list, the data is cleared directly.

[0044] Data cleanup process: Clear data from the data warehouse preload service that is outside the top X in the leaderboard.

[0045] Step 2: When the caller uses data lake data and needs data warehouse data, it uses the data warehouse index service to query the required data index. The data warehouse index service first queries and retrieves data from the data warehouse preloaded data service. If the data warehouse preloaded data service does not have the data, it queries and retrieves the data from the data warehouse server through the intelligent preload engine. The data retrieved from the data warehouse server will be cached in the data warehouse preloaded data service at the same time.

[0046] When the data warehouse also has a machine learning model server, during steps 1 and 2, the machine learning model server trains the model based on the data hits from the data warehouse preloaded data service in the data lake, combined with time periods, and simulates the success rate. As the model gradually improves, it simulates the prediction of hit data in the background. When the prediction success rate reaches M% (e.g., 80%) or higher, it notifies the intelligent preloading engine to switch over and take over the prediction engine's background service. The intelligent preloading engine then preloads the data from the data warehouse server into the data lake based on the prediction results from the machine learning model server.

[0047] This invention uses a custom algorithm and machine learning model to calculate which data is likely to be preloaded into the data lake cache by using data indexes and associated actual data, thereby achieving fast transmission.

[0048] The above description is merely an embodiment of the present invention and does not constitute any limitation on the technical scope of the present invention. Therefore, any minor modifications, equivalent changes, and alterations made to the above embodiments based on the technical essence of the present invention shall still fall within the scope of the technical solution of the present invention.

Claims

1. A system for transmitting data from a data warehouse to a data lake, comprising a data warehouse and a data lake, wherein the data warehouse includes a data warehouse server, the data lake includes a data lake server, and both the data warehouse server and the data lake server are used for storing data; characterized in that: The data warehouse also includes a custom algorithm server and an intelligent preloading engine. The custom algorithm server is used to calculate the preloading method. The intelligent preloading engine preloads the data in the data warehouse server into the data lake according to the preloading method calculated by the custom algorithm server, and records the log information of each call to the data warehouse. The data lake also includes a data warehouse preload data service and a data warehouse index service. The data warehouse preload data service is used to store data preloaded from the data warehouse server into the data lake. The data warehouse index service is used to provide index queries and retrieve data from the data warehouse preload service or the data warehouse server to transmit to the data lake server. Each index in the data warehouse index service points to the actual data in the data warehouse preload data service or the actual data in the data warehouse server.

2. The system for transmitting data from a data warehouse to a data lake according to claim 1, characterized in that: The custom algorithm server calculation preloading method is as follows: Retrieve log information recorded by the intelligent preloading engine, and query which indexes are called most frequently in the corresponding time period based on the current system time; then extract the frequently called data and process it as follows: When the extracted data is being preloaded for the first time, or when the extracted data is called no more than N times, the extracted data is directly preloaded into the data warehouse preloading service of the data lake, and then the data is cleared. When the extracted data is not being preloaded for the first time and is called more than N times, the index weight of the data is adjusted according to its hit rate, and a weight ranking list is generated. Then, it is determined whether the extracted data is in the top X of the ranking list. If it is in the top X of the ranking list, the extracted data is preloaded into the data warehouse preloading service of the data lake, and then the data is cleaned. If a data point is outside the top X positions on the leaderboard, the data will be cleared directly. Data cleanup process: Clear data from the data warehouse preload service that is outside the top X in the leaderboard.

3. The system for transmitting data from a data warehouse to a data lake according to claim 1, characterized in that: The data warehouse also includes a machine learning model server. This machine learning model server trains a model based on the data hits in the data warehouse preload data service of the data lake, combined with time periods, and simulates the calculation of the success rate. When the model is perfected, it simulates the calculation of the predicted hit data in the background. When the prediction success rate reaches M% or more, the intelligent preloading engine preloads the data in the data warehouse server into the data lake based on the prediction results of the machine learning model server.

4. A method for transferring data from a data warehouse to a data lake, characterized in that: The method is implemented using the system described in any one of claims 1-3, and the method includes the following steps: Step 1: The custom algorithm server periodically calculates the preloading method and guides the intelligent preloading engine to preload the data from the data warehouse server into the data warehouse preloading data service according to the preloading method calculated by the custom algorithm server. Step 2: When the caller uses data lake data and needs data warehouse data, it uses the data warehouse index service to query the required data index. The data warehouse index service first queries and retrieves data from the data warehouse preloaded data service. If the data warehouse preloaded data service does not have the data, it queries and retrieves the data from the data warehouse server through the intelligent preload engine. The data retrieved from the data warehouse server will be cached in the data warehouse preloaded data service at the same time.

5. The method for transferring data from a data warehouse to a data lake according to claim 4, characterized in that: The specific preloading method for the custom algorithm server calculation is as follows: Retrieve log information recorded by the intelligent preloading engine, and query which indexes are called most frequently in the corresponding time period based on the current system time; then extract the frequently called data and process it as follows: When the extracted data is being preloaded for the first time, or when the extracted data is called no more than N times, the extracted data is directly preloaded into the data warehouse preloading service of the data lake, and then the data is cleared. When the extracted data is not being preloaded for the first time and is called more than N times, the index weight of the data is adjusted according to its hit rate, and a weight ranking list is generated. Then, it is determined whether the extracted data is in the top X of the ranking list. If it is in the top X of the ranking list, the extracted data is preloaded into the data warehouse preloading service of the data lake, and then the data is cleaned. If a data point is outside the top X positions on the leaderboard, the data will be cleared directly. Data cleanup process: Clear data from the data warehouse preload service that is outside the top X in the leaderboard.

6. The method for transmitting data from a data warehouse to a data lake according to claim 4, characterized in that: The data warehouse also has a machine learning model server. During the execution of steps 1 and 2, the machine learning model server trains the model based on the data hit in the data warehouse preload data service of the data lake and the time period, and simulates the calculation of the success rate. When the model is perfected, it simulates the calculation of the predicted hit data in the background. When the prediction success rate reaches M% or more, the intelligent preloading engine preloads the data in the data warehouse server into the data lake based on the prediction results of the machine learning model server.

Images