A method and system for frequent deletion, modification and query optimization based on Clickhouse

Abstract

The invention discloses a method and system for frequent deletion, modification and query optimization based on Clickhouse, and relates to the technical field of data processing; the method comprises the following steps: S1, data synchronization, synchronizing the real-time operation log data in the original database to parse and store them into a kafka cluster and mongodb; S2, consumption of stream data, an application end Application is respectively connected to a kafka cluster, mongodb and a clickhouse cluster, when the application end Application consumes stream data, the application end Application first queries the data in mongodb, if no data is found, then queries the data in the clickhouse cluster; S3, operations on the query data; S4, timed synchronization of incremental data; the beneficial effects of the invention are: being able to support fast real-time insertion and update operations, and supporting high concurrent queries for real-time data.

Description

Technical Field

[0001] The present invention relates to the technical field of big data processing, and more specifically, to a method and system for frequent deletion, modification, query and optimization based on Clickhouse. Background Art

[0002] At present, big data has been widely used in finance, e-commerce, logistics, corporate government affairs and other fields. In recent years, it has begun to be applied to industrial Internet. In the 5G era, as the process of Internet of Everything accelerates, the storage of massive data and real-time query and OLAP analysis of massive data have brought challenges. Clickhouse is widely used in real-time processing and OLAP online analysis with its outstanding query and write performance. Some systems can store the values ​​of individual columns separately, but due to the optimization of other scenarios, they cannot effectively process analytical queries, such as HBase, BigTable, Cassandra and HyperTable. In these systems, a throughput of about 100,000 rows per second can be obtained, but it cannot reach hundreds of millions of rows per second like Clickhouse.

[0003] ClickHouse was developed for Yandex.Metrica, the world's second largest web analytics platform. It has been used continuously by the system as a core component for many years. So far, the system has more than 13 trillion records in ClickHouse, and more than 20 billion events are processed every day. It allows dynamic query and report generation directly from raw data. It is currently widely used in real-time reporting, online real-time analysis, user profiling, intelligent recommendation systems and other scenarios. However, ClickHouse also has its inherent shortcomings in the use process, which face some technical points that need to be solved:

[0004] 1. Does not support transactions, does not support real delete / update;

[0005] 2. High concurrency is not supported. The official recommended qps is 100. The number of connections can be increased by modifying the configuration file, but only if the server is good enough;

[0006] 3. It is not suitable for real-time insertion or small batch insert, update, and delete operations, because the underlying ClickHouse will continuously perform asynchronous data merging, which will affect query performance. This should be avoided as much as possible when writing real-time data. Summary of the invention

[0007] In order to overcome the shortcomings of the prior art, the present invention provides a method and system for frequent deletion, modification and query optimization based on Clickhouse, which can support fast real-time insertion and update operations and support high-concurrency query for real-time data.

[0008] The technical solution adopted by the present invention to solve the technical problem is: a method for frequent deletion, modification and optimization based on Clickhouse, the improvement of which is that the method comprises the following steps:

[0009] S1. Data synchronization: parse and store real-time operation log data in the original database to the Kafka cluster and MongoDB.

[0010] S2. Consumption of stream data. The application is connected to the Kafka cluster, MongoDB, and ClickHouse clusters respectively. When the application consumes stream data, it first queries the MongoDB data. If no data is found, it queries the ClickHouse cluster data.

[0011] S3: Operations on query data: When the application side Application queries data in MongoDB, updates or deletes the data, and directly operates MongoDB; when the application side Application does not query data in MongoDB, but queries data in the ClickHouse cluster, the query records of the Application are written back to MongoDB for query;

[0012] S4. Scheduled synchronization of incremental data. Mongodb is connected to the ClickHouse cluster. Mongodb regularly synchronizes incremental data to the ClickHouse cluster.

[0013] Furthermore, in step S1, the original database is an Oracle database or a MySQL database.

[0014] Furthermore, in the step S1, OGG or Canal is used to achieve data synchronization.

[0015] Furthermore, in step S2, when the application queries the data of the latest three days, it queries in MongoDB.

[0016] Furthermore, in step S2, when the application queries historical data, it queries the clickhouse cluster.

[0017] Furthermore, in step S4, the incremental data is synchronized to the ClickHouse cluster at a regular interval to ensure the integrity of the ClickHouse cluster data.

[0018] On the other hand, the present invention also discloses a system based on Clickhouse frequent deletion, modification, query and optimization, the improvement of which is that the system includes an original database, a kafka cluster, mongodb, an application end Application and a clickhouse cluster;

[0019] The original database is used to store real-time operation log data;

[0020] The kafka cluster and mongodb are connected to the original database respectively, and the real-time operation log data in the original database is synchronized and parsed and stored in the kafka cluster and mongodb;

[0021] The application end Application is connected to the kafka cluster, mongodb and clickhouse cluster respectively, and the application end Application is used to realize data query on mongodb and data query on clickhouse cluster;

[0022] The ClickHouse cluster is connected to MongoDB, and MongoDB periodically synchronizes incremental data to the ClickHouse cluster.

[0023] In the above structure, the original database is an Oracle database or a Mysql database.

[0024] In the above structure, the data queried by the application side Application in mongodb is the data of the latest three days.

[0025] In the above structure, the data that the application side Application queries the clickhouse cluster is historical data.

[0026] The beneficial effects of the present invention are as follows: a method and system for frequent deletion, modification and query optimization based on Clickhouse of the present invention can support fast real-time insertion and update operations, support high-concurrency queries for real-time data, and can quickly respond to query requests, and can achieve millisecond-level responses. BRIEF DESCRIPTION OF THE DRAWINGS

[0027] Figure 1 The present invention is a flowchart of a method for frequent deletion, modification, query and optimization based on Clickhouse.

[0028] Figure 2 The present invention is a framework diagram of a system for frequent deletion, modification, query and optimization based on Clickhouse.

[0029] Figure 3The present invention is a flowchart of incremental data synchronization based on Clickhouse frequent deletion, modification and query optimization method. DETAILED DESCRIPTION

[0030] The present invention is further described below in conjunction with the accompanying drawings and embodiments.

[0031] The following will clearly and completely describe the concept, specific structure and technical effects of the present invention in combination with the embodiments and drawings, so as to fully understand the purpose, characteristics and effects of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of them. Based on the embodiments of the present invention, other embodiments obtained by technicians in this field without creative work are all within the scope of protection of the present invention. In addition, all the connection / connection relationships involved in the patent do not refer to the direct connection of components, but refer to the formation of a better connection structure by adding or reducing connection accessories according to the specific implementation situation. The various technical features in the invention can be combined interchangeably without conflicting with each other.

[0032] Reference Figure 1 As shown, the present invention discloses a method for frequent deletion and modification query optimization based on Clickhouse. Specifically, the method comprises the following steps:

[0033] S1. Data synchronization: parse and store real-time operation log data in the original database to the Kafka cluster and MongoDB.

[0034] In this embodiment, in step S1, the original database is an Oracle database or a Mysql database; and OGG or Canal is used to achieve data synchronization;

[0035] S2. Consumption of stream data. The application is connected to the Kafka cluster, MongoDB, and ClickHouse clusters respectively. When the application consumes stream data, it first queries the MongoDB data. If no data is found, it queries the ClickHouse cluster data.

[0036] In this embodiment, when the application side Application queries the data of the last three days, it goes to mongodb for query; when the application side Application queries the historical data, it goes to the clickhouse cluster for query; it should be noted that the historical data may include the data of the last three days, or may not include the data of the last three days, depending on the actual situation. In the case of high real-time performance, the historical data generally includes the data of the last three days.

[0037] S3: Operations on query data: When the application side Application queries data in MongoDB, updates or deletes the data, and directly operates MongoDB; when the application side Application does not query data in MongoDB, but queries data in the ClickHouse cluster, the query records of the Application are written back to MongoDB for query;

[0038] S4. Scheduled synchronization of incremental data. Mongodb is connected to the ClickHouse cluster. Mongodb regularly synchronizes incremental data to the ClickHouse cluster.

[0039] The integrity of the ClickHouse cluster data is ensured by periodically synchronizing incremental data to the ClickHouse cluster.

[0040] In this embodiment, if Figure 3 As shown, for the timing synchronization of incremental data in step S4, the present invention provides a specific embodiment, wherein the incremental data is first imported into a temporary table "table name_tmp

[0041] ", confirm whether the polling is completed. If completed, write the formal table into the temporary table, and then confirm whether the polling is completed. If completed, name the formal table "table name_tmp_tmp" and the temporary table "table name_tmp

[0042] " is renamed as a formal table, and the table "table name_tmp_tmp" is deleted to complete the synchronization of incremental data.

[0043] Reference Figure 2 As shown, the present invention also discloses a system based on Clickhouse frequent deletion, modification and query optimization, the system comprising an original database, a kafka cluster 10, a mongodb 20, an application end Application 30 and a clickhouse cluster 40;

[0044] The original database is used to store real-time operation log data; in this embodiment, the original database is an Oracle database or a Mysql database;

[0045] The kafka cluster 10 and mongodb 20 are respectively connected to the original database, and the real-time operation log data in the synchronized original database is parsed and stored in the kafka cluster 10 and mongodb 20;

[0046] The application end Application30 is connected to the kafka cluster 10, mongodb20 and clickhouse cluster 40 respectively. The application end Application30 is used to implement data query on mongodb20 and data query on clickhouse cluster 40; combined Figure 3 As shown, the data queried by the application end Application30 from mongodb20 is the data of the latest three days; the data queried by the application end Application30 from clickhouse cluster 40 is the historical data;

[0047] The clickhouse cluster 40 is connected to mongodb20, and mongodb20 synchronizes incremental data to the clickhouse cluster 40 at regular intervals.

[0048] Through the above-mentioned method and system based on Clickhouse frequent deletion and modification query optimization, the steps of the business process are revealed. Because the client often performs high-frequency addition, deletion and modification operations when doing real-time queries, but Clickhouse cluster 40 is not friendly to deletion and modification operations, which greatly affects performance. In addition, even if it is an insertion, if it is a single high-frequency insertion, it will seriously affect the performance of Clickhouse cluster 40 cluster, so such operations should be avoided and should be processed in batch form. Since real-time data needs a place to record these operations and needs to meet the requirements of real-time performance, the performance of mongodb is very good for small data volumes (three days of data); this can achieve real-time processing on the one hand, and does not affect the performance of Clickhouse cluster 40 on the other.

[0049] The following describes how the present invention solves the problem that Clickhouse cluster 40 is not suitable for high-concurrency scenarios. Since most of the data that need to be operated in most real-time applications are hot data in the last three days or so, MongoDB is introduced. Since MongoDB supports concurrent queries better and supports millisecond-level responses for small data volumes, this problem can be perfectly solved here.

[0050] Furthermore, we explain in detail how the system of the present invention can achieve millisecond-level response under high concurrency. As described above, most requests will be processed by mongodb20, and such processing is at the millisecond level. In addition, the remaining small part of the request is sent to the Clickhouse cluster 40. At this time, the concurrent query is not very high, and the Clickhouse cluster 40 responds to such queries in about a few milliseconds, so it is guaranteed that the entire system can achieve millisecond-level response.

[0051] It is necessary to synchronize data with mongodb20 regularly. The synchronization period depends on the business scenario. When Clickhouse cluster 40 is needed for online analysis OLAP, the synchronization operation can be accelerated. For example, 2,000 records can be synchronized once. If the business only requires T+1 data services, then synchronization can be done once a day.

[0052] In addition, direct multi-table operations will cause merge partitions to affect the performance of tables that provide external services. Therefore, the incremental data is first imported into a temporary table and then the formal table data is written back to the temporary table. When the data import is completed, it is renamed to a formal table to provide external services.

[0053] Therefore, through the above description, a method and system based on Clickhouse frequent deletion, modification and query optimization of the present invention can support fast real-time insertion and update operations, support high-concurrency queries for real-time data, and can quickly respond to query requests, and can achieve millisecond-level responses.

[0054] The above is a specific description of the preferred implementation of the present invention, but the invention is not limited to the embodiments. Those skilled in the art can make various equivalent modifications or substitutions without violating the spirit of the present invention. These equivalent modifications or substitutions are all included in the scope defined by the claims of this application.

Claims

1. A method for frequent deletion and modification based on Clickhouse, characterized in that: The method comprises the following steps: S1. Data synchronization: parse and store real-time operation log data in the original database to the Kafka cluster and MongoDB. S2. Consumption of stream data. The application is connected to the Kafka cluster, MongoDB, and ClickHouse clusters respectively. When the application consumes stream data, it first queries the MongoDB data. If no data is found, it queries the ClickHouse cluster data. S3: Operations on query data: When the application side Application queries data in MongoDB, updates or deletes the data, and directly operates MongoDB; when the application side Application does not query data in MongoDB, but queries data in the ClickHouse cluster, the query record of the Application is written back to the front-end MongoDB for query; S4. Scheduled synchronization of incremental data. Mongodb is connected to the ClickHouse cluster. Mongodb regularly synchronizes incremental data to the ClickHouse cluster.

2. According to claim 1, a method for frequent deletion and modification based on Clickhouse is characterized in that: In the step S1, the original database is an Oracle database or a MySQL database.

3. According to claim 1, a method for frequent deletion and modification based on Clickhouse is characterized in that: In the step S1, OGG or Canal is used to achieve data synchronization.

4. According to claim 1, a method for frequent deletion and modification based on Clickhouse is characterized in that: In the step S2, when the application queries the data of the latest three days, it queries in MongoDB.

5. According to claim 4, a method for frequent deletion and modification based on Clickhouse is characterized in that: In step S2, when the application queries historical data, it queries the clickhouse cluster.

6. A system based on Clickhouse frequent deletion, modification and optimization, characterized in that: The system includes the original database, kafka cluster, mongodb, application-side Application and clickhouse cluster; The original database is used to store real-time operation log data; The kafka cluster and mongodb are connected to the original database respectively, and the real-time operation log data in the original database is synchronized and parsed and stored in the kafka cluster and mongodb; The application end Application is connected to the kafka cluster, mongodb and clickhouse cluster respectively, and the application end Application is used to realize data query on mongodb and data query on clickhouse cluster; The ClickHouse cluster is connected to MongoDB, and MongoDB periodically synchronizes incremental data to the ClickHouse cluster.

7. A system based on Clickhouse frequent deletion, modification, query and optimization according to claim 6, characterized in that: The original database is an Oracle database or a Mysql database.

8. According to claim 6, a system based on Clickhouse frequent deletion, modification, query and optimization is characterized in that: The data queried by the application end Application in mongodb is the data of the most recent three days.

9. According to claim 6, a system based on Clickhouse frequent deletion, modification, query and optimization is characterized in that: The data that the application side Application queries the clickhouse cluster is historical data.

Images