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 |
We can see current flink many sql statements only support showing with full datas, without 'FROM/IN' or 'LIKE' filter clause.
show create | Support or Not |
show create database | No |
show create table | Yes |
show create view | Yes |
show create function | No |
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
before | after(under discussed) | Note | |
show catalogs | SHOW CATALOGS | SHOW CATALOGS [ [NOT] LIKE <sql_like_pattern> ] | |
show databases | SHOW DATABASES | SHOW DATABASES [ ( FROM | IN ) catalog_name] [ [NOT] LIKE <sql_like_pattern> ] | |
show functions | SHOW [USER] FUNCTIONS | SHOW [USER] FUNCTIONS [ ( FROM | IN ) [catalog_name.]database_name ] [ [NOT] LIKE <sql_like_pattern> ] | |
show views | SHOW VIEWS | SHOW VIEWS [ ( FROM | IN ) [catalog_name.]database_name ] [ [NOT] LIKE <sql_like_pattern> ] | |
show modules | SHOW [FULL] MODULES | SHOW [FULL] MODULES [ [NOT] LIKE <sql_like_pattern> ] | |
show jars | SHOW JARS | SHOW JARS [ [NOT] LIKE <sql_like_pattern> ] | only work in SQL CLI or SQL Gateway. |
show jobs | SHOW JOBS | SHOW JOBS [ [NOT] LIKE <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.
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 Operations could be added or changed.
// Add DescribeCatalogOperation -> Catalog // Add DescribeDatabaseOperation -> CatalogDataBase // Add DescribeFunctionOperation -> CatalogFunction // Changed Operations(offer filtering support) // ShowCatalogsOperation public class ShowCatalogsOperation implements ShowOperation { // enable like private final boolean useLike; // true for not like, false is like private final boolean notLike; // like or not like pattern (sql-like-pattern, consistent with current flink behavior) private final String likePattern; public ShowCatalogsOperation(boolean useLike, boolean notLike, String likePattern) { this.useLike = useLike; this.notLike = notLike; this.likePattern = likePattern; } } // ShowDatabasesOperation public class ShowDatabasesOperation implements ShowOperation { private final String catalogName; private final boolean useLike; private final boolean notLike; private final String likePattern; // in or from to indicate specific catalog or database private final String preposition; public ShowDatabasesOperation( String catalogName, boolean useLike, boolean notLike, String likePattern, String preposition) { this.catalogName = catalogName; this.useLike = useLike; this.notLike = notLike; this.likePattern = likePattern; this.preposition = preposition; } } // ShowFunctionsOperation public class ShowFunctionsOperation implements ShowOperation { private final String catalogName; private final String databaseName; private final boolean useLike; private final boolean notLike; private final String likePattern; private final String preposition; private final FunctionScope functionScope; /** * Represent scope of function. * * <ul> * <li><b>USER</b> return only user-defined functions * <li><b>ALL</b> return all user-defined and built-in functions * </ul> */ public enum FunctionScope { USER, ALL } public ShowFunctionsOperation( String catalogName, String databaseName, boolean useLike, boolean notLike, String likePattern, String preposition, FunctionScope functionScope) { this.catalogName = catalogName; this.databaseName = databaseName; this.useLike = useLike; this.notLike = notLike; this.likePattern = likePattern; this.preposition = preposition; this.functionScope = functionScope; } public FunctionScope getFunctionScope() { return functionScope; } } // ShowViewsOperation public class ShowViewsOperation implements ShowOperation { private final String catalogName; private final String databaseName; private final boolean useLike; private final boolean notLike; private final String likePattern; private final String preposition; public ShowViewsOperation( String catalogName, String databaseName, boolean useLike, boolean notLike, String likePattern, String preposition) { this.catalogName = catalogName; this.databaseName = databaseName; this.useLike = useLike; this.notLike = notLike; this.likePattern = likePattern; this.preposition = preposition; } } // ShowModulesOperation public class ShowModulesOperation implements ShowOperation { private final boolean requireFull; private final boolean useLike; private final boolean notLike; private final String likePattern; public ShowModulesOperation( boolean requireFull, boolean useLike, boolean notLike, String likePattern) { this.requireFull = requireFull; this.useLike = useLike; this.notLike = notLike; this.likePattern = likePattern; } public boolean requireFull() { return requireFull; } } // ShowJarsOperation public class ShowJarsOperation implements ShowOperation { private final boolean useLike; private final boolean notLike; private final String likePattern; public ShowJarsOperation(boolean useLike, boolean notLike, String likePattern) { this.useLike = useLike; this.notLike = notLike; this.likePattern = likePattern; } } // ShowJobsOperation public class ShowJobsOperation implements ShowOperation { private final boolean useLike; private final boolean notLike; private final String likePattern; public ShowJobsOperation(boolean useLike, boolean notLike, String likePattern) { this.useLike = useLike; this.notLike = notLike; this.likePattern = likePattern; } }
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.