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 2026/06/11 00:34:06 UTC
(spark) branch branch-4.x updated: [SPARK-53840][SQL] Add AS JSON output support for SHOW TABLES and SHOW TABLE EXTENDED
This is an automated email from the ASF dual-hosted git repository.
cloud-fan pushed a commit to branch branch-4.x
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/branch-4.x by this push:
new 91eb4ec65c02 [SPARK-53840][SQL] Add AS JSON output support for SHOW TABLES and SHOW TABLE EXTENDED
91eb4ec65c02 is described below
commit 91eb4ec65c02cf16c58b66c52475ff882b39679d
Author: Ayush <bi...@hotmail.com>
AuthorDate: Wed Jun 10 17:33:29 2026 -0700
[SPARK-53840][SQL] Add AS JSON output support for SHOW TABLES and SHOW TABLE EXTENDED
### What changes were proposed in this pull request?
Support SHOW TABLES ... [AS JSON] and SHOW TABLE EXTENDED ... [AS JSON] to optionally display table listing metadata in JSON format.
SQL syntax:
```
SHOW TABLES [(IN|FROM) database_name] [[LIKE] pattern] [AS JSON]
SHOW TABLE EXTENDED [(IN|FROM) database_name] LIKE pattern [AS JSON]
```
Output: json_metadata: String
SHOW TABLES AS JSON:
`{"tables":[{"name":"t1","namespace":["db"],"isTemporary":false}]}`
SHOW TABLE EXTENDED ... AS JSON additionally includes catalog and type:
`{"tables":[{"catalog":"spark_catalog","namespace":["db"],"name":"t1","type":"TABLE","isTemporary":false}]}`
### Why are the changes needed?
The existing text-based output of SHOW TABLES requires fragile string parsing for programmatic consumption.
A structured JSON format provides a stable, machine-readable contract for tooling and automation.
### Does this PR introduce _any_ user-facing change?
Yes. Two new SQL syntax variants that return JSON output. Existing commands without AS JSON are unaffected.
SHOW TABLE EXTENDED with both PARTITION and AS JSON is explicitly rejected.
### How was this patch tested?
- Parser tests in `ShowTablesParserSuite` for all AS JSON variants and the PARTITION + AS JSON error case.
- Execution tests in `ShowTablesSuiteBase` covering JSON schema validation, empty databases, EXTENDED output, and temp view inclusion.
- Manual verification in spark-shell
### Was this patch authored or co-authored using generative AI tooling?
No
---
Continuation of #[54824](https://github.com/apache/spark/pull/54824) (closed to move off fork `master` branch for CI)
Closes #56414 from ayushbilala/show-tables-json.
Authored-by: Ayush <bi...@hotmail.com>
Signed-off-by: Wenchen Fan <we...@databricks.com>
(cherry picked from commit 79dcac935b2f18c6605cc14a76eccf9efee161d1)
Signed-off-by: Wenchen Fan <we...@databricks.com>
---
.../src/main/resources/error/error-conditions.json | 5 +
.../spark/sql/catalyst/parser/SqlBaseParser.g4 | 4 +-
.../spark/sql/errors/CompilationErrors.scala | 6 +
.../spark/sql/execution/SparkSqlParser.scala | 32 +++
.../execution/command/ShowTablesJsonCommand.scala | 151 +++++++++++++
.../execution/command/ShowTablesParserSuite.scala | 37 ++++
.../execution/command/ShowTablesSuiteBase.scala | 246 +++++++++++++++++++++
.../sql/execution/command/v1/ShowTablesSuite.scala | 1 +
.../sql/execution/command/v2/ShowTablesSuite.scala | 1 +
9 files changed, 481 insertions(+), 2 deletions(-)
diff --git a/common/utils/src/main/resources/error/error-conditions.json b/common/utils/src/main/resources/error/error-conditions.json
index f4b6b8980059..8205d87eeb6b 100644
--- a/common/utils/src/main/resources/error/error-conditions.json
+++ b/common/utils/src/main/resources/error/error-conditions.json
@@ -8308,6 +8308,11 @@
"<variableName> is a VARIABLE and cannot be updated using the SET statement. Use SET VARIABLE <variableName> = ... instead."
]
},
+ "SHOW_TABLE_EXTENDED_JSON_WITH_PARTITION" : {
+ "message" : [
+ "SHOW TABLE EXTENDED with PARTITION does not support AS JSON output."
+ ]
+ },
"SQL_CURSOR" : {
"message" : [
"SQL cursor operations (DECLARE CURSOR, OPEN, FETCH, CLOSE) are not supported."
diff --git a/sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseParser.g4 b/sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseParser.g4
index 744c472b2017..152ea0764167 100644
--- a/sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseParser.g4
+++ b/sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseParser.g4
@@ -364,9 +364,9 @@ statement
| EXPLAIN (LOGICAL | FORMATTED | EXTENDED | CODEGEN | COST)?
(statement|setResetStatement) #explain
| SHOW TABLES ((FROM | IN) identifierReference)?
- (LIKE? pattern=stringLit)? #showTables
+ (LIKE? pattern=stringLit)? (AS JSON)? #showTables
| SHOW TABLE EXTENDED ((FROM | IN) ns=identifierReference)?
- LIKE pattern=stringLit partitionSpec? #showTableExtended
+ LIKE pattern=stringLit partitionSpec? (AS JSON)? #showTableExtended
| SHOW TBLPROPERTIES table=identifierReference
(LEFT_PAREN key=propertyKeyOrStringLit RIGHT_PAREN)? #showTblProperties
| SHOW COLUMNS (FROM | IN) table=identifierReference
diff --git a/sql/api/src/main/scala/org/apache/spark/sql/errors/CompilationErrors.scala b/sql/api/src/main/scala/org/apache/spark/sql/errors/CompilationErrors.scala
index 6a275b9ad0c1..8ddf82225ca5 100644
--- a/sql/api/src/main/scala/org/apache/spark/sql/errors/CompilationErrors.scala
+++ b/sql/api/src/main/scala/org/apache/spark/sql/errors/CompilationErrors.scala
@@ -53,6 +53,12 @@ private[sql] trait CompilationErrors extends DataTypeErrorsBase {
messageParameters = Map.empty)
}
+ def showTableExtendedJsonWithPartitionError(): AnalysisException = {
+ new AnalysisException(
+ errorClass = "UNSUPPORTED_FEATURE.SHOW_TABLE_EXTENDED_JSON_WITH_PARTITION",
+ messageParameters = Map.empty)
+ }
+
def cannotFindDescriptorFileError(filePath: String, cause: Throwable): AnalysisException = {
new AnalysisException(
errorClass = "PROTOBUF_DESCRIPTOR_FILE_NOT_FOUND",
diff --git a/sql/core/src/main/scala/org/apache/spark/sql/execution/SparkSqlParser.scala b/sql/core/src/main/scala/org/apache/spark/sql/execution/SparkSqlParser.scala
index 5993e86f5cce..bce51f7c23fa 100644
--- a/sql/core/src/main/scala/org/apache/spark/sql/execution/SparkSqlParser.scala
+++ b/sql/core/src/main/scala/org/apache/spark/sql/execution/SparkSqlParser.scala
@@ -1496,6 +1496,38 @@ class SparkSqlAstBuilder extends AstBuilder {
Option(ctx.pattern).map(x => string(visitStringLit(x))))
}
+ /**
+ * Create a [[ShowTablesJsonCommand]] or [[ShowTables]] command.
+ */
+ override def visitShowTables(ctx: ShowTablesContext): LogicalPlan = withOrigin(ctx) {
+ if (ctx.JSON == null) return super.visitShowTables(ctx)
+ val ns = if (ctx.identifierReference() != null) {
+ withIdentClause(ctx.identifierReference, UnresolvedNamespace(_))
+ } else {
+ CurrentNamespace
+ }
+ val pattern = Option(ctx.pattern).map(x => string(visitStringLit(x)))
+ ShowTablesJsonCommand(ns, pattern, isExtended = false)
+ }
+
+ /**
+ * Create a [[ShowTablesJsonCommand]], [[ShowTablesExtended]], or [[ShowTablePartition]] command.
+ */
+ override def visitShowTableExtended(
+ ctx: ShowTableExtendedContext): LogicalPlan = withOrigin(ctx) {
+ val asJson = ctx.JSON != null
+ if (asJson && ctx.partitionSpec != null) {
+ throw QueryCompilationErrors.showTableExtendedJsonWithPartitionError()
+ }
+ if (!asJson || ctx.partitionSpec != null) return super.visitShowTableExtended(ctx)
+ val ns = if (ctx.identifierReference() != null) {
+ withIdentClause(ctx.identifierReference, UnresolvedNamespace(_))
+ } else {
+ CurrentNamespace
+ }
+ ShowTablesJsonCommand(ns, Some(string(visitStringLit(ctx.pattern))), isExtended = true)
+ }
+
override def visitDescribeProcedure(
ctx: DescribeProcedureContext): LogicalPlan = withOrigin(ctx) {
withIdentClause(ctx.identifierReference(), procIdentifier =>
diff --git a/sql/core/src/main/scala/org/apache/spark/sql/execution/command/ShowTablesJsonCommand.scala b/sql/core/src/main/scala/org/apache/spark/sql/execution/command/ShowTablesJsonCommand.scala
new file mode 100644
index 000000000000..7c3ed1564e44
--- /dev/null
+++ b/sql/core/src/main/scala/org/apache/spark/sql/execution/command/ShowTablesJsonCommand.scala
@@ -0,0 +1,151 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.spark.sql.execution.command
+
+import scala.collection.mutable.ArrayBuffer
+
+import org.json4s._
+import org.json4s.jackson.JsonMethods._
+
+import org.apache.spark.SparkException
+import org.apache.spark.sql.{Row, SparkSession}
+import org.apache.spark.sql.catalyst.analysis.ResolvedNamespace
+import org.apache.spark.sql.catalyst.expressions.{Attribute, AttributeReference}
+import org.apache.spark.sql.catalyst.plans.logical.LogicalPlan
+import org.apache.spark.sql.catalyst.util.StringUtils
+import org.apache.spark.sql.connector.catalog.{CatalogV2Util, TableCatalog, TableViewCatalog}
+import org.apache.spark.sql.connector.catalog.CatalogV2Implicits._
+import org.apache.spark.sql.types.{MetadataBuilder, StringType}
+
+/**
+ * The command for `SHOW TABLES AS JSON` and `SHOW TABLE EXTENDED AS JSON`.
+ */
+case class ShowTablesJsonCommand(
+ child: LogicalPlan,
+ pattern: Option[String],
+ isExtended: Boolean,
+ override val output: Seq[Attribute] = Seq(
+ AttributeReference(
+ "json_metadata",
+ StringType,
+ nullable = false,
+ new MetadataBuilder()
+ .putString("comment", "JSON metadata of the tables")
+ .build())()
+ )) extends UnaryRunnableCommand {
+
+ override def run(sparkSession: SparkSession): Seq[Row] = {
+ val jsonOutput = child match {
+ case ResolvedNamespace(catalog, ns, _) =>
+ if (CatalogV2Util.isSessionCatalog(catalog)) {
+ runForSessionCatalog(sparkSession, ns)
+ } else {
+ runForV2Catalog(sparkSession, catalog.asTableCatalog, ns)
+ }
+ case other =>
+ throw SparkException.internalError(
+ s"Unexpected child in ShowTablesJsonCommand: ${other.getClass.getSimpleName}")
+ }
+ Seq(Row(compact(render(jsonOutput))))
+ }
+
+ private def runForSessionCatalog(
+ sparkSession: SparkSession,
+ ns: Seq[String]): JObject = {
+ val sessionCatalog = sparkSession.sessionState.catalog
+ val db = ns.headOption.getOrElse(sessionCatalog.getCurrentDatabase)
+ val tables = pattern
+ .map(p => sessionCatalog.listTables(db, p))
+ .getOrElse(sessionCatalog.listTables(db))
+
+ val jsonTables = tables.map { tableIdent =>
+ val isTemp = sessionCatalog.isTempView(tableIdent)
+ val namespace = tableIdent.database.toList
+
+ if (isExtended) {
+ val tableType = if (isTemp) {
+ "VIEW"
+ } else {
+ sessionCatalog.getTempViewOrPermanentTableMetadata(tableIdent).tableType.name
+ }
+ JObject(
+ "name" -> JString(tableIdent.table),
+ "catalog" -> JString(
+ sparkSession.sessionState.catalogManager.v2SessionCatalog.name()),
+ "namespace" -> JArray(namespace.map(JString(_))),
+ "type" -> JString(tableType),
+ "isTemporary" -> JBool(isTemp)
+ )
+ } else {
+ JObject(
+ "name" -> JString(tableIdent.table),
+ "namespace" -> JArray(namespace.map(JString(_))),
+ "isTemporary" -> JBool(isTemp)
+ )
+ }
+ }.toList
+
+ JObject("tables" -> JArray(jsonTables))
+ }
+
+ private def runForV2Catalog(
+ sparkSession: SparkSession,
+ catalog: TableCatalog,
+ ns: Seq[String]): JObject = {
+ val identifiers = if (!isExtended) {
+ catalog match {
+ case mc: TableViewCatalog =>
+ mc.listTableAndViewSummaries(ns.toArray).map(_.identifier())
+ case _ => catalog.listTables(ns.toArray)
+ }
+ } else {
+ catalog.listTables(ns.toArray)
+ }
+
+ val pat = pattern.getOrElse("*")
+ val filteredIdents = identifiers.filter { ident =>
+ StringUtils.filterPattern(Seq(ident.name()), pat).nonEmpty
+ }
+
+ val jsonRows = new ArrayBuffer[JObject]()
+ filteredIdents.foreach { ident =>
+ val nsArray = JArray(ident.namespace().map(JString(_)).toList)
+ val entry = if (isExtended) {
+ JObject(
+ "name" -> JString(ident.name()),
+ "catalog" -> JString(catalog.name()),
+ "namespace" -> nsArray,
+ "type" -> JString("TABLE"),
+ "isTemporary" -> JBool(false)
+ )
+ } else {
+ JObject(
+ "name" -> JString(ident.name()),
+ "namespace" -> nsArray,
+ "isTemporary" -> JBool(false)
+ )
+ }
+ jsonRows += entry
+ }
+
+ JObject("tables" -> JArray(jsonRows.toList))
+ }
+
+ override protected def withNewChildInternal(newChild: LogicalPlan): LogicalPlan =
+ copy(child = newChild)
+}
diff --git a/sql/core/src/test/scala/org/apache/spark/sql/execution/command/ShowTablesParserSuite.scala b/sql/core/src/test/scala/org/apache/spark/sql/execution/command/ShowTablesParserSuite.scala
index 2a4a49c75ad9..06ac05b48fa2 100644
--- a/sql/core/src/test/scala/org/apache/spark/sql/execution/command/ShowTablesParserSuite.scala
+++ b/sql/core/src/test/scala/org/apache/spark/sql/execution/command/ShowTablesParserSuite.scala
@@ -17,9 +17,11 @@
package org.apache.spark.sql.execution.command
+import org.apache.spark.sql.AnalysisException
import org.apache.spark.sql.catalyst.analysis.{AnalysisTest, CurrentNamespace, UnresolvedNamespace, UnresolvedPartitionSpec, UnresolvedTable}
import org.apache.spark.sql.catalyst.parser.CatalystSqlParser.parsePlan
import org.apache.spark.sql.catalyst.plans.logical.{ShowTablePartition, ShowTables, ShowTablesExtended}
+import org.apache.spark.sql.execution.command.ShowTablesJsonCommand
import org.apache.spark.sql.test.SharedSparkSession
class ShowTablesParserSuite extends AnalysisTest with SharedSparkSession {
@@ -49,6 +51,19 @@ class ShowTablesParserSuite extends AnalysisTest with SharedSparkSession {
ShowTables(UnresolvedNamespace(Seq("ns1")), Some("*test*")))
}
+ test("show tables as json") {
+ val parse = spark.sessionState.sqlParser.parsePlan _
+ comparePlans(
+ parse("SHOW TABLES AS JSON"),
+ ShowTablesJsonCommand(CurrentNamespace, None, isExtended = false))
+ comparePlans(
+ parse("SHOW TABLES IN ns1 AS JSON"),
+ ShowTablesJsonCommand(UnresolvedNamespace(Seq("ns1")), None, isExtended = false))
+ comparePlans(
+ parse("SHOW TABLES IN ns1 LIKE '*test*' AS JSON"),
+ ShowTablesJsonCommand(UnresolvedNamespace(Seq("ns1")), Some("*test*"), isExtended = false))
+ }
+
test("show table extended") {
comparePlans(
parsePlan("SHOW TABLE EXTENDED LIKE '*test*'"),
@@ -80,4 +95,26 @@ class ShowTablesParserSuite extends AnalysisTest with SharedSparkSession {
"SHOW TABLE EXTENDED ... PARTITION ..."),
UnresolvedPartitionSpec(Map("ds" -> "2008-04-09"))))
}
+
+ test("show table extended as json") {
+ val parse = spark.sessionState.sqlParser.parsePlan _
+ comparePlans(
+ parse("SHOW TABLE EXTENDED LIKE '*test*' AS JSON"),
+ ShowTablesJsonCommand(CurrentNamespace, Some("*test*"), isExtended = true))
+ comparePlans(
+ parse(s"SHOW TABLE EXTENDED IN $catalog.ns1.ns2 LIKE '*test*' AS JSON"),
+ ShowTablesJsonCommand(
+ UnresolvedNamespace(Seq(catalog, "ns1", "ns2")), Some("*test*"), isExtended = true))
+ }
+
+ test("show table extended as json with partition should fail") {
+ checkError(
+ exception = intercept[AnalysisException] {
+ spark.sessionState.sqlParser.parsePlan(
+ "SHOW TABLE EXTENDED LIKE '*test*' PARTITION(ds='2008-04-09') AS JSON")
+ },
+ condition = "UNSUPPORTED_FEATURE.SHOW_TABLE_EXTENDED_JSON_WITH_PARTITION",
+ parameters = Map.empty
+ )
+ }
}
diff --git a/sql/core/src/test/scala/org/apache/spark/sql/execution/command/ShowTablesSuiteBase.scala b/sql/core/src/test/scala/org/apache/spark/sql/execution/command/ShowTablesSuiteBase.scala
index dbeb67c25320..5d40421045b3 100644
--- a/sql/core/src/test/scala/org/apache/spark/sql/execution/command/ShowTablesSuiteBase.scala
+++ b/sql/core/src/test/scala/org/apache/spark/sql/execution/command/ShowTablesSuiteBase.scala
@@ -17,6 +17,9 @@
package org.apache.spark.sql.execution.command
+import org.json4s._
+import org.json4s.jackson.JsonMethods._
+
import org.apache.spark.sql.{AnalysisException, QueryTest, Row}
import org.apache.spark.sql.connector.catalog.CatalogV2Implicits._
import org.apache.spark.sql.internal.SQLConf
@@ -32,8 +35,12 @@ import org.apache.spark.sql.internal.SQLConf
* - V1 Hive External catalog: `org.apache.spark.sql.hive.execution.command.ShowTablesSuite`
*/
trait ShowTablesSuiteBase extends QueryTest with DDLCommandTestUtils {
+ implicit val formats: Formats = DefaultFormats
+
override val command = "SHOW TABLES"
protected def defaultNamespace: Seq[String]
+ protected def expectedTableTypeInJson: String = "TABLE"
+ protected def expectsSessionTempViews: Boolean = true
protected def runShowTablesSql(sqlText: String, expected: Seq[Row]): Unit = {
val df = spark.sql(sqlText)
@@ -461,4 +468,243 @@ trait ShowTablesSuiteBase extends QueryTest with DDLCommandTestUtils {
}
}
}
+
+ test("SHOW TABLES AS JSON returns single row with json_metadata column") {
+ withNamespaceAndTable("ns", "tbl") { t =>
+ sql(s"CREATE TABLE $t (id INT, data STRING) $defaultUsing")
+ val df = sql(s"SHOW TABLES IN $catalog.ns AS JSON")
+ assert(df.schema.fieldNames === Seq("json_metadata"))
+ assert(df.count() == 1)
+
+ val jsonStr = df.collect()(0).getString(0)
+ val json = parse(jsonStr)
+ val tables = (json \ "tables").asInstanceOf[JArray].arr
+ assert(tables.length == 1, s"Expected 1 entry, got: $tables")
+ val tblEntry = tables.find(t => (t \ "name").extract[String] == "tbl")
+ assert(tblEntry.isDefined)
+ assert((tblEntry.get \ "isTemporary").extract[Boolean] == false)
+ assert((tblEntry.get \ "namespace").isInstanceOf[JArray])
+ }
+ }
+
+ test("SHOW TABLES AS JSON with empty database") {
+ withNamespace(s"$catalog.ns_empty") {
+ sql(s"CREATE NAMESPACE $catalog.ns_empty")
+ val df = sql(s"SHOW TABLES IN $catalog.ns_empty AS JSON")
+ assert(df.count() == 1)
+
+ val jsonStr = df.collect()(0).getString(0)
+ val json = parse(jsonStr)
+ val tables = (json \ "tables").asInstanceOf[JArray].arr
+ assert(tables.isEmpty)
+ }
+ }
+
+ test("SHOW TABLE EXTENDED AS JSON returns single row with json_metadata column") {
+ withNamespaceAndTable("ns", "tbl") { t =>
+ sql(s"CREATE TABLE $t (id INT, data STRING) $defaultUsing")
+ val df = sql(s"SHOW TABLE EXTENDED IN $catalog.ns LIKE 'tbl' AS JSON")
+ assert(df.schema.fieldNames === Seq("json_metadata"))
+ assert(df.count() == 1)
+
+ val jsonStr = df.collect()(0).getString(0)
+ val json = parse(jsonStr)
+ val tables = (json \ "tables").asInstanceOf[JArray].arr
+ assert(tables.length == 1)
+ val entry = tables.head
+ assert((entry \ "name").extract[String] == "tbl")
+ assert((entry \ "type").extract[String] == expectedTableTypeInJson)
+ assert((entry \ "isTemporary").extract[Boolean] == false)
+ assert((entry \ "catalog").isInstanceOf[JString])
+ assert((entry \ "namespace").isInstanceOf[JArray])
+ }
+ }
+
+ test("SHOW TABLES AS JSON includes temp views") {
+ withNamespaceAndTable("ns", "tbl") { t =>
+ sql(s"CREATE TABLE $t (id INT) $defaultUsing")
+ withTempView("tv") {
+ sql("CREATE TEMP VIEW tv AS SELECT 1 AS id")
+ val df = sql(s"SHOW TABLES IN $catalog.ns AS JSON")
+ val jsonStr = df.collect()(0).getString(0)
+ val json = parse(jsonStr)
+ val tables = (json \ "tables").asInstanceOf[JArray].arr
+ val names = tables.map(t => (t \ "name").extract[String])
+ assert(names.distinct.length == names.length, s"Duplicate entries found: $names")
+ val tempView = tables.find(t => (t \ "name").extract[String] == "tv")
+ if (expectsSessionTempViews) {
+ assert(tempView.isDefined)
+ assert((tempView.get \ "isTemporary").extract[Boolean] == true)
+ } else {
+ assert(tempView.isEmpty)
+ }
+ }
+ }
+ }
+
+ test("SHOW TABLE EXTENDED AS JSON with local temp view") {
+ withNamespaceAndTable("ns", "tbl") { t =>
+ sql(s"CREATE TABLE $t (id INT) $defaultUsing")
+ val localTmpViewName = "tbl_local_tmp"
+ withTempView(localTmpViewName) {
+ sql(s"CREATE TEMPORARY VIEW $localTmpViewName AS SELECT id FROM $t")
+
+ val df = sql(s"SHOW TABLE EXTENDED IN $catalog.ns LIKE 'tbl*' AS JSON")
+ assert(df.schema.fieldNames === Seq("json_metadata"))
+ assert(df.count() == 1)
+
+ val jsonStr = df.collect()(0).getString(0)
+ val json = parse(jsonStr)
+ val tables = (json \ "tables").asInstanceOf[JArray].arr
+
+ val tblEntry = tables.find(e => (e \ "name").extract[String] == "tbl")
+ assert(tblEntry.isDefined)
+ assert((tblEntry.get \ "isTemporary").extract[Boolean] == false)
+ assert((tblEntry.get \ "type").extract[String] == expectedTableTypeInJson)
+
+ if (expectsSessionTempViews) {
+ assert(tables.length == 2)
+ val tempViewEntry =
+ tables.find(e => (e \ "name").extract[String] == localTmpViewName)
+ assert(tempViewEntry.isDefined)
+ assert((tempViewEntry.get \ "isTemporary").extract[Boolean] == true)
+ assert((tempViewEntry.get \ "type").extract[String] == "VIEW")
+ } else {
+ assert(tables.length == 1)
+ }
+ }
+ }
+ }
+
+ test("SHOW TABLE EXTENDED AS JSON with global temp view") {
+ withNamespaceAndTable("ns", "tbl") { t =>
+ sql(s"CREATE TABLE $t (id INT) $defaultUsing")
+ val globalTmpViewName = "ext_json_gtv"
+ val globalNamespace = "global_temp"
+ withView(s"$globalNamespace.$globalTmpViewName") {
+ sql(s"CREATE OR REPLACE GLOBAL TEMP VIEW $globalTmpViewName AS SELECT id FROM $t")
+
+ val df = sql(s"SHOW TABLE EXTENDED IN $globalNamespace LIKE 'ext_json*' AS JSON")
+ assert(df.schema.fieldNames === Seq("json_metadata"))
+ assert(df.count() == 1)
+
+ val jsonStr = df.collect()(0).getString(0)
+ val json = parse(jsonStr)
+ val tables = (json \ "tables").asInstanceOf[JArray].arr
+ assert(tables.length == 1, s"Expected 1 entry, got: $tables")
+
+ val globalTempViewEntry =
+ tables.find(e => (e \ "name").extract[String] == globalTmpViewName)
+ assert(globalTempViewEntry.isDefined)
+ assert((globalTempViewEntry.get \ "isTemporary").extract[Boolean] == true)
+ assert((globalTempViewEntry.get \ "type").extract[String] == "VIEW")
+ }
+ }
+ }
+
+ test("SHOW TABLES AS JSON with global temp view") {
+ withNamespaceAndTable("ns", "tbl") { t =>
+ sql(s"CREATE TABLE $t (id INT) $defaultUsing")
+ val globalTmpViewName = "show_json_gtv"
+ val globalNamespace = "global_temp"
+ withView(s"$globalNamespace.$globalTmpViewName") {
+ sql(s"CREATE OR REPLACE GLOBAL TEMP VIEW $globalTmpViewName AS SELECT id FROM $t")
+
+ val df = sql(s"SHOW TABLES IN $globalNamespace AS JSON")
+ assert(df.schema.fieldNames === Seq("json_metadata"))
+ assert(df.count() == 1)
+
+ val jsonStr = df.collect()(0).getString(0)
+ val json = parse(jsonStr)
+ val tables = (json \ "tables").asInstanceOf[JArray].arr
+ assert(tables.length == 1, s"Expected 1 entry, got: $tables")
+
+ val globalTempViewEntry =
+ tables.find(e => (e \ "name").extract[String] == globalTmpViewName)
+ assert(globalTempViewEntry.isDefined)
+ assert((globalTempViewEntry.get \ "isTemporary").extract[Boolean] == true)
+ }
+ }
+ }
+
+ test("SHOW TABLE EXTENDED AS JSON with both local and global temp views") {
+ withNamespaceAndTable("ns", "tbl") { t =>
+ sql(s"CREATE TABLE $t (id INT) $defaultUsing")
+ val localTmpViewName = "both_json_ltv"
+ val globalTmpViewName = "both_json_gtv"
+ val globalNamespace = "global_temp"
+ withView(localTmpViewName, s"$globalNamespace.$globalTmpViewName") {
+ sql(s"CREATE OR REPLACE TEMP VIEW $localTmpViewName AS SELECT id FROM $t")
+ sql(s"CREATE OR REPLACE GLOBAL TEMP VIEW $globalTmpViewName AS SELECT id FROM $t")
+
+ val df = sql(s"SHOW TABLE EXTENDED IN $globalNamespace LIKE 'both_json*' AS JSON")
+ assert(df.count() == 1)
+
+ val jsonStr = df.collect()(0).getString(0)
+ val json = parse(jsonStr)
+ val tables = (json \ "tables").asInstanceOf[JArray].arr
+
+ assert(tables.length == 2)
+
+ val globalTempViewEntry =
+ tables.find(e => (e \ "name").extract[String] == globalTmpViewName)
+ assert(globalTempViewEntry.isDefined)
+ assert((globalTempViewEntry.get \ "isTemporary").extract[Boolean] == true)
+
+ val localTempViewEntry =
+ tables.find(e => (e \ "name").extract[String] == localTmpViewName)
+ assert(localTempViewEntry.isDefined)
+ assert((localTempViewEntry.get \ "isTemporary").extract[Boolean] == true)
+ }
+ }
+ }
+
+ test("SHOW TABLES AS JSON with LIKE pattern") {
+ withNamespaceAndTable("ns", "tab_matched") { t =>
+ sql(s"CREATE TABLE $t (id INT) $defaultUsing")
+ withTable(s"$catalog.ns.other") {
+ sql(s"CREATE TABLE $catalog.ns.other (id INT) $defaultUsing")
+ val jsonStr = sql(s"SHOW TABLES IN $catalog.ns LIKE 'tab_matched' AS JSON")
+ .collect()(0).getString(0)
+ val tables = (parse(jsonStr) \ "tables").asInstanceOf[JArray].arr
+ assert(tables.exists(e => (e \ "name").extract[String] == "tab_matched"))
+ assert(!tables.exists(e => (e \ "name").extract[String] == "other"))
+ }
+ }
+ }
+
+ test("SHOW TABLES AS JSON without IN clause") {
+ withNamespaceAndTable("ns", "tbl") { t =>
+ sql(s"CREATE TABLE $t (id INT) $defaultUsing")
+ sql(s"USE $catalog.ns")
+ val jsonStr = sql("SHOW TABLES AS JSON").collect()(0).getString(0)
+ val tables = (parse(jsonStr) \ "tables").asInstanceOf[JArray].arr
+ assert(tables.exists(e => (e \ "name").extract[String] == "tbl"))
+ }
+ }
+
+ test("SHOW TABLES AS JSON wraps entries in tables key") {
+ withNamespace(s"$catalog.ns") {
+ sql(s"CREATE NAMESPACE $catalog.ns")
+ val jsonStr = sql(s"SHOW TABLES IN $catalog.ns AS JSON").collect()(0).getString(0)
+ val json = parse(jsonStr)
+ assert(json \ "tables" != JNothing)
+ }
+ }
+
+ test("SHOW TABLES AS JSON produces same result for useV1Command true and false") {
+ withNamespaceAndTable("ns", "tbl") { t =>
+ sql(s"CREATE TABLE $t (id INT) $defaultUsing")
+ val query = s"SHOW TABLES IN $catalog.ns AS JSON"
+ val resultDefault = sql(query).collect()(0).getString(0)
+ withSQLConf(SQLConf.LEGACY_USE_V1_COMMAND.key -> "true") {
+ val resultV1 = sql(query).collect()(0).getString(0)
+ val namesDefault = (parse(resultDefault) \ "tables").asInstanceOf[JArray].arr
+ .map(e => (e \ "name").extract[String]).sorted
+ val namesV1 = (parse(resultV1) \ "tables").asInstanceOf[JArray].arr
+ .map(e => (e \ "name").extract[String]).sorted
+ assert(namesDefault === namesV1)
+ }
+ }
+ }
}
diff --git a/sql/core/src/test/scala/org/apache/spark/sql/execution/command/v1/ShowTablesSuite.scala b/sql/core/src/test/scala/org/apache/spark/sql/execution/command/v1/ShowTablesSuite.scala
index 9d353fde898f..3928f63fe246 100644
--- a/sql/core/src/test/scala/org/apache/spark/sql/execution/command/v1/ShowTablesSuite.scala
+++ b/sql/core/src/test/scala/org/apache/spark/sql/execution/command/v1/ShowTablesSuite.scala
@@ -31,6 +31,7 @@ import org.apache.spark.sql.internal.SQLConf
*/
trait ShowTablesSuiteBase extends command.ShowTablesSuiteBase with command.TestsV1AndV2Commands {
override def defaultNamespace: Seq[String] = Seq("default")
+ override def expectedTableTypeInJson: String = "MANAGED"
private def withSourceViews(f: => Unit): Unit = {
withTable("source", "source2") {
diff --git a/sql/core/src/test/scala/org/apache/spark/sql/execution/command/v2/ShowTablesSuite.scala b/sql/core/src/test/scala/org/apache/spark/sql/execution/command/v2/ShowTablesSuite.scala
index 5719fbee370a..96e61014980e 100644
--- a/sql/core/src/test/scala/org/apache/spark/sql/execution/command/v2/ShowTablesSuite.scala
+++ b/sql/core/src/test/scala/org/apache/spark/sql/execution/command/v2/ShowTablesSuite.scala
@@ -26,6 +26,7 @@ import org.apache.spark.util.Utils
*/
class ShowTablesSuite extends command.ShowTablesSuiteBase with CommandSuiteBase {
override def defaultNamespace: Seq[String] = Nil
+ override def expectsSessionTempViews: Boolean = false
// The test fails for V1 catalog with the error:
// org.apache.spark.sql.AnalysisException:
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org