Transforming timeseries and non-relational data to relational for complex and analytical query processing

• US 9,529,875 B2
• Filed: 01/13/2014
• Issued: 12/27/2016
• Est. Priority Date: 01/13/2014
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Ho, Frederick C., Brown, Kevin, Murthy, Raghupathi K., Ostner, Karl
• Primary Examiner(s): Pham, Hung Q
• Application Number:
  US14/153,904
• Publication Number:
  US 20150199413A1
• Time in Patent Office: 1,079 Days
• Field of Search: None
• US Class Current: 1/1
• CPC Class Codes: G06F 16/22 Indexing; Data structures therefor; Storage structures
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: G06F 16/24554 Unary operations; Data partitioning operations
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP

Abstract

A system for transforming time series data into data that is accessible by a data warehouse identifies a data table comprising the time series data. The system creates a virtual view of the data table where the time series data is represented as at least one standard relational table in the virtual view, where the virtual view is presented as a virtual table. The system partitions the virtual table into a plurality of virtual partitions according to a time interval. The virtual table is partitioned across a data time range, where the data time range comprises at least one time interval, and where each of the plurality of virtual partitions has a respective partition time range that spans the time interval. The virtual partitions are created to optimize loading of the data into the data warehouse by incrementally refreshing the data according to the respective partition time range.

Claims

1. A method for transforming time series data into data that is accessible by a data warehouse, implemented by a computing processor, the method comprising:

identifying, by the computing processor, a time series database, wherein the time series database comprises a column representing data collected from at least one source, wherein the column contains at least one array for storing the collected data, wherein the at least one array in a row grows larger in the time series database as new data is collected instead of adding a new row as the new data is collected, wherein the collected data is time series data;

creating, by the computing processor, a virtual view of the time series data table by transforming the collected data that is inaccessible by the data warehouse into time series data that is accessible by the data warehouse by representing the collected data from the time series database as at least one standard relational table in the virtual view, the virtual view presented as a virtual table, the virtual view stored the at least one standard relational table as an in-memory storage structure without any intermediate storage, wherein the time series data is not stored in the at least one standard relational table;

grouping, by the computing processor, the time series data in the virtual view into a plurality of virtual partitions according to time intervals, wherein the time intervals are defined by a user, wherein each of the plurality of virtual partitions is across a data time range, wherein the data time range comprises at least one time interval, wherein each of the plurality of virtual partitions has a respective partition time range that spans the at least one time interval, the virtual partitions created to optimize loading of the collected new data into the data warehouse by incrementally refreshing the collected new data according to a respective partition time range associated with each of the plurality of virtual partitions, wherein the virtual partitions allow the time series data in the virtual view to be incrementally refreshed according to the at least one time interval whereas the time series data in the virtual view otherwise could not be incrementally refreshed; and

displaying time series data that corresponds to a corresponding time interval by displaying a subset of the plurality of virtual partitions that span the corresponding time interval.

2. The method of claim 1 comprising:

providing, by the computing processor, the plurality of virtual partitions to the data warehouse for analysis of the data via a data accelerator, wherein selection of the plurality of virtual partitions, based on the data time range, optimizes analysis of the data.

3. The method of claim 1 wherein grouping, by the computing processor, the time series data in the virtual view into the plurality of virtual partitions according to the time interval comprises:

incrementally refreshing the data by extracting new time series data from the data table, according to the time interval;

creating a new virtual partition that spans the time interval, the new virtual partition having the respective partition time range; and

adding the new virtual partition to the plurality of virtual partitions.

4. The method of claim 1 wherein grouping, by the computing processor, the time series data in the virtual view into the plurality of virtual partitions according to the time interval comprises:

incrementally refreshing the data by identifying the data time range as representing a chosen view of the data, wherein the chosen view comprises a subset of the plurality of virtual partitions, wherein each of the subset of the plurality of virtual partitions has a respective partition time range that is within the data time range.

5. The method of claim 4 comprising:

detecting at least one virtual partition, within the subset of the plurality of virtual partitions, having the respective partition time range outside of the data time range; and

removing the at least one virtual partition from the subset of the plurality of virtual partitions, wherein the at least one virtual partition is no longer represented within the chosen view.

6. The method of claim 4 comprising:

detecting at least one virtual partition having the respective partition time range within the data time range, wherein the at least one virtual partition is not in the subset of the plurality of virtual partitions; and

adding the at least one virtual partition to the subset of the plurality of virtual partitions, wherein the at least one virtual partition is now represented within the chosen view.

7. The method of claim 1 wherein grouping, by the computing processor, the time series data in the virtual view into the plurality of virtual partitions according to the time interval comprises:

creating a partitioning calendar;

associating the time interval to the partitioning calendar;

assigning the partitioning calendar to the virtual table; and

partitioning the virtual table, using the partitioning calendar, wherein each partition spans the time interval.

8. The method of claim 1 wherein grouping, by the computing processor, the time series data in the virtual view into the plurality of virtual partitions according to the time interval comprises:

defining a time window selected to optimize an amount of relevant data that is loaded into the data warehouse;

associating the time interval to the time window; and

applying the time window to the virtual table to partition the virtual table into the plurality of virtual partitions according to the time interval.

9. A computer program product for transforming time series data into data that is accessible by a data warehouse, the computer program product comprising:

a computer readable storage medium having computer readable program code embodied therewith, the program code executable by a processor to;

identify, by the computing processor, a time series database, wherein the time series database comprises a column representing data collected from at least one source, wherein the column contains at least one array for storing the collected data, wherein the at least one array in a row grows larger in the time series database as new data is collected instead of adding a new row as the new data is collected, wherein the collected data is time series data;

create, by the computing processor, a virtual view of the time series data table by transforming the collected data that is inaccessible by the data warehouse into time series data that is accessible by the data warehouse, by representing the collected data from the time series database as at least one standard relational table in the virtual view, the virtual view presented as a virtual table, the virtual view stored as an in-memory storage structure without any intermediate storage, wherein the time series data is not stored in the at least one standard relational table;

group, by the computing processor, the time series data in the virtual view into a plurality of virtual partitions according to time intervals, wherein the time intervals are defined by a user, wherein each of the plurality of virtual partitions is across a data time range, wherein the data time range comprises at least one time interval, wherein each of the plurality of virtual partitions has a respective partition time range that spans the at least one time interval, the virtual partitions created to optimize loading of the collected new data into the data warehouse by incrementally refreshing the collected new data according to a respective partition time range associated with each of the plurality of virtual partitions, wherein the virtual partitions allow the time series data in the virtual view to be incrementally refreshed according to the at least one time interval whereas the time series data in the virtual view otherwise could not be incrementally refreshed; and

displaying time series data that corresponds to a corresponding time interval by displaying a subset of the plurality of virtual partitions that spans the corresponding time interval.

10. The computer program product of claim 9 wherein the computer readable program code is further configured to:

provide, by the computing processor, the plurality of virtual partitions to the data warehouse for analysis of the data via a data accelerator, wherein selection of the plurality of virtual partitions, based on the data time range, optimizes analysis of the data.

11. The computer program product of claim 9 wherein the computer readable program code configured to group, by the computing processor, the time series data in the virtual view into the plurality of virtual partitions according to the time interval is further configured to:

incrementally refresh the data by extracting new time series data from the data table, according to the time interval;

create a new virtual partition that spans the time interval, the new virtual partition having the respective partition time range; and

add the new virtual partition to the plurality of virtual partitions.

12. The computer program product of claim 9 wherein the computer readable program code configured to group, by the computing processor, the time series data in the virtual view into the plurality of virtual partitions according to the time interval is further configured to:

incrementally refresh the data by identifying the data time range as representing a chosen view of the data, wherein the chosen view comprises a subset of the plurality of virtual partitions, wherein each of the subset of the plurality of virtual partitions has a respective partition time range that is within the data time range.

13. The computer program product of claim 12 wherein the computer readable program code is further configured to:

detect at least one virtual partition, within the subset of the plurality of virtual partitions, having the respective partition time range outside of the data time range; and

remove the at least one virtual partition from the subset of the plurality of virtual partitions, wherein the at least one virtual partition is no longer represented within the chosen view.

14. The computer program product of claim 12 wherein the computer readable program code is further configured to:

detect at least one virtual partition having the respective partition time range within the data time range, wherein the at least one virtual partition is not in the subset of the plurality of virtual partitions; and

add the at least one virtual partition to the subset of the plurality of virtual partitions, wherein the at least one virtual partition is now represented within the chosen view.

15. The computer program product of claim 9 wherein the computer readable program code configured to group, by the computing processor, the time series data in the virtual view into the plurality of virtual partitions according to the time interval is further configured to:

create a partitioning calendar;

associate the time interval to the partitioning calendar;

assign the partitioning calendar to the virtual table; and

partition the virtual table, using the partitioning calendar, wherein each partition spans the time interval.

16. The computer program product of claim 9 wherein the computer readable program code configured to group, by the computing processor, the time series data in the virtual view into the plurality of virtual partitions according to the time interval is further configured to:

define a time window selected to optimize an amount of relevant data that is loaded into the data warehouse;

associate the time interval to the time window; and

apply the time window to the virtual table to partition the virtual table into the plurality of virtual partitions according to the time interval.

17. A system comprising:

a processor; and

a computer readable storage medium operationally coupled to the processor, the computer readable storage medium having computer readable program code embodied therewith to be executed by the processor, the computer readable program code configured to;

identify, by the computing processor, a time series database, wherein the time series database comprises a column representing data collected from at least one source, wherein the column contains at least one array for storing the collected data, wherein the at least one array in a row grows larger in the time series database as new data is collected instead of adding a new row as the new data is collected, wherein the collected data is time series data;

create, by the computing processor, a virtual view of the time series data table by transforming the collected data that is inaccessible by the data warehouse into time series data that is accessible by the data warehouse, by representing the collected data from the time series database as at least one standard relational table in the virtual view, the virtual view presented as a virtual table, the virtual view stored as an in-memory storage structure without any intermediate storage, wherein the time series data is not stored in the at least one standard relational table;

group, by the computing processor, the time series data in the virtual view into a plurality of virtual partitions according to time intervals, wherein the time intervals are defined by a user, wherein each of the plurality of virtual partitions is across a data time range, wherein the data time range comprises at least one time interval, wherein each of the plurality of virtual partitions has a respective partition time range that spans the at least one time interval, the virtual partitions created to optimize loading of the collected new data into the data warehouse by incrementally refreshing the collected new data according to a respective partition time range associated with each of the plurality of virtual partitions, wherein the virtual partitions allow the time series data in the virtual view to be incrementally refreshed according to the at least one time interval whereas the time series data in the virtual view otherwise could not be incrementally refreshed; and

displaying time series data that corresponds to a corresponding time interval by displaying a subset of the plurality of virtual partitions that spans the corresponding time interval.

18. The system of claim 17 wherein the computer readable program code is further configured to:

provide, by the computing processor, the plurality of virtual partitions to the data warehouse for analysis of the data via a data accelerator, wherein selection of the plurality of virtual partitions, based on the data time range, optimizes analysis of the data.

19. The system of claim 17 wherein the computer readable program code configured to group, by the computing processor, the time series data in the virtual view into the plurality of virtual partitions according to the time interval is further configured to:

incrementally refresh the data by extracting new time series data from the data table, according to the time interval;

create a new virtual partition that spans the time interval, the new virtual partition having the respective partition time range; and

add the new virtual partition to the plurality of virtual partitions.

20. The system of claim 17 wherein the computer readable program code configured to group, by the computing processor, the time series data in the virtual view into the plurality of virtual partitions according to the time interval is further configured to:

incrementally refresh the data by identifying the data time range as representing a chosen view of the data, wherein the chosen view comprises a subset of the plurality of virtual partitions, wherein each of the subset of the plurality of virtual partitions has a respective partition time range that is within the data time range.