THIS IS A TEST INSTANCE. ALL YOUR CHANGES WILL BE LOST!!!!
...
But these features are not very complete compared with other popular engines such as spark, hive, presto and commercial engines such as snowflake.
For example, many popular engines support show operation with filtering except flink, and support describe other objectmany objects(flink only support supports describe table).
And these improved features are very useful for users and developers.
Comparison with other popular engines
Because each engine has its own personalized auxiliary sql statements features, here are some common operations listed as much as possible, and compare what other unrealized abilities of flink.
show catalogs
Support or Not
filter
Engine | Support |
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
, If support show the syntax | |
Flink | SHOW CATALOGS |
Spark | No |
Hive | No |
Presto | SHOW CATALOGS [ LIKE pattern ] |
MySQL | No |
SnowFlake | No |
show databases
Engine | Support or Not, If support show the syntax |
Flink | SHOW DATABASES |
Spark | SHOW { DATABASES | SCHEMAS } [ LIKE regex_pattern ] |
Hive | SHOW (DATABASES|SCHEMAS) [LIKE 'identifier_with_wildcards'] |
Presto | SHOW SCHEMAS [ FROM catalog ] [ LIKE pattern ] |
MySQL | SHOW {DATABASES | SCHEMAS} [LIKE 'pattern' | WHERE expr] |
SnowFlake | SHOW [ TERSE ] DATABASES [ HISTORY ] [ LIKE '<pattern>' ] [ STARTS WITH '<name_string>' ] [ LIMIT <rows> [ FROM '<name_string>' ] ] |
show functions
Engine | Support or Not, If support show the syntax |
Flink | SHOW [USER] FUNCTIONS |
Spark | SHOW [ function_kind ] FUNCTIONS [ { FROM | IN } database_name ] [ LIKE regex_pattern ] |
Hive | SHOW FUNCTIONS [LIKE "<pattern>"] |
Presto | SHOW FUNCTIONS [ LIKE pattern [ ESCAPE 'escape_character' ] ] |
MySQL | SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION' ORDER BY ROUTINE_NAME; |
SnowFlake | SHOW FUNCTIONS [ LIKE '<pattern>' ] [ IN { ACCOUNT | DATABASE | DATABASE <database_name> | SCHEMA | SCHEMA <schema_name> | <schema_name> } ] |
show views
Engine | Support or Not, If support show the syntax |
Flink | SHOW VIEWS |
Spark | SHOW VIEWS [ { FROM | IN } database_name ] [ LIKE regex_pattern ] |
Hive | SHOW VIEWS [IN/FROM database_name] [LIKE 'pattern_with_wildcards'] |
Presto | No |
MySQL | SHOW FULL TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr] (Alternative syntax) |
SnowFlake | SHOW [ TERSE ] VIEWS [ LIKE '<pattern>' ] [ IN { ACCOUNT | DATABASE [ <db_name> ] | [ SCHEMA ] [ <schema_name> ] } ] [ STARTS WITH '<name_string>' ] [ LIMIT <rows> [ FROM '<name_string>' ] ] |
show modules
Engine | Support or Not, If support show the syntax |
Flink | SHOW [FULL] MODULES |
Spark | No |
Hive | No |
Presto | No |
MySQL | No |
SnowFlake | No |
show jars
Engine | Support or Not, If support show the syntax | Note |
Flink | SHOW JARS | only work in SQL CLI or SQL Gateway. |
Spark | No | |
Hive | No | |
Presto | No | |
MySQL | No | |
SnowFlake | No |
show jobs
Engine | Support or Not, If support show the syntax | Note |
Flink | SHOW JOBS | only work in SQL CLI or SQL Gateway. |
Spark | No | |
Hive | No | |
Presto | No | |
MySQL | No | |
SnowFlake | No |
show create database
Engine | Support or Not, If support show the syntax | Note |
Flink | No | |
Spark | SHOW CREATE DATABASE [database_name] | |
Hive | SHOW CREATE DATABASE [database_name] | |
Presto | SHOW CREATE DATABASE [database_name] | |
MySQL | SHOW CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name | |
SnowFlake | SHOW CREATE DATABASE [database_name] |
show create function
Engine | Support or Not, If support show the syntaxes | Note |
Flink | No | |
Spark | No | |
Hive | SHOW CREATE FUNCTION [db_name.]function_name; | |
Presto | SHOW CREATE FUNCTION function_name [ ( parameter_type[, ...] ) ] | |
MySQL | SHOW CREATE FUNCTION func_name | |
SnowFlake | SHOW CREATE FUNCTION [schema_name.]function_name |
describe catalog
Engine | Support or Not, If support show the syntaxes | Note |
Flink | No | |
Spark | No | |
Hive | No | |
Presto | No | |
MySQL | No | |
SnowFlake | DESC[RIBE] SCHEMA <schema_name> |
describe database
Engine | Support or Not, If support show the syntaxes | Note |
Flink | { DESC | DESCRIBE } DATABASE [ EXTENDED ] db_name | but not expose to table 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] DATABASE <database_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 <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
...
describe table
...
Yes
...
describe function
...
No
...
describe view
...
No
current flink only supports describing tables.
So we
...
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.
...
proposed this FLIP. And also some unique statements such as show jars/show modules in flink can also support this feature.
Proposed Syntax Changes:
Note: we both support LIKE and ILIKE in this FLIP.
...
ILIKE: sql_like_pattern, same behavior as LIKE except case insensitive
And For consistency, we will add ILIKE support for query either.
Because calcite already support parse ILIKE and exist SqlLikeOperator(NonStandard). We just need to support it in table & sql api.
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 If the optional like spark and hive. | |
describe function | Not Support | { DESCRIBE | DESC } FUNCTION [ EXTENDED ] function_name If the optional | |
... |
...