[jira] [Commented] (FLINK-25684) Support enhanced show databases syntax

["Francesco Guardiani (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/FLINK-25684?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17519492#comment-17519492 ] 

Francesco Guardiani commented on FLINK-25684:
---------------------------------------------

Hi [~jark] [~lzljs3620320] [~ZhangChaoming], I left a comment on the PR about whether it makes sense to have this feature or not. I'm gonna repost here for broader visibility:
{quote}One thing about this PR which I'm not sure about is that we're adding support only for LIKE, which is a very specific feature, which might not cover all use cases out there for querying the catalog and look what tables and databases you have.

This makes sense, but on the other hand I think it's limited and very specific. I wonder whether we should approach to this problem from a different angle, which is not continuing to include syntax, but rather go in the direction that other traditional databases already do: Allow to query catalog informations (databases, tables, functions, etc) as if they are other tables, by reusing everything we already have. This then would allow users to query catalog informations (tables, databases, functions, etc) as if they are normal flink tables by using SELECT and every other functionality provided by projection, filter and calc operators. As I said, this is the approach took by most traditional databases, e.g. MySQL information_schema.

Implementation wise, it should even be relatively easy, as you just need to implement a DynamicTableSource for querying the catalog and then automatically register it.
{quote}
Do you have any opinions about this? Wouldn't it be a better and more definitive solution for the problem?

> Support enhanced show databases syntax
> --------------------------------------
>
>                 Key: FLINK-25684
>                 URL: https://issues.apache.org/jira/browse/FLINK-25684
>             Project: Flink
>          Issue Type: Improvement
>          Components: Table SQL / API
>            Reporter: Moses
>            Assignee: Moses
>            Priority: Major
>              Labels: pull-request-available
>
> Enhanced `show databases` statement like ` show databasesfrom like 'db%' ` has been supported broadly in many popular SQL engine like Spark SQL/MySQL.
> We could use such statement to easily show the databases that we wannted.
> h3. SHOW DATABSES [ LIKE regex_pattern ]
> Examples:
> {code:java}
> Flink SQL> create database db1;
> [INFO] Execute statement succeed.
> Flink SQL> create database db1_1;
> [INFO] Execute statement succeed.
> Flink SQL> create database pre_db;
> [INFO] Execute statement succeed.
> Flink SQL> show databases;
> +------------------+
> |    database name |
> +------------------+
> | default_database |
> |              db1 |
> |            db1_1 |
> |           pre_db |
> +------------------+
> 4 rows in set
> Flink SQL> show databases like 'db1';
> +---------------+
> | database name |
> +---------------+
> |           db1 |
> +---------------+
> 1 row in set
> Flink SQL> show databases like 'db%';
> +---------------+
> | database name |
> +---------------+
> |           db1 |
> |         db1_1 |
> +---------------+
> 2 rows in set
> Flink SQL> show databases like '%db%';
> +---------------+
> | database name |
> +---------------+
> |           db1 |
> |         db1_1 |
> |        pre_db |
> +---------------+
> 3 rows in set
> Flink SQL> show databases like '%db';
> +---------------+
> | database name |
> +---------------+
> |        pre_db |
> +---------------+
> 1 row in set
> {code}



--
This message was sent by Atlassian Jira
(v8.20.1#820001)