Will data get deleted on dropping internal table using location clause during its creation from hive?

Question

In hive if I create an internal table using the loaction clause (mentioning loaction other than default location of hive) in table creation statement then on dropping that table will it delete the data from the specified location just like it does when the data is in default location of hive?

Answer 1

Yes, it will delete the location even it is not default location of hive also. Let's assume i'm having test table in default database on /user/yashu/test5 directory.

hive> desc formatted test_tmp;
+-------------------------------+-------------------------------------------------------------+-----------------------+--+
|           col_name            |                          data_type                          |        comment        |
+-------------------------------+-------------------------------------------------------------+-----------------------+--+
| # col_name                    | data_type                                                   | comment               |
|                               | NULL                                                        | NULL                  |
| id                            | int                                                         |                       |
| name                          | string                                                      |                       |
|                               | NULL                                                        | NULL                  |
| # Detailed Table Information  | NULL                                                        | NULL                  |
| Database:                     | default                                                     | NULL                  |
| Owner:                        | shu                                                   | NULL                  |
| CreateTime:                   | Fri Mar 23 03:42:15 EDT 2018                                | NULL                  |
| LastAccessTime:               | UNKNOWN                                                     | NULL                  |
| Protect Mode:                 | None                                                        | NULL                  |
| Retention:                    | 0                                                           | NULL                  |
| Location:                     | hdfs://nn1.com/user/yashu/test5                   | NULL                  |
| Table Type:                   | MANAGED_TABLE                                               | NULL                  |
| Table Parameters:             | NULL                                                        | NULL                  |
|                               | numFiles                                                    | 1                     |
|                               | totalSize                                                   | 12                    |
|                               | transient_lastDdlTime                                       | 1521790935            |
|                               | NULL                                                        | NULL                  |
| # Storage Information         | NULL                                                        | NULL                  |
| SerDe Library:                | org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe          | NULL                  |
| InputFormat:                  | org.apache.hadoop.mapred.TextInputFormat                    | NULL                  |
| OutputFormat:                 | org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat  | NULL                  |
| Compressed:                   | No                                                          | NULL                  |
| Num Buckets:                  | -1                                                          | NULL                  |
| Bucket Columns:               | []                                                          | NULL                  |
| Sort Columns:                 | []                                                          | NULL                  |
| Storage Desc Params:          | NULL                                                        | NULL                  |
|                               | field.delim                                                 | ,                     |
|                               | serialization.format                                        | ,                     |
+-------------------------------+-------------------------------------------------------------+-----------------------+--+

hadoop directory having one .txt file in test 5 directory

 bash$    hadoop fs -ls /user/yashu/test5/
    Found 1 items
    -rw-r--r--   3 hdfs hdfs         12 2018-03-23 03:42 /user/yashu/test5/test.txt

Hive table data

select * from test_tmp;
+--------------+----------------+--+
| test_tmp.id  | test_tmp.name  |
+--------------+----------------+--+
| 1            | bar            |
| 2            | foo            |
+--------------+----------------+--+

once i drop the table in hive then the directory test5 also dropped from hdfs

hive> drop table test_tmp;
bash$ hadoop fs -ls /user/yashu/test5/
ls: `/user/yashu/test5/': No such file or directory

So once we delete the internal table in hive even the hive table is not on default location also drops the directory(location) that the table is pointing to.