Clickhome database data writing method and device

Abstract

The invention relates to a Clickhome database data writing method and device, and relates to the technical field of databases, the method comprises the following steps: obtaining current to-be-written data; writing the to-be-written data into a newly added second node, determining target data in the existing first node in response to the control instruction, writing the target data into the second node, and deleting the target data in the first node; and writing subsequent to-be-written data into the first node and the second node. According to the method and the device, data balance between the first node and the second node can be realized based on an automatic data rebalancing mechanism of the Clickhome database, and the data hotspot problem of the newly added second node is relieved.

Description

Technical Field

[0001] The invention relates to the technical field of databases, and in particular to a method and device for writing data into a Clickhouse database. Background Art

[0002] In the industrial and commercial photovoltaic energy storage system, the data of equipment such as inverters, batteries, fire protection systems, and air conditioning systems are standardized and transmitted to the monitoring cloud platform through 5G / 4G / WIFI / Ethernet. The cloud platform monitors the received data such as voltage, current, power, temperature and humidity, power generation, battery capacity, etc. in real time, and analyzes the real-time and historical data of the power generation system and energy storage system to form corresponding reports and statements to provide decision support for managers. The amount of data generated is getting bigger and bigger, and the requirements for data processing and real-time analysis are getting higher and higher. At the same time, a large amount of historical data needs to be stored for a long time, so the data of the cloud platform is written to the Clickhouse data warehouse for storage. ClickHouse divides the data table into multiple logical fragments through sharding, each shard corresponds to a part of the data, and distributes it to different nodes. When writing data to Clickhouse, when the amount of data reaches a certain level, nodes will be added. Since Clickhouse itself has an automatic rebalancing mechanism, in order to achieve the purpose of data balance, the newly added shards will fall into the newly added nodes first. At this time, there will be obvious hot spots (too many service requests and traffic concentration). Summary of the invention

[0003] The Clickhouse database data writing method and device provided by the embodiments of the present invention can at least alleviate the hot spot problem when the Clickhouse database writes data to a new node.

[0004] According to one aspect of the present application, a method for writing data to a Clickhouse database is provided, the method comprising: obtaining current data to be written; writing the data to be written to a newly added second node, and determining target data in an existing first node in response to a control instruction, writing the target data to the second node, and deleting the target data in the first node; writing subsequent data to be written to the first node and the second node.

[0005] According to another aspect of the present application, a Clickhouse database data writing device is also provided, which includes: an acquisition module, used to acquire current data to be written; a management module, used to write the data to be written into a newly added second node, and determine the target data in the existing first node in response to a control instruction, write the target data into the second node, and delete the target data in the first node; a writing module, used to write subsequent data to be written into the first node and the second node.

[0006] According to another aspect of the present application, an electronic device is provided, including: a processor, and a memory storing a program, wherein the program includes instructions, and when the instructions are executed by the processor, the processor executes the above method.

[0007] According to another aspect of the present application, a non-transitory machine-readable medium storing computer instructions is also provided, wherein the computer instructions are used to enable the computer to execute the above method.

[0008] The Clickhouse database data writing method and device provided by the invention embodiment include: obtaining the current data to be written; writing the data to be written into a newly added second node, and determining the target data in the existing first node in response to a control instruction, writing the target data into the second node, and deleting the target data in the first node; writing the subsequent data to be written into the first node and the second node. The embodiment of the present invention writes the current data to be written into the newly added second node, and at the same time, determines the target data in the existing first node in response to a control instruction, and reduces the pressure of the first node by writing the target data in the first node into the second node and deleting the target data in the first node, so that when writing the subsequent data to be written, the data balance between the first node and the second node can be achieved based on the automatic data rebalancing mechanism of the Clickhouse database, and the data hot spot problem of the newly added second node can be alleviated. BRIEF DESCRIPTION OF THE DRAWINGS

[0009] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the following briefly introduces the drawings required for use in the embodiments or the prior art descriptions. Obviously, the drawings described below are only some embodiments of the present invention, and for ordinary technicians in this field, other embodiments can be obtained based on these drawings without creative work.

[0010] Figure 1 It is a flow chart of a data processing method of a data acquisition device according to an embodiment of the present invention;

[0011] Figure 2It is a schematic diagram of the development of a data warehouse according to an embodiment of the present invention;

[0012] Figure 3 It is a schematic diagram of the data layering processing flow of an embodiment of the invention;

[0013] Figure 4 It is a schematic diagram of the data warehouse data processing and flow flow of an embodiment created by the present invention;

[0014] Figure 5 It is a schematic diagram of the data warehouse cluster architecture of an embodiment of the present invention;

[0015] Figure 6 It is a schematic diagram of a batch data writing process according to an embodiment of the present invention;

[0016] Figure 7 It is a schematic diagram of a write failure processing flow of an embodiment of the present invention;

[0017] Figure 8 It is a schematic diagram of the Flink fault tolerance mechanism of an embodiment created by the present invention;

[0018] Fig. 9 It is a structural schematic diagram of the electronic device created by the present invention. DETAILED DESCRIPTION

[0019] The embodiments of the present invention will be described in more detail below with reference to the accompanying drawings. Although certain embodiments of the present invention are shown in the accompanying drawings, it should be understood that the present invention can be implemented in various forms and should not be construed as being limited to the embodiments described herein, which are instead provided to provide a more thorough and complete understanding of the present invention. It should be understood that the drawings and embodiments of the present invention are only for exemplary purposes and are not intended to limit the scope of protection of the present invention.

[0020] First, the terms involved are explained.

[0021] Flink: Apache Flink is an open source stream processing framework, the core of which is a distributed streaming data flow engine written in Java (a widely used object-oriented programming language) and Scala (a multi-paradigm programming language). Flink executes any streaming data program in a data-parallel and pipelined manner. Flink's pipeline runtime system can execute batch and stream processing programs, making it an excellent real-time stream-batch integrated framework.

[0022] Clickhouse: Clickhouse is an open source column-based database for online analytical processing (OLAP). Clickhouse allows analysis of data that is updated in real time. The system is designed for high performance and can demonstrate excellent performance at the PB level of data.

[0023] Kafka: Kafka is an open source stream processing platform written in Scala and Java. Kafka is a high-throughput distributed publish-subscribe messaging system that can handle all the action stream data of consumers in the website.

[0024] BalancedClickhouseDataSource: A Java-based load-balancing client that writes data to a single Clickhouse machine or cluster.

[0025] Hotspot: A large number of requests or traffic hits the same shard, module, or service.

[0026] From the emergence of the concept of data warehouse to the present, data warehouse architecture has gone through the initial traditional data warehouse architecture, offline data warehouse, offline big data architecture, Lambda architecture (a design pattern for big data processing, aimed at solving the needs of processing offline batch data and real-time data streams in large-scale distributed data systems at the same time), Kappa architecture (a big data processing architecture pattern, which is a simplification and optimization of Lambda architecture) and Flink-based real-time data warehouse architecture (such as Figure 2 As shown in the figure, data architecture technology is constantly evolving, and its essence is to develop in the direction of stream-batch integration, completing real-time computing in the most natural and minimal cost. Compared with the real-time stream-batch integrated data warehouse architecture based on Flink, the shortcomings of traditional solutions are as follows: Traditional data warehouse: It is difficult to carry massive data based on traditional databases; Offline big data architecture: Based on big data technology, but the real-time performance is not good; Lambda architecture: Lambda architecture, a more serious problem is that two sets of logic need to be maintained. One part is implemented in the batch engine, and the other part is implemented in the streaming engine, which has a high maintenance cost. In addition, the resource consumption is also large. Kappa architecture: The biggest problem of Kappa architecture is that the throughput capacity of streaming reprocessing historical data will be lower than that of batch processing. The above technical implementations have more or less shortcomings.

[0027] Based on this, the present invention provides a method and device for writing data to a Clickhouse database. The method provides a real-time stream-batch integrated data warehouse construction method based on Fink real-time computing combined with Clickhouse column storage, which can not only realize real-time computing, but also has batch processing capabilities, and has the characteristics of short aggregate analysis query response time, strong throughput and concurrency capabilities in massive data scenarios.

[0028] In an embodiment of the present invention, a method for writing data into a Clickhouse database is provided. Figure 1 This is a flowchart of a method for writing data to a Clickhouse database according to an embodiment of the present application. Figure 1 The method steps involved are described.

[0029] Step S101, obtaining the current data to be written.

[0030] In the embodiment of the present invention, the data to be written is data that has completed data preprocessing and can be written to the Clickhouse database data. The data preprocessing may include but is not limited to: data analysis, data format conversion and data calculation. The current data to be written is the data that will be written to the Clickhouse database data at the current moment.

[0031] Step S102, writing the data to be written into the newly added second node, and determining the target data in the existing first node in response to the control instruction, writing the target data into the second node, and deleting the target data in the first node.

[0032] In an embodiment of the present invention, a node is an independently running Clickhouse service instance constituting a Clickhouse distributed cluster. A Clickhouse distributed cluster may include multiple nodes, each of which has data storage, processing and query capabilities. The existing nodes in the current Clickhouse distributed cluster are used as the first nodes, and there may be one or more first nodes. The nodes that did not exist in the Clickhouse distributed cluster before and are currently newly added are used as the second nodes, and there may be one or more second nodes. The current data to be written is written to the newly added second node in real time.

[0033] It should be noted that see Figure 5The schematic diagram of the data warehouse cluster architecture is shown in the figure, where n-1, n-2, ..., nn represent n services, and the number of n depends on the business volume. The Reblance module is used to implement rebalancing processing. The dotted bracket represents a node. The figure shows the 1st to nth nodes, and 3 shards are shown in each node. The zk cluster includes 3 nodes, namely node-1, node-2 and node-3. The Clickhouse cluster uses Zookeeper as the registration center and is designed based on a sharded architecture. Data sharding is one of the key technologies for Clickhouse to achieve high availability and horizontal expansion. Data sharding is the process of distributing data on multiple nodes so that data can be processed in parallel during querying, thereby improving query performance. In Clickhouse, data sharding is implemented through a Distributed storage engine. Clickhouse supports multiple sharding strategies. The embodiments of the present invention can adopt a time-based sharding strategy and a device type hash value-based sharding strategy.

[0034] The control instruction is used to control the start of processing of data in the first node. The control instruction may include information for determining target data. When the control instruction is received, the target data is determined in the first node.

[0035] The target data is written into the second node, so that the newly added second node is used to store the target data, and the target data in the first node is deleted, so that the storage space occupied by the target data in the first node is cleared. Furthermore, part of the data pressure of the first node is transferred to the second node, and the data is more evenly distributed between the first node and the second node.

[0036] Step S103, writing subsequent data to be written into the first node and the second node.

[0037] In the embodiment of the present invention, the subsequent data to be written is the data to be written generated after the current moment. The data can be written into the Clickhouse database based on the BalancedClickhouseDataSource open source middleware.

[0038] When the data distribution between the first node and the second node is more even, the subsequent data to be written is written to the first node and the second node. The subsequent data to be written is evenly written to the first node and the second node, rather than mainly or more to the second node. This can avoid the hot spot problem of the second node caused by the automatic rebalancing mechanism of the Clickhouse database, thereby maintaining the stability and efficient operation of the cluster.

[0039] The Clickhouse database data writing method provided by the present invention creation embodiment includes: obtaining the current data to be written; writing the data to be written into a newly added second node, and determining the target data in the existing first node in response to a control instruction, writing the target data into the second node, and deleting the target data in the first node; writing the subsequent data to be written into the first node and the second node. The embodiment of the present invention writes the current data to be written into the newly added second node, and at the same time, determines the target data in the existing first node in response to a control instruction, and reduces the pressure of the first node by writing the target data in the first node into the second node and deleting the target data in the first node, so that when writing the subsequent data to be written, the data balance between the first node and the second node can be achieved based on the automatic data rebalancing mechanism of the Clickhouse database, and the data hot spot problem of the newly added second node can be alleviated.

[0040] In a possible implementation, the second node includes a data table that is the same as the table where the target data is located; writing the target data into the second node includes: writing the target data into the data table that is the same as the table where the target data is located in the second node.

[0041] In this possible implementation, when a second node is added, a table with the same table name as the original node, i.e., the first node, can be established in the second node, i.e., the second node includes a data table with the same table where the target data is located. The target data determined from the first node is written to the data table with the same table where the target data is located in the second node, so as to facilitate subsequent search of Clickhouse database data.

[0042] It should be noted that during the specific execution, the Clickhouse-Copier tool can be used to migrate the target data as a whole to the new table of the second node.

[0043] In one possible implementation, target data is determined in an existing first node in response to a control instruction, including: in response to the control instruction, obtaining storage occupancy information of the first node, and determining a target quantity based on the storage occupancy information; according to the target quantity, using part of the data in the first node as target data.

[0044] In this possible implementation, the control instruction may include data related to storage occupancy information such as the storage capacity and storage data volume of the first node, or include calculated storage occupancy information, obtain the storage occupancy information of the first node, and determine the target volume based on the storage occupancy information, which is the volume of target data. According to the target volume, part of the data in the first node is used as target data. For example, when the storage capacity of the first node is 5TB, the overall disk space storage ratio of the current node is relatively high, and the data of some tables therein can be determined as target data, such as about 1TB of data therein. .

[0045] Taking into account the influence of factors such as network jitter, database IO busyness, system busyness, etc., there is a certain probability of failure in writing data to the Clickhouse database. Therefore, in a possible implementation, the subsequent data to be written is written to the first node and the second node, including: if the write fails, rewrite the subsequent data to be written and record the number of retries; if the number of retries exceeds a given threshold, record the failed data to be written in the Mysql database; read the failed data to be written from the Mysql database based on a scheduled task to obtain a reading result; write the reading result to the Clickhouse database, and delete the failed data to be written recorded in the Mysql database.

[0046] In this possible implementation, see Figure 7 The write failure processing flow diagram shown in the figure shows the process of Flink writing data to Clickhouse. If the data writing fails, the subsequent data to be written is rewritten and the number of retries is recorded. In order to prevent too many retries from affecting the normal work of the Clickhouse database, the number of retries can be limited by setting a number threshold. Among them, the number threshold can be determined based on experience or system performance, and the embodiment of the present invention does not make specific limitations on this. Afterwards, the data to be written that failed to be written is written to the log table, and the log table is stored in the Mysql database. That is, if the number of retries exceeds the given number threshold, the data to be written that failed to be written is recorded in the Mysql data. The database instance of this log table is the Mysql database, which is not the same instance as Clickhouse, achieving higher availability.

[0047] See also Figure 7The write failure processing flow diagram shown in the figure shows that the compensation mechanism can be implemented by reading data from the log table and writing it into Clickhouse through a scheduled task. After successful writing, the data will be deleted from the log table. If it fails, it will continue to try until it succeeds. That is, based on the scheduled task, the data to be written that failed to be written is read from the Mysql database to obtain the reading result; the reading result is written into the Clickhouse database, and the data to be written that failed to be written recorded in the Mysql database is deleted.

[0048] In this possible implementation, data loss can be avoided through retry and compensation mechanisms.

[0049] In a possible implementation, writing subsequent data to be written to the first node and the second node includes: caching the data to be written; and when the cached amount of the data to be written reaches a given quantity threshold, writing the cached data to be written in batches to the first node and the second node.

[0050] In this possible implementation, after the device data is reported to the cloud platform, it is distributed to the data service through Kafka messages. The data service writes the data to the Clickhouse database based on the BalancedClickhouseDataSource open source middleware. BalancedClickhouseDataSource is a load balancing client based on Java that writes data to the Clickhouse database. The source code is modified in the embodiment of the present invention. Figure 6 As shown in the schematic diagram of the batch data writing process, an embodiment of the present invention uses a Redis queue to cache the data to be written, and writes it once when the data volume reaches a given quantity threshold, wherein the given quantity threshold may be 500,000, that is, the data to be written is cached; when the cached quantity of the data to be written reaches the given quantity threshold, the cached data to be written is written in batches to the first node and the second node.

[0051] In this step, data is written in batches, which increases the number of data entries written at a time, reduces the number of "toomany parts" errors, improves writing efficiency, reduces database IO overhead, and improves efficiency by 50%-200%.

[0052] In a possible implementation, writing subsequent data to be written to the first node and the second node includes: caching the data to be written; and writing the cached data to be written in batches to the first node and the second node at intervals of a specified time.

[0053] In this possible implementation, after the device data is reported to the cloud platform, it is distributed to the data service through Kafka messages. The data service writes the data to the Clickhouse database based on the BalancedClickhouseDataSource open source middleware. BalancedClickhouseDataSource is a load balancing client based on Java that writes data to the Clickhouse database. The source code is modified in the embodiment of the present invention. Figure 6 As shown in the schematic diagram of the batch data writing process, an embodiment of the present invention uses a Redis queue to cache the data to be written, and triggers a write action at an interval of a specified time, wherein the specified time may be 10 seconds, that is, the data to be written is cached; at an interval of a specified time, the cached data to be written is batch-written to the first node and the second node.

[0054] In this step, data is written in batches, which increases the number of data entries written at a time, reduces the number of "toomany parts" errors, improves writing efficiency, reduces database IO overhead, and improves efficiency by 50%-200%.

[0055] In a possible implementation, obtaining the current data to be written includes: obtaining original data of a device; calculating the original data based on a flink engine to obtain detailed data, summary data, and application data; wherein the detailed data is obtained by screening the original data, the summary data is obtained by calculating the detailed data, and the application data is obtained by analyzing and processing the summary data; and the original data, the detailed data, the summary data, and the application data are used as the data to be written.

[0056] In this possible implementation, see Figure 3 In the data hierarchical processing flow diagram shown, the original data of the device may include working data of different devices, for example, including but not limited to inverter data, battery data, etc. The original data of the device may be obtained through Kafka messages.

[0057] See also Figure 4The data warehouse data processing and flow diagram shown in the figure shows that ODS (Operation Data Store, original data layer) stores the original data of the equipment. For example, the real-time data and alarm data reported by the inverter, battery and other equipment are stored in the original data table of Clickhouse, forming the ODS layer. DWD (Data Warehouse Detail, data detail layer), the data is parsed and filtered, and stored in the data detail table of Clickhouse, forming the DWD layer. DWS (Data Warehouse Summary, data service layer) is about the processing and use of various topics, which is a wide table aggregation value. Based on business rules, such as based on user dimensions, device type dimensions, device model dimensions, time dimensions, etc., user data is extracted from DWD to form a wide table summary table for use by the business system. ADS (Application Data Service, application data layer) is the top layer of the data warehouse. It is a layer for secondary processing and business application development based on the DWS layer data. The ADS layer is usually modeled and developed for specific business scenarios and business needs, and provides various business analysis, reporting, visualization and decision support services. It can also include DIM (Dimension, dimension layer), which uses dimension as the modeling driver. Based on the business meaning of each dimension, it defines the calculation logic by adding dimension attributes, associating dimensions, etc., completes the attribute definition process and establishes a consistent data analysis dimension table, involving multiple dimensions such as user dimension, device type dimension, device model dimension, time dimension, etc.

[0058] The above data processing process can perform real-time calculation on the original data based on the flink engine at the data calculation layer, and finally obtain detailed data, summary data and application data, wherein the detailed data is obtained by screening the original data, the summary data is obtained by calculating the detailed data, and the application data is obtained by analyzing and processing the summary data.

[0059] The original data, the detailed data, the summary data and the application data are written into the Clickhouse database as the data to be written. Figure 3, the device data is reported to the cloud platform through MQTT (MessageQueuing Telemetry Transport, a lightweight IoT communication protocol based on publish / subscribe mode) long connection. After being parsed, the data will be sent to the data computing layer in the form of Kafka messages. After being calculated by Flink or Clickhouse materialized view technology, filtered and cleaned, it will be stored in ODS, DWD, DWS, ADS and other layers. In this possible implementation, the data is stored in the Clickhouse database data according to the structure of original data, detailed data, summary data and application data to improve the query speed of the database.

[0060] The present invention can use Flink's fault tolerance mechanism to restore the data flow application to a consistent state. In order for the fault tolerance mechanism to take effect, the data source (such as a queue (a basic data structure) or a broker (message middleware)) needs to be able to roll back to a specified location to replay the data flow. The fault tolerance mechanism continuously creates snapshots of the distributed data flow, namely checkpoints, as shown in Figure 8 Schematic diagram of Flink's fault-tolerance mechanism shown. For streaming programs with small states, snapshots are very lightweight and can be created at a high frequency with little impact on performance. The state of the streaming program is stored in a configurable location (such as on the master node). When the program fails (due to machine, network, or software failure), Flink stops the distributed data stream, and then the system restarts the operator (script or program in the automated operation and maintenance scenario) and resets it to the latest successful checkpoint, and the input stream is reset to the corresponding state snapshot location. In this possible implementation, the fault-tolerant mechanism of data writing exactly-once (once and only once) in the business is implemented through Flink's time window technology.

[0061] The present invention provides a method and device for writing data into a Clickhouse database. The method is based on Fink real-time computing combined with Clickhouse column storage to build a real-time stream-batch integrated data warehouse, utilizes Flink stream computing to perform real-time computing on device status indicators, and pushes real-time information to a business application layer to achieve real-time computing. Based on Clickhouse column storage, massive data storage and high-performance query are achieved. Data is not only stored in columns, but also processed in vectors, and the query speed is extremely fast. The data compression space is large, IO is reduced, and the storage cost is reduced. The single query high throughput can process billions of rows per server per second at most. The index is a non-B-tree structure and does not need to meet the leftmost principle. It only needs to include the filtering condition in the index column. Even if the used data is not in the index, the speed of Clickhouse full table scanning based on the parallel processing mechanism is also very fast.

[0062] An embodiment of the present invention also provides a Clickhouse database data writing device, which includes: an acquisition module, used to acquire the current data to be written; a management module, used to write the data to be written into a newly added second node, and determine the target data in the existing first node in response to a control instruction, write the target data into the second node, and delete the target data in the first node; a writing module, used to write subsequent data to be written into the first node and the second node.

[0063] The Clickhouse database data writing device provided by the invention embodiment includes: obtaining the current data to be written; writing the data to be written into a newly added second node, and determining the target data in the existing first node in response to a control instruction, writing the target data into the second node, and deleting the target data in the first node; writing the subsequent data to be written into the first node and the second node. The embodiment of the present invention writes the current data to be written into the newly added second node, and at the same time, determines the target data in the existing first node in response to a control instruction, and reduces the pressure of the first node by writing the target data in the first node into the second node and deleting the target data in the first node, so that when writing the subsequent data to be written, the data balance between the first node and the second node can be achieved based on the automatic data rebalancing mechanism of the Clickhouse database, and the data hot spot problem of the newly added second node can be alleviated.

[0064] In a possible implementation, the second node includes a data table that is the same as the table where the target data is located; writing the target data into the second node includes: writing the target data into the data table that is the same as the table where the target data is located in the second node.

[0065] In one possible implementation, target data is determined in an existing first node in response to a control instruction, including: in response to the control instruction, obtaining storage occupancy information of the first node, and determining a target quantity based on the storage occupancy information; according to the target quantity, using part of the data in the first node as target data.

[0066] In a possible implementation, the subsequent data to be written is written to the first node and the second node, including: if the writing fails, rewriting the subsequent data to be written and recording the number of retries; if the number of retries exceeds a given threshold, recording the data to be written that failed to be written in a Mysql database; reading the data to be written that failed to be written from the Mysql database based on a scheduled task to obtain a reading result; writing the reading result to the Clickhouse database, and deleting the data to be written that failed to be written recorded in the Mysql database.

[0067] In a possible implementation, writing subsequent data to be written to the first node and the second node includes: caching the data to be written; and when the cached amount of the data to be written reaches a given quantity threshold, writing the cached data to be written in batches to the first node and the second node.

[0068] In a possible implementation, writing subsequent data to be written to the first node and the second node includes: caching the data to be written; and writing the cached data to be written in batches to the first node and the second node at intervals of a specified time.

[0069] In a possible implementation, obtaining the current data to be written includes: obtaining original data of a device; calculating the original data based on a flink engine to obtain detailed data, summary data, and application data; wherein the detailed data is obtained by screening the original data, the summary data is obtained by calculating the detailed data, and the application data is obtained by analyzing and processing the summary data; and the original data, the detailed data, the summary data, and the application data are used as the data to be written.

[0070] An embodiment of the present invention also provides a non-transitory machine-readable medium storing a computer program, wherein the computer program, when executed by a processor of a computer, is used to enable the computer to execute the Clickhouse database data writing method of the embodiment of the present invention.

[0071] An embodiment of the present invention also provides a computer program product, including a computer program, wherein the computer program, when executed by a processor of a computer, is used to enable the computer to execute the Clickhouse database data writing method of the embodiment of the present invention.

[0072] The embodiment of the invention also provides an electronic device, comprising: at least one processor; and a memory connected to the at least one processor. The memory stores a computer program that can be executed by the at least one processor, and when the computer program is executed by the at least one processor, the electronic device executes the Clickhouse database data writing method of the embodiment of the invention.

[0073] refer to Fig. 9 , a block diagram of an electronic device that can be used as a server or client of an embodiment of the invention will now be described, which is an example of hardware devices that can be applied to various aspects of the invention. The electronic device is intended to represent various forms of digital electronic computer devices, such as laptop computers, desktop computers, workbenches, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device can also represent various forms of mobile devices, such as personal digital processing, cellular phones, smart phones, wearable devices, and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely examples and are not intended to limit the implementation of the invention described and / or required herein.

[0074] like Fig. 9 As shown, the electronic device includes a computing unit 901, which can perform various appropriate actions and processes according to a computer program stored in a read-only memory (ROM) 902 or a computer program loaded from a storage unit 908 into a random access memory (RAM) 903. In the RAM 903, various programs and data required for the operation of the electronic device can also be stored. The computing unit 901, the ROM 902, and the RAM 903 are connected to each other via a bus 904. An input / output (I / O) interface 905 is also connected to the bus 904.

[0075] Multiple components in the electronic device are connected to the I / O interface 905, including: an input unit 906, an output unit 907, a storage unit 908, and a communication unit 909. The input unit 906 can be any type of device that can input information to the electronic device, and the input unit 906 can receive input digital or character information, and generate key signal input related to user settings and / or function control of the electronic device. The output unit 907 can be any type of device that can present information, and can include but is not limited to a display, a speaker, a video / audio output terminal, a vibrator, and / or a printer. The storage unit 908 can include but is not limited to a disk, an optical disk. The communication unit 909 allows the electronic device to exchange information / data with other devices through a computer network such as the Internet and / or various telecommunication networks, and can include but is not limited to a modem, a network card, an infrared communication device, and / or a wireless communication transceiver, such as a Bluetooth device, a WiFi device, a WiMax device, a cellular communication device, and / or the like.

[0076] The computing unit 901 may be a variety of general and / or special processing components with processing and computing capabilities. Some examples of the computing unit 901 include, but are not limited to, a CPU, a graphics processing unit (GPU), various dedicated artificial intelligence (AI) computing units, various computing units running machine learning model algorithms, digital signal processors (DSPs), and any appropriate processors, controllers, microcontrollers, etc. The computing unit 901 performs the various methods and processes described above. For example, in some embodiments, the method embodiments created by the present invention may be implemented as a computer program, which is tangibly contained in a machine-readable medium, such as a storage unit 908. In some embodiments, part or all of the computer program may be loaded and / or installed on an electronic device via ROM 902 and / or a communication unit 909. In some embodiments, the computing unit 901 may be configured to perform the above method in any other appropriate manner (e.g., by means of firmware).

[0077] The computer program for implementing the method of the present invention can be written in any combination of one or more programming languages. These computer programs can be provided to a processor or controller of a general-purpose computer, a special-purpose computer, or other programmable data processing device, so that the computer program, when executed by the processor or controller, enables the functions / operations specified in the flow chart and / or block diagram to be implemented. The computer program can be executed entirely on the machine, partially on the machine, partially on the machine as a stand-alone software package and partially on a remote machine, or entirely on a remote machine or server.

[0078] In the context of the present invention, the machine-readable medium can be a tangible medium that can contain or store a program for use by an instruction execution system, device or equipment or used in combination with an instruction execution system, device or equipment. The machine-readable medium can be a machine-readable signal medium or a machine-readable storage medium. The machine-readable signal medium can include, but is not limited to, electronic, magnetic, optical, electromagnetic or infrared systems, devices or equipment, or any suitable combination of the above. A more specific example of a machine-readable storage medium can include an electrical connection based on one or more lines, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the above.

[0079] It should be noted that the term "including" and its variations used in the embodiments of the present invention are open inclusions, that is, "including but not limited to". The term "based on" means "based at least in part on". The term "one embodiment" means "at least one embodiment"; the term "another embodiment" means "at least one other embodiment"; the term "some embodiments" means "at least some embodiments". The modifications of "one" and "multiple" mentioned in the embodiments of the present invention are illustrative and not restrictive. Those skilled in the art should understand that unless otherwise clearly indicated in the context, it should be understood as "one or more".

[0080] The user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, stored data, displayed data, etc.) involved in the embodiments created by the present invention are all information and data authorized by the user or fully authorized by all parties, and the collection, use and processing of relevant data must comply with the relevant laws, regulations and standards of relevant countries and regions, and provide corresponding operation entrances for users to choose to authorize or refuse.

[0081] The various steps described in the method implementation methods provided by the embodiments of the present invention can be performed in different orders and / or in parallel. In addition, the method implementation methods may include additional steps and / or omit the steps shown. The scope of protection of the present invention is not limited in this respect.

[0082] The term "embodiment" in this specification refers to specific features, structures or characteristics described in conjunction with the embodiment that can be included in at least one embodiment of the invention. The appearance of this phrase in various places in the specification does not necessarily mean the same embodiment, nor does it mean that it is mutually exclusive with other embodiments and is independent or optional. The various embodiments in this specification are described in a related manner, and the same and similar parts between the various embodiments refer to each other. In particular, for the device, equipment, and system embodiments, since they are basically similar to the method embodiments, the description is relatively simple, and the relevant parts refer to the partial description of the method embodiment.

[0083] The above-described embodiments only express several implementation methods of the present invention, and the descriptions thereof are relatively specific and detailed, but they cannot be understood as limiting the scope of protection. It should be pointed out that, for a person of ordinary skill in the art, several modifications and improvements can be made without departing from the concept of the present invention, and these all belong to the scope of protection of the present invention. Therefore, the scope of protection of the present invention shall be subject to the attached claims.

Claims

1. A method for writing data into a Clickhouse database, characterized in that: include: Get the current data to be written; Writing the data to be written into the newly added second node, determining target data in the existing first node in response to the control instruction, writing the target data into the second node, and deleting the target data in the first node; The subsequent data to be written is written into the first node and the second node.

2. The method according to claim 1, characterized in that: The second node includes a data table that is the same as the table where the target data is located; Writing the target data into the second node includes: The target data is written into a data table in the second node which is the same as the table where the target data is located.

3. The method according to claim 1, characterized in that Determining target data in an existing first node in response to a control instruction includes: In response to the control instruction, acquiring storage occupancy information of the first node, and determining a target amount based on the storage occupancy information; According to the target amount, part of the data in the first node is used as target data.

4. The method according to claim 1, characterized in that Writing subsequent to-be-written data into the first node and the second node includes: If writing fails, re-write the subsequent data to be written and record the number of retries; If the number of retries exceeds a given threshold, the data to be written that failed to be written is recorded in the MySQL database; Based on the scheduled task, the to-be-written data that failed to be written is read from the Mysql database to obtain a reading result; The read result is written into the Clickhouse database, and the data to be written that failed to be written and recorded in the Mysql database is deleted.

5. The method according to claim 1, characterized in that Writing subsequent to-be-written data into the first node and the second node includes: caching the data to be written; When the cached amount of the data to be written reaches a given amount threshold, the cached data to be written are written in batches to the first node and the second node.

6. The method according to claim 1, characterized in that Writing subsequent to-be-written data into the first node and the second node includes: caching the data to be written; The cached data to be written is written in batches to the first node and the second node at intervals of a specified duration.

7. The method according to claim 1, characterized in that Get the current data to be written, including: Get the original data of the device; The original data is calculated based on the flink engine to obtain detailed data, summary data and application data; wherein the detailed data is obtained by screening the original data, the summary data is obtained by calculating the detailed data, and the application data is obtained by analyzing and processing the summary data; The original data, the detailed data, the summary data and the application data are taken as data to be written.

8. A Clickhouse database data writing device, characterized in that: include: An acquisition module is used to obtain the current data to be written; A management module, used for writing the data to be written into the newly added second node, and determining target data in the existing first node in response to a control instruction, writing the target data into the second node, and deleting the target data in the first node; A writing module is used to write subsequent data to be written into the first node and the second node.

9. An electronic device, comprising: A processor, and a memory storing a program, wherein the program comprises instructions, which, when executed by the processor, cause the processor to perform the method according to any one of claims 1 to 7.

10. A non-transitory machine-readable medium storing computer instructions, characterized in that: The computer instructions are used to cause the computer to execute the method according to any one of claims 1-7.

Images