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

Real-time storage and query system for mass device data Download PDF

Info

Publication number
CN114817334A
CN114817334A CN202210555363.6A CN202210555363A CN114817334A CN 114817334 A CN114817334 A CN 114817334A CN 202210555363 A CN202210555363 A CN 202210555363A CN 114817334 A CN114817334 A CN 114817334A
Authority
CN
China
Prior art keywords
data
database
query
equipment
real
Prior art date
Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
Pending
Application number
CN202210555363.6A
Other languages
Chinese (zh)
Inventor
钱克成
秦钢
宋杨
陈波
叶运生
周垤飞
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
Hangzhou Diji Intelligent Technology Co ltd
Original Assignee
Hangzhou Diji Intelligent Technology Co ltd
Priority date (The priority date is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the date listed.)
Filing date
Publication date
Application filed by Hangzhou Diji Intelligent Technology Co ltd filed Critical Hangzhou Diji Intelligent Technology Co ltd
Priority to CN202210555363.6A priority Critical patent/CN114817334A/en
Publication of CN114817334A publication Critical patent/CN114817334A/en
Pending legal-status Critical Current

Links

Images

Classifications

    • GPHYSICS
    • G06COMPUTING; CALCULATING OR COUNTING
    • G06FELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/24Querying
    • G06F16/245Query processing
    • G06F16/2455Query execution
    • GPHYSICS
    • G06COMPUTING; CALCULATING OR COUNTING
    • G06FELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/25Integrating or interfacing systems involving database management systems

Landscapes

  • Engineering & Computer Science (AREA)
  • Theoretical Computer Science (AREA)
  • Databases & Information Systems (AREA)
  • Data Mining & Analysis (AREA)
  • Physics & Mathematics (AREA)
  • General Engineering & Computer Science (AREA)
  • General Physics & Mathematics (AREA)
  • Computational Linguistics (AREA)
  • Information Retrieval, Db Structures And Fs Structures Therefor (AREA)

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.
CN202210555363.6A 2022-05-20 2022-05-20 Real-time storage and query system for mass device data Pending CN114817334A (en)

Priority Applications (1)

Application Number Priority Date Filing Date Title
CN202210555363.6A CN114817334A (en) 2022-05-20 2022-05-20 Real-time storage and query system for mass device data

Applications Claiming Priority (1)

Application Number Priority Date Filing Date Title
CN202210555363.6A CN114817334A (en) 2022-05-20 2022-05-20 Real-time storage and query system for mass device data

Publications (1)

Publication Number Publication Date
CN114817334A true CN114817334A (en) 2022-07-29

Family

ID=82517957

Family Applications (1)

Application Number Title Priority Date Filing Date
CN202210555363.6A Pending CN114817334A (en) 2022-05-20 2022-05-20 Real-time storage and query system for mass device data

Country Status (1)

Country Link
CN (1) CN114817334A (en)

Cited By (1)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN115048390A (en) * 2022-08-16 2022-09-13 国能日新科技股份有限公司 Data storage method and device based on influxdb

Cited By (2)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN115048390A (en) * 2022-08-16 2022-09-13 国能日新科技股份有限公司 Data storage method and device based on influxdb
CN115048390B (en) * 2022-08-16 2022-11-01 国能日新科技股份有限公司 Data storage method and device based on influxdb

Similar Documents

Publication Publication Date Title
CN102117240B (en) A kind of method and device obtaining progress blocking information
WO2018121404A1 (en) Method and device for timeout monitoring
CN109981737B (en) Network request processing method, device, terminal and storage medium
CN108874324B (en) Access request processing method, device, equipment and readable storage medium
CN114817334A (en) Real-time storage and query system for mass device data
CN107908518A (en) Database performance monitoring method, device, storage medium and computer equipment
CN112527600A (en) Monitoring log processing method, device, equipment and storage medium
CN115168088A (en) Method and device for repairing uncorrectable errors of memory
EP3422186B1 (en) Method, device and system for preventing memory data loss
CN112463550A (en) Remote monitoring method, device and equipment for terminal equipment state
CN114637656B (en) Redis-based monitoring method and device, storage medium and equipment
CN111405077A (en) Domain name switching method and device, computer readable storage medium and computer equipment
CN115617518A (en) Thread management method and device, electronic equipment and storage medium
CN116361106A (en) Log processing method and device, electronic equipment and storage medium
CN115119081A (en) Data reading method and device, front-end equipment and storage medium
CN113687942B (en) Detection method and device and electronic equipment
CN115396341A (en) Service stability evaluation method and device, storage medium and electronic device
CN113312234B (en) Health detection optimization method and terminal
CN106919488B (en) Method and device for determining response state of application program
CN111090627B (en) Log storage method and device based on pooling, computer equipment and storage medium
CN113536034A (en) Data writing method and data reading method based on bloom filter
CN109582244B (en) Metadata disk-dropping method, device, terminal and computer-readable storage medium
CN112131276A (en) Data statistics method, electronic equipment and readable storage medium
CN116719621B (en) Data write-back method, device, equipment and medium for mass tasks
CN112948214B (en) Software overload warning method and device

Legal Events

Date Code Title Description
PB01 Publication
PB01 Publication
SE01 Entry into force of request for substantive examination
SE01 Entry into force of request for substantive examination