Menu
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
519
For external tables Hive assumes that it does not manage the data. Managed or external tables can be identified using the DESCRIBE FORMATTED table_name command, which will display either MANAGED_TABLE or EXTERNAL_TABLE depending on table type.
In the Hive shell, get an extended description of the table. For example: DESCRIBE EXTENDED mydatabase. mytable; Scroll to the bottom of the command output to see the table type.
If you want to see the actual data storage location of hive table,you can use multiple ways . 1) hive> show create table <TableName>; It will provide you the table syntax long with where actual data located path . 2) describe extended table_name or describe formatted table_name .
An external table is a table for which Hive does not manage storage. If you delete an external table, only the definition in Hive is deleted. The data remains. An internal table is a table that Hive manages. If you delete an internal table, both the definition in Hive and the data are deleted.
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...!!!
135
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
