How to find out if a Hive table is external or internal?

Question

I have multiple questions here. I am looking for any hive shell commands or queries to find the below details. Given a hive database name, how can I get the list of external tables in that database? Given a hive table name, how can I find out whether the table is external or internal? Thanks in advance

Accepted Answer

1) Given a hive database name, how can I get the list of external tables in that database ?

You can try this command:

```
SHOW TABLES [IN database_name] [identifier_with_wildcards];
```

It will give you all tables. As far as I know there is no direct command to know all the tables of type external/internal. For that you have use JDBC connection to connect to HiveMetastore and get the required info.

2) Given a hive table name, how can I find that whether the table is external or internal table ?

You can try any of this commands:

```
describe formatted table_name

describe extended table_name
```

It show all the detail info of a table. Along with :

```
Table Type:             EXTERNAL_TABLE           
Table Parameters:       EXTERNAL=TRUE
```

Hope it helps...!!!

How to find out if a Hive table is external or internal?

Solution

Problem