Just get column names from hive table

Question

I know that you can get column names from a table via the following trick in hive:

hive> set hive.cli.print.header=true;
hive> select * from tablename;

Is it also possible to just get the column names from the table?

I dislike having to change a setting for something I only need once.

My current solution is the following:

hive> set hive.cli.print.header=true;
hive> select * from tablename;
hive> set hive.cli.print.header=false;

This seems too verbose and against the DRY-principle.

Do you mean you need to get the column names along with the select * output ? — K S Nidhin, Oct 03 '14 at 15:19
doh! didnt find that one in the docs. describe table does it. — cantdutchthis, Oct 03 '14 at 16:44
Possible duplicate of [Hive - get column names](https://stackoverflow.com/questions/19038624/hive-get-column-names) — Ani Menon, Nov 12 '17 at 06:17

JJFord3 · Accepted Answer · 2014-10-09T00:58:16.527

90

If you simply want to see the column names this one line should provide it without changing any settings:

describe database.tablename;

However, if that doesn't work for your version of hive this code will provide it, but your default database will now be the database you are using:

use database;
describe tablename;

edited Oct 09 '14 at 00:58

answered Oct 08 '14 at 14:58

JJFord3

1,976
1
25
40

I got one question: will describe scan all partitions, causing HiveMetastore OOM? I encountered OOM when executing describe tablename in my case. – Gary Gauh May 02 '17 at 09:02
4

Thanks!, but this also gives extra info like about partition etc, how to filter only for column names any idea? – user3868051 Jun 28 '18 at 19:29
1

How I can add a value eg. '%INVENTORY%' to the code above to check if there is any column which contains Inventory? – Kshitij Manvelikar Jun 29 '20 at 09:06
Just follow the below answer, which is the more correct answer. `show columns in $table` – Indrajeet Gour Apr 06 '21 at 15:08
How do we do select statement to get all attributes as column in hive instead of using describe statement – Farid Arshad Apr 09 '21 at 07:32

score 71 · Answer 2 · edited May 23 '17 at 12:02

71

you could also do show columns in $table or see Hive, how do I retrieve all the database's tables columns for access to hive metadata

edited May 23 '17 at 12:02

Community

1
1

answered Oct 15 '15 at 17:07

Angelo Di Donato

1,093
9
11

12

This should be the accepted answer to the question "is it possible to **just** get the column names", as `describe` provides extra information. – Matthew Moisen Sep 20 '16 at 22:30
This answer is more appropriate. Because it doesn't return extra information. For example, if your table has partitions, DESCRIBE command will return some extra rows, including empty ones. – Pastor Sep 10 '19 at 17:45
This answer requires an edit since Hive 3.0! Now ``show columns in $table`` orders the column names, possibly braking other code when the order is important. – runr Dec 08 '20 at 12:41

score 1 · Answer 3 · edited Feb 14 '22 at 12:34

1

The solution is

show columns in table_name;

This is simpler than use

describe tablename;

Thanks a lot.

edited Feb 14 '22 at 12:34

Amira Bedhiafi

8,088
6
24
60

answered May 21 '21 at 02:57

Luis Fernando Ramos Castillo

29
3

score 0 · Answer 4 · answered Sep 06 '16 at 10:44

use desc tablename from Hive CLI or beeline to get all the column names. If you want the column names in a file then run the below command from the shell.

$ hive -e 'desc dbname.tablename;' > ~/columnnames.txt

where dbname is the name of the Hive database where your table is residing You can find the file columnnames.txt in your root directory.

$cd ~
$ls

Mahesh Kumar · Answer 5 · 2021-01-03T18:39:39.507

-2

Best way to do this is setting the below property:

set hive.cli.print.header=true;
set hive.resultset.use.unique.column.names=false;

edited Jan 03 '21 at 18:39

answered Jan 03 '21 at 12:34

Mahesh Kumar

49
7

Just get column names from hive table

5 Answers5

Linked