Recommending Join Queries via Query Log Analysis
Recommending Join Queries via Query Log Analysis
复制标题
DOI：
10.1109/icde.2009.122
复制
发表时间：
2009-03
期刊：
影响因子：
--
通讯作者：
Xiaoyan Yang;Cecilia M. Procopiuc;D. Srivastava
中科院分区：
文献类型：
--
作者：
Xiaoyan Yang;Cecilia M. Procopiuc;D. Srivastava
业务数据分析师通常使用对企业数据库的复杂即席连接查询来理解和分析各种企业范围的流程。然而，有效地制定此类查询对于人类用户来说是一项具有挑战性的任务，尤其是在具有大型异构模式的数据库上。在本文中，我们提出了一种基于输入输出规范（即施加选择条件的输入表和属性值必须在查询结果中的输出表）自动创建连接查询推荐的新颖方法。推荐的连接查询图包括（i）“中间”表，以及（ii）通过中间表连接输入和输出表的连接条件。我们的方法基于分析企业数据库上的现有查询日志。借鉴程序切片技术，提取部分对于影响给定变量值的程序，我们首先从日志中的每个查询中提取“查询切片”。给定用户规范，我们然后重新组合适当的切片以创建新的连接查询图，该图通过中间表连接输入和输出表集。我们提出并研究了几种质量度量，以便能够在多种可能性中选择一个好的连接查询图。每个度量都表达了一个直观的概念，即日志中应该有足够的证据来支持我们对连接查询图的推荐。我们使用实际企业数据库系统的日志进行了广泛的研究，以证明我们推荐连接查询的新方法的可行性。
Complex ad hoc join queries over enterprise databases are commonly used by business data analysts to understand and analyze a variety of enterprise-wide processes. However, effectively formulating such queries is a challenging task for human users, especially over databases that have large, heterogeneous schemas. In this paper, we propose a novel approach to automatically create join query recommendations based on input-output specifications (i.e.,input tables on which selection conditions are imposed, and output tables whose attribute values must be in the result of the query).The recommended join query graph includes (i) "intermediate'' tables, and (ii) join conditions that connect the input and output tables via the intermediate tables. Our method is based on analyzing an existing query log over the enterprise database. Borrowing from program slicing techniques, which extract parts of a program that affect the value of a given variable, we first extract "query slices'' from each query in the log. Given a user specification, we then re-combine appropriate slices to create a new join query graph, which connects the sets of input and output tables via the intermediate tables. We propose and study several quality measures to enable choosing a good join query graph among the many possibilities. Each measure expresses an intuitive notion that there should be sufficient evidence in the log to support our recommendation of the join query graph. We conduct an extensive study using the log of an actual enterprise database system to demonstrate the viability of our novel approach for recommending join queries.