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.

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

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

Thanks in advance

Answer 1

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...!!!