DROP TABLE Statement Syntax for Hive

A DROP TABLE statement deletes a table and its data from a Treasure Data CDP database. Data engineers use it to remove tables directly from HiveQL — for example, to clean up staging tables at the end of an ingestion workflow — without switching to Trino.

Note

This page describes the DROP TABLE syntax supported by the Hive query engine. The Trino query engine also supports DROP TABLE, with slightly different syntax.

Syntax

DROP TABLE [IF EXISTS] [<database_name>.]<table_name> [PURGE]

Where:

<table_name> is the name of the table to delete.
IF EXISTS makes it explicit that the statement succeeds when the table does not exist.
PURGE is accepted for HiveQL compatibility but has no effect. See PURGE Has No Effect.

Note

Dropping a table that does not exist succeeds without an error even when IF EXISTS is omitted.

Examples

Delete a table:

DROP TABLE my_database.staging_events;

Delete a table only if it exists:

DROP TABLE IF EXISTS staging_events;

Behavior

PURGE Has No Effect

In open-source Hive, the PURGE keyword skips the HDFS trash so that the data is deleted immediately and cannot be recovered. In Treasure Data CDP, the lifecycle of deleted table data is managed by the platform, so PURGE is parsed and ignored: DROP TABLE t and DROP TABLE t PURGE are equivalent.

Permissions

The user running the query must have permission to delete the table. Without it, the statement fails with an authorization error and the table is not deleted.

Limitations

FOR REPLICATION Clause

The FOR [METADATA] REPLICATION('<event_id>') clause is not supported and is rejected with an error.