Is there any way to get the column name along with the output while execute any query in Hive?
HadoopHiveRdbmsHadoop Problem Overview
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?
Hadoop Solutions
Solution 1 - Hadoop
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
Solution 2 - Hadoop
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
Solution 3 - Hadoop
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.
Solution 4 - Hadoop
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.
Solution 5 - Hadoop
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
Solution 6 - Hadoop
Set this property before executing your query :
hive> set hive.cli.print.header=true;
Solution 7 - Hadoop
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