Drop an external table along with data

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 's3a://andrena'
  TBLPROPERTIES ('external.table.purge'='true');

Run DROP TABLE on the external table.
```
DROP TABLE names_text;
```
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 HDFS.

Prevent data in external table from being deleted by a DROP TABLE statement.

ALTER TABLE addresses_text SET TBLPROPERTIES ('external.table.purge'='false');