...
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 | RDBMS, such as Oracle, MariaDB, TiDB also support it. |
SnowFlake |
SHOW CREATE FUNCTION [schema_name.]function_name
describe catalog
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. |
No
MySQL
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.
...
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:
...