Is there any way to get the column name along with the output while execute any query in Hive?

72
votes

In Hive, when we do a query (like: select * from employee), we do not get any column names in the output (like name, age, salary that we would get in RDBMS SQL), we only get the values.

Is there any way to get the column names to be displayed along with the output when you execute any query?

7
hadoophiverdbms

7 Answers

146
votes

If we want to see the columns names of the table in HiveQl, the following hive conf property should be set to true.

hive> set hive.cli.print.header=true;

If you prefer to see the column names always then update the $HOME/.hiverc file with the above setting in the first line..

--Hive automatically looks for a file named .hiverc in your HOME directory and runs the commands it contains, if any

15
votes

To print header along with the output, the following hive conf property should be set to true before executing the query.

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

We can also use query like this, if we want to get result in file.

hive -e 'set hive.cli.print.header=true;select * from table_name;' > result.xls

Where table_name your table name

4
votes

All above answers already answer the question. But in case if someone wants this property to be ON permanently, then there is this property: hive.cli.print.header in hive-default.xml or hive-site.xml.

Its default value is false. Make its value as true and save. Done.

2
votes

Set this property before executing your query :

hive> set hive.cli.print.header=true;
2
votes

Most of the solutions are accurate.

setting the property hive.cli.print.header = true works.

But if you are using a cloudera , HDP or any other distributions, these will be reset. Hence update these value in the Hive configurations and restart the services.

This will be a permanent fix. hope this helps.

0
votes

Use set hive.cli.print.header=true;

hive> set hive.cli.print.header=true;      
hive> select * from tblemployee;
OK
id      name    gender  salary  departmentid
1       tomr    male    40000   1
2       cats    female  30000   2
3       john    male    50000   1
4       james   male    35000   3
5       sara    female  29000   2
6       bens    male    35000   1
7       saman   female  30000   NULL
8       russel  male    40000   2
9       valar   female  30000   1
10      todd    male    95000   NULL
Time taken: 9.892 seconds
0
votes
1)Permenant solution
change this property in hive-site.xml file under $HIVE_HOME/conf folder
    <property>
    <name>hive.cli.print.header</name>
    <value>true</value>
    <description>Whether to print the names of the columns in query output.
    </description>
    </property>
2)Temporary solution:
go to hive prompt execute this comman
   hive>set hive.cli.print.header=True