I have created a table in hive, I would like to know which directory my table is created in?

Question

I have created a table in hive, I would like to know which directory my table is created in? I would like to know the path...

score 104 · Answer 1 · answered Nov 01 '12 at 22:43

104

DESCRIBE FORMATTED my_table;

or

DESCRIBE FORMATTED my_table PARTITION (my_column='my_value');

answered Nov 01 '12 at 22:43

Joe K

18,204
2
36
58

This answer is excellent... Thank you very much Joe... I can find every thing here about my table. – Muneer Basha Syed Nov 02 '12 at 19:05
3

This works very fine.. Any idea how we can see all partitions info in one command? – minhas23 Sep 10 '15 at 07:08
I have a maprfs system. I am trying to find the location of the file for which hive view is created. I am describing the hive view. but i am not seeing the location option there. Can u help! – semicolon Mar 11 '22 at 10:07

score 26 · Answer 2 · edited Apr 30 '23 at 03:50

26

There are three ways to describe a table in Hive.

To see table primary info of Hive table, use describe table_name; command
To see more detailed information about the table, use describe extended table_name; command
To see code in a clean manner use describe formatted table_name; command to see all information. also describe all details in a clean manner.

edited Apr 30 '23 at 03:50

sideshowbarker

81,827
26
193
197

answered May 11 '15 at 13:08

Venu A Positive

2,992
2
28
31

score 15 · Answer 3 · answered Apr 16 '15 at 17:42

15

You can use below commands for the same.

show create table <table>;
desc formatted <table>;
describe formatted <table>;

answered Apr 16 '15 at 17:42

InfamousCoconut

784
8
19

score 12 · Answer 4 · edited Nov 12 '18 at 09:10

12

DESCRIBE FORMATTED <tablename>

or

DESCRIBE EXTENDED <tablename>

I prefer formatted because it is more human readable format

edited Nov 12 '18 at 09:10

Suraj Rao

29,388
11
94
103

answered Apr 13 '18 at 21:23

Nick

121
1
4

score 8 · Answer 5 · edited Jul 04 '20 at 15:06

8

To see both of the structure and location (directory) of an any (internal or external)table, we can use table's create statment-

show create table table_name;

edited Jul 04 '20 at 15:06

GihanDB

591
2
6
23

answered Mar 17 '17 at 06:52

Snigh

461
5
10

pensz · Answer 6 · 2012-11-02T00:29:45.673

5

in hive 0.1 you can use SHOW CREATE TABLE to find the path where hive store data.

in other versions, there is no good way to do this.

upadted:

thanks Joe K

use DESCRIBE FORMATTED <table> to show table information.

ps: database.tablename is not supported here.

edited Nov 02 '12 at 00:29

answered Nov 01 '12 at 15:42

pensz

1,871
1
13
18

What can I do to see that info for a table in the non default database. I can't provide the database in that command? – ludwigm Jul 11 '13 at 21:48
confirmative.DESCRIBE FORMATTED does work while SHOW CREATE TABLE does not work
– runzhi xiao Aug 30 '18 at 06:05

score 3 · Answer 7 · answered Nov 09 '12 at 08:32

3

Further to pensz answer you can get more info using:

DESCRIBE EXTENDED my_table;

or

DESCRIBE EXTENDED my_table PARTITION (my_column='my_value');

answered Nov 09 '12 at 08:32

Samuel Kerrien

6,965
2
29
32

score 2 · Answer 8 · edited Nov 18 '14 at 06:52

2

All HIVE managed tables are stored in the below HDFS location.

hadoop fs -ls /user/hive/warehouse/databasename.db/tablename

edited Nov 18 '14 at 06:52

AliciaBytes

7,300
6
36
47

answered Nov 18 '14 at 06:26

Clement

37
1

4

That looks very specific upon the installation/configuration, no ? – Brian Agnew Apr 20 '16 at 16:51
this is exactly the question – Thomas Decaux Apr 09 '18 at 16:07

score 1 · Answer 9 · answered Jul 18 '13 at 06:01

1

If you use Hue, you can browse the table in the Metastore App and then click on 'View file location': that will open the HDFS File Browser in its directory.

answered Jul 18 '13 at 06:01

Romain

7,022
3
30
30

score 0 · Answer 10 · answered Apr 16 '15 at 17:09

0

in the 'default' directory if you have not specifically mentioned your location.

you can use describe and describe extended to know about the table structure.

answered Apr 16 '15 at 17:09

natarajan k

406
9
24

I have created a table in hive, I would like to know which directory my table is created in?

10 Answers10

Linked