Skip first line of csv while loading in hive table

Question

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.

Looks like a duplicate http://stackoverflow.com/questions/15751999/hive-external-table-skip-first-row/15753145#15753145 — Rohit Menon, Dec 28 '13 at 10:51
possible duplicate of [Hive External table-CSV File- Header row](http://stackoverflow.com/questions/16457267/hive-external-table-csv-file-header-row) — Marek Grzenkowicz, Aug 13 '14 at 09:43
Does this answer your question? [How to skip CSV header in Hive External Table?](https://stackoverflow.com/questions/15751999/how-to-skip-csv-header-in-hive-external-table) — Jacek Laskowski, Jun 29 '21 at 08:44

score 56 · Answer 1 · edited Jun 16 '15 at 16:42

56

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");

edited Jun 16 '15 at 16:42

Jim Counts

12,535
9
45
63

answered Dec 12 '14 at 07:02

Bector

1,324
19
35

1

Working on Hive, but not on Impala. – Ameba Spugnosa Dec 27 '16 at 10:52
1

have you created table in Hive and accessing from Impala? – Bector Dec 29 '16 at 06:46
No, I used the `CREATE TABLE` statement with that `tblproperty` directly on Impala shell. I did not try the statement in Hive and then access the same table from Impala. – Ameba Spugnosa Dec 29 '16 at 08:59

score 10 · Answer 2 · answered May 18 '18 at 14:59

10

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");

answered May 18 '18 at 14:59

Sunil K-Standard Chartered

362
2
10

score 3 · Answer 3 · answered Oct 12 '19 at 03:31

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.

Skip first line of csv while loading in hive table

3 Answers3

Linked