Twitter情感分析遇JSON解析错误，求Cloudera Json SerDe排查方案

Hey there, sorry you’ve been stuck on this for three days—those JSON parsing errors can be such a headache, but let’s break down exactly what’s going on and how to fix it. That error message tells us Hive’s Json SerDe is hitting a line starting with 'O' that isn’t a valid JSON value (like {, [, a string, number, etc.). Here’s how to diagnose and resolve this step by step:

1. First, Validate Your Raw Flume-Collected Data #

The most likely culprit is malformed or non-JSON data sneaking into your HDFS files. Let’s check:

• Pull a sample of your data from HDFS using this command:

  hdfs dfs -cat /path/to/your/flume/output/*.log | head -20
  

• Look for lines that start with 'O'—chances are these are error messages (like OAuth authentication errors from the Twitter API) or extra metadata added by Flume, not valid tweet JSON. For example, a line like OAuth error: Invalid credentials would start with 'O' and break the SerDe immediately.

2. Fix Flume Configuration Issues #

Since you’re using Flume to collect Twitter data, let’s make sure your setup isn’t corrupting the JSON:

• Double-check your Twitter Source configuration: Ensure it’s set to output raw JSON tweets and not mixing in debug log messages. Some Twitter Source implementations might emit verbose logs alongside tweet data—disable any extra logging in your Flume agent config.
• Verify your HDFS Sink settings: Add hdfs.fileType=DataStream to your sink config to prevent Flume from writing extra metadata or formatting that could break the JSON structure. Also, avoid using interceptors that modify raw tweet content unless you’re 100% sure they preserve valid JSON.

3. Adjust Hive Json SerDe Settings #

If some malformed data is unavoidable, tweak your SerDe to handle it more gracefully:

• When creating your Hive table, add the ignore.malformed.json property to skip bad lines instead of failing entirely:

  CREATE TABLE twitter_sentiment (
    -- Add your column definitions here (e.g., text STRING, user STRING)
  )
  ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
  WITH SERDEPROPERTIES (
    "ignore.malformed.json" = "true"
  )
  STORED AS TEXTFILE;
  

• If you’re still having issues, try switching to Hive’s native JsonSerDe instead of the Cloudera one (compatibility can vary between distributions):

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

4. Pre-Clean Data With Hive #

If you need to isolate valid JSON rows first:

• Create a temporary text table to load all raw data:

  CREATE TABLE twitter_raw (line STRING) STORED AS TEXTFILE;
  LOAD DATA INPATH '/path/to/flume/data' INTO TABLE twitter_raw;
  

• Filter out valid JSON rows (those starting with { and ending with }) and insert into your main table:

  INSERT INTO TABLE twitter_sentiment
  SELECT get_json_object(line, '$.text') AS tweet_text,
         get_json_object(line, '$.user.screen_name') AS username
         -- Repeat for all columns you need
  FROM twitter_raw
  WHERE regexp_like(line, '^\\{.*\\}$');
  

Quick Debugging Tip #

Pinpoint the exact problematic lines with this query:

SELECT line FROM twitter_raw WHERE line LIKE 'O%' LIMIT 5;

This will show you those 'O'-starting rows directly, so you can fix their root cause (e.g., updating invalid Twitter API credentials if it’s an OAuth error).

内容的提问来源于stack exchange，提问作者sachin bhikule