gengliangwang commented on a change in pull request #24938: [SPARK-27946][SQL]
Hive DDL to Spark DDL conversion USING "show create table"
URL: https://github.com/apache/spark/pull/24938#discussion_r373726907
##########
File path:
sql/hive/src/test/scala/org/apache/spark/sql/hive/HiveShowCreateTableSuite.scala
##########
@@ -188,23 +204,297 @@ class HiveShowCreateTableSuite extends
ShowCreateTableSuite with TestHiveSinglet
}
assert(cause.getMessage.contains(" - partitioned view"))
+
+ val causeForSpark = intercept[AnalysisException] {
+ sql("SHOW CREATE TABLE v1 AS SERDE")
+ }
+
+ assert(causeForSpark.getMessage.contains(" - partitioned view"))
}
}
}
test("SPARK-24911: keep quotes for nested fields in hive") {
withTable("t1") {
- val createTable = "CREATE TABLE `t1`(`a` STRUCT<`b`: STRING>) USING hive"
+ val createTable = "CREATE TABLE `t1` (`a` STRUCT<`b`: STRING>) USING
hive"
sql(createTable)
val shownDDL = getShowDDL("SHOW CREATE TABLE t1")
assert(shownDDL == createTable.dropRight(" USING hive".length))
- checkCreateTable("t1")
+ 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}")
}
}
private def createRawHiveTable(ddl: String): Unit = {
hiveContext.sharedState.externalCatalog.unwrapped.asInstanceOf[HiveExternalCatalog]
.client.runSqlHive(ddl)
}
+
+ private def checkCreateSparkTableAsHive(tableName: String): Unit = {
+ val table = TableIdentifier(tableName, Some("default"))
+ val db = table.database.get
+ val hiveTable = spark.sharedState.externalCatalog.getTable(db, table.table)
+ val sparkDDL = sql(s"SHOW CREATE TABLE
${table.quotedString}").head().getString(0)
+ // Drops original Hive table.
+ sql(s"DROP TABLE ${table.quotedString}")
+
+ try {
+ // Creates Spark datasource table using generated Spark DDL.
+ sql(sparkDDL)
+ val sparkTable = spark.sharedState.externalCatalog.getTable(db,
table.table)
+ checkHiveCatalogTables(hiveTable, sparkTable)
+ } finally {
+ sql(s"DROP TABLE IF EXISTS ${table.table}")
+ }
+ }
+
+ private def checkHiveCatalogTables(hiveTable: CatalogTable, sparkTable:
CatalogTable): Unit = {
+ def normalize(table: CatalogTable): CatalogTable = {
+ val nondeterministicProps = Set(
+ "CreateTime",
+ "transient_lastDdlTime",
+ "grantTime",
+ "lastUpdateTime",
+ "last_modified_by",
+ "last_modified_time",
+ "Owner:",
+ // The following are hive specific schema parameters which we do not
need to match exactly.
+ "totalNumberFiles",
+ "maxFileSize",
+ "minFileSize"
+ )
+
+ table.copy(
+ createTime = 0L,
+ lastAccessTime = 0L,
+ properties =
table.properties.filterKeys(!nondeterministicProps.contains(_)),
+ stats = None,
+ ignoredProperties = Map.empty,
+ storage = table.storage.copy(properties = Map.empty),
+ provider = None,
+ tracksPartitionsInCatalog = false
+ )
+ }
+
+ def fillSerdeFromProvider(table: CatalogTable): CatalogTable = {
+ table.provider.flatMap(HiveSerDe.sourceToSerDe(_)).map { hiveSerde =>
+ val newStorage = table.storage.copy(
+ inputFormat = hiveSerde.inputFormat,
+ outputFormat = hiveSerde.outputFormat,
+ serde = hiveSerde.serde
+ )
+ table.copy(storage = newStorage)
+ }.getOrElse(table)
+ }
+
+ assert(normalize(fillSerdeFromProvider(sparkTable)) ==
normalize(hiveTable))
+ }
+
+ test("simple hive table in Spark DDL") {
+ withTable("t1") {
+ sql(
+ s"""
+ |CREATE TABLE t1 (
+ | c1 STRING COMMENT 'bla',
+ | c2 STRING
+ |)
+ |TBLPROPERTIES (
+ | 'prop1' = 'value1',
+ | 'prop2' = 'value2'
+ |)
+ |STORED AS orc
+ """.stripMargin
+ )
+
+ checkCreateSparkTableAsHive("t1")
+ }
+ }
+
+ test("show create table as serde can't work on data source table") {
+ withTable("t1") {
+ sql(
+ s"""
+ |CREATE TABLE t1 (
+ | c1 STRING COMMENT 'bla',
+ | c2 STRING
+ |)
+ |USING orc
+ """.stripMargin
+ )
+
+ val cause = intercept[AnalysisException] {
+ checkCreateHiveTableOrView("t1")
+ }
+
+ assert(cause.getMessage.contains("Use `SHOW CREATE TABLE` without `AS
SERDE` instead"))
+ }
+ }
+
+ test("simple external hive table in Spark DDL") {
+ withTempDir { dir =>
+ withTable("t1") {
+ sql(
+ s"""
+ |CREATE TABLE t1 (
+ | c1 STRING COMMENT 'bla',
+ | c2 STRING
+ |)
+ |LOCATION '${dir.toURI}'
+ |TBLPROPERTIES (
+ | 'prop1' = 'value1',
+ | 'prop2' = 'value2'
+ |)
+ |STORED AS orc
+ """.stripMargin
+ )
+
+ checkCreateSparkTableAsHive("t1")
+ }
+ }
+ }
+
+ test("hive table with STORED AS clause in Spark DDL") {
Review comment:
nit: a test case with nested fields would be great.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
[email protected]
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
