Change field termination value in Hive

This blog post describes how to change the field termination value in Hive. Assume when you created the Hive table, it was created with below script. Look at the ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’ syntax, this means Hive assumes the data behind the table is separated by comma.

hive> CREATE TABLE IF NOT EXISTS stocks (
exch STRING,
symbol STRING,
ymd STRING,
price_open FLOAT,
price_high FLOAT,
price_low FLOAT,
price_close FLOAT,
volume INT,
price_adj_close FLOAT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
TBLPROPERTIES ('creator'='hirw', 'created_on' = '2015-02-16', 'description'='This table holds stocks data!!!');

Now let’s say the format of your data is changed, now your data is delimited by the pipe (|) symbol. Is there a way to change the table to use | as the delimiter ? 

Solution

Yes. There is. You can change the delimiter using the below alter table command.

hive > ALTER TABLE stocks    
set serde 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES ('field.delim' = '|');