Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

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

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

New 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?

1 REPLY 1

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

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.

Don't have an account?
Coming from Hortonworks? Activate your account here