You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spark.apache.org by we...@apache.org on 2020/04/06 05:36:21 UTC
[spark] branch branch-3.0 updated: [SPARK-31224][SQL] Add view
support to SHOW CREATE TABLE
This is an automated email from the ASF dual-hosted git repository.
wenchen pushed a commit to branch branch-3.0
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/branch-3.0 by this push:
new dc552a9 [SPARK-31224][SQL] Add view support to SHOW CREATE TABLE
dc552a9 is described below
commit dc552a9907f716efff17d7f1f3d3668d91bc50db
Author: Liang-Chi Hsieh <vi...@gmail.com>
AuthorDate: Mon Apr 6 05:34:59 2020 +0000
[SPARK-31224][SQL] Add view support to SHOW CREATE TABLE
### What changes were proposed in this pull request?
For now `SHOW CREATE TABLE` command doesn't support views, but `SHOW CREATE TABLE AS SERDE` supports it. Since the views syntax are the same between Hive DDL and Spark DDL, we should be able to support views in both two commands.
This is Hive syntax for creating views:
```
CREATE VIEW [IF NOT EXISTS] [db_name.]view_name [(column_name [COMMENT column_comment], ...) ]
[COMMENT view_comment]
[TBLPROPERTIES (property_name = property_value, ...)]
AS SELECT ...;
```
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-CreateView
This is Spark syntax for creating views:
```
CREATE [OR REPLACE] [[GLOBAL] TEMPORARY] VIEW [IF NOT EXISTS [db_name.]view_name
create_view_clauses
AS query;
```
https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-ddl-create-view.html
Looks like it is the same. We could support views in both commands.
This patch proposes to add views support to `SHOW CREATE TABLE`.
### Why are the changes needed?
To extend the view support of `SHOW CREATE TABLE`, so users can use `SHOW CREATE TABLE` to show Spark DDL for views.
### Does this PR introduce any user-facing change?
Yes. `SHOW CREATE TABLE` can be used to show Spark DDL for views.
### How was this patch tested?
Unit tests.
Closes #27984 from viirya/spark-view.
Authored-by: Liang-Chi Hsieh <vi...@gmail.com>
Signed-off-by: Wenchen Fan <we...@databricks.com>
(cherry picked from commit d782a1c4565c401a02531db3b8aa3cb6fc698fb1)
Signed-off-by: Wenchen Fan <we...@databricks.com>
---
.../spark/sql/execution/command/tables.scala | 101 ++++++++++++---------
.../sql-tests/inputs/show-create-table.sql | 12 ++-
.../sql-tests/results/show-create-table.sql.out | 51 +++++++----
.../apache/spark/sql/ShowCreateTableSuite.scala | 20 ++--
.../spark/sql/hive/HiveShowCreateTableSuite.scala | 100 +++++++++-----------
5 files changed, 151 insertions(+), 133 deletions(-)
diff --git a/sql/core/src/main/scala/org/apache/spark/sql/execution/command/tables.scala b/sql/core/src/main/scala/org/apache/spark/sql/execution/command/tables.scala
index 61955ba..2c9ca36 100644
--- a/sql/core/src/main/scala/org/apache/spark/sql/execution/command/tables.scala
+++ b/sql/core/src/main/scala/org/apache/spark/sql/execution/command/tables.scala
@@ -1057,6 +1057,42 @@ trait ShowCreateTableCommandBase {
protected def concatByMultiLines(iter: Iterable[String]): String = {
iter.mkString("(\n ", ",\n ", ")\n")
}
+
+ protected def showCreateView(metadata: CatalogTable, builder: StringBuilder): Unit = {
+ showViewDataColumns(metadata, builder)
+ showTableComment(metadata, builder)
+ showViewProperties(metadata, builder)
+ showViewText(metadata, builder)
+ }
+
+ private def showViewDataColumns(metadata: CatalogTable, builder: StringBuilder): Unit = {
+ if (metadata.schema.nonEmpty) {
+ val viewColumns = metadata.schema.map { f =>
+ val comment = f.getComment()
+ .map(escapeSingleQuotedString)
+ .map(" COMMENT '" + _ + "'")
+
+ // view columns shouldn't have data type info
+ s"${quoteIdentifier(f.name)}${comment.getOrElse("")}"
+ }
+ builder ++= concatByMultiLines(viewColumns)
+ }
+ }
+
+ private def showViewProperties(metadata: CatalogTable, builder: StringBuilder): Unit = {
+ val viewProps = metadata.properties.filterKeys(!_.startsWith(CatalogTable.VIEW_PREFIX))
+ if (viewProps.nonEmpty) {
+ val props = viewProps.map { case (key, value) =>
+ s"'${escapeSingleQuotedString(key)}' = '${escapeSingleQuotedString(value)}'"
+ }
+
+ builder ++= s"TBLPROPERTIES ${concatByMultiLines(props)}"
+ }
+ }
+
+ private def showViewText(metadata: CatalogTable, builder: StringBuilder): Unit = {
+ builder ++= metadata.viewText.mkString("AS ", "", "\n")
+ }
}
/**
@@ -1100,10 +1136,6 @@ case class ShowCreateTableCommand(table: TableIdentifier)
)
}
- if (tableMetadata.tableType == VIEW) {
- throw new AnalysisException("Hive view isn't supported by SHOW CREATE TABLE")
- }
-
if ("true".equalsIgnoreCase(tableMetadata.properties.getOrElse("transactional", "false"))) {
throw new AnalysisException(
"SHOW CREATE TABLE doesn't support transactional Hive table. " +
@@ -1111,10 +1143,26 @@ case class ShowCreateTableCommand(table: TableIdentifier)
"to show Hive DDL instead.")
}
- convertTableMetadata(tableMetadata)
+ if (tableMetadata.tableType == VIEW) {
+ tableMetadata
+ } else {
+ convertTableMetadata(tableMetadata)
+ }
}
- val stmt = showCreateDataSourceTable(metadata)
+ val builder = StringBuilder.newBuilder
+
+ val stmt = if (tableMetadata.tableType == VIEW) {
+ builder ++= s"CREATE VIEW ${table.quotedString} "
+ showCreateView(metadata, builder)
+
+ builder.toString()
+ } else {
+ builder ++= s"CREATE TABLE ${table.quotedString} "
+
+ showCreateDataSourceTable(metadata, builder)
+ builder.toString()
+ }
Seq(Row(stmt))
}
@@ -1194,18 +1242,13 @@ case class ShowCreateTableCommand(table: TableIdentifier)
}
}
- private def showCreateDataSourceTable(metadata: CatalogTable): String = {
- val builder = StringBuilder.newBuilder
-
- builder ++= s"CREATE TABLE ${table.quotedString} "
+ private def showCreateDataSourceTable(metadata: CatalogTable, builder: StringBuilder): Unit = {
showDataSourceTableDataColumns(metadata, builder)
showDataSourceTableOptions(metadata, builder)
showDataSourceTableNonDataColumns(metadata, builder)
showTableComment(metadata, builder)
showTableLocation(metadata, builder)
showTableProperties(metadata, builder)
-
- builder.toString()
}
}
@@ -1264,10 +1307,7 @@ case class ShowCreateTableAsSerdeCommand(table: TableIdentifier)
builder ++= s"CREATE$tableTypeString ${table.quotedString}"
if (metadata.tableType == VIEW) {
- showViewDataColumns(metadata, builder)
- showTableComment(metadata, builder)
- showViewProperties(metadata, builder)
- showViewText(metadata, builder)
+ showCreateView(metadata, builder)
} else {
showHiveTableHeader(metadata, builder)
showTableComment(metadata, builder)
@@ -1280,35 +1320,6 @@ case class ShowCreateTableAsSerdeCommand(table: TableIdentifier)
builder.toString()
}
- private def showViewDataColumns(metadata: CatalogTable, builder: StringBuilder): Unit = {
- if (metadata.schema.nonEmpty) {
- val viewColumns = metadata.schema.map { f =>
- val comment = f.getComment()
- .map(escapeSingleQuotedString)
- .map(" COMMENT '" + _ + "'")
-
- // view columns shouldn't have data type info
- s"${quoteIdentifier(f.name)}${comment.getOrElse("")}"
- }
- builder ++= concatByMultiLines(viewColumns)
- }
- }
-
- private def showViewProperties(metadata: CatalogTable, builder: StringBuilder): Unit = {
- val viewProps = metadata.properties.filterKeys(!_.startsWith(CatalogTable.VIEW_PREFIX))
- if (viewProps.nonEmpty) {
- val props = viewProps.map { case (key, value) =>
- s"'${escapeSingleQuotedString(key)}' = '${escapeSingleQuotedString(value)}'"
- }
-
- builder ++= s"TBLPROPERTIES ${concatByMultiLines(props)}"
- }
- }
-
- private def showViewText(metadata: CatalogTable, builder: StringBuilder): Unit = {
- builder ++= metadata.viewText.mkString("AS ", "", "\n")
- }
-
private def showHiveTableHeader(metadata: CatalogTable, builder: StringBuilder): Unit = {
val columns = metadata.schema.filterNot { column =>
metadata.partitionColumnNames.contains(column.name)
diff --git a/sql/core/src/test/resources/sql-tests/inputs/show-create-table.sql b/sql/core/src/test/resources/sql-tests/inputs/show-create-table.sql
index dc77f87..00b46d1 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/show-create-table.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/show-create-table.sql
@@ -74,6 +74,9 @@ CREATE VIEW view_SPARK_30302 (aaa, bbb)
AS SELECT a, b FROM tbl;
SHOW CREATE TABLE view_SPARK_30302 AS SERDE;
+
+SHOW CREATE TABLE view_SPARK_30302;
+
DROP VIEW view_SPARK_30302;
@@ -83,6 +86,9 @@ COMMENT 'This is a comment with \'quoted text\' for view'
AS SELECT a, b FROM tbl;
SHOW CREATE TABLE view_SPARK_30302 AS SERDE;
+
+SHOW CREATE TABLE view_SPARK_30302;
+
DROP VIEW view_SPARK_30302;
@@ -92,13 +98,9 @@ TBLPROPERTIES ('a' = '1', 'b' = '2')
AS SELECT a, b FROM tbl;
SHOW CREATE TABLE view_SPARK_30302 AS SERDE;
-DROP VIEW view_SPARK_30302;
-
--- SHOW CREATE TABLE does not support view
-CREATE VIEW view_SPARK_30302 (aaa, bbb)
-AS SELECT a, b FROM tbl;
SHOW CREATE TABLE view_SPARK_30302;
+
DROP VIEW view_SPARK_30302;
DROP TABLE tbl;
diff --git a/sql/core/src/test/resources/sql-tests/results/show-create-table.sql.out b/sql/core/src/test/resources/sql-tests/results/show-create-table.sql.out
index f5ca1ef..88fef89 100644
--- a/sql/core/src/test/resources/sql-tests/results/show-create-table.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/show-create-table.sql.out
@@ -302,6 +302,17 @@ AS SELECT a, b FROM tbl
-- !query
+SHOW CREATE TABLE view_SPARK_30302
+-- !query schema
+struct<createtab_stmt:string>
+-- !query output
+CREATE VIEW `default`.`view_SPARK_30302` (
+ `aaa`,
+ `bbb`)
+AS SELECT a, b FROM tbl
+
+
+-- !query
DROP VIEW view_SPARK_30302
-- !query schema
struct<>
@@ -332,6 +343,18 @@ AS SELECT a, b FROM tbl
-- !query
+SHOW CREATE TABLE view_SPARK_30302
+-- !query schema
+struct<createtab_stmt:string>
+-- !query output
+CREATE VIEW `default`.`view_SPARK_30302` (
+ `aaa` COMMENT 'comment with \'quoted text\' for aaa',
+ `bbb`)
+COMMENT 'This is a comment with \'quoted text\' for view'
+AS SELECT a, b FROM tbl
+
+
+-- !query
DROP VIEW view_SPARK_30302
-- !query schema
struct<>
@@ -364,29 +387,17 @@ AS SELECT a, b FROM tbl
-- !query
-DROP VIEW view_SPARK_30302
--- !query schema
-struct<>
--- !query output
-
-
-
--- !query
-CREATE VIEW view_SPARK_30302 (aaa, bbb)
-AS SELECT a, b FROM tbl
--- !query schema
-struct<>
--- !query output
-
-
-
--- !query
SHOW CREATE TABLE view_SPARK_30302
-- !query schema
-struct<>
+struct<createtab_stmt:string>
-- !query output
-org.apache.spark.sql.AnalysisException
-Hive view isn't supported by SHOW CREATE TABLE;
+CREATE VIEW `default`.`view_SPARK_30302` (
+ `aaa`,
+ `bbb`)
+TBLPROPERTIES (
+ 'a' = '1',
+ 'b' = '2')
+AS SELECT a, b FROM tbl
-- !query
diff --git a/sql/core/src/test/scala/org/apache/spark/sql/ShowCreateTableSuite.scala b/sql/core/src/test/scala/org/apache/spark/sql/ShowCreateTableSuite.scala
index 0425764..4e85f73 100644
--- a/sql/core/src/test/scala/org/apache/spark/sql/ShowCreateTableSuite.scala
+++ b/sql/core/src/test/scala/org/apache/spark/sql/ShowCreateTableSuite.scala
@@ -188,18 +188,26 @@ abstract class ShowCreateTableSuite extends QueryTest with SQLTestUtils {
if (result.length > 1) result(0) + result(1) else result.head
}
- protected def checkCreateTable(table: String): Unit = {
- checkCreateTableOrView(TableIdentifier(table, Some("default")), "TABLE")
+ protected def checkCreateTable(table: String, serde: Boolean = false): Unit = {
+ checkCreateTableOrView(TableIdentifier(table, Some("default")), "TABLE", serde)
}
- protected def checkCreateView(table: String): Unit = {
- checkCreateTableOrView(TableIdentifier(table, Some("default")), "VIEW")
+ protected def checkCreateView(table: String, serde: Boolean = false): Unit = {
+ checkCreateTableOrView(TableIdentifier(table, Some("default")), "VIEW", serde)
}
- private def checkCreateTableOrView(table: TableIdentifier, checkType: String): Unit = {
+ protected def checkCreateTableOrView(
+ table: TableIdentifier,
+ checkType: String,
+ serde: Boolean): Unit = {
val db = table.database.getOrElse("default")
val expected = spark.sharedState.externalCatalog.getTable(db, table.table)
- val shownDDL = sql(s"SHOW CREATE TABLE ${table.quotedString}").head().getString(0)
+ val shownDDL = if (serde) {
+ sql(s"SHOW CREATE TABLE ${table.quotedString} AS SERDE").head().getString(0)
+ } else {
+ sql(s"SHOW CREATE TABLE ${table.quotedString}").head().getString(0)
+ }
+
sql(s"DROP $checkType ${table.quotedString}")
try {
diff --git a/sql/hive/src/test/scala/org/apache/spark/sql/hive/HiveShowCreateTableSuite.scala b/sql/hive/src/test/scala/org/apache/spark/sql/hive/HiveShowCreateTableSuite.scala
index 50c9018..1e31e8b 100644
--- a/sql/hive/src/test/scala/org/apache/spark/sql/hive/HiveShowCreateTableSuite.scala
+++ b/sql/hive/src/test/scala/org/apache/spark/sql/hive/HiveShowCreateTableSuite.scala
@@ -42,16 +42,43 @@ class HiveShowCreateTableSuite extends ShowCreateTableSuite with TestHiveSinglet
}
test("view") {
- withView("v1") {
- sql("CREATE VIEW v1 AS SELECT 1 AS a")
- checkCreateHiveTableOrView("v1", "VIEW")
+ Seq(true, false).foreach { serde =>
+ withView("v1") {
+ sql("CREATE VIEW v1 AS SELECT 1 AS a")
+ checkCreateView("v1", serde)
+ }
}
}
- test("view with output columns") {
- withView("v1") {
- sql("CREATE VIEW v1 (b) AS SELECT 1 AS a")
- checkCreateHiveTableOrView("v1", "VIEW")
+ test("view with output columns") {
+ Seq(true, false).foreach { serde =>
+ withView("v1") {
+ sql("CREATE VIEW v1 (a, b COMMENT 'b column') AS SELECT 1 AS a, 2 AS b")
+ checkCreateView("v1", serde)
+ }
+ }
+ }
+
+ test("view with table comment and properties") {
+ Seq(true, false).foreach { serde =>
+ withView("v1") {
+ sql(
+ s"""
+ |CREATE VIEW v1 (
+ | c1 COMMENT 'bla',
+ | c2
+ |)
+ |COMMENT 'table comment'
+ |TBLPROPERTIES (
+ | 'prop1' = 'value1',
+ | 'prop2' = 'value2'
+ |)
+ |AS SELECT 1 AS c1, '2' AS c2
+ """.stripMargin
+ )
+
+ checkCreateView("v1", serde)
+ }
}
}
@@ -69,7 +96,7 @@ class HiveShowCreateTableSuite extends ShowCreateTableSuite with TestHiveSinglet
""".stripMargin
)
- checkCreateHiveTableOrView("t1")
+ checkCreateTable("t1", serde = true)
}
}
@@ -89,7 +116,7 @@ class HiveShowCreateTableSuite extends ShowCreateTableSuite with TestHiveSinglet
""".stripMargin
)
- checkCreateHiveTableOrView("t1")
+ checkCreateTable("t1", serde = true)
}
}
}
@@ -109,7 +136,7 @@ class HiveShowCreateTableSuite extends ShowCreateTableSuite with TestHiveSinglet
""".stripMargin
)
- checkCreateHiveTableOrView("t1")
+ checkCreateTable("t1", serde = true)
}
}
@@ -127,7 +154,7 @@ class HiveShowCreateTableSuite extends ShowCreateTableSuite with TestHiveSinglet
""".stripMargin
)
- checkCreateHiveTableOrView("t1")
+ checkCreateTable("t1", serde = true)
}
}
@@ -142,7 +169,7 @@ class HiveShowCreateTableSuite extends ShowCreateTableSuite with TestHiveSinglet
""".stripMargin
)
- checkCreateHiveTableOrView("t1")
+ checkCreateTable("t1", serde = true)
}
}
@@ -164,7 +191,7 @@ class HiveShowCreateTableSuite extends ShowCreateTableSuite with TestHiveSinglet
""".stripMargin
)
- checkCreateHiveTableOrView("t1")
+ checkCreateTable("t1", serde = true)
}
}
@@ -177,7 +204,7 @@ class HiveShowCreateTableSuite extends ShowCreateTableSuite with TestHiveSinglet
|INTO 2 BUCKETS
""".stripMargin
)
- checkCreateHiveTableOrView("t1")
+ checkCreateTable("t1", serde = true)
}
}
@@ -222,27 +249,7 @@ class HiveShowCreateTableSuite extends ShowCreateTableSuite with TestHiveSinglet
val shownDDL = getShowDDL("SHOW CREATE TABLE t1")
assert(shownDDL == "CREATE TABLE `default`.`t1` (`a` STRUCT<`b`: STRING>)")
- checkCreateHiveTableOrView("t1")
- }
- }
-
- /**
- * This method compares the given table with the table created by the DDL generated by
- * `SHOW CREATE TABLE AS SERDE`.
- */
- private def checkCreateHiveTableOrView(tableName: String, checkType: String = "TABLE"): Unit = {
- val table = TableIdentifier(tableName, Some("default"))
- val db = table.database.getOrElse("default")
- val expected = spark.sharedState.externalCatalog.getTable(db, table.table)
- val shownDDL = sql(s"SHOW CREATE TABLE ${table.quotedString} AS SERDE").head().getString(0)
- sql(s"DROP $checkType ${table.quotedString}")
-
- try {
- sql(shownDDL)
- val actual = spark.sharedState.externalCatalog.getTable(db, table.table)
- checkCatalogTables(expected, actual)
- } finally {
- sql(s"DROP $checkType IF EXISTS ${table.table}")
+ checkCreateTable("t1", serde = true)
}
}
@@ -344,7 +351,7 @@ class HiveShowCreateTableSuite extends ShowCreateTableSuite with TestHiveSinglet
)
val cause = intercept[AnalysisException] {
- checkCreateHiveTableOrView("t1")
+ checkCreateTable("t1", serde = true)
}
assert(cause.getMessage.contains("Use `SHOW CREATE TABLE` without `AS SERDE` instead"))
@@ -446,27 +453,6 @@ class HiveShowCreateTableSuite extends ShowCreateTableSuite with TestHiveSinglet
}
}
- test("hive view is not supported by show create table without as serde") {
- withTable("t1") {
- withView("v1") {
- sql("CREATE TABLE t1 (c1 STRING, c2 STRING)")
-
- createRawHiveTable(
- s"""
- |CREATE VIEW v1
- |AS SELECT * from t1
- """.stripMargin
- )
-
- val cause = intercept[AnalysisException] {
- sql("SHOW CREATE TABLE v1")
- }
-
- assert(cause.getMessage.contains("view isn't supported"))
- }
- }
- }
-
test("partitioned, bucketed hive table in Spark DDL") {
withTable("t1") {
sql(
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org