DROP Statements

CREATE Statements #

With Hive dialect, the following DROP statements are supported for now:

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

DROP DATABASE #

Description #

DROP DATABASE statement is used to drop a database as well as the tables/directories associated with the database.

Syntax # 

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

The use of SCHEMA and DATABASE are interchangeable - they mean the same thing. The default behavior is RESTRICT, where DROP DATABASE will fail if the database is not empty. To drop the tables in the database as well, use DROP DATABASE ... CASCADE.

DROP returns an error if the database doesn’t exist, unless IF EXISTS is specified or the configuration variable hive.exec.drop.ignorenonexistent is set to true.

Examples # 

DROP DATABASE db1 CASCADE;

DROP TABLE #

Description #

DROP TABLE statement removes metadata and data for this table. The data is actually moved to the .Trash/Current directory if Trash is configured. The metadata is completely lost.

When drop an EXTERNAL table, data in the table will not be deleted from the filesystem.

Syntax # 

DROP TABLE [IF EXISTS] table_name;

DROP returns an error if the table doesn’t exist, unless IF EXISTS is specified or the configuration variable hive.exec.drop.ignorenonexistent is set to true.

Examples # 

DROP TABLE IF EXISTS t1;

DROP VIEW #

Description #

DROP VIEW statement is used to removed metadata for the specified view.

Syntax # 

DROP VIEW [IF EXISTS] [db_name.]view_name;

DROP returns an error if the view doesn’t exist, unless IF EXISTS is specified or the configuration variable hive.exec.drop.ignorenonexistent is set to true.

Examples # 

DROP VIEW IF EXISTS v1;

DROP MARCO #

DROP MARCO statement is used to drop the existing MARCO. Please refer to CREATE MARCO for how to create MARCO.

Syntax # 

DROP TEMPORARY MACRO [IF EXISTS] macro_name;

DROP returns an error if the macro doesn’t exist, unless IF EXISTS is specified.

Examples # 

DROP TEMPORARY MACRO IF EXISTS m1;

DROP FUNCTION #

DROP FUNCTION statement is used to drop the existing FUNCTION.

Syntax # 

--- Drop temporary function
DROP TEMPORARY FUNCTION [IF EXISTS] function_name;

--- Drop permanent function
DROP FUNCTION [IF EXISTS] function_name;

DROP returns an error if the function doesn’t exist, unless IF EXISTS is specified or the configuration variable hive.exec.drop.ignorenonexistent is set to true.

Examples # 

DROP FUNCTION IF EXISTS f1;