Query-based searching using a virtual table

• US 8,886,617 B2
• Filed: 05/09/2013
• Issued: 11/11/2014
• Est. Priority Date: 02/20/2004
• Status: Active Grant

• Current Assignee: Informatica LLC (Informatica, Inc. (California))
• Original Assignee: Informatica Corporation
• Inventors: Grondin, Richard, Zarouba, Vassili
• Primary Examiner(s): Perveen, Rehana
• Assistant Examiner(s): Hoffler, Raheem
• Application Number:
  US13/891,190
• Publication Number:
  US 20130254171A1
• Time in Patent Office: 551 Days
• Field of Search: 707/693
• US Class Current: 707/693
• CPC Class Codes: G06F 16/2455 Query execution
• CPC Class Codes: G06F 16/256 in federated or virtual databases
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99953 Recoverability

Abstract

A method of searching all tables in a data model is disclosed, using a non-materializing virtual table interface that acts as a view into the underlying data model. The virtual table is virtually built on the fly at query execution time, and maps to all columns and rows within the data model. A query on the virtual table is translated into a set of data model queries for searching the data model, based on columns selected from the virtual table and other specified search parameters, as well as the virtual table definition. The search process works in conjunction with data domains, and uses compaction and tokenization of data.

Claims

1. A computer-implemented method of searching a data model using a virtual table, comprising:

storing in a database a definition of a virtual table that is a view into a data model of the database represented in a domain structure;

receiving a query by the database, the query indicating the virtual table, selecting one or more columns of the virtual table, and specifying one or more search parameters for the query;

translating the received query into a set of data model queries for searching the data model, using the one or more selected columns of the virtual table and the one or more specified search parameters and the virtual table definition;

searching one or more compressed tokenized data segments stored in the data model based on the set of data model queries to produce data model query results; and

processing the data model query results based on the selected one or more columns of the virtual table to produce a result set table comprising a schema column, a table column, a column column, a urowid column, and a value column and a plurality of rows corresponding to each urowid in the result set table.

2. The method of claim 1, wherein the domain structure maps to columns and rows of the data model.

3. The method of claim 1, wherein the virtual columns comprise schema, table, column, urowid, and value.

4. The method of claim 1, wherein the received query is a SQL query.

5. The method of claim 4, wherein the SQL query is a keyword query.

6. The method of claim 1, wherein the received query comprises a parameter specifying one or more type checks.

7. The method of claim 1, wherein the received query comprises a parameter specifying ordering of the result set.

8. The method of claim 1, wherein searching the one or more compressed segments of data values stored in the data model further comprises:

selecting a compacted file that may include a datum corresponding to the set of data model queries, the compacted file created from the one or more compressed tokenized data segments and segment metadata;

accessing the selected compacted file;

selecting a compressed segment from the one or more compressed tokenized data segments using the segment metadata;

generating a decompressed segment from the selected compressed segment; and

searching the decompressed segment to determine if the decompressed segment includes the datum corresponding to the set of data model queries.

9. The method of claim 8, further comprising:

generating a domain structure and tokenized data from archive data;

determining metadata from the tokenized data;

generating a set of bit vectors from the tokenized data; and

creating one or more compacted files from the set of bit vectors, the selected compacted file selected from the one or more compacted files.

10. The method of claim 9, wherein creating one or more compacted files further comprises storing the compressed tokenized data segments in the one or more compacted files.

11. The method of claim 1, wherein the set of data model queries are provided to a separate search process, which performed the searching and returns the data model query results.

12. The method of claim 1, wherein the translating the received query into a set of data model queries for searching the data model further comprises:

parsing received query into search expressions constraints and data model scope constraints;

identifying potential data type constraints in the search expressions constraints; and

defining the scope of the set of data model queries by applying the data model scope constraints and data type constraints to the data model.

13. A system for searching a data model using a virtual table, comprising:

a database storing a definition of a virtual table that is a view into a data model of the database represented in a domain structure;

a virtual table module for receiving a query, the query indicating the virtual table, selecting one or more columns of the virtual table, and specifying one or more search parameters for the query;

a query translation module for translating the received query into a set of data model queries for searching the data model, using the one or more selected columns of the virtual table and the one or more specified search parameters and the virtual table definition;

a search module for searching one or more compressed tokenized data segments stored in the data model based on the set of data model queries to produce data model query results; and

a result processing module for processing the data model query results based on the selected one or more columns of the virtual table to produce a result set table comprising a schema column, a table column, a column column, a urowid column, and a value column and a plurality of rows corresponding to each urowid in the result set table.

14. The system of claim 13, wherein the virtual columns comprise schema, table, column, urowid, and value.

15. The system of claim 13, wherein the received query is a SQL query.

16. The system of claim 13, wherein the received query comprises a parameter specifying one or more type checks.

17. The system of claim 13, wherein the received query comprises a parameter specifying ordering of the result set.

18. The system, of claim 13, the search module further comprising:

an identify files module for selecting a compacted file that may include a datum corresponding to the set of data model queries, the compacted file created from the one or more compressed tokenized data segments and segment metadata; and

a search agent module for accessing the selected compacted file, selecting a compressed segment from the one or more compressed tokenized data segments using the segment metadata, generating a decompressed segment from the selected compressed segment, and searching the decompressed segment to determine if the decompressed segment includes the datum corresponding to the set of data model queries.

19. The system of claim 13, wherein the query translation module is further configured for parsing received query into search expressions constraints and data model scope constraints, identifying potential data type constraints in the search expressions constraints, and defining the scope of the set of data model queries by applying the data model scope constraints and data type constraints to the data model.

20. A computer program product, configured for execution on a computer device, and comprising computer executable instructions for causing the computer device to performed the following steps:

storing in a database a definition of a virtual table that is a view into a data model of the database represented in a domain structure;

receiving a query by the database, the query indicating the virtual table, selecting one or more columns of the virtual table, and specifying one or more search parameters for the query;

translating the received query into a set of data model queries for searching the data model, using the one or more selected columns of the virtual table and the one or more specified search parameters and the virtual table definition;

searching one or more compressed tokenized data segments stored in the data model based on the set of data model queries to produce data model query results; and

processing the data model query results based on the selected one or more columns of the virtual table to produce a result set table comprising a schema column, a table column, a column column, a urowid column, and a value column and a plurality of rows corresponding to each urowid in the result set table.

21. The computer program product of claim 20, wherein the virtual columns comprise schema, table, column, urowid, and value.

22. The computer program product of claim 20, wherein the received query is a SQL query.

23. The computer program product of claim 20, wherein the received query comprises a parameter specifying one or more type checks.

24. The computer program product of claim 20, wherein the received query comprises a parameter specifying ordering of the result set.

25. The computer program product of claim 20, further comprising computer executable instructions for causing the computer device to performed the following steps:

parsing received query into search expressions constraints and data model scope constraints;

identifying potential data type constraints in the search expressions constraints; and

defining the scope of the set of data model queries by applying the data model scope constraints and data type constraints to the data model.