Hive load CSV with commas in quoted fields

Question

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

User · Answer

As of Hive 0 14  the CSV SerDe is a standard part of the Hive install  ROW FORMAT SERDE  org apache hadoop hive serde2 OpenCSVSerde    See  https   cwiki apache org confluence display Hive CSV Serde

User · Answer

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

User · Answer

If you can re-create or parse your input data  you can specify an escape character for the CREATE TABLE   ROW FORMAT DELIMITED FIELDS TERMINATED BY     ESCAPED BY         Will accept this line as 4 fields  1 some text   with comma in it 123 more text

User · Answer

ORG APACHE HADOOP HIVE SERDE2 OPENCSVSERDE Serde worked for me  My delimiter was     and one of the columns is enclosed in double quotes   Query   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

User · Answer

keep the delimiter in single quotes it will work   ROW FORMAT DELIMITED  FIELDS TERMINATED BY     LINES TERMINATED BY   n     This will work

User · Answer

Add a backward slash in FIELDS TERMINATED BY       For Example   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    I have tested it  and it worked

[hadoop] Hive load CSV with commas in quoted fields

Examples related to hadoop

Examples related to hbase

Examples related to hive

Examples related to hdfs

Examples related to delimiter