When you run DROP TABLE on an external table, by default Hive drops only the metadata
(schema). If you want the DROP TABLE command to also remove the actual data in the external
table, as DROP TABLE does on a managed table, you need to configure the table properties
accordingly.
-
Create a CSV file of data you want to query in Hive.
-
Start Hive.
-
Create an external table to store the CSV data, configuring the table so you can drop it along with the data.
CREATE EXTERNAL TABLE IF NOT EXISTS names_text(
a INT, b STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION '<file system>://andrena'
TBLPROPERTIES ('external.table.purge'='true');
-
Run DROP TABLE on the external table.
The table is removed from Hive Metastore and the data stored externally.
For example, names_text
is removed from the Hive Metastore and
the CSV file that stored the data is also deleted from the file
system.
-
Prevent data in external table from being deleted by a DROP TABLE statement.
ALTER TABLE addresses_text SET TBLPROPERTIES ('external.table.purge'='false');