use below code to simulate my problem on your hive.
CREATE DATABASE IF NOT EXISTS tst;
CREATE TABLE IF NOT EXISTS tst.t_data (
id INT
,name STRING
,value BIGINT
,dt STRING
)
;
SET hive.exec.compress.output=false;
INSERT INTO TABLE tst.t_data
SELECT id, name, value, dt
FROM (
SELECT 1 AS id, "ABC" AS name, 300000 AS value, "2021-09-01" AS dt UNION ALL
SELECT 2 AS id, "DEF" AS name, 400000 AS value, "2021-09-01" AS dt UNION ALL
SELECT 3 AS id, "GHI" AS name, 500000 AS value, "2021-09-01" AS dt UNION ALL
SELECT 4 AS id, "JKL" AS name, 600000 AS value, "2021-09-02" AS dt UNION ALL
SELECT 5 AS id, "MNO" AS name, 700000 AS value, "2021-09-02" AS dt UNION ALL
SELECT 6 AS id, "PQR" AS name, 800000 AS value, "2021-09-03" AS dt
) a;
CREATE TABLE IF NOT EXISTS tst.t_serde (
id INT
,name STRING
,value BIGINT
)
PARTITIONED BY (
dt STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
"separatorChar" = ",",
"quoteChar" = "\""
)
STORED AS TEXTFILE
;
SET hive.exec.dynamic.partition.mode=nonstrict;
SET hive.exec.compress.output=false;
INSERT INTO TABLE tst.t_serde PARTITION (dt)
SELECT id, name, value, dt
FROM tst.t_data
;
-- If i do dfs cat, i am getting proper csv values with double quotes and commas.
hdfs dfs -cat 'hdfs://ns1/user/hive/warehouse/tst.db/t_serde/dt=*/*' > t_serde_1.csv;
cat t_serde_1.csv
/*
"1","ABC","300000"
"2","DEF","400000"
"3","GHI","500000"
"4","JKL","600000"
"5","MNO","700000"
"6","PQR","800000"
*/
-- Problem, If i do normal select * statement from same table, i am getting tab separated values and without quotes
hive -e "SET hive.mapred.mode=nonstrict; SET hive.cli.print.header=false; SELECT * FROM tst.t_serde;" > t_serde_2.csv &
cat t_serde_2.csv
/*
1 ABC 300000 2021-09-01
2 DEF 400000 2021-09-01
3 GHI 500000 2021-09-01
4 JKL 600000 2021-09-02
5 MNO 700000 2021-09-02
6 PQR 800000 2021-09-03
*/
Is there any way to achieve the output like t_serde_1.csv using select statement in hive-1.2.1 ?
