适配多数据类型的Hive表CSV Serde格式配置及乱序CSV处理

Hey there! Let's tackle this messy CSV to Hive table conversion. Your CSV has all sorts of edge cases—embedded commas in salary values, semicolon-separated values in some fields, empty entries, and mixed data types. The OpenCSVSerDe is your best bet here because it handles quoted fields, custom delimiters, and empty values way better than Hive's default CSV SerDe.

Key Configuration Details for OpenCSVSerDe #

Here are the critical parameters you need to set to parse your CSV correctly:

• separatorChar: Set to , since your top-level field delimiter is a comma.
• quoteChar: Configure this to " because your salary fields (like "$40,000-50,000") are wrapped in double quotes to escape internal commas.
• escapeChar: Optional but safe to set to \—it handles any escaped quotes inside fields if they exist in your full dataset.
• null.string: Set to empty string ("") to map empty CSV entries (like the ,, in your sample) to Hive NULL values.
• skip.header.line.count: If your CSV has a header row, add this property with a value of 1 to skip it during data loading.

Example Hive Table DDL #

Let's define a table that matches your sample data (adjust column names to your actual schema):

CREATE EXTERNAL TABLE IF NOT EXISTS user_survey (
    country STRING,
    age_range STRING,
    gender STRING,
    custom_field_1 STRING,
    custom_field_2 STRING,
    job_role STRING,
    unique_identifier STRING,
    salary_range STRING,
    industry STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
    "separatorChar" = ",",
    "quoteChar" = "\"",
    "escapeChar" = "\\",
    "null.string" = ""
)
STORED AS TEXTFILE
LOCATION '/path/to/your/csv/storage'; -- Replace with your HDFS/S3/local path

Important Usage Notes #

1. Semicolon-separated values: Fields like Mobile Developer;zzzz-vvvv will load as a single string. If you need to split them later, use Hive's split() function:

   SELECT split(job_role, ';') AS job_role_parts FROM user_survey;
   

2. Data type flexibility: All columns are defined as STRING here to accommodate range values, formatted salary strings, and mixed content. You can cast columns to numeric types later if needed (e.g., extract salary bounds using regex functions).
3. Validation: After creating the table, run a quick check to confirm parsing works:

   SELECT * FROM user_survey LIMIT 2;
   

   This should return your two sample rows with all fields correctly mapped, no broken values from embedded commas.

内容的提问来源于stack exchange，提问作者Akash Tyagi