...

Current state: ["Under Discussion"]

Discussion thread: here httpshttp://mail-archives.apache.org/mod_mbox/flink-dev/201909.mbox/%3CFFD7AA16-D05F-4CFD-A908-8DDE28FB561C%40gmail.com%3Eapache-flink-mailing-list-archive.1008284.n3.nabble.com/DISCUSS-FLIP-69-Flink-SQL-DDL-Enhancement-td33090.html

JIRA: here: TBD

Released:  TBD

Please keep the discussion on the mailing list rather than commenting on the wiki (wiki discussions get unwieldy fast).

Motivation

...

In flink 1.9, we have introduced some awesome features such as complete catalog support[1] and sql ddl support[2]. These features have been a critical integration for Flink to be able to manage data and metadata like a classic RDBMS and make developers more easy to construct their real-time/off-line warehouse or sth similar base on flink.

Despite that big advancement, there is still a lack of support on how Flink SQL DDL to manage metadata and data.

1. Don’t support show/describe/use catalog(s)
2. Don’t support create/drop/alter/use/show/desc database(s) 
3. Don’t support show/desc table(s) and create table as select
4. Don’t support create/drop/show function(s)
5. Don’t support create/drop/show/desc view(s)

Goals:

We would like to achieve the following goals in this FLIP.

• Add Catalog DDL enhancement support
• Add Database DDL enhancement support
• Add Table DDL enhancement support
• Add Function DDL enhancement support

Note:

The following issues are out of the scope of this FLIP. They will be taken care of by other teams/committers.

1. DDL related to View(s)
2. DDL related to Partition(s)
3. Make SQL CLIENT and tEnv.sqlUpdate support new added ddl in a unified way

Public Interfaces

Briefly list any new interfaces that will be introduced as part of this proposal or any existing interfaces that will be removed or changed. The purpose of this section is to concisely call out the public contract that will come along with this feature.

Proposed Changes

There are a few DDLs following that are already supported in SQL client. We want them should be migrated using the parser approach.

A public interface is any change to the following:

• Binary log format

• The network protocol and api behavior

• Any class in the public packages under clientsConfiguration, especially client configuration

  • org/apache/kafka/common/serialization

  • org/apache/kafka/common

  • org/apache/kafka/common/errors

  • org/apache/kafka/clients/producer

  • org/apache/kafka/clients/consumer (eventually, once stable)

• Monitoring

• Command line tools and arguments

• Anything else that will likely break existing users in some way when they upgrade

Proposed Changes

Catalog DDL:

We propose to add the following DDLs related to catalog operations.

• showCatalogsStatement

 SHOW CATALOGS

Return all databases in the current catalog.

note: already support in sql client but need support in tEnv.sqlQuery();

• describeCatalogStatement

DESCRIBE  CATALOG [ EXTENDED] catalogName

Return the metadata of an existing catalogName 

EXTENDED:

Display the catalog properties.

• useCatalogStatement

USE CATALOG catalogName 

Set the current catalog. 

note: already support in sql client but need support in tEnv.sqlQuery();

Database DDL:

We propose to add the following DDLs related to database operations.

• createDatabaseStatement:

  CREATE  DATABASE [ IF NOT EXISTS ] [ catalogName.] dataBaseName 

 [ COMMENT database_comment ]

 [WITH ( name=value [, name=value]*)]

IF NOT EXISTS:

If a database with the same name already exists, nothing will happen.

• dropDatabaseStatement:

DROP  DATABASE [ IF EXISTS ] [ catalogName.] dataBaseName 

[ (RESTRICT|CASCADE)]

IF EXISTS

If the database to drop does not exist, nothing happens.

RESTRICT

Dropping a non-empty database triggers an exception. Enabled by default.

CASCADE

Dropping a non-empty database also drops all associated tables and functions.

• alterDatabaseStatement:

ALTER  DATABASE  [ catalogName.] dataBaseName SET DBPROPERTIES

( name=value [, name=value]*)

Set one or more properties in the specified database. If a particular property is already set in the database, override the old value with the new one.

• useDatabaseStatement:

USE [ catalogName.] dataBaseName 

Set the current database. All subsequent commands that do not explicitly specify a database will use this one. If the provided database does not exist, an exception is thrown.

note: already support in sql client but need support in tEnv.sqlQuery();

• showDatabasesStatement:

 SHOW DATABASES

Return all databases in the current catalog.

note: already support in sql client but need support in tEnv.sqlQuery();

• descDatabaseStatement:

DESCRIBE  DATABASE [ EXTENDED] [ catalogName.] dataBasesName 

Return the metadata of an existing database (name, comment). 

EXTENDED:

Display the database properties.

Table DDL:

We propose to add the following DDLs related to table operations.

• showTablesStatement:

  SHOW TABLES

Return all tables in the current database.

note: already support in sql client but need support in tEnv.sqlQuery();

• descTableStatement:

  DESCRIBE [ EXTENDED]  [[catalogName.] dataBasesName].tableName

Return the metadata of an existing table (column names, data types, and comments). 

EXTENDED

Display detailed information about the table, including table type, properties and so on.

note: already support in sql client but need support in tEnv.sqlQuery();

• alterTableStatement:

  ALTER TABLE  [[catalogName.] dataBasesName].tableName 

REAME TO newTableName

Rename an existing table.

ALTER TABLE  [[catalogName.] dataBasesName].tableName 

SET  TBLROPERTIES ( name=value [, name=value]*)

Set the properties of an existing table 

Function DDL:

• createFunctionStatement:

  CREATE  FUNCTION 

[IF NOT EXISTS] [[catalogName.]databaseName.]functionName AS className.

Create a function indicated by a className

IF NOT EXISTS

If a function with the same name already exists, nothing will happen.

• dropFunctionStatement:

  DROP  FUNCTION  [IF EXISTS] 

[[catalogName.]databaseName.]functionName.

Create a function indicated by a className

IF EXISTS

If a function with the same name not exists, nothing will happen.

• alterFunctionStatement:

  ALTER FUNCTION [IF EXISTS] [[catalogName.]databaseName.]functionName 

RENAME TO new_name;

Rename a non-temporary function to new name.

IF EXISTS

If a function with the same name not exists, nothing will happen.

• showFunctionsStatement:

  SHOW FUNCTIONS

Return all functions in the current database.

note: already support in sql client but need support in tEnv.sqlQuery();Describe the new thing you want to do in appropriate detail. This may be fairly extensive and have large subsections of its own. Or it may be a few sentences. Use judgement based on the scope of the change.

Compatibility, Deprecation, and Migration Plan

...