在Hive中加载带有引号字段中逗号的CSV文件

Question

我正在尝试像这样将CSV文件加载到Hive表中：

CREATE TABLE mytable
(
num1 INT,
text1 STRING,
num2 INT,
text2 STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ",";

LOAD DATA LOCAL INPATH '/data.csv'
OVERWRITE INTO TABLE mytable;    

csv文件使用逗号（,）作为分隔符，格式如下：

1, "some text, with comma in it", 123, "more text"

由于第一个字符串中有“，”，因此这将返回损坏的数据。是否有一种方法可以设置文本分隔符或使Hive忽略字符串中的“，”？

我无法更改CSV的分隔符，因为它是从外部来源获取的。

- Martijn Lenderink

Answer 1

如果您可以重新创建或解析输入数据，您可以为 CREATE TABLE 指定转义字符：

ROW FORMAT DELIMITED FIELDS TERMINATED BY "," ESCAPED BY '\\';

会将这一行视为4个字段

1,some text\, with comma in it,123,more text

- libjack

---

这个处理嵌入逗号的问题，但不处理CSV数据中可能存在的换行符问题。不知道是否也可以对换行符进行转义？在 https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-CreateTable 规范中好像没有关于转义换行符的说明。 - Ken Williams

非常感谢，这对我很有效。为了重新创建CSV文件，我使用了Python CSV写入器，并使用以下方言： csv.register_dialect('for_hive', escapechar='\', quoting=csv.QUOTE_NONE) - John Prawyn

Answer 2

问题在于Hive无法处理带引号的文本。你可以通过预处理数据来更改字段之间的分隔符（例如使用Hadoop流作业），或者尝试使用自定义的CSV SerDe，它使用OpenCSV解析文件。

- Lorand Bendig

---

sed -i 's/"//g' your_file_name 可以通过删除带引号的文本来进行原地预处理。但是，您需要确保没有意外删除其他有意引用（“）字符。 - ekta

Answer 3

截至Hive 0.14版本，CSV SerDe已成为Hive安装的标准组件。

行格式SerDe为：'org.apache.hadoop.hive.serde2.OpenCSVSerde'。

（详见：https://cwiki.apache.org/confluence/display/Hive/CSV+Serde）

- wrschneider

---

如果你的HIVE是最新的，那么这就是最好的答案 :) - bartektartanus

这也帮助了我！ - Kulasangar

当您使用OpenCSVSerde时，是否有一种方法可以指定Null的定义方式？使用“ROW FORMAT DELIMITED”，我可以添加选项“NULL DEFINED AS ' '”来识别数据中的null值。 - JeffR

@wrschneider，我在哪里可以下载这个serde？ - Aditya

@wrschneider 感谢您的回复。我现在找到了它。但是，这不支持列中的多行值。例如，多行地址。唯一的解决方案似乎是使用Scala或Python替换引号内的\n。这里有什么建议吗？ - Aditya

Answer 4

在 FIELDS TERMINATED BY '\;' 中添加反斜杠

例如：

CREATE  TABLE demo_table_1_csv
COMMENT 'my_csv_table 1'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\;'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE
LOCATION 'your_hdfs_path'
AS 
select a.tran_uuid,a.cust_id,a.risk_flag,a.lookback_start_date,a.lookback_end_date,b.scn_name,b.alerted_risk_category,
CASE WHEN (b.activity_id is not null ) THEN 1 ELSE 0 END as Alert_Flag 
FROM scn1_rcc1_agg as a LEFT OUTER JOIN scenario_activity_alert as b ON a.tran_uuid = b.activity_id;

我已经测试过了，它可以正常工作。

- Mantej Singh

---

它能正常工作，因为 '\;' 和 ';' 是一样的。不需要转义分号，但也没有必要。 - Nas Banov

Answer 5

将分隔符保持在单引号中，它就可以工作。

ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

这将会奏效

- suyash

Answer 6

ORG.APACHE.HADOOP.HIVE.SERDE2.OPENCSVSERDE Serde 对我很有用。我的分隔符是 '|'，其中一列被双引号包含。

查询：

CREATE EXTERNAL TABLE EMAIL(MESSAGE_ID STRING, TEXT STRING, TO_ADDRS STRING, FROM_ADDRS STRING, SUBJECT STRING, DATE STRING)
ROW FORMAT SERDE 'ORG.APACHE.HADOOP.HIVE.SERDE2.OPENCSVSERDE'
WITH SERDEPROPERTIES (
     "SEPARATORCHAR" = "|",
     "QUOTECHAR"     = "\"",
     "ESCAPECHAR"    = "\""
)    
STORED AS TEXTFILE location '/user/abc/csv_folder';

- vivesh saladi

---

它创建一个包含模式中所有字符串的表。 - Tokci