> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-revert-104359-revert-104251-parquet-single.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> Documentation for the HiveText format

# HiveText

| Input | Output | Alias |
| ----- | ------ | ----- |
| ✔     | ✔      |       |

<h2 id="description">
  Description
</h2>

`HiveText` reads and writes the text serialization format used by [Apache Hive](https://hive.apache.org/)
tables (the format produced by Hive's `LazySimpleSerDe`). It is a delimited text
format, similar to [`CSV`](/reference/formats/CSV/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`](#format-settings).

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`](/reference/settings/formats/date-time#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`](/reference/data-types/array) is read from the bracketed
representation (for example, `"['a','b','c']"`), not from values separated by
the Hive collection delimiter `\x02`.

<Info>
  **Nested delimiter settings have no effect on input**

  The [`input_format_hive_text_collection_items_delimiter`](#format-settings) and
  [`input_format_hive_text_map_keys_delimiter`](#format-settings) settings are
  accepted for compatibility but are currently not used during parsing. They are,
  however, used when writing nested values on the output side.
</Info>

By default, rows are allowed to have a variable number of fields (see
[`input_format_hive_text_allow_variable_number_of_columns`](#format-settings)):
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.

<h2 id="example-usage">
  Example usage
</h2>

The examples below override the default field delimiter with a comma (`,`) using
[`input_format_hive_text_fields_delimiter`](#format-settings) so that the input
files are easy to read.

<h3 id="reading-data">
  Reading a HiveText file
</h3>

Given a file `hive_data.txt` with comma-separated fields:

```text title="hive_data.txt" theme={null}
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`:

```sql title="Query" theme={null}
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;
```

```response title="Response" theme={null}
┌─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`.

<h3 id="variable-number-of-columns">
  Variable number of columns
</h3>

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:

```text title="hive_extras.txt" theme={null}
1,2,3,4,5
6,7,8
```

```sql title="Query" theme={null}
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;
```

```response title="Response" theme={null}
┌─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.

<h2 id="output">
  Output
</h2>

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`](#format-settings)). Values of nested types
([`Array`](/reference/data-types/array), [`Map`](/reference/data-types/map)
and [`Tuple`](/reference/data-types/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`](#format-settings)
(`\x02` by default, used for array elements, map entries and tuple elements) and
[`input_format_hive_text_map_keys_delimiter`](#format-settings) (`\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`](/reference/settings/formats/date-time#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`](/reference/settings/formats/bool#bool_true_representation)
and [`bool_false_representation`](/reference/settings/formats/bool#bool_false_representation)
settings, and `NULL` values are always written as Hive's default null sequence
`\N`, independent of the [`format_csv_null_representation`](/reference/settings/formats/format-csv#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`](/reference/settings/formats/format-csv#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`.

<Info>
  **Hive-compatible output, not a full round-trip through the input format**

  The output side targets Hive's default `LazySimpleSerDe` and is not symmetric with
  ClickHouse's own `HiveText` input:

  * Nested [`Array`](/reference/data-types/array), [`Map`](/reference/data-types/map)
    and [`Tuple`](/reference/data-types/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`](#format-settings) /
    [`input_format_hive_text_map_keys_delimiter`](#format-settings). 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`](#format-settings) 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.
</Info>

```sql title="Query" theme={null}
SELECT '20240305', tuple(123567, 'e01001', map('action1', 33333, 'act2', 5555)) FORMAT HiveText;
```

<h2 id="format-settings">
  Format settings
</h2>

| Setting                                                   | Description                                                                                                                                           | Default |
| --------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------- | ------- |
| `input_format_hive_text_fields_delimiter`                 | Delimiter between fields in Hive Text File                                                                                                            | `\x01`  |
| `input_format_hive_text_collection_items_delimiter`       | Delimiter between collection (array or map) items in Hive Text File. Used by the output format; accepted but currently not used during input parsing. | `\x02`  |
| `input_format_hive_text_map_keys_delimiter`               | Delimiter between a pair of map key/values in Hive Text File. Used by the output format; accepted but currently not used during input parsing.        | `\x03`  |
| `input_format_hive_text_allow_variable_number_of_columns` | Ignore extra columns in Hive Text input (if file has more columns than expected) and treat missing fields as default values                           | `1`     |
| `format_hive_text_rows_delimiter`                         | Delimiter at the end of each row in Hive Text output                                                                                                  | `\n`    |