You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 41 Next »

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, hive, presto and commercial engines such as snowflake.

For example, many popular engines support show operation with filtering except flink, and support describe many objects(flink only 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.

We focus on standard sql, but also consider absorbing the syntax of some mature engines to let users know that this is not standard, just for better use (e.g. ILIKE).


show catalogs

Engine

Support or Not, 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

DESCRIBE FUNCTION [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 flink only supports describing tables.
And also some unique statements such as show jars/show modules in flink can also support this feature.

So we proposed this FLIP.

Proposed Syntax Changes:

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

And For consistency, we will add ILIKE support for query either.

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 catalog_name


describe database

Not Support

{ DESCRIBE | DESC } DATABASE [ EXTENDED ] db_name

If the optional EXTENDED option is specified, it returns the basic metadata information along with the database properties

like spark and hive. 


describe function

Not Support

{ DESCRIBE | DESC } FUNCTION [ EXTENDED ] function_name

If the optional EXTENDED option is specified, the basic metadata information is returned along with the extended information.


...





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.

Operations
// Add SqlDescribeCatalog & DescribeCatalogOperation
// Add SqlDescribeDatabase & DescribeDatabaseOperation
// Add SqlDescribeFunction & DescribeFunctionOperation

About DescribeCatalog:

+-------------------------+-----------------------------+

| catalog_description_item|   catalog_description_value |
+-------------------------+-----------------------------+
|            Catalog Name |                       xxx   |
|               Properties| (key1=val1, key2=val2, ...) |
+-------------------------+-----------------------------+


About DescribeDatabase:

+-------------------------+-----------------------------+
|database_description_item|   database_description_value|
+-------------------------+-----------------------------+
|            Database Name|                    employees|
|              Description|       For software companies|
|               Properties| (key1=val1, key2=val2, ...) |
+-------------------------+-----------------------------+

About DescribeFunction:

+-------------------------+-----------------------------+
|function_description_item|   function_description_value|
+-------------------------+-----------------------------+
|        Function Name    |                    ABS      |
|        Function Language|                    JAVA     |
|        Resource Uri     |                  file://xxx |
|        Details          |                details      |
+-------------------------+-----------------------------+

The concrete describe infos we can get from the specific catalog object.  

// 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 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

TBD.

Compatibility, Deprecation, and Migration Plan

No migration. We will ensure changes of this FLIP will be compatible with current flink behavior.

Test Plan

Add new cases to cover these features.

Rejected Alternatives

to be added.































  • No labels