Discussion thread | https://lists.apache.org/thread/54fyd27m8on1cf3hn6dz564zqmkobjyd |
---|---|
Vote thread | |
JIRA | |
Release |
Please keep the discussion on the mailing list rather than commenting on the wiki (wiki discussions get unwieldy fast).
Motivation:
Currently flink sql auxiliary statements has supported some good features such as catalog/databases/table support.
These features have been a critical integration for Flink to be able to manage metadata like a classic RDBMS and make developers more easy to create or modify or list needed meta datas.
But these features are not very complete compared with other popular engines such as spark, presto.
For example, many popular engines support show operation with filtering except flink, and support describe other object(flink only support describe table).
show | Support or Not | Support filter or Not |
show tables | Yes | Yes |
show columns | Yes | Yes |
show catalogs | Yes | No |
show databases | Yes | No |
show functions | Yes | No |
show views | Yes | No |
show modules | Yes | No |
show jars | Yes | No |
show jobs | Yes | No |
show connectors | No | No |
We can see current flink many sql statements only support showing with full datas, without 'FROM/IN' or 'LIKE' filter clause.
describe | Support or Not | note |
describe catalog | No | has sqlNode but not support in table api |
describe database | No | has sqlNode but not support in table api |
describe table | Yes | |
describe function | No | |
describe view | No |
current flink only supports describing tables.
So we propose this flip, try to support these useful features.
Proposed Syntax Changes:
We compare flink with other popular engines and give an improved syntax example. Welcome everyone to discuss and improve the final syntax.
Because it may be modified under discuss, we put it on the google docs. please see FLIP-297: Improve Auxiliary Sql Statements Docs
Note: we both support LIKE and ILIKE in this FLIP.
LIKE: sql_like_pattern, case sensitive
ILIKE: sql_like_pattern, same behavior as LIKE except case insensitive
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 catalog_name | |
describe database | Not Support | { DESCRIBE | DESC } DATABASE [ EXTENDED ] db_name | |
describe function | Not Support | { DESCRIBE | DESC } FUNCTION [ EXTENDED ] function_name | |
... |
Proposed CataLog API Changes:
No need to add new apis (there is a discussion about api changes, pls see the discuss thread).
We can use such as TableEnvironment.getCatalog(catalogName).get().listDatabases(), TableEnvironment.getCatalog(catalogName).get().listFunctions(databaseName) for util to get result.
In TableEnvironmentImpl we do filter for returned full results.
Some SqlNodes and Operations could be added or changed.
// Add SqlDescribeCatalog & DescribeCatalogOperation // Add SqlDescribeDatabase & DescribeDatabaseOperation // Add SqlDescribeFunction & DescribeFunctionOperation // 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 boolean withLike; private final boolean notLike; private final String likePattern; public ShowDatabasesOperation() { this.preposition = null; this.catalogName = null; this.withLike = false; this.notLike = false; this.likePattern = null; } public ShowDatabasesOperation(boolean notLike, String likePattern) { this.preposition = null; this.catalogName = null; this.withLike = likePattern != null; this.notLike = notLike; this.likePattern = likePattern; } public ShowDatabasesOperation( String preposition, String catalogName, boolean notLike, String likePattern) { this.preposition = checkNotNull(preposition, "Preposition must not be null"); this.catalogName = checkNotNull(catalogName, "Catalog name must not be null"); this.withLike = likePattern != null; this.notLike = notLike; this.likePattern = likePattern; } public String getPreposition() { return preposition; } public String getCatalogName() { return catalogName; } public boolean isWithLike() { return withLike; } 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 (withLike) { if (notLike) { builder.append(String.format(" %s LIKE %s", "NOT", likePattern)); } else { builder.append(String.format(" LIKE %s", likePattern)); } } return builder.toString(); } } 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
Future Work
to be added
Compatibility, Deprecation, and Migration Plan
No compatibility and migration.
Test Plan
Add new cases to cover these features.
Rejected Alternatives
to be added.