在Hive中使用OPEN CSV SERDE解析数组及列分割处理问题

Hey there! Let's figure out how to get your data parsed correctly with the array split you need.

First, let's break down what you're trying to do: you have pipe-separated data, where the 4th column looks like 1,A (wrapped in quotes in raw data), and you want to turn that into an array while stripping the quotes. The catch is that OpenCSV Serde doesn't natively support the COLLECTION ITEMS TERMINATED BY parameter that Hive's default delimited Serde uses for arrays. But we can work around this easily with a two-step approach:

Step 1: Use OpenCSV Serde to handle pipes and quotes #

First, create your external table using OpenCSV Serde to correctly split the pipe-separated columns and strip the quotes. We'll define the 4th column as a string first (since we'll convert it to an array later):

CREATE EXTERNAL TABLE job_data (
    col1 INT,
    col2 INT,
    job_code STRING,
    raw_col4 STRING,
    ref_code STRING,
    identifier STRING,
    col7 INT
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
    "separatorChar" = "|",
    "quoteChar" = "\"",
    "escapeChar" = "\\"
)
LOCATION '/path/to/your/hdfs/data';

This setup will automatically strip the quotes from columns like "AAAA" and give you the clean string 1,A in raw_col4 (even if there's leading whitespace like in your sample data).

Step 2: Convert the string to an array with split() #

Now, use Hive's built-in split() function to turn the comma-separated string into an array. If there's leading/trailing whitespace in the column (like 1,A), add trim() to clean it up first:

SELECT 
    col1,
    col2,
    job_code,
    split(trim(raw_col4), ',') AS col4_array,
    ref_code,
    identifier,
    col7
FROM job_data;

This will give you an array like ["1", "A"] for the 4th column, exactly what you need.

Why this works #

OpenCSV Serde is great for handling quoted values and custom column separators, but it's designed to treat each column as a single value. Hive's default delimited Serde supports collection splitting, but it doesn't handle quoted values as cleanly. By combining both tools—using OpenCSV to parse the raw data into clean strings, then using split() to create the array—you get the best of both worlds.

If you want this array to be part of the table definition permanently, you can create a view based on the query above:

CREATE VIEW job_data_with_array AS
SELECT 
    col1,
    col2,
    job_code,
    split(trim(raw_col4), ',') AS col4_array,
    ref_code,
    identifier,
    col7
FROM job_data;

Then you can query the view directly whenever you need the array column.

内容的提问来源于stack exchange，提问作者Surya Joseph