DROP DATABASE

Description

Removes a database and its associated directory from the file system. An exception is thrown if the database does not exist.

Syntax

DROP { DATABASE | SCHEMA } [ IF EXISTS ] db_name [ RESTRICT | CASCADE ]

Parameters

• DATABASE | SCHEMA
  • These keywords are interchangeable. Either can be used to refer to the database object.
• IF EXISTS
  • Prevents an exception from being raised when the target database is not found.
• RESTRICT
  • Default behavior. Prevents deletion of databases that contain tables or other objects.
• CASCADE
  • Removes all associated tables and functions before dropping the database.

Examples

-- Create `inventory_db` Database
CREATE DATABASE inventory_db COMMENT 'This database is used to maintain Inventory';

-- Remove the database along with all its tables
DROP DATABASE inventory_db CASCADE;

-- Safely attempt removal without throwing an error
DROP DATABASE IF EXISTS inventory_db CASCADE;

DROP FUNCTION

Description

Deletes a temporary or user-defined function (UDF). An exception is thrown if the function does not exist.

Syntax

DROP [ TEMPORARY ] FUNCTION [ IF EXISTS ] function_name

Parameters

• function_name
  • Specifies the name of the existing function to remove. Optionally qualified with a database name using the syntax [database_name.]function_name.
• TEMPORARY
  • Use this modifier to remove a temporary function specifically.
• IF EXISTS
  • Prevents an exception from being raised when the target function is not found.

Examples

-- Create a permanent function `test_avg`
CREATE FUNCTION test_avg AS 'org.apache.hadoop.hive.ql.udf.generic.GenericUDAFAverage';

-- Display all user-defined functions
SHOW USER FUNCTIONS;
+----------------+
|        function|
+----------------+
|default.test_avg|
+----------------+

-- Create a temporary function with the same name
CREATE TEMPORARY FUNCTION test_avg AS
    'org.apache.hadoop.hive.ql.udf.generic.GenericUDAFAverage';


-- Verify both functions exist
SHOW USER FUNCTIONS;
+----------------+
|        function|
+----------------+
|default.test_avg|
|        test_avg|
+----------------+

-- Remove the permanent function
DROP FUNCTION test_avg;

-- Attempt to drop a non-existent permanent function
DROP FUNCTION test_avg;
Error: Error running query:
org.apache.spark.sql.catalyst.analysis.NoSuchPermanentFunctionException:
Function 'default.test_avg' not found in database 'default'; (state=,code=0)

-- List remaining functions after dropping permanent function
SHOW USER FUNCTIONS;
+--------+
|function|
+--------+
|test_avg|
+--------+

-- Remove the temporary function safely
DROP TEMPORARY FUNCTION IF EXISTS test_avg;

DROP TABLE

Description

Removes a table and its associated directory from the file system, unless the table is an external table. For external tables, only the metadata is removed from the metastore. Cached tables are uncached along with all their dependents.

Syntax

DROP TABLE [ IF EXISTS ] table_identifier [ PURGE ]

Parameters

• IF EXISTS
  • Prevents an exception from being raised when the target table is not found.
• table_identifier
  • Specifies the table to remove. Optionally qualified with a database name using the syntax [database_name.]table_name.
• PURGE
  • Immediately removes table data without moving it to trash. Available in Hive Metastore 0.14.0 and later versions.

Examples

-- Remove the `employeetable` table
DROP TABLE employeetable;


-- Remove the table from a specific database
DROP TABLE userdb.employeetable;

-- Attempt to drop a non-existent table (raises exception)
DROP TABLE employeetable;
Error: org.apache.spark.sql.AnalysisException: Table or view not found: employeetable;
(state=,code=0)

-- Safely attempt to drop without throwing an error
DROP TABLE IF EXISTS employeetable;

-- Permanently remove the table and skip trash
DROP TABLE employeetable PURGE;

DROP VIEW

Description

Removes metadata associated with a specified view from the catalog.

Syntax

DROP VIEW [ IF EXISTS ] view_identifier

Parameters

• IF EXISTS
  • Prevents an exception from being raised when the target view is not found.
• view_identifier
  • Specifies the view to remove. Optionally qualified with a database name using the syntax [database_name.]view_name.

Examples

-- Remove the `employeeView` view
DROP VIEW employeeView;

-- Remove the view from a specific database
DROP VIEW userdb.employeeView;


-- Attempt to drop a non-existent view (raises exception)
DROP VIEW employeeView;
Error: org.apache.spark.sql.AnalysisException: Table or view not found: employeeView;
(state=,code=0)

-- Safely attempt to drop without throwing an error
DROP VIEW IF EXISTS employeeView;