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