Data synchronization method, device, computer equipment and storage medium

Abstract

The present application relates to a data synchronization method, apparatus, computer equipment and storage medium, wherein the method comprises: receiving a user's data synchronization request information, the request information comprising field information of data for online analysis by the user, obtaining a fact table and a dimension table corresponding to the field information, and left-joining the fact table and the dimension table to obtain a widened table, and optimizing the table, partitioning the data according to the accounting time of the data in the optimized widened table, and saving the partitioned data to the partition corresponding to the distributed file system HDFS cluster to obtain the partitioned data, writing the partitioned data into a temporary table of the columnar database management unit Clickhouse, and completing data synchronization. The present synchronization method can perform distributed writing of data according to the main time specified by the user, realize the concurrency of data synchronization, and improve the accuracy of data synchronization.

Description

Technical Field

[0001] The present application relates to the technical field of big data analysis, and in particular to a data synchronization method, apparatus, computer equipment and storage medium. Background Art

[0002] Online Analytical Processing (OLAP) is a fast analysis technology that shares multidimensional information. It uses multidimensional database technology to allow users to observe data from different angles. OLAP is mainly used to support complex analytical operations, focusing on decision support for managers. It can meet the requirements of analysts to quickly and flexibly perform complex queries on large amounts of data, and present query results in an intuitive and easy-to-understand form to assist decision-making.

[0003] At present, a full coverage method is usually used to synchronize data from a data warehouse to an OLAP platform. The data sets in the data warehouse are synchronized to the OLAP platform according to the partition time. However, the partition time of the data warehouse is the data processing time, which is not the main time dimension. The accounting time of the data record is the main time dimension. When performing data synchronization operations, the data cannot be synchronized to the OLAP platform according to the specified main time, which reduces the accuracy of data synchronization. In addition, the data records of a partition in the data warehouse can only be written to one partition in the OLAP platform, and concurrent data synchronization cannot be achieved. Summary of the invention

[0004] Based on this, it is necessary to provide a data synchronization method, device, computer equipment and storage medium to address the above technical problems, which can perform distributed writing of data according to the main time specified by the user, realize concurrency of data synchronization, and improve the accuracy of data synchronization.

[0005] In a first aspect, a data synchronization method is provided, the method comprising:

[0006] Receive a user's data synchronization request information, where the request information includes field information of the data that the user performs online analysis on;

[0007] Obtain the fact table and dimension table corresponding to the field information, and left-join the fact table and dimension table to obtain the widened table corresponding to the field information;

[0008] Perform a mapjoin operation on the skewed data in the widened table to obtain an optimized widened table;

[0009] Classify the data in the optimized widened table according to the accounting time, and save the classified data to the partition corresponding to the distributed file system HDFS cluster to obtain the partitioned data;

[0010] The partitioned data is written into a temporary table of Clickhouse, a column-based database management unit, for data synchronization.

[0011] In a possible implementation, the partitioned data is written into a temporary table of the column-based database management unit Clickhouse, and data synchronization is performed, including:

[0012] Count the data volume of the temporary table and the optimized widened table to obtain the data volume of the temporary table and the widened table;

[0013] When the data volume of the temporary table is consistent with that of the optimized widened table, the partitioned data in the temporary table is written to the main table of Clickhouse to complete data synchronization.

[0014] In a possible implementation, the method further includes:

[0015] When the data volume of the temporary table is inconsistent with that of the widened table, the partitioned data is rewritten into the temporary table of Clickhouse to obtain a rewritten temporary table;

[0016] When the amount of data in the rewritten temporary table is consistent with the amount of data in the optimized widened table, the partitioned data in the rewritten temporary table is written into the main table of Clickhouse to complete data synchronization.

[0017] In a possible implementation, the method further includes:

[0018] When the data volume of the temporary table is consistent with the data volume of the optimized widened table, after the partitioned data in the temporary table is written into the main table of Clickhouse, the first status information of the data in the main table is recorded as the submission status.

[0019] In a possible implementation, the method further includes:

[0020] When the data volume of the temporary table is inconsistent with that of the widened table, obtain the meta information and execution information of the temporary table;

[0021] The second state information of the data in the temporary table is recorded as the pre-commit state;

[0022] The meta information, execution information and second state information are saved in a relational database management system MySQL.

[0023] In a possible implementation, the method further includes:

[0024] Get the first fact table and offset table to be synchronized within the preset time;

[0025] Multiplying the metric data in the first fact table by a preset value to obtain a preliminary fact table;

[0026] Add the measurement data in the preliminary fact table and the offset table to obtain the fact offset table;

[0027] Expand the fact offset table to synchronize data.

[0028] In a second aspect, a data synchronization device is provided, the device comprising:

[0029] A receiving module, used to receive a user's request information for data synchronization, where the request information includes field information of the data that the user performs online analysis on;

[0030] A splicing module is used to obtain the fact table and dimension table corresponding to the field information, and left-splice the fact table and the dimension table to obtain the widened table corresponding to the field information;

[0031] The optimization module is used to perform a mapjoin operation on the tilted data in the widened table to obtain an optimized widened table;

[0032] The partition module is used to classify the data in the optimized widened table according to the accounting time, and save the classified data to the partition corresponding to the distributed file system HDFS cluster to obtain the partitioned data;

[0033] The synchronization module is used to write the partitioned data into the temporary table of the columnar database management unit Clickhouse for data synchronization.

[0034] In a possible implementation, the synchronization module is specifically used to:

[0035] Count the data volume of the temporary table and the optimized widened table to obtain the data volume of the temporary table and the widened table;

[0036] When the data volume of the temporary table is consistent with that of the optimized widened table, the partitioned data in the temporary table is written to the main table of Clickhouse to complete data synchronization.

[0037] In a third aspect, a computer device is provided, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein when the processor executes the computer program, the data synchronization method in the first aspect or any one of the implementations of the first aspect is implemented.

[0038] In a fourth aspect, a computer-readable storage medium is provided, on which a computer program is stored. When the computer program is executed by a processor, the data synchronization method in the first aspect or any one of the implementations of the first aspect is implemented.

[0039] The above-mentioned data synchronization method, device, computer equipment and storage medium receive the user's data synchronization request information, the request information includes the field information of the data that the user performs online analysis, obtains the fact table and dimension table corresponding to the field information, and left-joins the fact table and the dimension table to obtain the widened table corresponding to the field information, performs a mapjoin operation on the skewed data in the widened table to obtain an optimized widened table, partitions the data in the optimized widened table according to the accounting time, and saves the partitioned data to the partition corresponding to the distributed file system HDFS cluster to obtain the partitioned data, writes the partitioned data into the temporary table of the columnar database management unit Clickhouse, and performs data synchronization. It can perform distributed writing of data according to the main time specified by the user, realizes data synchronization concurrency, and improves the accuracy of data synchronization. BRIEF DESCRIPTION OF THE DRAWINGS

[0040] Figure 1 This is a structural block diagram of a data synchronization system in one embodiment of the present application;

[0041] Figure 2 This is a flowchart of a data synchronization method in one embodiment of the present application;

[0042] Figure 3 This is a structural block diagram of a data synchronization device in one embodiment of the present application;

[0043] Figure 4 This is a diagram of the internal structure of a computer device in one embodiment of the present application. DETAILED DESCRIPTION

[0044] In order to make the purpose, technical solution and advantages of the present application more clearly understood, the present application 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 application and are not used to limit the present application.

[0045] The data synchronization method provided in this application can be applied to Figure 1 The data synchronization system shown includes: a data warehouse module 110, an OLAP widening module 120, and an OLAP engine module 130, wherein the OLAP widening module 120 includes an online analytical processing data warehouse tool OLAP-HIVE cluster, and the OLAP engine module 130 includes a database management unit Clickhouse. The data warehouse module 110 is used to synchronize the fact table and the latitude table to the OLAP-HIVE cluster, the OLAP-HIVE cluster is used to widen the fact table and the latitude table and write them to Clickhouse, and Clickhouse is used to synchronize the widened data.

[0046] In some embodiments, Figure 2As shown, a data synchronization method is provided, comprising the following steps:

[0047] S210, receiving data synchronization request information from a user, where the request information includes field information of data that the user performs online analysis on.

[0048] When a user performs data analysis through the OLAP platform, he or she enters the search statement for data query through the OLAP platform interactive interface. The OLAP platform interactive interface receives the search information entered by the user, generates request information based on the search statement, and sends it to the data synchronization system. The data synchronization system receives the request information, wherein the request information includes field information, which is the keyword for the user to obtain online analysis data.

[0049] S220, obtaining a fact table and a dimension table corresponding to the field information, and left-joining the fact table and the dimension table to obtain a widened table corresponding to the field information.

[0050] A fact table is a central table in a data warehouse structure that contains numeric measures and keys that link fact to dimension tables. Fact tables contain data that describes specific events within a business.

[0051] Dimension tables can be seen as windows for users to analyze data. Dimension tables contain the characteristics of fact records in fact tables. Some characteristics provide descriptive information, and some characteristics specify how to summarize fact table data to provide useful information to analysts. Dimension tables contain hierarchical structures of characteristics that help summarize data.

[0052] Obtain the fact table and dimension table corresponding to the field information from the data warehouse module, and widen the fact table and dimension table through the OLAP-HIVE cluster, that is, perform a left join on the fact table and dimension table, regard one table as the left table and the other table as the right table, and the widened table will display all the data in the left table and the data in the right table that meets the field information conditions. The places in the left table that do not have data in the right table are all NULL. By widening the fact table and dimension table, the data used by users for online analysis are associated on one table, making it easier for users to analyze data more intuitively.

[0053] S230, performing a mapjoin operation on the tilted data in the widening table to obtain an optimized widening table.

[0054] In the process of widening fact tables and dimension tables, if the data volumes corresponding to the dimensions in the table differ greatly, and the data volumes corresponding to one or several dimensions are particularly large, data skew will occur, which will prolong data synchronization time. Therefore, it is necessary to optimize the widened table and perform mapjoin operations on the widened table.

[0055] The skewed data in the widened table is divided into a large table and a small table, the small table is read into the memory, the large table is scanned sequentially, and the splicing operation is directly performed on the map side to obtain the optimized widened table. Since the optimized widened table greatly reduces the impact of skewed data when synchronizing data, it reduces the time for data synchronization and improves the speed of data synchronization.

[0056] S240, classify the data in the optimized widened table according to the accounting time, and save the classified data to the partition corresponding to the distributed file system HDFS cluster to obtain the partitioned data.

[0057] The data with the same accounting time in the optimized widened table is classified into one category. The partitions in the distributed file system (Hadoop Distributed File System, HDFS) cluster are also divided according to the accounting date. The classified data is saved to the corresponding path of the HDFS cluster and added to the Hive partition. For example, the data with an accounting date of 2021.12.25 is classified into one category, and the data with an accounting date of 2021.12.26 is classified into one category. The data with an accounting date of 2021.12.25 is saved in the 2021.12.25 partition in the HDFS cluster, and the data with an accounting date of 2021.12.26 is saved in the 2021.12.25 partition in the HDFS cluster.

[0058] Among them, the classified data is saved to the corresponding partition of the HDFS cluster in the form of a global lock to ensure concurrent data synchronization.

[0059] S250, writing the partitioned data into a temporary table of the column-based database management unit Clickhouse to perform data synchronization.

[0060] According to the accounting time of the partitioned data, write all the partitioned data to the location corresponding to the accounting time in the Clickhouse temporary table, and then synchronize it to the main table to complete data synchronization.

[0061] In an embodiment of the present application, by receiving user request information, the request information includes field information of data that the user performs online analysis, obtaining fact tables and dimension tables corresponding to the field information, and left-joining the fact tables and dimension tables to obtain a widened table corresponding to the field information, performing a mapjoin operation on the skewed data in the widened table to obtain an optimized widened table, partitioning the data according to the accounting time of the optimized widened table, and saving the partitioned data to the partition corresponding to the distributed file system HDFS cluster to obtain partitioned data, writing the partitioned data into a temporary table of the columnar database management unit Clickhouse, and performing data synchronization. The data can be distributedly written according to the main time specified by the user, thereby realizing concurrency of data synchronization and improving the accuracy of data synchronization.

[0062] In some embodiments, the partitioned data is written into a temporary table of the column-based database management unit Clickhouse, and data synchronization is performed, including:

[0063] Count the data volume of the temporary table and the optimized widened table to obtain the data volume of the temporary table and the widened table;

[0064] When the data volume of the temporary table is consistent with that of the optimized widened table, the partitioned data in the temporary table is written to the main table of Clickhouse to complete data synchronization.

[0065] Write the partitioned data into Clickhouse's temporary table, count the data volume in the temporary table and the optimized widened table, and when the data volume in the temporary table is consistent with that in the optimized widened table, it indicates that the HDFS cluster has accurately written the data into Clickhouse's temporary table, and the synchronization task is successful. The data in the temporary table can be synchronized to the main table through attachpartitionfrom and presented to the user.

[0066] In some embodiments, the method further comprises:

[0067] When the data volume of the temporary table is inconsistent with that of the widened table, the partitioned data is rewritten into the temporary table of Clickhouse to obtain a rewritten temporary table;

[0068] When the amount of data in the rewritten temporary table is consistent with the amount of data in the optimized widened table, the partitioned data in the rewritten temporary table is written into the main table of Clickhouse to complete data synchronization.

[0069] When the data volume of the temporary table is inconsistent with the data volume of the widened table, it indicates that the HDFS cluster has not completely written the partitioned data into the temporary table of Clickhouse, and the synchronization task fails. At this time, the data in the temporary table cannot be synchronized with the main table to present to the user, and data synchronization is required again. Rewrite the partitioned data into the temporary table of Clickhouse to obtain a new temporary table, that is, the rewrite temporary table, and compare the data volume of the rewritten temporary table and the widened table again. If the two are still inconsistent, continue to rewrite the partitioned data into the temporary table of Clickhouse until the data volume in the rewritten temporary table is consistent with the data volume of the optimized widened table. Then, write the partitioned data in the rewritten temporary table into the main table of Clickhouse to complete data synchronization and ensure the accuracy of data synchronization.

[0070] In some embodiments, the method further comprises:

[0071] When the data volume of the temporary table is consistent with the data volume of the optimized widened table, after the partitioned data in the temporary table is written into the main table of Clickhouse, the first status information of the data in the main table is recorded as the submission status.

[0072] A transaction is a unit for maintaining data consistency in a database. It transforms a database from a consistent state to a new consistent state. In short, if a set of processing steps either all occur or none of them are executed, we call the set of processing steps a transaction. Since data synchronization between multiple nodes may not all succeed, corresponding distributed transactions are deployed during the data synchronization process to ensure the integrity and reliability of the synchronized data.

[0073] The first status information is the status information of the data in the main table that is successfully synchronized. The commit status indicates that the transaction is ended and all steps of data synchronization are completed. After the synchronization task is successful and the data in the temporary table is written to the main table, the status of the data in the main table is recorded as the commit status.

[0074] In some embodiments, the method further comprises:

[0075] When the data volume of the temporary table is inconsistent with that of the widened table, obtain the meta information and execution information of the temporary table;

[0076] The second state information of the data in the temporary table is recorded as the pre-commit state;

[0077] The meta information, execution information and second state information are saved in a relational database management system MySQL.

[0078] The metadata is the business description information of the data. The execution information includes data synchronization failure information and data synchronization success information. When the data volume of the temporary table is inconsistent with the data volume of the widened table, the data synchronization failure information and metadata of the temporary table are obtained. The second status information is the status information of the data in the main table identified by synchronization. The pre-commit status indicates the end of the transaction. All data synchronization steps have failed and Clickhouse data synchronization needs to be performed again. When the synchronization task fails, the status of the data in the temporary table is recorded as the pre-commit status, and the partitioned data is rewritten into the temporary table. At the same time, the metadata, execution information and second status information are saved to the relational database management system MySQL, and the data records in MySQL are updated.

[0079] After all the data in the temporary table is written to the main table, verify whether the data volume in the temporary table is consistent with that in the main table. If not, resynchronize the Clickhouse data. If the data is consistent, restore the execution information of Clickhouse and unlock the global lock.

[0080] When the Clickhouse data synchronization process cannot guarantee transactions, all partitioned data will be overwritten into the Clickhouse master table.

[0081] In some embodiments, the method further comprises:

[0082] Get the first fact table and offset table to be synchronized within the preset time;

[0083] Multiplying the metric data in the first fact table by a preset value to obtain a preliminary fact table;

[0084] Add the measurement data in the preliminary fact table and the offset table to obtain the fact offset table;

[0085] Expand the fact offset table to synchronize data.

[0086] In some cases, it is necessary to synchronize the data of the current day, which is called offsetting and making up the number. The offsetting data of the current day is only valid on that day and does not affect the scheduling data of the next day.

[0087] The preset time is the day specified by the user, and the preset data is -1. Get the fact table, dimension table and offset table of the day, multiply the measurement data in the fact table by -1 to get the preliminary fact table, add the measurement data in the preset fact table and the offset table to get the fact offset table, widen the fact offset table, and synchronize the widened fact offset table to the main table of Clickhouse for reporting purposes.

[0088] It should be understood that although Figure 2 The steps in the flowchart are shown in sequence as indicated by the arrows, but these steps are not necessarily executed in the order indicated by the arrows. Unless otherwise specified in this document, there is no strict order restriction for the execution of these steps, and these steps can be executed in other orders. Moreover, Figure 2 At least part of the steps may include multiple sub-steps or multiple stages. These sub-steps or stages are not necessarily executed at the same time, but can be executed at different times. The execution order of these sub-steps or stages is not necessarily sequential, but can be executed in turn or alternately with other steps or at least part of the sub-steps or stages of other steps.

[0089] In some embodiments, Figure 3 As shown, a data synchronization device is provided, including: a receiving module 310, a splicing module 320, an optimization module 330, a partitioning module 340 and a synchronization module 350, wherein:

[0090] The receiving module 310 is used to receive a user's request information for data synchronization, where the request information includes field information of the data that the user performs online analysis on;

[0091] The splicing module 320 is used to obtain the fact table and dimension table corresponding to the field information, and perform left splicing on the fact table and the dimension table to obtain a widened table corresponding to the field information;

[0092] An optimization module 330 is used to perform a mapjoin operation on the tilted data in the widened table to obtain an optimized widened table;

[0093] A partitioning module 340 is used to classify the data in the optimized widened table according to the accounting time, and save the classified data to the partition corresponding to the distributed file system HDFS cluster to obtain partitioned data;

[0094] The synchronization module 350 is used to write the partitioned data into a temporary table of the column database management unit Clickhouse to perform data synchronization.

[0095] In the embodiment of the present application, data can be written in a distributed manner according to the main time specified by the user, thereby achieving concurrency of data synchronization and improving the accuracy of data synchronization.

[0096] In some embodiments, the synchronization module 350 is specifically configured to:

[0097] Count the data volume of the temporary table and the optimized widened table to obtain the data volume of the temporary table and the widened table;

[0098] When the data volume of the temporary table is consistent with that of the optimized widened table, the partitioned data in the temporary table is written to the main table of Clickhouse to complete data synchronization.

[0099] In some embodiments, the apparatus further comprises: a rewriting device 360, configured to:

[0100] When the data volume of the temporary table is inconsistent with that of the widened table, the partitioned data is rewritten into the temporary table of Clickhouse to obtain a rewritten temporary table;

[0101] When the amount of data in the rewritten temporary table is consistent with the amount of data in the optimized widened table, the partitioned data in the rewritten temporary table is written into the main table of Clickhouse to complete data synchronization.

[0102] In some embodiments, the apparatus further includes: a recording module 370, configured to:

[0103] When the data volume of the temporary table is consistent with the data volume of the optimized widened table, after the partitioned data in the temporary table is written into the main table of Clickhouse, the first status information of the data in the main table is recorded as the submission status.

[0104] In some embodiments, the apparatus further comprises:

[0105] An acquisition module 380 is used to acquire meta information and execution information of the temporary table when the data volume of the temporary table is inconsistent with the data volume of the widening table;

[0106] The recording module 370 is further used to record the second state information of the data in the temporary table as a pre-submit state;

[0107] The saving module 390 is used to save the meta information, execution information and second state information into the relational database management system MySQL.

[0108] In some embodiments, the apparatus further comprises:

[0109] The acquisition module 380 is further used to acquire the first fact table and the offset table to be synchronized within a preset time;

[0110] A multiplication module 3100, configured to multiply the metric data in the first fact table by a preset value to obtain a preliminary fact table;

[0111] An adding module 3110, used for adding the measurement data in the preliminary fact table and the offset table to obtain the fact offset table;

[0112] The synchronization module 350 is also used to widen the fact cancellation table and perform data synchronization.

[0113] For the specific definition of the data synchronization device, please refer to the definition of the data synchronization method above, which will not be repeated here. Each module in the above-mentioned data synchronization device can be implemented in whole or in part by software, hardware and a combination thereof. The above-mentioned modules can be embedded in or independent of the processor in the computer device in the form of hardware, or can be stored in the memory of the computer device in the form of software, so that the processor can call and execute the operations corresponding to the above modules.

[0114] In some embodiments, a computer device is provided. The computer device may be a server, and its internal structure diagram may be as follows: Figure 4 As shown. The computer device includes a processor, a memory, a network interface and a database connected through a system bus. Among them, the processor of the computer device is used to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, a computer program and a database. The internal memory provides an environment for the operation of the operating system and the computer program in the non-volatile storage medium. The database of the computer device is used to store table data. The network interface of the computer device is used to communicate with an external terminal through a network connection. When the computer program is executed by the processor, a data synchronization method is implemented.

[0115] Those skilled in the art will understand that Figure 4The structure shown in the figure is only a block diagram of a part of the structure related to the solution of the present application, and does not constitute a limitation on the computer device to which the solution of the present application is applied. The specific computer device may include more or fewer components than those shown in the figure, or combine certain components, or have a different arrangement of components.

[0116] In some embodiments, a computer device is provided, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the following steps when executing the computer program:

[0117] Receive request information from a user, where the request information includes field information of data that the user wants to analyze online;

[0118] Obtain the fact table and dimension table corresponding to the field information, and left-join the fact table and dimension table to obtain the widened table corresponding to the field information;

[0119] Perform a mapjoin operation on the skewed data in the widened table to obtain an optimized widened table;

[0120] Classify the data in the optimized widened table according to the accounting time, and save the classified data to the partition corresponding to the distributed file system HDFS cluster to obtain the partitioned data;

[0121] The partitioned data is written into a temporary table of Clickhouse, a column-based database management unit, for data synchronization.

[0122] In some embodiments, when the processor executes the computer program, it also implements the following steps: writing the partitioned data into a temporary table of the column-based database management unit Clickhouse to perform data synchronization, including: counting the amount of data in the temporary table and the optimized widened table to obtain the amount of data in the temporary table and the amount of data in the widened table; when the amount of data in the temporary table is consistent with the amount of data in the optimized widened table, writing the partitioned data in the temporary table into the main table of Clickhouse to complete data synchronization.

[0123] In some embodiments, the processor also implements the following steps when executing the computer program: The method also includes: when the amount of data in the temporary table is inconsistent with the amount of data in the widened table, the partitioned data is rewritten into the temporary table of Clickhouse to obtain a rewritten temporary table; until the amount of data in the rewritten temporary table is consistent with the amount of data in the optimized widened table, the partitioned data in the rewritten temporary table is written into the main table of Clickhouse to complete data synchronization.

[0124] In some embodiments, the processor further implements the following steps when executing the computer program: The method also includes: when the amount of data in the temporary table is consistent with the amount of data in the optimized widened table, after writing the partitioned data in the temporary table into the main table of Clickhouse, the first status information of the data in the main table is recorded as the submitted status.

[0125] In some embodiments, the processor also implements the following steps when executing the computer program: the method also includes: when the amount of data in the temporary table is inconsistent with the amount of data in the widened table, obtaining the metadata and execution information of the temporary table; recording the second state information of the data in the temporary table as a pre-submission state; and saving the metadata, execution information and second state information to the relational database management system MySQL.

[0126] In some embodiments, the processor further implements the following steps when executing the computer program: the method also includes: obtaining a first fact table and an offset table to be synchronized within a preset time; multiplying the metric data in the first fact table by a preset value to obtain a preliminary fact table; adding the metric data in the preliminary fact table and the offset table to obtain a fact offset table; widening the fact offset table to perform data synchronization.

[0127] In some embodiments, a computer-readable storage medium is provided, on which a computer program is stored, and when the computer program is executed by a processor, the following steps are implemented:

[0128] Receive request information from a user, where the request information includes field information of data that the user wants to analyze online;

[0129] Obtain the fact table and dimension table corresponding to the field information, and left-join the fact table and dimension table to obtain the widened table corresponding to the field information;

[0130] Perform a mapjoin operation on the skewed data in the widened table to obtain an optimized widened table;

[0131] Classify the data in the optimized widened table according to the accounting time, and save the classified data to the partition corresponding to the distributed file system HDFS cluster to obtain the partitioned data;

[0132] The partitioned data is written into a temporary table of Clickhouse, a column-based database management unit, for data synchronization.

[0133] In some embodiments, when the computer program is executed by the processor, the following steps are also implemented: writing the partitioned data into the temporary table of the column-based database management unit Clickhouse to perform data synchronization, including: counting the amount of data in the temporary table and the optimized widened table to obtain the amount of data in the temporary table and the amount of data in the widened table; when the amount of data in the temporary table is consistent with the amount of data in the optimized widened table, writing the partitioned data in the temporary table into the main table of Clickhouse to complete data synchronization.

[0134] In some embodiments, when the computer program is executed by the processor, the following steps are also implemented: The method also includes: when the amount of data in the temporary table is inconsistent with the amount of data in the widened table, the partitioned data is rewritten into the temporary table of Clickhouse to obtain a rewritten temporary table; until the amount of data in the rewritten temporary table is consistent with the amount of data in the optimized widened table, the partitioned data in the rewritten temporary table is written into the main table of Clickhouse to complete data synchronization.

[0135] In some embodiments, when the computer program is executed by the processor, the following steps are also implemented: The method also includes: when the amount of data in the temporary table is consistent with the amount of data in the optimized widened table, after writing the partitioned data in the temporary table into the main table of Clickhouse, the first status information of the data in the main table is recorded as the submitted status.

[0136] In some embodiments, when the computer program is executed by the processor, the following steps are also implemented: the method also includes: when the data volume of the temporary table is inconsistent with the data volume of the widened table, obtaining the metadata and execution information of the temporary table; recording the second state information of the data in the temporary table as a pre-submission state; and saving the metadata, execution information and second state information to the relational database management system MySQL.

[0137] In some embodiments, when the computer program is executed by the processor, the following steps are also implemented: the method also includes: obtaining a first fact table and an offset table to be synchronized within a preset time; multiplying the metric data in the first fact table by a preset value to obtain a preliminary fact table; adding the metric data in the preliminary fact table and the offset table to obtain a fact offset table; widening the fact offset table to perform data synchronization.

[0138] Those skilled in the art can understand that all or part of the processes in the above-mentioned embodiment methods can be completed by instructing the relevant hardware through a computer program, and the computer program can be stored in a non-volatile computer-readable storage medium. When the computer program is executed, it can include the processes of the embodiments of the above-mentioned methods. Among them, any reference to memory, storage, database or other media used in the embodiments provided in this application can include non-volatile and / or volatile memory. Non-volatile memory can include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM) or flash memory. Volatile memory can include random access memory (RAM) or external cache memory. As an illustration and not limitation, RAM is available in many forms, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), double data rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), synchronous link (Synchlink) DRAM (SLDRAM), memory bus (Rambus) direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM).

[0139] The technical features of the above embodiments may be arbitrarily combined. To make the description concise, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.

[0140] The above-mentioned embodiments only express several implementation methods of the present application, and the descriptions thereof are relatively specific and detailed, but they cannot be understood as limiting the scope of the invention patent. It should be pointed out that, for a person of ordinary skill in the art, several variations and improvements can be made without departing from the concept of the present application, and these all belong to the protection scope of the present application. Therefore, the protection scope of the patent of the present application shall be subject to the attached claims.

Claims

1. A data synchronization method, characterized in that: The method comprises: Receiving a user's request information for data synchronization, wherein the request information includes field information of data to be analyzed online by the user; Obtaining a fact table and a dimension table corresponding to the field information, and left-joining the fact table and the dimension table to obtain a widened table corresponding to the field information; Performing a mapjoin operation on the tilted data in the widened table to obtain an optimized widened table; Classify the data in the optimized widened table according to the accounting time, and save the classified data to the partition corresponding to the distributed file system HDFS cluster to obtain the partitioned data; The partitioned data is written into a temporary table of the column-based database management unit Clickhouse, and data synchronization is performed, specifically including: counting the data volume of the temporary table and the optimized widened table to obtain the data volume of the temporary table and the data volume of the widened table; When the data volume of the temporary table is consistent with the data volume of the optimized widened table, the partitioned data in the temporary table is written into the main table of Clickhouse to complete data synchronization.

2. The method according to claim 1, characterized in that The method further comprises: When the data volume of the temporary table is inconsistent with the data volume of the widened table, the partitioned data is rewritten into the temporary table of Clickhouse to obtain a rewritten temporary table; When the amount of data in the rewritten temporary table is consistent with the amount of data in the optimized widened table, the partitioned data in the rewritten temporary table is written into the main table of Clickhouse to complete data synchronization.

3. The method according to claim 1, characterized in that The method further comprises: When the data volume of the temporary table is consistent with the data volume of the optimized widened table, after the partitioned data in the temporary table is written into the main table of Clickhouse, the first status information of the data in the main table is recorded as the submission status.

4. The method according to claim 2, characterized in that: The method further comprises: When the data volume of the temporary table is inconsistent with the data volume of the widening table, obtaining meta information and execution information of the temporary table; The second state information of the data in the temporary table is recorded as a pre-commit state; The meta information, the execution information and the second state information are saved in a relational database management system MySQL.

5. The method according to claim 1, characterized in that The method further comprises: Get the first fact table and offset table to be synchronized within the preset time; Multiplying the metric data in the first fact table by a preset value to obtain a preliminary fact table; Adding the measurement data in the preliminary fact table and the offset table to obtain a fact offset table; Expand the fact offset table and perform data synchronization.

6. A data synchronization device, characterized in that: The device comprises: A receiving module, used to receive a user's request information for data synchronization, wherein the request information includes field information of data to be analyzed online by the user; A splicing module, used for obtaining a fact table and a dimension table corresponding to the field information, and left-splicing the fact table and the dimension table to obtain a widened table corresponding to the field information; An optimization module, used for performing a mapjoin operation on the tilted data in the widening table to obtain an optimized widening table; A partitioning module is used to classify the data in the optimized widened table according to the accounting time, and save the classified data to the partition corresponding to the distributed file system HDFS cluster to obtain partitioned data; A synchronization module is used to write the partitioned data into a temporary table of the column-based database management unit Clickhouse to perform data synchronization, wherein the synchronization module is specifically used to count the data volume in the temporary table and the optimized widened table to obtain the data volume of the temporary table and the widened table; when the data volume of the temporary table is consistent with the data volume of the optimized widened table, the partitioned data in the temporary table is written into the main table of Clickhouse to complete data synchronization.

7. A computer device comprising a memory, a processor and a computer program stored in the memory and executable on the processor, characterized in that: When the processor executes the computer program, the steps of the method according to any one of claims 1 to 5 are implemented.

8. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the steps of the method according to any one of claims 1 to 5 are implemented.

Images