Skip to content
Tutorials Center
My learning

Topics

Lesson 23 of 36 ยท File Formats and Compression

Text, SequenceFile, RCFile and Avro

Compare Hive's row-oriented and early columnar formats: TEXTFILE with delimiters, SequenceFile, RCFile, Avro and JSON, and learn when each one is still the right choice.

A Hive table is a schema stored in the metastore plus a directory of files. The file format decides how rows are laid out inside those files, and it affects almost everything: how much disk and network a query uses, whether it can skip data, and whether the table can be updated.

This lesson covers the older and simpler formats you will meet in real clusters. The next lesson covers ORC and Parquet, the formats you should use for most analytics.

Choosing a format

You choose a format when creating a table with STORED AS:

SQL
CREATE TABLE events_text (
  user_id BIGINT,
  action  STRING,
  ts      TIMESTAMP
)
ROW FORMAT DELIMITED
  FIELDS TERMINATED BY ','
  LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

STORED AS picks the input and output format (how files are split and read) and often a default SerDe (how bytes become columns). The available shortcuts include TEXTFILE, SEQUENCEFILE, RCFILE, ORC, PARQUET, AVRO and, in Hive 4, JSONFILE. DESCRIBE FORMATTED events_text shows what you got: the InputFormat, OutputFormat and SerDe Library lines.

flowchart LR
    F["Files in HDFS"] --> IF["InputFormat: splits files into records"]
    IF --> SD["SerDe: bytes to columns"]
    SD --> Q["Query engine"]

A format is really two cooperating pieces: an input/output format and a SerDe.

TEXTFILE

TEXTFILE is the default. Each line is a row, columns are separated by a delimiter. Without a ROW FORMAT clause, Hive uses the control character \001 (Ctrl-A) between fields, \002 between collection items and \003 between map keys and values, because those bytes almost never appear in real text.

SQL
CREATE TABLE people (
  name   STRING,
  langs  ARRAY<STRING>,
  scores MAP<STRING,INT>
)
ROW FORMAT DELIMITED
  FIELDS TERMINATED BY '|'
  COLLECTION ITEMS TERMINATED BY ','
  MAP KEYS TERMINATED BY ':'
STORED AS TEXTFILE;
-- a file line:  Ana|java,python|math:90,art:75

Strengths: human-readable, trivial to produce, easy to debug, splittable when uncompressed. Weaknesses: large (numbers are stored as text), no type safety (a bad value becomes NULL), no column pruning (every column is parsed), and compression with gzip makes files non-splittable. Use it for landing raw data and for small lookup files, and convert to a binary format for repeated queries.

What the parser does

This stand-in (plain SQL in SQLite, not Hive) parses one delimited line the way LazySimpleSerDe does, cutting at the first delimiter and keeping the rest:

SQL Splitting a delimited line

Press Run (or Ctrl + Enter) to see the result here.

Every query over a text table repeats this work for every row and every column, one more reason to move hot data to a binary format.

Messy files

Bad input rarely makes a query fail. Missing fields become NULL and unparseable numbers become NULL. Two table properties help with typical CSV files:

SQL
CREATE TABLE sales_csv (id STRING, amount STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE
TBLPROPERTIES ('skip.header.line.count' = '1');

For quoted CSV (fields containing commas) use OpenCSVSerde, which reads every column as STRING, and cast afterwards. See compression codecs and SerDes.

SequenceFile

A SequenceFile is Hadoop's own binary container of key-value pairs. Hive stores each row as a value. It is splittable, supports record- or block-level compression, and loads faster than text because it avoids line scanning. It is also row-oriented and not very efficient, so it is mostly found in older pipelines and as a transport format between MapReduce jobs.

SQL
CREATE TABLE logs_seq (line STRING) STORED AS SEQUENCEFILE;

RCFile

RCFile (Record Columnar File) was Hive's first columnar format. It splits a table into row groups, and inside each group stores the values column by column. A query that reads two of fifty columns needs to decompress only those two columns of each row group. It was replaced by ORC and Parquet, which add rich metadata, better compression and predicate pushdown, so you will mostly meet RCFile in legacy tables. ALTER TABLE ... SET FILEFORMAT or a CREATE TABLE ... AS SELECT into ORC is the usual migration.

Avro and JSON

Avro is a row-oriented binary format that carries its schema inside each file, which makes it popular for data that arrives from Kafka or other streaming systems and for pipelines where the schema evolves (fields added with defaults). In Hive the schema can be given inline or from a file:

SQL
CREATE TABLE users_avro
STORED AS AVRO
TBLPROPERTIES ('avro.schema.url' = 'hdfs:///schemas/user.avsc');

JSON tables store one JSON object per line. Before Hive 4 you used the JSON SerDe explicitly; Hive 4 added STORED AS JSONFILE:

SQL
CREATE TABLE api_log (id BIGINT, path STRING, status INT)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS TEXTFILE;

JSON is flexible but slow and large, so land raw JSON in one table and convert it into ORC for analysis.

Row versus column layout

The formats above are either row-oriented (TEXTFILE, SequenceFile, Avro) or columnar (RCFile, and later ORC and Parquet). Row layout is best when you read whole records; columnar layout wins for analytics that touch a few columns of many rows, and compresses far better because similar values sit together.

JS Row versus column: bytes touched

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Summary table

Format Layout Splittable Schema in file Typical use
TEXTFILE rows, text yes (until gzipped) no raw landing, small files
SequenceFile rows, binary yes no legacy pipelines
RCFile columnar yes no legacy, migrate away
Avro rows, binary yes yes streaming, evolving schemas
JSON rows, text yes no (per record) API logs, raw landing
ORC / Parquet columnar yes yes analytics (next lesson)

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which delimiter does Hive use between fields in a TEXTFILE table when you give no ROW FORMAT?

    Hive defaults to the rarely used Ctrl-A byte so that ordinary text does not clash with the delimiter.
  2. 2. Why is a gzip-compressed text file a poor choice for large tables?

    Parallelism comes from splitting files, and gzip streams can only be decompressed from the start.
  3. 3. What is a main advantage of Avro?

    Each Avro file embeds its schema, so readers and writers can evolve independently.
  4. 4. Which format was Hive's first columnar format, now mostly replaced?

    RCFile stored values column by column inside row groups; ORC and Parquet improved on it.

Summary

  • STORED AS selects the file format; DESCRIBE FORMATTED shows the input format, output format and SerDe.
  • TEXTFILE is simple and readable but large and slow; keep it for raw landing zones.
  • SequenceFile and RCFile are legacy; Avro suits streaming data and changing schemas.
  • Columnar layout reads only the columns a query needs.

Next: ORC and Parquet