Self-service real-time data comparison method and system based on MPP database

Abstract

The present application relates to the technical field of data processing, in particular to a self-service real-time data comparison method and system based on MPP database, by constructing comparison parameters of source data and target data in user comparison requirements, and caching the source data and comparison parameters, the comparison parameters include comparison reference fields and comparison information items; the source data is loaded to a large-scale parallel processing architecture MPP database, and the comparison parameters are used to obtain source data and target data comparison results in the large-scale parallel processing architecture MPP database and output. The present application uses a distributed MPP database to replace a traditional big data database component, which can greatly improve the data comparison processing speed in the MPP database, achieve near real-time data comparison of business big data, and enable business personnel to quickly obtain to-be-processed data comparison results through operation of a Web interactive interface page without any code or SQL capability, greatly reduce the technical threshold, and facilitate implementation and application.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of data processing, in particular to a self-service real-time data comparison method and system based on an MPP database. BACKGROUND

[0002] With the development of computer technology, not only the data in the Internet is growing explosively, but also the data in many large business systems has increased significantly compared with the past. Moreover, not only the data volume is increasing, but also with the popularity of micro-service architecture, there may be numerous data sources in a large system, and these data sources may be composed of multiple heterogeneous data sources, such as a system containing MySQL / PostgresSQL databases, Excel files and other different data sources to store business data. In some business scenarios with data analysis requirements, it is often necessary to compare the data managed in these systems, and even to receive external data and system data for collision.

[0003] Currently, the common technologies for heterogeneous data comparison are as follows: 1. Through data synchronization tools to a big data platform such as HDFS, using the support of big data warehouse for SQL, such as Hive, SparkSQL, using SQL to perform comparison calculation, as shown in Figure 2 Due to the characteristics of big data components, the data calculation task scheduling is relatively heavy and time-consuming. If the data to be compared needs to be processed and calculated, an offline task needs to be scheduled for pre-calculation, and the timeliness will be compromised. Business personnel who do not know SQL cannot use it. 2. The data to be compared is queried out and preloaded into memory for comparison calculation through a script, as shown in Figure 3 The script can be a Python+Pandas script written by a technical personnel, or a script generated by a visual tool such as Kettle. In any case, it cannot be directly compared by a SQL, and it cannot be directly operated by a business personnel. For newly added data sources and external data, the existing script cannot be reused, resulting in difficult script management. This common processing method needs to query all the data of the comparison target each time, and then perform comparison calculation in the single machine memory of the script. When the data volume is large, memory overflow often occurs, resulting in comparison failure. Therefore, a self-service quasi-real-time data comparison scheme is urgently needed to meet the needs of business personnel. SUMMARY

[0004] Therefore, the present application provides a self-service real-time data comparison method and system based on an MPP database, which can solve the timeliness and user comparison data technical threshold requirements when comparing external data and big data, and is easy to implement.

[0005] According to the design scheme provided by the application, a self-service real-time data comparison method based on an MPP database is provided, comprising:

[0006] Comparison parameters of source data and target data in a user comparison requirement are constructed, and the source data and the comparison parameters are cached, the comparison parameters comprising a comparison reference field and comparison information items;

[0007] The source data is loaded into an MPP database of a large-scale parallel processing architecture, and the source data and target data are compared in the MPP database of the large-scale parallel processing architecture by using the comparison parameters and output, wherein the target data is loaded into the MPP database of the large-scale parallel processing architecture through a data processing process after change data capture.

[0008] As the self-service real-time data comparison method based on the MPP database of the application, further, comparison parameters of source data and target data in a user comparison requirement are constructed, and the source data and the comparison parameters are cached, comprising:

[0009] First, the comparison parameters for comparing the source data and the target data are generated according to the fields and information items selected by the user in the user interaction interface;

[0010] Then, the source data uploaded by the user is uploaded to the object storage component service for transfer storage, and the comparison parameters of the current comparison task are stored by using a task message queue.

[0011] As the self-service real-time data comparison method based on the MPP database of the application, further, when the source data uploaded by the user is uploaded to the object storage component service for transfer storage, the source data uploaded by the user is uploaded to the object storage component service by calling an object storage service OSS interface, and the ID information of the source data in the object storage component service is obtained.

[0012] As the self-service real-time data comparison method based on the MPP database of the application, further, the source data is loaded into the MPP database of the large-scale parallel processing architecture, and the target data loaded into the MPP database through CDC real-time processing, the comparison results of the source data and the target data are obtained in the MPP database of the large-scale parallel processing architecture by using the comparison parameters, and the comparison results are output, comprising:

[0013] First, the business data is loaded into the MPP database of the large-scale parallel processing architecture as target data after being processed in real time by CDC, and the comparison task is used at any time, and this action is performed at all times regardless of whether there is a comparison requirement or not;

[0014] Then, the comparison task source data and the comparison task configuration information are acquired and loaded into the script machine memory, and the source data and the target data item are embedded into the MPP database SQL statement according to the comparison reference field in the configuration information, and the SQL query clause is associated with other forms;

[0015] Then, the comparison information item is mapped into the form clause according to the mapping relationship between the data table in the MPP database and the comparison information item, and the comparison information item and the query condition corresponding to the comparison information item are embedded into the SQL statement to generate a subquery corresponding to the form;

[0016] Then, the subquery of all forms and the source data and the target data item embedded in the MPP database are associated by the primary key to generate a final SQL query statement;

[0017] Finally, the MPP database is used to execute the final SQL query statement to return the comparison result of the source data and the target data and output.

[0018] As the self-service real-time data comparison method based on the MPP database of the present application, further, the source data and the target data are embedded into the MPP database SQL statement according to the comparison reference field, comprising:

[0019] It is judged whether the data amount of the source data and the target data is less than the preset threshold upper limit, if less than the preset threshold upper limit, the source data and the target data are embedded into the MPP database through the comparison reference field and the embedded SQL statement is generated, all data embedded SQL statements are combined to form a subquery of the SQL statement, if higher than the preset threshold upper limit, the source data is embedded into the MPP database in batches, and a data ID is allocated in each batch until all the source data is embedded into the MPP database through the comparison reference field.

[0020] As the self-service real-time data comparison method based on the MPP database of the present application, further, the MPP database is used to execute the final SQL query statement to return the comparison result of the source data and the target data and output, comprising:

[0021] First, the MPP database is used to execute the final SQL query statement and obtain the data result of the query;

[0022] Then, the data result of the query, the source data and the target data item are spliced through the reference field to generate the comparison data of the source data and the target data for output.

[0023] As the self-service real-time data comparison method based on the MPP database of the present application, further, before constructing the comparison parameters of the source data and the target data in the user comparison requirement, it further comprises: judging whether the target data is a newly captured data source, if it is a newly captured data source, then configuring the new data source, writing the newly captured data source into the large-scale parallel processing architecture MPP database after preprocessing, and generating candidate options for constructing the comparison benchmark field and the comparison information item in the comparison parameters.

[0024] Further, the present application further provides a self-service real-time data comparison system based on the MPP database, comprising: a comparison configuration module and a comparison output module, wherein,

[0025] The comparison configuration module is used for constructing the comparison parameters of the source data and the target data in the user comparison requirement, and buffering the source data and the comparison parameters, and the comparison parameters comprise the comparison benchmark field and the comparison information item;

[0026] The comparison output module is used for loading the source data into the large-scale parallel processing architecture MPP database, and comparing the source data and the target data in the large-scale parallel processing architecture MPP database by using the comparison parameters and outputting, wherein the target data is loaded into the large-scale parallel processing architecture MPP database through the data processing process after the change data capture.

[0027] The present application has the following beneficial effects:

[0028] The present application uses the distributed MPP database to replace the traditional big data database components, which can greatly improve the data comparison processing speed in the MPP database, and achieve the near real-time data comparison of the business big data; and can realize the real-time synchronization of data, and can govern the data ETL in the synchronization process. In addition, aiming at the threshold for business personnel to use big data, according to the business data comparison business requirement, the business personnel can quickly obtain the to-be-processed data comparison result by operating the Web interactive interface page without any code or SQL ability, so that the user can configure the data comparison task by using the mouse operation, which can greatly reduce the technical threshold of the business personnel and facilitate the implementation and application. BRIEF DESCRIPTION OF DRAWINGS

[0029] Figure 1 It is a self-service real-time data comparison flowchart based on the MPP database in the embodiment;

[0030] Figure 2 It is a SQL data comparison calculation flowchart realized by using the data synchronization tool and the big data warehouse to support SQL in the embodiment;

[0031] Figure 3A comparison calculation process schematic for preloading comparison data queried out by scripts in the embodiment into memory;

[0032] Figure 4 A self-service real-time data comparison algorithm principle schematic based on an MPP database in the embodiment;

[0033] Figure 5 A user-side data comparison process schematic in the embodiment;

[0034] Figure 6 A data preprocessing process schematic on the development side in the embodiment;

[0035] Figure 7 A system principle framework schematic in the embodiment. DETAILED DESCRIPTION

[0036] In order to make the purpose, technical solutions and advantages of the present application more clear and explicit, the present application is further described in detail below in combination with the drawings and technical solutions.

[0037] Massively Parallel Processing (MPP) is a parallel processing architecture that disperses tasks to multiple servers and nodes, and after the calculation is completed on each node, the results of each part are aggregated together to obtain the final result. Linear expansion of the system is achieved by increasing the number of nodes. Similarly, small databases are organized and combined into a large database. Among them, the database using the MPP architecture is an MPP database.

[0038] Change Data Capture (CDC) identifies and captures changes made to data in a database (including insertion, update, deletion, etc. of data or data tables), then records these changes in the order of occurrence, and transmits them in real time to downstream processes or systems through a message middleware. In this way, CDC can provide efficient and low-latency data transmission to data warehouses so that information can be converted and delivered to applications for analysis in a timely manner.

[0039] ETL (Extract Transform Load) is a process of extracting data from the source system, transforming it, and loading it into the target system. The purpose is to integrate scattered, disorganized, and non-unified data in an enterprise to provide analysis basis for decision-making.

[0040] Hadoop Distributed File System (HDFS) is a distributed file system suitable for running on commodity hardware, which is a highly fault-tolerant system suitable for deployment on inexpensive machines. HDFS can provide high-throughput data access, which is suitable for large-scale data sets.

[0041] In view of the timeliness and user comparison data technical threshold requirements when existing external data and big data collide, the embodiment of the present application, referring to Figure 1 The present application provides a self-service real-time data comparison method based on an MPP database, which comprises:

[0042] S101, constructing comparison parameters of source data and target data in user comparison requirements, and caching the source data and the comparison parameters, the comparison parameters comprising comparison reference fields and comparison information items;

[0043] S102, loading the source data to a massively parallel processing architecture (MPP) database, and comparing the source data and target data in the MPP database using the comparison parameters and outputting, wherein the target data is loaded to the MPP database through a data processing process after change data capture.

[0044] Traditional big data storage and computing components based on HDFS, such as old versions of Hive, do not support ACID transactions or have large data update overheads, and computing engines such as Hive based on Yarn scheduling have large resource scheduling overheads when responding to new queries, and are difficult to meet real-time query requirements. In the embodiment of the present application, the MPP database is used to replace not only reduce the data write update overhead, but also do not need to wait for resource scheduling, so as to support high-concurrency real-time query requirements. Compared with the Presto real-time query engine based on traditional big data components, it does not introduce more component dependencies, which is beneficial to the overall maintenance of the system and reduces resource costs.

[0045] As a preferred embodiment, further, constructing comparison parameters of source data and target data in user comparison requirements, and caching the source data and the comparison parameters, comprises:

[0046] First, generating comparison parameters for comparing the source data and the target data according to the fields and information items selected by the user in the user interaction interface;

[0047] Then, uploading the source data uploaded by the user to the object storage component service for transfer storage, and storing the comparison parameters of the current comparison task in the task message queue.

[0048] The friendly Web operation page is used as the interactive interface provided to the user, so that the user can configure the data comparison task by using the mouse operation, which greatly reduces, and almost completely eliminates the technical threshold of the user.

[0049] As a preferred embodiment, when the user uploaded source data is uploaded to the object storage component service for transit storage, the user uploaded source data is uploaded to the object storage component service by calling the object storage service OSS interface, and the ID information of the source data and the target data in the object storage component service is obtained.

[0050] Referring to Figure 4 As shown in the figure, in the embodiment, the distributed MPP database is used instead of the traditional big data database component, the real-time synchronization is realized by using the Flink CDC technology, and the data ETL management is performed in the synchronization process. The data model managed in the MPP database is used for data comparison to greatly improve the processing speed, so as to achieve near real-time data comparison of business big data. In addition, in order to reduce the threshold of using big data for business personnel, the system page is designed according to the business data comparison process, so that the business personnel can independently take the external data in hand and quickly get the comparison data result through the operation Web page without any code or SQL ability. Due to the real-time synchronization and preprocessing of data, combined with the high processing capacity of the MPP database distributed architecture, the data comparison efficiency is greatly improved, and the self-service data comparison system page greatly reduces the use threshold of business personnel, and the data comparison operation can be performed without knowing SQL knowledge.

[0051] Further, in the embodiment, the source data is loaded into the large-scale parallel processing architecture MPP database, and the comparison parameters are used to obtain the source data and target data comparison result and output in the large-scale parallel processing architecture MPP database, including:

[0052] Firstly, the business data is loaded into the large-scale parallel processing architecture MPP database after the change data capture and real-time processing, and the comparison task source data is loaded into the script machine memory. The source data and target data items are embedded into the large-scale parallel processing architecture MPP database SQL statement according to the comparison reference field, and the SQL query clause is used for association with other forms. Since the target data is loaded into the large-scale parallel processing architecture MPP database for comparison task at any time after the ETL processing by CDC, only the source data needs to be loaded into the large-scale parallel processing architecture MPP database in the data comparison task preparation.

[0053] Then, according to the mapping relationship between the data table and the comparison information item in the MPP database, the comparison information item is mapped into the form clause; the comparison information item and the query condition corresponding to the comparison information item are embedded into the SQL statement to generate a subquery corresponding to the form;

[0054] Then, the subquery of all forms and the source data and target data embedded in the MPP database are associated by the primary key to generate a final SQL query statement;

[0055] Finally, the MPP database is used to execute the final SQL query statement to return the comparison result of the source data and the target data and output.

[0056] The MPP database is used as a computing engine to replace data calculation in the script, which eliminates the risk of memory overflow caused by single machine performance limitation, provides greater data processing capacity and calculation speed, and reduces the technical threshold of implementing parallel processing in the script. When the source data and the target data item are embedded into the MPP database SQL statement according to the comparison benchmark field, it can be determined whether the data volume of the source data is less than the preset threshold upper limit. If it is less than the preset threshold upper limit, the source data and the target data are embedded into the MPP database through the comparison benchmark field, and the embedded SQL statement is generated. The embedded SQL statements of all data are combined into a subquery of the SQL statement. If it is higher than the preset threshold upper limit, the source data is embedded into the MPP database in batches, and a data ID is allocated in each batch until all the source data is embedded into the MPP database through the comparison benchmark field.

[0057] Referring to Figure 5 As shown in the figure, on the user side, the user can log in to the Web interactive page, and the Web page logic obtains the registered information items from the data comparison meta-database through an interface. The user submits the external data file to be compared through the local file selection control of the Web page. The Web page logic uploads the user's local data file to the OSS by calling the OSS object storage service interface, and obtains the unique ID of the file in the OSS from the interface return result.

[0058] It should be noted that the user continues to select the benchmark field in the data comparison task through the Web page, that is, which column or combination of several columns in the externally provided data file is used as the primary key for data comparison. The Web page displays the obtained information items to the user, and the user can select the information items required in the data comparison task from them, which can be multiple selection, and some information items can be configured with query conditions such as time period limitation and number limitation.

[0059] According to all the selections made by the user, the data comparison task configuration information can be combined in JSON format and published to the MQ message queue through an interface. The developed general data comparison Python script listens to the MQ message queue, and when there is a new message, the Python script receives the message, i.e., the JSON of the task configuration, and starts formal processing of the data comparison task. The Python script records the unique identifier of this task, the "processing" state, and the start time to the data comparison task metadata database to provide the user with the task processing state on the page. The Python script reads the OSS file ID from the task configuration JSON information, obtains the external data file uploaded by the user through the OSS service interface, and loads it into the memory.

[0060] Check whether the data amount in the file is higher than a specified threshold. It should be noted that the threshold size is related to the script machine memory and the SQL length limit setting of the MPP data. In the embodiment, the threshold 1000 can be used.

[0061] If the data amount of the external data is less than the threshold, the Python script uses "Select CAST(<base field specific data>AS Varchar)AS compare_pk" to convert each piece of external data into a SQL statement, and then combines all the data into a subquery by using UNION ALL to combine a subquery, which can be placed in the FROM clause of the SQL and associated with other tables. If the data amount of the external data is greater than or equal to the threshold, the Python script assigns a unique ID to this batch of data, and inserts the base field data into a general temporary table established in advance in the MPP database.

[0062] As a preferred embodiment, further, the large-scale parallel processing architecture MPP database is used to execute a final SQL query statement to return the source data and target data comparison result and output, which includes:

[0063] First, the large-scale parallel processing architecture MPP database is used to execute a final SQL query statement and obtain the data result of the query;

[0064] Then, the data result of the query, the source data, and the target data are spliced through the base field to generate the source data and target data comparison data for output.

[0065] The Python script generates the query SQL of the MPP database according to the information in the task configuration JSON, and generates the query SQL by means of the mapping relationship between the data table and the information item in the MPP, according to the user selection to obtain which information items need to be queried, puts the mapped table into the From clause, puts the information item, i.e. the field, into the Select statement, puts the query condition corresponding to the information item into the Where clause, generates a subquery for each data table, and then performs primary key association on all the subqueries and the external data in step 10 to obtain the final query SQL.

[0066] The Python script submits the generated SQL statement to the MPP data execution, and waits synchronously (Synchronized) for the returned data result. The Python script splices the query returned result and the original external data through the benchmark field to generate the final result and outputs the final result into an Excel file, and then uploads the final result file to the OSS through the OSS service interface, obtains the file ID from the interface return content after success. The Python script modifies the execution result of the task in the comparison task metadata to "completed", and writes the result file ID.

[0067] The user refreshes the page after a certain event through the timing bar and the like on the Web page, and can check the task execution state. When the task execution state is identified as "completed", the page will provide a link for viewing the result file. The user obtains the task result file through the link, i.e. calling the OSS service interface and the task result file ID, and thus completes the whole data comparison task process.

[0068] On the user side, the user only needs to operate the Web interface, and checks the selected comparison data information items through the Web control to complete the data comparison task. The logic implementation is automatically completed by the program, and the user does not need to have SQL knowledge at all.

[0069] Further, before constructing the comparison parameters of the source data and the target data in the user comparison requirement, it further includes: judging whether the source data and the target data are new captured data sources, if the source data and the target data are new captured data sources, then configuring the new data source, writing the preprocessed new captured data source into the MPP database, and generating the candidate options for constructing the comparison benchmark field and the comparison information item in the comparison parameters.

[0070] Referring to Figure 6As shown, on the development side, when a new data source appears in the business, the developer collects the new data source information. The data source is configured through the data source module, including link information, user password, etc. The data of the data source can be streamed into the real-time data processing engine by using the Flink CDCConnector plug-in. The developer develops the Flink task to preprocess the accessed data, including data cleaning, removing dirty data, combining fact tables with dimension tables for wide table, sensitive data desensitization, etc. The source of the Flink task is the streaming data source accessed by the CDC Connector, and the target end (Sink) is the MPP database. The developer submits the Flink task to the Flink cluster for running, and the data of the data source starts to be processed in real time and stored in the database. The developer writes the classification, field, and annotation information of the data items written into the MPP database in the data comparison task metadata database, that is, registers the comparable data basic information items, which are used to provide the comparison information options for the Web operation page.

[0071] By performing incremental data real-time processing on the business data, the time difference between the business data and the big data platform in the traditional architecture is reduced, and the data freshness is improved.

[0072] It should be noted that the process is triggered only when there is a new data source in the business requirement. The production data of the normal business system is processed in real time by the connection channel and the Flink task created in this process, without the intervention of the developer.

[0073] Further, based on the above method, the embodiment of the present application also provides a self-service real-time data comparison system based on an MPP database, comprising: a comparison configuration module and a comparison output module, wherein,

[0074] The comparison configuration module is configured to build comparison parameters of source data and target data in the user comparison requirement, and cache the source data and the comparison parameters. The comparison parameters include comparison reference fields and comparison information items.

[0075] The comparison output module is configured to load the source data into the MPP database of the large-scale parallel processing architecture, and compare and output the source data and the target data by using the comparison parameters in the MPP database of the large-scale parallel processing architecture. The target data is loaded into the MPP database of the large-scale parallel processing architecture through a data processing process after the change data capture.

[0076] The real-time data preprocessing is performed on the business data by using the real-time computing engine, the data comparison calculation speed under large data is improved by using the MPP database, and the operation interface without technical threshold is provided for the user to configure the data comparison task in a self-service manner. Overall, the quasi-real-time data comparison calculation can be realized, and through actual verification test, the time from generating data from the business database to the user being able to query and obtain the final result set in the data comparison can be controlled within 10 seconds to 1 minute.

[0077] Further, the overall design structure of the quasi-real-time self-service data comparison system is as shown in Figure 7 The left side is three modules required when preparing the data comparison basic data, and the right side is the module for the user to perform the data comparison operation.

[0078] The data source module corresponds to multiple data sources, is responsible for creating a closed data source link, querying and extracting data, or listening to database update logs (such as MySQL Binlog), and delivering data in a streaming message format to the real-time processing module.

[0079] The data real-time processing module uses the streaming processing capability of Flink to perform data governance, fact table and dimension table widening, and other processing on the message data delivered by the data source module in real time, and then writes the result table into the MPP database in real time.

[0080] The data storage and calculation module is used to store the basic data required for data comparison and perform data comparison calculation. The distributed concurrent computing capability and online horizontal expansion capability of the MPP database are used to cope with large data volume data comparison tasks. At the same time, the processed basic data items that can be used for comparison are stored in the metadata database for the user to select and use when comparing.

[0081] The user interface operation module is a set of WebUI interactive interfaces designed for the user data comparison process, which provides functions of submitting external data to be compared by the user, selecting comparison reference fields, selecting required comparison information items, and viewing and downloading comparison results. In this module, the user's external data is uploaded to the object storage component service in CSV or Excel file format, and the ObjectID of the file, the comparison reference fields selected by the user in the interface, the comparison information items, and other options form a task configuration JSON, which is sent to the task message queue to trigger asynchronous data comparison processing tasks.

[0082] The task file storage module is used to store and transfer the configuration information and external data of the data comparison tasks submitted by the user. The configuration information is stored in the message queue component, and the external data file is stored in the object file storage service component.

[0083] The asynchronous data comparison task module is used to process the data comparison task submitted by the user. The execution of the comparison task and the feedback of the task result are both asynchronous, so it is called asynchronous data comparison. The specific implementation of the module is that a script always listens to the task message queue. After obtaining the task configuration information from the queue, the external data uploaded by the user to the object file storage service is loaded into the MPP database. Then, according to the task configuration information selected by the user, the SQL for comparing the external data and the basic data is dynamically generated, and is submitted to the MPP database to obtain the comparison result. Finally, the result is output into an Excel file and saved to the object file storage service.

[0084] Each data source is processed in real time and then enters the next step as data comparison basic data. The specific calculation of data comparison is performed in any database conforming to the MPP architecture. The self-service Web interactive interface is based on Web and does not require the user to use any programming technology such as SQL. The user uploads external data and downloads the result file based on the OSS object file storage service. The asynchronous communication is performed through the MQ message queue to deliver JSON information. The specific data comparison calculation SQL statement is dynamically generated according to the data comparison task information configured by the user.

[0085] In the embodiment, the data can be ETLed in the real-time synchronization process through the CDC, and the result can be synchronized into the MPP database of the big data local computing engine in real time. The interactive page is used to allow the business personnel to upload external data and self-select the comparison content option for data comparison. Due to the real-time synchronization and preprocessing of data, and the high processing capacity of the MPP database distributed architecture, the data comparison efficiency is greatly improved. The self-service data comparison system page greatly reduces the use threshold of the business personnel, and the business personnel can perform data comparison operation without knowing SQL knowledge, which is convenient for actual deployment and implementation.

[0086] Unless specifically stated, the relative steps, numerical expressions, and values of the components and steps described in the embodiments do not limit the scope of the present application.

[0087] The embodiments in the specification are described in a progressive manner, and each embodiment focuses on the difference from other embodiments. The same and similar parts between the embodiments can be referred to each other. For the system disclosed in the embodiments, since it corresponds to the method disclosed in the embodiments, the description is relatively simple, and the relevant parts can be referred to the method part.

[0088] The units and method steps of each example described in combination with the embodiments disclosed herein can be realized in electronic hardware, computer software, or a combination of both. In order to clearly illustrate the interchangeability of hardware and software, the components and steps of each example have been generally described above in functional generalities. Whether these functions are performed in hardware or software depends on the specific application and design constraints of the technical solution. Those skilled in the art can use different methods to implement the described functions for each specific application, but such implementation does not exceed the scope of the present application.

[0089] Those skilled in the art can understand that all or part of the steps in the above method can be instructed by a program to complete the relevant hardware, and the program can be stored in a computer readable storage medium, such as a read-only memory, a magnetic disk or an optical disk, etc. Alternatively, all or part of the steps of the above embodiments can also be implemented using one or more integrated circuits, and accordingly, each module / unit in the above embodiments can be implemented in the form of hardware or in the form of a software function module. The present application is not limited to any specific form of combination of hardware and software.

[0090] Finally, it should be noted that: the above-described embodiments are merely specific embodiments of the present application, used to illustrate the technical solutions of the present application, and not to limit it. The protection scope of the present application is not limited thereto. Although the present application has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that any person skilled in the art can make modifications or easily think of changes to the technical solutions recorded in the foregoing embodiments within the technical scope disclosed by the present application, or make equivalent replacements to some of the technical features; and these modifications, changes or replacements do not cause the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of the present application, and should be covered within the protection scope of the present application. Therefore, the protection scope of the present application should be subject to the protection scope of the claims.

Claims

1. A self-service real-time data comparison method based on MPP database, characterized in that, Comprise: Construct the comparison parameters of source data and target data in user comparison requirements, and cache the source data and comparison parameters, the comparison parameters comprise comparison reference fields and comparison information items; Load the source data into the MPP database, and compare the source data and target data in the MPP database using the comparison parameters and output, the target data is loaded into the MPP database through data processing after the change data capture; Wherein, loading the source data into the MPP database, and using the comparison parameters to obtain the comparison results of the source data and target data in the MPP database and output, comprising: First, the business data is loaded into the MPP database as target data after change data capture and real-time processing, and the comparison task source data is loaded into the script machine memory, and the source data and target data items are embedded into the MPP database SQL statement according to the comparison reference fields, and the SQL query clause is associated with other forms; Then, according to the mapping relationship between the data table in the MPP database and the comparison information items, the comparison information items are mapped to the form clause; the comparison information items and the corresponding query conditions of the comparison information items are embedded into the SQL statement and the subquery corresponding to the form is generated; Then, the primary key association of all form subqueries and the source data and target data embedded in the MPP database is performed to generate the final SQL query statement; Finally, the final SQL query statement is executed by the MPP database to return the comparison results of the source data and target data and output.

2. The method for self-service real-time data comparison based on MPP database according to claim 1, characterized in that, Construct the comparison parameters of source data and target data in user comparison requirements, and cache the source data and comparison parameters, comprising: First, generate comparison parameters for comparing the source data and target data according to the fields and information items selected by the user in the user interaction interface; Then, upload the source data uploaded by the user to the object storage component service for transfer storage, and store the comparison parameters of the current comparison task in the task message queue.

3. The method of claim 2, wherein, When uploading the source data uploaded by the user to the object storage component service for transfer storage, the source data uploaded by the user is uploaded to the object storage component service by calling the object storage service OSS interface, and the ID information of the source data in the object storage component service is obtained.

4. The method of claim 1, wherein, Embed the source data and target data into the MPP database SQL statement according to the comparison reference fields, comprising: determining whether the data volume of the source data is less than a preset upper threshold, if less than the preset upper threshold, embedding the source data into the MPP database through comparison of the reference field and generating an embedding SQL statement, combining all the embedding SQL statements of the data into a subquery of the SQL statement, if higher than the preset upper threshold, embedding the source data into the MPP database in batches, and assigning a data ID in each batch until all the source data is embedded into the MPP database through comparison of the reference field.

5. The method of claim 1, wherein, The final SQL query statement is executed by using the MPP database to return the source data and target data comparison results and output, including: First, the final SQL query statement is executed by using the MPP database to obtain the data results of the query; Then, the data results of the query, the source data and the target data are spliced through the reference field to generate the source data and target data comparison data for output.

6. The method of claim 1, wherein the MPP database-based self-service real-time data comparison method is characterized by, Before constructing the comparison parameters of the source data and the target data in the user comparison requirement, it also includes: determining whether the source data and the target data are new captured data sources, if they are new captured data sources, configuring the new captured data sources, writing the preprocessed new captured data sources into the MPP database, and generating candidate options for constructing the comparison reference field and the comparison information item in the comparison parameters.

7. A self-service real-time data comparison system based on MPP database, characterized in that, It includes a comparison configuration module and a comparison output module, wherein, The comparison configuration module is used to construct the comparison parameters of the source data and the target data in the user comparison requirement, and cache the source data and the comparison parameters, the comparison parameters including the comparison reference field and the comparison information item; The comparison output module is used to load the source data into the MPP database, and use the comparison parameters to compare the source data and the target data in the MPP database and output, the target data being loaded into the MPP database through data processing after the change data capture; Wherein, the source data is loaded into the MPP database, and the comparison parameters are used to obtain the source data and target data comparison results in the MPP database and output, including: First, the business data is captured and processed in real time as target data and loaded into the MPP database, and the comparison task source data is loaded into the script machine memory, the source data and target data items are embedded into the MPP database SQL statement according to the comparison reference field, and the SQL query clause is associated with other forms; Then, according to the mapping relationship between the data table and the comparison information item in the MPP database, the comparison information item is mapped to the form clause; the comparison information item and the corresponding query condition of the comparison information item are embedded into the SQL statement and a subquery corresponding to the form is generated. Then, the sub-queries of all forms and the source data and target data embedded in the massively parallel processing architecture MPP database are associated by primary keys to generate a final SQL query statement; Finally, the final SQL query statement is executed by the massively parallel processing architecture MPP database to return the comparison result of the source data and the target data and output.

8. An electronic device, comprising: The computer device comprises a processor, a communication interface, a memory and a communication bus, wherein the processor, the communication interface and the memory communicate with each other through the communication bus; The memory is used for storing a computer program. The processor is used for executing the program stored in the memory, and implements the method in any one of claims 1-6 when the program is executed.

9. A computer-readable storage medium, characterized in that, The computer readable storage medium stores a computer program, and the computer program is executed by the processor to implement the method in any one of claims 1-6. The computer readable storage medium stores a computer program, and the computer program is executed by the processor to implement the method in any one of claims 1-6.

Images