Methods, systems, and computer program products for database table aggregation

• US 8,429,132 B2
• Filed: 08/27/2012
• Issued: 04/23/2013
• Est. Priority Date: 12/18/2008
• Status: Active Grant

• Current Assignee: LYFT Incorporated
• Original Assignee: AT&T Intellectual Property I LP (AT&T, Inc.)
• Inventors: Zaifman, Arthur, Rao, Saiprakash, Brown, Philip E.
• Primary Examiner(s): Vital, Pierre
• Assistant Examiner(s): Kim, Taelor
• Application Number:
  US13/595,573
• Publication Number:
  US 20120323974A1
• Time in Patent Office: 239 Days
• Field of Search: None
• US Class Current: 707/661
• CPC Class Codes: G06F 16/212 with details for data modelling support
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409

Abstract

Database table aggregation is implemented by a method that includes encoding first and second components via a waveform definition, the first and second components specifying criteria for aggregating data. The method also includes generating a complex waveform representing the components in a pattern corresponding to the waveform definition and criteria, which spans a first axis indicative of a time period for aggregating the data. The method further includes providing a byte count for aggregated data stored in a first table defined by the criteria, aggregating the data for the time period, and creating a second table for overflow data when the count is exceeded. The method also includes updating the complex waveform to reflect results of the aggregation that includes generating a waveform representing the second table along a second axis depicted at a location corresponding to a time in which the data aggregation for the first table was initiated.

Claims

1. A method, comprising:

encoding first and second components via a waveform definition and attribute values of the respective first and second components, the first component specifying first criteria for aggregating data and the second component specifying second criteria for aggregating data;

generating a complex periodic aggregation waveform having square waves representing the first and second components in a repeating pattern corresponding to the waveform definition and the first and second criteria, respectively, and which spans a length of a first axis indicative of a period of time for aggregating the data;

providing, as a threshold value, a maximum byte count for aggregated data stored in a first table defined by at least one of the first and second criteria, and aggregating the data for the duration of the period of time and in accordance with the attribute values of the respective first and second components;

creating a second table for overflow of data determined for the first table when the threshold value has been exceeded;

updating the complex periodic aggregation waveform to reflect results of the data aggregation, the updating including generating a waveform representing the second table along a second axis, the second axis depicted at a location corresponding to a time in which the data aggregation for the first table was initiated;

creating tables containing aggregated data for the first and second components based upon the first and second criteria, each of the tables assigned an identifier indicating a start date and time in which the data is stored in the tables, the tables including the first and second tables;

in response to receiving a start date/time value and a read/write indicator value, wherein a read indicator specifies a read operation and a write indicator specifies a write operation, searching a database of complex periodic aggregation waveforms until the start date/time value exceeds an identifier of a complex periodic aggregation waveform in the database, scanning the complex periodic aggregation waveform found in response to the searching until a period of time specified in one of the first and second criteria encloses the start date/time value, and searching a list of the tables until the start date/time value is equal to an identifier of one of the tables in the list; and

in response to determining the table found in response to the searching has an amount of aggregated data that exceeds the maximum byte count, performing one of the read operation and the write operation with respect to the table found in response to the searching and based upon the read/write indicator value.

2. The method of claim 1, further comprising:

providing the attribute values for the first component;

providing attribute values for attributes that define a period specification including the period of time, the period of time encapsulating a duration of time in which data is aggregated pursuant to the first criteria and a duration of time in which data is aggregated pursuant to the second criteria; and

storing the attribute values of the first component, second component, and period specification in a database as a complex periodic aggregation encoding, and assigning a unique identifier to the complex periodic aggregation encoding, the unique identifier comprising a start date and time for aggregating the data.

3. The method of claim 2, wherein criteria used to define the period specification include:

the start date and time;

a duration;

a default aggregation duration;

a feedback arrival frequency; and

the first and second components.

4. The method of claim 1, wherein criteria used to define components include:

an aggregation duration that indicates a unit of time in which data is aggregated for a period duration, the period duration defining a rising and falling edge of a square wave that is defined by a component;

a count indicating a number of tables created for the aggregation duration, the number of tables including the first and second tables;

a relative start time indicating a time in which data aggregation is initiated for a component; and

priority value indicating a priority of a component that is evaluated when an overlap in component definitions occurs.

5. The method of claim 1, wherein generating the complex periodic aggregation waveform includes depicting start dates and times for each of the tables at corresponding locations on the complex periodic aggregation waveform.

6. The method of claim 1, further comprising:

outputting a graphical depiction of the updated complex periodic aggregation waveform.

7. The method of claim 1, further comprising:

if the read/write indicator value is equal to the write operation, returning a maximum table name representing the table having the latest start date and time; and

if the read/write indicator value is equal to the read operation, returning the list of table names for a component indicating the threshold value has been exceeded.

8. A system, comprising:

a host system computer; and

an aggregation application executing on the host system computer, the aggregation application including an encoder, the encoder encoding first and second components via a waveform definition and attribute values of the respective first and second components, the first component specifying first criteria for aggregating data and the second component specifying second criteria for aggregating data;

the aggregation application generating a complex periodic aggregation waveform having square waves representing the first and second components in a repeating pattern corresponding to the waveform definition and the first and second criteria, respectively, and which spans a length of a first axis indicative of a period of time for aggregating the data;

providing as a threshold value, via a user interface of the aggregation application, a maximum byte count for aggregated data stored in a first table defined by at least one of the first and second criteria, and the aggregation application aggregating the data for the duration of the period of time and in accordance with the attribute values of the respective first and second components;

the aggregation application creating a second table for overflow of data determined for the first table when the threshold value has been exceeded;

the aggregation application updating the complex periodic aggregation waveform to reflect results of the data aggregation, the updating including generating a waveform representing the second table along a second axis, the second axis depicted at a location corresponding to time in which the data aggregation for the first table was initiated;

creating tables containing aggregated data for the first and second components based upon the first and second criteria, each of the tables assigned an identifier indicating to a start date and time in which data is stored in the tables, the tables including the first and second tables;

in response to receiving a start date/time value and a read/write indicator value, wherein a read indicator specifies a read operation and a write indicator specifies a write operation, searching a database of complex periodic aggregation waveforms until the start date/time value exceeds an identifier of a complex periodic aggregation waveform in the database, scanning the complex periodic aggregation waveform found in response to the searching until a period of time specified in one of the first and second criteria encloses the start date/time value, and searching a list of the tables until the start date/time value is equal to an identifier of one of the tables in the list; and

in response to determining the table found in response to the searching has an amount of aggregated data that exceeds the maximum byte count, performing one of the read operation and the write operation with respect to the table found in response to the searching and based upon the read/write indicator value.

9. The system of claim 8, further comprising:

the aggregation application providing the attribute values for the first component via the user interface of the aggregation application;

the aggregation application providing, via the user interface of the attribute application, attribute values for attributes that define a period specification including the period of time, the period of time encapsulating;

a duration of time in which data is aggregated pursuant to the first criteria, and a duration of time in which data is aggregated pursuant to the second criteria; and

the aggregation application storing the attribute values of the first component, second component, and period specification in a database as a complex periodic aggregation encoding, and assigning a unique identifier to the complex periodic aggregation encoding, the unique identifier comprising a start date and time for aggregating the data.

10. The system of claim 9, wherein criteria used to define the period specification include:

the start date and time;

a duration;

a default aggregation duration;

a feedback arrival frequency; and

the first and second components.

11. The system of claim 8, wherein criteria used to define components include:

an aggregation duration that indicates a unit of time in which data is aggregated for a period duration, the period duration defining a rising and falling edge of a square wave that is defined by a component;

a count indicating a number of tables created for the aggregation duration, the number of tables including the first and second tables;

a relative start time indicating a time in which data aggregation is initiated for a component; and

priority value indicating a priority of a component that is evaluated when an overlap in component definitions occurs.

12. The system of claim 8, wherein generating the complex periodic aggregation waveform includes depicting start dates and times for each of the tables at corresponding locations on the complex periodic aggregation waveform.

13. The system of claim 8, further comprising:

outputting a graphical depiction of the updated complex periodic aggregation waveform.

14. A computer program product comprising a non-transitory storage medium having computer-readable program code embodied thereon, the program code causing a computer to implement:

encoding first and second components via a waveform definition and attribute values of the respective first and second components, the first component specifying first criteria for aggregating data and the second component specifying second criteria for aggregating data;

generating a complex periodic aggregation waveform having square waves representing the first and second components in a repeating pattern corresponding to the waveform definition and the first and second criteria, respectively, and which spans a length of a first axis indicative of a period of time for aggregating the data;

providing, as a threshold value, a maximum byte count for aggregated data stored in a first table defined by at least one of the first and second criteria, and aggregating the data for the duration of the period of time and in accordance with the attribute values of the respective first and second components;

creating a second table for overflow of data determined for the first table when the threshold value has been exceeded;

updating the complex periodic aggregation waveform to reflect results of the data aggregation, the updating including generating a waveform representing the second table along a second axis, the second axis depicted at a location corresponding to time in which the data aggregation for the first table was initiated;

creating tables containing aggregated data for the first and second components based upon the first and second criteria, each of the tables assigned an identifier indicating a start date and time in which data is stored in the tables, the tables including the first and second tables;

in response to receiving a start date/time value and a read/write indicator value, wherein a read indicator specifies a read operation and a write indicator specifies a write operation, searching a database of complex periodic aggregation waveforms until the start date/time value exceeds an identifier of a complex periodic aggregation waveform in the database, scanning the complex periodic aggregation waveform found in response to the searching until a period of time specified in one of the first and second criteria encloses the start date/time value, and searching a list of the tables until the start date/time value is equal to an identifier of one of the tables in the list; and

in response to determining the table found in response to the searching has an amount of aggregated data that exceeds the maximum byte count, performing one of the read operation and the write operation with respect to the table found in response to the searching and based upon the read/write indicator value.

15. The computer program product of claim 14, further comprising program code implementing:

providing the attribute values for the first component;

providing attribute values for attributes that define a period specification including the period of time, the period of time encapsulating;

a duration of time in which data is aggregated pursuant to the first criteria, and a duration of time in which data is aggregated pursuant to the second criteria; and

storing the attribute values of the first component, second component, and period specification in a database as a complex periodic aggregation encoding, and assigning a unique identifier to the complex periodic aggregation encoding, the unique identifier comprising a start date and time for aggregating the data.

16. The computer program product of claim 15, wherein criteria used to define the period specification includes:

the start date and time;

a duration;

a default aggregation duration;

a feedback arrival frequency; and

the first and second components.

17. The computer program product of claim 14, wherein criteria used to define components include:

an aggregation duration that indicates a unit of time in which data is aggregated for a period duration, the period duration defining a rising and falling edge of a square wave that is defined by a component;

a count indicating a number of tables created for the aggregation duration, the number of tables including the first and second tables;

a relative start time indicating a time in which data aggregation is initiated for a component; and

priority value indicating a priority of a component that is evaluated when an overlap in component definitions occurs.

18. The computer program product of claim 14, wherein generating the complex periodic aggregation waveform includes depicting start dates and times for each of the tables at corresponding locations on the complex periodic aggregation waveform.

19. The computer program product of claim 14, further comprising program code implementing:

outputting a graphical depiction of the updated complex periodic aggregation waveform.

20. The computer program product of claim 14, further comprising program code implementing:

if the read/write indicator value is equal to the write operation, returning a maximum table name representing the table having the latest start date and time; and

if the read/write indicator value is equal to the read operation, returning the list of table names for a component indicating the threshold value has been exceeded.