1. Application Development
2. Table API & SQL
3. SQL
4. DROP Statements

DROP Statements

• Run a DROP statement
• DROP TABLE
• DROP DATABASE
• DROP VIEW
• DROP FUNCTION

DROP statements are used to remove a registered table/view/function from current or specified Catalog.

Flink SQL supports the following DROP statements for now:

• DROP TABLE
• DROP DATABASE
• DROP VIEW
• DROP FUNCTION

Run a DROP statement

DROP statements can be executed with the executeSql() method of the TableEnvironment, or executed in SQL CLI. The executeSql() method returns ‘OK’ for a successful DROP operation, otherwise will throw an exception.

The following examples show how to run a DROP statement in TableEnvironment and in SQL CLI.

EnvironmentSettings settings = EnvironmentSettings.newInstance()...
TableEnvironment tableEnv = TableEnvironment.create(settings);

// register a table named "Orders"
tableEnv.executeSql("CREATE TABLE Orders (`user` BIGINT, product STRING, amount INT) WITH (...)");

// a string array: ["Orders"]
String[] tables = tableEnv.listTables();
// or tableEnv.executeSql("SHOW TABLES").print();

// drop "Orders" table from catalog
tableEnv.executeSql("DROP TABLE Orders");

// an empty string array
String[] tables = tableEnv.listTables();
// or tableEnv.executeSql("SHOW TABLES").print();

val settings = EnvironmentSettings.newInstance()...
val tableEnv = TableEnvironment.create(settings)

// register a table named "Orders"
tableEnv.executeSql("CREATE TABLE Orders (`user` BIGINT, product STRING, amount INT) WITH (...)")

// a string array: ["Orders"]
val tables = tableEnv.listTables()
// or tableEnv.executeSql("SHOW TABLES").print()

// drop "Orders" table from catalog
tableEnv.executeSql("DROP TABLE Orders")

// an empty string array
val tables = tableEnv.listTables()
// or tableEnv.executeSql("SHOW TABLES").print()

settings = EnvironmentSettings.new_instance()...
table_env = StreamTableEnvironment.create(env, settings)

# a string array: ["Orders"]
tables = table_env.list_tables()
# or table_env.execute_sql("SHOW TABLES").print()

# drop "Orders" table from catalog
table_env.execute_sql("DROP TABLE Orders")

# an empty string array
tables = table_env.list_tables()
# or table_env.execute_sql("SHOW TABLES").print()

Flink SQL> CREATE TABLE Orders (`user` BIGINT, product STRING, amount INT) WITH (...);
[INFO] Table has been created.

Flink SQL> SHOW TABLES;
Orders

Flink SQL> DROP TABLE Orders;
[INFO] Table has been removed.

Flink SQL> SHOW TABLES;
[INFO] Result was empty.

DROP TABLE

DROP TABLE [IF EXISTS] [catalog_name.][db_name.]table_name

Drop a table with the given table name. If the table to drop does not exist, an exception is thrown.

IF EXISTS

If the table does not exist, nothing happens.

DROP DATABASE

DROP DATABASE [IF EXISTS] [catalog_name.]db_name [ (RESTRICT | CASCADE) ]

Drop a database with the given database name. If the database to drop does not exist, an exception is thrown.

IF EXISTS

If the database 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.

DROP VIEW

DROP [TEMPORARY] VIEW  [IF EXISTS] [catalog_name.][db_name.]view_name

Drop a view that has catalog and database namespaces. If the view to drop does not exist, an exception is thrown.

TEMPORARY

Drop temporary view that has catalog and database namespaces.

IF EXISTS

If the view does not exist, nothing happens.

MAINTAIN DEPENDENCIES Flink does not maintain dependencies of view by CASCADE/RESTRICT keywords, the current way is producing postpone error message when user tries to use the view under the scenarios like the underlying table of view has been dropped.

DROP FUNCTION

DROP [TEMPORARY|TEMPORARY SYSTEM] FUNCTION [IF EXISTS] [catalog_name.][db_name.]function_name;

Drop a catalog function that has catalog and database namespaces. If the function to drop does not exist, an exception is thrown.

TEMPORARY

Drop temporary catalog function that has catalog and database namespaces.

TEMPORARY SYSTEM

Drop temporary system function that has no namespace.

IF EXISTS

If the function doesn’t exists, nothing happens.