Skip first line of csv while loading in hive table

32
votes

Hello Friends,

I created table in hive with help of following command - 

CREATE TABLE db.test 
  ( 
     fname STRING, 
     lname STRING, 
     age   STRING, 
     mob   BIGINT 
  ) row format delimited fields terminated BY '\t' stored AS textfile;

Now to load data in table from file, I am using following command -

load data local inpath '/home/cluster/TestHive.csv' into table db.test;

Problem is, all the rows are getting inserted, and I don't want first row because it contains only column names.

Please suggest me a way to skip first line.

Thanks in advance.

3
hadoophivehiveql
Looks like a duplicate stackoverflow.com/questions/15751999/… - Rohit Menon
possible duplicate of Hive External table-CSV File- Header row - Marek Grzenkowicz
Does this answer your question? How to skip CSV header in Hive External Table? - Jacek Laskowski

3 Answers

52
votes

To get this you can use hive's property which is TBLPROPERTIES ("skip.header.line.count"="1")
you can also refer example -

CREATE TABLE temp 
  ( 
     name STRING, 
     id   INT 
  ) 
row format delimited fields terminated BY '\t' lines terminated BY '\n' 
tblproperties("skip.header.line.count"="1");
6
votes

Just for those who have already created the table with the header. Here is the alter command for the same.

ALTER TABLE tablename SET TBLPROPERTIES ("skip.header.line.count"="1");

1
votes

Just don't mix double quotes and single quotes in the same CLI command:

CREATE TABLE db.test 
  ( 
     fname STRING, 
     lname STRING, 
     age   STRING, 
     mob   BIGINT 
  ) row format delimited fields terminated BY '\t' tblproperties('skip.header.line.count'='1') stored AS textfile;

otherwise hive load NULL values.