A data warehouse engine

Abstract

The present invention discloses a data warehouse engine, which is divided into a task issuing area and a configuration writing area. The task issuing area includes a manual synchronization module, an automatic synchronization module, a reverse synchronization module, a table storage engine module, a calculation distribution module and an aggregation calculation module. The configuration writing area includes a calculation tree module and an aggregation table configuration module. The table storage engine module is used to manage data warehouse data. Users input relevant information in the manual synchronization module and the aggregation table configuration module, and the remaining modules can complete the automatic processing of the calculation task. The present invention adopts the data warehouse engine of the above structure, and the calculation task processing efficiency is high and the calculation is safer.

Description

Technical Field

[0001] The present invention relates to the technical field of data storage, and in particular to a data warehouse engine. Background Art

[0002] Data warehouse is a strategic collection of all types of data support for decision-making processes at all levels of the enterprise. As the scale of the enterprise grows, the capacity of the data warehouse is also expanding, requiring a large amount of computer resources to manage. Often, data warehouse resources cannot be properly managed and controlled, which ultimately leads to a waste of computer resources and even leaves loopholes for hackers to attack. Summary of the invention

[0003] The purpose of the present invention is to provide a data warehouse engine to solve the problems of chaotic data warehouse management, unsafe operation and low efficiency.

[0004] To achieve the above object, the present invention provides the following technical solutions:

[0005] A data warehouse engine is divided into a task issuing area and a configuration writing area. The task issuing area includes a manual synchronization module, an automatic synchronization module, a reverse synchronization module, a table storage engine module, a calculation distribution module and an aggregation calculation module. The configuration writing area includes a calculation tree module and an aggregation table configuration module. The table storage engine module is used to manage data warehouse data.

[0006] The manual synchronization module sends the computing task to the queue of the computing distribution module, and reads and writes the computing task increments to the data warehouse after the computing task is completed; the automatic synchronization module reads the computing task, forms an index structure based on the task type, and then rewrites it to the data warehouse; the computing distribution module filters the computing tasks, and then sends the computing tasks that need to be calculated to the process according to the distribution strategy; the aggregation computing module reads the tenant computing tree of the computing tree module and calculates the unique identifier, reads the single-table computing SQL statement of the aggregation table configuration module, and completes the computing task based on the unique identifier and SQL statement; the reverse synchronization module synchronizes the calculation results of the computing task back to the data warehouse; the aggregation table configuration module is used for users to write configuration files, and the computing tree module reads the single-table configuration of the aggregation table configuration module and updates the computing tree.

[0007] Preferably, the aggregation table configuration module forms a JSON file with user configuration information of different nodes of the aggregation table, and generates single-table calculation SQL statements and parameters based on the JSON file.

[0008] Preferably, the aggregation table configuration module is provided with a node simplification function and a data detection function; the node simplification function automatically simplifies and merges the computing nodes according to the aggregation table configuration nodes; the data detection function automatically performs a Cartesian product detection on the SQL when there are connection nodes in the aggregation table to prevent data expansion.

[0009] Preferably, the data warehouse is divided into a computing cluster and a display cluster, both of which use Greenplum as the basic database.

[0010] The present invention adopts the data warehouse engine of the above structure, uses a tree diagram to process the associated table data, has a faster update speed, and the data operation is monitored throughout the process, making the operation safer. A distribution strategy is set to improve the efficiency of computing task processing. BRIEF DESCRIPTION OF THE DRAWINGS

[0011] Figure 1 This is a model architecture diagram of an embodiment of the present invention;

[0012] Figure 2 It is a structural diagram between tables in an embodiment of the present invention;

[0013] Figure 3 This is a tree diagram after table conversion according to an embodiment of the present invention. DETAILED DESCRIPTION

[0014] The technical solution of the present invention is further described below in conjunction with the accompanying drawings and embodiments.

[0015] The data warehouse engine is divided into a task distribution area and a configuration writing area. The task distribution area includes a manual synchronization module, an automatic synchronization module, a reverse synchronization module, a table storage engine module, a calculation distribution module, and an aggregate calculation module. The configuration writing area includes a calculation tree module and an aggregate table configuration module.

[0016] The overall model architecture of the engine is as follows Figure 1 shown.

[0017] Data warehouse data storage

[0018] The data warehouse is divided into a computing cluster and a display cluster, both of which use Greenplum as the basic database. The data warehouse is managed by the table storage engine.

[0019] 1. Computing cluster: The computing cluster contains item data and uses Greenplum column storage to improve computing speed and reduce data volume. Hash partitioning is performed based on table ID.

[0020] 2. Display cluster: Display cluster uses Greenplum row storage. Hash partitioning is performed based on table ID.

[0021] 3. Data structure: item contains two important attributes: table ID and values. Values ​​is a jsonb structure that stores the mapping structure of field ID=>VALUE of the table field.

[0022] 4. Dual cluster synchronization: After the computing cluster completes the aggregate table calculation, the result is exported as a file and shared with the display cluster through the NAS network disk. The display cluster executes the result file to complete data synchronization.

[0023] 5. Extension: Enhance Greenplum's ability to process jsonb data structures by developing Greenplum extensions.

[0024] Manual Sync Module

[0025] When the tenant clicks manual synchronization, a computing task with the tenant ID is sent to the queue of the computing distribution module. When the computing task is completed, the current automatic synchronization timestamp is read, and the incremental data is read and written to the data warehouse. According to the automatic synchronization logic, the tenant's data warehouse aggregation table calculation is triggered.

[0026] Automatic synchronization module

[0027] In the table and dynamic database, create triggers to maintain the update, modification, and deletion time of the table data, and create corresponding indexes for the three fields to perform incremental reading when reading data. After incremental reading, the read data is divided into different data files according to the three operations of addition, deletion, and modification, and the data structure required for the data warehouse is generated through the ETL (data warehouse technology) process and written into the data warehouse database. In the ETL process, the table ID and tenant ID with structural or data changes are recorded to maintain the mapping structure. After the reading is completed, the start timestamp is updated in the redis library for the next incremental reading.

[0028] Computation distribution module

[0029] Through the mapping structure of tenant ID and table ID obtained during the automatic synchronization process, based on tenant ID and table ID, with incremental table ID as the starting point, obtain some subtrees of the tenant's aggregated computing tree, filter tenants and tree nodes that do not need to be calculated, and start multiple computing worker processes according to the distribution strategy. Each worker is responsible for the computing tasks of N tenants and performs calculations one by one.

[0030] Aggregate computing module

[0031] First, the unique identifier of the data is calculated, and then the incremental calculation update is completed.

[0032] 1. Data unique mark

[0033] By traversing the configuration structure in a single aggregation table, the calculation method for the unique mark of each layer of aggregated data is obtained, and then the unique mark (unique_key) of each layer of calculation results is connected by a string to obtain the unique mark (unique_key) of the final data.

[0034] Example: Aggregate table A has three nodes: join, group, and union.

[0035] The join node is a left join b on a2=b2

[0036] The group node is group by a1,b1

[0037] Union is the full table union

[0038] The unique_key of the join node is a2_b2

[0039] The group node unique_key is a2_b2_a1_b1

[0040] The unique_key of the union node is a2_b2_a1_b1_{a.item_id}_{b.item_id}

[0041] The final unique_key is a2_b2_a1_b1_{a.Item_id}_{b.item_id}.

[0042] 2. Incremental calculation update

[0043] When an aggregate table is calculated, it is assumed that the original table code is old_table and the new table code is new_table.

[0044] ①The data where old_table.values!=new_table.values ​​AND old_table.unique_key=new_table.unique_key is the data to be updated.

[0045] ②The data of old_table.unique_key NOT IN(new_table.unique_key) is the data to be deleted.

[0046] ③The data of new_table.unique_key NOT IN(old_table.unique_key) is the data to be added.

[0047] Therefore, incremental update of aggregate table data is achieved.

[0048] Reverse synchronization module

[0049] Synchronize the calculation results of the aggregate table back to the table and dynamics.

[0050] Aggregate table configuration module

[0051] 1. Attributes:

[0052] JSON configuration: saves tenant configuration information of different nodes of the aggregate table, such as the connection conditions of the table connection node and the object IDs of the left and right tables.

[0053] SQL: Generates the SQL statements required for calculation based on the JSON configuration, and supplements calculation logic such as unique_key.

[0054] Params: The list of bound parameters required for SQL execution.

[0055] 2. Optimization:

[0056] ①Configure nodes according to the aggregation table and automatically simplify and merge computing nodes.

[0057] For example, in aggregate table A, there are connection nodes a1 and field setting nodes b1. The a1 operator operation results in 100 fields, and the b1 node is set to display 3 fields. The a1 operator operation result is automatically optimized to generate only 3 fields, reducing memory and CPU usage. This optimization will reversely traverse the aggregate table nodes, automatically identify the fields required by multiple nodes, and filter out invalid fields for each node.

[0058] ②When there are connection nodes in the aggregate table, the SQL is automatically checked for Cartesian products to prevent data expansion.

[0059] Computation tree module

[0060] A tenant's data warehouse may have N aggregate tables, and N tables form a computation tree. For example: Table A and Table B are aggregated into Table C, Table C and Table B are aggregated into Table D, and Table D and Table E are aggregated into Table F, where C, D, and F are aggregate tables. The final structure is as follows: Figure 2 As shown. Transform the structure into a tree structure as shown Figure 3 shown.

[0061] By traversing the tree structure in breadth-first order, the calculation order of the aggregate table in the tenant is: C=>D=>F. When only table E is updated, breadth-first traversal starts from the tree layer where E is located, and the aggregate table to be calculated is: F. When only table B is updated, breadth-first traversal starts from the highest tree layer where B is located, and the aggregate tables to be calculated are: C, D, F. Each time an aggregate table is created, modified, or updated, the tenant's calculation tree is updated.

[0062] The above are specific embodiments of the present invention, but the protection scope of the present invention should not be limited thereto. Any changes or substitutions that can be easily thought of by a person skilled in the art within the technical scope disclosed by the present invention should be included in the protection scope of the present invention, so the protection scope of the present invention should be based on the protection scope defined in the claims.

Claims

1. A data warehouse engine, characterized in that: It is divided into the task delivery area and the configuration writing area. The task delivery area includes the manual synchronization module, automatic synchronization module, reverse synchronization module, table storage engine module, calculation distribution module and aggregation calculation module. The configuration writing area includes the calculation tree module and aggregation table configuration module. The table storage engine module is used to manage data warehouse data. The manual synchronization module sends the computing task to the queue of the computing distribution module, and after the computing task is completed, it reads and writes the computing task incrementally to the data warehouse; the automatic synchronization module reads the computing task, forms an index structure according to the task type, and then rewrites it to the data warehouse; the computing distribution module filters the computing tasks, and then sends the computing tasks that need to be calculated to the process according to the distribution strategy; the aggregation computing module reads the tenant computing tree of the computing tree module and calculates the unique identifier, reads the single-table computing SQL statement of the aggregation table configuration module, and completes the computing task based on the unique identifier and SQL statement; the reverse synchronization module synchronizes the calculation results of the computing task back to the data warehouse; the aggregation table configuration module is used for users to write configuration files, and the computing tree module reads the single-table configuration of the aggregation table configuration module and updates the computing tree; The aggregation table configuration module is provided with a node simplification function and a data detection function; the node simplification function automatically simplifies and merges the computing nodes according to the aggregation table configuration nodes; the data detection function automatically performs a Cartesian product detection on the SQL when there are connection nodes in the aggregation table to prevent data expansion.

2. The data warehouse engine according to claim 1, characterized in that: The aggregation table configuration module forms a JSON file with the user configuration information of different nodes of the aggregation table, and generates a single table calculation SQL statement and parameters according to the JSON file.

3. The data warehouse engine according to claim 1, characterized in that: The data warehouse is divided into a computing cluster and a display cluster, both of which use Greenplum as the basic database.

Images