ASSISTED QUERY FORMATION, VALIDATION, AND RESULT PREVIEWING IN A DATABASE HAVING A COMPLEX SCHEMA

• US 20140280287A1
• Filed: 10/18/2013
• Published: 09/18/2014
• Est. Priority Date: 03/17/2013
• Status: Active Grant

• Current Assignee: Alation, Inc.
• Original Assignee: Alation, Inc.
• Inventors: Ganti, Venkatesh, Kalb, Aaron, Niu, Feng, Sangani, Satyen
• Granted Patent: US 8,965,915 B2
• Time in Patent Office: Days
• US Class Current: 707/766
• CPC Class Codes: G06F 16/211 Schema design and management
• CPC Class Codes: G06F 16/219 Managing data history or versioning querying versioned data G06F16/2474; querying temporal data G06F16/2477
• CPC Class Codes: G06F 16/248 Presentation of query results
• CPC Class Codes: G06F 16/3325 Reformulation based on results of preceding query

Abstract

Disclosed are a method, a device and/or a system of assisted query formation, validation, and result previewing in a database having a complex schema. In one aspect, a method of a query editor includes generating a data profile which includes a set of characteristics captured at various granularities of an initial result set generated from an initial query using a processor and a memory. The method determines what a user expects in the initial result set of the initial query and/or a subsequent result set of a subsequent query based on the data profile and/or a heuristically estimated data profile. The method includes enabling the user to evaluate a semantic accuracy of the subsequent query based on the likely expectation of the user as determined through the set of characteristics of the data profile. The set of characteristics may include metadata of the initial query.

Claims

1. A method of a query editor comprising:

generating a data profile comprising a set of characteristics captured at various granularities of an initial result set generated from an initial query using a processor and a memory;

determining what a user expects in at least one of the initial result set of the initial query and the subsequent result set of a subsequent query based on one of the data profile and a heuristically estimated data profile; and

enabling the user to evaluate a semantic accuracy of the subsequent query based on the likely expectation of the user as determined through the set of characteristics of the data profile.

2. The method of claim 1:

wherein the set of characteristics include metadata of the initial query comprising at least one of;

a number of attributes in the initial result set,

a data type of each of the attributes,

a frequency of usage per user,

a uniqueness constraint on the attributes,

a nullability of individual attributes, and

a functional dependency between attributes.

3. The method of claim 1 further comprising:

determining a match ratio between the subsequent query and the initial query; and

presenting at least some of the set of characteristics of the data profile to the user based on the match ratio through the query editor.

4. The method of claim 3:

wherein the set of characteristics include individual row characteristics, and

wherein the data profile includes a random sample of information presented in the initial result set,

to enable the user to perform a cursory examination of at least one of the presented ones of the at least some of the set of characteristics and the random sample of information presented in at least one of the initial result set and the subsequent result set based on the match ratio between the subsequent query and the initial query.

5. The method of claim 4:

wherein the set of characteristics include aggregate statistical characteristics comprising at least one of generic aggregate statistics and scenario aggregate statistics,

wherein the generic aggregate statistics are calculated in a manner that is independent of a set of semantics of the attributes, and

wherein the scenario aggregate statistics are defined through a domain expert and are relevant in a particular scenario to enable the incorporation of a domain-specific interpretation of semantics of each attribute and each set of data of at least one of the initial result set and the subsequent result set.

6. The method of claim 5:

wherein the generic aggregate statistics include;

a number of rows in at least one of the initial result set and the subsequent result set,

a count of distinct values in each attribute,

a distribution of attribute values including frequency per attribute value,

a pattern of attribute values for attribute values, and

a set of functional dependencies among attribute value pairs, and

wherein the generic aggregate value statistics are applicable with no customization by the user.

7. The method of claim 6:

wherein the scenario aggregate statistics are programmed via user-defined aggregate queries are associated with at least one of the initial result set and the subsequent result set,

wherein a syntax of the user-defined aggregate queries are specific to a standard query processing engine, and

wherein a signature of the scenario aggregate statistics create the signature that correspond to a set of table valued functions.

8. The method of the query editor of claim 1 further comprising:

presenting the set of characteristics of the data profile to the user through the query editor based on an auto-complete algorithm in which at least one of the data profile, initial result set and the subsequent result set is predicted when a partial input of at least one of the initial query and the subsequent query in entered in the query editor.

9. The method of claim 1 wherein the set of characteristics of the data profile is presented to the user of the query editor through a profile visualizer module which generates a visual representation of the data profile in at least one of a searchable format, a hierarchical format, and a navigable format.

10. The method of claim 1 further comprising computing the data profile through a result set profiler module through which:

a first set of hash tables to map each attribute value of an attribute to a number of times a particular value is observed in the attribute,

a second set of hash tables to map patterns exhibited by an attribute value to a count of times a pattern is observed in the attribute, and

wherein a pattern is constructed based a regular expression of string-valued attributes.

11. The method of claim 10 further comprising:

permitting the user to provide a user defined table value function using a visual interface through an application programming interface, wherein the application programming interface to consider at least one of a data source, a table, and a table value aggregate query data as an input to be registered with the result set profiler; and

permitting the user to mark the subsequent query as completed after an iterative evaluation of the data profile generated from previous queries.

12. The method of claim 1 further comprising:

generating a sample database having an identical schema as that of an original database using a database sampler module, wherein the sample database comprises a potentially biased random sample of all relations with a reduced set of data;

executing the subsequent query efficiently through the use of the sample database to enable the user to reduce a time in debugging the subsequent query; and

computing the estimated data profile from the sample database.

13. The method of claim 12 further comprising applying a reservoir sampling algorithm to ensure that a constant sample size is maintained and such that each row in the subsequent result set of any subsequent query is selected with a same probability in a manner such that a tuple that is selected by many subsequent queries in a query log is likely to be selected in the potentially biased random sample with a higher probability.

14. The method of claim 13 further comprising:

generating an estimate of an aggregate value based on the potentially biased random sample through a heuristical-estimation algorithm.

15. The method of claim 14 further comprising:

monitoring a behavior of the user in interacting with the set of characteristics of the data profile when the subsequent query is generated; and

refining an presentation algorithm that optimally determines which characteristics to present to the user based in a further query based on a monitored behavior of the user in interacting with at least one of the initial query and the subsequent query in the query editor.

16. A query editor application comprising:

a result set profiler module to generate a data profile comprising a set of characteristics captured at various granularities of an initial result set generated from an initial query using a processor and a memory;

a heuristical-estimation module to determine what a user expects in at least one of the initial result set of the initial query and in a subsequent result set of a subsequent query based on at least one of the data profile and a heuristically estimated data profile; and

a profile visualizer module to enable the user to evaluate a semantic accuracy of the subsequent query based on the likely expectation of the user as determined through the set of characteristics of the data profile.

17. The query editor application of claim 16:

wherein the set of characteristics include metadata of the initial query comprising at least one of;

a number of attributes in the initial result set,

a data type of each of the attributes,

a frequency of usage per user,

a uniqueness constraint on the attributes,

a nullability of individual attributes, and

a functional dependency between attributes.

18. The query editor application of claim 16:

wherein a match ratio between the subsequent query and the initial query is determined, and

wherein at least some of the set of characteristics of the data profile are presented to the user based on the match ratio through the query editor.

19. The query editor application of claim 18:

wherein the set of characteristics include individual row characteristics, and

wherein the data profile includes a random sample of information presented in at least one of the initial result set and the subsequent result set,

to enable the user to perform a cursory examination of at least one of the presented ones of the at least some of the set of characteristics and the random sample of information presented in the initial result set based on the match ratio between the subsequent query and the initial query.

20. The query editor application of claim 19:

wherein the set of characteristics include aggregate statistical characteristics comprising at least one of generic aggregate statistics and scenario aggregate statistics,

wherein the generic aggregate statistics are calculated in a manner that is independent of a set of semantics of the attributes, and

wherein the scenario aggregate statistics are defined through a domain expert and are relevant in a particular scenario to enable the incorporation of a domain-specific interpretation of semantics of each attribute and each set of data of at least one of the initial result set and the subsequent result set.

21. The query editor application of claim 20:

wherein the generic aggregate statistics include;

a number of rows in at least one of the initial result set and the subsequent result set,

a count of distinct values in each attribute,

a distribution of attribute values including frequency per attribute value,

a pattern of attribute values for attribute values, and

a set of functional dependencies among attribute value pairs, and

wherein the generic aggregate value statistics are applicable with no customization by the user.

22. The query editor application of claim 21:

wherein the scenario aggregate statistics are programmed via user-defined aggregate queries are associated with at least one of the initial result set and the subsequent result set,

wherein a syntax of the user-defined aggregate queries are specific to a standard query processing engine, and

wherein a signature of the scenario aggregate statistics create the signature that correspond to a set of table valued functions.

23. The query editor application of claim 16 further comprising:

the set of characteristics of the data profile are presented to the user through the query editor based on an auto-complete algorithm in which at least one of the data profile, initial result set and the subsequent result set is predicted when a partial input of at least one of the initial query and the subsequent query in entered in the query editor.

24. The query editor application of claim 16 wherein the set of characteristics of the data profile is presented to the user of the query editor through a profile visualizer module which generates a visual representation of the data profile in at least one of a searchable format, a hierarchical format, and a navigable format.

25. The query editor application of claim 16 wherein the data profile is computed through a result set profiler module through which:

a first set of hash tables to map each attribute value of an attribute to a number of times a particular value is observed in the attribute,

a second set of hash tables to map patterns exhibited by an attribute value to a count of times a pattern is observed in the attribute, and

wherein a pattern is constructed based a regular expression of string-valued attributes.

26. The query editor application of claim 25:

wherein the user to provide a user defined table value function using a visual interface through an application programming interface,

wherein the application programming interface to consider at least one of a data source, a table, and a table value aggregate query data as an input to be registered with the result set profiler, and

wherein the user is permitted to mark the subsequent query as completed after an iterative evaluation of the data profile generated from previous queries.

27. The query editor application of claim 26 further comprising:

wherein a sample database is generated having an identical schema as that of an original database using a database sampler module, wherein the sample database comprises a potentially biased random sample of all relations with a reduced set of data, and

wherein the subsequent query is executed efficiently through the use of the sample database to enable the user to reduce a time in debugging the subsequent query.

28. The query editor application of claim 27

wherein a reservoir sampling algorithm is applied to ensure that a constant sample size is maintained and such that each row in the subsequent result set of any subsequent query is selected with a same probability in a manner such that a tuple that is selected by many subsequent queries in a query log is likely to be selected in the potentially biased random sample with a higher probability.

29. The method claim 28 wherein an estimate of an aggregate value is generated based on the potentially biased random sample through a heuristical-estimation algorithm.

30. The method of claim 29:

wherein a behavior of the user in interacting with the set of characteristics of the data profile is monitored when the subsequent query is generated, and

wherein a presentation algorithm that optimally determines which characteristics to present to the user is refined based in a further query based on a monitored behavior of the user in interacting with at least one of the initial query and the subsequent query in the query editor.