DROP Statements

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 FUNCTION

Run a DROP statement

DROP statements can be executed with the sqlUpdate() method of the TableEnvironment, or executed in SQL CLI. The sqlUpdate() method returns nothing 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.sqlUpdate("CREATE TABLE Orders (`user` BIGINT, product STRING, amount INT) WITH (...)");

// a string array: ["Orders"]
String[] tables = tableEnv.listTable();

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

// an empty string array
String[] tables = tableEnv.listTable();
val settings = EnvironmentSettings.newInstance()...
val tableEnv = TableEnvironment.create(settings)

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

// a string array: ["Orders"]
val tables = tableEnv.listTable()

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

// an empty string array
val tables = tableEnv.listTable()
settings = EnvironmentSettings.newInstance()...
table_env = TableEnvironment.create(settings)

# a string array: ["Orders"]
tables = tableEnv.listTable()

# drop "Orders" table from catalog
tableEnv.sqlUpdate("DROP TABLE Orders")

# an empty string array
tables = tableEnv.listTable()
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 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.