hive 字段中逗号怎么处理,Hive使用带引号的字段中的逗号加载CSV

I am trying to load a CSV file into a Hive table like so:

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;

The csv is delimited by an comma (,) and looks like this:

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

This will return corrupt data since there is a ',' in the first string.

Is there a way to set an text delimiter or make Hive ignore the ',' in strings?

I can't change the delimiter of the csv since it gets pulled from an external source.

解决方案

The problem is that Hive doesn't handle quoted texts. You either need to pre-process the data by changing the delimiter between the fields (e.g: with a Hadoop-streaming job) or you can also give a try to use a custom CSV SerDe which uses OpenCSV to parse the files.