Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Hive table need to be stored with a different delimiter after import

avatar
Contributor

I have a text file with values separated by ','. And I have to save the file as a text file in hive with delimiter '|'. How to apply this in query?

3 REPLIES 3

avatar
Expert Contributor

Create text table with comma(,) as field delimiter

create table textcomma(age int, name string)  row format delimited fields terminated by ',' stored as textfile; 
insert into textcomma values(1,'a'),(2,'b'),(3,'c'); 

Option 1 : CTAS text table with pipe(|) as field delimiter

create table textpipe row format delimited fields terminated by '|' stored as textfile as select * from textcomma; 

# hadoop fs -cat /apps/hive/warehouse/textcomma/000000_0

1,a
2,b
3,c 

# hadoop fs -cat /apps/hive/warehouse/textpipe/000000_0

1|a
2|b
3|c

Option 2 : Insert overwrite directory to write textfile with pipe(|) delimiter

INSERT OVERWRITE DIRECTORY '/tmp/text-pipe'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE
SELECT * FROM textcomma;

# hadoop fs -cat /tmp/text-pipe/000000_0

1|a
2|b
3|c

If it helps to solves your query, accept the answer. It might help others.

avatar
New Contributor

hive -e "use default;set hive.cli.print.header=true;select * from hive_table;" | sed 's/[\t]/|/g' >/tmp/hive_table.txt

avatar
New Contributor

I know this post was a long time ago, but does the script below add text qualifiers if a field contains one or more  '|'?

 

INSERT OVERWRITE DIRECTORY '/tmp/text-pipe'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE
SELECT * FROM textcomma;

Thanks!