You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spark.apache.org by hv...@apache.org on 2016/04/01 18:27:29 UTC
spark git commit: [SPARK-14184][SQL] Support native execution of SHOW
DATABASE command and fix SHOW TABLE to use table identifier pattern
Repository: spark
Updated Branches:
refs/heads/master 3715ecdf4 -> 0b04f8fdf
[SPARK-14184][SQL] Support native execution of SHOW DATABASE command and fix SHOW TABLE to use table identifier pattern
## What changes were proposed in this pull request?
This PR addresses the following
1. Supports native execution of SHOW DATABASES command
2. Fixes SHOW TABLES to apply the identifier_with_wildcards pattern if supplied.
SHOW TABLE syntax
```
SHOW TABLES [IN database_name] ['identifier_with_wildcards'];
```
SHOW DATABASES syntax
```
SHOW (DATABASES|SCHEMAS) [LIKE 'identifier_with_wildcards'];
```
## How was this patch tested?
Tests added in SQLQuerySuite (both hive and sql contexts) and DDLCommandSuite
Note: Since the table name pattern was not working , tests are added in both SQLQuerySuite to
verify the application of the table pattern.
Author: Dilip Biswal <db...@us.ibm.com>
Closes #11991 from dilipbiswal/dkb_show_database.
Project: http://git-wip-us.apache.org/repos/asf/spark/repo
Commit: http://git-wip-us.apache.org/repos/asf/spark/commit/0b04f8fd
Tree: http://git-wip-us.apache.org/repos/asf/spark/tree/0b04f8fd
Diff: http://git-wip-us.apache.org/repos/asf/spark/diff/0b04f8fd
Branch: refs/heads/master
Commit: 0b04f8fdf1614308cb3e7e0c7282f7365cc3d1bb
Parents: 3715ecd
Author: Dilip Biswal <db...@us.ibm.com>
Authored: Fri Apr 1 18:27:11 2016 +0200
Committer: Herman van Hovell <hv...@questtec.nl>
Committed: Fri Apr 1 18:27:11 2016 +0200
----------------------------------------------------------------------
.../apache/spark/sql/catalyst/parser/SqlBase.g4 | 6 +-
.../scala/org/apache/spark/sql/SQLContext.scala | 4 +-
.../spark/sql/execution/SparkSqlParser.scala | 22 ++++--
.../spark/sql/execution/command/commands.scala | 42 +++++++++---
.../sql/execution/command/DDLCommandSuite.scala | 11 +++
.../spark/sql/execution/command/DDLSuite.scala | 72 ++++++++++++++++++++
.../spark/sql/hive/thriftserver/CliSuite.scala | 2 +-
.../sql/hive/execution/SQLQuerySuite.scala | 22 ++++++
8 files changed, 163 insertions(+), 18 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/spark/blob/0b04f8fd/sql/catalyst/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBase.g4
----------------------------------------------------------------------
diff --git a/sql/catalyst/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBase.g4 b/sql/catalyst/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBase.g4
index a857e67..5513bbd 100644
--- a/sql/catalyst/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBase.g4
+++ b/sql/catalyst/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBase.g4
@@ -114,7 +114,8 @@ statement
| DROP TEMPORARY? FUNCTION (IF EXISTS)? qualifiedName #dropFunction
| EXPLAIN explainOption* statement #explain
| SHOW TABLES ((FROM | IN) db=identifier)?
- (LIKE (qualifiedName | pattern=STRING))? #showTables
+ (LIKE? pattern=STRING)? #showTables
+ | SHOW DATABASES (LIKE pattern=STRING)? #showDatabases
| SHOW FUNCTIONS (LIKE? (qualifiedName | pattern=STRING))? #showFunctions
| (DESC | DESCRIBE) FUNCTION EXTENDED? qualifiedName #describeFunction
| (DESC | DESCRIBE) option=(EXTENDED | FORMATTED)?
@@ -618,7 +619,7 @@ number
;
nonReserved
- : SHOW | TABLES | COLUMNS | COLUMN | PARTITIONS | FUNCTIONS
+ : SHOW | TABLES | COLUMNS | COLUMN | PARTITIONS | FUNCTIONS | DATABASES
| ADD
| OVER | PARTITION | RANGE | ROWS | PRECEDING | FOLLOWING | CURRENT | ROW | MAP | ARRAY | STRUCT
| LATERAL | WINDOW | REDUCE | TRANSFORM | USING | SERDE | SERDEPROPERTIES | RECORDREADER
@@ -836,6 +837,7 @@ OUTPUTFORMAT: 'OUTPUTFORMAT';
INPUTDRIVER: 'INPUTDRIVER';
OUTPUTDRIVER: 'OUTPUTDRIVER';
DATABASE: 'DATABASE' | 'SCHEMA';
+DATABASES: 'DATABASES' | 'SCHEMAS';
DFS: 'DFS';
TRUNCATE: 'TRUNCATE';
METADATA: 'METADATA';
http://git-wip-us.apache.org/repos/asf/spark/blob/0b04f8fd/sql/core/src/main/scala/org/apache/spark/sql/SQLContext.scala
----------------------------------------------------------------------
diff --git a/sql/core/src/main/scala/org/apache/spark/sql/SQLContext.scala b/sql/core/src/main/scala/org/apache/spark/sql/SQLContext.scala
index 0576a1a..221782e 100644
--- a/sql/core/src/main/scala/org/apache/spark/sql/SQLContext.scala
+++ b/sql/core/src/main/scala/org/apache/spark/sql/SQLContext.scala
@@ -781,7 +781,7 @@ class SQLContext private[sql](
* @since 1.3.0
*/
def tables(): DataFrame = {
- Dataset.ofRows(this, ShowTablesCommand(None))
+ Dataset.ofRows(this, ShowTablesCommand(None, None))
}
/**
@@ -793,7 +793,7 @@ class SQLContext private[sql](
* @since 1.3.0
*/
def tables(databaseName: String): DataFrame = {
- Dataset.ofRows(this, ShowTablesCommand(Some(databaseName)))
+ Dataset.ofRows(this, ShowTablesCommand(Some(databaseName), None))
}
/**
http://git-wip-us.apache.org/repos/asf/spark/blob/0b04f8fd/sql/core/src/main/scala/org/apache/spark/sql/execution/SparkSqlParser.scala
----------------------------------------------------------------------
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 16a899e..7efe98d 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
@@ -70,12 +70,26 @@ class SparkSqlAstBuilder extends AstBuilder {
/**
* Create a [[ShowTablesCommand]] logical plan.
+ * Example SQL :
+ * {{{
+ * SHOW TABLES [(IN|FROM) database_name] [[LIKE] 'identifier_with_wildcards'];
+ * }}}
*/
override def visitShowTables(ctx: ShowTablesContext): LogicalPlan = withOrigin(ctx) {
- if (ctx.LIKE != null) {
- logWarning("SHOW TABLES LIKE option is ignored.")
- }
- ShowTablesCommand(Option(ctx.db).map(_.getText))
+ ShowTablesCommand(
+ Option(ctx.db).map(_.getText),
+ Option(ctx.pattern).map(string))
+ }
+
+ /**
+ * Create a [[ShowDatabasesCommand]] logical plan.
+ * Example SQL:
+ * {{{
+ * SHOW (DATABASES|SCHEMAS) [LIKE 'identifier_with_wildcards'];
+ * }}}
+ */
+ override def visitShowDatabases(ctx: ShowDatabasesContext): LogicalPlan = withOrigin(ctx) {
+ ShowDatabasesCommand(Option(ctx.pattern).map(string))
}
/**
http://git-wip-us.apache.org/repos/asf/spark/blob/0b04f8fd/sql/core/src/main/scala/org/apache/spark/sql/execution/command/commands.scala
----------------------------------------------------------------------
diff --git a/sql/core/src/main/scala/org/apache/spark/sql/execution/command/commands.scala b/sql/core/src/main/scala/org/apache/spark/sql/execution/command/commands.scala
index 964f0a7..f90d871 100644
--- a/sql/core/src/main/scala/org/apache/spark/sql/execution/command/commands.scala
+++ b/sql/core/src/main/scala/org/apache/spark/sql/execution/command/commands.scala
@@ -322,18 +322,17 @@ case class DescribeCommand(
* If a databaseName is not given, the current database will be used.
* The syntax of using this command in SQL is:
* {{{
- * SHOW TABLES [IN databaseName]
+ * SHOW TABLES [(IN|FROM) database_name] [[LIKE] 'identifier_with_wildcards'];
* }}}
*/
-case class ShowTablesCommand(databaseName: Option[String]) extends RunnableCommand {
+case class ShowTablesCommand(
+ databaseName: Option[String],
+ tableIdentifierPattern: Option[String]) extends RunnableCommand {
// The result of SHOW TABLES has two columns, tableName and isTemporary.
override val output: Seq[Attribute] = {
- val schema = StructType(
- StructField("tableName", StringType, false) ::
- StructField("isTemporary", BooleanType, false) :: Nil)
-
- schema.toAttributes
+ AttributeReference("tableName", StringType, nullable = false)() ::
+ AttributeReference("isTemporary", BooleanType, nullable = false)() :: Nil
}
override def run(sqlContext: SQLContext): Seq[Row] = {
@@ -341,11 +340,36 @@ case class ShowTablesCommand(databaseName: Option[String]) extends RunnableComma
// instead of calling tables in sqlContext.
val catalog = sqlContext.sessionState.catalog
val db = databaseName.getOrElse(catalog.getCurrentDatabase)
- val rows = catalog.listTables(db).map { t =>
+ val tables =
+ tableIdentifierPattern.map(catalog.listTables(db, _)).getOrElse(catalog.listTables(db))
+ tables.map { t =>
val isTemp = t.database.isEmpty
Row(t.table, isTemp)
}
- rows
+ }
+}
+
+/**
+ * A command for users to list the databases/schemas.
+ * If a databasePattern is supplied then the databases that only matches the
+ * pattern would be listed.
+ * The syntax of using this command in SQL is:
+ * {{{
+ * SHOW (DATABASES|SCHEMAS) [LIKE 'identifier_with_wildcards'];
+ * }}}
+ */
+case class ShowDatabasesCommand(databasePattern: Option[String]) extends RunnableCommand {
+
+ // The result of SHOW DATABASES has one column called 'result'
+ override val output: Seq[Attribute] = {
+ AttributeReference("result", StringType, nullable = false)() :: Nil
+ }
+
+ override def run(sqlContext: SQLContext): Seq[Row] = {
+ val catalog = sqlContext.sessionState.catalog
+ val databases =
+ databasePattern.map(catalog.listDatabases(_)).getOrElse(catalog.listDatabases())
+ databases.map { d => Row(d) }
}
}
http://git-wip-us.apache.org/repos/asf/spark/blob/0b04f8fd/sql/core/src/test/scala/org/apache/spark/sql/execution/command/DDLCommandSuite.scala
----------------------------------------------------------------------
diff --git a/sql/core/src/test/scala/org/apache/spark/sql/execution/command/DDLCommandSuite.scala b/sql/core/src/test/scala/org/apache/spark/sql/execution/command/DDLCommandSuite.scala
index cebf9c8..458f36e 100644
--- a/sql/core/src/test/scala/org/apache/spark/sql/execution/command/DDLCommandSuite.scala
+++ b/sql/core/src/test/scala/org/apache/spark/sql/execution/command/DDLCommandSuite.scala
@@ -762,4 +762,15 @@ class DDLCommandSuite extends PlanTest {
comparePlans(parsed2, expected2)
}
+ test("show databases") {
+ val sql1 = "SHOW DATABASES"
+ val sql2 = "SHOW DATABASES LIKE 'defau*'"
+ val parsed1 = parser.parsePlan(sql1)
+ val expected1 = ShowDatabasesCommand(None)
+ val parsed2 = parser.parsePlan(sql2)
+ val expected2 = ShowDatabasesCommand(Some("defau*"))
+ comparePlans(parsed1, expected1)
+ comparePlans(parsed2, expected2)
+ }
+
}
http://git-wip-us.apache.org/repos/asf/spark/blob/0b04f8fd/sql/core/src/test/scala/org/apache/spark/sql/execution/command/DDLSuite.scala
----------------------------------------------------------------------
diff --git a/sql/core/src/test/scala/org/apache/spark/sql/execution/command/DDLSuite.scala b/sql/core/src/test/scala/org/apache/spark/sql/execution/command/DDLSuite.scala
index f148f2d..885a04a 100644
--- a/sql/core/src/test/scala/org/apache/spark/sql/execution/command/DDLSuite.scala
+++ b/sql/core/src/test/scala/org/apache/spark/sql/execution/command/DDLSuite.scala
@@ -45,6 +45,7 @@ class DDLSuite extends QueryTest with SharedSQLContext {
dbNames.foreach { name =>
sqlContext.sql(s"DROP DATABASE IF EXISTS $name CASCADE")
}
+ sqlContext.sessionState.catalog.setCurrentDatabase("default")
}
}
@@ -159,4 +160,75 @@ class DDLSuite extends QueryTest with SharedSQLContext {
}
// TODO: ADD a testcase for Drop Database in Restric when we can create tables in SQLContext
+
+ test("show tables") {
+ withTempTable("show1a", "show2b") {
+ sql(
+ """
+ |CREATE TEMPORARY TABLE show1a
+ |USING org.apache.spark.sql.sources.DDLScanSource
+ |OPTIONS (
+ | From '1',
+ | To '10',
+ | Table 'test1'
+ |
+ |)
+ """.stripMargin)
+ sql(
+ """
+ |CREATE TEMPORARY TABLE show2b
+ |USING org.apache.spark.sql.sources.DDLScanSource
+ |OPTIONS (
+ | From '1',
+ | To '10',
+ | Table 'test1'
+ |)
+ """.stripMargin)
+ checkAnswer(
+ sql("SHOW TABLES IN default 'show1*'"),
+ Row("show1a", true) :: Nil)
+
+ checkAnswer(
+ sql("SHOW TABLES IN default 'show1*|show2*'"),
+ Row("show1a", true) ::
+ Row("show2b", true) :: Nil)
+
+ checkAnswer(
+ sql("SHOW TABLES 'show1*|show2*'"),
+ Row("show1a", true) ::
+ Row("show2b", true) :: Nil)
+
+ assert(
+ sql("SHOW TABLES").count() >= 2)
+ assert(
+ sql("SHOW TABLES IN default").count() >= 2)
+ }
+ }
+
+ test("show databases") {
+ withDatabase("showdb1A", "showdb2B") {
+ sql("CREATE DATABASE showdb1A")
+ sql("CREATE DATABASE showdb2B")
+
+ assert(
+ sql("SHOW DATABASES").count() >= 2)
+
+ checkAnswer(
+ sql("SHOW DATABASES LIKE '*db1A'"),
+ Row("showdb1A") :: Nil)
+
+ checkAnswer(
+ sql("SHOW DATABASES LIKE 'showdb1A'"),
+ Row("showdb1A") :: Nil)
+
+ checkAnswer(
+ sql("SHOW DATABASES LIKE '*db1A|*db2B'"),
+ Row("showdb1A") ::
+ Row("showdb2B") :: Nil)
+
+ checkAnswer(
+ sql("SHOW DATABASES LIKE 'non-existentdb'"),
+ Nil)
+ }
+ }
}
http://git-wip-us.apache.org/repos/asf/spark/blob/0b04f8fd/sql/hive-thriftserver/src/test/scala/org/apache/spark/sql/hive/thriftserver/CliSuite.scala
----------------------------------------------------------------------
diff --git a/sql/hive-thriftserver/src/test/scala/org/apache/spark/sql/hive/thriftserver/CliSuite.scala b/sql/hive-thriftserver/src/test/scala/org/apache/spark/sql/hive/thriftserver/CliSuite.scala
index 7ad7f92..e93b0c1 100644
--- a/sql/hive-thriftserver/src/test/scala/org/apache/spark/sql/hive/thriftserver/CliSuite.scala
+++ b/sql/hive-thriftserver/src/test/scala/org/apache/spark/sql/hive/thriftserver/CliSuite.scala
@@ -177,7 +177,7 @@ class CliSuite extends SparkFunSuite with BeforeAndAfterAll with Logging {
}
test("Single command with -e") {
- runCliWithin(2.minute, Seq("-e", "SHOW DATABASES;"))("" -> "OK")
+ runCliWithin(2.minute, Seq("-e", "SHOW DATABASES;"))("" -> "")
}
test("Single command with --database") {
http://git-wip-us.apache.org/repos/asf/spark/blob/0b04f8fd/sql/hive/src/test/scala/org/apache/spark/sql/hive/execution/SQLQuerySuite.scala
----------------------------------------------------------------------
diff --git a/sql/hive/src/test/scala/org/apache/spark/sql/hive/execution/SQLQuerySuite.scala b/sql/hive/src/test/scala/org/apache/spark/sql/hive/execution/SQLQuerySuite.scala
index 6199253..c203518 100644
--- a/sql/hive/src/test/scala/org/apache/spark/sql/hive/execution/SQLQuerySuite.scala
+++ b/sql/hive/src/test/scala/org/apache/spark/sql/hive/execution/SQLQuerySuite.scala
@@ -1811,4 +1811,26 @@ class SQLQuerySuite extends QueryTest with SQLTestUtils with TestHiveSingleton {
}
}
}
+
+ test("show tables") {
+ withTable("show1a", "show2b") {
+ sql("CREATE TABLE show1a(c1 int)")
+ sql("CREATE TABLE show2b(c2 int)")
+ checkAnswer(
+ sql("SHOW TABLES IN default 'show1*'"),
+ Row("show1a", false) :: Nil)
+ checkAnswer(
+ sql("SHOW TABLES IN default 'show1*|show2*'"),
+ Row("show1a", false) ::
+ Row("show2b", false) :: Nil)
+ checkAnswer(
+ sql("SHOW TABLES 'show1*|show2*'"),
+ Row("show1a", false) ::
+ Row("show2b", false) :: Nil)
+ assert(
+ sql("SHOW TABLES").count() >= 2)
+ assert(
+ sql("SHOW TABLES IN default").count() >= 2)
+ }
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org