output_format_* format settings

These settings are autogenerated from source.

output_format_always_write_decimal_point_in_float_and_decimal

Always print a decimal point for floating-point and Decimal numbers in text formats, even when the value is a whole number. For example, output 1. instead of 1.

Disabled by default.

output_format_arrow_compression_method

Compression method for Arrow output format. Supported codecs: lz4_frame, zstd, none (uncompressed)

output_format_arrow_date_as_uint16

Write Date values as plain 16-bit numbers (read back as UInt16), instead of converting to a 32-bit Arrow DATE32 type (read back as Date32).

output_format_arrow_fixed_string_as_fixed_byte_array

Use Arrow FIXED_SIZE_BINARY type instead of Binary for FixedString columns.

output_format_arrow_low_cardinality_as_dictionary

Enable output LowCardinality type as Dictionary Arrow type

output_format_arrow_string_as_string

Use Arrow String type instead of Binary for String columns

output_format_arrow_unsupported_types_as_binary

Output types having no conversion as raw binary data. If false - such types would raise UNKNOWN_TYPE exception.

output_format_arrow_use_64_bit_indexes_for_dictionary

Always use 64 bit integers for dictionary indexes in Arrow format

output_format_arrow_use_signed_indexes_for_dictionary

Use signed integers for dictionary indexes in Arrow format

output_format_avro_codec

Compression codec used for output. Possible values: ‘null’, ‘deflate’, ‘snappy’, ‘zstd’.

output_format_avro_confluent_subject

For AvroConfluent output format: the subject name under which the schema is registered in the Confluent Schema Registry. Required when writing AvroConfluent output.

output_format_avro_rows_in_file

Max rows in a file (if permitted by storage)

output_format_avro_string_column_pattern

For Avro format: regexp of String columns to select as AVRO string.

output_format_avro_sync_interval

Sync interval in bytes.

output_format_binary_encode_types_in_binary_format

Write data types in binary format instead of type names in RowBinaryWithNamesAndTypes output format

output_format_binary_write_json_as_string

Write values of JSON data type as JSON String values in RowBinary output format.

output_format_bson_string_as_string

Use BSON String type instead of Binary for String columns.

output_format_compression_level

Default compression level if query output is compressed. The setting is applied when SELECT query has INTO OUTFILE or when writing to table functions file, url, hdfs, s3, or azureBlobStorage.

Possible values: from 1 to 22

output_format_compression_zstd_window_log

Can be used when the output compression method is zstd. If greater than 0, this setting explicitly sets compression window size (power of 2) and enables a long-range mode for zstd compression. This can help to achieve a better compression ratio.

Possible values: non-negative numbers. Note that if the value is too small or too big, zstdlib will throw an exception. Typical values are from 20 (window size = 1MB) to 30 (window size = 1GB).

output_format_csv_crlf_end_of_line

If it is set true, end of line in CSV format will be \r\n instead of \n.

output_format_csv_header_serialize_tuple_into_separate_columns

When output_format_csv_serialize_tuple_into_separate_columns is enabled, the header rows of CSVWithNames and CSVWithNamesAndTypes flatten each bare Tuple column into its leaf fields (dotted names like t.a, t.b, and the leaf type names), so the header has the same number of columns as the data. A Nullable(Tuple) column is never flattened (its data stays a single CSV field), so its header keeps the single top-level column name and type. For CustomSeparated* this flattening applies only when format_custom_escaping_rule = 'CSV' and format_custom_field_delimiter is a single character equal to format_csv_delimiter; otherwise (for example the default tab delimiter or format_custom_field_delimiter = '|') the header stays unflattened so it still matches the data. Set it to 0 to keep the previous behavior where the header keeps the single top-level Tuple name and type.

Note: a flattened header is not read back into a Tuple by name when input_format_with_names_use_header = 1. To read such data back into a Tuple, either set this setting to 0 on output, or read with input_format_with_names_use_header = 0 (and, for the *WithNamesAndTypes formats CSVWithNamesAndTypes and CustomSeparatedWithNamesAndTypes, also input_format_with_types_use_header = 0, since the flattened types row is otherwise validated against the single top-level Tuple input field and rejected).

output_format_csv_serialize_tuple_into_separate_columns

If it set to true, then bare Tuple columns in CSV format are serialized as separate columns (that is, their nesting in the tuple is lost).

This flattening applies only to bare Tuple. A Nullable(Tuple) is always serialized as a single CSV field (so that NULL and non-null rows occupy the same number of fields), regardless of this setting.

output_format_decimal_trailing_zeros

Output trailing zeros when printing Decimal values. E.g. 1.230000 instead of 1.23.

Disabled by default.

output_format_float_precision

When non-zero, format floating-point output (Float32, Float64, BFloat16) with at most this many digits after the decimal point (trailing zeros are removed). When 0 (the default), use the shortest round-trip representation.

Values too large for fixed notation, and values whose magnitude is so small that rounding to the requested precision would lose all significant digits (the mantissa would become ±0), are emitted in scientific notation instead. In these fallback cases the mantissa may carry more than the requested number of fractional digits.

Valid range: 0 to 100.

output_format_image_height

The height of the output image in pixels for image output formats such as PNG.

Default value: 1024.

output_format_image_streaming_animation

For image output formats such as PNG, write each frame of an animation as soon as the next value of the t column is seen, instead of buffering all the frames in memory until the end of the query.

Only one frame is kept in memory, and the frames reach the output while the query is still running. In exchange, the values of t must be non-decreasing, otherwise the query throws an exception, and the number of frames is not known when the header of the animation has to be written, so the declared frame count is an upper bound rather than the exact value. Browsers play such a file, but decoders that trust the declared count report an error after the last real frame.

Default value: false.

output_format_image_terminal_mode

For image output formats such as PNG, output the image directly to the terminal using an inline image protocol instead of writing the raw image bytes.

Possible values:

• `` (empty) — write the raw image bytes (the default).
• iterm — use the iTerm2 inline image protocol.
• kitty — use the Kitty graphics protocol.
• sixel — use the Sixel protocol.
• auto — if the output is a terminal, detect its capabilities and use iterm, kitty, or sixel (in this order); otherwise write the raw image bytes.

Default value: `` (empty).

output_format_image_time_divisor_seconds

The denominator of the time unit of the t column, in seconds, for image output formats such as PNG.

See output_format_image_time_multiplier_seconds.

Default value: 60.

output_format_image_time_multiplier_seconds

The numerator of the time unit of the t column, in seconds, for image output formats such as PNG.

The presence of a t column makes the format produce an animation, in which t is the relative time offset of the frame. One unit of t corresponds to output_format_image_time_multiplier_seconds / output_format_image_time_divisor_seconds seconds. With the default values (1 and 60), one unit of t is 1/60 of a second.

Default value: 1.

output_format_image_width

The width of the output image in pixels for image output formats such as PNG.

Default value: 1024.

output_format_json_array_of_rows

Enables the ability to output all rows as a JSON array in the JSONEachRow format.

Possible values:

• 1 — ClickHouse outputs all rows as an array, each row in the JSONEachRow format.
• 0 — ClickHouse outputs each row separately in the JSONEachRow format.

Example of a query with the enabled setting

Query:

SET output_format_json_array_of_rows = 1;
SELECT number FROM numbers(3) FORMAT JSONEachRow;

Result:

[
{"number":"0"},
{"number":"1"},
{"number":"2"}
]

Example of a query with the disabled setting

Query:

SET output_format_json_array_of_rows = 0;
SELECT number FROM numbers(3) FORMAT JSONEachRow;

Result:

{"number":"0"}
{"number":"1"}
{"number":"2"}

output_format_json_escape_forward_slashes

Controls escaping forward slashes for string outputs in JSON output format. This is intended for compatibility with JavaScript. Don’t confuse with backslashes that are always escaped.

Enabled by default.

output_format_json_map_as_array_of_tuples

Serialize maps columns as JSON arrays of tuples.

Disabled by default.

output_format_json_named_tuples_as_objects

Serialize named tuple columns as JSON objects.

Enabled by default.

output_format_json_pretty_print

This setting determines how nested structures such as Tuples, Maps, and Arrays are displayed within the data array when using the JSON output format.

For example, instead of output:

"data":
[
  {
    "tuple": {"a":1,"b":2,"c":3},
    "array": [1,2,3],
    "map": {"a":1,"b":2,"c":3}
  }
],

The output will be formatted as:

"data":
[
    {
        "tuple": {
            "a": 1,
            "b": 2,
            "c": 3
        },
        "array": [
            1,
            2,
            3
        ],
        "map": {
            "a": 1,
            "b": 2,
            "c": 3
        }
    }
],

Enabled by default.

output_format_json_quote_64bit_floats

Controls quoting of 64-bit floats when they are output in JSON* formats.

Disabled by default.

output_format_json_quote_64bit_integers

Controls quoting of 64-bit or bigger integers (like UInt64 or Int128) when they are output in a JSON format. Such integers are enclosed in quotes by default. This behavior is compatible with most JavaScript implementations.

Possible values:

• 0 — Integers are output without quotes.
• 1 — Integers are enclosed in quotes.

output_format_json_quote_decimals

Controls quoting of decimals in JSON output formats.

Disabled by default.

output_format_json_quote_denormals

Enables +nan, -nan, +inf, -inf outputs in JSON output format.

Possible values:

• 0 — Disabled.
• 1 — Enabled.

Example

Consider the following table account_orders:

┌─id─┬─name───┬─duration─┬─period─┬─area─┐
│  1 │ Andrew │       20 │      0 │  400 │
│  2 │ John   │       40 │      0 │    0 │
│  3 │ Bob    │       15 │      0 │ -100 │
└────┴────────┴──────────┴────────┴──────┘

When output_format_json_quote_denormals = 0, the query returns null values in output:

SELECT area/period FROM account_orders FORMAT JSON;

{
        "meta":
        [
                {
                        "name": "divide(area, period)",
                        "type": "Float64"
                }
        ],

        "data":
        [
                {
                        "divide(area, period)": null
                },
                {
                        "divide(area, period)": null
                },
                {
                        "divide(area, period)": null
                }
        ],

        "rows": 3,

        "statistics":
        {
                "elapsed": 0.003648093,
                "rows_read": 3,
                "bytes_read": 24
        }
}

When output_format_json_quote_denormals = 1, the query returns:

{
        "meta":
        [
                {
                        "name": "divide(area, period)",
                        "type": "Float64"
                }
        ],

        "data":
        [
                {
                        "divide(area, period)": "inf"
                },
                {
                        "divide(area, period)": "-nan"
                },
                {
                        "divide(area, period)": "-inf"
                }
        ],

        "rows": 3,

        "statistics":
        {
                "elapsed": 0.000070241,
                "rows_read": 3,
                "bytes_read": 24
        }
}

output_format_json_skip_null_value_in_named_tuples

Skip key value pairs with null value when serialize named tuple columns as JSON objects. It is only valid when output_format_json_named_tuples_as_objects is true.

output_format_json_validate_utf8

Controls validation of UTF-8 sequences in JSON output formats, doesn’t impact formats JSON/JSONCompact/JSONColumnsWithMetadata, they always validate UTF-8.

Disabled by default.

output_format_markdown_escape_special_characters

When enabled, escape special characters in Markdown.

Common Mark defines the following special characters that can be escaped by :

! " # $ % & ' ( ) * + , - . / : ; < = > ? @ [ \ ] ^ _ ` { | } ~

Possible values:

• 0 — Disable.
• 1 — Enable.

output_format_msgpack_uuid_representation

The way how to output UUID in MsgPack format.

output_format_native_encode_types_in_binary_format

Write data types in binary format instead of type names in Native output format

output_format_native_use_flattened_dynamic_and_json_serialization

Write data of JSON and Dynamic columns in a flattened format (all types/paths as separate subcolumns).

output_format_native_write_json_as_string

Write data of JSON column as String column containing JSON strings instead of default native JSON serialization.

output_format_orc_compression_block_size

The size of the compression block in bytes for ORC output format.

output_format_orc_compression_method

Compression method for ORC output format. Supported codecs: lz4, snappy, zlib, zstd, none (uncompressed)

output_format_orc_dictionary_key_size_threshold

For a string column in ORC output format, if the number of distinct values is greater than this fraction of the total number of non-null rows, turn off dictionary encoding. Otherwise dictionary encoding is enabled

output_format_orc_row_index_stride

Target row index stride in ORC output format

output_format_orc_string_as_string

Use ORC String type instead of Binary for String columns

output_format_orc_writer_time_zone_name

The time zone name for ORC writer, the default ORC writer’s time zone is GMT.

output_format_parallel_formatting

Enables or disables parallel formatting of data formats. Supported only for TSV, TSKV, CSV and JSONEachRow formats.

Possible values:

• 1 — Enabled.
• 0 — Disabled.

output_format_parquet_batch_size

Check page size every this many rows. Consider decreasing if you have columns with average values size above a few KBs.

output_format_parquet_bloom_filter_bits_per_value

Approximate number of bits to use for each distinct value in parquet bloom filters. Estimated false positive rates:

• 6 bits - 10%
• 10.5 bits - 1%
• 16.9 bits - 0.1%
• 26.4 bits - 0.01%
• 41 bits - 0.001%

output_format_parquet_bloom_filter_flush_threshold_bytes

Where in the parquet file to place the bloom filters. Bloom filters will be written in groups of approximately this size. In particular:

• if 0, each row group’s bloom filters are written immediately after the row group,
• if greater than the total size of all bloom filters, bloom filters for all row groups will be accumulated in memory, then written together near the end of the file,
• otherwise, bloom filters will be accumulated in memory and written out whenever their total size goes above this value.

output_format_parquet_compression_method

Compression method for Parquet output format. Supported codecs: snappy, lz4, brotli, zstd, gzip, none (uncompressed)

output_format_parquet_data_page_size

Target page size in bytes, before compression.

output_format_parquet_date_as_uint16

Write Date values as plain 16-bit numbers (read back as UInt16), instead of converting to a 32-bit parquet DATE type (read back as Date32).

output_format_parquet_datetime_as_uint32

Write DateTime values as raw unix timestamp (read back as UInt32), instead of converting to milliseconds (read back as DateTime64(3)).

output_format_parquet_enum_as_byte_array

Write enum using parquet physical type: BYTE_ARRAY and logical type: ENUM

output_format_parquet_fixed_string_as_fixed_byte_array

Use Parquet FIXED_LEN_BYTE_ARRAY type instead of Binary for FixedString columns.

output_format_parquet_geometadata

Allow to write information about geo columns in parquet metadata and encode columns in WKB format.

output_format_parquet_max_dictionary_size

If dictionary size grows bigger than this many bytes, switch to encoding without dictionary. Set to 0 to disable dictionary encoding.

output_format_parquet_parallel_encoding

Do Parquet encoding in multiple threads.

output_format_parquet_row_group_size

Target row group size in rows.

output_format_parquet_row_group_size_bytes

Target row group size in bytes, before compression.

output_format_parquet_string_as_string

Use Parquet String type instead of Binary for String columns.

output_format_parquet_wide_integer_as_decimal

Write Int128, UInt128, Int256, and UInt256 values as standards-compliant Parquet DECIMAL values in big-endian byte order. The default keeps the legacy unannotated little-endian FIXED_LEN_BYTE_ARRAY representation for compatibility with older ClickHouse versions. The decimal representation enables interoperable numeric statistics, but some Parquet readers do not support precisions above 38 or 76.

output_format_parquet_write_bloom_filter

Write bloom filters in parquet files.

output_format_parquet_write_checksums

Put crc32 checksums in parquet page headers.

output_format_parquet_write_page_index

Write column index and offset index (i.e. statistics about each data page, which may be used for filter pushdown on read) into parquet files.

output_format_pretty_color

Use ANSI escape sequences in Pretty formats. 0 - disabled, 1 - enabled, ‘auto’ - enabled if a terminal.

output_format_pretty_display_footer_column_names

Display column names in the footer if there are many table rows.

Possible values:

• 0 — No column names are displayed in the footer.
• 1 — Column names are displayed in the footer if row count is greater than or equal to the threshold value set by output_format_pretty_display_footer_column_names_min_rows (50 by default).

Example

Query:

SELECT *, toTypeName(*) FROM (SELECT * FROM system.numbers LIMIT 1000);

Result:

      ┌─number─┬─toTypeName(number)─┐
   1. │      0 │ UInt64             │
   2. │      1 │ UInt64             │
   3. │      2 │ UInt64             │
   ...
 999. │    998 │ UInt64             │
1000. │    999 │ UInt64             │
      └─number─┴─toTypeName(number)─┘

output_format_pretty_display_footer_column_names_min_rows

Sets the minimum number of rows for which a footer with column names will be displayed if setting output_format_pretty_display_footer_column_names is enabled.

output_format_pretty_fallback_to_vertical

If enabled, and the table is wide but short, the Pretty format will output it as the Vertical format does. See output_format_pretty_fallback_to_vertical_max_rows_per_chunk and output_format_pretty_fallback_to_vertical_min_table_width for detailed tuning of this behavior.

output_format_pretty_fallback_to_vertical_max_rows_per_chunk

The fallback to Vertical format (see output_format_pretty_fallback_to_vertical) will be activated only if the number of records in a chunk is not more than the specified value.

output_format_pretty_fallback_to_vertical_min_columns

The fallback to Vertical format (see output_format_pretty_fallback_to_vertical) will be activated only if the number of columns is greater than the specified value.

output_format_pretty_fallback_to_vertical_min_table_width

The fallback to Vertical format (see output_format_pretty_fallback_to_vertical) will be activated only if the sum of lengths of columns in a table is at least the specified value, or if at least one value contains a newline character.

output_format_pretty_glue_chunks

If the data rendered in Pretty formats arrived in multiple chunks, even after a delay, but the next chunk has the same column widths as the previous, use ANSI escape sequences to move back to the previous line and overwrite the footer of the previous chunk to continue it with the data of the new chunk. This makes the result more visually pleasant.

0 - disabled, 1 - enabled, ‘auto’ - enabled if a terminal.

output_format_pretty_grid_charset

Charset for printing grid borders. Available charsets: ASCII, UTF-8 (default).

In interactive mode, clickhouse-client automatically switches to ASCII when the terminal does not support UTF-8 (as determined by the LC_ALL, LC_CTYPE and LANG environment variables), unless this setting is specified explicitly.

output_format_pretty_highlight_digit_groups

If enabled and if output is a terminal, highlight every digit corresponding to the number of thousands, millions, etc. with underline.

output_format_pretty_highlight_trailing_spaces

If enabled and if output is a terminal, highlight trailing spaces with a gray color and underline.

output_format_pretty_max_column_name_width_cut_to

If the column name is too long, cut it to this length. The column will be cut if it is longer than output_format_pretty_max_column_name_width_cut_to plus output_format_pretty_max_column_name_width_min_chars_to_cut.

output_format_pretty_max_column_name_width_min_chars_to_cut

Minimum characters to cut if the column name is too long. The column will be cut if it is longer than output_format_pretty_max_column_name_width_cut_to plus output_format_pretty_max_column_name_width_min_chars_to_cut.

output_format_pretty_max_column_pad_width

Maximum width to pad all values in a column in Pretty formats.

output_format_pretty_max_rows

Rows limit for Pretty formats.

output_format_pretty_max_value_width

Maximum width of value to display in Pretty formats. If greater - it will be cut. The value 0 means - never cut.

output_format_pretty_max_value_width_apply_for_single_value

Only cut values (see the output_format_pretty_max_value_width setting) when it is not a single value in a block. Otherwise output it entirely, which is useful for the SHOW CREATE TABLE query.

output_format_pretty_multiline_fields

If enabled, Pretty formats will render multi-line fields inside table cell, so the table’s outline will be preserved. If not, they will be rendered as is, potentially deforming the table (one upside of keeping it off is that copy-pasting multi-line values will be easier).

output_format_pretty_named_tuples_as_json

Controls whether named tuples in Pretty format are output as pretty-printed JSON objects.

output_format_pretty_row_numbers

Add row numbers before each row for pretty output format

output_format_pretty_single_large_number_tip_threshold

Print a readable number tip on the right side of the table if the block consists of a single number which exceeds this value (except 0)

output_format_pretty_squash_consecutive_ms

Wait for the next block for up to specified number of milliseconds and squash it to the previous before writing. This avoids frequent output of too small blocks, but still allows to display data in a streaming fashion.

output_format_pretty_squash_max_wait_ms

Output the pending block in pretty formats if more than the specified number of milliseconds has passed since the previous output.

output_format_pretty_use_nbsp_for_padding

If enabled, padding in Pretty output formats is rendered with U+00A0 instead of an ASCII space. The output remains visually identical in monospace, but the padding survives tools that compress or trim runs of regular spaces. Only takes effect when output_format_pretty_grid_charset is UTF-8.

output_format_protobuf_nullables_with_google_wrappers

When serializing Nullable columns with Google wrappers, serialize default values as empty wrappers. If turned off, default and null values are not serialized

output_format_schema

The path to the file where the automatically generated schema will be saved in Cap’n Proto or Protobuf formats.

output_format_sql_insert_include_column_names

Include column names in INSERT query

output_format_sql_insert_max_batch_size

The maximum number of rows in one INSERT statement.

output_format_sql_insert_quote_names

Quote column names with ‘`’ characters

output_format_sql_insert_table_name

The name of table in the output INSERT query

output_format_sql_insert_use_replace

Use REPLACE statement instead of INSERT

output_format_trim_fixed_string

Trim trailing null bytes from FixedString values in text output formats. E.g. toFixedString('John', 8) is printed as John instead of John\0\0\0\0.

output_format_tsv_crlf_end_of_line

If it is set true, end of line in TSV format will be \r\n instead of \n.

output_format_values_escape_quote_with_quote

If true escape ’ with ‘’, otherwise quoted with \’

output_format_write_statistics

Write statistics about read rows, bytes, time elapsed in suitable output formats.

Enabled by default