Data processing method based on ClickHouse cold and hot cluster

Abstract

The invention provides a ClickHouse cold and hot cluster-based data processing method, which comprises the following steps of: determining a falling number routing rule according to ClickHouse cluster fragments, splitting periodic incremental data into a plurality of sub-files or sub-tables, reading the data by utilizing a Spark engine, and respectively writing the data into local tables of corresponding fragments of a hot cluster and a cold cluster, and finally, obtaining a query result from the corresponding cold and hot cluster according to a data query request rule. According to the method, efficient processing, intelligent storage and quick query of data can be realized, and the system performance and the resource utilization rate are remarkably improved.

Description

Technical Field

[0001] This application relates to the technical field of data processing, and in particular, to a data processing method based on ClickHouse hot and cold clusters. Background Art

[0002] With the advent of the big data era, the amount of data has shown an explosive growth. How to efficiently store, manage, and query this data has become a major challenge for enterprises and research institutions. Traditional database systems often have performance bottlenecks when dealing with large-scale data and cannot meet the requirements of real-time analysis and fast query.

[0003] ClickHouse, as an open-source columnar database management system, has been widely used in the field of big data analysis due to its excellent query performance and compression ability. However, a single ClickHouse cluster still faces challenges in terms of storage cost, query efficiency, and data availability when dealing with massive data. Summary of the Invention

[0004] In view of this, the embodiments of this application provide a data processing method based on ClickHouse hot and cold clusters, which can achieve efficient data processing, intelligent storage, and fast query, and significantly improve system performance and resource utilization.

[0005] The technical solution of the embodiments of this application is implemented as follows:

[0006] In a first aspect, the embodiments of this application provide a data processing method based on ClickHouse hot and cold clusters, and the method includes:

[0007] Obtain periodic incremental data, determine the falling number routing rule through each shard of the ClickHouse cluster, and split the periodic incremental data into a target number of sub-files and / or sub-tables based on the falling number routing rule;

[0008] Read the corresponding sub-files and / or sub-tables based on the Spark engine, and write the sub-files and / or sub-tables into the local tables of the corresponding shards of the first cluster and the second cluster respectively; wherein, the first cluster is a hot cluster, the second cluster is a cold cluster, and the data storage time of the hot cluster is less than the data storage time of the cold cluster;

[0009] In response to a data query request, obtain a query result from the corresponding hot cluster or cold cluster based on the data query request rule.

[0010] In a second aspect, the embodiments of this application further provide a data processing device based on ClickHouse hot and cold clusters, and the device includes:

[0011] A splitting module, configured to obtain periodic incremental data, determine a falling number routing rule through each shard of the ClickHouse cluster, and split the periodic incremental data into a target number of sub-files and / or sub-tables based on the falling number routing rule;

[0012] A writing module, configured to read the corresponding sub-files and / or sub-tables based on the Spark engine and write the sub-files and / or sub-tables into the local tables of the corresponding shards of the first cluster and the second cluster respectively; wherein, the first cluster is a hot cluster, the second cluster is a cold cluster, and the data storage time of the hot cluster is less than that of the cold cluster;

[0013] A query module, configured to respond to a data query request and obtain a query result from the corresponding hot cluster or cold cluster based on the data query request rule.

[0014] In a third aspect, an embodiment of the present application further provides an electronic device, including: a processor, a storage medium, and a bus. The storage medium stores machine-readable instructions executable by the processor. When the electronic device runs, the processor communicates with the storage medium through the bus, and the processor executes the machine-readable instructions to execute the data processing method based on ClickHouse hot and cold clusters according to any one of the first aspects.

[0015] In a fourth aspect, an embodiment of the present application further provides a computer-readable storage medium, on which a computer program is stored. When the computer program is run by a processor, it executes the data processing method based on ClickHouse hot and cold clusters according to any one of the first aspects.

[0016] The embodiments of the present application have the following beneficial effects:

[0017] By integrating ClickHouse hot and cold clusters with the Spark engine, fine-grained management and efficient processing of periodic incremental data are achieved. Through an intelligent falling number routing rule, the incremental data is split into multiple sub-files and / or sub-tables and accurately written into the hot cluster and the cold cluster, which not only ensures the real-time performance and fast access ability of hot data, but also makes full use of the storage scalability of the cold cluster and reduces the storage cost. In addition, the embodiments of the present application can flexibly respond to data query requests, intelligently select the query cluster according to the importance and access frequency of the data, and further improve the query efficiency and user experience. In summary, the embodiments of the present application significantly optimize the data processing process, improve the utilization rate of storage resources, and provide strong support for big data applications. Description of the Drawings

[0018] To more clearly illustrate the technical solutions of the embodiments of the present application, the accompanying drawings required for the embodiments will be briefly introduced below. It should be understood that the following accompanying drawings only show some embodiments of the present application and should not be regarded as limiting the scope. For those of ordinary skill in the art, without creative efforts, other related accompanying drawings can also be obtained based on these drawings.

[0019] Figure 1 It is a schematic flowchart of steps S101 - S103 provided by an embodiment of the present application;

[0020] Figure 2 It is a schematic diagram of the incremental data storage in the cold and hot clusters provided by an embodiment of the present application;

[0021] Figure 3 It is a schematic flowchart of steps S301 - S303 provided by an embodiment of the present application;

[0022] Figure 4 It is a schematic flowchart of steps S401 - S402 provided by an embodiment of the present application;

[0023] Figure 5 It is a schematic diagram of the query request for the ClickHouse cold and hot clusters provided by an embodiment of the present application;

[0024] Figure 6 It is a schematic flowchart of steps S601 - S602 provided by an embodiment of the present application;

[0025] Figure 7 It is a schematic structural diagram of a data processing device based on the ClickHouse cold and hot clusters provided by an embodiment of the present application;

[0026] Figure 8 It is a schematic structural diagram of the composition of an electronic device provided by an embodiment of the present application. Detailed implementation manners

[0027] To make the objectives, technical solutions, and advantages of the embodiments of the present application clearer, the technical solutions in the embodiments of the present application will be clearly and completely described below in conjunction with the accompanying drawings in the embodiments of the present application. It should be understood that the accompanying drawings in the present application only serve the purposes of illustration and description and are not used to limit the protection scope of the present application. Additionally, it should be understood that the schematic accompanying drawings are not drawn to actual scale. The flowcharts used in the present application show the operations implemented according to some embodiments of the present application. It should be understood that the operations in the flowchart may not be implemented in sequence, and steps without logical context relationships may be reversed in order or implemented simultaneously. In addition, those skilled in the art can add one or more other operations to the flowchart or remove one or more operations from the flowchart under the guidance of the content of the present application.

[0028] In the following description, reference is made to "some embodiments", which describe a subset of all possible embodiments. However, it is understood that "some embodiments" may be the same subset or different subsets of all possible embodiments, and may be combined with each other without conflict.

[0029] In addition, the described embodiments are only a part of the embodiments of the present application, rather than all embodiments. The components of the embodiments of the present application described and illustrated herein generally can be arranged and designed in various different configurations. Therefore, the following detailed description of the embodiments of the present application provided in the drawings is not intended to limit the scope of the claimed present application, but merely represents selected embodiments of the present application. All other embodiments obtained by those skilled in the art based on the embodiments of the present application without creative efforts fall within the scope of protection of the present application.

[0030] In the following description, the terms "first / second / third" involved only distinguish similar objects and do not represent a specific order for the objects. It is understood that "first / second / third" can be interchanged with a specific order or sequence when allowed, so that the embodiments of the present application described herein can be implemented in an order other than that illustrated or described herein.

[0031] It should be noted that the term "including" will be used in the embodiments of the present application to indicate the existence of the features stated thereafter, but does not exclude the addition of other features.

[0032] Unless otherwise defined, all technical and scientific terms used herein have the same meaning as commonly understood by those of ordinary skill in the technical field to which this application belongs. The terms used herein are for the purpose of describing the embodiments of the present application and are not intended to limit the present application.

[0033] See Figure 1 , Figure 1 is a schematic flowchart of steps S101 - S102 of the data processing method based on ClickHouse hot and cold clusters provided by the embodiments of the present application, and will be described in conjunction with Figure 1 the steps S101 - S102 shown.

[0034] In step S101, periodic incremental data is obtained, the falling number routing rules are determined through each shard of the ClickHouse cluster, and the periodic incremental data is split into a target number of sub - files and / or sub - tables based on the falling number routing rules.

[0035] Please refer to Figure 2 , Figure 2 is a schematic diagram of the incremental data falling into the database of the hot and cold clusters provided by the embodiments of the present application. As shown in Figure 2As shown, the starting point of this step of data processing involves extracting newly generated or updated data from data sources (such as databases, log files, etc.). In a ClickHouse cluster, data is usually sharded and distributed across multiple nodes to improve parallel processing capabilities. The falling number routing rule determines which shards the data should be distributed to. According to the falling number routing rule, the periodic incremental data is split into multiple sub-files or sub-tables. This step aims to ensure that the data can be evenly distributed in the cluster during subsequent processing, thereby optimizing performance.

[0036] In step S102, based on the Spark engine, the corresponding sub-files and / or sub-tables are read and written into the local tables of the corresponding shards in the first cluster and the second cluster respectively; wherein, the first cluster is a hot cluster, the second cluster is a cold cluster, and the data storage time of the hot cluster is less than that of the cold cluster.

[0037] Here, the Spark engine is used to read the sub-files or sub-tables generated in the previous step. Spark is a powerful big data processing engine that can efficiently process large-scale data sets.

[0038] The hot cluster stores recent data or data that is frequently accessed. Since this data has a high access frequency, storing it in the hot cluster can respond to query requests faster. The cold cluster stores historical data or data that is less frequently accessed. Since the access frequency of this data is low, it can be stored on a lower-cost storage medium, thereby reducing costs. The data storage time of the hot cluster is usually shorter because as time goes by, the access frequency of the data will gradually decrease, so it needs to be migrated to the cold cluster to free up storage space.

[0039] In step S103, in response to a data query request, the query result is obtained from the corresponding hot cluster or cold cluster based on the data query request rule.

[0040] Here, when there is a data query request, the system will decide whether to query data from the hot cluster or the cold cluster according to the characteristics of the query and the hotness or coldness of the data. For frequently queried hot data, reading from the hot cluster can obtain the result faster; while for less frequently queried cold data, reading from the cold cluster can reduce costs without affecting performance.

[0041] The above method achieves the dual goals of performance optimization and cost savings through a reasonable data splitting and distribution strategy.

[0042] In some embodiments, refer to Figure 3 , Figure 3It is a schematic flowchart of steps S301 - S303 provided by an embodiment of the present application. The periodic incremental data is Hive table data. Splitting the periodic incremental data into a target number of sub - files and / or sub - tables based on the falling number routing rule can be achieved through steps S301 - S303, and will be described in combination with each step.

[0043] In step S301, a custom partitioner is created in Spark.

[0044] In step S302, the Hive table data is read through Spark, and the custom partitioner is applied to partition the Hive table data; wherein, Spark reads the Hive table data through the DataFrame API or SQL query. The Hive table data is split into the target number of partitions, and each partition in the target number of partitions corresponds to the data to be sent to the corresponding ClickHouse shard.

[0045] In step S303, the partitioned data is written into sub - files and / or sub - tables.

[0046] Please continue to refer to Figure 2 , the custom partitioner is designed according to the business logic and the sharding strategy of the ClickHouse cluster. Its function is to determine how to split the data in the Hive table into multiple partitions, and these partitions will correspond to different shards of ClickHouse. The Hive table data is read through Spark, using the DataFrame API or SQL query function of Spark to read the incremental data from the Hive table. This data can be newly inserted data or data updated within a certain time period. The custom partitioner is applied to partition the data, and the read Hive table data is passed to the custom partitioner. The partitioner splits the data into multiple partitions according to preset rules (such as the timestamp of the data, ID range, etc.). The data in each partition will be sent to a specific shard of the ClickHouse cluster. The "target number" of partitions here refers to the number of partitions determined according to the sharding number of the ClickHouse cluster and business requirements. The amount of data in each partition should be relatively balanced to ensure the uniform distribution of data in the ClickHouse cluster. In Spark, the partitioned data can be saved as multiple sub - files (such as Parquet, ORC, etc. formats), or directly written into the pre - created sub - tables in the ClickHouse cluster. The implementation method of this step depends on specific business requirements and system architecture.

[0047] By reading the data of the Hive table into Spark, applying a custom partitioner for partitioning, and then writing the partitioned data into the hot and cold clusters of the ClickHouse cluster, the cold and hot separation storage of data can be achieved. This method not only improves the query efficiency of the data but also reduces the storage cost. At the same time, by utilizing the parallel processing ability of Spark, the data processing speed can be accelerated to meet the requirements of big data processing.

[0048] In some embodiments, referring to Figure 4 , Figure 4 is a schematic flowchart of steps S401 - S402 provided by the embodiments of the present application, which will be described in conjunction with each step.

[0049] In step S401, the corresponding data is read from the split files and / or split tables.

[0050] In step S402, based on the sharding information of the ClickHouse, the data is respectively written into the local tables of the corresponding shards of the first cluster and the second cluster through the custom partitioner according to the mapping relationship.

[0051] Here, the Spark engine will first read the data from the previously created split files (such as Parquet, ORC, etc.) or split tables. This data has been processed by the custom partitioner and has been partitioned according to logic (such as timestamp, ID, etc.). Before writing the data, Spark needs to know the sharding information of the ClickHouse cluster, including connection information such as the address, port, username, password, etc. of each shard. This information can be obtained through the configuration file or database metadata. The custom partitioner is not only used for partitioning during data reading but also for determining which ClickHouse shard the data should be sent to during data writing. This mapping relationship is established based on the sharding strategy of the ClickHouse cluster and the partitioning logic of the data.

[0052] According to the mapping relationship of the custom partitioner, the Spark engine will write the data into the local tables of the corresponding shards of the first cluster (hot cluster) and the second cluster (cold cluster) respectively. This step is usually implemented through the DataFrame API of Spark or a dedicated ClickHouse connector (such as spark-clickhouse-connector). When writing the data, it is also necessary to consider the data format and encoding method to ensure that the data can be correctly parsed and queried in ClickHouse.

[0053] In the above method, by reading data from sub-files and / or sub-tables and writing the data into corresponding shards of the first cluster and the second cluster respectively based on the sharding information of ClickHouse and the mapping relationship of the custom partitioner, we can achieve the cold and hot separation storage of data. This method not only improves the query efficiency of data, but also reduces the storage cost, and ensures the consistency and integrity of data. At the same time, by utilizing the parallel processing ability of Spark and the high-performance query engine of ClickHouse, the requirements of big data processing and high-concurrency queries can be met.

[0054] In some embodiments, the data storage time of the first cluster and the second cluster is determined by setting different TTL data retention policies. There are shard instances that are mutual primary and standby in the first cluster and / or the second cluster. The local tables maintained by two shard instances that are mutual primary and standby are realized by enabling internal shard synchronization.

[0055] Here, the data in the hot cluster is usually newly generated or frequently queried data. Therefore, its TTL policy will be set relatively short to ensure the timeliness and query efficiency of the data. For example, it can be set to be valid within 1 year after the data is generated, and the data exceeding this time will be automatically deleted or transferred to the cold cluster. The data in the cold cluster is usually data generated a long time ago or rarely queried data. Therefore, its TTL policy will be set relatively long to reduce the storage cost. For example, it can be set to be valid within five years after the data is generated, and the data exceeding this time can be deleted or archived according to business requirements.

[0056] In each cluster, each shard is responsible for maintaining the local table managed by each shard instance, and there are two shards with a mutual primary and standby relationship. The local tables and the data in the local tables maintained by the two shards with a mutual primary and standby relationship are the same. The two shards with a mutual primary and standby relationship are distributed and deployed on different physical nodes, and the shard instances that are mutual primary and standby are physically separated. When a certain shard instance that is mutual primary and standby fails or becomes unavailable, the standby shard instance can quickly take over its work to ensure the continuity of data and the availability of services. At the same time, the standby shard instance can also be used for load balancing of data reading to improve query performance.

[0057] ClickHouse will automatically handle the data synchronization problem between shards that are replicas of each other to ensure data consistency between them, which can be achieved by enabling internal shard synchronization; when reading data, the data can be selected to be read from the primary shard or the replica shard according to the load balancing policy.

[0058] In the above - mentioned manner, by setting different TTL data retention policies and the relationship between replica shards and primary - standby, the hot - cold cluster architecture of ClickHouse can effectively manage the data life cycle and improve data high - availability. This architecture not only meets the requirements of big data processing and high - concurrency queries, but also reduces storage costs and improves the fault - tolerance of the system.

[0059] In some embodiments, multiple Distribute instance nodes for distributed query are set in the first cluster and / or the second cluster. The multiple Distribute instance nodes are connected to the query interface through the deployed corresponding load balancer chproxy, and the query interface restricts the amount of data queried at one time through limit parameters.

[0060] Please refer to Figure 5 , Figure 5 which is a schematic diagram of a ClickHouse hot - cold cluster query request provided by an embodiment of the present application. As Figure 5 shown, distributed table nodes are the core components in the ClickHouse cluster responsible for storing and querying data. They achieve distributed storage and query of data through a distributed table engine (such as the Distributed engine). Distributed table nodes can hide the details of the underlying distributed structure, enabling users to query distributed tables in the same way as local tables. At the same time, they are also responsible for distributing query requests to each shard in the cluster and merging the query results of the shards to return to the client. In the first cluster and the second cluster, multiple distributed table nodes can be set according to business requirements and data characteristics. These nodes perform data exchange and query cooperation through network communication within the cluster. Each distributed table node needs to be configured with corresponding shard keys, distributed engine parameters, etc. to ensure the correct distribution of data and the efficient execution of queries.

[0061] The load balancer LB is a key component connecting distributed table nodes and the query interface. It is responsible for evenly distributing query requests to each distributed table node in the cluster to avoid single - point overload and improve query performance. The load balancer can also perform intelligent scheduling according to factors such as the load of nodes and the priority of query requests to achieve more efficient resource utilization and query response. The load balancer can be implemented by a hardware load balancer (such as F5, Cisco, etc.) or a software load balancer (such as Nginx, chProxy, etc.). In the ClickHouse cluster, a software load balancer can be used to reduce costs and improve flexibility. These load balancers can be dynamically adjusted and optimized through configuration files or APIs.

[0062] In the above - mentioned manner, by setting up distributed table nodes, load balancers, and query interfaces with limit parameters, the cluster architecture of ClickHouse can effectively support scenarios of distributed query and separation of hot and cold data. This architecture not only improves query performance and data availability but also reduces storage costs and operation and maintenance complexity.

[0063] In some embodiments, refer to Figure 6 , Figure 6 which is a schematic flowchart of steps S601 - S602 provided by the embodiments of the present application and will be described in combination with each step.

[0064] In step S601, in response to a data query request, determine the data period queried by the query request.

[0065] In step S602, if the data period is within the first period range, route the query request to the first cluster and perform a distributed query in the first cluster based on the load - balancing policy; if the data period is within the second period range, route the query request to the second cluster and perform a distributed query in the second cluster based on the load - balancing policy.

[0066] Please continue to refer to Figure 5 , when a user submits a data query request through a query interface (such as an HTTP API, SQL client, etc.), the system will first receive and parse this request. During the process of parsing the query request, the system will identify the period of the data that the user wants to query. This period is a specific time point, time period, or a relative time related to the current time (such as "data for one month").

[0067] Then, the system will compare the data period with the predefined first period range and second period range according to preset rules or configurations. The first period range usually corresponds to hot data, that is, data generated recently or frequently queried; the second period range corresponds to cold data, that is, data generated a long time ago or rarely queried. According to the judgment result of the data period, the system will route the query request to the corresponding cluster: if the data period is within the first period range, route the query request to the first cluster (hot cluster).

[0068] If the data period is within the second period range, route the query request to the second cluster (cold cluster). In the target cluster, the system will distribute the query request to each distributed table node in the cluster based on the load - balancing policy. These nodes will execute the query operation in parallel and return the results to the load balancer. The load balancer will merge these results and return the final query result to the user.

[0069] In the above - mentioned method, by routing query requests to corresponding clusters according to the data expiration period and performing distributed queries in combination with load - balancing strategies, the hot - cold data separation architecture of the ClickHouse cluster can effectively improve query performance and data availability. This architecture not only meets the requirements of big - data processing and high - concurrency queries, but also reduces storage costs and operation and maintenance complexity.

[0070] In some embodiments, the method further includes:

[0071] If the first cluster fails and the data expiration period is within the first expiration - period range, route the query request to the second cluster;

[0072] Query the data specified by the query request in the second cluster.

[0073] Here, the system needs to monitor the status of the first cluster in real - time, including the health status of each node, network communication, data storage, and query performance, etc. When it detects that the first cluster fails or has an anomaly (such as a node crashing, network interruption, or a significant drop in performance), the system will trigger a fault - recovery mechanism. After the fault - recovery mechanism is triggered, if a query request with a data expiration period within the first expiration - period range is received at this time (i.e., a request that should originally be routed to the first cluster), the system will redirect this request to the second cluster. The redirected query request will be sent to the second cluster and perform a distributed query operation in the second cluster.

[0074] In the above - mentioned method, when the first cluster fails, redirecting the query requests that should originally be routed to the first cluster to the second cluster for processing is an important measure to ensure the high availability and fault tolerance of the hot - cold data separation architecture of the ClickHouse cluster. Through reasonable mechanisms such as fault detection, query redirection, data migration and synchronization, load balancing, and query - performance optimization, the system can maintain the continuity and availability of services when a failure occurs.

[0075] In summary, the embodiments of the present application have the following beneficial effects:

[0076] (1) Efficient hot - cold data separation and management: In the embodiments of the present application, by periodically obtaining incremental data and splitting it into a target number of sub - files and / or sub - tables according to the data - falling routing rules, refined data management is achieved. This splitting method helps to optimize data storage and query performance, especially in scenarios with a large amount of data. By storing hot data and cold data in hot clusters and cold clusters respectively and setting different TTL data - retention policies, automatic separation and management of hot and cold data are realized. This helps to reduce storage costs, improve data - query efficiency, and meet different business requirements.

[0077] (2) Flexible Spark engine integration: Leveraging the powerful computing capabilities of the Spark engine, embodiments of this application can efficiently read and process data in split files and / or split tables. By customizing the partitioner, Spark can accurately write data into the corresponding shards of the ClickHouse cluster according to the mapping relationship, ensuring data accuracy and consistency. The tight integration of Spark and ClickHouse makes data processing and querying more flexible and efficient. Users can fully utilize the distributed computing capabilities of Spark and the columnar storage advantages of ClickHouse to achieve complex data analysis and query tasks.

[0078] (3) Highly available cluster architecture: Embodiments of this application set up replica shards in the first cluster and the second cluster, implementing a data redundancy backup and fault recovery mechanism. When a certain cluster or node fails, the system can quickly route query requests to other available clusters or nodes, ensuring service continuity and availability. In addition, through the setting of distributed table nodes and load balancers, embodiments of this application achieve balanced distribution and efficient processing of query requests, improving the overall performance and stability of the system.

[0079] (4) Intelligent query routing and load balancing: According to the different data deadlines, embodiments of this application can intelligently route query requests to the hot cluster or the cold cluster, achieving precise matching and efficient processing of query requests. This query routing strategy based on data deadlines helps reduce query latency and improve the user experience. At the same time, through the application of the load balancing strategy, embodiments of this application can reasonably allocate query loads, avoid overloading of a single node or cluster, and improve the scalability and stability of the system.

[0080] (5) Limiting query parameters to protect system resources: By setting limit parameters in the query interface, embodiments of this application can limit the amount of data queried in a single query, preventing system resource exhaustion or performance degradation caused by overly large query requests. This limiting strategy helps protect system resources and ensures the stable operation of the system and the user's query experience.

[0081] In summary, the data processing method based on ClickHouse hot and cold clusters has beneficial effects such as efficient data management, flexible Spark engine integration, highly available cluster architecture, intelligent query routing and load balancing, and limiting query parameters. These effects together improve the overall performance and stability of the system, meet the requirements of big data processing and high-concurrency queries, and provide strong support for the development of the business.

[0082] Based on the same inventive concept, in the embodiments of the present application, there is also provided a data processing device based on ClickHouse hot and cold clusters corresponding to the data processing method based on ClickHouse hot and cold clusters in the first embodiment. Since the principle of solving problems by the device in the embodiments of the present application is similar to the above-mentioned data processing method based on ClickHouse hot and cold clusters, the implementation of the device can refer to the implementation of the method, and the repeated parts will not be described again.

[0083] As Figure 7 shown, Figure 7 is a schematic structural diagram of a data processing device 700 based on ClickHouse hot and cold clusters provided by an embodiment of the present application. The data processing device 700 based on ClickHouse hot and cold clusters includes:

[0084] A splitting module 701, configured to obtain periodic incremental data, determine a falling number routing rule through each shard of the ClickHouse cluster, and split the periodic incremental data into a target number of sub-files and / or sub-tables based on the falling number routing rule;

[0085] A writing module 702, configured to read the corresponding sub-files and / or sub-tables based on the Spark engine, and write the sub-files and / or sub-tables into the local tables of the corresponding shards of the first cluster and the second cluster respectively; wherein, the first cluster is a hot cluster, the second cluster is a cold cluster, and the data storage time of the hot cluster is less than the data storage time of the cold cluster;

[0086] A query module 703, configured to obtain a query result from the corresponding hot cluster or cold cluster based on a data query request rule in response to a data query request.

[0087] Those skilled in the art should understand that Figure 7 The implementation functions of the various units in the data processing device 700 based on ClickHouse hot and cold clusters shown can be understood with reference to the relevant descriptions of the foregoing data processing method based on ClickHouse hot and cold clusters. Figure 7 The functions of the various units in the data processing device 700 based on ClickHouse hot and cold clusters shown can be implemented by a program running on a processor or by specific logic circuits.

[0088] In a possible implementation manner, the periodic incremental data is Hive table data. The splitting module 701 splits the periodic incremental data into a target number of sub-files and / or sub-tables based on the falling number routing rule, including:

[0089] Create a custom partitioner in Spark;

[0090] Read the data of the Hive table through Spark, and apply the custom partitioner to partition the data of the Hive table; wherein, Spark reads the data of the Hive table through the DataFrame API or SQL query, and the data of the Hive table is split into the target number of partitions, and each partition in the target number of partitions corresponds to the data to be sent to the corresponding ClickHouse shard;

[0091] Write the partitioned data into sub-files and / or sub-tables.

[0092] In a possible implementation manner, the writing module 702 reads the corresponding sub-files and / or sub-tables based on the Spark engine, and writes the sub-files and / or sub-tables into the corresponding shards of the first cluster and the second cluster respectively, including:

[0093] Read the corresponding data from the sub-files and / or sub-tables;

[0094] Based on the shard information of the ClickHouse, write the data into the local tables of the corresponding shards of the first cluster and the second cluster respectively according to the mapping relationship through the custom partitioner.

[0095] In a possible implementation manner, the data storage time of the first cluster and the second cluster is determined by setting different TTL data retention policies, and there are shard instances that are mutually primary and standby in the first cluster and / or the second cluster. The local tables maintained by the two shard instances that are mutually primary and standby are realized by enabling internal shard synchronization.

[0096] In a possible implementation manner, multiple Distribute instance nodes for distributed query are set in the first cluster and / or the second cluster. The multiple Distribute instance nodes are connected to the query interface through the deployed corresponding load balancer chproxy, and the query interface restricts the amount of data queried each time through limit parameters.

[0097] In a possible implementation manner, the query module 703 responds to a data query request and queries through the first cluster or the second cluster, including:

[0098] In response to a data query request, determine the data period queried by the query request;

[0099] If the data period is in the first period interval, route the query request to the first cluster and perform distributed query in the first cluster based on the load balancing strategy; if the data period is in the second period interval, route the query request to the second cluster and perform distributed query in the second cluster based on the load balancing strategy.

[0100] In a possible implementation, the query module 703 further includes:

[0101] If the first cluster fails and the data deadline is within the first deadline range, route the query request to the second cluster;

[0102] Query the data specified by the query request in the second cluster.

[0103] The above data processing device based on ClickHouse hot and cold clusters has the following beneficial effects:

[0104] (1) Efficient separation and management of hot and cold data: In the embodiment of the present application, by periodically obtaining incremental data and splitting it into a target number of sub-files and / or sub-tables according to the falling data routing rule, refined management of data is achieved. This splitting method helps to optimize the storage and query performance of data, especially in the scenario of large data volumes. By storing hot data and cold data in the hot cluster and the cold cluster respectively, and setting different TTL data retention policies, automatic separation and management of hot and cold data are realized. This helps to reduce storage costs, improve data query efficiency, and meet different business requirements.

[0105] (2) Flexible integration of the Spark engine: Utilizing the powerful computing power of the Spark engine, the embodiment of the present application can efficiently read and process the data in the sub-files and / or sub-tables. By customizing the partitioner, Spark can accurately write the data into the corresponding slices of the ClickHouse cluster according to the mapping relationship, ensuring the accuracy and consistency of the data. The close integration of Spark and ClickHouse makes data processing and query more flexible and efficient. Users can make full use of the distributed computing power of Spark and the columnar storage advantage of ClickHouse to implement complex data analysis and query tasks.

[0106] (3) Highly available cluster architecture: In the embodiment of the present application, replica slices are set in the first cluster and the second cluster, realizing a redundant backup and fault recovery mechanism for data. When a certain cluster or node fails, the system can quickly route the query request to other available clusters or nodes to ensure the continuity and availability of the service. In addition, through the setting of distributed table nodes and load balancers, the embodiment of the present application realizes the balanced distribution and efficient processing of query requests, improving the overall performance and stability of the system.

[0107] (4) Intelligent query routing and load balancing: According to the different data deadlines, the embodiments of the present application can intelligently route query requests to the hot cluster or the cold cluster, achieving accurate matching and efficient processing of query requests. This query routing strategy based on data deadlines helps reduce query latency and improve the user experience. At the same time, through the application of the load balancing strategy, the embodiments of the present application can reasonably allocate query loads, avoid overloading of individual nodes or clusters, and improve the scalability and stability of the system.

[0108] (5) Limiting query parameters to protect system resources: By setting limit parameters in the query interface, the embodiments of the present application can limit the amount of data queried at one time, preventing system resource exhaustion or performance degradation caused by overly large query requests. This limiting strategy helps protect system resources and ensure the stable operation of the system and the query experience of users.

[0109] In summary, the data processing method based on ClickHouse hot and cold clusters has beneficial effects such as efficient data management, flexible Spark engine integration, highly available cluster architecture, intelligent query routing and load balancing, and limiting query parameters. These effects together improve the overall performance and stability of the system, meet the requirements of big data processing and high-concurrency queries, and provide strong support for the development of the business.

[0110] As Figure 8 shown, Figure 8 is a schematic structural diagram of an electronic device 800 provided by an embodiment of the present application. The electronic device 800 includes:

[0111] A processor 801, a storage medium 802, and a bus 803. The storage medium 802 stores machine-readable instructions executable by the processor 801. When the electronic device 800 runs, the processor 801 communicates with the storage medium 802 through the bus 803. The processor 801 executes the machine-readable instructions to perform the steps of the data processing method based on ClickHouse hot and cold clusters described in the embodiments of the present application.

[0112] In actual application, the various components in the electronic device 800 are coupled together through the bus 803. It can be understood that the bus 803 is used to realize the connection and communication between these components. In addition to the data bus, the bus 803 also includes a power bus, a control bus, and a status signal bus. However, for the sake of clear illustration, in Figure 8 all kinds of buses are labeled as the bus 803.

[0113] The above-mentioned electronic device has the following beneficial effects:

[0114] (1)Efficient separation and management of hot and cold data: In the embodiments of this application, by periodically obtaining incremental data and splitting it into a target number of sub-files and / or sub-tables according to the falling number routing rules, refined data management is achieved. This splitting method helps to optimize data storage and query performance, especially in scenarios with large amounts of data. By storing hot data and cold data in hot clusters and cold clusters respectively and setting different TTL data retention policies, automatic separation and management of hot and cold data are realized. This helps to reduce storage costs, improve data query efficiency, and meet different business requirements.

[0115] (2)Flexible integration of Spark engine: Utilizing the powerful computing power of the Spark engine, the embodiments of this application can efficiently read and process data in sub-files and / or sub-tables. By customizing the partitioner, Spark can accurately write data into the corresponding slices of the ClickHouse cluster according to the mapping relationship, ensuring data accuracy and consistency. The close integration of Spark and ClickHouse makes data processing and query more flexible and efficient. Users can make full use of the distributed computing power of Spark and the columnar storage advantages of ClickHouse to achieve complex data analysis and query tasks.

[0116] (3)Highly available cluster architecture: In the embodiments of this application, replica slices are set in the first cluster and the second cluster to implement a data redundancy backup and fault recovery mechanism. When a certain cluster or node fails, the system can quickly route query requests to other available clusters or nodes to ensure service continuity and availability. In addition, through the setting of distributed table nodes and load balancers, the embodiments of this application achieve balanced distribution and efficient processing of query requests, improving the overall performance and stability of the system.

[0117] (4)Intelligent query routing and load balancing: According to the different data deadlines, the embodiments of this application can intelligently route query requests to hot clusters or cold clusters, achieving accurate matching and efficient processing of query requests. This query routing strategy based on data deadlines helps to reduce query latency and improve the user experience. At the same time, through the application of load balancing strategies, the embodiments of this application can reasonably distribute query loads, avoid overloading of individual nodes or clusters, and improve the scalability and stability of the system.

[0118] (5)Restricting query parameters to protect system resources: By setting restriction parameters in the query interface, the embodiments of this application can limit the amount of data queried at one time, preventing system resource exhaustion or performance degradation caused by overly large query requests. This restriction strategy helps to protect system resources and ensure the stable operation of the system and the query experience of users.

[0119] In summary, the data processing method based on ClickHouse hot and cold clusters has beneficial effects such as efficient data management, flexible Spark engine integration, highly available cluster architecture, intelligent query routing and load balancing, and restricted query parameters. These effects together improve the overall performance and stability of the system, meet the requirements of big data processing and high-concurrency queries, and provide strong support for the development of the business.

[0120] The embodiment of the present application also provides a computer-readable storage medium. The storage medium stores executable instructions, and when the executable instructions are executed by at least one processor 801, the data processing method based on ClickHouse hot and cold clusters described in the embodiment of the present application is implemented.

[0121] In some embodiments, the storage medium may be a ferromagnetic random access memory (FRAM), read-only memory (ROM), programmable read-only memory (PROM), erasable programmable read-only memory (EPROM), electrically erasable programmable read-only memory (EEPROM), flash memory, magnetic surface memory, optical disc, or compact disc read-only memory (CD ROM), etc.; it may also be various devices including one or any combination of the above memories.

[0122] In some embodiments, the executable instructions may be in the form of a program, software, software module, script, or code, written in any form of programming language (including compiled or interpreted languages, or declarative or procedural languages), and may be deployed in any form, including being deployed as an independent program or being deployed as a module, component, subroutine, or other unit suitable for use in a computing environment.

[0123] As an example, executable instructions may or may not correspond to files in a file system and may be stored as part of a file that holds other programs or data. For example, they may be stored in one or more scripts in a HyperText Markup Language (HTML) document, in a single file dedicated to the program under discussion, or in multiple cooperating files (such as files that store one or more modules, subroutines, or code portions).

[0124] As an example, executable instructions may be deployed to execute on one computing device, or on multiple computing devices located at one site, or on multiple computing devices distributed across multiple sites and interconnected by a communication network.

[0125] The above computer-readable storage medium has the following beneficial effects:

[0126] (1) Efficient hot and cold data separation and management: In the embodiments of the present application, by periodically acquiring incremental data and splitting it into a target number of sub-files and / or sub-tables according to the falling number routing rule, refined data management is achieved. This splitting method helps to optimize the storage and query performance of data, especially in large data volume scenarios. By storing hot data and cold data in a hot cluster and a cold cluster respectively and setting different TTL data retention policies, automatic separation and management of hot and cold data are realized. This helps to reduce storage costs, improve data query efficiency, and meet different business requirements.

[0127] (2) Flexible Spark engine integration: Leveraging the powerful computing power of the Spark engine, the embodiments of the present application can efficiently read and process data in sub-files and / or sub-tables. By customizing the partitioner, Spark can accurately write data into the corresponding slices of the ClickHouse cluster according to the mapping relationship, ensuring data accuracy and consistency. The close integration of Spark and ClickHouse makes data processing and query more flexible and efficient. Users can make full use of the distributed computing power of Spark and the columnar storage advantage of ClickHouse to achieve complex data analysis and query tasks.

[0128] (3) Highly available cluster architecture: In the embodiments of the present application, replica slices are set in the first cluster and the second cluster to implement a data redundancy backup and fault recovery mechanism. When a certain cluster or node fails, the system can quickly route query requests to other available clusters or nodes to ensure service continuity and availability. In addition, through the setting of distributed table nodes and load balancers, the embodiments of the present application achieve balanced distribution and efficient processing of query requests, improving the overall performance and stability of the system.

[0129] (4) Intelligent query routing and load balancing: According to the different data deadlines, the embodiments of the present application can intelligently route query requests to the hot cluster or the cold cluster, achieving accurate matching and efficient processing of query requests. This query routing strategy based on data deadlines helps reduce query latency and improve the user experience. At the same time, through the application of the load balancing strategy, the embodiments of the present application can reasonably allocate query loads, avoid overloading of a single node or cluster, and improve the scalability and stability of the system.

[0130] (5) Limiting query parameters to protect system resources: By setting limit parameters in the query interface, the embodiments of the present application can limit the amount of data queried at a single time, preventing system resource exhaustion or performance degradation caused by overly large query requests. This limiting strategy helps protect system resources and ensure the stable operation of the system and the query experience of users.

[0131] In summary, the data processing method based on ClickHouse hot and cold clusters has beneficial effects such as efficient data management, flexible Spark engine integration, highly available cluster architecture, intelligent query routing and load balancing, and limiting query parameters. These effects together enhance the overall performance and stability of the system, meet the requirements of big data processing and high-concurrency queries, and provide strong support for the development of the business.

[0132] In several embodiments provided by the present application, it should be understood that the disclosed methods and electronic devices can be implemented in other ways. The device embodiments described above are merely illustrative. For example, the division of the units is only a logical function division, and there may be other division methods in actual implementation. For example, multiple units or components can be combined, or can be integrated into another system, or some features can be ignored, or not executed. In addition, the coupling, direct coupling, or communication connection between the various components shown or discussed with each other can be through some interfaces, and the indirect coupling or communication connection of the devices or units can be electrical, mechanical, or other forms.

[0133] The modules described as separate components may or may not be physically separated, and the components shown as modules may or may not be physical units, that is, they can be located in one place, or can be distributed to multiple network units. Some or all of the units can be selected according to actual needs to achieve the purpose of the solution of this embodiment.

[0134] In addition, the functional units in each embodiment of the present application can be integrated into one processing unit, or each unit can exist physically alone, or two or more units can be integrated into one unit.

[0135] When the above-mentioned functions are implemented in the form of software functional units and sold or used as independent products, they can be stored in a non-volatile computer-readable storage medium executable by a processor. Based on such an understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, or a part of this technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions for causing a computer device (which may be a personal computer, a platform server, or a network device, etc.) to execute all or part of the steps of the methods described in various embodiments of this application. The aforementioned storage medium includes: various media such as USB flash drives, mobile hard disks, ROM, RAM, magnetic disks, or optical discs that can store program codes.

[0136] The above is only the specific implementation manner of this application, but the protection scope of this application is not limited thereto. Any person skilled in the art within the technical scope disclosed by this application can easily think of changes or substitutions, which should all be covered within the protection scope of this application. Therefore, the protection scope of this application should be subject to the protection scope of the claims.

Claims

1. A data processing method based on ClickHouse hot and cold clusters, characterized in that: The method comprises: Obtain periodic incremental data, determine the fall-number routing rules through each shard of the ClickHouse cluster, and split the periodic incremental data into a target number of sub-files and / or sub-tables based on the fall-number routing rules; Read the corresponding sub-files and / or sub-tables based on the Spark engine, and write the sub-files and / or sub-tables into local tables of the corresponding shards of the first cluster and the second cluster respectively; wherein the first cluster is a hot cluster, the second cluster is a cold cluster, and the data storage time of the hot cluster is shorter than the data storage time of the cold cluster; In response to the data query request, a query result is obtained from the corresponding hot cluster or cold cluster based on the data query request rule.

2. The method according to claim 1, characterized in that The periodic incremental data is Hive table data, and the step of splitting the periodic incremental data into a target number of sub-files and / or sub-tables based on the falling number routing rule includes: Creating custom partitioners in Spark; Read the Hive table data through Spark, and apply the custom partitioner to partition the Hive table data; wherein Spark reads the Hive table data through DataFrame API or SQL query, and the Hive table data is split into the target number of partitions, and each partition in the target number of partitions corresponds to data to be sent to the corresponding ClickHouse shard; Write the partitioned data into partitioned files and / or partitioned tables.

3. The method according to claim 2, characterized in that The reading of corresponding sub-files and / or sub-tables based on the Spark engine, and writing the sub-files and / or sub-tables into shards corresponding to the first cluster and the second cluster, respectively, includes: Read corresponding data from the sub-files and / or sub-tables; Based on the sharding information of ClickHouse, the data is written into the local tables of the corresponding shards of the first cluster and the second cluster respectively according to the mapping relationship through the custom partitioner.

4. The method according to claim 1, characterized in that The data storage time of the first cluster and the second cluster is determined by setting different TTL data retention policies. There are shard instances that are mutually primary and backup in the first cluster and / or the second cluster. The local tables maintained by the two shard instances that are mutually primary and backup are achieved by enabling internal synchronization of the shards.

5. The method according to claim 1, characterized in that The first cluster and / or the second cluster are provided with a plurality of Distribute instance nodes for distributed query, and the plurality of Distribute instance nodes are connected to the query interface by deploying a corresponding load balancer chproxy, and the query interface limits the amount of data for a single query by limiting parameters.

6. The method according to claim 5, characterized in that The step of querying the data through the first cluster or the second cluster in response to the data query request includes: In response to a data query request, determining a data period queried by the query request; If the data deadline is within the first deadline interval, the query request is routed to the first cluster, and a distributed query is performed in the first cluster based on a load balancing strategy; if the data deadline is within the second deadline interval, the query request is routed to the second cluster, and a distributed query is performed in the second cluster based on a load balancing strategy.

7. The method according to claim 6, characterized in that The method further comprises: If the first cluster fails and the data deadline is within the first deadline interval, routing the query request to the second cluster; The second cluster is queried for data specified by the query request.

8. A data processing device based on ClickHouse cold and hot clusters, characterized in that: The device comprises: A segmentation module is used to obtain periodic incremental data, determine the fall-number routing rules through each shard of the ClickHouse cluster, and split the periodic incremental data into a target number of sub-files and / or sub-tables based on the fall-number routing rules; A writing module is used to read corresponding sub-files and / or sub-tables based on the Spark engine, and write the sub-files and / or sub-tables into local tables of the corresponding shards of the first cluster and the second cluster respectively; wherein the first cluster is a hot cluster, the second cluster is a cold cluster, and the data storage time of the hot cluster is shorter than the data storage time of the cold cluster; The query module is used to respond to a data query request and obtain a query result from a corresponding hot cluster or a cold cluster based on a data query request rule.

9. An electronic device, characterized in that: include: A processor, a storage medium and a bus, wherein the storage medium stores machine-readable instructions executable by the processor. When the electronic device is running, the processor communicates with the storage medium through the bus, and the processor executes the machine-readable instructions to execute the data processing method based on ClickHouse hot and cold clusters as described in any one of claims 1 to 7.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, and when the computer program is executed by the processor, the data processing method based on ClickHouse hot and cold clusters as described in any one of claims 1 to 7 is executed.

Images