Just Get Column Names from Hive Table

Just get column names from hive table

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;

Spark SQL - Get Column Names of a Hive Table in a String

Instead of show columns, Try below approach as it is faster than yours.

val colNameDF = spark.sql("select * from hive_table").limit(0)

Or

val colNameDF = spark.table("hive_table").limit(0)

val colNameStr = colNameDF.columns.mkString(", ")

Hive - getting the column names count of a table

create table mytable(i int,str string,dt date, ai array<int>,strct struct<k:int,j:int>);

---

select  count(*) 
from    (select transform ('') 
                using 'hive -e "desc mytable"' 
                as col_name,data_type,comment
        ) t
;

5

---

Some additional playing around:

create table mytable (id int,first_name string,last_name string);
insert into mytable values (1,'Dudu',null);

select size(array(*)) from mytable limit 1;

This is not bulletproof since not all combinations of columns types can be combined into an array.

It also requires that the table will contain at least 1 row.

---

Here is a more complex but also stronger solution (types versa), but also requires that the table will contain at least 1 row

select size(str_to_map(val)) from (select transform (struct(*)) using 'sed -r "s/.(.*)./\1/' as val from mytable) t;

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

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

---

Related Topics