Skip to main content
ClickHouse can automatically determine the structure of input data in almost all supported Input formats. This document will describe when schema inference is used, how it works with different input formats and which settings can control it.

​
Usage

Schema inference is used when ClickHouse needs to read the data in a specific data format and the structure is unknown.

​
Table functions file, s3, url, hdfs, azureBlobStorage.

These table functions have the optional argument structure with the structure of input data. If this argument is not specified or set to auto, the structure will be inferred from the data. Example: Let’s say we have a file hobbies.jsonl in JSONEachRow format in the user_files directory with this content:
ClickHouse can read this data without you specifying its structure:
Note: the format JSONEachRow was automatically determined by the file extension .jsonl. You can see an automatically determined structure using the DESCRIBE query:

​
Table engines File, S3, URL, HDFS, azureBlobStorage

If the list of columns is not specified in CREATE TABLE query, the structure of the table will be inferred automatically from the data. Example: Let’s use the file hobbies.jsonl. We can create a table with engine File with the data from this file:

​
clickhouse-local

clickhouse-local has an optional parameter -S/--structure with the structure of input data. If this parameter is not specified or set to auto, the structure will be inferred from the data. Example: Let’s use the file hobbies.jsonl. We can query the data from this file using clickhouse-local:

​
Using structure from insertion table

When table functions file/s3/url/hdfs are used to insert data into a table, there is an option to use the structure from the insertion table instead of extracting it from the data. It can improve insertion performance because schema inference can take some time. Also, it will be helpful when the table has an optimized schema, so no conversions between types will be performed. There is a special setting use_structure_from_insertion_table_in_table_functions that controls this behaviour. It has 3 possible values:
  • 0 - table function will extract the structure from the data.
  • 1 - table function will use the structure from the insertion table.
  • 2 - ClickHouse will automatically determine if it’s possible to use the structure from the insertion table or use schema inference. Default value.
Example 1: Let’s create table hobbies1 with the next structure:
And insert data from the file hobbies.jsonl:
In this case, all columns from the file are inserted into the table without changes, so ClickHouse will use the structure from the insertion table instead of schema inference. Example 2: Let’s create table hobbies2 with the next structure:
And insert data from the file hobbies.jsonl:
In this case, all columns in the SELECT query are present in the table, so ClickHouse will use the structure from the insertion table. Note that it will work only for input formats that support reading a subset of columns like JSONEachRow, TSKV, Parquet, etc. (so it won’t work for example for TSV format). Example 3: Let’s create table hobbies3 with the next structure:
And insert data from the file hobbies.jsonl:
In this case, column id is used in the SELECT query, but the table doesn’t have this column (it has a column with the name identifier), so ClickHouse cannot use the structure from the insertion table, and schema inference will be used. Example 4: Let’s create table hobbies4 with the next structure:
And insert data from the file hobbies.jsonl:
In this case, there are some operations performed on the column hobbies in the SELECT query to insert it into the table, so ClickHouse cannot use the structure from the insertion table, and schema inference will be used.

​
Schema inference cache

For most input formats schema inference reads some data to determine its structure and this process can take some time. To prevent inferring the same schema every time ClickHouse read the data from the same file, the inferred schema is cached and when accessing the same file again, ClickHouse will use the schema from the cache. There are special settings that control this cache:
  • schema_inference_cache_max_elements_for_{file/s3/hdfs/url/azure} - the maximum number of cached schemas for the corresponding table function. The default value is 4096. These settings should be set in the server config.
  • schema_inference_use_cache_for_{file,s3,hdfs,url,azure} - allows turning on/off using cache for schema inference. These settings can be used in queries.
The schema of the file can be changed by modifying the data or by changing format settings. For this reason, the schema inference cache identifies the schema by file source, format name, used format settings, and the last modification time of the file. Note: some files accessed by url in url table function may not contain information about the last modification time; for this case, there is a special setting schema_inference_cache_require_modification_time_for_url. Disabling this setting allows the use of the schema from cache without the last modification time for such files. There is also a system table schema_inference_cache with all current schemas in cache and system query SYSTEM CLEAR SCHEMA CACHE [FOR File/S3/URL/HDFS] that allows cleaning the schema cache for all sources, or for a specific source. Examples: Let’s try to infer the structure of a sample dataset from s3 github-2022.ndjson.gz and see how the schema inference cache works:
As you can see, the second query succeeded almost instantly. Let’s try to change some settings that can affect inferred schema:
As you can see, the schema from the cache was not used for the same file, because the setting that can affect inferred schema was changed. Let’s check the content of system.schema_inference_cache table:
As you can see, there are two different schemas for the same file. We can clear the schema cache using a system query:

​
Text formats

For text formats, ClickHouse reads the data row by row, extracts column values according to the format, and then uses some recursive parsers and heuristics to determine the type for each value. The maximum number of rows and bytes read from the data in schema inference is controlled by the settings input_format_max_rows_to_read_for_schema_inference (25000 by default) and input_format_max_bytes_to_read_for_schema_inference (32Mb by default). By default, all inferred types are Nullable, but you can change this by setting schema_inference_make_columns_nullable (see examples in the settings section).

​
JSON formats

In JSON formats ClickHouse parses values according to the JSON specification and then tries to find the most appropriate data type for them. Let’s see how it works, what types can be inferred and what specific settings can be used in JSON formats. Examples Here and further, the format table function will be used in examples. Integers, Floats, Bools, Strings:
Dates, DateTimes:
Arrays:
If an array contains null, ClickHouse will use types from the other array elements:
If an array contains values of different types and setting input_format_json_infer_array_of_dynamic_from_array_of_different_types is enabled (it is enabled by default), then it will have type Array(Dynamic):
Named tuples: When setting input_format_json_try_infer_named_tuples_from_objects is enabled, during schema inference ClickHouse will try to infer named Tuple from JSON objects. The resulting named Tuple will contain all elements from all corresponding JSON objects from sample data.
Unnamed Tuples: If setting input_format_json_infer_array_of_dynamic_from_array_of_different_types is disabled, we treat Arrays with elements of different types as Unnamed Tuples in JSON formats.
If some values are null or empty, we use types of corresponding values from the other rows:
Maps: In JSON we can read objects with values of the same type as Map type. Note: it will work only when settings input_format_json_read_objects_as_strings and input_format_json_try_infer_named_tuples_from_objects are disabled.
Nested complex types:
If ClickHouse cannot determine the type for some key, because the data contains only nulls/empty objects/empty arrays, type String will be used if setting input_format_json_infer_incomplete_types_as_strings is enabled or an exception will be thrown otherwise:

​
JSON settings

Enabling this setting allows inferring numbers from string values. This setting is disabled by default. Example:
Enabling this setting allows inferring named Tuples from JSON objects. The resulting named Tuple will contain all elements from all corresponding JSON objects from sample data. It can be useful when JSON data is not sparse so the sample of data will contain all possible object keys. This setting is enabled by default. Example
Query
Response
Query
Response
Enabling this setting allows to use String type for ambiguous paths during named tuples inference from JSON objects (when input_format_json_try_infer_named_tuples_from_objects is enabled) instead of an exception. It allows to read JSON objects as named Tuples even if there are ambiguous paths. Disabled by default. Examples With disabled setting:
Query
Response
With enabled setting:
Query
Response
Enabling this setting allows reading nested JSON objects as strings. This setting can be used to read nested JSON objects without using JSON object type. This setting is enabled by default. Note: enabling this setting will take effect only if setting input_format_json_try_infer_named_tuples_from_objects is disabled.
Enabling this setting allows reading numeric values as strings. This setting is enabled by default. Example
Enabling this setting allows reading Bool values as numbers. This setting is enabled by default. Example:
Enabling this setting allows reading Bool values as strings. This setting is enabled by default. Example:
Enabling this setting allows reading JSON array values as strings. This setting is enabled by default. Example
Enabling this setting allows 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 if all corresponding settings are enabled (they are all enabled by default), 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:
Query
Response

​
CSV

In CSV format ClickHouse extracts column values from the row according to delimiters. ClickHouse expects all types except numbers and strings to be enclosed in double quotes. If the value is in double quotes, ClickHouse tries to parse the data inside quotes using the recursive parser and then tries to find the most appropriate data type for it. If the value is not in double quotes, ClickHouse tries to parse it as a number, and if the value is not a number, ClickHouse treats it as a string. If you don’t want ClickHouse to try to determine complex types using some parsers and heuristics, you can disable setting input_format_csv_use_best_effort_in_schema_inference and ClickHouse will treat all columns as Strings. If setting input_format_csv_detect_header is enabled, ClickHouse will try to detect the header with column names (and maybe types) while inferring schema. This setting is enabled by default. Examples: Integers, Floats, Bools, Strings:
Strings without quotes:
Dates, DateTimes:
Arrays:
If an array contains null, ClickHouse will use types from the other array elements:
Maps:
Nested Arrays and Maps:
If ClickHouse cannot determine the type inside quotes, because the data contains only nulls, ClickHouse will treat it as String:
Example with disabled setting input_format_csv_use_best_effort_in_schema_inference:
Examples of header auto-detection (when input_format_csv_detect_header is enabled): Only names:
Names and types:
Note that the header can be detected only if there is at least one column with a non-String type. If all columns have String type, the header is not detected:

​
CSV settings

Enabling this setting allows inferring numbers from string values. This setting is disabled by default. Example:

​
TSV/TSKV

In TSV/TSKV formats ClickHouse extracts column value from the row according to tabular delimiters and then parses extracted value using the recursive parser to determine the most appropriate type. If the type cannot be determined, ClickHouse treats this value as String. If you don’t want ClickHouse to try to determine complex types using some parsers and heuristics, you can disable setting input_format_tsv_use_best_effort_in_schema_inference and ClickHouse will treat all columns as Strings. If setting input_format_tsv_detect_header is enabled, ClickHouse will try to detect the header with column names (and maybe types) while inferring schema. This setting is enabled by default. Examples: Integers, Floats, Bools, Strings:
Dates, DateTimes:
Arrays:
If an array contains null, ClickHouse will use types from the other array elements:
Tuples:
Maps:
Nested Arrays, Tuples and Maps:
If ClickHouse cannot determine the type, because the data contains only nulls, ClickHouse will treat it as String:
Example with disabled setting input_format_tsv_use_best_effort_in_schema_inference:
Examples of header auto-detection (when input_format_tsv_detect_header is enabled): Only names:
Names and types:
Note that the header can be detected only if there is at least one column with a non-String type. If all columns have String type, the header is not detected:

​
Values

In Values format ClickHouse extracts column value from the row and then parses it using the recursive parser similar to how literals are parsed. Examples: Integers, Floats, Bools, Strings:
Dates, DateTimes:
Arrays:
If an array contains null, ClickHouse will use types from the other array elements:
Tuples:
Maps:
Nested Arrays, Tuples and Maps:
If ClickHouse cannot determine the type, because the data contains only nulls, an exception will be thrown:
Example with disabled setting input_format_tsv_use_best_effort_in_schema_inference:

​
CustomSeparated

In CustomSeparated format ClickHouse first extracts all column values from the row according to specified delimiters and then tries to infer the data type for each value according to escaping rule. If setting input_format_custom_detect_header is enabled, ClickHouse will try to detect the header with column names (and maybe types) while inferring schema. This setting is enabled by default. Example
Example of header auto-detection (when input_format_custom_detect_header is enabled):

​
Template

In Template format ClickHouse first extracts all column values from the row according to the specified template and then tries to infer the data type for each value according to its escaping rule. Example Let’s say we have a file resultset with the next content:
And a file row_format with the next content:
Then we can make the next queries:

​
Regexp

Similar to Template, in Regexp format ClickHouse first extracts all column values from the row according to specified regular expression and then tries to infer data type for each value according to the specified escaping rule. Example

​
Settings for text formats

​
input_format_max_rows_to_read_for_schema_inference/input_format_max_bytes_to_read_for_schema_inference

These settings control the amount of data to be read while schema inference. The more rows/bytes are read, the more time is spent on schema inference, but the greater the chance to correctly determine the types (especially when the data contains a lot of nulls). Default values:
  • 25000 for input_format_max_rows_to_read_for_schema_inference.
  • 33554432 (32 Mb) for input_format_max_bytes_to_read_for_schema_inference.

​
column_names_for_schema_inference

The list of column names to use in schema inference for formats without explicit column names. Specified names will be used instead of default c1,c2,c3,.... The format: column1,column2,column3,.... Example

​
schema_inference_hints

The list of column names and types to use in schema inference instead of automatically determined types. The format: ‘column_name1 column_type1, column_name2 column_type2, …’. This setting can be used to specify the types of columns that could not be determined automatically or for optimizing the schema. Example

​
schema_inference_make_columns_nullable $

Controls making inferred types Nullable in schema inference for formats without information about nullability. Possible values:
  • 0 - the inferred type will never be Nullable,
  • 1 - all inferred types will be Nullable,
  • 2 or ‘auto’ - for text formats, the inferred type will be Nullable only if the column contains NULL in a sample that is parsed during schema inference; for strongly-typed formats (Parquet, ORC, Arrow), nullability information is taken from file metadata,
  • 3 - for text formats, use Nullable; for strongly-typed formats, use file metadata.
Default: 3. Examples

​
input_format_try_infer_integers

This setting does not apply to the JSON data type.
If enabled, ClickHouse will try to infer integers instead of floats in schema inference for text formats. If all numbers in the column from sample data are integers, the result type will be Int64, if at least one number is float, the result type will be Float64. If the sample data contains only integers and at least one integer is positive and overflows Int64, ClickHouse will infer UInt64. Enabled by default. Examples

​
input_format_try_infer_datetimes

If enabled, ClickHouse will try to infer type DateTime or DateTime64 from string fields in schema inference for text formats. If all fields from a column in sample data were successfully parsed as datetimes, the result type will be DateTime or DateTime64(9) (if any datetime had fractional part), if at least one field was not parsed as datetime, the result type will be String. Enabled by default. Examples

​
input_format_try_infer_datetimes_only_datetime64

If enabled, ClickHouse will always infer DateTime64(9) when input_format_try_infer_datetimes is enabled even if datetime values don’t contain fractional part. Disabled by default. Examples
Note: Parsing datetimes during schema inference respect setting date_time_input_format

​
input_format_try_infer_dates

If enabled, ClickHouse will try to infer type Date from string fields in schema inference for text formats. If all fields from a column in sample data were successfully parsed as dates, the result type will be Date, if at least one field was not parsed as date, the result type will be String. Enabled by default. Examples

​
input_format_try_infer_exponent_floats

If enabled, ClickHouse will try to infer floats in exponential form for text formats (except JSON where numbers in exponential form are always inferred). Disabled by default. Example

​
Self describing formats

Self-describing formats contain information about the structure of the data in the data itself, it can be some header with a description, a binary type tree, or some kind of table. To automatically infer a schema from files in such formats, ClickHouse reads a part of the data containing information about the types and converts it into a schema of the ClickHouse table.

​
Formats with -WithNamesAndTypes suffix

ClickHouse supports some text formats with the suffix -WithNamesAndTypes. This suffix means that the data contains two additional rows with column names and types before the actual data. While schema inference for such formats, ClickHouse reads the first two rows and extracts column names and types. Example

​
JSON formats with metadata

Some JSON input formats (JSON, JSONCompact, JSONColumnsWithMetadata) contain metadata with column names and types. In schema inference for such formats, ClickHouse reads this metadata. Example

​
Avro

In Avro format ClickHouse reads its schema from the data and converts it to ClickHouse schema using the following type matches: * Avro logical types Other Avro types are not supported.

​
Parquet

In Parquet format ClickHouse reads its schema from the data and converts it to ClickHouse schema using the following type matches: Other Parquet types are not supported.

​
Arrow

In Arrow format ClickHouse reads its schema from the data and converts it to ClickHouse schema using the following type matches: Other Arrow types are not supported.

​
ORC

In ORC format ClickHouse reads its schema from the data and converts it to ClickHouse schema using the following type matches: Other ORC types are not supported.

​
Native

Native format is used inside ClickHouse and contains the schema in the data. In schema inference, ClickHouse reads the schema from the data without any transformations.

​
Formats with external schema

Such formats require a schema describing the data in a separate file in a specific schema language. To automatically infer a schema from files in such formats, ClickHouse reads external schema from a separate file and transforms it to a ClickHouse table schema.

​
Protobuf

In schema inference for Protobuf format ClickHouse uses the following type matches:

​
CapnProto

In schema inference for CapnProto format ClickHouse uses the following type matches:

​
Strong-typed binary formats

In such formats, each serialized value contains information about its type (and possibly about its name), but there is no information about the whole table. In schema inference for such formats, ClickHouse reads data row by row (up to input_format_max_rows_to_read_for_schema_inference rows or input_format_max_bytes_to_read_for_schema_inference bytes) and extracts the type (and possibly name) for each value from the data and then converts these types to ClickHouse types.

​
MsgPack

In MsgPack format there is no delimiter between rows, to use schema inference for this format you should specify the number of columns in the table using the setting input_format_msgpack_number_of_columns. ClickHouse uses the following type matches: By default, all inferred types are inside Nullable, but it can be changed using the setting schema_inference_make_columns_nullable.

​
BSONEachRow

In BSONEachRow each row of data is presented as a BSON document. In schema inference ClickHouse reads BSON documents one by one and extracts values, names, and types from the data and then transforms these types to ClickHouse types using the following type matches: By default, all inferred types are inside Nullable, but it can be changed using the setting schema_inference_make_columns_nullable.

​
Formats with constant schema

Data in such formats always have the same schema.

​
LineAsString

In this format, ClickHouse reads the whole line from the data into a single column with String data type. The inferred type for this format is always String and the column name is line. Example

​
JSONAsString

In this format, ClickHouse reads the whole JSON object from the data into a single column with String data type. The inferred type for this format is always String and the column name is json. Example

​
JSONAsObject

In this format, ClickHouse reads the whole JSON object from the data into a single column with JSON data type. Inferred type for this format is always JSON and the column name is json. Example

​
Schema inference modes

Schema inference from the set of data files can work in 2 different modes: default and union. The mode is controlled by the setting schema_inference_mode.

​
Default mode

In default mode, ClickHouse assumes that all files have the same schema and tries to infer the schema by reading files one by one until it succeeds. Example: Let’s say we have 3 files data1.jsonl, data2.jsonl and data3.jsonl with the next content: data1.jsonl:
data2.jsonl:
data3.jsonl:
Let’s try to use schema inference on these 3 files:
Query
Response
As we can see, we don’t have field3 from file data3.jsonl. It happens because ClickHouse first tried to infer schema from file data1.jsonl, failed because of only nulls for field field2, and then tried to infer schema from data2.jsonl and succeeded, so data from file data3.jsonl wasn’t read.

​
Union mode

In union mode, ClickHouse assumes that files can have different schemas, so it infer schemas of all files and then union them to the common schema. Let’s say we have 3 files data1.jsonl, data2.jsonl and data3.jsonl with the next content: data1.jsonl:
data2.jsonl:
data3.jsonl:
Let’s try to use schema inference on these 3 files:
Query
Response
As we can see, we have all fields from all files. Note:
  • As some of the files may not contain some columns from the resulting schema, union mode is supported only for formats that support reading subset of columns (like JSONEachRow, Parquet, TSVWithNames, etc) and won’t work for other formats (like CSV, TSV, JSONCompactEachRow, etc).
  • If ClickHouse cannot infer the schema from one of the files, the exception will be thrown.
  • If you have a lot of files, reading schema from all of them can take a lot of time.

​
Automatic format detection

If data format is not specified and cannot be determined by the file extension, ClickHouse will try to detect the file format by its content. Examples: Let’s say we have data with the following content:
We can inspect and query this file without specifying format or structure:
ClickHouse can detect only some subset of formats and this detection takes some time, it’s always better to specify the format explicitly.
Last modified on June 19, 2026