Analysis of database query logs
Some embodiments provide a method for analyzing database queries performed on a database. The method receives log data associated with queries that were performed on the database, wherein the log data includes multiple sub-tables that each include a set of data entries. Based on the log data, the method assembles a set of queries that were performed on the database, where different queries in the set are assembled by combining different subsets of data entries from the sub-tables. The method performs a query interpretation operation on each query to quantify the impact of performing the assembled set of queries on the database.
1. A method for analyzing database queries performed on a first database as part of a migration process that assesses whether to migrate data from the first database to a second database, the method comprising:
receiving log data associated with queries that were performed on the first database, wherein the log data comprises a plurality of sub-tables each comprising a set of data entries;
based on the log data, assembling a set of queries that were performed on the first database, wherein different queries in the set are assembled by combining different subsets of data entries from the sub-tables;
performing a query interpretation operation on each query to identify a set of workload attributes that quantify the impact of performing the assembled set of queries on the first database; and
presenting, in a report for display in a user interface, the set of workload attributes as data for a user to view in order to assess whether the second database should be selected as a target database to which data from the first database is migrated.
2. The method of claim 1 , wherein assembling a particular query comprises combining at least two entries from different sub-tables.
3. The method of claim 1 , wherein assembling a particular query comprises combining at least two entries from a single sub-table.
4. The method of claim 1 further comprising removing duplicate queries from the assembled set of queries, to prevent inflating the quantified impact of performing the assembled set of queries on the first database.
5. The method of claim 4 , wherein removing duplicate queries comprises comparing a semantic structure of a first query to a semantic structure of a second query in the set and determining that the semantic structure of the first query is identical to the semantic structure of the second query.
6. The method of claim 4 , wherein removing duplicate queries comprises comparing a text of a first query to a text of a second query in the set and determining that the text of the first query is identical to the text of the second query.
7. The method of claim 4 , wherein removing duplicate queries comprises storing in a metadata storage a set of metadata associated with each removed duplicate query.
8. The method of claim 1 , wherein the log data further comprises a set of identifiers associated with each query, wherein assembling the set of queries comprises sorting the queries by at least one identifier, wherein the set of identifiers comprises an application identifier, a session identifier, a user identifier, and a start time identifier.
9. The method of claim 1 , wherein performing the query interpretation operation on a particular query comprises identifying a set of components of the query that are used to calculate a complexity indicator, said complexity indicator representing a complexity expression of the subset of queries.
10. The method of claim 9 , further comprising identifying an error from the query interpretation operation for a particular query, said error associated with a non-standard Structured Query Language (SQL) feature in the particular query.
11. A non-transitory machine readable medium storing a program which when executed by at least one processing unit analyzes database queries performed on a first database as part of a migration process that assesses whether to migrate data from the first database to a second database, the program comprising sets of instructions for:
receiving log data associated with queries that were performed on the first database, wherein the log data comprises a plurality of sub-tables each comprising a set of data entries;
based on the log data, assembling a set of queries that were performed on the first database, wherein different queries in the set are assembled by combining different subsets of data entries from the sub-tables;
performing a query interpretation operation on each query to identify a set of workload attributes that quantify the impact of performing the assembled set of queries on the first database; and
presenting, in a report for display in a user interface, the set of workload attributes as data for a user to view in order to assess whether the second database should be selected as a target database to which data from the first database is migrated.
12. The non-transitory machine readable medium of claim 11 , wherein the set of instructions for assembling a particular query comprises a set of instructions for combining at least two entries from different sub-tables.
13. The non-transitory machine readable medium of claim 11 , wherein the set of instructions for assembling a particular query comprises a set of instructions for combining at least two entries from a single sub-table.
14. The non-transitory machine readable medium of claim 11 , the program further comprising a set of instructions for removing duplicate queries from the assembled set of queries, to prevent inflating the quantified impact of performing the assembled set of queries on the first database.
15. The non-transitory machine readable medium of claim 14 , wherein the set of instructions for removing duplicate queries comprises sets of instructions for comparing a semantic structure of a first query to a semantic structure of a second query in the set and determining that the semantic structure of the first query is identical to the semantic structure of the second query.
16. The non-transitory machine readable medium of claim 14 , wherein the set of instructions for removing duplicate queries comprises a set of instructions comparing a text of a first query to a text of a second query in the set and determining that the text of the first query is identical to the text of the second query.
17. The method of claim 14 , wherein removing duplicate queries comprises storing in a metadata storage a set of metadata associated with each removed duplicate query.
18. The non-transitory machine readable medium of claim 11 , wherein the log data further comprises a set of identifiers associated with each query, wherein the set of instructions for assembling the set of queries comprises a set of instructions for sorting the queries by at least one identifier, wherein the set of identifiers comprises an application identifier, a session identifier, a user identifier, and a start time identifier.
19. The non-transitory machine readable medium of claim 11 , wherein the set of instructions for performing the query interpretation operation on a particular query comprises a set of instructions for identifying a set of components of the query that are used to calculate a complexity indicator, said complexity indicator representing a complexity expression of the subset of queries.
20. The non-transitory machine readable medium of claim 19 , the program further comprising a set of instructions for identifying an error from the query interpretation operation for a particular query, said error associated with a non-standard Structured Query Language (SQL) feature in the particular query.