Database consistent sample data extraction
Various embodiments of systems and methods for database consistent sample data extraction are described herein. The technique uses production data as input and outputs sample data in the same relational schema while preserving the integrity of joins of the different tables in the schema. For a given relational schema, the master tables are found. Then a subset is created by placing selection criteria in a query defining how to sample the data for these master tables. Following the joins, the dependent tables are added to the query automatically.
1. A computer implemented method for database consistent sample data extraction comprising:
receiving a selection of a source database system;
receiving a selection of one or more tables from the source database system;
identifying one or more master tables from the one or more selected tables, the one or more master tables connected to one or more dependent tables by:
defining oriented cardinalities of tables from a schema comprising the selected tables and their related tables; and
defining paths using the oriented cardinalities;
receiving a selection of sample data extraction variant for extraction of sample data from the one or more master tables;
filtering the one or more dependent tables to keep valid joins between the sample data of the one or more master tables and the one or more dependent tables by following join cardinalities to reach the one or more dependent tables starting from the one or more master tables; and
generating a sample database from the sample data of the one or more master tables and the filtered dependent tables.
2. The method of claim 1 , wherein the sample data extraction variant for extraction of sample data from the master tables is selected from the group consisting of column value selection, range of values selection, and random rows selection.
3. The method of claim 1 , further comprising receiving a selection of sensitive data to be obfuscated from the sample data of the one or more master tables and the one or more dependent tables.
4. The method of claim 1 , further comprising obfuscating sensitive data from the sample data of the one or more master tables and the one or more dependent tables.
5. The method of claim 4 , further comprising:
identifying column contents of the sample data; and
obfuscating columns of data from the sample data identified to fall within a predefined set of sensitive data.
6. A computer system for data visualization and interaction including at least one processor for executing program code and memory, the system comprising:
a source database with one or more fact tables;
an input device to provide user selection of one or more selected tables from the fact tables;
an identifier module to identify one or more master tables from the one or more selected tables, the one or more master tables connected to one or more dependent tables by:
defining oriented cardinalities of tables from a schema comprising the selected tables and their related tables; and
defining paths using the oriented cardinalities;
an extraction module to extract sample data from the one or more master tables;
a filtering module to filter the one or more dependent tables to keep valid joins between the sample data of the one or more master tables and the one or more dependent tables by following join cardinalities to reach the one or more dependent tables starting from the one or more master tables; and
a generating module to generate a sample database from the sample data of the one or more master tables and the filtered dependent tables.
7. The method of claim 6 , wherein the extraction module uses sample data extraction variants for extraction of sample data from the one or more master tables.
8. The system of claim 6 , further comprising an obfuscating module to obfuscate sensitive data from the sample data of the one or more master tables and the one or more dependent tables.
9. The system of claim 8 , wherein the obfuscating module is operable to:
identify column contents of the sample data; and
obfuscate columns of data from the sample data identified to fall within a predefined set of sensitive data.
10. An article of manufacture including a non-transitory computer readable storage medium to tangibly store instructions, which when executed by a computer, cause the computer to:
receive a selection of a source database system;
receive a selection of one or more tables from the source database system;
identify one or more master tables from the one or more selected tables, the one or more master tables connected to one or more dependent tables by:
defining oriented cardinalities of tables from a schema comprising the selected tables and their related tables; and
defining paths using the oriented cardinalities;
receive a selection of sample data extraction variant for extraction of sample data from the one or more master tables;
filter the one or more dependent tables to keep valid joins between the sample data of the one or more master tables and the one or more dependent tables by following join cardinalities to reach the one or more dependent tables starting from the one or more master tables; and
generate a sample database from the sample data of the one or more master tables and the filtered dependent tables.
11. The article of manufacture of claim 10 , further comprising instructions, which when executed by a computer, cause the computer to receive a selection of sample data extraction variant, wherein the sample data extraction variant is selected from the group consisting of column value selection, range of values selection, and random rows selection.
12. The article of manufacture of claim 10 , further comprising instructions, which when executed by a computer, cause the computer to receive a selection of sensitive data to be obfuscated from the sample data of the one or more master tables and the one or more dependent tables.
13. The article of manufacture of claim 10 , further comprising instructions, which when executed by a computer, cause the computer to obfuscate sensitive data from the sample data of the one or more master tables and the one or more dependent tables.
14. The article of manufacture of claim 13 , further comprising instructions, which when executed by a computer, cause the computer to:
identify column contents of the of sample data; and
obfuscate columns of data from the sample data identified to fall within a predefined set of sensitive data.