Automatic consistent sampling for data analysis

• US 9,239,853 B2
• Filed: 09/18/2014
• Issued: 01/19/2016
• Est. Priority Date: 07/19/2011
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Gorelik, Alexander
• Primary Examiner(s): Fan, Shiow-Jy
• Application Number:
  US14/489,658
• Publication Number:
  US 20150006542A1
• Time in Patent Office: 488 Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/21 Design, administration or maintenance of databases
• CPC Class Codes: G06F 16/2228 Indexing structures
• CPC Class Codes: G06F 16/2282 Tablespace storage structures; Management thereof
• CPC Class Codes: G06F 16/2365 Ensuring data consistency and integrity

Abstract

A method, computer program product, and system for analyzing data within one or more databases, comprising selecting one or more databases for analysis, each database comprising one or more database objects comprising one or more data values, applying a function to each data value in each database object within the one or more databases, where the function produces function values limited to a predetermined range, identifying for analysis the data values producing a certain function value within the predetermined range to form a sampled data set, and analyzing the sampled data set to determine relationships between the database objects within and across the one or more databases.

Claims

1. A computer-implemented method of analyzing data within one or more databases, comprising:

selecting one or more databases for analysis, each database comprising one or more database tables with one or more columns including one or more data values;

applying a function to each data value of one or more columns in the database tables within the selected one or more databases, wherein the function produces different function values for the data values limited to a predetermined range;

identifying for analysis the data values producing a same function value within the predetermined range to form a sampled data set;

analyzing the sampled data set by matching data values from columns of different database tables within the sampled data set to determine key relationships between columns of the database tables within and across the selected one or more databases, wherein the key relationships between the columns of the database tables are determined without key relationships between the columns of the database tables being known beforehand; and

retrieving data from a plurality of the database tables of the selected one or more databases based on the determined key relationships.

2. The method of claim 1, wherein said analysis further comprises determining one or more primary key-foreign key relationships between the database tables within and across the selected one or more databases.

3. The method of claim 1, further comprising:

identifying for analysis the data values that do not produce the same function value to form an unsampled data set, unless the data values are in a high cardinality column; and

analyzing the unsampled data set with the sampled data set to determine relationships between the database tables within and across the selected one or more databases.

4. The method of claim 3, wherein a column is a high cardinality column if a number of data values in the column that produce the same function value exceeds a predetermined cardinality threshold.

5. The method of claim 3, wherein a column is a high cardinality column if a number of unique data values in the column divided by the number of data values in the column exceeds a predetermined selectivity threshold.

6. The method of claim 1, wherein the function is a hash function.

7. A computer program product for analyzing data within one or more databases, comprising:

a computer readable storage medium having computer readable program code embodied therewith, the computer readable program code comprising computer readable program code configured to;

select one or more databases for analysis, each database comprising one or more database tables with one or more columns including one or more data values;

apply a function to each data value of one or more columns in the database tables within the selected one or more databases, wherein the function produces different function values for the data values limited to a predetermined range;

identify for analysis the data values producing a same function value within the predetermined range to form a sampled data set;

analyze the sampled data set by matching data values from columns of different database tables within the sampled data set to determine key relationships between columns of the database tables within and across the selected one or more databases, wherein the key relationships between the columns of the database tables are determined without key relationships between the columns of the database tables being known beforehand; and

retrieve data from a plurality of the database tables of the selected one or more databases based on the determined key relationships.

8. The computer program product of claim 7, wherein the computer readable program code is further configured to:

determine one or more primary key-foreign key relationships between the database tables within and across the selected one or more databases.

9. The computer program product of claim 7, wherein the computer readable program code being further configured to:

identify for analysis the data values that do not produce the same function value to form an unsampled data set, unless the data values are in a high cardinality column; and

analyze the unsampled data set with the sampled data set to determine relationships between the database tables within and across the selected one or more databases.

10. The computer program product of claim 9, wherein a column is a high cardinality column if a number of data values in the column that produce the same function value exceeds a predetermined cardinality threshold.

11. The computer program product of claim 9, wherein a column is a high cardinality column if a number of unique data values in the column divided by the number of data values in the column exceeds a predetermined selectivity threshold.

12. The computer program product of claim 7, wherein the function is a hash function.

13. A system for analyzing data within one or more databases, comprising:

one or more databases, each database comprising one or more database tables with one or more columns including one or more data values stored in memory; and

a processor configured with logic to;

select one or more databases from the one or more databases for analysis;

apply a function to each data value of one or more columns in the database tables within the selected one or more databases, wherein the function produces different function values for the data values limited to a predetermined range;

identify for analysis the data values producing a same function value within the predetermined range to form a sampled data set;

analyze the sampled data set by matching data values from columns of different database tables within the sampled data set to determine key relationships between columns of the database tables within and across the selected one or more databases, wherein the key relationships between the columns of the database tables are determined without key relationships between the columns of the database tables being known beforehand; and

retrieve data from a plurality of the database tables of the selected one or more databases based on the determined key relationships.

14. The system of claim 13, wherein the processor being further configured with logic to:

determine one or more primary key-foreign key relationships between the database tables within and across the selected one or more databases.

15. The system of claim 13, wherein the processor being further configured with logic to:

identify for analysis the data values that do not produce the same function value to form an unsampled data set, unless the data values are in a high cardinality column; and

analyze the unsampled data set with the sampled data set to determine relationships between the database tables within and across the selected one or more databases.

16. The system of claim 15, wherein a column is a high cardinality column if a number of data values in the column that produce the same function value exceeds a predetermined cardinality threshold, or if a number of unique data values in the column divided by the number of data values in the column exceeds a predetermined selectivity threshold.

17. The system of claim 13, wherein the function is a hash function.