HiveText

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.

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:

1,3
3,5,9

We create a table that defines the column names and types, and insert the file into it with FORMAT HiveText:

CREATE TABLE test_tbl (a UInt16, b UInt32, c UInt32) ENGINE = MergeTree ORDER BY a;

INSERT INTO test_tbl FROM INFILE 'hive_data.txt'
SETTINGS input_format_hive_text_fields_delimiter = ','
FORMAT HiveText;

SELECT * FROM test_tbl;

┌─a─┬─b─┬─c─┐
│ 1 │ 3 │ 0 │
│ 3 │ 5 │ 9 │
└───┴───┴───┘

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:

1,2,3,4,5
6,7,8

CREATE TABLE test_extras (a UInt16, b UInt32, c UInt32) ENGINE = MergeTree ORDER BY a;

INSERT INTO test_extras FROM INFILE 'hive_extras.txt'
SETTINGS input_format_hive_text_fields_delimiter = ','
FORMAT HiveText;

SELECT * FROM test_extras ORDER BY a;

┌─a─┬─b─┬─c─┐
│ 1 │ 2 │ 3 │
│ 6 │ 7 │ 8 │
└───┴───┴───┘

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.

SELECT '20240305', tuple(123567, 'e01001', map('action1', 33333, 'act2', 5555)) FORMAT HiveText;

Format settings