PROCESSING MESSAGES OF A PLURALITY OF DEVICES

• US 20180218019A1
• Filed: 01/30/2017
• Published: 08/02/2018
• Est. Priority Date: 01/30/2017
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Libow, Eric B., Lorch, Markus, Oberhofer, Martin A., Steinbach, Torsten
• Granted Patent: US 10,762,072 B2
• Time in Patent Office: Days
• CPC Class Codes: G06F 16/2282 Tablespace storage structures; Management thereof
• CPC Class Codes: G06F 16/86 Mapping to a database
• CPC Class Codes: H04L 12/2823 Reporting information sensed by appliance or service execution status of appliance services in a home automation network device-related reporting H04L43/065; arrangements in telecontrol or telemetry systems for selectively calling a substation from a main station, in which substation desired apparatus is selected for applying a control signal thereto or for obtaining measured values therefrom H04Q9/00
• CPC Class Codes: H04L 51/00 User-to-user messaging in packet-switching networks, transmitted according to store-and-forward or real-time protocols, e.g. e-mail
• CPC Class Codes: H04L 51/48 Message addressing, e.g. address format or anonymous messages, aliases

Abstract

An embodiment of the invention pertains to processing messages received from a set of devices. A mapping of message fields to fact table columns is stored in a mapping table of a relational database. The method comprises, for each received message, determining all fields of the received message. If it is determined that the mapping table does not assign a fact table column to each of the determined fields of the message, a new mapping between message fields to respective fact table is automatically stored in the mapping table. A mapping is identified in the mapping table that assigns each of the determined fields of the message to a respective one of the columns of the fact table. The field values of the message are stored in the fact table in accordance with the identified mapping.

Claims

1. A computer-implemented method for processing messages received from a set of devices, the method comprising:

accessing a mapping table and a fact table stored in a relational database, the mapping table comprising mappings, each mapping assigning message fields to columns of the fact table; and

for each of the received messages;

determining all fields of the received message;

analyzing the mapping table to determine whether the mapping table comprises a mapping that assigns each of the determined fields of the message to respective ones of the columns of the fact table;

in response to determining that the mapping table does not comprise the mapping that assigns each of the determined fields of the message to the respective ones of the columns of the fact table, automatically generating a new mapping, the new mapping assigning each of the determined fields of the message to the respective ones of the columns of the fact table, and storing the new mapping in the mapping table;

identifying in the mapping table an identified mapping that assigns each of the determined fields of the message to the respective one of the columns of the fact table; and

storing field values of the message in the fact table in accordance with the assignment of fields to fact table columns of the identified mapping.

2. The computer-implemented method of claim 1, wherein analyzing the mapping table to determine whether the mapping table comprises the mapping that assigns each of the determined fields of the message to a respective one of the columns of the fact table comprises:

determining that the fact table comprises fewer columns than a number of fields comprised in the message; and

automatically, in response to the determination that the fact table comprises fewer columns than the number of fields comprised in the message, adding at least as many columns to the fact table as required for storing each field value of the received message in a respective column of the fact table.

3. The computer-implemented method of claim 1, wherein each of the fact table columns have an assigned column data type, and wherein analyzing the mapping table to determine whether the mapping table comprises the mapping that assigns each of the determined fields of the message to the respective ones of the columns of the fact table comprises:

determining, for each of the fields of the received message, a data type to be used for storing the respective field values in the relational database;

determining that for at least one of the fields of the received message the fact table lacks a column having the data type that was determined to be used for storing the field value of the message field; and

automatically, in response to the determination that for at least one of the fields of the received message the fact table lacks the column having the data type that was determined to be used for storing the field value of the message field, adding at least as many columns having the data type to the fact table as required for storing each field value of the received message in the respective ones of the columns of the fact table having the data type determined for the field of the field value.

4. The computer-implemented method of claim 1, wherein the identified mapping is the new mapping.

5. The computer-implemented method of claim 1, wherein the set of devices comprises more than one hundred devices.

6. The computer-implemented method of claim 1, wherein the set of devices comprises devices of different device types, and wherein messages of devices of different device types comprises one or more different fields.

7. The computer-implemented method of claim 1, wherein each of the devices is configured to automatically and repeatedly submit messages directly or via a messaging system to a database management system that manages the relational database.

8. The computer-implemented method of claim 1, wherein the set of devices is a set of sensors integrated into a vehicle, a building, a machine, or a production line.

9. The computer-implemented method of claim 1, wherein the received messages have a human-readable format and comprise data in the form of field-name-field-value pairs.

10. The computer-implemented method of claim 9, further comprising:

computing a schema signature from all field-names of the fields of the received message, wherein;

the analyzing the mapping table to determine whether the mapping table comprises the mapping that assigns each of the determined fields of the message to respective ones of the columns of the fact table comprises checking whether the mapping table comprises a mapping having the computed signature;

the mapping is determined to lack the mapping that assigns each of the determined fields of the message to the respective ones of the columns of the fact table if the mapping lacks the computed signature; and

the new mapping comprising the computed schema signature.

11. The computer-implemented method of claim 1, further comprising, for each of the devices:

analyzing a totality of the received messages to identify one or more device-metadata columns, wherein the one or more device-metadata columns are one or more of the fact table columns whose field values are identical for the totality of the received messages;

storing a copy of all of the one or more device-metadata columns in a device-metadata table; and

updating the mapping table by replacing, in the mappings assigning message fields to columns of the fact table, all metadata column names with respective column names of the device-metadata table.

12. The computer-implemented method of claim 1, further comprising:

in response to determining that the mapping table does not comprise the mapping that assigns each of the determined fields of the message to the respective ones of the columns of the fact table, automatically generating a new view in the relational database, the new view being configured to retrieve field values for a totality of fields in the received message from the fact table by selectively reading data values from the fact table columns that are mapped in the new mapping to the determined fields of the received message.

13. The computer-implemented method of claim 1, further comprising:

receiving a request from a client system, the request demanding retrieval of data of a plurality of messages of a device of the set devices;

analyzing the mapping table to identify the fact table columns that are assigned to the message fields of the plurality of messages;

transforming the request into a database query adapted to selectively retrieve data values provided by the plurality of messages, the database query being configured to selectively access the identified fact table columns; and

triggering an execution of the database query in the relational database.

14. The computer-implemented method of claim 1, further comprising:

storing the received message in a software cache as a most recent message received from a device of the set of devices;

receiving a further message from the device;

comparing the received message and the further message to determine whether the field values of respective fields of all message fields of the received message and the further message are identical;

in response to determining that the field values are identical, storing a current time as a time stamp associated with a device-ID of the device in a time stamp table instead of storing the field values of the further message in the fact table; and

in response to determining that the field values of the respective fields of at least one of the message fields are not identical, storing the further message in the software cache as the most recent message received from the device of the set of the devices.

15. The computer-implemented method of claim 14, further comprising:

in response to receiving a request for data of all messages of the device of the set of devices over a time period, triggering the execution of a structured query language query that combines information in the time stamp table and the fact table to reconstruct the field values of all messages received during the time period; and

returning the reconstructed field values.

16. The computer-implemented method of claim 1, wherein the method is performed by a message processing application adapted to receive the messages from the devices and to create at least the fact table and the mapping table and/or to modify the structure and content of at least the fact table and the mapping table.

17. The computer-implemented method of claim 16, wherein the message processing application is hosted on a messaging computer system that is coupled to a client and the relational database via a network.

18. The computer-implemented method of claim 1, further comprising:

creating, via a database management system that manages the relational database, the fact table and the mapping table, and storing the fact table and mapping table in the relational database.

19. The computer-implemented method of claim 1, wherein each of the fact table columns is assigned a column data type, and the storing of the field values comprising:

using, by a database management system that manages the relational database, the data type of the fact table column for storing the field value to be stored in a column in a dense data format.

20. The computer-implemented method of claim 1, wherein each of the fact table columns is assigned a column data type, and the method further comprises:

processing, by a database management system that manages the relational database, a database on the field values of a plurality of messages stored in the fact table, the processing comprising loading a continuous sequential list of INTEGER field values stored in a FLOAT fact table column or FLOAT field values stored in a FLOAT fact table column into a central processing unit register; and

performing a same type of central processing unit operation on each of the field values in the list without any intermitting central processing unit operation.

21. A tangible non-volatile storage medium comprising computer-readable instructions which, when executed by a processor, cause the processor to perform a method for processing messages received from a set of devices, the method comprising:

accessing a mapping table and a fact table in a relational database, the mapping table comprising mappings, each mapping assigning message fields to columns of the fact table; and

for each of the received messages;

determining all fields of the received message;

analyzing the mapping table to determine whether the mapping table comprises a mapping that assigns each of the determined fields of the message to respective ones of the columns of the fact table;

in response to determining that the mapping table does not comprise the mapping that assigns each of the determined fields of the message to the respective ones of the columns of the fact table, automatically generating a new mapping, the new mapping assigning each of the determined fields of the message to the respective ones of the columns of the fact table, and storing the new mapping in the mapping table;

identifying in the mapping table an identified mapping that assigns each of the determined fields of the message to the respective one of the columns of the fact table; and

storing the field values of the message in the fact table in accordance with the assignment of fields to fact table columns of the identified mapping.

22. A system comprising a computer system, the computer system comprising a message processing application, the message processing application being operatively coupled to a relational database and to a set of devices via a network, the message processing application being configured for:

accessing a mapping table and a fact table in the relational database, the mapping table comprising mappings, each mapping assigning message fields to columns of the fact table; and

for each of the received messages;

determining all fields of the received message;

analyzing the mapping table to determine whether the mapping table comprises a mapping that assigns each of the determined fields of the message to respective ones of the columns of the fact table;

in response to determining that the mapping table does not comprise the mapping that assigns each of the determined fields of the message to the respective ones of the columns of the fact table, automatically generating a new mapping, the new mapping assigning each of the determined fields of the message to the respective ones of the columns of the fact table, and storing the new mapping in the mapping table;

identifying in the mapping table an identified mapping that assigns each of the determined fields of the message to the respective one of the columns of the fact table; and

storing the field values of the message in the fact table in accordance with the assignment of fields to fact table columns of the identified mapping.