title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/interfaces/schema-inference.md)

ClickHouse can automatically determine the structure of input data in almost all supported [Input formats](/docs/interfaces/formats).
This document will describe when schema inference is used, how it works with different input formats and which settings
can control it.

## Usage[​](#usage "Direct link to 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](/docs/sql-reference/table-functions/file), [s3](/docs/sql-reference/table-functions/s3), [url](/docs/sql-reference/table-functions/url), [hdfs](/docs/sql-reference/table-functions/hdfs), [azureBlobStorage](/docs/sql-reference/table-functions/azureBlobStorage).[​](#table-functions-file-s3-url-hdfs-azureblobstorage "Direct link to 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:

```
{"id" :  1, "age" :  25, "name" :  "Josh", "hobbies" :  ["football", "cooking", "music"]}
{"id" :  2, "age" :  19, "name" :  "Alan", "hobbies" :  ["tennis", "art"]}
{"id" :  3, "age" :  32, "name" :  "Lana", "hobbies" :  ["fitness", "reading", "shopping"]}
{"id" :  4, "age" :  47, "name" :  "Brayan", "hobbies" :  ["movies", "skydiving"]}
```

ClickHouse can read this data without you specifying its structure:

```
SELECT * FROM file('hobbies.jsonl')
```

```
┌─id─┬─age─┬─name───┬─hobbies──────────────────────────┐
│  1 │  25 │ Josh   │ ['football','cooking','music']   │
│  2 │  19 │ Alan   │ ['tennis','art']                 │
│  3 │  32 │ Lana   │ ['fitness','reading','shopping'] │
│  4 │  47 │ Brayan │ ['movies','skydiving']           │
└────┴─────┴────────┴──────────────────────────────────┘
```

Note: the format `JSONEachRow` was automatically determined by the file extension `.jsonl`.

You can see an automatically determined structure using the `DESCRIBE` query:

```
DESCRIBE file('hobbies.jsonl')
```

```
┌─name────┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id      │ Nullable(Int64)         │              │                    │         │                  │                │
│ age     │ Nullable(Int64)         │              │                    │         │                  │                │
│ name    │ Nullable(String)        │              │                    │         │                  │                │
│ hobbies │ Array(Nullable(String)) │              │                    │         │                  │                │
└─────────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
```

## Table engines [File](/docs/engines/table-engines/special/file), [S3](/docs/engines/table-engines/integrations/s3), [URL](/docs/engines/table-engines/special/url), [HDFS](/docs/engines/table-engines/integrations/hdfs), [azureBlobStorage](/docs/engines/table-engines/integrations/azureBlobStorage)[​](#table-engines-file-s3-url-hdfs-azureblobstorage "Direct link to 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:

```
CREATE TABLE hobbies ENGINE=File(JSONEachRow, 'hobbies.jsonl')
```

```
Ok.
```

```
SELECT * FROM hobbies
```

```
┌─id─┬─age─┬─name───┬─hobbies──────────────────────────┐
│  1 │  25 │ Josh   │ ['football','cooking','music']   │
│  2 │  19 │ Alan   │ ['tennis','art']                 │
│  3 │  32 │ Lana   │ ['fitness','reading','shopping'] │
│  4 │  47 │ Brayan │ ['movies','skydiving']           │
└────┴─────┴────────┴──────────────────────────────────┘
```

```
DESCRIBE TABLE hobbies
```

```
┌─name────┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id      │ Nullable(Int64)         │              │                    │         │                  │                │
│ age     │ Nullable(Int64)         │              │                    │         │                  │                │
│ name    │ Nullable(String)        │              │                    │         │                  │                │
│ hobbies │ Array(Nullable(String)) │              │                    │         │                  │                │
└─────────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
```

## clickhouse-local[​](#clickhouse-local "Direct link to 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`:

```
clickhouse-local --file='hobbies.jsonl' --table='hobbies' --query='DESCRIBE TABLE hobbies'
```

```
id    Nullable(Int64)
age    Nullable(Int64)
name    Nullable(String)
hobbies    Array(Nullable(String))
```

```
clickhouse-local --file='hobbies.jsonl' --table='hobbies' --query='SELECT * FROM hobbies'
```

```
1    25    Josh    ['football','cooking','music']
2    19    Alan    ['tennis','art']
3    32    Lana    ['fitness','reading','shopping']
4    47    Brayan    ['movies','skydiving']
```

## Using structure from insertion table[​](#using-structure-from-insertion-table "Direct link to 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](/docs/operations/settings/settings#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:

```
CREATE TABLE hobbies1
(
    `id` UInt64,
    `age` LowCardinality(UInt8),
    `name` String,
    `hobbies` Array(String)
)
ENGINE = MergeTree
ORDER BY id;
```

And insert data from the file `hobbies.jsonl`:

```
INSERT INTO hobbies1 SELECT * FROM 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:

```
CREATE TABLE hobbies2
(
  `id` UInt64,
  `age` LowCardinality(UInt8),
  `hobbies` Array(String)
)
  ENGINE = MergeTree
ORDER BY id;
```

And insert data from the file `hobbies.jsonl`:

```
INSERT INTO hobbies2 SELECT id, age, hobbies FROM 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: