ClickHouse-based row-column mixed storage method, device and equipment for time series data of internet of things and medium

Abstract

The application discloses an Internet of Things time series data row-column hybrid storage method and device based on ClickHouse, equipment and medium, and the method comprises the steps of automatically creating a corresponding ClickHouse attribute data table according to a product identifier; splitting a device reporting message received and containing a plurality of attribute key-value pairs into a plurality of records with one attribute per row, and generating a row structure data stream; outputting the row structure data stream buffered in a window buffer as a data batch; and writing the data batch into the attribute data table through a batch insertion interface of ClickHouse. The application combines one attribute per row with ClickHouse based on a row-column hybrid storage architecture, that is, through the combination of logical row storage and physical column storage, the Schema rigidification problem of a traditional relational database when facing frequent changes of Internet of Things device attributes is solved, and the query efficiency is greatly improved.

Description

Technical Field

[0001] This invention relates to the field of data storage technology, and in particular to a method, apparatus, device, and medium for hybrid row and column storage of IoT time-series data based on ClickHouse. Background Technology

[0002] With the rapid development of IoT technology, the number of smart devices has exploded. The amount of data generated by IoT devices globally in recent years is enormous. These devices continuously report sensor data (temperature, humidity, location, etc.), forming massive amounts of time-series data, and the storage of this time-series data is a key issue of concern in the industry.

[0003] Current mainstream IoT time-series data storage solutions have the following drawbacks: 1. Rigid Schema: The number of device attributes is not fixed. Traditional wide table solutions require ALTERTABLE for each new attribute, which is extremely costly.

[0004] 2. Poor query performance: Relational databases have insufficient performance for time-series range queries (such as temperature curves for the past 7 days).

[0005] 3. Storage waste: A large number of attribute columns in a wide table are NULL, which wastes storage space.

[0006] 4. EAV mode is too slow: The Entity-Attribute-Value mode is flexible but slow to query and requires a lot of JOINs. Summary of the Invention

[0007] In view of the above problems, the present invention is proposed to provide a ClickHouse-based method, apparatus, device and medium for hybrid row and column storage of IoT time-series data to overcome or at least partially solve the above problems.

[0008] Other features and advantages of the invention will become apparent from the following detailed description, or may be learned in part by practice of the invention.

[0009] According to a first aspect of the present invention, a method for hybrid row-column storage of IoT time-series data based on ClickHouse is provided, comprising: S1: When the device attribute data reported by the device for the first time is received, the corresponding ClickHouse attribute data table is automatically created according to the product identifier. The attribute data table adopts a row-column hybrid storage architecture and includes at least fields for storing device identifier, attribute name, attribute value, and reporting timestamp. S2: The received device reporting message containing multiple attribute key-value pairs is split into multiple records with one attribute per row. Each record corresponds to one attribute key-value pair. The attribute value is stored in the corresponding special type column according to the type of the attribute value, generating a row-structured data stream. The attribute key-value pair is a pair of data consisting of an attribute name and an attribute value. S3: Receive the row structure data stream using the Flux pipeline of the reactive stream programming framework, group the row structure data stream according to the product identifier of the attribute data table, and set a window buffer for each group containing a dual trigger condition of a record quantity threshold and a time window threshold. When either trigger condition is met, output the row structure data stream buffered in the window buffer as a data batch. S4: Write the data batch into the attribute data table through ClickHouse's batch insert interface.

[0010] In some embodiments of the present invention, storing the attribute value into a corresponding special type column according to the type of the attribute value includes: Confirm the type of the attribute value, which includes numeric, time, geographic location, and other types. If the attribute value is a numeric type, it will be stored in the numeric type column numberValue; If the attribute value is of type time, it will be stored in the time type column timeValue; If the attribute value is of type geolocation, it is stored in the geolocation column geoValue, and the geolocation column geoValue uses ClickHouse's floating-point array type to store longitude and latitude coordinates; If the attribute value is of another type, it will be stored in the string type column 'value'. In each record, the attribute value is stored in only one special type column, while the other special type columns are stored as null values.

[0011] In some embodiments of the present invention, the Flux pipeline of the reactive stream programming framework adopts the Reactor framework and receives the row-structured data stream in a non-blocking manner through FluxSink as the data entry point.

[0012] In some embodiments of the present invention, the attribute data table adopts the MergeTree storage engine, and its sort key includes at least the device identifier, attribute name and reporting timestamp fields, and the attribute name field is stored and compressed using the LowCardinality data type.

[0013] In some embodiments of the present invention, setting a window buffer for each group with dual trigger conditions including a record quantity threshold and a time window threshold, and outputting the row-structured data stream buffered in the window buffer as a data batch when either trigger condition is met, includes: The window buffer is confirmed to meet the triggering conditions by using a window operator with timeout. When the number of records accumulated in the window buffer reaches the record number threshold, the window buffer is confirmed to meet the first triggering condition. When the time interval between the last batch write trigger in the window buffer reaches the time window threshold, the window buffer is confirmed to meet the second triggering condition. When the window buffer satisfies either the first trigger condition or the second trigger condition, the row-structured data stream buffered within the window is output as a data batch.

[0014] In some embodiments of the present invention, after creating the attribute data table, the method further includes: Create one or more materialized views on the attribute data table. The materialized views perform pre-aggregation calculations on the numerical attribute values ​​at an hourly or daily granularity. The aggregation results of the pre-aggregation calculations include at least the average value, the maximum value, and the minimum value. When a new batch of data is written to the attribute data table, the materialized view is automatically updated via ClickHouse, and the aggregation result is written to the pre-aggregation storage table. When an aggregation query request is received, the aggregation result is read from the pre-aggregation storage table or the attribute data table based on a preset read priority, wherein the read priority of the pre-aggregation storage table is higher than that of the attribute data table.

[0015] In some embodiments of the present invention, after creating the attribute data table, the method further includes: Create a corresponding log data table, which adopts a columnar storage architecture to store the device's raw message and event logs; And set an automatic expiration policy for the attribute data table and the log data table. The automatic expiration policy limits the retention period of the original data of the attribute data table, the pre-aggregated storage table corresponding to the attribute data table, and the log data table, respectively. Once the attribute data table or log data table reaches its corresponding retention period, the expired attribute data table or log data table will be automatically deleted.

[0016] According to a second aspect of the present invention, a ClickHouse-based IoT time-series data row-column hybrid storage device is provided, the device comprising: The dynamic table creation module is used to automatically create a corresponding ClickHouse attribute data table based on the product identifier when the device attribute data is received for the first time. The attribute data table adopts a row-column hybrid storage architecture and includes at least fields for storing device identifier, attribute name, attribute value, and reporting timestamp. The row-column conversion module is used to split the received device report message containing multiple attribute key-value pairs into multiple records with one attribute per row. Each record corresponds to one attribute key-value pair, and the attribute value is stored in the corresponding special type column according to the type of the attribute value to generate a row-structured data stream. The attribute key-value pair is a pair of data consisting of an attribute name and an attribute value. The data batch buffer module is used to receive the row structure data stream using the Flux pipeline of the reactive stream programming framework, group the row structure data stream according to the product identifier of the attribute data table, and set a window buffer for each group containing a dual trigger condition of a record quantity threshold and a time window threshold. When either trigger condition is met, the row structure data stream buffered in the window buffer is output as a data batch. The data batch writing module is used to write the data batches into the attribute data table through ClickHouse's batch insert interface.

[0017] According to a third aspect of the present invention, a computer device is provided, including a processor and a memory, the memory storing computer program instructions executable by the processor, wherein when the processor executes the computer program instructions, it implements the instructions as described in any of the above methods.

[0018] According to a fourth aspect of the present invention, a computer-readable storage medium is provided, wherein computer program instructions are stored therein, the computer program instructions being loaded and executed by a processor to perform the operations performed by the method described in any of the preceding claims.

[0019] The technical solutions provided in the embodiments of the present invention have at least the following technical effects or advantages: This invention provides a method, apparatus, device, and medium for hybrid row-column storage of IoT time-series data based on ClickHouse. The method is based on a hybrid row-column storage architecture, combining one attribute per row with ClickHouse. That is, by combining logical row-based storage with physical column-based storage, it eliminates the need for expensive ALTER TABLE operations. When writing data, the new attribute name is filled into the property column, and the value is filled into the corresponding value column. This solves the schema rigidity problem of traditional relational databases when facing frequent changes in IoT device attributes, and greatly improves query efficiency by configuring a sorting key.

[0020] The above description is merely an overview of the technical solution of the present invention. In order to better understand the technical means of the present invention and to implement it in accordance with the contents of the specification, and in order to make the above and other objects, features and advantages of the present invention more apparent and understandable, specific embodiments of the present invention are described below. Attached Figure Description

[0021] To more clearly illustrate the technical solutions in the embodiments of the present invention, the accompanying drawings used in the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0022] Figure 1 A flowchart illustrating a ClickHouse-based method for hybrid row and column storage of IoT time-series data is provided in an embodiment of the present invention. Figure 2 This is a schematic diagram of the principle structure of a ClickHouse-based IoT time-series data row-column hybrid storage device provided in an embodiment of the present invention; Figure 3 This is an internal structural diagram of a computer device in one embodiment. Detailed Implementation

[0023] Exemplary embodiments of this application will now be described in more detail with reference to the accompanying drawings.

[0024] The accompanying drawings illustrate various structural schematics according to embodiments of this application. These drawings are not to scale, and some details have been enlarged for clarity, and some details may have been omitted. The shapes of the various regions and layers shown in the drawings, as well as their relative sizes and positional relationships, are merely exemplary and may deviate from reality due to manufacturing tolerances or technical limitations. Furthermore, those skilled in the art can design regions / layers with different shapes, sizes, and relative positions as needed.

[0025] It should be noted that the terms "first," "second," etc., used in this application may be used to describe various elements, but these elements are not limited by these terms. These terms are only used to distinguish the first element from the second element. The terms "comprising" and "having," and any variations thereof, used in this application, are intended to cover non-exclusive inclusion. The term "multiple" used in this application refers to two or more. In the context of this application, similar or identical parts may be represented by the same or similar reference numerals.

[0026] To better understand the above technical solutions, the following will describe the above technical solutions in detail with reference to specific implementation methods. It should be understood that the embodiments of this application and the specific features in the embodiments are detailed descriptions of the technical solutions of the present invention, rather than limitations on the technical solutions of the present invention. In the absence of conflict, the embodiments of the present invention and the technical features in the embodiments can be combined with each other.

[0027] Figure 1 This is a flowchart illustrating a ClickHouse-based method for hybrid row and column storage of IoT time-series data, as provided in an embodiment of the present invention. Figure 1 As shown, this ClickHouse-based method for hybrid row and column storage of IoT time-series data includes the following steps: S1: When the device attribute data reported by the device for the first time is received, the corresponding ClickHouse attribute data table is automatically created according to the product identifier. The attribute data table adopts a row-column hybrid storage architecture and includes at least fields for storing device identifier, attribute name, attribute value, and reporting timestamp. IoT (Internet of Things) devices report JSON messages containing deviceId, timestamp, and multiple attribute key-value pairs (i.e., the device attribute data or the device report message in step S2) to the cloud platform via the MQTT protocol. Upon receiving the device attribute data reported by the device for the first time, this embodiment of the invention confirms whether a ClickHouse attribute data table exists. If the attribute data table does not exist, a corresponding ClickHouse attribute data table is automatically created based on the product identifier. ClickHouse is an open-source columnar online analytical processing (OLAP) database management system with a distributed architecture design, supporting real-time analysis and efficient querying of massive amounts of data. It is based on columnar storage and MPP (Massively Parallel Processing) architecture, combined with a vectorized execution engine, and has the capabilities of data compression, high-concurrency querying, and multi-data source access. The attribute data table adopts a row-column hybrid storage architecture. The attribute data table at least includes data for storing device identifier (deviceId String), attribute name (property String), and attribute value (numeric type valueString, other types numberValue Float64, timeValue Int64, geoValue). The data includes fields for an array (Float32) and a reporting timestamp (Int64); the device identifier is used to uniquely identify an IoT device, locate data from a specific device during querying, and also serves as part of the sorting key in step S4 for physical data aggregation; the attribute name identifies the type of the attribute, such as temperature or humidity; the attribute value stores the specific numerical value of the attribute, stored separately according to type, and the attribute value and attribute name are combined into a key-value pair; the reporting timestamp records the actual time when the device data was generated, and is used for time-series queries and partitioning; The product identifier serves as the table name of the attribute data table. The table name is bound to the product ID (properties_{productId}) to achieve physical data isolation. Since each record carries the product identifier, it can be used to group the row-structured data stream in subsequent steps.

[0028] S2: The received device reporting message containing multiple attribute key-value pairs is split into multiple records with one attribute per row. Each record corresponds to one attribute key-value pair. The attribute value is stored in the corresponding special type column according to the type of the attribute value, generating a row-structured data stream. The attribute key-value pair is a pair of data consisting of an attribute name and an attribute value. After receiving the device's reported message, the multiple attribute key-value pairs contained in the device's reported message are split into multiple records (Maps) with one attribute per row using a row-column transformation adapter. During the split, different types of attribute values, such as numerical values, time, and geographical location, are stored in corresponding dedicated type columns. Through the splitting method of one attribute per row (EAV (Entity-Attribute-Value) flexibility), each attribute value is made independent on a separate row, which facilitates dynamic expansion and filtering by attribute. For example, when a new attribute needs to be added to the device, there is no need to perform an expensive ALTER TABLE operation. When writing data, the new attribute name is filled into the property column, and the value is filled into the corresponding value column. This solves the schema rigidity problem of traditional relational databases when facing frequent changes in IoT device attributes.

[0029] This embodiment of the invention stores the attribute value into the corresponding dedicated type column according to the type of the attribute value, including: confirming the type of the attribute value, which includes numeric type, time type, geographic location type, and other types; if the attribute value is numeric type, it is stored in the numeric type column numberValue; if the attribute value is time type, it is stored in the time type column timeValue; if the attribute value is geographic location type, it is stored in the geographic location column geoValue, and the geographic location column geoValue uses ClickHouse's floating-point array type to store longitude and latitude coordinates; if the attribute value is other types, it is stored in the string type column value; wherein, in each record, the attribute value is stored in only one dedicated type column, and the other dedicated type columns are all stored as empty values.

[0030] In this embodiment of the invention, four dedicated type columns are configured according to the type of the attribute value: numberValue (numerical type), timeValue (time type), geoValue (geo location type), and value (string type). These columns are used to store the attribute values ​​of numerical type, time type, geolocation type, and other types, respectively. By storing the attribute values ​​in separate columns according to their data types, type conversion is avoided, and compression rate and query efficiency are improved.

[0031] Furthermore, this embodiment of the invention employs sparse filling, storing the attribute value in only one dedicated type column within each record, while storing null values ​​in the remaining dedicated type columns. That is, each row is filled with only one valid value column, with the rest being null values. ClickHouse has an extremely high null value compression rate, avoiding the type conversion overhead caused by mixing different types of data in the same text column. Numerical and time columns are stored in fixed-length or highly efficient encoded formats, facilitating vectorized calculations. Null values ​​are efficiently compressed in ClickHouse columnar storage, occupying almost no space. Geographic coordinates are stored using a floating-point array type, supporting spatial query functionality.

[0032] S3: Receive the row structure data stream using the Flux pipeline of the reactive stream programming framework, group the row structure data stream according to the product identifier of the attribute data table, and set a window buffer for each group containing a dual trigger condition of a record quantity threshold and a time window threshold. When either trigger condition is met, output the row structure data stream buffered in the window buffer as a data batch. This invention utilizes the Flux pipeline of a reactive streaming programming framework to receive the row-structured data stream. The row-structured data stream is grouped according to the product identifier of the attribute data table. Specifically, in the row-structured data (each record Map) generated in step S2, the data consumption service knows which product the message belongs to (because the message contains a productId). Before the row-structured data stream enters the Flux pipeline, each record is associated with its target table name (i.e., the product identifier, for example, encapsulated as a Buffer object, which has a getTable() method to return the table name). Using the groupBy operator of reactive streaming, with the table name as the grouping key, all records with the same table name are assigned to the same sub-data stream (GroupedFlux), thereby grouping the row-structured data stream.

[0033] The Flux pipeline of the reactive stream programming framework adopts the Reactor framework and receives the row-structured data stream in a non-blocking manner through FluxSink as the data entry point. FluxSink, as a non-blocking entry point, receives all records to be written and temporarily stores them as Buffer objects. The batch write optimizer uses the groupBy(Buffer::getTable) operation to group the row-structured data stream according to the table name. The row-structured data streams of different products enter their respective independent subsequent processing pipelines.

[0034] In this embodiment of the invention, each sub-data stream is configured with an independent window buffer (windowTimeout) and write logic. Data streams from different products are completely isolated and can be processed in parallel without interfering with each other. When multiple products report a large amount of data at the same time, the system can execute multiple write tasks concurrently, making full use of CPU and network resources.

[0035] The step of setting a window buffer for each group with dual trigger conditions including a record quantity threshold and a time window threshold, and outputting the row-structured data stream buffered in the window buffer as a data batch when either trigger condition is met, includes: confirming whether the window buffer meets the trigger conditions using a window operator with a timeout; confirming that the window buffer meets the first trigger condition when the number of records accumulated in the window buffer reaches the record quantity threshold; confirming that the window buffer meets the second trigger condition when the time interval between the last batch write trigger in the window buffer reaches the time window threshold; and outputting the row-structured data stream buffered in the window as a data batch when the window buffer meets either the first or the second trigger condition.

[0036] The first triggering condition is, for example, when the cumulative number of records reaches the record quantity threshold (5000 records), and the second triggering condition is, for example, when the time interval reaches the time window threshold (100 milliseconds). This ensures both batch writing efficiency and keeps the maximum write latency within 100 milliseconds. In other embodiments of the present invention, the record quantity threshold and the time window threshold can be determined according to actual application requirements (e.g., according to the reporting frequency of IoT devices), and the embodiments of the present invention do not limit this.

[0037] This invention, after data grouping and window buffering, further employs an exponential backoff retry mechanism to handle write failures. The exponential backoff retry mechanism works as follows: the first retry waits one second, the second waits two seconds, the third waits four seconds, and a maximum of three retries. This mechanism is automatically triggered when writing to ClickHouse fails, avoiding excessive pressure on the server due to frequent retries. Simultaneously, by gradually increasing the waiting time, it improves the probability of successful recovery after a brief network outage. If the retry still fails after three attempts, an alarm is triggered, and the data is temporarily stored in a local disk queue. Once the network or ClickHouse cluster recovers, a background thread automatically reads the data from the temporary queue and re-executes the write operation. The local disk temporary queue is implemented based on an embedded key-value storage engine, with a default capacity limit of ten gigabytes. When the capacity is exceeded, an alarm is triggered, and the new data is transferred to a dead-letter queue in the message queue, ensuring data reliability during network partitions or ClickHouse cluster failures.

[0038] S4: Write the data batch into the attribute data table through ClickHouse's batch insert interface.

[0039] The data batch can be written to the original attribute data table or the newly created attribute data table through ClickHouse's batch insert interface. When writing the data batch, the insert statement is executed through the Hypertext Transfer Protocol interface, and one data batch is written at a time.

[0040] In this embodiment of the invention, the attribute data table uses the MergeTree storage engine, and its sort key includes at least the device identifier, attribute name, and reporting timestamp fields. For example, the sort key of the MergeTree storage engine used in the attribute data table is set as a composite sort key composed of the tenant identifier, device identifier, attribute name, and reporting timestamp. This sort key ensures that data of the same attribute from the same device is physically stored contiguously, and the data is physically sorted in the order of tenant identifier → device identifier → attribute name → reporting time. When querying the time-series data of a certain attribute of a certain device, the target data is completely contiguous on the disk, and the sparse index can skip more than 99% of irrelevant data blocks, achieving millisecond-level accuracy. The system responds by day and partitions the data by day. The partition key is a function that converts the reported timestamp into a year-month-day format. When performing a time range query (such as the past seven days), ClickHouse only scans the corresponding seven partitions and automatically ignores all other partitions, significantly reducing input and output operations. Furthermore, the attribute name field is stored and compressed using the LowCardinality data type. Since the attribute name field takes the value of a finite set of attribute names, using the LowCardinality data type allows ClickHouse to internally create a dictionary for the string and actually store integer key-value pairs, reducing storage space by approximately 90%. At the same time, aggregation queries grouped by attribute name are significantly accelerated due to the use of integer operations.

[0041] After creating the attribute data table, this embodiment of the invention further includes: creating one or more materialized views on the attribute data table, wherein the materialized views perform pre-aggregation calculations on numerical attribute values ​​at an hourly or daily granularity, and the aggregation results of the pre-aggregation calculations include at least the average, maximum, and minimum values; when a new batch of data is written to the attribute data table, the incremental update of the materialized views is automatically triggered through ClickHouse, and the aggregation results are written to the pre-aggregation storage table; when an aggregation query request is received, the aggregation results are read from the pre-aggregation storage table or the attribute data table based on a preset read priority, wherein the read priority of the pre-aggregation storage table is higher than the read priority of the attribute data table.

[0042] In this embodiment of the invention, a materialized view hourly statistics table is created. The engine is a summation and merging tree storage engine. The partition key is to convert the hour to year-month-day format, and the sort key is (device identifier, hour). The query statement is as follows: select the device identifier, attribute name, convert the timestamp to hour as the hour, the average value (numerical value column) as the average value, the maximum value (numerical value column) as the maximum value, and the minimum value (numerical value column) as the minimum value. The data is then grouped from the industrial attribute table by device identifier, attribute name, and hour. ClickHouse's materialized views automatically input new data into the calculation logic defined in the view definition each time a batch of data is written to the source table (attribute data table), instantly generating aggregation results and writing them to the target pre-aggregation storage table. The entire process is completely transparent to the application and ensures the real-time performance of the aggregated data. Furthermore, this embodiment of the invention uses hourly or daily granularity, as well as typical aggregation functions such as average, maximum, and minimum values, to apply to the most commonly used query patterns in IoT time-series data analysis. For example, the "average temperature over the past 24 hours" can be directly queried from the hourly aggregation table, with a data volume only one three-thousandth of a hundredth of the original table. Simultaneously, based on a preset read priority, the embodiment of the invention reads aggregation results from the pre-aggregation storage table or the attribute data table. It identifies whether the requested query can be satisfied by the pre-aggregation table; if so, it directly queries the pre-aggregation storage table instead of scanning the original attribute data table, thereby avoiding a full scan and greatly improving aggregation query performance.

[0043] After creating the attribute data table, this embodiment of the invention further includes: creating a corresponding log data table, wherein the log data table adopts a columnar storage architecture for storing the original messages and event logs of the device; and setting an automatic time-to-live (TTL) expiration policy for both the attribute data table and the log data table, wherein the TTL expiration policy respectively limits the retention period of the original data of the attribute data table, the pre-aggregated storage table corresponding to the attribute data table, and the log data table. For example, the original data of the attribute data table is retained for thirty days, the materialized view pre-aggregated table corresponding to the attribute data table is retained for one year, and the log data table is retained for seven days; after the attribute data table or log data table reaches the corresponding retention period, the expired attribute data table or log data table is automatically deleted; by adopting different expiration policies for different data and automatically cleaning it up after its expiration, disk storage space is greatly saved.

[0044] The ClickHouse-based IoT time-series data row-column hybrid storage method described in this invention has the following advantages compared to existing technologies: 1. Based on a hybrid row-column storage architecture, it combines one attribute per row with ClickHouse. That is, by combining logical row-based storage with physical column-based storage, there is no need to perform expensive ALTER TABLE operations. When writing data, you only need to fill the new attribute name into the property column and the value into the corresponding value column. This solves the schema rigidity problem of traditional relational databases when facing frequent changes in the attributes of IoT devices. 2. Sparse filling is used, where the attribute value is stored in only one special type column in each record, and the remaining special type columns are stored as null values. That is, each row is filled with only one valid value column, and the rest are null values. ClickHouse has an extremely high null value compression rate. By storing the attribute value in the corresponding special type column according to the type of the attribute value, a row-structured data stream is generated, and a low cardinality data type is used for storage and compression. Combined with an automatic expiration strategy for data lifetime, disk storage space is greatly saved. 3. By configuring a sorting key, the data is physically sorted in the order of tenant identifier → device identifier → attribute name → reporting time. When querying the time-series data of a certain attribute of a certain device, the target data is completely contiguous on the disk. The sparse index can skip more than 99% of irrelevant data blocks, achieving millisecond-level response. On the other hand, by using ClickHouse's materialized view and preset read priority to read the aggregated results, the query efficiency is greatly improved.

[0045] Based on the above embodiments, as a supplement to the above... Figure 1 The present invention provides an embodiment of a ClickHouse-based IoT time-series data row-column hybrid storage device, which implements the method shown. Figure 1 Corresponding to the method embodiments shown, this device can be specifically applied to various electronic devices, see reference. Figure 2 As shown, the ClickHouse-based IoT time-series data row-column hybrid storage device includes: The dynamic table creation module 100 is used to automatically create a corresponding ClickHouse attribute data table based on the product identifier when the device attribute data is received for the first time. The attribute data table adopts a row-column hybrid storage architecture and includes at least fields for storing device identifier, attribute name, attribute value, and reporting timestamp. The row-column conversion module 200 is used to split the received device reporting message containing multiple attribute key-value pairs into multiple records with one attribute per row. Each record corresponds to one attribute key-value pair, and the attribute value is stored in the corresponding special type column according to the type of the attribute value to generate a row-structured data stream. The attribute key-value pair is a pair of data consisting of an attribute name and an attribute value. The data batch buffer module 300 is used to receive the row structure data stream using the Flux pipeline of the reactive stream programming framework, group the row structure data stream according to the product identifier of the attribute data table, and set a window buffer for each group containing a dual trigger condition of a record quantity threshold and a time window threshold. When either trigger condition is met, the row structure data stream buffered in the window buffer is output as a data batch. The data batch writing module 400 is used to write the data batches into the attribute data table through ClickHouse's batch insert interface.

[0046] The modules in the ClickHouse-based IoT time-series data row-column hybrid storage device described above can be implemented entirely or partially through software, hardware, or a combination thereof. These modules can be embedded in or independent of the processor in a computer device, or stored in the memory of a computer device as software, so that the processor can call and execute the operations corresponding to each module.

[0047] The ClickHouse-based IoT time-series data row-column hybrid storage device described in this embodiment can execute the ClickHouse-based IoT time-series data row-column hybrid storage method provided in the above embodiments. The ClickHouse-based IoT time-series data row-column hybrid storage device has the corresponding functional steps and beneficial effects of the ClickHouse-based IoT time-series data row-column hybrid storage method described in the above embodiments. For details, please refer to the embodiments of the ClickHouse-based IoT time-series data row-column hybrid storage method described above. The embodiments of this invention will not be repeated here.

[0048] In one exemplary embodiment, a computer device is provided, which may be a terminal, and its internal structure diagram may be as follows: Figure 3As shown, the computer device includes a processor, memory, input / output interfaces, a communication interface, a display unit, and an input device. The processor, memory, and input / output interfaces are connected via a system bus, and the communication interface, display unit, and input device are also connected to the system bus via the input / output interfaces. The processor provides computing and control capabilities. The memory includes non-volatile storage media and internal memory. The non-volatile storage media stores the operating system and computer programs. The internal memory provides an environment for the operation of the operating system and computer programs stored in the non-volatile storage media. The input / output interfaces are used for exchanging information between the processor and external devices. The communication interface, such as a network interface card, is used for wired or wireless communication with external terminals. Wireless communication can be achieved through Wi-Fi, mobile cellular networks, Near Field Communication (NFC), or other technologies. The display unit is used to create a visually visible image and can be a display screen, a projection device, or a virtual reality imaging device. The display screen can be an LCD screen or an e-ink screen. The input device of the computer device can be a touch layer covering the display screen, or buttons, trackballs, or touchpads set on the casing of the computer device, or external keyboards, touchpads, or mice, etc.

[0049] Those skilled in the art will understand that Figure 3 The structure shown is merely a block diagram of a portion of the structure related to the present application and does not constitute a limitation on the computer device to which the present application is applied. Specific computer devices may include more or fewer components than those shown in the figure, or combine certain components, or have different component arrangements.

[0050] In one exemplary embodiment, a chip is provided, including a memory and a processor. The memory stores a computer program, and the processor executes the computer program to implement the steps of the method in any of the above embodiments.

[0051] In one exemplary embodiment, a network interface card is provided, including a chip as described in any of the above embodiments and multiple interfaces, wherein the chip communicates externally through the interfaces.

[0052] In one embodiment, a computer device is also provided, including a processor, a chip in any of the above embodiments, or a network interface card in any of the above embodiments, wherein the chip or the network interface card is used to schedule packets to the processor or the chip or the network interface card itself for processing, and the processor is used to process the packets scheduled by the chip or the network interface card.

[0053] In one embodiment, a computer-readable storage medium is provided having a computer program stored thereon that, when executed by a processor, implements the steps in the above method embodiments.

[0054] In one embodiment, a computer program product is provided, including a computer program that, when executed by a processor, implements the steps in the above method embodiments.

[0055] Those skilled in the art will understand that all or part of the processes in the above embodiments can be implemented by a computer program instructing related hardware. The computer program can be stored in a non-volatile computer-readable storage medium, and when executed, it can include the processes of the embodiments of the above methods. Any references to memory, databases, or other media used in the embodiments provided in this application can include at least one of non-volatile memory and volatile memory. Non-volatile memory may include read-only memory (Read-Only Memory). Only memory (ROM), magnetic tape, floppy disk, flash memory, optical storage, high-density embedded non-volatile memory, resistive random access memory (ReRAM), magnetic random access memory (MRAM), ferroelectric random access memory (FRAM), phase change memory (PCM), graphene memory, etc. Volatile memory may include random access memory (RAM) or external cache memory, etc. By way of illustration and not limitation, RAM can be in various forms, such as static random access memory (SRAM) or dynamic random access memory (DRAM), etc. The databases involved in the embodiments provided in this application may include at least one of relational databases and non-relational databases. Non-relational databases may include distributed databases based on blockchain, etc., and are not limited thereto. The processors involved in the various embodiments provided in this application may be general-purpose processors, central processing units, graphics processors, digital signal processors, programmable logic devices, quantum computing-based data processing logic devices, artificial intelligence (AI) processors, etc., and are not limited thereto.

[0056] Numerous specific details are set forth in the specification provided herein. However, it will be understood that embodiments of the invention may be practiced without these specific details. In some instances, well-known methods, structures, and techniques have not been shown in detail so as not to obscure the understanding of this specification.

[0057] Similarly, it should be understood that, for the purpose of simplification and aiding understanding of one or more aspects of the invention, various features of the invention are sometimes grouped together in a single embodiment, figure, or description thereof in the description of exemplary embodiments of the invention above. Other embodiments of the invention will readily occur to those skilled in the art upon consideration of the specification and practice of the disclosure herein. This application is intended to cover any variations, uses, or adaptations of the invention that follow the general principles of the invention and include common knowledge or customary techniques in the art not disclosed herein. The specification and embodiments are to be considered exemplary only, and it should be noted that the above embodiments are illustrative of the invention and not restrictive, and that alternative embodiments can be devised by those skilled in the art without departing from its scope.

Claims

1. A method for hybrid row-column storage of IoT time-series data based on ClickHouse, characterized in that, include: S1: When the device attribute data reported by the device for the first time is received, the corresponding ClickHouse attribute data table is automatically created according to the product identifier. The attribute data table adopts a row-column hybrid storage architecture and includes at least fields for storing device identifier, attribute name, attribute value, and reporting timestamp. S2: The received device reporting message containing multiple attribute key-value pairs is split into multiple records with one attribute per row. Each record corresponds to one attribute key-value pair. The attribute value is stored in the corresponding special type column according to the type of the attribute value, generating a row-structured data stream. The attribute key-value pair is a pair of data consisting of an attribute name and an attribute value. S3: Receive the row structure data stream using the Flux pipeline of the reactive stream programming framework, group the row structure data stream according to the product identifier of the attribute data table, and set a window buffer for each group containing a dual trigger condition of a record quantity threshold and a time window threshold. When either trigger condition is met, output the row structure data stream buffered in the window buffer as a data batch. The Flux pipeline of the reactive stream programming framework adopts the Reactor framework and receives the row-structured data stream in a non-blocking manner through FluxSink as the data entry point. The step of setting a window buffer for each group with dual trigger conditions including a record quantity threshold and a time window threshold, and outputting the row-structured data stream buffered in the window buffer as a data batch when either trigger condition is met, includes: The window buffer is confirmed to meet the triggering conditions by using a window operator with timeout. When the number of records accumulated in the window buffer reaches the record number threshold, the window buffer is confirmed to meet the first triggering condition. When the time interval between the last batch write trigger in the window buffer reaches the time window threshold, the window buffer is confirmed to meet the second triggering condition. When the window buffer satisfies either the first trigger condition or the second trigger condition, the row-structured data stream buffered in the window buffer is output as a data batch. S4: Write the data batch into the attribute data table through ClickHouse's batch insert interface.

2. The ClickHouse-based method for hybrid row and column storage of IoT time-series data according to claim 1, characterized in that, The step of storing the attribute value into the corresponding special type column according to the type of the attribute value includes: Confirm the type of the attribute value, which includes numeric, time, geographic location, and other types. If the attribute value is a numeric type, it will be stored in the numeric type column numberValue; If the attribute value is of type time, it will be stored in the time type column timeValue; If the attribute value is of type geolocation, it is stored in the geolocation column geoValue, and the geolocation column geoValue uses ClickHouse's floating-point array type to store longitude and latitude coordinates; If the attribute value is of another type, it will be stored in the string type column 'value'. In each record, the attribute value is stored in only one special type column, while the other special type columns are stored as null values.

3. The method for hybrid row and column storage of IoT time-series data based on ClickHouse according to claim 1, characterized in that: The attribute data table uses the MergeTree storage engine, and its sort key includes at least the device identifier, attribute name, and reporting timestamp fields. The attribute name field is stored and compressed using the LowCardinality data type.

4. The ClickHouse-based method for hybrid row and column storage of IoT time-series data according to claim 2, characterized in that, After creating the attribute data table, the method further includes: Create one or more materialized views on the attribute data table. The materialized views perform pre-aggregation calculations on the numerical attribute values ​​at an hourly or daily granularity. The aggregation results of the pre-aggregation calculations include at least the average value, the maximum value, and the minimum value. When a new batch of data is written to the attribute data table, the materialized view is automatically updated via ClickHouse, and the aggregation result is written to the pre-aggregation storage table. When an aggregation query request is received, the aggregation result is read from the pre-aggregation storage table or the attribute data table based on a preset read priority, wherein the read priority of the pre-aggregation storage table is higher than that of the attribute data table.

5. The ClickHouse-based method for hybrid row and column storage of IoT time-series data according to claim 4, characterized in that, After creating the attribute data table, the method further includes: Create a corresponding log data table, which adopts a columnar storage architecture to store the device's raw message and event logs; And set an automatic expiration policy for the attribute data table and the log data table. The automatic expiration policy limits the retention period of the original data of the attribute data table, the pre-aggregated storage table corresponding to the attribute data table, and the log data table, respectively. Once the attribute data table or log data table reaches its corresponding retention period, the expired attribute data table or log data table will be automatically deleted.

6. A ClickHouse-based IoT time-series data row-column hybrid storage device, applied to the method described in any one of claims 1-5, characterized in that, The device includes: The dynamic table creation module is used to automatically create a corresponding ClickHouse attribute data table based on the product identifier when the device attribute data is received for the first time. The attribute data table adopts a row-column hybrid storage architecture and includes at least fields for storing device identifier, attribute name, attribute value, and reporting timestamp. The row-column conversion module is used to split the received device report message containing multiple attribute key-value pairs into multiple records with one attribute per row. Each record corresponds to one attribute key-value pair, and the attribute value is stored in the corresponding special type column according to the type of the attribute value to generate a row-structured data stream. The attribute key-value pair is a pair of data consisting of an attribute name and an attribute value. The data batch buffer module is used to receive the row structure data stream using the Flux pipeline of the reactive stream programming framework, group the row structure data stream according to the product identifier of the attribute data table, and set a window buffer for each group containing a dual trigger condition of a record quantity threshold and a time window threshold. When either trigger condition is met, the row structure data stream buffered in the window buffer is output as a data batch. The data batch writing module is used to write the data batches into the attribute data table through ClickHouse's batch insert interface.

7. A computer device comprising a processor and a memory, characterized in that, The memory stores computer program instructions that can be executed by the processor, and when the processor executes the computer program instructions, it implements the method as described in any one of claims 1-5.

8. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer program instructions, which are loaded and executed by a processor to implement the method as described in any one of claims 1-5.

Images