Impala Text and CSV Support, in depth: delimited tables, the quoting gap, NULLs, headers, bad data, compressed text and converting to Parquet

How Impala reads a text table

When a query touches a text table, the planner lists the table's files and cuts each one into scan ranges, byte ranges that executors read in parallel. An uncompressed file can be split anywhere: a scanner that starts in the middle of a file skips forward to the first line terminator and starts parsing there, and a scanner reading the range before it reads past its end to finish the last line. Together they cover every line exactly once.

Each line is then split into fields on the field delimiter, honouring the escape character if one is declared. Fields are matched to columns by position: the first field goes to the first column, and so on. If a line has fewer fields than the table has columns, the missing columns are NULL. If it has more, the extra fields are ignored. Each field is then converted from text to the column's type. A STRING column takes the bytes as they are; an INT, DECIMAL or TIMESTAMP column parses them, and a value that does not parse becomes NULL with a warning.

There is no column pruning, no statistics in the file and no quote handling: every byte is read and scanned even if the query uses one column. Text is a fine landing format and a poor serving format.

Declaring the format: ROW FORMAT DELIMITED

A table created with no STORED AS clause is uncompressed text separated by ASCII 0x01, the Ctrl-A character that Hive also uses by default. Most real files use a comma, tab or pipe, which you declare in the ROW FORMAT DELIMITED clause:

-- A landing table over files that other systems drop into a directory.
CREATE EXTERNAL TABLE landing.orders_csv (
  order_id     BIGINT,
  customer     STRING,
  amount       DECIMAL(12,2),
  ordered_at   TIMESTAMP,
  status       STRING
)
ROW FORMAT DELIMITED
  FIELDS TERMINATED BY ','
  ESCAPED BY '\\'
  LINES TERMINATED BY '\n'
STORED AS TEXTFILE
LOCATION 's3a://lake/landing/orders/'
TBLPROPERTIES ('skip.header.line.count'='1',
               'serialization.null.format'='');

Three rules from the Impala CREATE TABLE reference matter here. First, FIELDS TERMINATED BY, ESCAPED BY and LINES TERMINATED BY each take a single character. A multi-character delimiter such as || cannot be declared; if your files use one, convert them or define the table in Hive with a SerDe that supports it and convert to Parquet there. Second, you can write the character as a literal, as an octal escape such as '\054' for a comma, or as an integer in quotes between -127 and 128, where negative values are subtracted from 256 (so '-2' means byte 254). Third, the escape character lets a delimiter appear inside a value: with ESCAPED BY '\\', the text Acme\, Inc is read as the single value Acme, Inc.

Use an external table for landing directories, so dropping it leaves the files alone. After new files arrive, run REFRESH so Impala's catalog sees them.

CSV is not delimited text: the quoting gap

Real CSV, as most tools write it, quotes fields that contain the delimiter, a quote or a newline. Impala's text scanner has no concept of quotes. Hive can read quoted CSV with org.apache.hadoop.hive.serde2.OpenCSVSerde, but Impala does not support that SerDe, and a table defined with it in Hive is not readable from Impala. So take a line like this:

order_id,customer,amount,ordered_at,status
1001,"Acme, Inc",250.00,2026-09-30 10:15:00,shipped

Impala splits the data line on every comma and maps fields by position. The result, for the table above, is:

The extra field, shipped, is silently dropped. Nothing fails: the query returns a row with plausible-looking garbage, and an aggregate over amount simply skips it. A quoted field containing a newline is worse: it splits one record into two lines.

There are three workable answers. The best is to change the producer: ask for a delimiter that cannot occur in the data (tab, pipe, or Ctrl-A) and no quoting, or for an escape character instead of quotes. If you cannot, read the files once in Hive or Spark with a real CSV parser and write Parquet, then query the Parquet from Impala. As a last resort, define every column as STRING, then repair known patterns in SQL; this only works when you know exactly which fields can contain the delimiter, and it does not survive embedded newlines.

Headers, NULLs and empty strings

From Impala 2.6, the table property skip.header.line.count tells the scanner to skip that many lines at the start of each file. It applies per file, so it suits a directory of exports that each carry a header. The documentation describes it for files on HDFS; on object storage, as in the example below, test it on your version first. Headers in the middle of concatenated files are read as data.

By default Impala reads the two-character string \N as NULL, the same convention Hive writes. Other producers write other things. Sqoop-style imports may write the literal word null; many CSV exporters write nothing at all between two delimiters. Set serialization.null.format in TBLPROPERTIES to the string your files use; with '' as in the DDL above, an empty field becomes NULL.

Be deliberate: an empty typed field is NULL either way, but with the default setting it also raises a warning on every row, while an empty STRING field stays an empty string that WHERE customer IS NULL will not find.

Bad data: conversion errors, ABORT_ON_ERROR and MAX_ERRORS

A value that cannot be converted to its column type becomes NULL, and Impala attaches a warning to the query naming the file and the problem. One bad line should not cost a billion-line query, but the result can quietly discard values, and most BI tools never show the warnings.

Two query options control this. With ABORT_ON_ERROR set to true (its default is false), Impala cancels the query as soon as any node hits an error, rather than continuing and possibly returning incomplete results. When it stays false, MAX_ERRORS controls how many non-fatal errors are logged. Turn ABORT_ON_ERROR on for ingestion and conversion jobs, where a bad file should stop the pipeline, and leave it off for exploration.

Better still, measure: count NULLs per typed column after every load, and keep a STRING-only twin table over the same location to see the raw text of suspect rows:

-- 1. How many rows lost a value they should have had?
SELECT COUNT(*)                                    AS total_rows,
       COUNT(*) - COUNT(order_id)                  AS bad_order_id,
       COUNT(*) - COUNT(amount)                    AS bad_amount,
       COUNT(*) - COUNT(ordered_at)                AS bad_timestamp,
       SUM(CASE WHEN status LIKE '%\r' THEN 1 ELSE 0 END) AS crlf_rows
FROM landing.orders_csv;

The crlf_rows check catches a common surprise: files written on Windows end lines with carriage return and line feed. With LINES TERMINATED BY '\n', the carriage return can be left at the end of the last field, so a string column carries an invisible trailing character and a numeric last column may fail to parse. Fix line endings at the producer or during conversion.

Compressed text and splitting

Impala reads text compressed with gzip, bzip2, deflate, snappy and zstd, and it recognises the codec from the file extension: .gz, .bz2, .deflate, .snappy, .zst. A file with the wrong extension is read as plain text and produces garbage, so keep extensions honest.

None of these compressed text files can be split. Each file is read from start to end by one scanner thread, so a single 20 GB gzip file is a single-threaded scan however large the cluster is. Older documentation lists LZO as a splittable option, through the separate Impala-lzo plugin; the Impala 4.0 release notes say that support was removed, so do not plan on it. For compressed landing data, aim for many files of a few hundred megabytes each rather than a few huge ones, so every executor has work.

Impala can INSERT into uncompressed text tables only. To get compressed text into a table, write the files with another tool, LOAD DATA them, or place them in the table's location and REFRESH. Usually the better answer is Parquet, which is smaller, splittable and faster to scan.

Writing and exporting text

An INSERT ... SELECT into a text table writes delimited files with the table's delimiters, one or more per executor that produced rows. An INSERT ... VALUES statement writes a new tiny file every time it runs, which is the fastest way to create a small-files problem; see small files in Hive and Impala for why that hurts. Text written by Impala is not quoted, so a STRING value containing the delimiter breaks the file unless it is escaped. Pick a delimiter that cannot occur in the data, test the round trip, or write Parquet.

For a one-off export, impala-shell is simpler:

# Export a result as delimited text from impala-shell.
impala-shell -i coordinator:21050 -B --output_delimiter=',' --print_header \
  -q "SELECT order_id, customer, amount FROM curated.orders WHERE order_date='2026-10-01'" \
  -o orders_2026-10-01.csv

The output is not quoted either, so strip the delimiter from values in the SELECT. Shell options are covered in the Impala shell and Web UI guide.

Worked example: from a CSV landing zone to Parquet

A team receives one orders file per hour in s3a://lake/landing/orders/, about 300 MB each, comma separated with a header line. They define the external table shown earlier plus a STRING-only twin. The checks query finds 0.2% of rows with a NULL amount, and the twin shows why: quoted customer names containing commas. The producer switches to a pipe delimiter with a backslash escape; older files move to a separate directory and are converted once through Spark's CSV reader.

They then build a typed, partitioned Parquet table and load it daily:

-- Typed, columnar copy for everything downstream.
CREATE TABLE curated.orders
PARTITIONED BY (order_date)
STORED AS PARQUET
AS SELECT order_id, customer, amount, ordered_at, status,
          CAST(TO_DATE(ordered_at) AS STRING) AS order_date
   FROM landing.orders_csv
   WHERE order_id IS NOT NULL;

COMPUTE STATS curated.orders;

-- Daily: new files arrived in the landing directory.
REFRESH landing.orders_csv;
INSERT INTO curated.orders PARTITION (order_date)
SELECT order_id, customer, amount, ordered_at, status,
       CAST(TO_DATE(ordered_at) AS STRING)
FROM landing.orders_csv
WHERE ordered_at >= '2026-10-01' AND ordered_at < '2026-10-02';

Dashboards query only curated.orders. The daily job runs with ABORT_ON_ERROR enabled, so a malformed file fails loudly instead of loading silent NULLs, and landing files expire after seven days. If the landing data lived on object storage with high listing latency, the advice in Impala on HDFS and S3 about metadata and the data cache applies to the landing table as well.

Trade-offs: text versus Parquet

Text wins at the edge, where any tool can write it and a person can read it; Parquet wins for everything queried twice.

Failure modes

• Quoted CSV loaded as delimited text: values shift columns, typed columns go NULL, and nothing fails.
• Embedded newlines inside quoted fields split one record into two partial rows.
• Header lines in concatenated files read as data because skip.header.line.count only applies at the start of each file.
• Wrong null format: empty strings never match IS NULL, or every empty numeric field raises a warning.
• Windows line endings leave a carriage return in the last column; one huge gzip file scanned by one thread while the rest of the cluster idles.
• A file with a misleading extension read as plain text.
• New files invisible to queries because nobody ran REFRESH after they arrived.
• Written or exported text broken by unescaped delimiters inside values.

What to do next

1. Inventory every text table and record its delimiter, escape, null format, header handling and producer.
2. For each producer, confirm whether it quotes fields; if it does, switch it to a safe delimiter or convert with a real CSV parser.
3. Create a STRING-only twin table for each landing table so you can inspect raw values.
4. Add a post-load check that counts NULLs per typed column and rows ending in a carriage return.
5. Run ingestion and conversion jobs with ABORT_ON_ERROR enabled.
6. Keep compressed landing files to a few hundred megabytes each, with correct extensions.
7. Convert landing data to partitioned Parquet, run COMPUTE STATS, and point every repeated query at the Parquet table.
8. Schedule REFRESH for landing tables after new files arrive, and expire landing files on a fixed retention.