CN114817334A - Real-time storage and query system for mass device data - Google Patents

Abstract

The invention discloses a real-time storage and query system for mass equipment data, which is internally provided with a database and comprises: the data writing module is in communication connection with the external equipment and the database and is used for initiating data storage to the database once through configuring a regular and quantitative rule when the equipment data is reported; the data query module is in communication connection with the database and is used for providing an API (application programming interface), providing paging query according to a time range or an equipment ID (identity) field and performing aggregation query according to an aggregation function and an aggregation period; and the database switching module is provided with an API (application programming interface) and is used for switching the inflixdb, clickhouse and tdengine databases in real time through the API. The mass device data real-time storage and query system supports device data access of different databases, efficiently stores the data and simultaneously ensures query convenience.

Description

Real-time storage and query system for mass device data

Technical Field

The invention relates to a data system, in particular to a mass equipment data real-time storage and query system.

Background

With the gradual development of the internet of things technology, more and more devices are accessed into the internet of things platform, so that an internet of things platform with massive access devices is formed, and the current internet of things platform generally sets a database for storing device data in real time and realizing device data query, however, the current internet of things platform adopts a direct receiving and direct querying mode, so that the following problems can be caused:

mass equipment data real-time storage problem: when a large number of devices access the internet of things platform and the reporting frequency is high, great pressure is exerted on the storage capacity of the database;

mass equipment data query problem; mass equipment data are stored in a database, and quick and efficient query needs to be carried out according to multidimensional conditions such as time, equipment id and the like;

different database problems: different clients may have their own databases and corresponding business processes on their own servers, and the clients may wish to write device history data to their databases.

Disclosure of Invention

Aiming at the defects in the prior art, the invention aims to provide a mass equipment data real-time storage and query system which can effectively provide mass equipment data storage and data query and can write historical data.

In order to achieve the purpose, the invention provides the following technical scheme: a real-time storage and query system for mass device data is provided with a database inside, and comprises:

the data writing module is in communication connection with the external equipment and the database and is used for initiating data storage to the database once through configuring a regular and quantitative rule when the equipment data is reported;

the data query module is in communication connection with the database and is used for providing an API (application programming interface), providing paging query according to a time range or an equipment ID (identity) field and performing aggregation query according to an aggregation function and an aggregation period;

and the database switching module is provided with an API (application program interface) and is used for switching the inflixdb, clickhouse and tdengine databases in real time through the API.

As a further improvement of the present invention, the rule of the timing quantification is: the timing is to trigger one-time writing according to a preset duration, the quantification is to judge whether the configured quantity is exceeded or not according to the data rows recorded in the memory, if so, the writing action is not executed, and if not, the writing action is executed.

As a further improvement of the present invention, the paging query mode is to put the current number of pages current and the paging size in the parameters into the corresponding sql according to the paging mode provided by the database, and the aggregation query mode is to convert the aggregation function type into the corresponding function name according to different databases and then generate the corresponding sql.

As a further improvement of the present invention, the data writing module writes data by the following specific steps:

when equipment data is reported, after being processed by equipment service, the equipment data is converted into historical data and then is input into a writing module;

step two, the data writing module stores the received historical data into kafka, and the isc-metadata-service acquires the equipment historical data from the kafka and stores the equipment historical data into a memory queue;

and step three, judging according to a regular and quantitative rule, entering a data storage step when the judgment is passed, and determining which specific database is stored according to the configured database type when the judgment is passed.

As a further improvement of the present invention, the step three further includes a database switching step:

step three, a database switching module reads the connection information of a target database in the configuration, tries connection, and if the connection is successful, the switching is successful; otherwise, switching back to the last configuration and prompting the exception of connecting the target database.

As a further improvement of the present invention, the specific steps of the data query module querying the read data are as follows: step 1, after receiving user query, a data query module finds a corresponding instance through a configured database type according to an incoming condition;

step 2, generating corresponding sql according to the syntax of the corresponding database sql, and delivering the generated sql to a database for execution;

and 3, after the database is executed, packaging the returned results into a unified structure, and returning the unified structure to the user. As a further improvement of the present invention, the specific determination steps of the rule of the timing quantification are as follows:

step two, after the device data are input into the memory queue, judging whether the queue number is greater than the configuration number, if not, directly ending, and if so, ending after storage; and simultaneously, the timer detects whether data exist in the memory queue at regular time, if so, the data are stored and then the data are stored, and if not, the data are directly stored.

The invention has the advantages that through the setting of the data writing module, data can be effectively stored in the database according to the configured timing and quantitative rule, the situation that the writing mode adopted by default in the prior art is one-time data writing, so that frequent network io has a lot of pressure for the database, the overall writing rate is reduced due to the io blockage of the database at the server, the data processing of equipment is influenced, through the setting of the data query module, the situation that the database is interrupted or even crashed due to excessive data query at one time can be effectively avoided, meanwhile, a large data screen page is met, a large display of data is needed, and through the setting of the database switching module, the back-and-forth switching among the three databases of inflixdb, clickhouse and tdengine can be effectively realized.

Drawings

FIG. 1 is a schematic diagram of a process for writing data;

FIG. 2 is a schematic flow chart of the quantitative determination at regular time;

FIGS. 3, 4, 5, 6 are example illustrations of the INFLUXDB database;

FIGS. 7, 8, 9, 10 are exemplary illustrations of the CLICKHOUSE database;

fig. 11, 12, 13, 14, and 15 are exemplary illustrations of the TDENGINE database.

Detailed Description

The invention will be further described in detail with reference to the following examples, which are given in the accompanying drawings.

Referring to fig. 1 to 2, a mass device data real-time storage and query system according to this embodiment has a database built therein, and includes:

the data writing module is in communication connection with the external equipment and the database and is used for initiating data storage to the database once through configuring a regular and quantitative rule when the equipment data is reported;

the data query module is in communication connection with the database and is used for providing an API (application programming interface), providing paging query according to a time range or an equipment ID (identity) field and performing aggregation query according to an aggregation function and an aggregation period;

the database switching module is provided with an API (application programming interface) interface and is used for switching inflixdb, clickhouse and tdengine databases in real time through the API interface, in the process of using the system of the embodiment, data of equipment can be gradually stored into the database through a regular and quantitative rule through the setting of the data writing module, so that the problem that the data processing of the equipment is influenced due to the fact that the writing mode adopted by default in the prior art is one-time data writing is effectively avoided, and thus, frequent network io for the database has a lot of pressure, and the service end can cause the whole writing rate to be reduced due to the fact that io of the database is blocked. With provision for aggregation functions (by default COUNT ("statistics"))

FIRST ("FIRST") LAST ("SUM") SUM ("maximum") MAX ("minimum") COUNT ("statistics") MEAN ("MEDIAN") SPREAD ("difference between maximum and minimum") DISTINCT ("unique value"), perform aggregation query in an aggregation cycle (convert the aggregation function type into corresponding function names according to different databases, and then generate corresponding sql) so as to meet the requirement of a certain data large-screen page and a general display of data, and finally realize real-time switching of inflixdb, clickhouse and tdtengine databases through the setting of a database switching module, so that the device history data can be effectively written into the corresponding databases.

As a specific embodiment of the improvement, the rule of the timing quantification is: the method comprises the steps of triggering write-in once according to preset duration at regular time, quantitatively judging whether the number exceeds the configured number according to data columns recorded in a memory, if so, not executing write-in action, and if not, executing write-in action.

As an improved specific implementation manner, the paging query manner is to put the current number of pages current and the paging size in the transferred parameters into the corresponding sql according to the paging manner provided by the database, the aggregation query manner is to convert the aggregation function type into the corresponding function name according to different databases, and then generate the corresponding sql.

As an improved specific implementation manner, the specific steps of writing data by the data writing module are as follows: when equipment data is reported, after being processed by equipment service, the equipment data is converted into historical data and then is input into a writing module;

step two, the data writing module stores the received historical data into kafka, and the isc-metadata-service acquires the equipment historical data from the kafka and stores the equipment historical data into a memory queue;

and step three, judging according to a regular and quantitative rule, entering a data storage step when the judgment is passed, determining which specific database is stored according to the configured database type when the judgment is passed, and effectively writing data by setting from the step one to the step three.

As a specific implementation manner of the improvement, the step three further includes a database switching step:

step three, a database switching module reads the connection information of a target database in the configuration, tries connection, and if the connection is successful, the switching is successful; otherwise, switching back to the last configuration and prompting the abnormal connection of the target database, the switching of the databases can be realized through the steps, and the abnormal connection of the user database is prompted in time after the switching fails.

As an improved specific implementation manner, the specific steps of the data query module querying the read data are as follows:

step 1, after receiving user query, a data query module finds a corresponding instance through a configured database type according to an incoming condition;

step 2, generating corresponding sql according to the syntax of the corresponding database sql, and delivering the generated sql to a database for execution;

and 3, after the database is executed, packaging the returned results into a unified structure and returning the unified structure to the user.

As a specific implementation manner of improvement, the specific determination steps of the rule of timing quantification are as follows: step two, after the device data are input into the memory queue, judging whether the queue number is greater than the configuration number, if not, directly ending, and if so, ending after storage; meanwhile, the timer detects whether data exist in the memory queue at regular time, if the data exist, the data are stored and then the data are stored, and if the data do not exist, the data are directly written.

In this embodiment, test results for three databases are provided

INFLUXDB

FIG. 3 shows the consumption kafka situation, where continuous consumption accumulation occurs, FIG. 4 shows the IO situation (maximum write is 250.92iops, average write is 148.34iops, maximum read is 319.08iops, average read is 15.76iops, I/O operation time consumption per second is 28.992% on average, I/O write time per time is 170.76ms at most, 83.32ms on average, I/O read time per time is 223.00ms at most, 3.05ms on average), FIG. 5 shows the service resource usage situation (CPU usage 1100%, memory 23G, and time-growing gradually), FIG. 6 shows the message trace of querying a certain device, and interface consumption 1734 ms.

CLICKHOUSE

Fig. 7 shows the consumption kafka situation, no consumption pile-up occurs (about 17:00 is caused by service restart), fig. 8 shows the IO situation (maximum write is 269.70iops, average write is 44.60iops, maximum read is 630.41iops, average read is 5.40iops, average ratio of time consumption of I/O operation per second is 7.066%, maximum time consumption of I/O write is 185.44ms, average is 125.27ms, maximum time consumption of I/O read is 832.00ms, average is 46.30ms), fig. 9 shows the service resource usage situation (CPU usage is 25%, memory is about 1G), fig. 10 shows the message trace of querying a certain device, and interface consumes 200 ms.

TDENGINE

FIG. 11 shows the kafka consumption case with the first half being the close message trace, normal consumption; after the message tracking is started in the second half, the consumption lag is gradually increased, and the consumption is finally stopped, fig. 12 shows service resource usage (CPU usage is 30% and memory 15G or so), fig. 13 shows server resource usage (when the message tracking is not started, CPU 28% and memory 80G; after the message tracking is started, CPU continuously jumps to 66% and memory reaches 100G), fig. 14 shows IO situations (maximum write is 50.82iops, average write is 25.36iops, maximum read is 241.06iops, average read is 15.93iops, the ratio of I/O operation consumption per second is 6.174% on average, maximum time consumption for I/O write is 114.48ms, average time consumption is 73.11ms, maximum time consumption for I/O read is 217.00ms, average time consumption is 19.53ms), fig. 15 shows that the time consumption of about 400ms for querying a certain historical attribute of a certain device, and the message tracking query is overtime.

In summary, the system of this embodiment can effectively implement the fixed-time quantitative writing, the paging query and the dynamic switching of the database by the arrangement of the data writing module, the data query module and the database switching module.

The above description is only a preferred embodiment of the present invention, and the protection scope of the present invention is not limited to the above embodiments, and all technical solutions belonging to the idea of the present invention belong to the protection scope of the present invention. It should be noted that modifications and embellishments within the scope of the invention may occur to those skilled in the art without departing from the principle of the invention, and are considered to be within the scope of the invention.

Claims (7)

1. A mass equipment data real-time storage and query system is provided with a database in a built-in manner, and is characterized in that: the method comprises the following steps:

the data writing module is in communication connection with the external equipment and the database and is used for initiating data storage to the database once through configuring a regular and quantitative rule when the equipment data is reported;

the data query module is in communication connection with the database and is used for providing an API (application programming interface), providing paging query according to a time range or an equipment ID (identity) field and performing aggregation query according to an aggregation function and an aggregation period;

and the database switching module is provided with an API (application programming interface) and is used for switching the inflixdb, clickhouse and tdengine databases in real time through the API.

2. The mass device data real-time storage and query system of claim 1, wherein: the timing and quantifying rules are as follows: the timing is to trigger one-time writing according to a preset duration, the quantification is to judge whether the configured quantity is exceeded or not according to the data rows recorded in the memory, if so, the writing action is not executed, and if not, the writing action is executed.

3. The mass device data real-time storage and query system of claim 1 or 2, wherein: the paging query mode is to put the current page number current and the paging size in the transmitted parameters into the corresponding sql according to the paging mode provided by the database, and the aggregation query mode is to convert the aggregation function type into the corresponding function name according to different databases and then generate the corresponding sql.

4. The mass device data real-time storage and query system of claim 3, wherein: the data writing module writes data in the following specific steps:

when equipment data is reported, after being processed by equipment service, the equipment data is converted into historical data and then is input into a writing module;

step two, the data writing module stores the received historical data into kafka, and the isc-metadata-service acquires the equipment historical data from the kafka and stores the equipment historical data into a memory queue;

and step three, judging according to a regular and quantitative rule, entering a data storage step when the judgment is passed, and determining which specific database is stored according to the configured database type when the judgment is passed.

5. The mass device data real-time storage and query system of claim 4, wherein: the third step also comprises a database switching step:

step three, a database switching module reads the connection information of a target database in the configuration, tries connection, and if the connection is successful, the switching is successful; otherwise, switching back to the last configuration and prompting that the target database is connected abnormally.

6. The system for real-time storage and query of mass device data according to claim 1 or 2, wherein: the data query module comprises the following specific steps of querying and reading data:

step 1, after receiving user query, a data query module finds a corresponding instance through a configured database type according to an incoming condition;

step 2, generating corresponding sql according to the syntax of the corresponding database sql, and delivering the generated sql to a database for execution;

and 3, after the database is executed, packaging the returned results into a unified structure, and returning the unified structure to the user.

7. The mass device data real-time storage and query system of claim 2, wherein: the specific determination steps of the timing and quantitative rule are as follows:

step two, after the device data are input into the memory queue, judging whether the queue number is greater than the configuration number, if not, directly ending, and if so, ending after storage; and simultaneously, the timer detects whether data exist in the memory queue at regular time, if so, the data are stored and then the data are stored, and if not, the data are directly stored.

Images