Format Settings

Use some tweaks and heuristics to infer schema in CSV format

## input\_format\_csv\_use\_default\_on\_bad\_values[​](#input_format_csv_use_default_on_bad_values "Direct link to input_format_csv_use_default_on_bad_values")

Allow to set default value to column when CSV field deserialization failed on bad value

## input\_format\_custom\_allow\_variable\_number\_of\_columns[​](#input_format_custom_allow_variable_number_of_columns "Direct link to input_format_custom_allow_variable_number_of_columns")

Ignore extra columns in CustomSeparated input (if file has more columns than expected) and treat missing fields in CustomSeparated input as default values

## input\_format\_custom\_detect\_header[​](#input_format_custom_detect_header "Direct link to input_format_custom_detect_header")

Automatically detect header with names and types in CustomSeparated format

## input\_format\_custom\_skip\_trailing\_empty\_lines[​](#input_format_custom_skip_trailing_empty_lines "Direct link to input_format_custom_skip_trailing_empty_lines")

Skip trailing empty lines in CustomSeparated format

## input\_format\_defaults\_for\_omitted\_fields[​](#input_format_defaults_for_omitted_fields "Direct link to input_format_defaults_for_omitted_fields")

When performing `INSERT` queries, replace omitted input column values with default values of the respective columns. This option applies to [JSONEachRow](/docs/interfaces/formats/JSONEachRow) (and other JSON formats), [CSV](/docs/interfaces/formats/CSV), [TabSeparated](/docs/interfaces/formats/TabSeparated), [TSKV](/docs/interfaces/formats/TSKV), [Parquet](/docs/interfaces/formats/Parquet), [Arrow](/docs/interfaces/formats/Arrow), [Avro](/docs/interfaces/formats/Avro), [ORC](/docs/interfaces/formats/ORC), [Native](/docs/interfaces/formats/Native) formats and formats with `WithNames`/`WithNamesAndTypes` suffixes.

Note

When this option is enabled, extended table metadata are sent from server to client. It consumes additional computing resources on the server and can reduce performance.

Possible values:

- 0 — Disabled.
- 1 — Enabled.

## input\_format\_force\_null\_for\_omitted\_fields[​](#input_format_force_null_for_omitted_fields "Direct link to input_format_force_null_for_omitted_fields")

Force initialize omitted fields with null values

## input\_format\_hive\_text\_allow\_variable\_number\_of\_columns[​](#input_format_hive_text_allow_variable_number_of_columns "Direct link to 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 in Hive Text input as default values

## input\_format\_hive\_text\_collection\_items\_delimiter[​](#input_format_hive_text_collection_items_delimiter "Direct link to input_format_hive_text_collection_items_delimiter")

Delimiter between collection(array or map) items in Hive Text File

## input\_format\_hive\_text\_fields\_delimiter[​](#input_format_hive_text_fields_delimiter "Direct link to input_format_hive_text_fields_delimiter")

Delimiter between fields in Hive Text File

## input\_format\_hive\_text\_map\_keys\_delimiter[​](#input_format_hive_text_map_keys_delimiter "Direct link to input_format_hive_text_map_keys_delimiter")

Delimiter between a pair of map key/values in Hive Text File

## input\_format\_import\_nested\_json[​](#input_format_import_nested_json "Direct link to input_format_import_nested_json")

Enables or disables the insertion of JSON data with nested objects.

Supported formats:

- [JSONEachRow](/docs/interfaces/formats/JSONEachRow)

Possible values:

- 0 — Disabled.
- 1 — Enabled.

See also:

- [Usage of Nested Structures](/docs/integrations/data-formats/json/other-formats#accessing-nested-json-objects) with the `JSONEachRow` format.

## input\_format\_ipv4\_default\_on\_conversion\_error[​](#input_format_ipv4_default_on_conversion_error "Direct link to input_format_ipv4_default_on_conversion_error")

Deserialization of IPv4 will use default values instead of throwing exception on conversion error.

Disabled by default.

## input\_format\_ipv6\_default\_on\_conversion\_error[​](#input_format_ipv6_default_on_conversion_error "Direct link to input_format_ipv6_default_on_conversion_error")

Deserialization of IPV6 will use default values instead of throwing exception on conversion error.

Disabled by default.

## input\_format\_json\_compact\_allow\_variable\_number\_of\_columns[​](#input_format_json_compact_allow_variable_number_of_columns "Direct link to input_format_json_compact_allow_variable_number_of_columns")

Allow variable number of columns in rows in JSONCompact/JSONCompactEachRow input formats.
Ignore extra columns in rows with more columns than expected and treat missing columns as default values.

Disabled by default.

## input\_format\_json\_defaults\_for\_missing\_elements\_in\_named\_tuple[​](#input_format_json_defaults_for_missing_elements_in_named_tuple "Direct link to input_format_json_defaults_for_missing_elements_in_named_tuple")

Insert default values for missing elements in JSON object while parsing named tuple.
This setting works only when setting `input_format_json_named_tuples_as_objects` is enabled.

Enabled by default.

## input\_format\_json\_empty\_as\_default[​](#input_format_json_empty_as_default "Direct link to input_format_json_empty_as_default")

When enabled, replace empty input fields in JSON with default values. For complex default expressions `input_format_defaults_for_omitted_fields` must be enabled too.

Possible values:

- 0 — Disable.
- 1 — Enable.

## input\_format\_json\_ignore\_unknown\_keys\_in\_named\_tuple[​](#input_format_json_ignore_unknown_keys_in_named_tuple "Direct link to input_format_json_ignore_unknown_keys_in_named_tuple")

Ignore unknown keys in json object for named tuples.

Enabled by default.

## input\_format\_json\_ignore\_unnecessary\_fields[​](#input_format_json_ignore_unnecessary_fields "Direct link to input_format_json_ignore_unnecessary_fields")

Ignore unnecessary fields and not parse them. Enabling this may not throw exceptions on json strings of invalid format or with duplicated fields

## input\_format\_json\_infer\_array\_of\_dynamic\_from\_array\_of\_different\_types[​](#input_format_json_infer_array_of_dynamic_from_array_of_different_types "Direct link to input_format_json_infer_array_of_dynamic_from_array_of_different_types")

If enabled, during schema inference ClickHouse will use Array(Dynamic) type for JSON arrays with values of different data types.

Example:

```
SET input_format_json_infer_array_of_dynamic_from_array_of_different_types=1;
DESC format(JSONEachRow, '{"a" : [42, "hello", [1, 2, 3]]}');
```

```
┌─name─┬─type───────────┐
│ a    │ Array(Dynamic) │
└──────┴────────────────┘
```

```
SET input_format_json_infer_array_of_dynamic_from_array_of_different_types=0;
DESC format(JSONEachRow, '{"a" : [42, "hello", [1, 2, 3]]}');
```

```
┌─name─┬─type─────────────────────────────────────────────────────────────┐
│ a    │ Tuple(Nullable(Int64), Nullable(String), Array(Nullable(Int64))) │
└──────┴──────────────────────────────────────────────────────────────────┘
```

Enabled by default.

## input\_format\_json\_infer\_incomplete\_types\_as\_strings[​](#input_format_json_infer_incomplete_types_as_strings "Direct link to input_format_json_infer_incomplete_types_as_strings")

Allow to use String type for JSON keys that contain only `Null`/`{}`/`[]` in data sample during schema inference.
In JSON formats any value can be read as String, and we can avoid errors like `Cannot determine type for column 'column_name' by first 25000 rows of data, most likely this column contains only Nulls or empty Arrays/Maps` during schema inference
by using String type for keys with unknown types.

Example:

```
SET input_format_json_infer_incomplete_types_as_strings = 1, input_format_json_try_infer_named_tuples_from_objects = 1;
DESCRIBE format(JSONEachRow, '{"obj" : {"a" : [1,2,3], "b" : "hello", "c" : null, "d" : {}, "e" : []}}');
SELECT * FROM format(JSONEachRow, '{"obj" : {"a" : [1,2,3], "b" : "hello", "c" : null, "d" : {}, "e" : []}}');
```

Result: