IoT-based data storage management and query analysis methods and storage media

Abstract

This invention discloses a data storage management and query analysis method and storage medium based on the Internet of Things (IoT), belonging to the field of IoT data management and analysis technology. The technical problem this invention aims to solve is that real-time databases lack data analysis capabilities in the IoT management and analysis field, while relational databases cannot meet the performance requirements of real-time database management. The technical solution adopted is as follows: This method is based on a relational data model, introducing a third dimension into the two-dimensional table of the relational data model. This third dimension is used for the description and management of real-time data, and extends the SQL language to support the definition and query of real-time and relational data. Specifically, it includes: defining non-real-time and real-time data types; real-time data import; real-time data query; subscribing to and publishing real-time data; historical data query; historical data aggregation; real-time-relational data query fusion; and real-time / historical data storage management.

Description

Technical Field

[0001] This invention relates to the field of Internet of Things (IoT) data management and analysis technology, specifically to a data storage management and query analysis method and storage medium based on IoT. Background Technology

[0002] The Internet of Things (IoT) represents the third wave of the global information industry. Core IoT technologies are continuously developing, standards are being rapidly established, and the industrial system is in the process of being built and improved. In the future, the IoT market will experience sustained and rapid growth. The IoT environment contains a large amount of sensor data / real-time data, which is characterized by high frequency, large volume, and low data value density, posing new requirements and challenges to databases.

[0003] Traditional relational databases possess strong descriptive capabilities, supporting the storage and management of various data types, including frequently changing sensor / real-time data in the IoT environment. However, relational databases are inefficient at storing and managing this type of real-time data, making it difficult to meet the needs of IoT applications. First, the insertion performance of relational databases is insufficient; inserting data using traditional INSERT SQL statements is inefficient. Second, the massive data storage in the IoT consumes significant disk space, and relational databases do not provide effective mechanisms to reduce disk usage. Third, standard SQL does not support publish-subscribe functionality, failing to meet the real-time publish-subscribe requirements of the IoT. Finally, in the relational model, discrete records cannot accurately describe the continuously changing real-time data in the IoT environment; storing multiple data collection points in a single table leads to excessive data redundancy, while storing only one data collection point results in an excessive number of tables; furthermore, queries involving multiple tables suffer from poor performance.

[0004] Industrial real-time databases can efficiently support the storage and management of massive amounts of real-time / historical data. However, their data query and analysis capabilities are weak. Therefore, in order to achieve complex data analysis, manufacturing execution systems in the industrial field usually build another relational database and import real-time data from the real-time database into the relational database for application analysis. Building two databases in this way will increase the complexity of application system development and also lead to problems such as poor system performance and high resource consumption.

[0005] Recently, the concept of time-series databases has emerged in the database field, and open-source time-series database systems such as InfluxDB, TimescaleDB, and TDEngine have sprung up. Although these time-series databases offer open-source versions, and industrial real-time databases are expensive, they have not yet replaced industrial real-time databases in applications with high real-time requirements. While time-series databases implement the storage, management, and query analysis of time-series data, possessing certain real-time and historical data analysis capabilities, their real-time data storage and management efficiency is far inferior to traditional industrial real-time databases because their time-series data is still stored on a record-by-record basis. This makes them unable to meet the high-efficiency processing requirements of industrial applications for real-time data, including high-speed data import, high data compression ratios, real-time subscription and publication, and historical data querying. Furthermore, the capabilities and performance of time-series databases in complex data query analysis are far inferior to traditional relational databases.

[0006] The data model of time-series databases differs significantly from the actual data in the IoT environment, making it difficult to accurately describe the characteristics of IoT data. Device sensor data in the IoT environment has states, including normal / device down / hardware failure / acquisition failure, etc. The data generated by device sensors is continuously changing; data exists at any given time, but its values ​​and states may differ. Relational / time-series databases typically use discrete records to describe real-time data, making it difficult to read data at a specific historical point in time because a record may not have been collected and written at the time of the user's query. The data changes and acquisition of each device sensor are relatively independent; storing multiple acquisition points in a single record results in a large amount of duplicate or empty data, as not all data at the same timestamp changes and is acquired. Therefore, uncollected data needs to be filled in. Furthermore, cross-device data query analysis requires traversing multiple tables, leading to poor query performance.

[0007] Due to the rapid development of the Internet of Things (IoT), a large amount of frequently updated real-time data has emerged not only in the field of industrial control, but also in various other application areas, including smart grids, smart cities, and intelligent transportation.

[0008] In summary, real-time databases and relational databases have two main shortcomings in the field of IoT management and analysis: real-time databases lack data analysis capabilities, and relational databases cannot meet the performance requirements of real-time database management. These are technical problems that urgently need to be solved. Summary of the Invention

[0009] The technical objective of this invention is to provide a data storage management and query analysis method and storage medium based on the Internet of Things (IoT) to address the problems of real-time databases lacking data analysis capabilities and relational databases failing to meet the performance requirements of real-time database management in the field of IoT management and analysis.

[0010] The technical objective of this invention is achieved as follows: a data storage management and query analysis method based on the Internet of Things (IoT). This method is based on a relational data model, inheriting the relational data model's functions for describing and querying relational data. A third dimension is introduced into the two-dimensional table of the relational data model. This third dimension is used for describing and managing real-time data. Furthermore, the SQL language is extended to support the definition and querying of both real-time and relational data, enabling the modeling, management, and analysis of IoT data within a single database. Specifically:

[0011] Define non-real-time data and real-time data types;

[0012] Real-time data import;

[0013] Real-time data query;

[0014] Subscribe to publish real-time data;

[0015] Historical data query;

[0016] Historical data aggregation;

[0017] Real-time relational data query fusion;

[0018] Real-time / historical data storage and management.

[0019] As a preferred approach, real-time data and relational data are defined as follows:

[0020] Non-real-time changing data in the equipment is described using a relational data model;

[0021] For sensor / collection point data that changes frequently in real time within the device, it is described and managed in the third dimension as real-time data type; the real-time data type actually stores two types of data: real-time data and historical data; real-time data refers to the latest data, i.e., the latest snapshot; historical data is data at historical points in time;

[0022] The data attributes of real-time data types include numerical values, timestamps, and status.

[0023] The value is the actual value collected by the sensor at the corresponding collection point, and the value type can be any relational data type or a user-defined structure data type.

[0024] The timestamp represents the time when the data was collected. When data from a collection point is entered into the database, if the timestamp corresponding to the data from that collection point is greater than the timestamp of the real-time snapshot data of the collection point, the current real-time snapshot data will be placed in the historical data queue / array, and the real-time snapshot data will be updated to the newly collected data; otherwise, the data from the collection point will be treated as historical data and directly placed in the historical queue / array.

[0025] The status refers to the state of the sensor / collection point when data is collected. The status includes initial, normal, fault, and shutdown. The specific supported statuses can be customized according to application needs. If the collection point is initially created and no data has been written, the status of the collection point is initial. If the data collection point is collecting data normally, the status of the collection point is normal. If the device corresponding to the collection point malfunctions, shuts down, or the corresponding sensor is not working properly, the status of the corresponding collection point will be set to fault, stop, invalid, or other statuses.

[0026] The basic configurable attributes of real-time data types include whether to store history, whether to compress, allowable error, interpolation / restoration algorithm, and lifecycle;

[0027] Whether to store history is used to specify whether to persist historical data; if set to yes, the historical data of the collection point will be persisted, that is, written to the disk for permanent storage; otherwise, the historical data will be discarded directly, and only the real-time data will be stored; the compression algorithm, allowable error, interpolation / restoration method and lifecycle attributes only take effect when the storage history is set;

[0028] Whether to compress is specified to determine whether historical data is compressed during persistence. If set to yes, historical data will be compressed online upon insertion based on its data type, and decompression will be performed during historical data queries. Otherwise, historical data will not be compressed and will be persisted directly without decompression during queries. Compression algorithms are generally divided into lossy compression and lossless compression. For lossless compression, the value before compression is exactly the same as the value after decompression. Lossy compression allows for errors between the value before compression and the value after decompression.

[0029] The allowable error is the range of error between the decompressed and interpolated data and the actual value. If the allowable error is Δ, then the decompressed and reconstructed value will differ from the actual value within the range of [-Δ, +Δ], that is:

[0030]

[0031] Where T is the current time; t is any historical time point before T; HisData t This refers to historical data recovered from the data collection points at time t using an interpolation algorithm; ActualData tThis refers to the historical data actually occurring at time point t for the sensor / collection point.

[0032] Interpolation / restoration algorithms are used to specify the interpolation / restoration algorithm used when querying historical data. Interpolation / restoration algorithms include step interpolation and linear interpolation. Step interpolation, like a step, means that the current value is maintained until the next data acquisition is reached. This interpolation algorithm is used for digital signals. Linear interpolation refers to an interpolation method where the interpolation function is a first-order polynomial. The interpolation error of the interpolation function at the acquisition time point is zero. Linear interpolation algorithms are usually used for analog signals.

[0033] The lifecycle specifies the minimum time for historical data from a collection point to be persisted. Historical data that has not exceeded its lifecycle must be retained, while data that has exceeded its lifecycle must be deleted.

[0034] More preferably, after introducing a third dimension into the two-dimensional table based on the relational data model, each table includes multiple discrete columns and real-time columns. A table containing real-time columns is called a real-time table. A real-time table is a type of device, and a record in it is a specific device. The real-time column in it is any specific collection point.

[0035] In addition to the current record's real-time data, each real-time column maintains a historical data queue / data. The specific operations are as follows:

[0036] Append the real-time column data of the current record to the historical data queue / array;

[0037] Overwrite the real-time column with new data in the current record;

[0038] If the "Store History" attribute of the real-time column is set to no, then the update operation of the real-time column is the same as the update operation of the relational column, and the data of the real-time column can be directly overwritten on the current record.

[0039] The syntax for creating a real-time table is as follows:

[0040] CREATE TABLE table_name(

[0041] column1[realtime]datatype,

[0042] column2[realtime]datatype,

[0043] column3[realtime]datatype, ...

[0045] columnN[realtime]datatype,

[0046] PRIMARY KEY(one or more columns) );

[0048] Adding the keyword "realtime" (which can be abbreviated to "rt") before the data type of a column indicates that the field / column is a real-time column; otherwise, it is a relational column.

[0049] A real-time table is used to define a class of devices in an IoT application, containing the same relational columns and the same real-time columns; the syntax for inserting a record into the real-time table is as follows:

[0050] INSERT INTO table_name(column1,column2,column3,...)

[0051] VALUES

[0052] value1|{initial:value1,historian:his1,compressed:comp1,deviation:dev1,interpolation:inter1,lifetime:life1},

[0053] value2|{initial:value2,historian:his2,compressed:comp2,deviation:dev1,interpolation:inter2,lifetime:life2},

[0054] value3|{initial:value3,historian:his3,compressed:comp3,deviation:dev1,interpolation:inter3,lifetime:life3}, ... );

[0057] If any column is a relational column, the value corresponding to that relational column is consistent with the traditional value format; if any column is a real-time column, the value corresponding to that real-time column is a tuple, which includes the initial value, whether to store history, whether to compress, allowable deviation, interpolation / restoration algorithm, and lifetime.

[0058] If a table is a relational table, inserting a record is the same as inserting a record in a traditional relational table; if a table is a real-time table, inserting a record is the same as adding a device.

[0059] The syntax for querying the real-time column attributes of a table is as follows:

[0060] SELECT

[0061] column1[.id|.datatype|.historian|.compressed|.deviation|.interpolation|.lifetime],

[0062] column2[.id|.datatype|.historian|.compressed|.deviation|.interpolation|.lifetime],

[0063] column3[.id|.datatype|.historian|.compressed|.deviation|.interpolation|.lifetime], ...

[0065] FROM table_name

[0066] WHERE condition

[0067] GROUP BY column1[.id|.datatype|.historian|.compressed|.deviation|.interpolation|.lifetime];

[0068] ORDER BY column2[.id|.datatype|.historian|.compressed|.deviation|.interpolation|.lifetime];

[0069] If the column in the select list is a real-time column, the query returns a real-time snapshot of the corresponding real-time column in the record. Adding a "." after the column followed by the corresponding attribute name will retrieve the attribute value of the corresponding real-time column in the record. If the column in the select list is a relational column, the query returns the data of the corresponding relational column in the record. Real-time column attribute queries support the following attributes: whether to store history, whether to compress, allowable error, interpolation / restoration algorithm, and lifecycle. They also support the real-time column's ID and its data type (datatype). The real-time column ID is a unique ID automatically generated for the corresponding real-time column when a record is written. This ID allows direct access to the real-time column; its purpose will be explained in detail later when discussing fast database insertion via the interface.

[0070] Using the attribute names of the real-time column in the WHERE, GROUP BY, and ORDER BY clauses is equivalent to treating the attributes of the real-time column as ordinary relational columns.

[0071] Modifying Real-Time Column Attributes: When adding a record to the real-time table, you can set various attributes of the real-time columns and modify them within the record. The syntax for modifying real-time column attributes is as follows:

[0072] UPDATE table_name

[0073] SET column1[.historian|.compress|.deviation|.interpolation|.lifetime]=v1

[0074] SET column2[.historian|.compress|.deviation|.interpolation|.lifetime]=v2

[0075] ...

[0076] WHERE condition;

[0077] If column is a real-time column, the SQL statement will update the real-time snapshot of the record; if column is followed by a "." and the corresponding attribute name, the attribute value of the real-time column in the record will be updated; if column is a relational column, the SQL statement will update the relational column data in the record.

[0078] More preferably, the real-time data import method is as follows:

[0079] ①SQL method:

[0080] The SQL syntax for updating real-time data is as follows:

[0081] UPDATE table_name

[0082] SET column1=value1|{value1[,timestamp1][,status1]}

[0083] SET column2=value2|{value2[,timestamp2][,status2]}

[0084] ...

[0085] WHERE condition;

[0086] The SQL syntax extends the traditional UPDATE SQL syntax, adding support for real-time data updates; if the updated column is a real-time data type, the previous real-time value will be stored as historical data, and the new value will replace the real-time value; if the updated column is a relational data type, the data in the record will be directly overwritten.

[0087] ② Extend ODBC / JDBC to provide an API method for directly writing data via function calls:

[0088] The function is defined as: bool UpdateRealTimeData(long record_column_id, data_typevalue);

[0089] Where record_column_id is the real-time column ID corresponding to the updated record; returning true indicates that the data was successfully inserted into the database, otherwise the data insertion failed.

[0090] The syntax for querying records from a table during real-time data querying is as follows:

[0091] SELECT

[0092] column1[.value|.timestamp|.status],

[0093] column2[.value|.timestamp|.status],

[0094] column3[.value|.timestamp|.status], ...

[0096] FROM table_name

[0097] WHERE condition

[0098] GROUP BY column1[.value|.timestamp|.status];

[0099] ORDER BY column2[.value|.timestamp|.status];

[0100] This select query will return multiple records / devices that meet the conditions. In addition to returning the values ​​of the relation columns, each record will also return the real-time value / latest snapshot of the real-time column, which includes the attributes of value, timestamp, and status. Alternatively, the user can specify any attribute of the real-time data in the real-time column to return, represented by column.value, column.timestamp, and column.status respectively.

[0101] Ideally, the subscription and publishing of real-time data should be as follows:

[0102] The SQL syntax for subscribing to real-time data is as follows:

[0103] SUBSCRIBE column1, column2,...

[0104] FROM table_name

[0105] WHERE condition;

[0106] Where column must be a real-time column, SQL will subscribe to all real-time columns in the select list corresponding to the records that meet the conditions, and return the IDs of all real-time columns that were successfully subscribed to;

[0107] Additionally, you can subscribe to real-time data using the real-time column ID. The SQL syntax is as follows:

[0108] SUBSCRIBE id1,id2,id3,…;

[0109] The SQL statement `SUBSCRIBE id1,id2,id3,…` will return the real-time column IDs of all successfully subscribed items.

[0110] The SQL syntax for canceling a real-time data subscription is as follows:

[0111] UNSUBSCRIBE column1,column2,...

[0112] FROM table_name

[0113] WHERE condition;

[0114] Where column must be a real-time column, the SQL will unsubscribe from all real-time columns in the select list corresponding to the records that meet the conditions, and return the IDs of all real-time columns that were successfully unsubscribed;

[0115] Additionally, to cancel a real-time data subscription using the real-time column ID, the SQL syntax is as follows:

[0116] UNSUBSCRIBE id1,id2,id3,…;

[0117] The SQL statement UNSUBSCRIBE id1,id2,id3,… will return the real-time column id for all successfully unsubscribed items;

[0118] Alternatively, you can cancel the subscription to all real-time data for the current session using the SQL statement UNSUBSCRIBE ALL:

[0119] The SQL statement UNSUBSCRIBE ALL will return the ID column of all successfully unsubscribed live data. After the SQL is executed successfully, all live data currently subscribed to in the current session will be canceled.

[0120] The SQL syntax for displaying all subscribed real-time data is as follows:

[0121] SHOW SUBSCRIPTIONS;

[0122] The SQL statement for SHOW SUBSCRIPTIONS will display the ID column for all subscribed live data.

[0123] Get the latest real-time data: After the client successfully subscribes to real-time data, when the database system detects an update to the real-time data, it will actively push the latest data to the client, so that the client can get the latest data in a timely manner;

[0124] To avoid using SQL polling to obtain the latest real-time data, the ODBC / JDBC interface is extended to provide an interface function for obtaining published real-time data. The specific definition of the real-time data interface function is as follows:

[0125] bool GetRealTimeData(rt_data&value,int timeout);

[0126] Among them, rt_data is a real-time data object class that provides interfaces for obtaining the id, data type, value, status, and timestamp of real-time columns; the second parameter is the maximum waiting timeout; a return value of true for the function indicates successful acquisition of the latest published data; a return value of false for the function indicates failure to acquire the published data, which may be due to timeout or disconnection from the server.

[0127] Preferably, historical data queries include historical data queries at a specified time point, raw historical data queries within a specified time range, and interpolated historical data queries within a specified time range;

[0128] Among them, the SQL syntax for historical data queries at a specified time point is as follows:

[0129] SELECT column1,column2,...

[0130] FROM table_name

[0131] AT timestamp

[0132] WHERE condition;

[0133] The SQL statement will return multiple records / devices at any time point timestamp that meet the conditions; among them, column is a real-time column or a relational column; for real-time columns, if there is no data corresponding to the historical time point of timestamp in the historical data queue, interpolation and restoration will be performed according to the original historical data before and after the interpolation time point according to the specified interpolation restoration algorithm, and the interpolated and restored data will be returned, and the error between the restored value and the actual value at the corresponding time point is less than the allowable error range;

[0134] The interpolation restoration algorithms include step interpolation and linear interpolation;

[0135] Specifically, step interpolation is as follows: Let any real-time column of any record / device have n originally collected historical data HisData i , where i ∈ [1, n], and the corresponding historical timestamps are T i , where T i < T i+1 ; If the real-time column uses the step interpolation algorithm, for a given historical time point t, let T i ≤ t < T i+1 , its historical data Value t = HisData i ; When T3 ≤ t < T4, the historical data Value t = HisData3;

[0136] Specifically, linear interpolation means that if the real-time column adopts the linear interpolation algorithm, for a given historical time point t, assuming T i ≤t<T i+1 , its historical data is as follows:

[0137]

[0138] When T3≤t<T4, the historical data at time point t is:

[0139]

[0140] The SQL syntax for querying the original historical data within a specified time range is as follows:

[0141] SELECT column1,column2,...

[0142] FROM table_name

[0143] AT\[timestamp1,timestamp2\]

[0144] WHERE condition;

[0145] This SQL statement will return the original historical data of the devices / records that meet the conditions within the time range [timestamp1,timestamp2]; among them, column is the real-time column or the relationship column; for the relationship column, it can be directly filled with the corresponding relationship column value of the record; for the real-time column, its data is the data corresponding to the historical timestamp;

[0146] Assume that any real-time column of any record / device has n originally collected historical data HisData i , where i∈[1,n], T j ≤timestamp1<T j+1 and T l ≤timestamp2<T<000​​​​​​​​​ResultSet={HisData2,HisData3,HisData4,HisData5};

[0150] The SQL syntax for interpolating historical data within a specified time range is as follows:

[0151] SELECT column1, column2, ...

[0152] FROM table_name

[0153] AT\[timestamp1,timestamp2\]

[0154] INTERVAL interval

[0155] WHERE condition;

[0156] This SQL statement will return the interpolated historical data of devices / records that meet the conditions within the specified time range. Each device / record starts from timestamp1 and generates one record every interval. The real-time column data of each record is the data that is interpolated and restored according to the specified interpolation restoration algorithm based on the original historical data before and after the interpolation time point. The relation column data of each record is filled with the relation column data corresponding to its device / record.

[0157] More preferably, historical data aggregation functions include count, sum, max, min, avg, mean, first, last, median, and stddev. When using historical data aggregation functions, the "AT[timestamp1,timestamp2]" clause must be used, meaning that historical data aggregation functions are calculated for any historical time range.

[0158] count(column): Returns the number of original historical data entries for the current column within the specified time range;

[0159] sum(column): Returns the sum of all raw historical data within the specified time range;

[0160] `max(column)`: Returns the original historical data with the maximum value within a specified time range, including the maximum value, timestamp, and status. If multiple original historical data points at different time points have the same maximum value, multiple records will be returned. If the `max` function is applied to multiple real-time columns, multiple records will be returned, listing all possible combinations.

[0161] min(column): This function is similar to the max function, but it returns the original historical data with the minimum value within a specified time range;

[0162] avg(column): Returns the average value of the raw historical data within a specified time range. The average value is calculated as follows:

[0163]

[0164] Among them, HisData i It is the original historical data within a time frame;

[0165] mean(column): Returns the integral average over a specified time range. The integral average is calculated as follows:

[0166]

[0167] Among them, T i For HisData i The corresponding timestamp;

[0168] first(column): Returns the first original historical data within the specified time range;

[0169] last(column): Returns the last original historical data within the specified time range;

[0170] stddev(column): The function scope specifies the standard deviation of all historical data within a given time range;

[0171] If an INTERVAL clause follows the AT clause in a query, the query results will be grouped by time intervals. Historical data aggregation functions will be calculated within each time interval group, rather than the entire specified time range. In addition, INTERVAL can be understood as a time interval grouping that operates on the real-time column. If both INTERVAL and GROUP BY appear in a SQL statement, the operation priority of INTERVAL is higher than that of GROUP BY.

[0172] Even better, the real-time-relational data fusion query is as follows:

[0173] All real-time table query results will be automatically converted into two-dimensional tables. The real-time columns in the records contain real-time data, including values, timestamps, and status. The results of real-time table queries are the same as those of two-dimensional tables, allowing for any operations supported by relational tables, including project, filter, limit, group by, and order by. At the same time, the results of real-time table queries can be used for complex join queries with any other relational table or results of other real-time table queries, including join and union.

[0174] An electronic device includes: a memory and at least one processor;

[0175] The memory contains computer programs;

[0176] The at least one processor executes the computer program stored in the memory, causing the at least one processor to perform the IoT-based data storage management and query analysis method described above.

[0177] A computer-readable storage medium storing a computer program that can be executed by a processor to implement the Internet of Things-based data storage management and query analysis method described above.

[0178] The data storage management and query analysis method and storage medium based on the Internet of Things of the present invention have the following advantages:

[0179] (i) This invention introduces real-time data types on the basis of relational data models, supporting real-time / historical data storage, data compression, interpolation query and lifecycle management, etc. In the Internet of Things environment, it can not only manage relatively static relational data, but also efficiently manage massive and frequently changing real-time data.

[0180] (ii) The real-time data type proposed in this invention is used to describe continuously changing data. Unlike relational data, these real-time data are independent of each other. Compared with discrete records in a two-dimensional model, it can better describe and manage sensor data in IoT scenarios, reduce redundant data, reduce memory usage, and reduce disk space usage.

[0181] (III) This invention extends the SQL syntax, allowing users to manage real-time data through SQL statements. On the one hand, it supports real-time data update SQL and supports rapid updates of real-time data by extending the ODBC / JDBC interface to meet the needs of rapid storage of massive real-time data in the Internet of Things. On the other hand, it supports real-time data subscription SQL and supports the acquisition of published data by extending the ODBC / JDBC interface, giving it powerful subscription and publishing capabilities.

[0182] (iv) This invention can accurately describe various types of data in the Internet of Things environment, including relational data, real-time data, frequently changing data and low-frequency changing data, etc. The data model is not so abstract, making it easier for developers to understand. This enables developers to use simple and easy-to-understand SQL to implement data storage management, thereby simplifying the development of Internet of Things applications.

[0183] (v) This invention extends the standard SQL language to support real-time data query and analysis, and utilizes the powerful analysis and processing capabilities of the relational model to allow users to perform unified query and analysis of real-time data and relational data in one database. It can well meet the complex query and analysis needs of large-scale Internet of Things applications. Internet of Things applications do not need to deploy two databases, thereby reducing the complexity and development cost of Internet of Things application development.

[0184] (vi) Based on the relational data model, this invention extends support for real-time data description and management, providing a unified data model that integrates real-time data and relational data, and extending the SQL language to support the definition and query of real-time and relational data; this invention supports simple relational table definition, which is completely consistent with the relational table definition in the relational data model, supports real-time table definition, and a real-time table, in addition to related series, also has real-time columns and supports data querying of real-time tables and relational tables, as well as complex association queries that integrate real-time and relational data;

[0185] (vii) This invention can describe a large amount of relational data in the Internet of Things environment, including equipment models, specifications, manufacturers, etc., and supports the powerful data query and analysis capabilities of traditional relational databases. It can also accurately describe massive and frequently changing sensor / real-time data and has the high-performance processing and storage management capabilities of industrial real-time databases for real-time data. It can not only meet the requirements of real-time monitoring applications of the Internet of Things, but also well meet the management and analysis needs of large-scale Internet of Things systems.

[0186] (viii) Based on the relational data model, this invention inherits its ability to describe and query relational data. Based on the two-dimensional table of the relational data model, a third dimension is introduced to support the description and management of real-time data. This provides a complete solution for the storage, management and analysis of real-time and relational data for IoT applications, enabling users to model, manage and analyze IoT data in one database. This solves the pain points of real-time databases and relational databases in the field of IoT management and analysis, including the lack of data analysis capabilities of real-time databases and the inability of relational databases to meet the performance requirements of real-time data management.

[0187] (ix) Based on the relational data model, this invention extends support for real-time data description and management, and provides a unified data model that integrates real-time data and relational data. It allows users to define simple relational data such as tables, as well as to define relational devices, collection points belonging to the devices, and real-time and historical data stored in the collection points under this model. Attached Figure Description

[0188] The invention will be further described below with reference to the accompanying drawings.

[0189] Appendix Figure 1 This is a diagram illustrating relational data.

[0190] Appendix Figure 2 This is a schematic diagram of a data storage management and query analysis method based on the Internet of Things (IoT).

[0191] Appendix Figure 3 This is a schematic diagram of step interpolation;

[0192] Appendix Figure 4 This is a schematic diagram of linear interpolation;

[0193] Appendix Figure 5 This is a schematic diagram illustrating the scope of the original historical data query. Detailed Implementation

[0194] The following detailed description of the IoT-based data storage management and query analysis method and storage medium of the present invention is provided with reference to the accompanying drawings and specific embodiments.

[0195] Example 1:

[0196] This embodiment provides a data storage management and query analysis method based on the Internet of Things (IoT). This method is based on a relational data model, inheriting the relational data model's functions for describing and querying relational data. A third dimension is introduced into the two-dimensional table of the relational data model; this third dimension is used for describing and managing real-time data. Furthermore, the SQL language is extended to support the definition and querying of both real-time and relational data, enabling the modeling, management, and analysis of IoT data within a single database. Specifically:

[0197] S1. Define the data types for non-real-time data and real-time data;

[0198] S2, Real-time data import;

[0199] S3, Real-time data query;

[0200] S4, Subscribe to publish real-time data;

[0201] S5, Historical Data Query;

[0202] S6, Historical Data Aggregation;

[0203] S7, Real-time-relational data query fusion;

[0204] S8, Real-time / Historical Data Storage and Management.

[0205] The specific definitions of real-time data and relational data in step S1 of this embodiment are as follows:

[0206] S101. Non-real-time changing data in the equipment is described using a relational data model. For example, a certain type of equipment can be defined as a table with information such as equipment name, location, model, manufacturer, and manufacturing time. This is relational data, and the data is basically not updated or is rarely updated.

[0207] S102. For sensor / collection point data that changes frequently in real time in the equipment, describe and manage it in the third dimension of real-time data type; where real-time data type actually stores two types of data: real-time data and historical data; real-time data refers to the latest data, that is, the latest snapshot; historical data is data at historical points in time;

[0208] The data attributes of real-time data types include numerical values, timestamps, and status.

[0209] The numerical value represents the actual value collected by the sensor at the corresponding data point. The numerical value type can be any relational data type; however, in the specific implementation, only common data types can be supported, such as integer, floating-point, boolean, and string. In addition to traditional relational data types, to meet the needs of some sensors that can collect multiple indicators simultaneously (these indicators are indivisible), custom structure data types can also be supported. For example, geographic location information includes two indicators: longitude and latitude, where a single longitude or latitude data is insufficient to represent the actual geographic location.

[0210] The timestamp represents the time when the data was collected. When data from a collection point is entered into the database, if the timestamp corresponding to the data from that collection point is greater than the timestamp of the real-time snapshot data of the collection point, the current real-time snapshot data will be placed in the historical data queue / array, and the real-time snapshot data will be updated to the newly collected data; otherwise, the data from the collection point will be treated as historical data and directly placed in the historical queue / array.

[0211] The status refers to the state of the sensor / collection point when data is collected. The status includes initial, normal, fault, and shutdown. The specific supported statuses can be customized according to application needs. If the collection point is initially created and no data has been written, the status of the collection point is initial. If the data collection point is collecting data normally, the status of the collection point is normal. If the device corresponding to the collection point malfunctions, shuts down, or the corresponding sensor is not working properly, the status of the corresponding collection point will be set to fault, stop, invalid, or other statuses.

[0212] For real-time data, it is also necessary to support functions such as historical data storage, data compression, data interpolation and restoration, and lifecycle management. Therefore, the basic configurable attributes of real-time data types include whether to store history, whether to compress, allowable error, interpolation / restoration algorithm, and lifecycle.

[0213] Whether to store history is used to specify whether to persist historical data; if set to yes, the historical data of the collection point will be persisted, that is, written to the disk for permanent storage; otherwise, the historical data will be discarded directly, and only the real-time data will be stored; the compression algorithm, allowable error, interpolation / restoration method and lifecycle attributes only take effect when the storage history is set;

[0214] The "Compression" setting specifies whether historical data should be compressed during persistence. If set to "Yes," historical data will be compressed online upon entry based on its data type, and decompression will be performed during historical data queries. Otherwise, historical data will not be compressed and will be persisted directly without decompression during queries. Compression algorithms are typically divided into lossy and lossless compression. For lossless compression, the values ​​before compression are completely identical to the values ​​after decompression. Lossy compression allows for some error between the values ​​before and after compression. Generally, IoT applications require high precision for real-time data, and sensors themselves have a certain degree of sensing error, making 100% accuracy impossible. For example, for human body temperature, an accuracy range of 0.1 degrees Celsius is generally sufficient.

[0215] The allowable error is the range of error between the decompressed and interpolated data and the actual value. If the allowable error is Δ, then the decompressed and reconstructed value will differ from the actual value within the range of [-Δ, +Δ], that is:

[0216]

[0217] Where T is the current time; t is any historical time point before T; HisData t This refers to historical data recovered from the data collection points at time t using an interpolation algorithm; ActualData t This refers to the historical data actually occurring at time point t for the sensor / collection point.

[0218] Interpolation / restoration algorithms are used to specify the interpolation / restoration algorithm used when querying historical data. Interpolation / restoration algorithms include step interpolation and linear interpolation. Step interpolation, like a step, means that the current value is maintained until the next data acquisition is reached. This interpolation algorithm is used for digital signals. Linear interpolation refers to an interpolation method where the interpolation function is a first-order polynomial. The interpolation error of the interpolation function at the acquisition time point is zero. Linear interpolation algorithms are usually used for analog signals.

[0219] The lifecycle specifies the minimum time for historical data from a collection point to be persisted. Historical data not exceeding its lifecycle must be retained, while data exceeding its lifecycle is deleted. To reclaim disk space for storing the latest data, data exceeding its lifecycle is typically deleted as early as possible.

[0220] In this embodiment, as shown in the appendix Figure 1 As shown, a relational table in a traditional relational data model is a two-dimensional table with multiple discrete columns, each with its own data type. Multiple discrete records can be inserted into each table. Here, "discrete" refers to data that is not continuously changing and is presented as real-time data. Some time-series databases use records to store data collected by a device at various points in time. The columns represent the collection timestamp and the data from multiple collection points. If only one collection point shows a change at a given time point, the inserted record must be complete, filling in the remaining unchanged data. However, discrete records in relational databases are ill-suited for describing continuously changing real-time data and for meeting the need to query continuously changing historical data.

[0221] As attached Figure 2 As shown, after introducing the third dimension of real-time data into the 3D real-time-relational data model, each table still contains multiple discrete columns, and each table can also insert multiple discrete records. However, the difference lies in the data type of the discrete columns in the table. These discrete columns can be defined as third-dimensional real-time data types; these are called real-time columns, while columns of traditional data types are called relational columns. A table can have multiple relational columns or multiple real-time columns, and they have certain relationships with each other, so they are all defined in one table. However, the data changes of multiple real-time columns are relatively independent. A table containing real-time columns is called a real-time table; if all columns are relational columns, it is called a relational table. A real-time table can be understood as a type of device, where a record represents a specific device, and a real-time column represents a specific data acquisition point.

[0222] The operations supported for real-time columns in a real-time table are similar to those supported for relational columns; both support update (UPDATE). However, the effects of update operations differ. For relational columns, the update operation directly overwrites the relational column data in the current record and updates it with the new data. Update operations for real-time columns are slightly more complex. Each real-time column maintains a queue / database of historical data in addition to the current record's real-time data. The specific operations are as follows:

[0223] Append the real-time column data of the current record to the historical data queue / array;

[0224] Overwrite the real-time column with new data in the current record;

[0225] If the "Store History" attribute of the real-time column is set to no, then the update operation of the real-time column is the same as the update operation of the relational column, and the data of the real-time column can be directly overwritten on the current record.

[0226] If the real-time column stores historical data, then each record / device of the real-time table will maintain a historical data queue / array for that real-time column.

[0227] This embodiment supports extended SQL syntax to perform operations such as creating real-time tables, inserting records / devices, querying real-time attributes, modifying real-time attributes, importing real-time data into the database, subscribing to and publishing real-time data, and querying real-time / historical data.

[0228] The syntax for creating a real-time table is as follows:

[0229] CREATE TABLE table_name(

[0230] column1[realtime]datatype,

[0231] column2[realtime]datatype,

[0232] column3[realtime]datatype, ...

[0234] columnN[realtime]datatype,

[0235] PRIMARY KEY(one or more columns) );

[0237] Adding the keyword "realtime" (which can be abbreviated to "rt") before the data type of a column indicates that the field / column is a real-time column; otherwise, it is a relational column.

[0238] A real-time table is used to define a class of devices in an IoT application, containing the same relational columns and the same real-time columns; see attached. Figure 2 The table shown is a real-time table that records a certain type of equipment. Each record in the table represents a specific piece of equipment.

[0239] Because the real-time data in real-time columns changes frequently, no real-time column is allowed to be defined as the primary key of a real-time table. The syntax for inserting a record into a real-time table is as follows:

[0240] INSERT INTO table_name(column1,column2,column3,...)

[0241] VALUES

[0242] value1|{initial:value1,historian:his1,compressed:comp1,deviation:dev1,interpolation:inter1,lifetime:life1},

[0243] value2|{initial:value2,historian:his2,compressed:comp2,deviation:dev1,interpolation:inter2,lifetime:life2},

[0244] value3|{initial:value3,historian:his3,compressed:comp3,deviation:dev1,interpolation:inter3,lifetime:life3}, ... );

[0247] If any column is a relational column, the value corresponding to that relational column is consistent with the traditional value format; if any column is a real-time column, the value corresponding to that real-time column is a tuple, which includes the initial value, whether to store history, whether to compress, allowable deviation, interpolation / restoration algorithm, and lifetime.

[0248] If a table is a relational table, inserting a record is the same as inserting a record in a traditional relational table; if a table is a real-time table, inserting a record is the same as adding a device.

[0249] The syntax for querying the real-time column attributes in a table is as follows:

[0250] SELECT

[0251] column1[.id|.datatype|.historian|.compressed|.deviation|.interpolation|.lifetime],

[0252] column2[.id|.datatype|.historian|.compressed|.deviation|.interpolation|.lifetime],

[0253] column3[.id|.datatype|.historian|.compressed|.deviation|.interpolation|.lifetime], ...

[0255] FROM table_name

[0256] WHERE condition

[0257] GROUP BY column1[.id|.datatype|.historian|.compressed|.deviation|.interpolation|.lifetime];

[0258] ORDER BY column2[.id|.datatype|.historian|.compressed|.deviation|.interpolation|.lifetime];

[0259] If the column in the select list is a real-time column, the query returns a real-time snapshot of the corresponding real-time column in the record. Adding a "." after the column followed by the corresponding attribute name will retrieve the attribute value of the corresponding real-time column in the record. If the column in the select list is a relational column, the query returns the data of the corresponding relational column in the record. Real-time column attribute queries support the following attributes: whether to store history, whether to compress, allowable error, interpolation / restoration algorithm, and lifecycle. They also support the real-time column's ID and its data type (datatype). The real-time column ID is a unique ID automatically generated for the corresponding real-time column when a record is written. This ID allows direct access to the real-time column; its purpose will be explained in detail later when discussing fast database insertion via the interface.

[0260] Using the attribute names of the real-time column in the WHERE, GROUP BY, and ORDER BY clauses is equivalent to treating the attributes of the real-time column as ordinary relational columns.

[0261] Modifying Real-Time Column Attributes: When adding a record to the real-time table, you can set various attributes of the real-time columns and modify them within the record. The syntax for modifying real-time column attributes is as follows:

[0262] UPDATE table_name

[0263] SET column1[.historian|.compress|.deviation|.interpolation|.lifetime]=v1

[0264] SET column2[.historian|.compress|.deviation|.interpolation|.lifetime]=v2

[0265] ...

[0266] WHERE condition;

[0267] If column is a real-time column, the SQL statement will update the real-time snapshot of the record; if column is followed by a "." and the corresponding attribute name, the attribute value of the real-time column in the record will be updated; if column is a relational column, the SQL statement will update the relational column data in the record.

[0268] The specific method for real-time data import in step S2 of this embodiment is as follows:

[0269] ①SQL method:

[0270] The SQL syntax for updating real-time data is as follows:

[0271] UPDATE table_name

[0272] SET column1=value1|{value1[,timestamp1][,status1]}

[0273] SET column2=value2|{value2[,timestamp2][,status2]}

[0274] ...

[0275] WHERE condition;

[0276] The SQL syntax extends the traditional UPDATE SQL syntax, adding support for real-time data updates; if the updated column is a real-time data type, the previous real-time value will be stored as historical data, and the new value will replace the real-time value; if the updated column is a relational data type, the data in the record will be directly overwritten.

[0277] ② Extended ODBC / JDBC methods

[0278] In an IoT environment, some data is updated frequently, but not very often. Using SQL for data import can meet application needs. However, some data is updated very frequently and in large volumes, such as in industrial control. Therefore, using SQL for updates cannot meet real-time data import requirements. To meet the needs of these applications, the ODBC / JDBC interface is extended to support high-speed real-time data import. The interface definition is as follows:

[0279] bool UpdateRealTimeData(long record_column_id,data_type value);

[0280] Where record_column_id is the real-time column ID corresponding to the updated record; returning true indicates that the data was successfully inserted into the database, otherwise the data insertion failed.

[0281] The specific database system implementation can also provide an interface for batch data writing as needed, thereby providing a high-speed, real-time data import interface.

[0282] The syntax for querying records in a table during the real-time data query process in step S3 of this embodiment is as follows:

[0283] SELECT

[0284] column1[.value|.timestamp|.status],

[0285] column2[.value|.timestamp|.status],

[0286] column3[.value|.timestamp|.status], ...

[0288] FROM table_name

[0289] WHERE condition

[0290] GROUP BY column1[.value|.timestamp|.status];

[0291] ORDER BY column2[.value|.timestamp|.status];

[0292] This select query will return multiple records / devices that meet the conditions. In addition to returning the values ​​of the relation columns, each record will also return the real-time value / latest snapshot of the real-time column, which includes the attributes of value, timestamp, and status. Alternatively, the user can specify any attribute of the real-time data in the real-time column to return, represented by column.value, column.timestamp, and column.status respectively.

[0293] The specific details of subscribing to and publishing real-time data in step S4 of this embodiment are as follows:

[0294] In the Internet of Things (IoT) environment, especially in industrial control, large amounts of real-time data are frequently updated. Changes in this data can trigger alarms or malfunctions, requiring upper-layer applications to obtain this latest data promptly for analysis and processing, and to react as needed. Traditional relational databases can only retrieve the latest data through SQL queries. However, applications do not know when real-time data will change, necessitating frequent polling to obtain the latest data as quickly as possible. This places a heavy load on the database server, degrading database performance. Industrial real-time databases offer an efficient publish-subscribe service, promptly publishing the latest real-time data to clients for processing and analysis. To better meet the needs of IoT applications, a 3D real-time-relational data model provides publish-subscribe functionality.

[0295] The SQL syntax for subscribing to real-time data is as follows:

[0296] SUBSCRIBE column1, column2,...

[0297] FROM table_name

[0298] WHERE condition;

[0299] Where column must be a real-time column, SQL will subscribe to all real-time columns in the select list corresponding to the records that meet the conditions, and return the IDs of all real-time columns that were successfully subscribed to;

[0300] Additionally, you can subscribe to real-time data using the real-time column ID. The SQL syntax is as follows:

[0301] SUBSCRIBE id1,id2,id3,…;

[0302] The SQL statement `SUBSCRIBE id1,id2,id3,…` will return the real-time column IDs of all successfully subscribed items.

[0303] The SQL syntax for canceling a real-time data subscription is as follows:

[0304] UNSUBSCRIBE column1,column2,...

[0305] FROM table_name

[0306] WHERE condition;

[0307] Where column must be a real-time column, the SQL will unsubscribe from all real-time columns in the select list corresponding to the records that meet the conditions, and return the IDs of all real-time columns that were successfully unsubscribed;

[0308] Additionally, to cancel a real-time data subscription using the real-time column ID, the SQL syntax is as follows:

[0309] UNSUBSCRIBE id1,id2,id3,…;

[0310] The SQL statement UNSUBSCRIBE id1,id2,id3,… will return the real-time column id for all successfully unsubscribed items;

[0311] Alternatively, you can cancel the subscription to all real-time data for the current session using the SQL statement UNSUBSCRIBE ALL:

[0312] The SQL statement UNSUBSCRIBE ALL will return the ID column of all successfully unsubscribed live data. After the SQL is executed successfully, all live data currently subscribed to in the current session will be canceled.

[0313] The SQL syntax for displaying all subscribed real-time data is as follows:

[0314] SHOW SUBSCRIPTIONS;

[0315] The SQL statement for SHOW SUBSCRIPTIONS will display the ID column for all subscribed live data.

[0316] Get the latest real-time data: After the client successfully subscribes to real-time data, when the database system detects an update to the real-time data, it will actively push the latest data to the client, so that the client can get the latest data in a timely manner;

[0317] To avoid using SQL polling to obtain the latest real-time data, the ODBC / JDBC interface is extended to provide an interface function for obtaining published real-time data. The specific definition of the real-time data interface function is as follows:

[0318] bool GetRealTimeData(rt_data&value,int timeout);

[0319] Here, `rt_data` is a real-time data object class that provides an interface to obtain the ID, data type, value, status, and timestamp of a real-time column; the second parameter is the maximum wait timeout; the function returns `true`, indicating that the latest published data was successfully retrieved; the function returns `false`, indicating that the retrieval of published data failed, possibly due to a timeout or a disconnection from the server.

[0320] The historical data query in step S5 of this embodiment includes historical data query at a specified time point, raw historical data query within a specified time range, and interpolated historical data query within a specified time range.

[0321] The SQL syntax for querying historical data at a specific time point is as follows:

[0322] SELECT column1, column2, ...

[0323] FROM table_name

[0324] AT timestamp

[0325] WHERE condition;

[0326] The SQL statement will return multiple records / devices at any time point timestamp that meet the conditions; among them, column is a real-time column or a relational column; for a real-time column, if there is no data corresponding to the historical time point of the timestamp in the historical data queue, interpolation and restoration will be performed according to the specified interpolation restoration algorithm based on the original historical data before and after the interpolation time point, and the interpolated and restored data will be returned, and the error between the restored value and the actual value at the corresponding time point is less than the allowable error range;

[0327] The interpolation restoration algorithm includes step interpolation and linear interpolation;

[0328] Specifically, for step interpolation: Let there be n originally collected historical data HisData for any real-time column of any record / device i , where i ∈ [1, n], and the corresponding historical timestamps are T i , where T i < T i+1 ; If the step interpolation algorithm is used for this real-time column, for a given historical time point t, let T i ≤ t < T i+1 , and its historical data Value t = HisData i ; As shown in the appendix Figure 3 , when T3 ≤ t < T4, the historical data Value of time point t t = HisData3;

[0329] Specifically, for linear interpolation: If the linear interpolation algorithm is used for this real-time column, for a given historical time point t, let T i ≤ t < T i+1 , and its historical data is:

[0330]

[0331] As shown in the appendix Figure 4 , when T3 ≤ t < T4, the historical data of time point t is:

[0332]

[0333] The SQL statement for querying the original historical data within a specified time range is specifically as follows:

[0334] SELECT column1,column2,...

[0335] FROM table_name

[0336] AT\[timestamp1,timestamp2\]

[0337] WHERE condition;

[0338] This SQL statement returns the raw historical data of devices / records that meet the conditions within the time range [timestamp1, timestamp2]. Here, `column` can be a real-time column or a relational column. For relational columns, simply fill in the corresponding relational column values. For real-time columns, the data is the data corresponding to the historical timestamp. Because the change times of each real-time column are inconsistent, the data collection and storage times will be inconsistent, and it's possible that only one or some real-time columns are updated at a certain time point. Therefore, when querying raw historical data, if at least one real-time column has collected data stored at a certain timestamp, a record will be generated for that timestamp. For real-time columns that haven't been collected, an interpolation restoration algorithm will be used to recover their historical data, and their data status will be set to restored. For real-time columns that have been collected, the data for that timestamp will be returned directly, and its data status will be set to raw.

[0339] Due to the uncertainty of real-time data change times / collection times, when querying raw historical data, there may be situations where no raw historical data is found within the query range. Additionally, there may be periods after timestamp1 or before timestamp2 where no raw historical data is available, but historical data exists during these periods, which are typically required for queries. Therefore, when querying raw historical data within a specified time range, the time range needs to be extended outwards. If no raw historical data is found at timestamp1, the search will move backwards to find and return the most recent raw historical data; similarly, if no raw historical data is found at timestamp2, the search will move forwards to find and return the most recent raw historical data.

[0340] Suppose that any record / device has n raw historical data points HisData in any real-time column. i Where i∈[1,n], T j ≤timestamp1 <T j+1 And T l ≤timestamp2 <T l+1 The result set of querying the original historical data within the time range [timestamp1, timestamp2] is as follows:

[0341]

[0342] As attached Figure 5As shown, assume that T2 ≤ timestamp1 < T3 and T4 ≤ timestamp2 < T5. Then the result set of querying the original historical data within the time range [timestamp1, timestamp2] is as follows:

[0343] ResultSet = {HisData2, HisData3, HisData4, HisData5};

[0344] The SQL syntax for interpolating and querying historical data within a specified time range is as follows:

[0345] SELECT column1, column2,...

[0346] FROM table_name

[0347] AT\[timestamp1,timestamp2\]

[0348] INTERVAL interval

[0349] WHERE condition;

[0350] This SQL statement will return the interpolated historical data of devices / records that meet the conditions within the specified time range. For each device / record, it starts from timestamp1 and generates a record every interval. The real-time column data of each record is interpolated and restored from the original historical data before and after the interpolation time point according to the specified interpolation restoration algorithm, and the relationship column data of each record is filled with the corresponding relationship column data of its device / record.

[0351] The historical data aggregation functions in step S6 of this embodiment include count, sum, max, min, avg, mean, first, last, stddev. When using historical data aggregation functions, the "AT[timestamp1,timestamp2]" clause must be used, that is, the historical data aggregation function is a calculation for any historical time range;

[0352] count(column): It will return the number of original historical data of the real-time column within the specified time range;

[0353] sum(column): Returns the sum of all original historical data within the specified time range;

[0354] `max(column)`: Returns the original historical data with the largest value within a specified time range, including the maximum value, timestamp, and status. If multiple original historical data points at different time points have the same maximum value, multiple records will be returned. If the `max` function is applied to multiple real-time columns, it will return multiple records, listing all possible combinations. For example, if `max(column1)` has 2 records and `max(column2)` has 3 records, then 2 * 3 = 6 records will be returned. If the timestamps corresponding to the `max` values ​​of the two columns are the same, the number of records returned will be less than 6.

[0355] min(column): This function is similar to the max function, but it returns the original historical data with the minimum value within a specified time range;

[0356] avg(column): Returns the average value of the raw historical data within a specified time range. The average value is calculated as follows:

[0357]

[0358] Among them, HisData i It is the original historical data within a time frame;

[0359] mean(column): Returns the integral average over a specified time range. The integral average is calculated as follows:

[0360]

[0361] Among them, T i For HisData i The corresponding timestamp;

[0362] first(column): Returns the first original historical data within the specified time range;

[0363] last(column): Returns the last original historical data within the specified time range;

[0364] stddev(column): The function scope specifies the standard deviation of all historical data within a given time range;

[0365] If an INTERVAL clause follows the AT clause in a query, the query results will be grouped by time intervals. Historical data aggregation functions will be calculated within each time interval group, rather than the entire specified time range. In addition, INTERVAL can be understood as a time interval grouping that operates on the real-time column. If both INTERVAL and GROUP BY appear in a SQL statement, the operation priority of INTERVAL is higher than that of GROUP BY.

[0366] The real-time-relationship data fusion query in step S7 of this embodiment is as follows:

[0367] All real-time table query results will be automatically converted into two-dimensional tables. The real-time columns in the records contain real-time data, including values, timestamps, and status. The results of real-time table queries are the same as those of two-dimensional tables, allowing for any operations supported by relational tables, including project, filter, limit, group by, and order by. At the same time, the results of real-time table queries can be used for complex join queries with any other relational table or results of other real-time table queries, including join and union.

[0368] The real-time / historical data storage management in step S8 of this embodiment is as follows:

[0369] Real-time snapshot data can be stored along with the current device / record, requiring an update to the corresponding device / record each time the real-time snapshot is updated; alternatively, all real-time snapshot data can be stored and managed separately using main memory, with only the real-time column ID recorded in the device / record. The choice of which approach to adopt depends on which method facilitates faster data import and retrieval.

[0370] For historical data, each device's real-time column has an independent historical data queue / array. This can be stored as a two-dimensional table (like in a relational model), with records inserted or appended each time a historical data entry is added. Each record contains a value, a timestamp, and a status. Alternatively, an array or list of data blocks can be used for storage, facilitating rapid insertion and appending of historical data. Each historical data entry is a structure containing a value, a timestamp, and a status. The specific approach chosen depends on which method is more conducive to rapid data storage and retrieval.

[0371] Example 2:

[0372] This embodiment also provides an electronic device, including: a memory and a processor;

[0373] The memory stores the instructions executed by the computer.

[0374] The processor executes computer execution instructions stored in the memory, causing the processor to execute the Internet of Things-based data storage management and query analysis method in any embodiment of the present invention.

[0375] The processor can be a central processing unit (CPU), or other general-purpose processors, digital signal processors (DSPs), application-specific integrated circuits (ASICs), off-the-shelf programmable gate arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. The processor can be a microprocessor or any conventional processor.

[0376] Memory is used to store computer programs / modules. The processor implements various functions of electronic devices by running or executing computer programs / modules stored in memory and by accessing data stored in memory. Memory can mainly include a program storage area and a data storage area. The program storage area can store the operating system, at least one application program required for a function, etc.; the data storage area can store data created based on terminal usage, etc. In addition, memory can also include high-speed random access memory, and can also include non-volatile memory, such as hard disks, RAM, plug-in hard disks, smart memory cards (SMC), secure digital storage (SD) cards, flash memory cards, at least one disk storage device, flash memory devices, or other volatile solid-state storage devices.

[0377] Example 3:

[0378] This embodiment also provides a computer-readable storage medium storing multiple instructions, which are loaded by a processor to cause the processor to execute the IoT-based data storage management and query analysis method according to any embodiment of the present invention. Specifically, a system or device equipped with a storage medium may be provided, on which software program code implementing the functions of any of the above embodiments is stored, and the computer (or CPU or MPU) of the system or device may read and execute the program code stored in the storage medium.

[0379] In this case, the program code read from the storage medium can itself implement the function of any of the above embodiments, and therefore the program code and the storage medium storing the program code constitute part of the present invention.

[0380] Storage media embodiments for providing program code include floppy disks, hard disks, magneto-optical disks, optical disks (such as CD-ROM, CD-R, CD-RW, DVD-ROM, DVD-RYM, DVD-RW, DVD+RW), magnetic tapes, non-volatile memory cards, and ROMs. Alternatively, program code can be downloaded from a server computer via a communication network.

[0381] Furthermore, it should be clear that not only can the program code read by the computer be executed, but also the operating system or other components operating on the computer can be instructed based on the program code to perform some or all of the actual operations, thereby realizing the function of any of the embodiments described above.

[0382] Furthermore, it is understood that the program code read from the storage medium is written to the memory set in the expansion board inserted into the computer or to the memory set in the expansion unit connected to the computer. Then, based on the instructions of the program code, the CPU or other components installed on the expansion board or expansion unit execute some and all of the actual operations, thereby realizing the function of any of the embodiments described above.

[0383] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, and not to limit them; although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some or all of the technical features; and these modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of the present invention.

Claims

1. A data storage management and query analysis method based on the Internet of Things, characterized in that, This method is based on a relational data model, inheriting its functions for describing and querying relational data. It introduces a third dimension into the two-dimensional tables of the relational data model, used for describing and managing real-time data. Furthermore, it extends the SQL language to support the definition and querying of both real-time and relational data, enabling the modeling, management, and analysis of IoT data within a single database. Specifically: Define non-real-time data and real-time data types; Real-time data import; Real-time data query; Subscribe to publish real-time data; Historical data query; Historical data aggregation; Real-time relational data query fusion; Real-time / historical data storage management; The definitions of real-time data and relational data are as follows: Non-real-time changing data in the equipment is described using a relational data model; For sensor / collection point data that changes frequently in real time within the device, it is described and managed in the third dimension as real-time data type; the real-time data type actually stores two types of data: real-time data and historical data; real-time data refers to the latest data, i.e., the latest snapshot; historical data is data at historical points in time; The data attributes of real-time data types include numerical values, timestamps, and status. The value is the actual value collected by the sensor at the corresponding collection point, and the value type can be any relational data type or a user-defined structure data type. The timestamp represents the time when the data was collected. When data from a collection point is entered into the database, if the timestamp corresponding to the data from that collection point is greater than the timestamp of the real-time snapshot data of the collection point, the current real-time snapshot data will be placed in the historical data queue / array, and the real-time snapshot data will be updated to the newly collected data; otherwise, the data from the collection point will be treated as historical data and directly placed in the historical queue / array. The status refers to the state of the sensor / collection point when data is collected. The status includes initial, normal, fault, and shutdown. If the collection point is initially created and no data has been written, the status of the collection point is initial. If the data is collected normally, the status of the collection point is normal. If the device corresponding to the collection point malfunctions, shuts down, or the corresponding sensor is not working properly, the status of the corresponding collection point will be set to fault, stop, invalid, or other status. The basic configurable attributes of real-time data types include whether to store history, whether to compress, allowable error, interpolation / restoration algorithm, and lifecycle; Whether to store history is used to specify whether to persist historical data; if set to yes, the historical data of the collection point will be persisted, that is, written to the disk for permanent storage; otherwise, the historical data will be discarded directly, and only the real-time data will be stored; the compression algorithm, allowable error, interpolation / restoration method and lifecycle attributes only take effect when the storage history is set; Whether to compress is specified to determine whether historical data is compressed during persistence. If set to yes, historical data will be compressed online upon insertion based on its data type, and decompression will be performed during historical data queries. Otherwise, historical data will not be compressed and will be persisted directly without decompression during queries. Compression algorithms are generally divided into lossy compression and lossless compression. For lossless compression, the value before compression is exactly the same as the value after decompression. Lossy compression allows for errors between the value before compression and the value after decompression. The allowable error is the range of error between the decompressed and interpolated data and the actual value. If the allowable error is... The decompressed and restored value will differ from the actual value by [-]. , + Within the range, that is: ； Where T is the current time; t is any historical point in time before T; This refers to the historical data recovered from the data collection point at time t using an interpolation restoration algorithm. This refers to the historical data actually occurring at time point t for the sensor / collection point. Interpolation / restoration algorithms are used to specify the interpolation / restoration algorithm used when querying historical data. Interpolation / restoration algorithms include step interpolation and linear interpolation. Step interpolation means that the current value is maintained until the next data acquisition is reached. This interpolation algorithm is used for digital signals. Linear interpolation means that the interpolation function is a first-order polynomial. The interpolation error of the interpolation function at the acquisition time point is zero. Linear interpolation algorithm is usually used for analog signals. The lifecycle specifies the minimum time for historical data from a collection point to be persisted. Historical data that has not exceeded its lifecycle must be retained, while data that has exceeded its lifecycle must be deleted.

2. The data storage management and query analysis method based on the Internet of Things according to claim 1, characterized in that, After introducing a third dimension into a two-dimensional table based on a relational data model, each table includes multiple discrete columns and a real-time column. A table containing a real-time column is called a real-time table. A real-time table is a type of device, where each record is a specific device, and the real-time column is any specific data collection point. In addition to the current record's real-time data, each real-time column maintains a historical data queue / data. The specific operations are as follows: Append the real-time column data of the current record to the historical data queue / array; Overwrite the real-time column with new data in the current record; If the "store history" attribute of the real-time column is set to no, then the update operation of the real-time column is the same as the update operation of the relational column, and the data of the real-time column can be directly overwritten on the current record. The syntax for creating a real-time table is as follows: CREATE TABLE table_name( column1 [realtime] datatype, column2 [realtime] datatype, column3 [realtime] datatype, ..... columnN [realtime] datatype, PRIMARY KEY(one or more columns) )； Adding the keyword "realtime" before the data type of a column indicates that the field / column is a real-time column; otherwise, it is a relational column. A real-time table is used to define a class of devices in an IoT application, containing the same relational columns and the same real-time columns; the syntax for inserting a record into the real-time table is as follows: INSERT INTO table_name (column1, column2, column3, ...) VALUES ( value1 | {initial:value1, historian:his1, compressed:comp1, deviation:dev1, interpolation:inter1, lifetime:life1}, value2 | {initial:value2, historian:his2, compressed:comp2, deviation:dev1, interpolation:inter2, lifetime:life2}, value3 | {initial:value3, historian:his3, compressed:comp3, deviation:dev1, interpolation:inter3, lifetime:life3}, ... )； If any column is a relational column, the value corresponding to that relational column is in the same format as the traditional value; if any column is a real-time column, the value corresponding to that real-time column is a tuple, which includes the initial value, whether to store history, whether to compress, allowable error, interpolation / restoration algorithm, and lifecycle. If a table is a relational table, inserting a record is the same as inserting a record in a traditional relational table; if a table is a real-time table, inserting a record is the same as adding a device. The syntax for querying the real-time column attributes of a table is as follows: SELECT column1[.id | .datatype | .historian | .compressed | .deviation |.interpolation | .lifetime], column2[.id | .datatype | .historian | .compressed | .deviation |.interpolation | .lifetime], column3[.id | .datatype | .historian | .compressed | .deviation |.interpolation | .lifetime], ... FROM table_name WHERE condition GROUP BY column1[.id | .datatype | .historian | .compressed | .deviation| .interpolation | .lifetime]; ORDER BY column2[.id | .datatype | .historian | .compressed | .deviation| .interpolation | .lifetime]; If the column in the select list is a real-time column, the query returns a real-time snapshot of the corresponding real-time column in the record. Adding "." and the corresponding attribute name after the column will return the attribute values ​​for the corresponding real-time column in the record. If the column in the select list is a relational column, the query returns the data for the corresponding relational column in the record. Real-time column attribute queries support the following attributes: whether to store history, whether to compress, allowable error, interpolation / restoration algorithm, and lifecycle. They also support the real-time column's ID and its data type (datatype). The real-time column ID is a unique ID automatically generated for the corresponding real-time column when a record is written; this ID is used to directly access the real-time column. Using the attribute names of the real-time column in the WHERE, GROUP BY, and ORDER BY clauses is equivalent to treating the attributes of the real-time column as ordinary relational columns. Modifying Real-Time Column Attributes: When adding a record to the real-time table, you can set various attributes of the real-time columns and modify them within the record. The syntax for modifying real-time column attributes is as follows: UPDATE table_name SET column1[.historian | .compress | .deviation | .interpolation |.lifetime] = v1 SET column2[.historian | .compress | .deviation | .interpolation |.lifetime] = v2 …… WHERE condition; If column is a real-time column, the SQL statement will update the real-time snapshot of the record; if column is followed by "." and the corresponding attribute name, the attribute value of the real-time column in the record will be updated; if column is a relational column, the SQL statement will update the relational column data in the record.

3. The data storage management and query analysis method based on the Internet of Things according to claim 1 or 2, characterized in that, The specific methods for real-time data import are as follows: ①SQL method: The SQL syntax for updating real-time data is as follows: UPDATE table_name SET column1 = value1 | {value1[,timestamp1][,status1]} SET column2 = value2 | {value2[,timestamp2][,status2]} …… WHERE condition; The SQL syntax now supports real-time data updates; if the column being updated is a real-time data type, the previous real-time value will be stored as historical data, and the new value will replace the real-time value; if the column being updated is a relational data type, the data in the record will be overwritten. ② Extend ODBC / JDBC to provide an API method for directly writing data via function calls: The function is defined as: bool UpdateRealTimeData(long record_column_id, data_typevalue); Where record_column_id is the real-time column ID corresponding to the updated record; returning true indicates that the data was successfully inserted into the database, otherwise the data insertion failed. The syntax for querying records from a table during real-time data querying is as follows: SELECT column1[.value | .timestamp| .status], column2[.value | .timestamp| .status], column3[.value | .timestamp| .status], ... FROM table_name WHERE condition GROUP BY column1[.value | .timestamp| .status]; ORDER BY column2[.value | .timestamp| .status]; This select query will return multiple records / devices that meet the conditions. In addition to returning the values ​​of the relation columns, each record will also return the real-time value / latest snapshot of the real-time column, which includes the attributes of value, timestamp and status; or, the user can specify any attribute of the real-time data in the real-time column to return, which is represented by column.value, column.timestamp and column.status respectively.

4. The data storage management and query analysis method based on the Internet of Things according to claim 3, characterized in that, The details of subscribing to and publishing real-time data are as follows: The SQL syntax for subscribing to real-time data is as follows: SUBSCRIBE column1, column2, ... FROM table_name WHERE condition; Where column must be a real-time column, SQL will subscribe to all real-time columns in the select list corresponding to the records that meet the conditions, and return the IDs of all real-time columns that were successfully subscribed to; Additionally, you can subscribe to real-time data using the real-time column ID. The SQL syntax is as follows: SUBSCRIBE id1, id2, id3, …; The SQL statement `SUBSCRIBE id1, id2, id3, …` will return the real-time column IDs of all successfully subscribed items. The SQL syntax for canceling a real-time data subscription is as follows: UNSUBSCRIBE column1, column2, ... FROM table_name WHERE condition; Where column must be a real-time column, the SQL will unsubscribe from all real-time columns in the select list corresponding to the records that meet the conditions, and return the IDs of all real-time columns that were successfully unsubscribed; Additionally, to cancel a real-time data subscription via the real-time column ID, the SQL syntax is as follows: UNSUBSCRIBE id1, id2, id3, …; The SQL statement `UNSUBSCRIBE id1, id2, id3, …` will return the `id` column for all successfully unsubscribed entries. Alternatively, you can cancel the subscription to all real-time data for the current session using the SQL statement UNSUBSCRIBE ALL: The SQL statement UNSUBSCRIBE ALL will return the ID column of all successfully unsubscribed live data. After the SQL is executed successfully, all live data currently subscribed to by the current session will be canceled. The SQL syntax for displaying all subscribed real-time data is as follows: SHOW SUBSCRIPTIONS; The SQL statement for SHOW SUBSCRIPTIONS will display all subscribed live column IDs. Get the latest real-time data: After the client successfully subscribes to real-time data, when the database system detects an update to the real-time data, it will actively push the latest data to the client, so that the client can get the latest data in a timely manner; Extending the ODBC / JDBC interface, it provides interface functions for obtaining published real-time data. The specific definition of the real-time data interface functions is as follows: bool GetRealTimeData(rt_data& value, int timeout); Here, `rt_data` is a real-time data object class that provides an interface to obtain the ID, data type, value, status, and timestamp of a real-time column; the second parameter is the maximum wait timeout; the function returns `true`, indicating that the latest published data was successfully retrieved; the function returns `false`, indicating that the retrieval of published data failed, possibly due to a timeout or a disconnection from the server.

5. The data storage management and query analysis method based on the Internet of Things according to claim 4, characterized in that, Historical data queries include queries for historical data at a specified point in time, queries for raw historical data within a specified time range, and queries for interpolated historical data within a specified time range. The SQL syntax for querying historical data at a specific time point is as follows: SELECT column1, column2, ... FROM table_name AT timestamp WHERE condition; The SQL statement will return multiple records / devices for any given timestamp that meet the conditions; where column is a real-time column or a relational column; for real-time columns, if there is no data for the corresponding timestamp in the historical data queue, interpolation will be performed based on the original historical data before and after the interpolation time point according to the specified interpolation algorithm, and the interpolated data will be returned, and the error between the restored value and the actual value at the corresponding time point will be less than the allowable error range; Interpolation restoration algorithms include step interpolation and linear interpolation; Step interpolation specifically involves: assuming any record / device has n original historical data points in any real-time column. Where i ∈ [1, n], the corresponding historical timestamps are respectively ,in If the real-time column uses the step interpolation algorithm, then for a given historical time point t, let... Its historical data ;when Historical data at time point t ; Linear interpolation specifically refers to: if the real-time column uses a linear interpolation algorithm, then for a given historical time point t, let... Its historical data is as follows: ； when At that time, the historical data for point t is as follows: ； The SQL syntax for retrieving raw historical data within a specified time range is as follows: SELECT column1, column2, ... FROM table_name AT \[timestamp1, timestamp2\] WHERE condition; This SQL statement will return the raw historical data of devices / records that meet the conditions within the time range [timestamp1, timestamp2]; where column is either a real-time column or a relational column; for relational columns, simply fill in the corresponding relational column values ​​for the records; for real-time columns, the data is the data corresponding to the historical timestamps; Suppose that any record / device has n raw historical data points in any real-time column. Where iÎ[1,n], and The result set of querying the original historical data within the time range [timestamp1, timestamp2] is as follows: ； set up and The result set of querying the original historical data within the time range [timestamp1, timestamp2] is as follows: ； The SQL syntax for interpolating historical data within a specified time range is as follows: SELECT column1, column2, ... FROM table_name AT \[timestamp1, timestamp2\] INTERVAL interval WHERE condition; This SQL statement will return the interpolated historical data of devices / records that meet the conditions within the specified time range. Each device / record starts from timestamp1 and generates one record every interval. The real-time column data of each record is the data that is interpolated and restored according to the specified interpolation restoration algorithm based on the original historical data before and after the interpolation time point. The relation column data of each record is filled with the relation column data corresponding to its device / record.

6. The data storage management and query analysis method based on the Internet of Things according to claim 5, characterized in that, Historical data aggregation functions include count, sum, max, min, avg, mean, first, last, median, and stddev. When using historical data aggregation functions, the "AT [timestamp1, timestamp2]" clause must be used, meaning that historical data aggregation functions are calculated for any historical time range. count(column): Returns the number of original historical data entries for the current column within the specified time range; sum(column): Returns the sum of all raw historical data within a specified time range; max(column): Returns the original historical data with the maximum value within the specified time range, including the maximum value, timestamp and status; If multiple original historical data points at different historical time points have the same maximum value, multiple records will be returned; if the max function is applied to multiple real-time columns, multiple records will be returned, listing all possible combinations. min(column): Returns the original historical data with the minimum value within the specified time range; avg(column): Returns the average value of the raw historical data within a specified time range. The average value is calculated as follows: ； in, It is the original historical data within a time frame; mean(column): Returns the integral average over a specified time range. The integral average is calculated as follows: ； in, for The corresponding timestamp; first(column): Returns the first original historical data within the specified time range; last(column): Returns the last original historical data within the specified time range; stddev(column): The function scope specifies the standard deviation of all historical data within a given time range; If an INTERVAL clause follows the AT clause in a query, the query results will be grouped by time intervals. Historical data aggregation functions will be calculated within each time interval group, rather than the entire specified time range. In addition, INTERVAL can be understood as a time interval grouping that operates on the real-time column. If both INTERVAL and GROUP BY appear in a SQL statement, the operation priority of INTERVAL is higher than that of GROUP BY.

7. The data storage management and query analysis method based on the Internet of Things according to claim 6, characterized in that, The real-time-relationship data fusion query is as follows: All real-time table query results will be automatically converted into two-dimensional tables. The real-time columns in the records contain real-time data, including values, timestamps, and status. The results of real-time table queries are the same as those of two-dimensional tables, allowing for any operations supported by relational tables, including project, filter, limit, group by, and order by. At the same time, the results of real-time table queries can be used for complex join queries with any other relational table or results of other real-time table queries, including join and union.

8. An electronic device, characterized in that, include: Memory and at least one processor; The memory contains computer programs; The at least one processor executes the computer program stored in the memory, causing the at least one processor to perform the Internet of Things-based data storage management and query analysis method as described in any one of claims 1 to 7.

9. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a computer program that can be executed by a processor to implement the Internet of Things-based data storage management and query analysis method as described in any one of claims 1 to 7.

Images