A multi-source data fusion method, device and electronic equipment

Abstract

Embodiments of the present application disclose a multi-source data fusion method and device and electronic equipment, comprising: creating a detail table of the to-be-fused data in a columnar database based on each field contained in each of a plurality of data sources to be fused, the detail table including all fields of the plurality of data sources; adding data contained in the plurality of data sources to the columnar database according to the fields contained in the detail table; and performing light aggregation on the data in the columnar database according to business logic related to the data contained in the plurality of data sources to obtain a plurality of aggregation result sets. By using the present scheme, the adverse effect on the original database during multi-source data fusion is reduced.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database, and in particular to a multi-source data fusion method and device and electronic equipment. BACKGROUND

[0002] In actual project application based on a database, with new requirements and functions being continuously added and improved, different data sources need to be connected respectively in the first phase, the second phase and the third phase of a project, and thus data fusion is obviously necessary.

[0003] Currently, a common practice is to transform and extend a database of the first phase of a project (a database storing data of the first phase of the project) on the basis of the database, which is feasible but also causes some problems, for example, the structure of the database of the first phase of the project needs to be transformed, and some unpredictable bugs (vulnerabilities) may be generated in the transformation process, affecting the stability of the database of the first phase of the project; meanwhile, data of the second phase and the third phase of the project are connected to the database of the first phase of the project, which brings great pressure to the database of the first phase of the project, thereby slowing down the query speed of the database of the first phase of the project. SUMMARY

[0004] Therefore, the embodiments of the present application provide a multi-source data fusion method, device and electronic equipment to solve the problem of adverse effects on the original database in the prior art when multi-source data fusion is performed.

[0005] In a first aspect, the embodiments of the present application provide a multi-source data fusion method, comprising:

[0006] creating a detail table of to-be-fused data in a columnar database based on each field contained in a plurality of to-be-fused data sources, wherein the detail table includes all fields of the plurality of data sources;

[0007] adding data contained in the plurality of data sources to the columnar database according to the fields contained in the detail table;

[0008] performing light aggregation on the data in the columnar database according to business logic related to the data contained in the plurality of data sources to obtain a plurality of aggregation result sets.

[0009] According to a specific implementation manner of the embodiments of the present application, the creating of the detail table of to-be-fused data in the columnar database based on each field contained in the plurality of to-be-fused data sources comprises:

[0010] According to the correlation between the fields included in the plurality of data sources to be fused, a plurality of detail tables of the data to be fused are created in the columnar database, wherein the correlation between the fields included in each of the detail tables satisfies a preset correlation condition, and the plurality of detail tables include all the fields of the plurality of data sources.

[0011] According to a specific implementation manner of an embodiment of the present application, the data in the columnar database is lightly aggregated according to the business logic related to the data included in the plurality of data sources, including:

[0012] According to the business logic related to the data included in the plurality of data sources, the data included in each of the detail tables in the columnar database is lightly aggregated.

[0013] According to a specific implementation manner of an embodiment of the present application, the method further includes:

[0014] Obtaining a data query condition;

[0015] From the plurality of aggregation result sets, an aggregation result set matched with the data query condition is selected as a to-be-queried aggregation result set;

[0016] From the to-be-queried aggregation result set, data is queried according to the data query condition.

[0017] According to a specific implementation manner of an embodiment of the present application, the columnar database is a ClickHouse columnar database.

[0018] According to a specific implementation manner of an embodiment of the present application, the plurality of aggregation result sets are a plurality of materialized view result sets.

[0019] In a second aspect, an embodiment of the present application further provides a multi-source data fusion device, including:

[0020] A detail table creation module is configured to create, based on the fields included in the plurality of data sources to be fused, a detail table of the data to be fused in a columnar database, wherein the detail table includes all the fields of the plurality of data sources.

[0021] A data addition module is configured to add, according to the fields included in the detail table, the data included in the plurality of data sources to the columnar database.

[0022] A data aggregation module is configured to lightly aggregate the data in the columnar database according to the business logic related to the data included in the plurality of data sources, to obtain a plurality of aggregation result sets.

[0023] According to a specific implementation manner of the embodiment of the present application, the detail table creation module is specifically configured to create a plurality of detail tables of the data to be fused in the columnar database according to the correlation between the fields included in the plurality of data sources to be fused, wherein the correlation between the fields included in each detail table satisfies a preset correlation condition, and the plurality of detail tables include all the fields of the plurality of data sources.

[0024] According to a specific implementation manner of the embodiment of the present application, the data summarization module is specifically configured to perform light summarization on the data included in each detail table in the columnar database according to the business logic related to the data included in the plurality of data sources.

[0025] According to a specific implementation manner of the embodiment of the present application, the method further comprises:

[0026] The condition acquisition module is configured to acquire a data query condition.

[0027] The result set selection module is configured to select a summarization result set matched with the data query condition from the plurality of summarization result sets as a to-be-queried summarization result set.

[0028] The data query module is configured to perform data query according to the data query condition from the to-be-queried summarization result set.

[0029] According to a specific implementation manner of the embodiment of the present application, the columnar database is a ClickHouse columnar database.

[0030] According to a specific implementation manner of the embodiment of the present application, the plurality of summarization result sets are a plurality of materialized view result sets.

[0031] In a third aspect, an electronic device is provided, and the electronic device comprises a housing, a processor, a memory, a circuit board and a power supply circuit, wherein the circuit board is arranged inside a space enclosed by the housing, the processor and the memory are arranged on the circuit board; the power supply circuit is configured to supply power to each circuit or device of the electronic device; the memory is configured to store executable program codes; and the processor is configured to run programs corresponding to the executable program codes by reading the executable program codes stored in the memory, and execute the method according to any of the preceding implementation manners.

[0032] In a fourth aspect, a computer readable storage medium is provided, and the computer readable storage medium stores one or more programs, and the one or more programs can be executed by one or more processors to implement the method according to any of the embodiments of the present application.

[0033] In a fifth aspect, embodiments of the present application also provide an application program, which is executed to implement the method according to any of the embodiments of the present application.

[0034] In the multi-source data fusion method provided by the embodiments of the present application, based on each field contained in the multiple data sources to be fused, a detail table of the data to be fused is created in a column database, the detail table including all fields of the multiple data sources; data contained in the multiple data sources is added to the column database according to the fields contained in the detail table; and the data in the column database is subjected to light aggregation according to business logic related to the data contained in the multiple data sources, to obtain multiple aggregation result sets, thereby realizing fusion of the data of the multiple data sources. In the process of data fusion, the structure of the original database does not need to be changed, and no new data is added to the original database, so that the stability of the original database is not affected, new bugs are avoided, and the data query speed for the original database is not reduced, i.e., the adverse effects on the original database are reduced. BRIEF DESCRIPTION OF DRAWINGS

[0035] In order to more clearly illustrate the technical solutions in the embodiments of the present application or the prior art, the drawings needed to be used in the embodiments or the prior art description will be briefly introduced. Obviously, the drawings in the following description are only some embodiments of the present application, and for those skilled in the art, other drawings can also be obtained without creative labor based on these drawings.

[0036] Figure 1 A flowchart of the multi-source data fusion method provided by the embodiments of the present application;

[0037] Figure 2 A schematic diagram of the multi-source data fusion method provided by the embodiments of the present application;

[0038] Figure 3 A flowchart of the multi-source data fusion method provided by another embodiment of the present application;

[0039] Figure 4-1 A structural schematic diagram of the multi-source data fusion device provided by the embodiments of the present application;

[0040] Figure 4-2 A structural schematic diagram of the multi-source data fusion device provided by another embodiment of the present application;

[0041] Figure 5 A structural schematic diagram of the electronic device provided by the embodiments of the present application. DETAILED DESCRIPTION

[0042] The embodiments of the present invention will now be described in detail with reference to the accompanying drawings. It should be understood that the described embodiments are merely some, not all, of the embodiments of the present invention. All other embodiments obtained by those skilled in the art based on the embodiments of the present invention without creative effort are within the scope of protection of the present invention.

[0043] This invention provides a multi-source data fusion method, such as... Figure 1 As shown, it includes:

[0044] Step 11: Based on the fields contained in the multiple data sources to be merged, create a detail table of the data to be merged in the columnar database. The detail table includes all fields from the multiple data sources.

[0045] Step 12: Add the data from multiple data sources to the columnar database according to the fields contained in the detail table;

[0046] Step 13: Based on the business logic related to the data contained in multiple data sources, perform a light summary of the data in the columnar database to obtain multiple summary result sets.

[0047] The multi-source data fusion method provided in this embodiment of the invention does not require changing the structure of the original database or adding new data to the original database during the fusion of data from multiple data sources. Therefore, it does not affect the stability of the original database, avoids the generation of new bugs, and does not reduce the data query speed of the original database, thus reducing the adverse effects on the original database.

[0048] The methods and apparatus provided in the embodiments of the present invention will now be described in detail with reference to the accompanying drawings.

[0049] Another embodiment of the present invention also provides a multi-source data fusion method, the schematic diagram of which is shown below. Figure 2 As shown, taking three data sources as an example, namely PostgreSQL, Oracle, and Hadoop, these three data sources are located in the Operation Data Store. The multi-source data fusion method provided in this embodiment of the invention is used to fuse the data from these three data sources. The process is as follows: Figure 3 As shown, it may include the following steps:

[0050] Step 31: Based on the fields contained in the multiple data sources to be merged, create multiple detail tables of the data to be merged in the columnar database according to the correlation between the fields contained in the multiple data sources. The correlation between the multiple fields contained in each detail table meets the preset correlation conditions. The multiple detail tables include all fields of the multiple data sources.

[0051] Each data source can include data of multiple fields, in order to realize fusion of all data of multiple data sources, in this step, all fields of multiple data sources are included in multiple detail tables created in the columnar database.

[0052] In this step, the preset related conditions of the fields can be flexibly set based on actual business requirements, multiple detail tables are created, and each field meeting the preset related conditions is placed in a detail table, which is beneficial to subsequent extraction and use of data in each detail table.

[0053] In the embodiment of the application, the same fields can be included in different detail tables.

[0054] In the embodiment of the application, the columnar database used can be a ClickHouse columnar database.

[0055] As shown in Figure 2 Based on three data sources, two ClickHouse detail tables, ClickHouse detail table 1 and ClickHouse detail table 2, are created, and the two ClickHouse detail tables are located in the detail layer (Data Warehouse Detail).

[0056] Step 32, data contained in multiple data sources is added to the columnar database according to the fields contained in the detail table.

[0057] In this step, an ETL (Extract-Transform-Load, data extraction, transformation and loading) tool, such as a Kettle tool, can be used to connect the PostgreSQL, Oracle and Hadoop three data sources respectively, and SQL is used to fuse the data of the three data sources to the detail table of the ClickHouse columnar database.

[0058] After this step is performed, Figure 2 As shown in the detail layer,

[0059] Step 33, according to the business logic related to the data contained in the multiple data sources, the data contained in the columnar database in each detail table is slightly summarized to obtain multiple summary result sets.

[0060] The multiple summary result sets obtained by slightly summarizing the data are beneficial to subsequent data query requirements and improve the query speed.

[0061] The business logic related to the data contained in the plurality of data sources can be flexibly set based on actual business requirements and characteristics of the data, and is not specifically limited here.

[0062] For the ClickHouse columnar database, a plurality of materialized view result sets can be obtained after the data is lightly aggregated, so as to further improve the query speed in subsequent data query.

[0063] As shown in Figure 2 After the data of the two ClickHouse detail tables of the detail layer is lightly aggregated, n aggregation result sets are obtained, and the n aggregation result sets are located in the light aggregation layer (Data Warehouse Service).

[0064] Based on the fusion of the data of the three data sources and the aggregation result sets obtained by light aggregation, as shown in Figure 2 The application layer (Application Data Store) can provide data query services to users, which can include the following steps:

[0065] Step 34, obtaining a data query condition.

[0066] The data query condition can be input by a user based on a front-end display page.

[0067] Step 35, selecting an aggregation result set matching the data query condition from the plurality of aggregation result sets as a to-be-queried aggregation result set.

[0068] The aggregation result set is obtained by light aggregation of the data based on business logic, and the data query condition also has a relationship with the business logic, so in this step, the aggregation result set matching the data query condition can be selected from the plurality of aggregation result sets.

[0069] Step 36, querying data from the to-be-queried aggregation result set according to the data query condition.

[0070] In the embodiment of the application, when the data in a certain data source is updated, the data can be updated to the detail table in the detail layer first, and then updated to the aggregation result set in the light aggregation layer after light aggregation.

[0071] Based on the same inventive concept, according to the above multi-source data fusion method provided by the embodiment of the application, the embodiment of the application further provides a multi-source data fusion device, as shown in Figure 4-1 which includes:

[0072] The detail table creation module 41 is configured to create a detail table of the data to be fused in the columnar database based on fields included in the multiple data sources to be fused, and the detail table includes all fields of the multiple data sources.

[0073] The data addition module 42 is configured to add data included in the multiple data sources to the columnar database according to fields included in the detail table.

[0074] The data summarization module 43 is configured to perform light summarization on the data in the columnar database according to business logic related to the data included in the multiple data sources to obtain multiple summary result sets.

[0075] According to a specific implementation manner of the embodiment of the present application, the detail table creation module 41 is specifically configured to create multiple detail tables of the data to be fused in the columnar database based on fields included in the multiple data sources to be fused and according to correlations between the fields included in the multiple data sources, wherein the correlations between the multiple fields included in each detail table satisfy a preset correlation condition, and the multiple detail tables include all fields of the multiple data sources.

[0076] According to a specific implementation manner of the embodiment of the present application, the data summarization module 43 is specifically configured to perform light summarization on the data included in each detail table in the columnar database according to the business logic related to the data included in the multiple data sources.

[0077] According to a specific implementation manner of the embodiment of the present application, as shown in Figure 4-2 , the apparatus further includes:

[0078] The condition acquisition module 44 is configured to acquire a data query condition.

[0079] The result set selection module 45 is configured to select a summary result set matched with the data query condition from the multiple summary result sets as a to-be-queried summary result set.

[0080] The data query module 46 is configured to perform data query on the to-be-queried summary result set according to the data query condition.

[0081] According to a specific implementation manner of the embodiment of the present application, the columnar database is a ClickHouse columnar database.

[0082] According to a specific implementation manner of the embodiment of the present application, the multiple summary result sets are multiple materialized view result sets.

[0083] The apparatus of the embodiment can be used to execute the technical solution of the method embodiment shown in Figures 1-3 , and has similar implementation principles and technical effects, which will not be described herein again.

[0084] The embodiment of the present application also provides an electronic device comprising the device of any one of the preceding embodiments.

[0085] Figure 5 A structural schematic diagram of an embodiment of the electronic device of the present application can realize the present application Figures 1-3 The flow of the embodiment shown in the figure, as Figure 5 The electronic device can comprise a housing 51, a processor 52, a memory 53, a circuit board 54 and a power supply circuit 55, wherein the circuit board 54 is arranged inside a space enclosed by the housing 51, the processor 52 and the memory 53 are arranged on the circuit board 54; the power supply circuit 55 is used for supplying power to each circuit or device of the electronic device; the memory 53 is used for storing executable program codes; the processor 52 runs programs corresponding to the executable program codes by reading the executable program codes stored in the memory 53, and is used for executing the multi-source data fusion method of any one of the preceding embodiments.

[0086] The specific execution process of the processor 52 on the above steps and the steps further executed by the processor 52 by running the executable program codes can be referred to the description of the embodiment shown in the figure, and will not be repeated here. Figures 1-3

[0087] The electronic device exists in various forms, including but not limited to:

[0088] (1) Mobile communication device: the feature of this kind of device is to have mobile communication function and to provide voice and data communication as the main target. This kind of terminal includes smart phone (such as iPhone), multimedia phone, functional phone and low-end phone, etc.

[0089] (2) Ultra-mobile personal computer device: this kind of device belongs to the category of personal computer and has computing and processing functions, and generally has the feature of mobile Internet. This kind of terminal includes PDA, MID and UMPC device, such as iPad.

[0090] (3) Portable entertainment device: this kind of device can display and play multimedia content. This kind of device includes audio and video player (such as iPod), palm game machine, electronic book, smart toy and portable car navigation device.

[0091] (4) Server: a device providing computing service, the structure of the server includes processor, hard disk, memory, system bus, etc. The server is similar to general computer architecture, but since it needs to provide high-reliability service, it has higher requirements in processing capability, stability, reliability, security, scalability and manageability.

[0092] (5) Other electronic devices with data interaction function.​

[0093] The embodiment of the present application further provides a computer readable storage medium, which stores one or more programs, and the one or more programs are executable by one or more processors to implement the method according to any of the embodiments of the present application.

[0094] The embodiment of the present application further provides an application program, which is executed to implement the method according to any of the embodiments of the present application.

[0095] It should be noted that, in the present document, the relationship terms such as first and second are merely used to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply that there is any such actual relationship or order between these entities or operations. Moreover, the terms "include", "contain" or any other variants thereof are intended to cover non-exclusive inclusion, so that the process, method, article or equipment including a series of elements not only includes those elements, but also includes other elements not explicitly listed or inherent to such process, method, article or equipment. Without more limitation, the element defined by the statement "including a" does not exclude the presence of another identical element in the process, method, article or equipment including the element.

[0096] Each of the embodiments in the present specification is described in a relevant manner, and the same or similar parts between each of the embodiments can be referred to each other, and each of the embodiments mainly explains the difference from other embodiments.

[0097] Especially, for the device embodiments, since they are basically similar to the method embodiments, the description is relatively simple, and the relevant parts can be referred to the part of the description of the method embodiments.

[0098] For the convenience of description, the above device is described in various units / modules respectively according to functions. Of course, in the implementation of the present application, the functions of each unit / module can be implemented in the same or multiple software and / or hardware.

[0099] Those skilled in the art can understand that all or part of the processes in the above-mentioned embodiments can be completed by a computer program instructing relevant hardware, and the program can be stored in a computer readable storage medium. When the program is executed, the processes of the above-mentioned embodiments can be included. The storage medium can be a magnetic disc, an optical disc, a read-only memory (ROM) or a random access memory (RAM) and the like.

[0100] The above merely illustrates the specific embodiments of the present application, but the protection scope of the present application is not limited thereto, any person skilled in the art can easily think of the changes or replacements within the technical range disclosed by the present application, which should be covered in 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 multi-source data fusion method, characterized in that, The method comprises the following steps: creating a detail table of the data to be fused in a columnar database based on each field contained in a plurality of data sources to be fused, the detail table being a table in which fields meeting preset correlation conditions are placed, and the detail table including all fields of the plurality of data sources; adding data contained in the plurality of data sources to the columnar database according to fields contained in the detail table; performing light aggregation on data in the columnar database according to business logic related to the data contained in the plurality of data sources to obtain a plurality of aggregation result sets; wherein the step of creating the detail table of the data to be fused in the columnar database based on each field contained in the plurality of data sources to be fused comprises: creating a plurality of detail tables of the data to be fused in the columnar database based on each field contained in the plurality of data sources to be fused and according to correlations between the fields contained in the plurality of data sources, wherein the correlations between the fields contained in each detail table meet preset correlation conditions, and the plurality of detail tables include all fields of the plurality of data sources.

2. The multi-source data fusion method of claim 1, wherein, The step of performing light aggregation on data in the columnar database according to business logic related to the data contained in the plurality of data sources comprises: performing light aggregation on data in the columnar database according to business logic related to the data contained in the plurality of data sources, respectively for each detail table.

3. The multi-source data fusion method of claim 1, wherein, The method further comprises the following steps: obtaining a data query condition; selecting an aggregation result set matching the data query condition from the plurality of aggregation result sets as a to-be-queried aggregation result set; performing data query on the to-be-queried aggregation result set according to the data query condition.

4. The multi-source data fusion method according to any one of claims 1-3, characterized in that, The columnar database is a ClickHouse columnar database.

5. The multi-source data fusion method according to any one of claims 1-3, characterized in that, The plurality of aggregation result sets are a plurality of materialized view result sets.

6. A multi-source data fusion device, characterized by comprising: The method comprises the following steps: creating a detail table of the data to be fused in a columnar database based on each field contained in a plurality of data sources to be fused, the detail table being a table in which fields meeting preset correlation conditions are placed, and the detail table including all fields of the plurality of data sources; adding data contained in the plurality of data sources to the columnar database according to fields contained in the detail table; performing light aggregation on data in the columnar database according to business logic related to the data contained in the plurality of data sources to obtain a plurality of aggregation result sets; wherein the step of creating the detail table of the data to be fused in the columnar database based on each field contained in the plurality of data sources to be fused comprises:

7. The multi-source data fusion apparatus according to claim 6, wherein creating a plurality of detail tables of the data to be fused in the columnar database based on each field contained in the plurality of data sources to be fused and according to correlations between the fields contained in the plurality of data sources, wherein the correlations between the fields contained in each detail table meet preset correlation conditions, and the plurality of detail tables include all fields of the plurality of data sources.

8. The multi-source data fusion apparatus according to claim 6, wherein The step of performing light aggregation on data in the columnar database according to business logic related to the data contained in the plurality of data sources comprises: performing light aggregation on data in the columnar database according to business logic related to the data contained in the plurality of data sources, respectively for each detail table. The method further comprises the following steps: obtaining a data query condition; selecting an aggregation result set matching the data query condition from the plurality of aggregation result sets as a to-be-queried aggregation result set; performing data query on the to-be-queried aggregation result set according to the data query condition. The columnar database is a ClickHouse columnar database. The plurality of aggregation result sets are a plurality of materialized view result sets. The method comprises the following steps: creating a detail table of the data to be fused in a columnar database based on each field contained in a plurality of data sources to be fused, the detail table being a table in which fields meeting preset correlation conditions are placed, and the detail table including all fields of the plurality of data sources; adding data contained in the plurality of data sources to the columnar database according to fields contained in the detail table; performing light aggregation on data in the columnar database according to business logic related to the data contained in the plurality of data sources to obtain a plurality of aggregation result sets; wherein the step of creating the detail table of the data to be fused in the columnar database based on each field contained in the plurality of data sources to be fused comprises: creating a plurality of detail tables of the data to be fused in the columnar database based on each field contained in the plurality of data sources to be fused and according to correlations between the fields contained in the plurality of data sources, wherein the correlations between the fields contained in each detail table meet preset correlation conditions, and the plurality of detail tables include all fields of the plurality of data sources. The step of performing light aggregation on data in the columnar database according to business logic related to the data contained in the plurality of data sources comprises: performing light aggregation on data in the columnar database according to business logic related to the data contained in the plurality of data sources, respectively for each detail table. The method further comprises the following steps: A condition obtaining module is configured to obtain a data query condition. A result set selection module is configured to select, from the multiple summary result sets, a summary result set matching the data query condition as a to-be-queried summary result set. A data query module is configured to perform data query on the to-be-queried summary result set according to the data query condition.

9. The multi-source data fusion apparatus according to any one of claims 6-8, characterized in that, The columnar database is a ClickHouse columnar database.

10. The multi-source data fusion apparatus according to any one of claims 6-8, characterized in that, The multiple summary result sets are multiple materialized view result sets.

11. An electronic device, comprising: The electronic device comprises a shell, a processor, a memory, a circuit board and a power supply circuit, wherein the circuit board is arranged inside a space enclosed by the shell, the processor and the memory are arranged on the circuit board; the power supply circuit is configured to supply power to each circuit or device of the electronic device; the memory is configured to store executable program codes; the processor is configured to run programs corresponding to the executable program codes by reading the executable program codes stored in the memory, and the programs are configured to execute the method of any one of claims 1-5.

12. A computer-readable storage medium, characterized in that, The computer readable storage medium stores one or more programs, and the one or more programs are executable by one or more processors to implement the method of any one of claims 1-5.

Images