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
# hadoop fs -cat /apps/hive/warehouse/textpipe/000000_0
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
If it helps to solves your query, accept the answer. It might help others.