Listing only Hive internal tables

[#1]

Hello All,

I am just thinking if there is any way to list only Hive internal tables/External tables? Show tables lists all but if I would like to see only internal tables, the reason behind this is - In case of housekeeping activities, If I just list the internal tables and drop them then my task is simple, other wise I will to check if the table is external/internal if I need to do a complete clean up for both metadata and raw data.

Regards

Sree

[#2]

Hi Sree,

I don't think there is a way to list only internal tables using SHOW TABLES. You'll need to use DESCRIBE EXTENDED <TABLE> and look for the "tableType" property for each table.

(Alternatively, you could use different identifiers for internal and external table names, and use SHOW TABLES <pattern>, for example: SHOW TABLES 'internal_*' to list internal ones)

thanks,

Mohit

[#3]

Hello Mohit,
Thank you. I like the idea of having it in the name, which we are following
but not every one in the cluster follows, being we are one among multi
tenants

[#4]

Hello,

By using hive metastore you can get the list of hive external tables and internal tables with databases names and table data location path.

Please read below links you will find query to get list of tables .

Internal Tables --   

https://askdoubts.com/question/list-of-hive-internal-tables-from-metadata/

External tables ---- https://askdoubts.com/question/how-to-find-out-list-of-all-hive-external-tables-and-hdfs-paths-from-...