Skip to main content

​
Description

HiveText reads and writes the text serialization format used by Apache Hive tables (the format produced by Hive’s LazySimpleSerDe). It is a delimited text format, similar to CSV, in which fields are separated by the Hive default \x01 (Ctrl-A) delimiter. The field delimiter is configurable via input_format_hive_text_fields_delimiter. When used as an input format, the data has no header row: values are mapped positionally onto the columns of the destination table, so the column names and types are taken from the table (or from an explicitly provided structure) rather than inferred from the data. While reading, ClickHouse parses dates and times in best-effort mode (see date_time_input_format), fills omitted trailing fields with column defaults, and skips fields it does not recognize. Within a field, values are parsed using the same escaping rules as CSV rather than Hive’s nested delimiters. In particular, a column of type Array is read from the bracketed representation (for example, "['a','b','c']"), not from values separated by the Hive collection delimiter \x02.
Nested delimiter settings have no effect on inputThe input_format_hive_text_collection_items_delimiter and input_format_hive_text_map_keys_delimiter settings are accepted for compatibility but are currently not used during parsing. They are, however, used when writing nested values on the output side.
By default, rows are allowed to have a variable number of fields (see input_format_hive_text_allow_variable_number_of_columns): rows with fewer fields than the table have the missing columns filled with default values, and rows with extra trailing fields have the extras skipped.

​
Example usage

The examples below override the default field delimiter with a comma (,) using input_format_hive_text_fields_delimiter so that the input files are easy to read.

​
Reading a HiveText file

Given a file hive_data.txt with comma-separated fields:
hive_data.txt
We create a table that defines the column names and types, and insert the file into it with FORMAT HiveText:
Query
Response
Note that the first row, 1,3, has only two fields, so the missing column c is filled with its default value 0.

​
Variable number of columns

With the default input_format_hive_text_allow_variable_number_of_columns = 1, rows that have more fields than the table simply have the extra trailing fields skipped:
hive_extras.txt
Query
Response
Setting input_format_hive_text_allow_variable_number_of_columns = 0 instead enforces a strict field count, and a row with fewer fields than the table raises a parsing exception.

​
Output

When used as an output format, HiveText writes each row without any quoting: top-level fields are separated by the fields delimiter (\x01 by default) and rows are separated by the rows delimiter (\n by default, configurable via format_hive_text_rows_delimiter). Values of nested types (Array, Map and Tuple) are written without brackets and are separated by the Hive separator for their nesting level, the same way Hive’s LazySimpleSerDe does it. The first three separators are the configurable fields delimiter, input_format_hive_text_collection_items_delimiter (\x02 by default, used for array elements, map entries and tuple elements) and input_format_hive_text_map_keys_delimiter (\x03 by default, used between a map key and its value); deeper levels default to consecutive control characters (\x04, \x05, and so on, up to eight levels). A type tree nested deeply enough to need a separator beyond those eight levels is rejected with a NOT_IMPLEMENTED exception, since Hive’s LazySimpleSerDe has no separator for it either. Data types that have no natural Hive text representation are not supported for output and raise a NOT_IMPLEMENTED exception. This includes AggregateFunction, Dynamic, Variant, LowCardinality and Object, as well as the numeric-backed types Enum, Time, Time64 and Interval — Hive has no matching type for the latter, so they are rejected rather than written as their raw underlying numbers. The wide numeric types Int128, UInt128, Int256 and UInt256 are rejected for the same reason: the widest Hive integer is BIGINT (64-bit), and even Hive DECIMAL with its maximum precision of 38 cannot hold their value range. Likewise, Decimal values with a precision above 38 (that is, Decimal256) exceed the maximum precision of Hive DECIMAL and are rejected. Likewise, Map keys must be of a primitive type: Hive declares maps as MAP<primitive_type, data_type>, so a Map whose key type is an Array, Map or Tuple (which ClickHouse permits) is rejected with a NOT_IMPLEMENTED exception, because no Hive schema could read such values back. The empty map literal map() is rejected for the same reason: its type is Map(Nothing, Nothing), and Nothing is not a type that a Hive MAP<key_type, data_type> declaration could name. All these checks are applied upfront to the declared column types, before any row is written: a query whose header contains an unsupported type anywhere in its type tree is rejected even when the actual values would never reach the unsupported serialization (for example, a Nullable of an unsupported type holding only NULL values, or an empty Array/Map of an unsupported element type), because the file’s declared schema still could not belong to any Hive table. Date, Date32, DateTime and DateTime64 are always written in the plain Hive date and timestamp text (yyyy-MM-dd and yyyy-MM-dd HH:mm:ss[.fffffffff]), independent of the date_time_output_format setting, so the output stays parseable by Hive even when that setting is unix_timestamp or iso. For the same reason, Bool values are always written as true/false, independent of the bool_true_representation and bool_false_representation settings, and NULL values are always written as Hive’s default null sequence \N, independent of the format_csv_null_representation setting. This keeps the output readable by Hive’s LazySimpleSerDe regardless of these generic text settings. Symmetrically, the HiveText input format always reads \N as NULL, also independent of the format_csv_null_representation setting, so the top-level scalar round-trip does not depend on it. Non-finite Float32 and Float64 values are written using Hive’s Java spellings NaN, Infinity and -Infinity, rather than ClickHouse’s usual nan/inf/-inf tokens, so that Hive’s FLOAT/DOUBLE parser reads them back as the same values instead of NULL.
Hive-compatible output, not a full round-trip through the input formatThe output side targets Hive’s default LazySimpleSerDe and is not symmetric with ClickHouse’s own HiveText input:
  • Nested Array, Map and Tuple values are written with Hive’s nested separators (without brackets), but the input format parses each field with CSV/bracketed rules and ignores input_format_hive_text_collection_items_delimiter / input_format_hive_text_map_keys_delimiter. So nested output such as SELECT [1, 2] FORMAT HiveText is not read back by INSERT ... FORMAT HiveText — only top-level scalar fields round-trip, and only with the default \n row delimiter (see the next point).
  • Round-tripping also requires the default \n row delimiter. When format_hive_text_rows_delimiter is changed, the output separates rows with the configured byte, but the input side is still the newline-based CSVRowInputFormat and there is no matching input_format_hive_text_rows_delimiter. So multi-row scalar output such as SELECT number FROM numbers(3) FORMAT HiveText SETTINGS format_hive_text_rows_delimiter=';' (which produces 0;1;2;) is not read back by INSERT ... FORMAT HiveText as three rows.
  • Only the default, unescaped LazySimpleSerDe subset is implemented. Fields are written without escaping (there is no equivalent of Hive’s optional ROW FORMAT DELIMITED ... ESCAPED BY), and NULL is always written as \N (there is no equivalent of NULL DEFINED AS). A String that itself contains an active field, row or nested separator is therefore written literally and will be misread when parsed back — this matches how Hive itself behaves with a non-escaping serde. For the same reason a String whose value is literally \N (for example SELECT '\\N'::String FORMAT HiveText) is written as the same two bytes as a real NULL, so the two are indistinguishable on the Hive side.
Query

​
Format settings

Last modified on August 6, 2026