Data profiling

• US 9,323,802 B2
• Filed: 10/20/2014
• Issued: 04/26/2016
• Est. Priority Date: 09/15/2003
• Status: Active Grant

• Current Assignee: Ab Initio Technology LLC (Ab Initio Software Corporation)
• Original Assignee: Ab Initio Technology LLC (Ab Initio Software Corporation)
• Inventors: Gould, Joel, Feynman, Carl Richard, Bay, Paul
• Primary Examiner(s): Trujillo, James
• Assistant Examiner(s): MINA, FATIMA P
• Application Number:
  US14/519,030
• Publication Number:
  US 20150106341A1
• Time in Patent Office: 554 Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2365 Ensuring data consistency and integrity
• CPC Class Codes: G06F 16/24544 Join order optimisation
• CPC Class Codes: G06F 16/24554 Unary operations; Data partitioning operations
• CPC Class Codes: G06F 16/24556 Aggregation; Duplicate elimination
• CPC Class Codes: G06F 16/2465 Query processing support for facilitating data mining operations in structured databases
• CPC Class Codes: G06F 16/252 between a Database Management System and a front-end application
• CPC Class Codes: G06F 16/258 Data format conversion from or to a database
• CPC Class Codes: G06F 16/285 Clustering or classification

Abstract

Processing data includes profiling data from a data source, including reading the data from the data source, computing summary data characterizing the data while reading the data, and storing profile information that is based on the summary data. The data is then processed from the data source. This processing includes accessing the stored profile information and processing the data according to the accessed profile information.

Claims

1. A method for processing data including:

reading data records from a data source;

profiling the data records, including;

sending the data records to a partitioning component, the partitioning component partitioning the data records into a plurality of partitions;

generating, in each partition, a plurality of census elements for each data record, each census element including;

a field of the data; and

a corresponding value occurring within the field of the data record;

in each partition, combining occurrences of census elements having the same value for the same field into an output census element including the field, the value, and a count of the number of combined census elements;

partitioning the output census elements by the field and the value included in each output census element, wherein output census elements that have the same value for the same field are partitioned into the same partition; and

adding counts of the number of occurrences of the same value for the same field for the partitioned output census elements to produce, for each field and corresponding value, a single census element that includes a total count of occurrences of that field and corresponding value in the data records;

storing profile information; and

processing the data records, including;

accessing the stored profile information;

reading the data records from the data source;

processing the data records according to the profile information; and

outputting a result of the processing.

2. The method of claim 1, wherein profiling the data is performed without maintaining a copy of the data outside the data source.

3. The method of claim 2, wherein the data includes records with variable record structure with at least one of a conditional field and a variable number of fields.

4. The method of claim 3, wherein the records with variable record structure include one or both of a conditional record and a variable length vector and are processed without flattening the data of the records into fixed length segments.

5. The method of claim 1, wherein profiling the data includes counting a number of occurrences for each of a set of distinct values for a field.

6. The method of claim 5, wherein storing profile information includes storing statistics for the field based on the single census elements.

7. The method of claim 1, further including maintaining a metadata store that contains metadata related to the data source, wherein storing the profile information includes updating the metadata related to the data source.

8. The method of claim 7, wherein profiling the data records and processing the data records each make use of metadata for the data source.

9. The method of claim 8, wherein the metadata includes statistics related to the data source, the statistics including one or more of:

i) a histogram of values, ii) a maximum value, iii) a minimum value, iv) a mean value, v) a sample of a most common value, and vi) a sample of a least common value, in a field of a data record.

10. The method of claim 1, wherein processing the data records further includes determining information related to a format of the data records based on the profile information.

11. The method of claim 1, wherein profiling the data records further includes determining validation information based on the profile information, and processing the data records includes identifying invalid records in the data based on the validation specification.

12. The method of claim 11, wherein the validation information specifies a valid data type for each field of the data records and includes information for mapping invalid data values onto valid data values.

13. The method of claim 1, wherein processing the data records includes:

validating the data by comparing characteristics of the data to reference characteristics for the data; and

importing the data into a data storage system.

14. The method of claim 13, wherein the reference characteristics include statistical properties of the data.

15. The method of claim 1, wherein storing profile information includes storing the total count from the single census element for a field and a corresponding value.

16. The method of claim 1, wherein each census element further includes a flag indicating whether the value included in the element is valid or corresponds to a pre-determined null value.

17. The method of claim 1, wherein the data records are filtered, by a filtering component, to limit profiling to a field of each data record.

18. The method of claim 1, wherein the data records are filtered, by a filtering component, including:

determining that a data record is invalid; and

sending the data record to an invalid records component on a link.

19. The method of claim 1, wherein the data records are in a dataflow graph, the dataflow graph including components and links, wherein the links direct flows of data between components.

20. The method of claim 19, further including:

generating, in each partition, a flow of census elements on a respective one of a first plurality of links; and

generating, in each partition, a flow of output census elements on a respective one of a second plurality of links.

21. The method of claim 1, wherein the data records are profiled by a first data processing module and the data records are processed by a second data processing module.

22. A non-transitory medium storing a computer program including instructions for causing a computer to:

read data records from a data source;

profile the data records, including;

sending the data records to a partitioning component, the partitioning component partitioning the data records into a plurality of partitions;

generating, in each partition, a plurality of census elements for each data record, each census element including;

a field of the data; and

a corresponding value occurring within the field of the data record;

in each partition, combining occurrences of census elements having the same value for the same field into an output census element including the field, the value, and a count of the number of combined census elements;

partitioning the output census elements by the field and the value included in each output census element, wherein output census elements that have the same value for the same field are partitioned into the same partition; and

adding counts of the number of occurrences of the same value for the same field for the partitioned output census elements to produce, for each field and corresponding value, a single census element that includes a total count of occurrences of that field and corresponding value in the data records;

store profile information; and

process the data records, including;

accessing the stored profile information;

reading the data records from the data source;

processing the data records according to the profile information; and

outputting a result of the processing.

23. The non-transitory medium of claim 22, wherein profiling the data is performed without maintaining a copy of the data outside the data source.

24. The non-transitory medium of claim 23, wherein the data includes records with variable record structure with at least one of a conditional field and a variable number of fields.

25. The non-transitory medium of claim 24, wherein the records with variable record structure include one or both of a conditional record and a variable length vector and are processed without flattening the data of the records into fixed length segments.

26. The non-transitory medium of claim 22, wherein profiling the data includes counting a number of occurrences for each of a set of distinct values for a field.

27. The non-transitory medium of claim 26, wherein storing profile information includes storing statistics for the field based on the single census elements.

28. The non-transitory medium of claim 22, further including instructions for causing the computer to maintain a metadata store that contains metadata related to the data source, wherein storing the profile information includes updating the metadata related to the data source.

29. The non-transitory medium of claim 22, wherein processing the data records further includes determining information related to a format of the data records based on the profile information.

30. The non-transitory medium of claim 22, wherein profiling the data records further includes determining validation information based on the profile information, and processing the data records includes identifying invalid records in the data based on the validation specification.

31. The non-transitory medium of claim 22, wherein processing the data records includes:

validating the data by comparing characteristics of the data to reference characteristics for the data; and

importing the data into a data storage system.

32. The non-transitory medium of claim 22, wherein storing profile information includes storing the total count from the single census element for a field and a corresponding value.

33. The non-transitory medium of claim 22, wherein each census element further includes a flag indicating whether the value included in the element is valid or corresponds to a pre-determined null value.

34. The non-transitory medium of claim 22, wherein the data records are filtered, by a filtering component, to limit profiling to a field of each data record.

35. The non-transitory medium of claim 22, wherein the data records are filtered, by a filtering component, including:

determining that a data record is invalid; and

sending the data record to an invalid records component on a link.

36. The non-transitory medium of claim 22, wherein the data records are in a dataflow graph, the dataflow graph including components and links, wherein the links direct flows of data between components.

37. The non-transitory medium of claim 22, wherein the data records are profiled by a first data processing module and the data records are processed by a second data processing module.

38. A data processing system including:

a computer system, including a plurality of processors;

a data source accessible to the computer system;

a data storage subsystem in communication with the computer system;

a read data component configured to read data records from a data source;

a first partitioning component in communication with the read data component, the first partitioning component configured to partition the data records into a plurality of partitions;

a plurality of canonicalize components, each canonicalize component in communication with the first partitioning component and configured to generate, in each partition, a plurality of census elements for each data record, each census element including;

a field of the data; and

a corresponding value occurring within the field of the data record;

a plurality of local rollup components, each local rollup component in communication with a canonicalize component and configured to, in each partition, combine occurrences of census elements having the same value for the same field into an output census element including the field, the value, and a count of the number of combined census elements;

a second partitioning component in communication with each local rollup component and configured to partition the output census elements by the field and the value included in each output census element, wherein output census elements that have the same value for the same field are partitioned into the same partition;

a plurality of global rollup components, each global rollup component in communication with the second partitioning component and configured to;

add counts of the number of occurrences of the same value for the same field for the partitioned output census elements to produce, for each field and corresponding value, a single census element that includes a total count of occurrences of that field and corresponding value in the data records; and

store profile information; and

a processing module in communication with the data source and the data storage subsystem and configured to process the data records, including;

accessing the stored profile information;

reading the data records from the data source;

processing the data records according to the profile information; and

outputting a result of the processing.

39. The system of claim 38, wherein profiling the data is performed without maintaining a copy of the data outside the data source.

40. The system of claim 38, wherein profiling the data includes counting a number of occurrences for each of a set of distinct values for a field.

41. The system of claim 38, wherein processing the data records further includes determining information related to a format of the data records based on the profile information.

42. The system of claim 38, wherein profiling the data records further includes determining validation information based on the profile information, and processing the data records includes identifying invalid records in the data based on the validation specification.

43. The system of claim 38, wherein processing the data records includes:

validating the data by comparing characteristics of the data to reference characteristics for the data; and

importing the data into a data storage system.

44. The system of claim 38, wherein storing profile information includes storing the total count from the single census element for a field and a corresponding value.

45. The system of claim 38, wherein the data records are filtered, by a filtering component, to limit profiling to a field of each data record.

46. The system of claim 38, wherein the data records are filtered, by a filtering component, including:

determining that a data record is invalid; and

sending the data record to an invalid records component on a link.

47. The system of claim 38, wherein the data records are in a dataflow graph, the dataflow graph including components and links, wherein the links direct flows of data between components.

48. The system of claim 38, wherein the data records are profiled by a first data processing module and the data records are processed by a second data processing module.

49. A data processing system including:

means for reading data records from a data source;

means for profiling the data records, including;

sending the data records to a partitioning component, the partitioning component partitioning the data records into a plurality of partitions;

generating, in each partition, a plurality of census elements for each data record, each census element including;

a field of the data; and

a corresponding value occurring within the field of the data record;

in each partition, combining occurrences of census elements having the same value for the same field into an output census element including the field, the value, and a count of the number of combined census elements;

partitioning the output census elements by the field and the value included in each output census element, wherein output census elements that have the same value for the same field are partitioned into the same partition; and

adding counts of the number of occurrences of the same value for the same field for the partitioned output census elements to produce, for each field and corresponding value, a single census element that includes a total count of occurrences of that field and corresponding value in the data records;

means for storing profile information; and

means for processing the data records, including;

accessing the stored profile information;

reading the data records from the data source;

processing the data records according to the profile information; and

outputting a result of the processing.

50. The system of claim 49, wherein profiling the data is performed without maintaining a copy of the data outside the data source.

51. The system of claim 49, wherein profiling the data includes counting a number of occurrences for each of a set of distinct values for a field.

52. The system of claim 49, wherein processing the data includes:

validating the data by comparing characteristics of the data to reference characteristics for the data; and

importing the data into a data storage system.

53. The system of claim 49, wherein storing profile information includes storing the total count from the single census element for a field and a corresponding value.

54. The system of claim 49, wherein the data records are in a dataflow graph, the dataflow graph including components and links, wherein the links direct flows of data between components.