Hive with JSON and Avro data, in depth: SerDes, malformed records, Avro schema evolution and the path to columnar tables

How Hive reads a row

Every Hive table definition names two pieces of code. The InputFormat knows how to split files and find record boundaries: lines for text, sync markers for Avro container files. The SerDe (serializer and deserializer) turns one record into column values that match the table schema, and does the reverse when Hive writes.

Nothing validates files when they are copied into a table's location or loaded with LOAD DATA. The table is a lens. If the lens and the bytes disagree, you find out when a query runs, and what happens then depends on the SerDe: a null, an error for the whole query, or a value that lands in the wrong column. Choosing a format is largely choosing which of those behaviours you get.

JSON tables with the JsonSerDe

Hive ships a JSON SerDe in the HCatalog module. It expects one complete JSON object per line and matches top-level keys to column names; nested objects map to STRUCT or MAP columns and JSON arrays to ARRAY columns.

-- Older Hive releases may need: ADD JAR /path/to/hive-hcatalog-core.jar;
CREATE EXTERNAL TABLE landing.events_json (
  event_id   STRING,
  user_id    BIGINT,
  event_type STRING,
  ts         STRING,                 -- parse later; see below
  props      MAP<STRING,STRING>
)
PARTITIONED BY (dt STRING)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS TEXTFILE
LOCATION '/lake/landing/events_json';

ALTER TABLE landing.events_json ADD PARTITION (dt='2026-10-02');

Newer Hive releases also accept STORED AS JSONFILE as shorthand for the same SerDe (tracked as HIVE-19899); check that your distribution's version supports it before relying on it.

The matching rules explain most surprises. Keys missing from a record become NULL. Keys with no matching column are ignored, so a producer that renames userId to user_id produces a column of nulls rather than an error. Hive column names are case-insensitive and stored in lower case, so camelCase keys need checking against your SerDe's behaviour. Store timestamps as STRING in the landing table and convert explicitly, because producers rarely agree on a format, and a value the SerDe cannot parse into the declared type can fail the query.

When the JSON is dirty: the raw-string pattern

With the HCatalog SerDe, one malformed line, such as a truncated object written during a crash, can fail every query that touches its file. Third-party SerDes offer options to skip malformed records, but skipping silently hides data loss. A more robust design is to land each line as a single string and parse it in SQL, where you control what happens to bad rows.

CREATE EXTERNAL TABLE landing.events_raw (line STRING)
PARTITIONED BY (dt STRING)
STORED AS TEXTFILE
LOCATION '/lake/landing/events_json';      -- same files, different lens

-- Typed rows: lines whose required fields parse
INSERT OVERWRITE TABLE curated.events PARTITION (dt='2026-10-02')
SELECT get_json_object(line, '$.event_id'),
       CAST(get_json_object(line, '$.user_id') AS BIGINT),
       get_json_object(line, '$.event_type'),
       CAST(get_json_object(line, '$.ts') AS TIMESTAMP)
FROM landing.events_raw
WHERE dt = '2026-10-02'
  AND get_json_object(line, '$.event_id') IS NOT NULL
  AND CAST(get_json_object(line, '$.ts') AS TIMESTAMP) IS NOT NULL;

-- Quarantine: everything else, kept for inspection and replay
INSERT OVERWRITE TABLE quarantine.events PARTITION (dt='2026-10-02')
SELECT line FROM landing.events_raw
WHERE dt = '2026-10-02'
  AND (get_json_object(line, '$.event_id') IS NULL
       OR CAST(get_json_object(line, '$.ts') AS TIMESTAMP) IS NULL);

get_json_object returns NULL for invalid JSON or a missing path instead of failing, which is exactly what makes the split possible. When you need several fields, json_tuple in a LATERAL VIEW parses each line once instead of once per field. Alert on the quarantine count: a sudden jump almost always means a producer changed its output.

Avro tables: the schema travels with the data

An Avro container file starts with a header that contains the schema the producer wrote with, followed by blocks of binary records separated by sync markers. Records carry no field names, so they are compact, and the file can always be decoded because its writer schema is inside it. The Hive table supplies the reader schema: the shape the query wants. Avro's resolution rules map one onto the other for every file.

CREATE EXTERNAL TABLE landing.clicks
PARTITIONED BY (dt STRING)
STORED AS AVRO
LOCATION '/lake/landing/clicks'
TBLPROPERTIES ('avro.schema.url'='hdfs:///schemas/clicks/clicks-v2.avsc');

With avro.schema.url, the columns come from the schema file, so there is no column list in the DDL; avro.schema.literal embeds the schema JSON in the table properties instead. A URL is easier to version and review, but the file becomes a runtime dependency: if it is moved or unreadable, queries fail. You can also declare columns in ordinary DDL with STORED AS AVRO and let Hive derive the Avro schema.

Schema evolution: the rules that keep old files readable

Because every file keeps its writer schema, you can change the table's reader schema and still read files written years ago, as long as the change is one Avro can resolve:

• Add a field with a default. Old files lack it, so the reader fills in the default. Adding a field without a default breaks reads of every older file.
• Remove a field. Safe for reading old files: fields the reader does not ask for are skipped.
• Widen a number. int to long, float or double, and long to float or double are allowed promotions; narrowing is not.
• Rename. Not a rename to Avro: it is a removal plus an addition, unless the new field lists the old name in aliases.
• Change a type otherwise, for example string to int: not resolvable. Add a new field instead.

If a schema registry sits upstream, its compatibility mode is the same contract enforced at publish time, and the cheapest place to stop a breaking change.

Worked example: evolving a clickstream table

Version 1 of the click schema has click_id, user_id and url. Six months later the mobile team wants the device type. Version 2 adds it as an optional field with a default:

{
  "type": "record", "name": "Click", "namespace": "shop.events",
  "fields": [
    {"name": "click_id", "type": "string"},
    {"name": "user_id",  "type": "long"},
    {"name": "url",      "type": "string"},
    {"name": "device",   "type": ["null", "string"], "default": null}
  ]
}

-- publish clicks-v2.avsc next to v1, then point the table at it
ALTER TABLE landing.clicks
  SET TBLPROPERTIES ('avro.schema.url'='hdfs:///schemas/clicks/clicks-v2.avsc');

SELECT dt, count(*), count(device) FROM landing.clicks GROUP BY dt ORDER BY dt;
-- older dates: count(device) = 0 (default null); newer dates: populated

Note the order of the union: for a default of null, null must be the first branch. Upgrade readers before writers: point the table at v2 first, then let producers write v2 files. The reverse order still works for this change, but for others it can leave a window where new files contain a field the table cannot express. A tempting alternative, typing user_id as string in v2 because one producer sends UUIDs, is not a legal evolution; it needs a new field, user_key, and a migration of consumers.

Impala and the same tables

Impala does not run Hive's Java SerDes; it has its own native scanners. It can query Avro tables defined in Hive, and it resolves the table schema against each file's writer schema much as Hive does, but it has not traditionally been able to write Avro, so inserts into Avro tables happen in Hive or Spark. JSON is the larger gap: tables using the Hive JsonSerDe have historically been unreadable in Impala, and native JSON support depends on the Impala version, so check your release notes before promising it to users. In both cases, run REFRESH after new files or partitions land so Impala's catalog sees them. The cleanest answer is the next section: give Impala a Parquet table.

From landing format to serving format

JSON and Avro are row formats. A query that needs two columns still reads and decodes every field of every record, and JSON pays text parsing on top. Columnar formats store each column together with statistics, so queries read only the columns they need and skip blocks whose min and max values exclude the filter. For anything queried repeatedly, convert:

CREATE TABLE curated.clicks (click_id STRING, user_id BIGINT, url STRING, device STRING)
PARTITIONED BY (dt STRING)
STORED AS ORC
TBLPROPERTIES ('orc.compress'='ZLIB');   -- ZSTD needs a recent Hive/ORC

SET hive.exec.dynamic.partition.mode=nonstrict;
INSERT OVERWRITE TABLE curated.clicks PARTITION (dt)
SELECT click_id, user_id, url, device, dt
FROM landing.clicks
WHERE dt >= '2026-10-01';

Keep the landing data for a retention period so you can rebuild after a parsing bug. Run the conversion per partition, as a scheduled job, and size output files deliberately: streaming producers create many small Avro or JSON files, and copying them one-to-one into ORC preserves the problem described in Hive small files. ORC internals and Hive compression cover the columnar side.

Failure modes

• A column of nulls after a deploy. A JSON key was renamed or changed case. Compare the null rate per column per partition and alert on jumps.
• Every query fails on one partition. A truncated JSON line or a corrupt Avro block. Use the raw-string pattern for JSON; for Avro, find the file and quarantine it.
• Old partitions stop reading after a schema change. A field was added without a default, or a type was changed. Revert the schema URL, then fix the schema.
• Schema URL unreachable. The .avsc file was moved, or a cluster cannot reach that file system. Keep schemas in a stable, versioned location.
• Dropping an external table did not delete data, or a managed one did. Know which you created; Hive tables explains the difference.

Trade-offs

What to do next

1. List your JSON and Avro tables and mark each as landing or serving; anything queried daily that is still JSON or Avro is a conversion candidate.
2. For each JSON landing table, add a raw-string twin and a quarantine table, and alert on the quarantine count.
3. Store JSON timestamps as STRING in landing tables and convert in the curated insert.
4. Move Avro tables to avro.schema.url pointing at versioned schema files, and review schema changes like code.
5. Check every Avro schema change against the rules above: defaults on new fields, no type changes, aliases for renames.
6. Before exposing a table to Impala, confirm your version can read its format, or give Impala the Parquet copy.
7. Schedule per-partition conversion to ORC or Parquet with sensible file sizes, and keep landing data long enough to rebuild.