A data processing method and system based on BI system

Abstract

The embodiment of the present invention provides a data processing method based on a BI system, comprising the following steps: Step 1: a server receives a database splitting request from a client, wherein the database splitting request is based on an SQL statement to split a data table in a database; Step 2: based on the obtained SQL splitting rule, the SQL statement is split so that the data table is evenly divided into N portions of target data; Step 3: the obtained N portions of target data are stored in a memory buffer pool of the database; Step 4: the N portions of target data in the memory buffer pool of the database are merged and sent to a shared storage. The embodiment of the present invention splits different databases to improve query efficiency in different databases, thereby improving user experience.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and more specifically, to a data processing method and system based on a BI system. Background Art

[0002] With the rapid development of business, the amount of data has increased rapidly, and data storage and data usage have gradually become bottlenecks of the system.

[0003] The existing BI system only supports operations on related relational databases, which is not conducive to data download in big data scenarios. Therefore, how to split the database is a major problem in improving database performance.

[0004] SUMMARY OF THE INVENTION

[0005] In order to overcome the deficiencies of the prior art, the present invention provides a data processing method based on a BI system for splitting a database so as to improve the query efficiency of users.

[0006] The technical solution adopted by the present invention to solve its technical problem is: a data processing method based on a BI system, comprising the following steps: Step 1: a server receives a database splitting request from a client, and the database splitting request is based on an SQL statement to split a data table in a database; Step 2: based on the acquired SQL splitting rule, the SQL statement is split so that the data table is divided into N portions of target data; Step 3: the obtained N portions of target data are stored in a memory buffer pool of the database; Step 4: the N portions of target data in the memory buffer pool of the database are merged and sent to shared storage.

[0007] Preferably, after the server receives the client database splitting request, the step further includes:

[0008] marking the database splitting request received by the server as a pending data volume analysis;

[0009] The number of query records in the data table in the database is analyzed based on the amount of data to be processed.

[0010] Preferably, before the SQL splitting rule is obtained, the step further includes:

[0011] Automatically configure parameters according to the performance of the database, wherein the database performance includes CPU, memory, and database type;

[0012] The SQL splitting rule is obtained based on the configured parameters.

[0013] Preferably, the SQL statement is split, and the steps include:

[0014] The SQL statement is split based on a state asynchronous compensation mechanism.

[0015] Preferably, the steps of merging the N copies of target data in the memory buffer pool of the database and sending them to the shared storage include:

[0016] Based on the state asynchronous compensation mechanism, the N copies of target data in the memory buffer pool of the database are merged and sent to the shared storage.

[0017] A data processing system based on a BI system, the system comprising:

[0018] A receiving unit, configured to receive, by the server, a database splitting request from a client, wherein the database splitting request is based on an SQL statement to split a data table in a database;

[0019] A splitting unit, configured to split the SQL statement based on the obtained SQL splitting rule, so that the data table is evenly divided into N pieces of target data;

[0020] A storage unit, used for storing the obtained N copies of target data into a memory buffer pool of the database;

[0021] A sending unit is used to merge the N copies of target data in the memory buffer pool of the database and send them to the shared storage.

[0022] Preferably, the system further comprises:

[0023] a marking unit, configured to mark the database splitting request received by the server as a to-be-processed data volume analysis;

[0024] An analysis unit is used to analyze the number of query records in the data table in the database according to the amount of data to be processed.

[0025] Preferably, the system further comprises:

[0026] A configuration unit, used to automatically configure parameters according to the performance of the database, wherein the database performance includes CPU, memory, and database type;

[0027] An acquiring unit acquires the SQL splitting rule based on the configured parameters.

[0028] Preferably, the splitting unit comprises:

[0029] The splitting subunit is used to split the SQL statement based on the state asynchronous compensation mechanism.

[0030] Preferably, the sending unit comprises:

[0031] The sending subunit is used to merge the N copies of target data in the memory buffer pool of the database and send them to the shared storage based on the state asynchronous compensation mechanism.

[0032] The beneficial effect of the present invention is that by splitting different databases, the query efficiency in different databases is improved, thereby improving the user experience. BRIEF DESCRIPTION OF THE DRAWINGS

[0033] Figure 1 It is a flowchart of the data processing method based on the BI system.

[0034] Figure 2 It is a functional module diagram of the data processing system based on the BI system.

[0035] Figure 3 It is another functional module diagram of the data processing system based on the BI system.

[0036] Figure 4 It is another functional module diagram of the data processing system based on the BI system. DETAILED DESCRIPTION

[0037] In order to make the purpose, technical solution and advantages of the present invention more clearly understood, the present invention is further described in detail below in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present invention and are not intended to limit the present invention.

[0038] The specific implementation of the present invention is described in detail below in conjunction with specific embodiments:

[0039] Embodiment 1:

[0040] Figure 1 The implementation process of the data processing method based on the BI system provided in the first embodiment of the present invention is shown. For the convenience of description, only the part related to the embodiment of the present invention is shown, which is described in detail as follows:

[0041] In step S101: the server receives a database splitting request from a client, wherein the database splitting request is based on an SQL statement to split a data table in a database;

[0042] In an embodiment of the present application, the server receives a database splitting request from the client, which requests to split a data table in the database based on an SQL statement, records the database splitting request and stores it in the database, and the server periodically obtains data in the data table.

[0043] Preferably, after receiving the database splitting request from the client, the server marks the database splitting request received by the server as pending data volume analysis; and analyzes the number of query records of the data table in the database according to the pending data volume analysis.

[0044] Further preferably, before splitting the SQL statement, parameters are automatically configured according to the performance of the database, the database performance includes CPU, memory, and database type, the SQL splitting rules are obtained based on the configured parameters, and the parameters are pre-configured to facilitate subsequent splitting of the database.

[0045] In step S102: based on the obtained SQL splitting rule, the SQL statement is split so that the data table is evenly divided into N pieces of target data;

[0046] In an embodiment of the present application, SQL statements are split according to different database characteristics and machine performance. Database splitting includes horizontal splitting and vertical splitting. Horizontal splitting is based on the values ​​of certain fields in the data table, and then according to certain mapping rules, for example, modulus is taken to split the data in a data table into multiple data tables. Vertical splitting is based on the usage frequency of certain fields in the data table, and the fields and data in a table are split into multiple data tables. The data in the data table is split into N parts of data. For example, the hive database splits 5 million data into 10 parts, each with a data volume of 500,000. The oracle database splits 1 million data into 10 parts, each with a data volume of 100,000.

[0047] Preferably, the SQL statement is split based on a state asynchronous compensation mechanism, thereby ensuring that the data table is effectively split, thereby improving the efficiency of data query.

[0048] In step S103: storing the obtained N copies of target data in the memory buffer pool of the database;

[0049] In the embodiment of the present application, the database has a dedicated memory buffer pool. When querying the database, the database loads the data in the disk into the memory buffer pool in rows. When the memory is limited, the smaller the data row, the more data rows can be cached in the database memory buffer pool. The higher the cache access frequency of the data memory buffer pool, the higher the cache hit rate, and the fewer disk operations. Assuming that the size of the database memory buffer pool is 1G, if the size of each row of the data table before splitting is 1K, before splitting, only 1 million rows of data can be cached in the database memory buffer pool.

[0050] In step S104: the N copies of target data in the memory buffer pool of the database are merged and sent to the shared storage.

[0051] In an embodiment of the present application, the split SQL statements are executed asynchronously in batches at a scheduled time, and the N data in the split data table are stored in HDFS (shared storage). The results of multiple SQL statements are merged into one compressed file and saved in the HDFS path for users to download.

[0052] Preferably, the N copies of target data in the memory buffer pool of the database are merged and sent to the shared storage based on a state asynchronous compensation mechanism.

[0053] The entire design utilizes asynchronous scheduled tasks and handles different processing flows according to different states. The flow is based on state transfer, which is conducive to restarting the application when the system is hung up. It can be executed continuously without having to execute from the beginning, reducing resource waste and improving system stability.

[0054] A person skilled in the art can understand that all or part of the steps in the above-mentioned embodiment method can be completed by instructing related hardware through a program, and the program can be stored in a computer-readable storage medium, such as ROM / RAM, disk, CD-ROM, etc.

[0055] Embodiment 2:

[0056] Figure 2 The structure of the data processing system based on the BI system provided by the second embodiment of the present invention is shown. For the convenience of description, only the part related to the embodiment of the present invention is shown, which is described in detail as follows:

[0057] A receiving unit 201 is used for the server to receive a database splitting request from a client, wherein the database splitting request is based on an SQL statement to split a data table in a database;

[0058] A splitting unit 202 is used to split the SQL statement based on the obtained SQL splitting rule, so that the data table is evenly divided into N pieces of target data;

[0059] The storage unit 203 is used to store the obtained N copies of target data into the memory buffer pool of the database;

[0060] The sending unit 204 is used to merge the N copies of target data in the memory buffer pool of the database and send them to the shared storage.

[0061] In an embodiment of the present invention, a server receives a database splitting request from a client, the database splitting request is based on an SQL statement to split a data table in a database, the SQL statement is split based on the obtained SQL splitting rule, so that the data table is evenly divided into N copies of target data, the obtained N copies of target data are stored in the memory buffer pool of the database, the N copies of target data in the memory buffer pool of the database are merged and sent to a shared storage, the database is split, the database is optimized, the database performance is improved, thereby improving the efficiency of data query, and then improving the user experience. The specific implementation of each unit can refer to the description of Example 1, which will not be repeated here.

[0062] Embodiment three:

[0063] Figure 3 Another structure of a data processing system based on a BI system provided in Embodiment 3 of the present invention is shown. For the sake of convenience, only the part related to the embodiment of the present invention is shown, including:

[0064] A receiving unit 301 is used for the server to receive a database splitting request from a client, wherein the database splitting request is based on an SQL statement to split a data table in a database;

[0065] A marking unit 302, configured to mark the database splitting request received by the server as a to-be-processed data volume analysis;

[0066] An analysis unit 303 is used to analyze the number of query records in the data table in the database according to the amount of data to be processed;

[0067] A configuration unit 304, configured to automatically configure parameters according to the performance of the database, wherein the database performance includes CPU, memory, and database type;

[0068] An acquisition unit 305 acquires the SQL splitting rule based on the configured parameters;

[0069] A splitting unit 306 is used to split the SQL statement based on the obtained SQL splitting rule, so that the data table is evenly divided into N pieces of target data;

[0070] A storage unit 307 is used to store the obtained N copies of target data into a memory buffer pool of the database;

[0071] The sending unit 308 is used to merge the N copies of target data in the memory buffer pool of the database and send them to the shared storage.

[0072] In an embodiment of the present invention, a server receives a database splitting request from a client, and the database splitting request is based on an SQL statement to split a data table in a database; the database splitting request received by the server is marked as an analysis of the amount of data to be processed; the number of query records of the data table in the database is analyzed according to the analysis of the amount of data to be processed; parameters are automatically configured according to the performance of the database, and the database performance includes CPU, memory, and database type; the configured parameters obtain the SQL splitting rules; based on the obtained SQL splitting rules, the SQL statement is split so that the data table is divided into N portions of target data, and the database is optimized and the database performance is improved by splitting the database, thereby improving the efficiency of data query and further improving the user experience. The specific implementation methods of each unit can refer to the description of Example 1, which will not be repeated here.

[0073] Embodiment three:

[0074] Figure 4 Another structure of a data processing system based on a BI system provided in Embodiment 3 of the present invention is shown. For the sake of convenience, only the part related to the embodiment of the present invention is shown, including:

[0075] Splitting unit: used for splitting the SQL statement based on the acquired SQL splitting rule, so that the data table is evenly divided into N pieces of target data;

[0076] A splitting subunit, used for splitting the SQL statement based on a state asynchronous compensation mechanism;

[0077] A sending unit: used for merging the N copies of target data in the memory buffer pool of the database and sending them to a shared storage;

[0078] The sending subunit is used to merge the N copies of target data in the memory buffer pool of the database and send them to the shared storage based on the state asynchronous compensation mechanism.

[0079] The specific implementation of each unit can refer to the description of the first embodiment, which will not be repeated here.

[0080] In the embodiment of the present invention, the data processing system based on the BI system can be implemented by corresponding hardware or software units. Each unit can be an independent software or hardware unit or integrated into one software or hardware unit, which is not intended to limit the present invention.

Claims

1. A data processing method based on a BI system, characterized in that: The following steps are involved: Step 1: The server receives a database splitting request from the client, wherein the database splitting request is based on an SQL statement to split a data table in the database; After the server receives the client's database splitting request, the steps further include: marking the database splitting request received by the server as a pending data volume analysis; Analyzing the number of query records in the data table in the database according to the amount of data to be processed; Step 2: Split the SQL statement based on the obtained SQL splitting rule so that the data table is evenly divided into N pieces of target data; Before the SQL splitting rule is obtained, the step further includes: Automatically configure parameters according to the performance of the database, wherein the database performance includes CPU, memory, and database type; Acquiring the SQL splitting rule based on the configured parameters; splitting the SQL statement, the steps comprising: Splitting the SQL statement based on a state asynchronous compensation mechanism; Step 3: storing the obtained N copies of target data into the memory buffer pool of the database; Step 4: Merge the N copies of target data in the memory buffer pool of the database and send them to the shared storage; The step of merging the N copies of target data in the memory buffer pool of the database and sending them to the shared storage includes: merging the N copies of target data in the memory buffer pool of the database and sending them to the shared storage based on a state asynchronous compensation mechanism.

2. A data processing system based on a BI system, characterized in that: The system is used to implement a data processing method based on a BI system as claimed in claim 1; The system comprises: A receiving unit, configured to receive, by the server, a database splitting request from a client, wherein the database splitting request is based on an SQL statement to split a data table in a database; A splitting unit, configured to split the SQL statement based on the obtained SQL splitting rule, so that the data table is evenly divided into N pieces of target data; A storage unit, used for storing the obtained N copies of target data into a memory buffer pool of the database; A sending unit, used for merging the N copies of target data in the memory buffer pool of the database and sending them to a shared storage; The system further comprises: a marking unit, configured to mark the database splitting request received by the server as a to-be-processed data volume analysis; An analysis unit, configured to analyze the number of query records in the data table in the database according to the amount of data to be processed; The system further comprises: A configuration unit, used to automatically configure parameters according to the performance of the database, wherein the database performance includes CPU, memory, and database type; An acquisition unit, which acquires the SQL splitting rule based on the configured parameters; The splitting unit comprises: A splitting subunit, used for splitting the SQL statement based on a state asynchronous compensation mechanism; The sending unit comprises: The sending subunit is used to merge the N copies of target data in the memory buffer pool of the database and send them to the shared storage based on the state asynchronous compensation mechanism.

Images