Data profiling

• US 8,868,580 B2
• Filed: 09/15/2004
• Issued: 10/21/2014
• 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, Bay, Paul
• Primary Examiner(s): Girma, Anteneh
• Assistant Examiner(s): MINA, FATIMA P
• Application Number:
  US10/941,402
• Publication Number:
  US 20050114369A1
• Time in Patent Office: 3,688 Days
• Field of Search: 707/102, 707/770, 707/751, 707/758, 707/796, 707/736, 707/726, 707/737, 707/769, 707/802, 707/804, 707/806, 707/811, 707/673, 707/696
• US Class Current: 707/758
• 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, in a dataflow graph, from a data source, the dataflow graph including components and links, wherein the links direct flows of data between components;

profiling the data records using the data flow graph including

sending the data records on a first link to a partitioning component, the partitioning component partitioning the data records among a plurality of partitions;

generating, in each partition, by a canonicalize component in communication with the partitioning component, a flow of census elements on a respective one of a first plurality of links including generating a plurality of census elements for each data record, each census element including;

a field of the data,

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

generating, in each partition, by a rollup component in communication with the a corresponding canonicalize component, a flow of output census elements on a respective one of a second plurality of links including 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;

combining the flows of output census elements in each partition, and partitioning the combined flow of output census elements by the field and the value;

adding counts of the number of occurrences of the same value for the same field for the partitioned flows of 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 flow of data records;

storing profile information based on the single census elements; and

processing data from the data source, including accessing the stored profile information, reading the data from the data source after profiling the data from the data source, processing the data according to the accessed 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 variable record structure records with at least one of a conditional field and a variable number of fields.

4. The method of claim 3 wherein profiling the data includes interpreting the variable record structure records.

5. The method of claim 1 wherein the data source includes a data storage system.

6. The method of claim 5 wherein the data storage system includes a database system.

7. 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.

8. The method of claim 7 wherein storing profile information includes storing statistics for the field based on the single census elements.

9. The method of claim 1 further including maintaining a metadata store that contains metadata related to the data source.

10. The method of claim 9 wherein storing the profile information includes updating the metadata related to the data source.

11. The method of claim 9 wherein profiling the data records and processing the data each make use of metadata for the data source.

12. The method of claim 1 wherein profiling the data records from the data source further includes determining a format specification based on the profile information.

13. The method of claim 1 wherein profiling the data records further includes determining a validation specification based on the profile information.

14. The method of claim 13 wherein processing the data includes identifying invalid records in the data based on the validation specification.

15. The method of claim 1 wherein profiling the data records further includes specifying data transformation instructions based on the profile information.

16. The method of claim 15 wherein processing the data records includes applying the transformation instructions to the data.

17. The method of claim 1 wherein processing the data includes importing the data into a data storage subsystem.

18. The method of claim 17 wherein processing the data includes validating the data prior to importing the data into a data storage subsystem.

19. The method of claim 18 wherein validating the data includes comparing characteristics of the data to reference characteristics for said data.

20. The method of claim 19 wherein the reference characteristics include statistical properties of the data.

21. The method of claim 1 wherein reading data includes reading the data from a parallel data source, each part of the parallel data source being processed by a different one of a first set of parallel processors and/or computers.

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

25. The method of claim 1, wherein each census element further includes a flag indicating whether the value included in the element is valid.

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

27. 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.

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

determining that a data record is invalid; and

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

22. Software stored on a non-transitory computer-readable storage medium including executable instructions for causing a computer system to:

read data records, in a dataflow graph, from a data source, the dataflow graph including components and links, wherein the links direct flows of data between components;

profile the data records using the dataflow graph including

sending the data records on a first link to a partitioning component, the partitioning component partitioning the data records among a plurality of partitions;

generating, in each partition, a flow of census elements, by a canonicalize component in communication with the partitioning component, on a respective one of a first plurality of links including generating a plurality of census elements identifying a field and a corresponding value for each data record, each census element including;

a field of the data,

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

generating, in each partition, a flow of output census elements, by a rollup component in communication with a corresponding canonicalize component;

on a respective one of a second plurality of links including 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;

combining the flows of output census elements in each partition, and partitioning the combined flow of output census elements by the field and the value;

adding counts of the number of occurrences of the same value for the same field for the partitioned flows of 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 flow of data records;

store profile information based on the single census elements; and

process data from the data source by accessing the stored profile information, reading the data from the data source after profiling the data from the data source, processing the data according to the accessed profile information, and outputting a result of the processing.

23. 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 including a non-transitory computer-readable storage medium in communication with the computer system;

with a dataflow graph configured to execute on a plurality of the processors, the dataflow graph including components and links, wherein the links direct flows of data between components and the components including;

a read data component configured to read data records from a data source, a first partitioning component connected to the read data component by a link and configured to partition the data records among a plurality of partitions corresponding to different processors;

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

a field of the data,

a corresponding value occurring within the of the data record;

a plurality of local rollup components, each local rollup component in communication with a canonicalize component and configured to generate, in each partition, a flow of output census elements including 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;

a second partitioning component connected to each local rollup component in the plurality of local rollup components by a link and configured to combining the flows of output census elements in each partition, and partitioning the combined flow of output census elements by the field and the value;

a plurality of global rollup components, each global rollup component connected to the second partitioning component by a link and configured to;

add counts of the number of occurrences of the same value for the same field for the partitioned flows of 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 flow of data records, and store profile information based on the single census elements; and

a processing module connected over communication paths to the data source and the data storage subsystem and configured execute on the computer system to access the stored profile information, to read data from the data source after the profiling module reads the data from the data source, to process the data from the data source according to the accessed profile information, and to output a result of the processing.