Drop Table

In this section, you will find information about Hive's Drop Table statement and their Snowflake equivalent.

Description

A Drop Table statement is used to remove a table. This statement varies a little between Hive and Snowflake. Please double-check each documentation for more information regarding the differences.

In Hive, the Drop Table syntax is:

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

In Snowflake, the Drop table syntax is:

DROP TABLE [IF EXISTS] <table_name> [ CASCADE | RESTRICT ]

The main difference is that Snowflake does not have an equivalence for the PURGE clause, as the table will not be permanently removed from the system immediately.

Simple case

Hive

DROP TABLE IF EXISTS table1;

Snowflake

DROP TABLE IF EXISTS table1;

In this scenario, the Drop Table statement is used as simple as possible.

With PURGE clause

Hive

DROP TABLE table1 PURGE;

Snowflake

/*** MSC-WARNING - MSCEWI1108 - PURGE REMOVED FROM DROP TABLE STATEMENT. ***/
DROP TABLE table1;

This scenario uses the Drop Table statement with the PURGE clause. Since there is no equivalent in Snowflake for this clause within a Drop Table declaration, it is eliminated and an EWI is added informing the operation performed.

Known issues

1. PURGE Clause

Snowflake does not support the Drop Table statement with the PURGE clause. In Snowflake, deleting a table does not permanently remove it from the system. A deleted table version is kept in Time Travel for a set number of days (7 days by default). For more information check Snowflake documentation.

Related EWIs

1. MSCEWI1108: Purge removed from Drop Table.