Hive加载JSON数据时的Schema异常及字段错位问题排查

Let's break down what's going wrong here and fix it step by step!

What's Causing the Issues? #

Your two problems stem from using the wrong row format for your JSON data:

• Field misalignment: You defined the table with row format delimited fields terminated by ',', but your source data is full JSON objects—not comma-separated plain text. Hive splits the entire JSON string by commas, so chunks like "reviewerID": "A10000012B7CGYKOMPQ4L" get treated as the reviewerID field value, then the next comma-separated chunk ("asin": "000100039X") becomes the asin field, and so on. This completely breaks the field mapping, shoving helpful data into reviewText.
• reviewerID showing full key-value pair: Again, because Hive splits on commas, the first comma-free segment is the entire "reviewerID": "A10000012B7CGYKOMPQ4L" string—there's no logic to extract just the value when using comma-delimited formatting.

Correct Hive Schema for Your JSON Data #

To properly parse JSON, you need to use Hive's JSON SerDe (Serializer/Deserializer), which natively understands JSON key-value structures. Here's the correct table creation statement tailored to your data:

hc.sql("""
CREATE TABLE books (
  reviewerID string,
  asin string,
  reviewerName string,
  helpful array<int>,
  reviewText string,
  overall double,  -- Note: Your data uses 5.0 (float), int would truncate the decimal
  summary string,
  unixReviewTime bigint,  -- Timestamps are integers, bigint is better for this type
  reviewTime string
)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS TEXTFILE;
""")

Key Schema Adjustments: #

• Changed overall from int to double: Your source data has a floating-point value (5.0), so using int would lose precision.
• Swapped unixReviewTime from string to bigint: Timestamps are numeric values, making this type easier for future time-related operations.
• Replaced comma-delimited formatting with JsonSerDe to let Hive correctly parse the JSON structure.

Verify the Fix #

After loading your JSON data into this table, run your query again:

hc.sql("SELECT * FROM books").show()

You’ll see reviewerID displays just A10000012B7CGYKOMPQ4L, helpful correctly shows [0,0], and all fields align as expected.

内容的提问来源于stack exchange，提问作者Parv Bali