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.

Excurvate

Do you mean you need to get the column names along with the select * output ? – Biblical

why not do a describe table ?? – Pelops

doh! didnt find that one in the docs. describe table does it. – Excurvate

Possible duplicate of Hive - get column names – Complacence

Answer 1

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;

Abrahamabrahams

I got one question: will describe scan all partitions, causing HiveMetastore OOM? I encountered OOM when executing describe tablename in my case. – Gond

Thanks!, but this also gives extra info like about partition etc, how to filter only for column names any idea? – Skantze

How I can add a value eg. '%INVENTORY%' to the code above to check if there is any column which contains Inventory? – Eggnog

Just follow the below answer, which is the more correct answer. show columns in $table – Knar

How do we do select statement to get all attributes as column in hive instead of using describe statement – Outstation

Answer 2

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

Springtime

This should be the accepted answer to the question "is it possible to just get the column names", as describe provides extra information. – Oleviaolfaction

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. – Cana

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. – Predictory

Answer 3

The solution is

show columns in table_name;

This is simpler than use

describe tablename;

Thanks a lot.

Gesticulate

Answer 4

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

Harilda

Answer 5

Best way to do this is setting the below property:

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

Rawden