METHODS AND SYSTEMS FOR MERGING DATA SETS
Systems and methods for merging data sets are provided. Data sets are merged based upon a process which begins by sorting data sets. Data sets each include at least one data set key column storing at least one data set key column record. The key column record subsets include at least one data set key column record. Based upon the identification of the first and second key column record subsets, a working data set is assembled. The working data set includes at least the first and second key column record subset, a first and second last record indicator corresponding to the last record of the first and second key column record subset respectively, and a first and second position indicator associating the data set key column records with the data sets respectively. The working data set is sorted in accordance with a selected one or more key column record subsets.
A portion of the disclosure of this patent document may contain material which is subject to copyright protection. The copyright owner has no objection to the facsimile reproduction by anyone of the patent document or the patent disclosure, as it appears in the Patent and Trademark Office patent file or records, but otherwise reserves all copyright rights whatsoever.
BACKGROUNDIncreasingly, an abundance of business intelligence data is gathered from the Internet and other information sources. Much of this data takes the form of information describing an action or occurrence (i.e., an event) that is typically generated by a user or a computer. Event data, including but not limited to data that may be associated with or derived from events, is often stored for later access, identification, manipulation, or use. In many cases, event data is stored in the form of records within one or more datastores, data sets or database files (e.g., in the form of tables). Data sets storing event data typically require significant amounts of storage space that may be spread across a plurality of networked storage devices.
Event data that is gathered from one or more information sources may be related or share common properties despite being stored in different data sets or residing at different network or storage locations. In order to access, identify, manipulate, or use commercially useful information, businesses typically build queries or provide instructions for extracting event data based upon the related or shared common properties of the event data. Commonly referred to as “data mining,” this process typically involves searching through numerous data sets that include one or more fields (i.e., primary key fields) that uniquely identify event data sharing common or related properties. Event data matching a certain query may then be extracted from the numerous databases.
Data mining is typically a processor-intensive activity. Even in distributed processing systems, where multiple computers may be linked in a network to perform the same work, processing of queries that span large and/or numerous data sets often require a significant number of CPU machine cycles. Particularly where queries request event data from a plurality of databases, the processing overhead may be enormous for merging and analyzing event data records across the plurality of databases.
In many circumstances, query results may be required in a timely manner (e.g., microseconds) or query results may be required so as to reduce utilization of one or more processors. In response to these and other requirements, many queries may make use of data that is pre-sorted. Pre-sorting data set information typically makes searching more efficient by organizing a collection of data into a sequenced order that may permit faster extraction of the data on the basis of the sequenced order. Despite some efficiency that may be gained by pre-sorting a data set, queries requesting event data from a plurality of data sets do not necessarily exhibit the same efficiency if the query directs a search of more than one sorted, yet un-merged, data set. Such queries may exhibit a high number of input/output operations or in-memory tree/scan operations that may degrade the performance of the query operations. Thus, there exists a need for methods and systems to efficiently merge event data that may comprise a plurality of data sets.
SUMMARYAgainst this backdrop systems and methods have been developed for merging data sets. In one embodiment (which embodiment is intended to be illustrative and not restrictive), a method for merging data sets is provided. The method includes sorting a plurality of data sets including at least a first data set and a second data set, the first data set including at least one first data set key column storing at least one first data set key column record and the second data set including at least one second data set key column storing at least one second data set key column record. The method further includes identifying a first key column record subset and a second key column record subset, the first key column record subset including the at least one first data set key column record and the second key column record subset including the at least one second data set key column record. The method includes, based upon the identification of the first key column record subset and the second key column record subset, assembling a working data set including at least the first key column record subset and the second key column record subset, a first last record indicator corresponding to the last record of the first key column record subset, a second last record indicator corresponding to the last record of the second key column record subset, a first position indicator associating the first data set key column record with the first data set, and a second position indicator associating the second data set key column record with the second data set. The method further includes sorting the working data set in accordance with a selected one or more of the first key column record subset and second key column record subset. In an aspect of the method, the first key column record subset and the second key column record subset do not exceed a certain memory capacity. In another aspect of the method, the certain memory capacity corresponds to a cache size. In an aspect of the method, the first key column record subset equals the second key column record subset. In another aspect, the method further includes sorting a third data set, the third data set including at least one third data set key column storing at least one third data set key column record; identifying a third key column record subset, the third key column record subset including the at least one third data set key column record; and based upon the identification of the third key column record subset, assembling the working data set further including at least the third key column record subset, a third last record indicator corresponding to the last record of the third key column record subset, and a third position indicator associating the third data set key column record with the third data set. In an aspect of the method, the first data set key column stores a second first data set key column record. In another aspect of the method, the second first data set key column record duplicates the at least one first data set key column record. In an aspect of the method, the working data set further includes a duplicate record indicator corresponding to the first data set key column record, the duplicate record indicator identifying whether the second first data set key column record duplicates the at least one first data set key column record. In another aspect of the method, the working data set excludes the second first data set key column record. In an aspect of the method, the first position indicator identifies a location of the first data set and the second position indicator identifies a location of the second data set.
In another embodiment (which embodiment is intended to be illustrative and not restrictive), a computer-readable medium having stored thereon a data structure is provided. The computer-readable medium includes a first data field comprising a first instance of key data. The computer-readable medium further includes a second data field comprising data identifying a location of a second instance of the key data in a table including the second instance of the key data. In an aspect, the computer-readable medium further includes a third data field comprising data identifying a duplicate third instance of the key data in the table including the second instance of the key data. In another aspect, the computer-readable medium further includes a third data field comprising data identifying whether the first instance of key data corresponds to a last record indicator.
In another embodiment (which embodiment is intended to be illustrative and not restrictive), another method for merging data sets is provided. The method includes assembling a first working data set comprising a first portion of sorted key column records from a first sorted data set and a second portion of sorted key column records from a second sorted data set. The method further includes sorting the first working data set. The method includes identifying a first occurrence of a last record corresponding to either the first portion or the second portion of the first working data set. The method further includes determining whether one or more records following the last record are equivalent to the last record. The method includes, upon determining that the one or more records following the last record are equivalent to the last record, identifying a last record of the one or more records following the last record as a sorting cut-off point. In one aspect, the method further includes assembling a second working data set comprised of one or more records following the sorting cut-off point.
In another embodiment (which embodiment is intended to be illustrative and not restrictive), yet another method for merging data sets is provided. The method includes assembling a first working data set comprising a first portion of sorted key column records from a first sorted data set and a second portion of sorted key column records from a second sorted data set. The method further includes sorting the first working data set. The method includes identifying a last record corresponding to the first portion of the first working data set. The method further includes determining whether the record following the last record corresponding to the first portion of the first working data set is a last record corresponding to the second portion of the first working data set. The method includes, upon determining that the record following the last record corresponding to the first portion of the first working data set is a last record corresponding to the second portion of the first working data set, identifying the last record corresponding to the second portion of the first working data set as a sorting cut-off point.
In another embodiment (which embodiment is intended to be illustrative and not restrictive), a system for merging data sets is provided. The system includes a datastore that stores a plurality of data sets wherein each of the data sets include at least one key column comprised of associated data records. The system further includes a request module that requests a transformation of the associated data records of at least a portion of the plurality of the data sets stored within the datastore. The system includes a data transformation module that performs the steps of: sorting a plurality of data sets including at least a first data set and a second data set, the first data set including at least one first data set key column storing at least one first data set key column record and the second data set including at least one second data set key column storing at least one second data set key column record; identifying a first key column record subset and a second key column record subset, the first key column record subset including the at least one first data set key column record and the second key column record subset including the at least one second data set key column record; based upon the identification of the first key column record subset and the second key column record subset, assembling a working data set including at least the first key column record subset and the second key column record subset, a first last record indicator corresponding to the last record of the first key column record subset, a second last record indicator corresponding to the last record of the second key column record subset, a first position indicator associating the first data set key column record with the first data set, and a second position indicator associating the second data set key column record with the second data set; and sorting the working data set in accordance with a selected one or more of the first key column record subset and second key column record subset.
These and various other features as well as advantages will be apparent from a reading of the following detailed description and a review of the associated drawings. Additional features are set forth in the description which follows, and in part will be apparent from the description, or may be learned by practice of the described embodiments. While it is to be understood that both the foregoing general description and the following detailed description are exemplary and explanatory, the benefits and features will be realized and attained by the structure particularly pointed out in the written description and claims hereof as well as the appended drawings.
The following drawing figures, which form a part of this application, are illustrative of embodiments systems and methods described below and are not meant to limit the scope of this disclosure in any manner, which scope shall be based on the claims appended hereto.
In the embodiment illustrated by
In the embodiment illustrated by
In the embodiment illustrated by
In one embodiment, a system 300 further comprises a request module 302 that requests a transformation of the associated data records of at least a portion of the plurality of the data sets stored within the datastore 312. Requests may take many forms, including but not limited to user requests, event-driven requests and requests generated according to a certain script or algorithm. For example, a user may request transformation of the associated data records, an aggregation of a certain number of data records and/or data sets may trigger a transformation request, or a request may be generated according to a predetermined transformation request script (e.g., requesting transformation after a time period). One skilled in the art will recognize that many forms and types of transformation requests are possible and within the scope of this disclosure.
In one embodiment, a system 300 further comprises a data transformation module 304 that performs the steps of: sorting a plurality of data sets including at least a first data set and a second data set, the first data set including at least one first data set key column storing at least one first data set key column record and the second data set including at least one second data set key column storing at least one second data set key column record; identifying a first key column record subset and a second key column record subset, the first key column record subset including the at least one first data set key column record and the second key column record subset including the at least one second data set key column record; based upon the identification of the first key column record subset and the second key column record subset, assembling a working data set including at least the first key column record subset and the second key column record subset, a first last record indicator corresponding to the last record of the first key column record subset, a second last record indicator corresponding to the last record of the second key column record subset, a first position indicator associating the first data set key column record with the first data set, and a second position indicator associating the second data set key column record with the second data set; and sorting the working data set in accordance with a selected one or more of the first key column record subset and second key column record subset. The data transformation module 304 may be comprised of one or more processing units 306 that may include one or more processors 308 and caches 310. One skilled in the art will recognize that a processing unit 306 may take many forms, including but not limited to one or more on-chip caches 310 and one or more single and/or multiple core processors 308. One skilled in the art will also recognize that processing within data transformation module 304 may be distributed among one or more networked computing devices.
The method 400 further comprises identifying a first key column record subset and a second key column record subset in an identifying operation 404, the first key column record subset including the at least one first data set key column record and the second key column record subset including the at least one second data set key column record. For example, for each sorted input partition or table, approximately the same number of records may be identified from the top of the remaining rows in the partition or table. The number of records (e.g., key records) identified from each partition or table may be the number of records that may fit into the working table divided by the number of partitions or tables participating in the merge process. In one embodiment, all records in a data set may be identified and/or retrieved, while in another embodiment (as discussed previously) a portion of records (e.g., key column column records are identified and/or retrieved. One skilled in the art will recognize that identification of a first key column record subset and a second key column record subset may include, but is not limited to, identifying a certain number of records within a data set or identifying a set of records sufficient to occupy at least a portion of a memory capacity.
The method 400 further comprises, based upon the identification of the first key column record subset and the second key column record subset, assembling in an assembling operation 406 a working data set including at least the first key column record subset and the second key column record subset, a first last record indicator corresponding to the last record of the first key column record subset, a second last record indicator corresponding to the last record of the second key column record subset, a first position indicator associating the first data set key column record with the first data set, and a second position indicator associating the second data set key column record with the second data set. For example, a working set may be sized such that it is large enough to accommodate all key column record subsets at once. As another example, a working table may be comprised of multiple sub-tables or child tables that permit assembly of the respective data subsets. One skilled in the art will recognize that many forms of assembling a working data set are possible, including but not limited to assembly of a working data set comprised of data records and/or data record references.
The method 400 further includes sorting the working data set in accordance with a selected one or more of the first key column record subset and second key column record subset in a sorting operation 408. In an aspect of the method 400, the first key column record subset and the second key column record subset may not exceed a certain memory capacity. In another aspect of the method 400, the certain memory capacity may correspond to a cache size. In an aspect of the method 400, the first key column record subset may equal the second key column record subset. In another aspect, the method 400 may further includes sorting a third data set, the third data set including at least one third data set key column storing at least one third data set key column record; identifying a third key column record subset, the third key column record subset including the at least one third data set key column record; and based upon the identification of the third key column record subset, assembling the working data set further including at least the third key column record subset, a third last record indicator corresponding to the last record of the third key column record subset, and a third position indicator associating the third data set key column record with the third data set. In yet another aspect of the method 400, the first data set key column may store a second first data set key column record. In another aspect of the method 400, the second first data set key column record duplicates the at least one first data set key column record. In still yet another aspect of the method 400, the working data set may further include a duplicate record indicator corresponding to the first data set key column record, the duplicate record indicator identifying whether the second first data set key column record duplicates the at least one first data set key column record. In another aspect of the method 400, the working data set may exclude the second first data set key column record. In another aspect of the method 400, the first position indicator may identify a location of the first data set and the second position indicator identifies a location of the second data set.
Those skilled in the art will recognize that the methods and systems of the present disclosure may be implemented in many manners and as such are not to be limited by the foregoing exemplary embodiments and examples. In other words, functional elements being performed by a single or multiple components, in various combinations of hardware and software or firmware, and individual functions, can be distributed among software applications at either the client or server level or both. In this regard, any number of the features of the different embodiments described herein may be combined into single or multiple embodiments, and alternate embodiments having fewer than or more than all of the features herein described are possible. Functionality may also be, in whole or in part, distributed among multiple components, in manners now known or to become known. Thus, myriad software/hardware/firmware combinations are possible in achieving the functions, features, interfaces and preferences described herein. Moreover, the scope of the present disclosure covers conventionally known manners for carrying out the described features and functions and interfaces, and those variations and modifications that may be made to the hardware or software or firmware components described herein as would be understood by those skilled in the art now and hereafter.
While various embodiments have been described for purposes of this disclosure, various changes and modifications may be made which are well within the scope of the present disclosure. Numerous other changes may be made which will readily suggest themselves to those skilled in the art and which are encompassed in the spirit of this disclosure and as defined in the appended claims.
Claims
1-7. (canceled)
8. A method comprising:
- assembling, via a computing device, a first working data set comprising a first portion of sorted first records from a first sorted data set and a second portion of sorted second records from a second sorted data set, the sorted first portion having a last first record and identifying a first number of sorted records and the sorted second portion having a last second record and identifying a second number of sorted records, said assembling based in part on location information of a respective data set and the respective number of sorted records in each respective record subset, the location information corresponds to respective position indicators that associate respective key columns within the sorted data sets with the data sets;
- sorting, via the computing device, the first working data set, identifying a last sorted working record in the first working data set, the last sorted working record corresponding to the first occurrence in the sorted first working data set of either the last first record or last second record; and
- identifying, via the computing device, the last sorted working record and any records following the last sorted working record that are equivalent to the last sorted working record as a sorting cut-off point.
9. The method of claim 8 further comprising:
- assembling a second working data set comprised of all first and second records in the first working data set following the sorting cut-off point, a third portion of sorted first records from a first sorted data set and a fourth portion of sorted second records from a second sorted data set.
10. The method of claim 8 further comprising:
- copying all of the records before the sorting cut-off point to a combined data set.
11-13. (canceled)
17-22. (canceled)
23. The method of claim 8, wherein the first number and the second number are chosen based on a memory capacity.
24. The method of claim 23, wherein the memory capacity corresponds to a cache size.
25. The method of claim 8, wherein the first number and the second number are equal.
26. The method of claim 8, further comprising:
- associating with each record in the first working data set a position indicator, the position indicator identifying the data set and location within the data set from which the record was obtained.
27. The method of claim 8, further comprising:
- associating with each record in the first working data set a position indicator, the position indicator identifying the data set and location within the data set from which the record was obtained.
28. A non-transitory computer-readable storage medium having tangibly stored thereon computer executable instructions, that when executed by a computing device, performs a method comprising:
- assembling a first working data set comprising a first portion of sorted first records from a first sorted data set and a second portion of sorted second records from a second sorted data set, the sorted first portion having a last first record and identifying a first number of sorted records and the sorted second portion having a last second record and identifying a second number of sorted records, said assembling based in part on location information of a respective data set and the respective number of sorted records in each respective record subset, the location information corresponds to respective position indicators that associate respective key columns within the sorted data sets with the data sets;
- sorting the first working data set, identifying a last sorted working record in the first working data set, the last sorted working record corresponding to the first occurrence in the sorted first working data set of either the last first record or last second record; and
- identifying the last sorted working record and any records following the last sorted working record that are equivalent to the last sorted working record as a sorting cut-off point.
29. The non-transitory computer-readable storage medium of claim 28 further comprising:
- assembling a second working data set comprised of all first and second records in the first working data set following the sorting cut-off point, a third portion of sorted first records from a first sorted data set and a fourth portion of sorted second records from a second sorted data set.
30. The non-transitory computer-readable storage medium of claim 28 further comprising:
- copying all of the records before the sorting cut-off point to a combined data set.
31. The non-transitory computer-readable storage medium of claim 28, wherein the first number and the second number are chosen based on a memory capacity.
32. The non-transitory computer-readable storage medium of claim 31, wherein the memory capacity corresponds to a cache size.
33. The non-transitory computer-readable storage medium of claim 28, wherein the first number and the second number are equal.
34. The non-transitory computer-readable storage medium of claim 28, further comprising:
- associating with each record in the first working data set a position indicator, the position indicator identifying the data set and location within the data set from which the record was obtained.
35. A system comprising:
- a plurality of processors;
- a datastore that stores a plurality of data sets wherein each of the data sets includes at least one key column comprised of associated data records, each key column comprises information identifying a location of each of the associated data records within the respective data set;
- a request module implemented by at least one of said plurality of processors that requests a transformation of the associated data records of at least a portion of the plurality of the data sets stored within the datastore; and
- a data transformation module implemented by at least one of said plurality of processors that performs the steps of: assembling a first working data set comprising a first portion of sorted first records from a first sorted data set and a second portion of sorted second records from a second sorted data set, the sorted first portion having a last first record and identifying a first number of sorted records and the sorted second portion having a last second record and identifying a second number of sorted records, said assembling based in part on location information of a respective data set and the respective number of sorted records in each respective record subset, the location information corresponds to respective position indicators that associate respective key columns within the sorted data sets with the data sets; sorting the first working data set, identifying a last sorted working record in the first working data set, the last sorted working record corresponding to the first occurrence in the sorted first working data set of either the last first record or last second record; and identifying the last sorted working record and any records following the last sorted working record that are equivalent to the last sorted working record as a sorting cut-off point.
36. The system of claim 35 wherein the first number and second number are chosen by the data transformation module based on a memory available to the transformation module.
37. The system of claim 36 wherein the memory capacity corresponds to a cache size of a cache used by the transformation module.
38. The system of claim 35 wherein the first number and second number are equal.
39. The system of claim 35 wherein at least two of the first records in the first record subset are duplicate records and the transformation module includes only one of the at least two duplicate records in the working data set and identifies the only one of the at least two duplicate records with a duplicate record indicator.
Type: Application
Filed: Jan 30, 2012
Publication Date: May 24, 2012
Inventors: RadhaKrishna Uppala (Bellevue, WA), Joerg Harry Hallmann (Sammamish, WA)
Application Number: 13/361,000
International Classification: G06F 17/30 (20060101);