[SPARK-11012] Canonicalize view definitions

• Type: New Feature
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.0.0
• Fix Version/s: 2.0.0
• Component/s: SQL
• Target Version/s: 2.0.0
• Assignee: Yin Huai
• Reporter: Yin Huai
• Created: 08/Oct/15 19:53
• Updated: 24/Oct/18 07:11
• Resolved: 27/Apr/16 17:29

Description

In SPARK-10337, we added the first step of supporting view natively, which is basically wrapping the original view definition SQL text with an extra SELECT and then store the wrapped SQL text into metastore. This approach suffers at least two issues:

1. Switching current database may break view queries
2. HiveQL doesn't allow CTE as subquery, thus CTE can't be used in view definition

To fix these issues, we need to canonicalize the view definition. For example, for a SQL string

SELECT a, b FROM table

we will save this text to Hive metastore as

SELECT `table`.`a`, `table`.`b` FROM `currentDB`.`table`

The core infrastructure of this work is SQL query string generation (SPARK-12593). Namely, converting resolved logical query plans back to canonicalized SQL query strings. PR #10541 set up basic infrastructure of SQL generation, but more language structures need to be supported.

PR #10541 added round-trip testing infrastructure for SQL generation. All queries tested by test suites extending HiveComparisonTest are executed in the following order:

1. Parsing query string to logical plan
2. Converting resolved logical plan back to canonicalized SQL query string
3. Executing generated SQL query string
4. Comparing query results with golden answers

Note that not all resolved logical query plan can be converted back to SQL query string. Either because it consists of some language structure that has not been supported yet, or it doesn't have a SQL representation inherently (e.g. query plans built on top of local Scala collections).

If a logical plan is inconvertible, HiveComparisonTest falls back to its original behavior, namely executing the original SQL query string and compare the results with golden answers.

SQL generation details are logged and can be found in sql/hive/target/unit-tests.log (log level should be at least DEBUG).

Issue Links

• is duplicated by: SPARK-11148 Unable to create views
• is related to: SPARK-25797 Views created via 2.1 cannot be read via 2.2+
• is related to: SPARK-18209 More robust view canonicalization without full SQL expansion
• relates to: SPARK-12593 Convert basic resolved logical plans back to SQL query strings
• relates to: SPARK-11148 Unable to create views
• relates to: SPARK-12726 ParquetConversions doesn't always propagate metastore table identifier to ParquetRelation
• relates to: SPARK-14038 Enable native view by default
• relates to: SPARK-16576 Move plan SQL generation code from SQLBuilder into logical operators