如何为嵌套CSV数据创建仅含id、sal、name三列的Hive表

Got it, let's get this Hive table set up for you. You want a table with just id, sal, and name columns, even though one of your CSV rows has an extra trailing value. Here's the straightforward solution:

1. Create the Core Table #

First, define the table with exactly the three columns you need, configured to parse your comma-separated data:

CREATE TABLE employee (
    id INT,
    sal INT,
    name STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

Breakdown of the statement: #

• id INT and sal INT: Match the numeric values in your CSV
• name STRING: Handles the text-based name values
• ROW FORMAT DELIMITED FIELDS TERMINATED BY ',': Tells Hive to split each row using commas, which aligns with your CSV structure
• STORED AS TEXTFILE: Standard storage format for plain-text CSV data in Hive

2. Handle the Extra Field in the Final Row #

No extra configuration needed here! Hive automatically ignores any trailing fields that don't map to a defined column in the table. When you load your data, the extra daaaas value in the third row will be discarded, leaving only the first three values (3, 3000, dasasa) mapped to id, sal, and name.

3. Load Your CSV Data #

Assuming your CSV file is stored locally on your system, use this command to load it into the table (replace the file path with your actual path):

LOAD DATA LOCAL INPATH '/home/your-user/data/employee_data.csv' INTO TABLE employee;

4. Verify the Results #

Run a quick query to confirm the data was loaded correctly:

SELECT * FROM employee;

You should see output like this:

1   1000    sdadada
2   2000    sadssaa
3   3000    dasasa

内容的提问来源于stack exchange，提问作者Sai Mammahi