A method of parallel real-time incremental statistics based on database logs

Abstract

The present invention relates to a method for performing parallel real-time incremental statistics based on business system data, including two cases, a stand-alone mode and a distributed mode. By monitoring the operation log of a business database, the business data is incrementally synchronized to a ClickHouse node in real time without intrusion on the business data. The incremental statistics function of the ClickHouse node data synchronization table is used to perform real-time statistics of indicator data, and the indicator data calculation pressure of the business database is transferred to the ClickHouse node, thereby improving the indicator data calculation performance, realizing high-concurrency sub-second query of real-time indicator data, and solving the indicator data statistics of frequently updated massive data.

Description

Technical Field

[0001] The present invention relates to the field of computer science big data technology, and in particular to a method for performing parallel real-time incremental statistics based on business system data. Background Art

[0002] There are often some real-time indicator data statistics requirements in business systems. When business data changes, users can query the changed statistical indicator data in real time. Business systems usually use mature relational databases (such as MySQL, SQLServer, Oracle, PostgreSQL, etc.) as business data storage. Commonly used real-time statistical methods are as follows:

[0003] 1) Use statistical SQL statements to perform real-time queries on the business database.

[0004] 2) Use memory cache to save the real-time calculated statistical indicator data to the memory cache. When the data affecting the indicator statistics changes, the corresponding cached indicator data needs to be cleared, so as to trigger the indicator recalculation and update the cache when the statistical indicator data is accessed next time.

[0005] 3) The business database uses read-write separation technology to perform indicator data statistics and queries from the backup node.

[0006] 4) The business database uses shard storage technology to horizontally split tables with large data volumes, so that a single statistical SQL statement can be distributed to the database shard nodes for parallel execution.

[0007] 5) The server performs statistical calculations of indicators at regular intervals, and saves and updates the statistical indicator data after the calculations are completed.

[0008] 6) Create triggers in the database. When business data changes, the corresponding indicator data is updated through the triggers.

[0009] For the above method 1), since most statistical SQL statements require scanning the entire table when executed, when the amount of data in the business table is large or the concurrency of indicator data queries increases, the pressure on the business database will be seriously increased and it will not be able to respond normally.

[0010] For method 2), after introducing memory cache, the execution frequency of statistical SQL statements can be reduced. However, if the data changes frequently, the cache will be frequently cleared to recalculate the indicators, which cannot achieve the optimization effect. At the same time, it cannot guarantee that the statistical operations based on business tables with large data volumes can respond quickly when the indicators are recalculated. In addition, this method requires manual analysis of which business data changes will affect which statistical indicators, and corresponding coding operations to clear the corresponding indicator cache, which increases the complexity of development and implementation.

[0011] For method 3), using read-write separation technology, the indicator data statistics are executed in the standby database. Although the indicator data calculation does not affect the read-write performance of the primary business database, it also cannot guarantee that the statistical operations based on tables with large data volumes can respond quickly.

[0012] For method 4), shard storage technology can distribute the statistical computing pressure to each shard database node, but the indicator calculation still requires scanning the entire table, which cannot reduce the statistical computing overhead and requires adding a large amount of hardware resources to meet high-concurrency real-time statistical needs.

[0013] For method 5), the statistical pressure on the database can be reduced by reducing the execution frequency of statistical SQL statements, but the timely update of statistical indicator data cannot be guaranteed.

[0014] Regarding method 6), using this method to incrementally update statistical indicator data greatly reduces the pressure on the database and improves statistical performance, but this method also requires manual analysis of which business data changes will affect which statistical indicators, and corresponding coding processing; for some indicators that cannot be incrementally calculated based on the current statistical indicators combined with incremental data, it is also necessary to save and update the intermediate state data of the indicator statistical data during the incremental calculation process; for changes in the indicator statistical business logic or bugs in the incremental calculation process, it is also necessary to recalculate the indicator based on the existing data; for business database clusters with sharded storage, it is also necessary to consider the problem of merging indicator calculation results. The above situations greatly increase the complexity of the implementation of this method. Summary of the invention

[0015] The purpose of the present invention is to perform real-time data synchronization and real-time calculation of indicator data based on business database logs, to achieve high-concurrency sub-second query of real-time indicator data, to solve the problem of indicator data statistics of frequently updated massive data, and parallel real-time incremental statistics, and to provide a method for parallel real-time incremental statistics based on business system data, including both stand-alone mode and distributed mode.

[0016] In order to achieve the above-mentioned object of the invention, the embodiment of the present invention provides the following technical solutions:

[0017] As an implementable method, a method for parallel real-time incremental statistics of database logs in a stand-alone mode includes the following steps:

[0018] Step S1: Start the data event consumer, create a data synchronization table corresponding to the ClickHouse node, and synchronize the full data of the business database table to the created data synchronization table;

[0019] Step S2: Start the data event producer, read the business database log in the order of the business database log position recorded by redis, and push it to the message queue according to the order in which the business database log is read; the business database log includes data addition events and table structure change events;

[0020] Step S3: After the event data consumer completes full data synchronization, it automatically starts the incremental synchronization operation, consumes the data addition events and / or table structure change events in the message queue, and synchronizes the data addition events and / or table structure change events to the data synchronization table of the ClickHouse node;

[0021] Step S4: According to the statistical requirements of the indicator data, create a materialized view of the AggregatingMergeTree engine based on the data synchronization table corresponding to the ClickHouse node, and specify the SQL statement that needs to execute statistics in the materialized view creation statement;

[0022] Step S5: Use SQL statements to query the statistical indicator data results of the materialized view of the AggregatingMergeTree engine.

[0023] In the above scheme, by monitoring the operation log of the business database, the business data is synchronized to the ClickHouse node in real time without intrusion on the business data. The incremental statistics function of the ClickHouse node data synchronization table is used to perform real-time statistics of indicator data, and the indicator data calculation pressure of the business database is transferred to the ClickHouse node, which improves the indicator data calculation performance and realizes sub-second query of real-time indicator data with high concurrency.

[0024] Furthermore, the step S1 specifically includes the following steps:

[0025] Step S11: Start the data event consumer, and after starting, check whether the data synchronization table corresponding to the ClickHouse node is created;

[0026] Step S12: If the data synchronization table corresponding to the ClickHouse node is not created, a CollapsingMergeTree engine table is created as the data synchronization table corresponding to the ClickHouse node; the status field of the CollapsingMergeTree engine table is sign, when sign is 1, it indicates that the data is valid, and when sign is -1, it indicates that the data is invalid;

[0027] Step S13: After creating the data synchronization table corresponding to the ClickHouse node, synchronize the full data of the business database table to the CollapsingMergeTree engine table; during the full data synchronization, add a read-only lock to the synchronized business database table.

[0028] In the above solution, the VersionedCollapsingMergeTree engine table can be used to replace the CollapsingMergeTree engine table, and the CollapsingMergeTree engine table or the VersionedCollapsingMergeTree engine table can be used to save the synchronization data.

[0029] Furthermore, the step S2 specifically includes the following steps:

[0030] Step S21: Start the data event producer, read the business database log in the order of the business database log position recorded by redis, and if redis has not recorded the business database log, start reading from the end of the business database log;

[0031] Step S22: If a data addition log is read, it is converted into a data addition event, and the status field sign is set to 1; if a data modification log is read, it is converted into two data addition events, the first one is the data before modification, and the status field sign is set to -1, and the second one is the data after modification, and the status field sign is set to 1; if a data deletion log is read, it is converted into a data addition event, and the status field sign is set to -1;

[0032] Step S23: If the DDL operation log is read, it is converted into a table structure change event and converted into the DDL statement corresponding to the ClickHouse node;

[0033] Step S24: Push the converted data addition events and table structure change events to the message queue in the order of reading the business database logs.

[0034] In the above scheme, by setting the status field "sign", the original data addition, modification, and deletion operations are converted into data addition events, thereby converting random disk read and write operations into sequential disk write operations, achieving sub-second real-time incremental data synchronization.

[0035] Furthermore, if redis has not recorded the business database log, after successfully pushing to the message queue, the order in which the business database log is read is recorded in redis.

[0036] Furthermore, the specific steps of step S3 include: for new data events in the message queue, executing DML statements to insert data according to the ClickHouse node syntax; for table structure change events, executing the converted DDL statements, thereby synchronizing the new data events and / or table structure change events to the data synchronization table of the ClickHouse node.

[0037] Furthermore, the step S4 specifically includes the following steps:

[0038] Step S41: According to statistical requirements, create a materialized view of the AggregatingMergeTree engine based on the CollapsingMergeTree engine table corresponding to the synchronized ClickHouse node;

[0039] Step S42: In the materialized view creation statement of the AggregatingMergeTree engine, specify the SQL statement that needs to be statistically executed, and perform some statistical SQL statement indicator conversion to avoid data with invalid status fields from being counted.

[0040] In the above solution, with the help of the incremental statistics function of the materialized view of the AggregatingMergeTree engine of the ClickHouse node, the user only needs to specify the required statistical SQL statement in the materialized view table creation statement, and make some statistical indicator conversions based on the status field "sign", so as to perform full and incremental indicator statistics, thereby reducing the workload and complexity of indicator development, and meeting the needs of fast recalculation of indicator data.

[0041] Statistical indicator conversion means that, for example, if count(*) was originally needed to count the number of rows, it can be replaced by sum(sign); if sum(income) was originally needed to summarize the income field, it can be replaced by sum(income*sign); if avg(income) was originally needed to calculate the average value of the income field, it can be replaced by sum(income*sign) / sum(sign).

[0042] As another feasible manner, a method for parallel real-time incremental statistics of database logs based on distributed deployment includes the following steps:

[0043] Step S1: Deploy multiple data event consumers and multiple ClickHouse nodes to form a ClickHouse sharding cluster. The number of ClickHouse nodes is consistent with the number of business database sharding nodes. One data event consumer is responsible for synchronizing the data of a corresponding business database sharding node to a ClickHouse node;

[0044] Step S2: deploy multiple data event producers, one data event producer is responsible for reading the database log of one business database shard node;

[0045] When a data event producer is started, step S2 of the method according to claim 1 is executed;

[0046] Step S3: Each data event consumer is responsible for the automatic incremental synchronization operation of the corresponding business database shard node data events, thereby synchronizing the data events of the business database shard node to the corresponding ClickHouse node;

[0047] Step S4: Create a local materialized view using the data synchronization table of the business database synchronized by each node in the ClickHouse sharded cluster;

[0048] Step S5: Create a distributed table on each ClickHouse node, associate the distributed table with the local materialized view, and use SQL statements to query the statistical indicator data results of the distributed table of any node in the ClickHouse shard cluster.

[0049] In the above solution, for the business data shard cluster, real-time data synchronization is achieved through distributed deployment, and the ClickHouse shard cluster is deployed in a distributed manner to support parallel data synchronization and parallel indicator data statistics, solving the problem of parallel real-time incremental statistics for frequently updated massive data.

[0050] Furthermore, the specific steps of step S1 are:

[0051] Step S11: Start each data event consumer, and after startup, check whether the data synchronization table of the corresponding ClickHouse node is created;

[0052] Step S12: If the data synchronization table corresponding to the ClickHouse node is not created, create a CollapsingMergeTree engine table as the data synchronization table corresponding to the ClickHouse node, or create a VersionedCollapsingMergeTree engine table as the data synchronization table corresponding to the ClickHouse node;

[0053] The status field of the CollapsingMergeTree engine table is sign. When sign is 1, it indicates that the data is valid, and when sign is -1, it indicates that the data is invalid.

[0054] The business database log update timestamp of the business database shard node corresponding to the ClickHouse node is used as the version field of the VersionedCollapsingMergeTree engine table to ensure that the VersionedCollapsingMergeTree engine table can be merged according to the primary key plus the version field when the business database logs are merged, without being affected by the order of the business database logs;

[0055] Step S13: After creating the data synchronization table corresponding to the ClickHouse node, synchronize the full data of the business database table to the CollapsingMergeTree engine table or the VersionedCollapsingMergeTree engine table.

[0056] In the above solution, the order problem of automatic data merging is solved through the VersionedCollapsingMergeTree engine table field.

[0057] Furthermore, step S1 is specifically as follows: when deploying multiple ClickHouse nodes, create two or more ClickHouse shard replica sets for one ClickHouse node; replace the CollapsingMergeTree engine table in the ClickHouse shard replica set with the ReplicatedCollapsingMergeTree engine table, or replace the VersionedCollapsingMergeTree engine table in the ClickHouse shard replica set with the ReplicatedVersionedCollapsingMergeTree engine table.

[0058] Compared with the prior art, the present invention has the following beneficial effects:

[0059] (1) The present invention synchronizes business data to the ClickHouse node by monitoring the operation log of the business database, uses the CollapsingMergeTree engine table or the VersionedCollapsingMergeTree engine table of the ClickHouse node to save the synchronized data, and through the setting of the status field "sign", the original data addition, modification, and deletion operations are replaced with data addition events, thereby converting random disk read and write operations into sequential disk write operations, thereby realizing sub-second real-time incremental data synchronization.

[0060] (2) On this basis, the present invention uses the incremental statistical function of the materialized view of the AggregatingMergeTree engine of the ClickHouse node to transfer the statistical indicator data calculation pressure of the business database to the ClickHouse node, thereby reducing the complexity of development implementation and improving the indicator data calculation performance, thereby realizing sub-second query of indicators with high concurrency.

[0061] (3) The present invention also uses horizontal table partitioning technology for distributed storage of data. By distributing the ClickHouse sharding cluster, it supports parallel data synchronization and parallel indicator data statistics, solving the problem of parallel real-time incremental statistics of frequently updated massive data. BRIEF DESCRIPTION OF THE DRAWINGS

[0062] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings required for use in the embodiments are briefly introduced below. It should be understood that the following drawings only show certain embodiments of the present invention and therefore should not be regarded as limiting the scope. For ordinary technicians in this field, other related drawings can be obtained based on these drawings without creative work.

[0063] Figure 1 Schematic diagram of data flow of the present invention. DETAILED DESCRIPTION

[0064] The technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments are only a part of the embodiments of the present invention, rather than all the embodiments. The components of the embodiments of the present invention generally described and shown in the drawings here can be arranged and designed in various different configurations. Therefore, the following detailed description of the embodiments of the present invention provided in the drawings is not intended to limit the scope of the claimed invention, but merely represents the selected embodiments of the present invention. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without making creative work belong to the scope of protection of the present invention.

[0065] It should be noted that similar reference numerals and letters represent similar items in the following drawings, so once an item is defined in one drawing, it does not need to be further defined and explained in the subsequent drawings. At the same time, in the description of the present invention, the terms "first", "second", etc. are only used to distinguish the description, and cannot be understood as indicating or implying relative importance, or implying any such actual relationship or order between these entities or operations.

[0066] Embodiment 1:

[0067] The present invention is achieved through the following technical solutions: Figure 1 As shown, the method for parallel real-time incremental statistics of database logs in a single-machine mode includes the following steps:

[0068] Step S1: Start the data event consumer, create a data synchronization table corresponding to the ClickHouse node, and synchronize the full data of the business database table to the created data synchronization table.

[0069] Start the data event consumer. After starting, check whether the data synchronization table corresponding to the ClickHouse node is created. If it is created, the data synchronization table corresponding to the ClickHouse node should be the CollapsingMergeTree engine table. If it is not created, create the CollapsingMergeTree engine table. The status field of the CollapsingMergeTree engine table is sign. When sign is 1, it means that the data is valid. When sign is -1, it means that the data is invalid.

[0070] After the CollapsingMergeTree engine table is created, the full data of the business database table is synchronized to the CollapsingMergeTree engine table. During the full data synchronization, a read-only lock is added to the synchronized business database table.

[0071] Step S2: Start the data event producer, read the business database log in the order of the business database log position recorded by redis, and push it to the message queue according to the order in which the business database log is read; the business database log includes data addition events and table structure change events.

[0072] Start the data event producer and read the business database log in the order of the business database log position recorded by redis. If redis has not recorded the business database log, start reading from the end of the business database log.

[0073] If a data addition log is read, it is converted into a data addition event and the status field sign is set to 1; if a data modification log is read, it is converted into two data addition events, the first one is the data before modification, and the status field sign is set to -1, and the second one is the data after modification, and the status field sign is set to 1; if a data deletion log is read, it is converted into a data addition event and the status field sign is set to -1. If a DDL operation log is read, it is converted into a table structure change event and the DDL statement corresponding to the ClickHouse node.

[0074] Therefore, the business database log is converted into two types of events: data addition events or table structure change events. The converted data addition events and table structure change events are pushed to the message queue in the order in which the business database log is read.

[0075] If redis has not recorded the business database log, after successfully pushing it to the message queue, the order in which the business database log is read is recorded in redis as the business database log position recorded by redis.

[0076] Step S3: After the event data consumer completes full data synchronization, it automatically starts the incremental synchronization operation to consume the data new events and / or table structure change events in the message queue.

[0077] For new data events in the message queue, DML statements are executed according to the ClickHouse node syntax to insert data; for table structure change events, the converted DDL statements are executed to synchronize new data events and / or table structure change events to the data synchronization table of the ClickHouse node, completing the incremental synchronization operation.

[0078] Step S4: According to the statistical requirements of the indicator data, create a materialized view of the AggregatingMergeTree engine based on the data synchronization table corresponding to the ClickHouse node, and specify the SQL statement that needs to execute statistics in the materialized view creation statement.

[0079] According to the statistical requirements, create a materialized view of the AggregatingMergeTree engine based on the CollapsingMergeTree engine table corresponding to the ClickHouse node synchronization. In the materialized view creation statement of the AggregatingMergeTree engine, specify the SQL statement that needs to be statistically executed, and convert some statistical SQL statement indicators, such as replacing count(*) with sum(sign), so as to avoid invalid data in the status field from being counted.

[0080] Step S5: Use SQL statements to query the statistical indicator data results of the materialized view of the AggregatingMergeTree engine.

[0081] Since the AggregatingMergeTree engine will perform automatic incremental aggregation and save in the background according to the statistical SQL statement aggregation function specified when creating the materialized view, you can use SQL statements to query the statistical indicator data results of the materialized view of the AggregatingMergeTree engine, thereby achieving highly concurrent sub-second queries on real-time indicator data.

[0082] As a preferred solution, the backup business database log can be used for data synchronization, which can reduce the impact of the table lock operation on the business database during full data synchronization.

[0083] As another possible implementation, Figure 1 As shown, the method of parallel real-time incremental statistics of database logs based on distributed deployment is used to improve the performance of data synchronization and statistical calculation of shard cluster business database, including the following steps:

[0084] Step S1: Deploy multiple data event consumers and multiple ClickHouse nodes to form a ClickHouse shard cluster. The number of ClickHouse nodes is consistent with the number of business database shard nodes. A data event consumer is responsible for synchronizing the data of a corresponding business database shard node to a ClickHouse node.

[0085] After starting a data event consumer, execute step S1 in the aforementioned stand-alone mode. Since the data event reading and writing order of the distributed message queue cannot be guaranteed, the VersionedCollapsingMergeTree engine table can be created to replace the CollapsingMergeTree engine table, thereby using the distributed message queue to improve the system fault tolerance and message reading and writing performance. The business database log update timestamp of the business database shard node corresponding to the ClickHouse node is used as the version field of the VersionedCollapsingMergeTree engine table to ensure that the VersionedCollapsingMergeTree engine table can be merged according to the primary key plus the version field when the business database log is merged, without being affected by the order of the business database log.

[0086] After creating the data synchronization table corresponding to the ClickHouse node, synchronize the full data of the business database table shard node to the CollapsingMergeTree engine table or VersionedCollapsingMergeTree engine table.

[0087] Step S2: deploy multiple data event producers, one data event producer is responsible for reading the database log of one business database shard node.

[0088] When a data event producer is started, step S2 of the method in the above-mentioned stand-alone mode is executed. When a distributed message queue is used, it is not affected by the order of the business database logs.

[0089] Step S3: Each data event consumer is responsible for the automatic incremental synchronization operation of the corresponding business database shard node data events, thereby synchronizing the data events of the business database shard node to the corresponding ClickHouse node.

[0090] When each data event consumer consumes data events in the distributed message queue, the incremental synchronization operation is the same as step S3 of the method in the stand-alone mode.

[0091] Step S4: Create a local materialized view using the data synchronization table of the business database synchronized by each node in the ClickHouse sharded cluster.

[0092] When creating a local materialized view synchronized with each node, it is the same as step S4 of the method in the aforementioned stand-alone mode. It can be understood that when creating a materialized view of the AggregatingMergeTree engine based on the CollapsingMergeTree engine table, a corresponding materialized view of the AggregatingMergeTree engine can also be created based on the VersionedCollapsingMergeTree table.

[0093] Step S5: Create a distributed table on each ClickHouse node, associate the distributed table with the local materialized view, and use SQL statements to query the statistical indicator data results of the distributed table of any node in the ClickHouse shard cluster.

[0094] Since local materialized views will be automatically incrementally aggregated and saved in the background, and the statistical data results of local materialized views will be merged when querying distributed tables, you can use SQL statements to query the statistical indicator data results of distributed tables of any node in the ClickHouse sharding cluster, thereby achieving sub-second queries of real-time indicators with high concurrency.

[0095] As a preferred solution, when deploying multiple ClickHouse nodes, you can use ClickHouse shard replica sets. One ClickHouse shard node creates two or more ClickHouse shard replica sets. At this time, you need to replace the CollapsingMergeTree engine table with the ReplicatedCollapsingMergeTree engine table, or replace the VersionedCollapsingMergeTree engine table in the ClickHouse shard replica set with the ReplicatedVersionedCollapsingMergeTree engine table. Using the Replicated class engine table allows the replicas of the same ClickHouse node shard node to automatically synchronize data. At this time, a ClickHouse shard node only needs to synchronize data to one replica. In order to facilitate the synchronous replication of partition data, since the synchronous replication of partition data will be executed asynchronously, there may be inconsistent query of indicator data. Users can weigh and choose between partition fault tolerance and data consistency according to actual business needs.

[0096] Embodiment 2:

[0097] This embodiment is based on the above-mentioned embodiment 1 as a case study. As shown in embodiment 1, this solution uses the business database log to synchronize data to the ClickHouse node in real-time increments, uses redis to record the business database log reading position, and the message queue saves data events. Business database, real-time data synchronization, ClickHouse node, redis, and message queue can all use distributed deployment to achieve parallel real-time incremental data synchronization and parallel real-time incremental statistics.

[0098] Next, we will use an actual application case to illustrate. This case uses MySQL as the business database, Kafka as the message queue, and three ClickHouse nodes as a shard cluster for statistics.

[0099] Assume that there is an employment information table in the MySQL business database, named employment_info, which stores the current employment information of all personnel. The area_code field in the table represents the administrative division, and the work_status field represents the employment status. When work_status=1, it means employed, and this field will be updated in real time according to specific business needs. The project needs to count the number of employees in each administrative area in real time, because the data volume of the employment information table is in the tens of millions, and according to business needs, the number of employees in each administrative area needs to be frequently queried concurrently. In order to reduce the statistical pressure of the MySQL business database and display the statistical results in real time on the front-end interface, this solution is used to implement parallel real-time incremental statistics. The specific steps are as follows:

[0100] Step S1: After the data event consumer configures the MySQL business database, kafka and ClickHouse cluster addresses and related parameters, it starts. The data event consumer first creates the local table employment_info_local and the distributed table employment_info_all of the CollapsingMergeTree engine on each node in the ClickHouse cluster, and then synchronizes all the data of the employment_info employment information table of the MySQL business database to the distributed table employment_info_all of the ClickHouse node.

[0101] Step S2: After the data event producer configures the address and related parameters of the MySQL business database, redis, and kafka, the data event producer is started. The data event producer reads the binlog log in the MySQL business database and pushes the generated data events to kafka.

[0102] Step S3: After the full data synchronization is completed, the data event consumer switches to the incremental synchronization operation mode, consumes the data events in Kafka in sequence, and synchronizes the data events to the distributed table employment_info_all of the ClickHouse node.

[0103] Step S4: Create a local materialized view employment_info_view_local on each local table employment_info_local in the ClickHouse cluster, so as to count the number of employees in each administrative area on each local materialized view.

[0104] Step S5: Create a distributed table employment_info_view on each node of the ClickHouse cluster, associate the local materialized view on the corresponding node, and summarize the statistical results of each local materialized view.

[0105] Since the local materialized view will automatically aggregate the incremental data of the local table employment_info_local and save the aggregated results, and querying the distributed table employment_info_view will merge the local materialized view result data, it is possible to perform statistical queries on the distributed table employment_info_view, thereby achieving parallel real-time incremental statistics of the number of employees in each administrative area.

[0106] The present invention synchronizes business data to ClickHouse nodes by monitoring the operation log of the business database, uses the CollapsingMergeTree engine table or VersionedCollapsingMergeTree engine table of the ClickHouse node to save the synchronization data, and through the setting of the status field "sign", the original data addition, modification, and deletion operations are replaced with data addition events, thereby converting random disk read and write operations into sequential disk write operations, and realizing sub-second data real-time incremental synchronization. On this basis, the present invention transfers the statistical indicator data calculation pressure of the business database to the ClickHouse node with the help of the incremental statistics function of the AggregatingMergeTree engine materialized view of the ClickHouse node, reduces the development implementation complexity, improves the indicator data calculation performance, and thus realizes sub-second query with high concurrency of indicators.

[0107] The present invention also uses horizontal table partitioning technology for distributed storage of data, and supports parallel data synchronization and parallel indicator data statistics through distributed deployment of ClickHouse sharding clusters, thereby solving the problem of parallel real-time incremental statistics of frequently updated massive data.

[0108] The above is only a specific embodiment of the present invention, but the protection scope of the present invention is not limited thereto. Any person skilled in the art can easily think of changes or substitutions within the technical scope disclosed by the present invention, which should be included in the protection scope of the present invention. Therefore, the protection scope of the present invention should be based on the protection scope of the claims.

Claims

1. A method for parallel real-time incremental statistics of database logs in a single-machine mode, characterized by: The following steps are involved: Step S1: Start the data event consumer, create a data synchronization table corresponding to the ClickHouse node, and synchronize the full data of the business database table to the created data synchronization table; Step S2: Start the data event producer, read the business database log in the order of the business database log position recorded by redis, and push it to the message queue according to the order in which the business database log is read; the business database log includes data addition events and table structure change events; Step S3: After the event data consumer completes full data synchronization, it automatically starts the incremental synchronization operation, consumes the data addition events and / or table structure change events in the message queue, and synchronizes the data addition events and / or table structure change events to the data synchronization table of the ClickHouse node; Step S4: According to the statistical requirements of the indicator data, create a materialized view of the AggregatingMergeTree engine based on the data synchronization table corresponding to the ClickHouse node, and specify the SQL statement that needs to execute statistics in the materialized view creation statement; Step S5: Use SQL statements to query the statistical indicator data results of the materialized view of the AggregatingMergeTree engine.

2. The method for parallel real-time incremental statistics of database logs in stand-alone mode according to claim 1 is characterized in that: The step S1 specifically includes the following steps: Step S11: Start the data event consumer, and after starting, check whether the data synchronization table corresponding to the ClickHouse node is created; Step S12: If the data synchronization table corresponding to the ClickHouse node is not created, a CollapsingMergeTree engine table is created as the data synchronization table corresponding to the ClickHouse node; the status field of the CollapsingMergeTree engine table is sign, when sign is 1, it indicates that the data is valid, and when sign is -1, it indicates that the data is invalid; Step S13: After creating the data synchronization table corresponding to the ClickHouse node, synchronize the full data of the business database table to the CollapsingMergeTree engine table; during the full data synchronization, add a read-only lock to the synchronized business database table.

3. The method for parallel real-time incremental statistics of database logs in stand-alone mode according to claim 2 is characterized in that: The step S2 specifically includes the following steps: Step S21: Start the data event producer, read the business database log in the order of the business database log position recorded by redis, and if redis has not recorded the business database log, start reading from the end of the business database log; Step S22: If a data addition log is read, it is converted into a data addition event, and the status field sign is set to 1; if a data modification log is read, it is converted into two data addition events, the first one is the data before modification, and the status field sign is set to -1, and the second one is the data after modification, and the status field sign is set to 1; if a data deletion log is read, it is converted into a data addition event, and the status field sign is set to -1; Step S23: If the DDL operation log is read, it is converted into a table structure change event and converted into the DDL statement corresponding to the ClickHouse node; Step S24: Push the converted data addition events and table structure change events to the message queue in the order of reading the business database logs.

4. The method for parallel real-time incremental statistics of database logs in stand-alone mode according to claim 3 is characterized in that: If redis has not recorded the business database log, after successfully pushing it to the message queue, the order in which the business database log is read will be recorded in redis.

5. The method for parallel real-time incremental statistics of database logs in stand-alone mode according to claim 3 is characterized in that: The specific steps of step S3 include: for new data events in the message queue, executing DML statements to insert data according to the ClickHouse node syntax; for table structure change events, executing the converted DDL statements, thereby synchronizing the new data events and / or table structure change events to the data synchronization table of the ClickHouse node.

6. The method for parallel real-time incremental statistics of database logs in stand-alone mode according to claim 1 is characterized in that: The step S4 specifically comprises the following steps: Step S41: According to statistical requirements, create a materialized view of the AggregatingMergeTree engine based on the CollapsingMergeTree engine table corresponding to the synchronized ClickHouse node; Step S42: In the materialized view creation statement of the AggregatingMergeTree engine, specify the SQL statement that needs to be statistically executed, and perform some statistical SQL statement indicator conversion to avoid data with invalid status fields from being counted.

7. A method for parallel real-time incremental statistics of database logs based on distributed deployment, characterized in that: The following steps are involved: Step S1: Deploy multiple data event consumers and multiple ClickHouse nodes to form a ClickHouse sharding cluster. The number of ClickHouse nodes is consistent with the number of business database sharding nodes. One data event consumer is responsible for synchronizing the data of a corresponding business database sharding node to a ClickHouse node; Step S2: deploy multiple data event producers, one data event producer is responsible for reading the database log of one business database shard node; When a data event producer is started, step S2 of the method according to claim 1 is executed; Step S3: Each data event consumer is responsible for the automatic incremental synchronization operation of the corresponding business database shard node data events, thereby synchronizing the data events of the business database shard node to the corresponding ClickHouse node; Step S4: Create a local materialized view using the data synchronization table of the business database synchronized by each node in the ClickHouse sharded cluster; Step S5: Create a distributed table on each ClickHouse node, associate the distributed table with the local materialized view, and use SQL statements to query the statistical indicator data results of the distributed table of any node in the ClickHouse shard cluster.

8. The method for parallel real-time incremental statistics of database logs based on distributed deployment according to claim 7 is characterized in that: The specific steps of step S1 are: Step S11: Start each data event consumer, and after startup, check whether the data synchronization table of the corresponding ClickHouse node is created; Step S12: If the data synchronization table corresponding to the ClickHouse node is not created, create a CollapsingMergeTree engine table as the data synchronization table corresponding to the ClickHouse node, or create a VersionedCollapsingMergeTree engine table as the data synchronization table corresponding to the ClickHouse node; The status field of the CollapsingMergeTree engine table is sign. When sign is 1, it indicates that the data is valid, and when sign is -1, it indicates that the data is invalid. The business database log update timestamp of the business database shard node corresponding to the ClickHouse node is used as the version field of the VersionedCollapsingMergeTree engine table to ensure that the VersionedCollapsingMergeTree engine table can be merged according to the primary key plus the version field when the business database logs are merged, without being affected by the order of the business database logs; Step S13: After creating the data synchronization table corresponding to the ClickHouse node, synchronize the full data of the business database table to the CollapsingMergeTree engine table or the VersionedCollapsingMergeTree engine table.

9. The method for parallel real-time incremental statistics of database logs based on distributed deployment according to claim 8 is characterized in that: The step S1 is specifically as follows: when deploying multiple ClickHouse nodes, create two or more ClickHouse shard replica sets for one ClickHouse node; replace the CollapsingMergeTree engine table in the ClickHouse shard replica set with the ReplicatedCollapsingMergeTree engine table, or replace the VersionedCollapsingMergeTree engine table in the ClickHouse shard replica set with the ReplicatedVersionedCollapsingMergeTree engine table.

Images