...
Discussion thread | https://lists.apache.org/thread/54fyd27m8on1cf3hn6dz564zqmkobjyd | ||||||||
---|---|---|---|---|---|---|---|---|---|
Vote thread | https://lists.apache.org/thread/bc0qcmg73t4q7do3k657rqcdx1vyjqzy | ||||||||
JIRA |
| ||||||||
Release |
Please keep the discussion on the mailing list rather than commenting on the wiki (wiki discussions get unwieldy fast).
...
Engine | Support or Not, If support show the syntax | Note |
Flink | No | |
Spark | SHOW CREATE DATABASE [database_name]No | |
Hive | SHOW CREATE DATABASE [database_name]No | |
Presto | SHOW CREATE DATABASE [database_name]No | |
MySQL | SHOW CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name | RDBMS, such as Oracle, MariaDB, TiDB also support it. |
SnowFlake | SHOW CREATE DATABASE [database_name]No |
show create function
Engine | Support or Not, If support show the syntaxes | Note |
Flink | No | |
Spark | No | |
Hive |
No | ||
Presto | SHOW CREATE FUNCTION function_name [ ( parameter_type[, ...] ) ] | |
MySQL | SHOW CREATE FUNCTION func_name |
SnowFlake
RDBMS, such as Oracle, MariaDB, TiDB also support it. | |
SnowFlake | No |
describe catalog
Engine | Support or Not, If support show the syntaxes | Note |
Flink | No | with catalog-database-table three layer |
Spark | No | with database-table layer. schema and database they mean the same thing. But databricks on aws support it: |
Hive | No | with database-table layer. schema and database they mean the same thing |
Presto | No | Presto is schema-database-table three layer. schema and database are not equal. |
MySQL | No | with database-table layer. schema and database they mean the same thing. |
SnowFlake |
DESC[RIBE] DATABASE <database_name> | SnowFlake is database-schema-table three layer. database may act as catalog. |
describe database
Engine | Support or Not, If support show the syntaxes | Note |
Flink | { DESC | DESCRIBE } DATABASE [ EXTENDED ] db_name | just sqlNode, but not expose to table & sql api |
Spark | { DESC | DESCRIBE } DATABASE [ EXTENDED ] db_name | |
Hive | DESCRIBE DATABASE [EXTENDED] db_name; DESCRIBE SCHEMA [EXTENDED] db_name; | |
Presto | No | |
MySQL | No | |
SnowFlake | DESC[RIBE] |
SCHEMA <schema_name> |
describe function
Engine | Support or Not, If support show the syntaxes | Note |
Flink | No | |
Spark | { DESC | DESCRIBE } FUNCTION [ EXTENDED ] function_name | |
Hive | DESCRIBEFUNCTION [EXTENDED] <function_name> | |
Presto | No | |
MySQL | No | |
SnowFlake | DESC[RIBE] FUNCTION <name> ( [ <arg_data_type> ] [ , ... ] ) |
describe view
Engine | Support or Not, If support show the syntaxes | Note |
Flink | No | |
Spark | No | |
Hive | DESCRIBE [EXTENDED|FORMATTED] <view_name>; | |
Presto | No | |
MySQL | No | |
SnowFlake | DESC[RIBE] VIEW <name> |
We can see many popular engines have filtering with show statements, support 'FROM/IN' or 'LIKE/ILIKE' filter clause except flink.
And currently flink only supports describing tables. And also some unique statements such as show jars/show modules in flink can also support this feature.
So we proposed this FLIP.
Proposed Syntax Changes:
Note: we both support LIKE and ILIKE in this FLIP.
...
Because calcite already support parse ILIKE and exist SqlLikeOperator(SqlLibraryOperators.ILIKE, not std operator). We just need to support it in table & sql api (no need to modify sql keywords and Parser.jj).
before | after(under discussed) | Note | |
show catalogs | SHOW CATALOGS | SHOW CATALOGS[ [NOT] (LIKE | ILIKE) <sql_like_pattern> ] | |
show databases | SHOW DATABASES | SHOW DATABASES [ ( FROM | IN ) catalog_name] [ [NOT] (LIKE | ILIKE) <sql_like_pattern> ] | |
show tables | SHOW TABLES [ ( FROM | IN ) [catalog_name.]database_name ] [ [NOT] LIKE <sql_like_pattern> ] | SHOW TABLES [ ( FROM | IN ) [catalog_name.]database_name ] [ [NOT] (LIKE | ILIKE) <sql_like_pattern> ] | |
show columns | SHOW COLUMNS ( FROM | IN ) [[catalog_name.]database.]<table_name> [ [NOT] LIKE <sql_like_pattern>] | SHOW COLUMNS ( FROM | IN ) [[catalog_name.]database.]<table_name> [ [NOT] (LIKE | ILIKE) <sql_like_pattern>] | |
show functions | SHOW [USER] FUNCTIONS | SHOW [USER] FUNCTIONS [ ( FROM | IN ) [catalog_name.]database_name ] [ [NOT] (LIKE | ILIKE) <sql_like_pattern> ] | |
show views | SHOW VIEWS | SHOW VIEWS [ ( FROM | IN ) [catalog_name.]database_name ] [ [NOT] (LIKE | ILIKE) <sql_like_pattern> ] | |
show modules | SHOW [FULL] MODULES | SHOW [FULL] MODULES [ [NOT] (LIKE | ILIKE) <sql_like_pattern> ] | |
show jars | SHOW JARS | SHOW JARS [ [NOT] (LIKE | ILIKE) <sql_like_pattern> ] | only work in SQL CLI or SQL Gateway. |
show jobs | SHOW JOBS | SHOW JOBS [ [NOT] (LIKE | ILIKE) <sql_like_pattern> ] | only work in SQL CLI or SQL Gateway. |
describe catalog | Not Support | { DESCRIBE | DESC } CATALOG [ EXTENDED ] catalog_name If the optional | |
describe database | Not Support | { DESCRIBE | DESC } DATABASE [ EXTENDED |
] [catalog_name.]database_name If the optional |
option is specified, it returns the basic metadata information along with the |
properties |
like spark and hive. | |||
describe function | Not Support | { DESCRIBE | DESC } FUNCTION [ EXTENDED ] [[catalog_name.]database_name.]function_name If the optional | |
... |
Proposed CataLog API Changes:
...
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
// Add SqlDescribeCatalog & DescribeCatalogOperation
// Add SqlDescribeDatabase & DescribeDatabaseOperation
// Add SqlDescribeFunction & DescribeFunctionOperation
About DescribeCatalog:
+-------------------------+-----------------------------+
| catalog_description_item| catalog_description_value |
+-------------------------+-----------------------------+
| Catalog Name | xxx |
| Properties| (key1=val1, key2=val2, ...) |
+-------------------------+-----------------------------+
About DescribeDatabase:
+-------------------------+-----------------------------+
|database_description_item| database_description_value|
+-------------------------+-----------------------------+
| Database Name| employees|
| Description| For software companies|
| Properties| (key1=val1, key2=val2, ...) |
+-------------------------+-----------------------------+
About DescribeFunction:
+-------------------------+-----------------------------+
|function_description_item| function_description_value|
+-------------------------+-----------------------------+
| Function Name | ABS |
| Function Language| JAVA |
| Resource Uri | file://xxx |
| Details | details |
+-------------------------+-----------------------------+
The concrete describe infos we can get from the specific catalog object.
// Changed SqlNodes & Operations
// SqlShowCatalogs
public class SqlShowCatalogs extends SqlCall {
public static final SqlSpecialOperator OPERATOR =
new SqlSpecialOperator("SHOW CATALOGS", SqlKind.OTHER);
protected final SqlLikeType likeType;
protected final boolean notLike;
protected final SqlCharStringLiteral likeLiteral;
public SqlShowCatalogs(SqlParserPos pos) {
super(pos);
this.likeType = null;
this.notLike = false;
this.likeLiteral = null;
}
public SqlShowCatalogs(
SqlParserPos pos, String likeType, boolean notLike, SqlCharStringLiteral likeLiteral) {
super(pos);
if (likeType != null) {
this.likeType = SqlLikeType.of(likeType);
this.likeLiteral = requireNonNull(likeLiteral, "Like pattern must not be null");
} else {
this.likeType = null;
this.likeLiteral = null;
}
this.notLike = notLike;
}
public SqlLikeType getLikeType() {
return likeType;
}
public boolean isLike() {
return likeType == SqlLikeType.LIKE;
}
public boolean isILike() {
return likeType == SqlLikeType.ILIKE;
}
public boolean isWithLike() {
return isLike() || isILike();
}
public boolean isNotLike() {
return notLike;
}
public String getLikeSqlPattern() {
return Objects.isNull(likeLiteral) ? null : likeLiteral.getValueAs(String.class);
}
@Override
public SqlOperator getOperator() {
return OPERATOR;
}
@Override
public List<SqlNode> getOperandList() {
return Collections.emptyList();
}
@Override
public void unparse(SqlWriter writer, int leftPrec, int rightPrec) {
writer.keyword("SHOW CATALOGS");
if (isWithLike()) {
if (isNotLike()) {
writer.keyword(String.format("NOT %s '%s'", likeType.name(), getLikeSqlPattern()));
} else {
writer.keyword(String.format("%s '%s'", likeType.name(), getLikeSqlPattern()));
}
}
}
}
// ShowCatalogsOperation
public class ShowCatalogsOperation implements ShowOperation {
private final OperationLikeType likeType;
private final boolean notLike;
private final String likePattern;
/** Use when there is no sub-clause. */
public ShowCatalogsOperation() {
this.likeType = null;
this.notLike = false;
this.likePattern = null;
}
/** Use when there is like. */
public ShowCatalogsOperation(String likeType, boolean notLike, String likePattern) {
this.likeType = OperationLikeType.of(checkNotNull(likeType, "Like type must not be null"));
this.likePattern = checkNotNull(likePattern, "Like pattern must not be null");
this.notLike = notLike;
}
public boolean isLike() {
return likeType == OperationLikeType.LIKE;
}
public boolean isIlike() {
return likeType == OperationLikeType.ILIKE;
}
public boolean isWithLike() {
return isLike() || isIlike();
}
public boolean isNotLike() {
return notLike;
}
public String getLikePattern() {
return likePattern;
}
@Override
public String asSummaryString() {
StringBuilder builder = new StringBuilder().append("SHOW CATALOGS");
if (isWithLike()) {
if (notLike) {
builder.append(String.format(" NOT %s %s", likeType.name(), likePattern));
} else {
builder.append(String.format(" %s %s", likeType.name(), likePattern));
}
}
return builder.toString();
}
}
// SqlShowDatabases
public class SqlShowDatabases extends SqlCall {
public static final SqlSpecialOperator OPERATOR =
new SqlSpecialOperator("SHOW DATABASES", SqlKind.OTHER);
protected final String preposition;
protected final SqlIdentifier catalogName;
protected final SqlLikeType likeType;
protected final boolean notLike;
protected final SqlCharStringLiteral likeLiteral;
public SqlShowDatabases(SqlParserPos pos) {
super(pos);
this.preposition = null;
this.catalogName = null;
this.likeType = null;
this.notLike = false;
this.likeLiteral = null;
}
public SqlShowDatabases(
SqlParserPos pos,
String preposition,
SqlIdentifier catalogName,
String likeType,
boolean notLike,
SqlCharStringLiteral likeLiteral) {
super(pos);
this.preposition = preposition;
this.catalogName =
preposition != null
? requireNonNull(catalogName, "Catalog name must not be null.")
: null;
if (likeType != null) {
this.likeType = SqlLikeType.of(likeType);
this.likeLiteral = requireNonNull(likeLiteral, "Like pattern must not be null");
} else {
this.likeType = null;
this.likeLiteral = null;
}
this.notLike = notLike;
}
public String getPreposition() {
return preposition;
}
public SqlIdentifier getCatalogName() {
return catalogName;
}
public String catalogName() {
return catalogName != null ? catalogName.getSimple() : null;
}
public SqlLikeType getLikeType() {
return likeType;
}
public boolean isLike() {
return likeType == SqlLikeType.LIKE;
}
public boolean isILike() {
return likeType == SqlLikeType.ILIKE;
}
public boolean isNotLike() {
return notLike;
}
public boolean isWithLike() {
return isLike() || isILike();
}
public SqlCharStringLiteral getLikeLiteral() {
return likeLiteral;
}
public String getLikeSqlPattern() {
return Objects.isNull(likeLiteral) ? null : likeLiteral.getValueAs(String.class);
}
@Override
public SqlOperator getOperator() {
return OPERATOR;
}
@Override
public List<SqlNode> getOperandList() {
return Objects.isNull(this.catalogName)
? Collections.emptyList()
: Collections.singletonList(catalogName);
}
@Override
public void unparse(SqlWriter writer, int leftPrec, int rightPrec) {
if (this.preposition == null) {
writer.keyword("SHOW DATABASES");
} else if (catalogName != null) {
writer.keyword("SHOW DATABASES " + this.preposition);
catalogName.unparse(writer, leftPrec, rightPrec);
}
if (isWithLike()) {
if (isNotLike()) {
writer.keyword(String.format("NOT %s '%s'", likeType.name(), getLikeSqlPattern()));
} else {
writer.keyword(String.format("%s '%s'", likeType.name(), getLikeSqlPattern()));
}
}
}
// ShowDatabasesOperation
public class ShowDatabasesOperation implements ShowOperation {
private final String preposition;
private final String catalogName;
private final OperationLikeType likeType;
private final boolean notLike;
private final String likePattern;
/** Use when there is no sub-clause. */
public ShowDatabasesOperation() {
this.preposition = null;
this.catalogName = null;
this.likeType = null;
this.notLike = false;
this.likePattern = null;
}
/** Use when there is only like. */
public ShowDatabasesOperation(String likeType, boolean notLike, String likePattern) {
this.preposition = null;
this.catalogName = null;
this.likeType = OperationLikeType.of(checkNotNull(likeType, "Like type must not be null"));
this.likePattern = checkNotNull(likePattern, "Like pattern must not be null");
this.notLike = notLike;
}
/** Use when there is preposition and like. */
public ShowDatabasesOperation(
String preposition,
String catalogName,
String likeType,
boolean notLike,
String likePattern) {
this.preposition = checkNotNull(preposition, "Preposition must not be null");
this.catalogName = checkNotNull(catalogName, "Catalog name must not be null");
if (likeType != null) {
this.likeType = OperationLikeType.of(likeType);
this.likePattern = checkNotNull(likePattern, "Like pattern must not be null");
} else {
this.likeType = null;
this.likePattern = null;
}
this.notLike = notLike;
}
public String getPreposition() {
return preposition;
}
public String getCatalogName() {
return catalogName;
}
public boolean isLike() {
return likeType == OperationLikeType.LIKE;
}
public boolean isIlike() {
return likeType == OperationLikeType.ILIKE;
}
public boolean isWithLike() {
return isLike() || isIlike();
}
public boolean isNotLike() {
return notLike;
}
public String getLikePattern() {
return likePattern;
}
@Override
public String asSummaryString() {
StringBuilder builder = new StringBuilder().append("SHOW DATABASES");
if (preposition != null) {
builder.append(String.format(" %s %s", preposition, catalogName));
}
if (isWithLike()) {
if (notLike) {
builder.append(String.format(" NOT %s %s", likeType.name(), likePattern));
} else {
builder.append(String.format(" %s %s", likeType.name(), likePattern));
}
}
return builder.toString();
}
}
// omit here. basically same as ShowCatalogs/ShowDatabases above.
// SqlShowFunctions & ShowFunctionsOperation
// SqlShowViews & ShowViewsOperation
// SqlShowModules & ShowModulesOperation
// SqlShowJars & ShowJarsOperation
// SqlShowJobs & ShowJobsOperation |
...