Database management method, computer, sensor network system and time-series data management method
In a system manages a plurality of pieces of sensor information in a plant, or the like, it can be reducing an amount of data stored in a database and easily a processing for searching a place of an anomaly and an anomaly cause. A database management method for use in a computer for managing a database, the database management method including: a step of analyzing a query; a step of generating a first inquiry for searching the database for compressed data; a step of generating a second inquiry for executing a search of time-series data; a step of extracting given data from the obtained time-series data, based on a response result of the second inquiry; and a step of generating an output result by extracting data to be output to a client computer from the given data.
1. A database management method executed in a computer for managing a database,
the computer having a processor and a memory connected to the processor,
the database storing a plurality of pieces of compressed data which are compressed based on a given condition,
the database management method including:
a first step of analyzing, by the computer, a query for the database which is received from a client computer coupled to the computer;
a second step of generating, by the computer, a first inquiry for searching the database for at least one of the plurality of pieces of compressed data, based on a result of the analysis of the received query;
a third step of generating, by the computer, a second inquiry for executing a search of a plurality of pieces of time-series data obtained from the at least one of the plurality of pieces of compressed data, which is a response result of the first inquiry, based on the result of the analysis of the received query;
a fourth step of issuing, by the computer, the first inquiry to the database and obtaining the at least one of the plurality of pieces of compressed data from the database as the response result of the first inquiry;
a fifth step of obtaining, by the computer, the plurality of pieces of time-series data by decompressing the at least one of the plurality of pieces of compressed data which has been obtained as the response result of the first inquiry;
a sixth step of executing, by the computer, the second inquiry for the obtained plurality of pieces of time-series data;
a seventh step of extracting, by the computer, a plurality of pieces of any data from the obtained plurality of pieces of time-series data, based on a response result of the second inquiry; and
an eighth step of generating, by the computer, an output result by extracting data to be output to the client computer from the plurality of pieces of any data which has been extracted in the seventh step.
2. The database management method according to claim 1 ,
wherein each of the plurality of pieces of compressed data is compressed data obtained by compressing the plurality of pieces of time-series data into a given time unit,
wherein the second inquiry is an inquiry for searching a first data range where a given threshold condition is satisfied, from the plurality of pieces of time-series data,
wherein, in the sixth step, the computer gives flags to the plurality of pieces of time-series data included in the first data range, and
wherein, in the seventh step, the computer extracts a second data range which includes the first data range, based on the flags given to the plurality of pieces of time-series data.
3. The database management method according to claim 2 ,
wherein the second data range includes:
a third data range for extracting the plurality of pieces of time-series data that precedes, by a first length of time, an oldest piece of the time-series data within the first data range; and
a fourth data range for extracting the plurality of pieces of time-series data that follows, at a gap of a second length of time, a latest piece of the time-series data within the first data range.
4. The database management method according to claim 2 ,
wherein the eighth step includes a step of extracting the plurality of pieces of time-series data at a given time interval out of the plurality of pieces of time-series data within the second data range.
5. The database management method according to claim 1 ,
wherein the database stores the plurality of pieces of compressed data to which feature information is added, the feature information is used as a search key,
wherein the first inquiry includes the feature information, and
wherein the first inquiry is an inquiry for searching for the at least one of the plurality of pieces of compressed data to which the feature information, that matches the feature information included in the first inquiry, is added.
6. The database management method according to claim 5 ,
wherein the feature information is at least one of a maximum value, a minimum value, an average value, a variance value, and a specific point in a frequency spectrum among the plurality of pieces of time-series data prior to compression for obtaining the plurality of pieces of compressed data.
7. The database management method according to claim 1 ,
wherein the first inquiry is an SQL for searching the database for the at least one of the plurality of pieces of compressed data, and
wherein the second inquiry is a CQL for extracting the plurality of pieces of any data from the plurality of pieces of time-series data.
8. A computer for managing a database, comprising:
a processor, and
a memory connected to the processor,
the database storing a plurality of pieces of compressed data which are compressed based on a given condition,
the computer being configured to:
analyze a query to the database which is received from a client computer coupled to the computer;
generate a first inquiry for searching the database for at least one of the plurality of pieces of compressed data, based on a result of the analysis of the received query;
generate a second inquiry for executing a search of a plurality of pieces of time-series data obtained from the at least one of the plurality of pieces of compressed data, which is a response result of the first inquiry, based on the result of the analysis of the received query;
issue the first inquiry to the database and obtain the at least one of the plurality of pieces of compressed data from the database as the response result of the first inquiry;
obtain the plurality of pieces of time-series data by decompressing the at least one of the plurality of pieces of compressed data which has been obtained as the response result of the first inquiry;
execute the second inquiry for the obtained plurality of pieces of time-series data;
extract a plurality of pieces of any data from the obtained plurality of pieces of time-series data, based on a response result of the second inquiry; and
generate an output result by extracting data to be output to the client computer from the extracted plurality of pieces of any data.
9. The computer according to claim 8 ,
wherein each of the plurality of pieces of compressed data is compressed data obtained by compressing the plurality of pieces of time-series data into a given time unit,
wherein the second inquiry is an inquiry for searching a first data range where a given threshold condition is satisfied, from the plurality of pieces of time-series data, and
wherein the computer is further configured to:
give flags to the plurality of pieces of time-series data included in the first data range, in a case of executing the second inquiry for the obtained plurality of pieces of time-series data; and
extract a second data range which includes the first data range, based on the flags given to the plurality of pieces of time-series data, in a case of extracting the plurality of pieces of any data from the obtained plurality of pieces of time-series data based on the response result of the second inquiry.
10. The computer according to claim 9 ,
wherein the second data range includes:
a third data range for extracting the plurality of pieces of time-series data that precedes, by a first length of time, an oldest piece of the time-series data within the first data range; and
a fourth data range for extracting the plurality of pieces of time-series data that follows, at a gap of a second length of time, a latest piece of the time-series data within the first data range.
11. The computer according to claim 8 ,
wherein the database stores the plurality of pieces of compressed data to which feature information is added, the feature information is used as a search key,
wherein the first inquiry includes the feature information, and,
wherein the first inquiry is an inquiry for searching for the at least one of the plurality of pieces of compressed data to which the feature information, that matches the feature information included in the first inquiry, is added.
12. The computer according to claim 11 ,
wherein the feature information is at least one of a maximum value, a minimum value, an average value, and a variance value among the plurality of pieces of time-series data prior to compression for obtaining the plurality of pieces of compressed data.
13. The computer according to claim 8
wherein the first inquiry is an SQL for searching the database for the at least one of the plurality of pieces of compressed data, and
wherein the second inquiry is a CQL for extracting the plurality of pieces of any data from the plurality of pieces of time-series data.
14. A sensor network system comprising:
a sensor for measuring a physical quantity of a sensing target;
a computer for collecting, as time-series data, the physical quantity measured by the sensor; and
a management server for storing the time-series data from the computer in a database,
the computer having a first processor and a first memory connected to the first processor,
the management server having a second processor and a second memory connected to the second processor,
the computer and the management server being coupled to each other via a network,
the database storing a plurality of pieces of compressed data which are obtained by compressing a plurality of pieces of time-series data based on a given condition,
the management server being configured to:
analyze a query to the database which is received from a client computer coupled to the management server via the network;
generate a first inquiry for searching the database for at least one of the plurality of pieces of compressed data, based on a result of the analysis of the received query;
generate a second inquiry for executing a search of the plurality of pieces of time-series data obtained from the at least one of the plurality of pieces of compressed data which is a response result of the first inquiry, based on the result of the analysis of the received query;
issue the first inquiry to the database and obtain the at least one of the plurality of pieces of compressed data from the database as the response result of the first inquiry;
obtain the plurality of pieces of time-series data by decompressing the at least one of the plurality of pieces of compressed data which has been obtained as the response result of the first inquiry;
execute the second inquiry for the obtained plurality of pieces of time-series data;
extract a plurality of pieces of any data from the obtained plurality of pieces of time-series data, based on a response result of the second inquiry; and
generate an output result by extracting data to be output to the client computer from the extracted plurality of pieces of any data.
15. The sensor network system according to claim 14 ,
wherein each of the plurality of pieces of compressed data is compressed data obtained by compressing the plurality of pieces of time-series data into a given time unit,
wherein the second inquiry is an inquiry for searching a first data range where a given threshold condition is satisfied, from the plurality of pieces of time-series data, and
wherein the management server is further configured to:
give flags to the plurality of pieces of time-series data included in the first data range, in a case of executing the second inquiry for the obtained plurality of pieces of time-series data; and
extract a second data range which includes the first data range, based on the flags given to the plurality of pieces of time-series data, in a case of extracting the plurality of pieces of any data from the obtained plurality of pieces of time-series data based on the response result of the second inquiry.
16. The sensor network system according to claim 15 ,
wherein the second data range includes:
a third data range for extracting the plurality of pieces of time-series data that precedes, by a first length of time, an oldest piece of the time-series data within the first data range; and
a fourth data range for extracting the plurality of pieces of time-series data that follows, at a gap of a second length of time, a latest piece of the time-series data within the first data range.
17. The sensor network system according to claim 14 ,
wherein the database stores the plurality of pieces of compressed data to which feature information is added, the feature information is used as a search key,
wherein the first inquiry includes the feature information, and
wherein the first inquiry is an inquiry for searching for the at least one of the plurality of compressed data to which the feature information, that matches the feature information included in the first inquiry, is added.