Hive- how to escape field delimiter in column value to upload sql queries

getschwifty — Tue, 21 Apr 2026 11:27:41 GMT

Hi,

Trying to upload logs into a Hive external table. Some records contain queries which have multiple columns in them, but commas are also the field delimiters. Other questions here recommend using org.apache.hadoop.hive.serde2.OpenCSVSerde. So I used:

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'

STORED AS TEXTFILE

LOCATION 'hdfs://hdfs/path/to/directory/external/2020Jun30'

TBLPROPERTIES ('skip.header.line.count'='1');

This didn't deal with the additional non-escaped commas in the log file though. So I tried:

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'

WITH SERDEPROPERTIES (

"separatorChar" = ",",

"quoteChar" = '"',

"escapeChar" = "\\"

)

STORED AS TEXTFILE

However, in Hue I get this error when using org.apache.hadoop.hive.serde2.OpenCSVSerde:

AnalysisException: Failed to load metadata for table: 'db.ext_table' CAUSED BY: TableLoadingException: Failed to load metadata for table: db.ext_table CAUSED BY: InvalidStorageDescriptorException: Impala does not support tables of this type. REASON: SerDe library 'org.apache.hadoop.hive.serde2.OpenCSVSerde' is not supported.

I can read from the table via beeline, but not via Hue. Is this something (a library?) that I need to add to Hue?

Or do I need to create the table in some other way?

How do I deal with sql query commas in a comma delimitered log file?

Re: Hive- how to escape field delimiter in column value to upload sql queries

getschwifty — Fri, 10 Jul 2020 04:04:27 GMT

So the answer is the

'org.apache.hadoop.hive.serde2.OpenCSVSerde' isn't supported in Impala, but it is in Hive.

question Hive- how to escape field delimiter in column value to upload sql queries in Support Questions

Hive- how to escape field delimiter in column value to upload sql queries

Re: Hive- how to escape field delimiter in column value to upload sql queries