Inferencia automática de esquemas a partir de los datos de entrada

Página que describe la inferencia automática de esquemas a partir de los datos de entrada en ClickHouse

ClickHouse puede determinar automáticamente la estructura de los datos de entrada en casi todos los formatos de entrada compatibles. En este documento se describe cuándo se utiliza la inferencia de esquemas, cómo funciona con distintos formatos de entrada y qué opciones de configuración permiten controlarla.

​

Uso

La inferencia de esquemas se utiliza cuando ClickHouse necesita leer datos en un formato de datos específico y se desconoce la estructura.

​

Funciones de tabla file, s3, url, hdfs, azureBlobStorage.

Estas funciones de tabla incluyen el argumento opcional structure, que especifica la estructura de los datos de entrada. Si no se especifica este argumento o se establece en auto, la estructura se inferirá de los datos.

Ejemplo:

Supongamos que tenemos un archivo hobbies.jsonl en formato JSONEachRow en el directorio user_files con este contenido:

{"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 puede leer estos datos sin que tengas que especificar su estructura:

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']           │
└────┴─────┴────────┴──────────────────────────────────┘

Nota: el formato JSONEachRow se determinó automáticamente por la extensión del archivo .jsonl.

Puede ver la estructura detectada automáticamente con la consulta DESCRIBE:

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)) │              │                    │         │                  │                │
└─────────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

​

Motores de tabla File, S3, URL, HDFS, azureBlobStorage

Si no se especifica la lista de columnas en la consulta CREATE TABLE, la estructura de la tabla se inferirá automáticamente a partir de los datos.

Ejemplo:

Usemos el archivo hobbies.jsonl. Podemos crear una tabla con el motor File a partir de los datos de este archivo:

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 tiene un parámetro opcional -S/--structure que define la estructura de los datos de entrada. Si este parámetro no se especifica o se establece en auto, la estructura se inferirá a partir de los datos.

Ejemplo:

Usemos el archivo hobbies.jsonl. Podemos consultar los datos de este archivo con 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']

​

Uso de la estructura de la tabla de inserción

Cuando se usan las funciones de tabla file/s3/url/hdfs para insertar datos en una tabla, existe la opción de usar la estructura de la tabla de inserción en lugar de extraerla de los datos. Esto puede mejorar el rendimiento de la inserción, porque la inferencia de esquemas puede llevar algo de tiempo. Además, resulta útil cuando la tabla tiene un esquema optimizado, de modo que no se realizarán conversiones entre tipos.

Hay una configuración especial use_structure_from_insertion_table_in_table_functions que controla este comportamiento. Tiene 3 valores posibles:

• 0 - la función de tabla extraerá la estructura de los datos.
• 1 - la función de tabla usará la estructura de la tabla de inserción.
• 2 - ClickHouse determinará automáticamente si es posible usar la estructura de la tabla de inserción o la inferencia de esquemas. Valor predeterminado.

Ejemplo 1:

Vamos a crear la tabla hobbies1 con la siguiente estructura:

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

Y, a continuación, inserte datos desde el archivo hobbies.jsonl:

INSERT INTO hobbies1 SELECT * FROM file(hobbies.jsonl)

En este caso, todas las columnas del archivo se insertan en la tabla sin cambios, por lo que ClickHouse usará la estructura de la tabla de inserción en lugar de realizar la inferencia de esquemas.

Ejemplo 2:

Vamos a crear la tabla hobbies2 con la siguiente estructura:

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

Y, a continuación, inserte datos desde el archivo hobbies.jsonl:

INSERT INTO hobbies2 SELECT id, age, hobbies FROM file(hobbies.jsonl)

En este caso, todas las columnas de la consulta SELECT están presentes en la tabla, por lo que ClickHouse usará la estructura de la tabla de inserción. Ten en cuenta que esto solo funcionará con formatos de entrada que admitan leer un subconjunto de columnas, como JSONEachRow, TSKV, Parquet, etc. (por ejemplo, no funcionará con el formato TSV).

Ejemplo 3:

Vamos a crear la tabla hobbies3 con la siguiente estructura:

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

Y, a continuación, inserte datos desde el archivo hobbies.jsonl:

INSERT INTO hobbies3 SELECT id, age, hobbies FROM file(hobbies.jsonl)

En este caso, la columna id se usa en la consulta SELECT, pero la tabla no tiene esa columna (tiene una columna llamada identifier), por lo que ClickHouse no puede usar la estructura de la tabla de inserción y se utilizará la inferencia de esquemas.

Ejemplo 4:

Vamos a crear la tabla hobbies4 con la siguiente estructura:

CREATE TABLE hobbies4
(
  `id` UInt64,
  `any_hobby` Nullable(String)
)
  ENGINE = MergeTree
ORDER BY id;

Y, a continuación, inserte datos desde el archivo hobbies.jsonl:

INSERT INTO hobbies4 SELECT id, empty(hobbies) ? NULL : hobbies[1] FROM file(hobbies.jsonl)

En este caso, se realizan algunas operaciones sobre la columna hobbies en la consulta SELECT para insertarla en la tabla, por lo que ClickHouse no puede usar la estructura de la tabla de inserción y se utilizará la inferencia de esquemas.

​

Caché de inferencia de esquemas

Para la mayoría de los formatos de entrada, la inferencia de esquemas lee algunos datos para determinar su estructura, y este proceso puede llevar algo de tiempo. Para evitar inferir el mismo esquema cada vez que ClickHouse lee datos del mismo archivo, el esquema inferido se almacena en caché y, cuando se vuelve a acceder a ese mismo archivo, ClickHouse usará el esquema de la caché.

Hay ajustes especiales que controlan esta caché:

• schema_inference_cache_max_elements_for_{file/s3/hdfs/url/azure} - el número máximo de esquemas almacenados en caché para la función de tabla correspondiente. El valor predeterminado es 4096. Estos ajustes deben establecerse en la configuración del servidor.
• schema_inference_use_cache_for_{file,s3,hdfs,url,azure} - permite activar o desactivar el uso de la caché para la inferencia de esquemas. Estos ajustes pueden usarse en consultas.

El esquema del archivo puede cambiar al modificar los datos o al cambiar los ajustes de formato. Por esta razón, la caché de inferencia de esquemas identifica el esquema según el origen del archivo, el nombre del formato, los ajustes de formato utilizados y la hora de la última modificación del archivo.

Nota: puede que algunos archivos a los que se accede mediante URL en la función de tabla url no contengan información sobre la hora de la última modificación; para ese caso, existe un ajuste especial schema_inference_cache_require_modification_time_for_url. Deshabilitar este ajuste permite usar el esquema de la caché sin la hora de la última modificación para esos archivos.

También existe una system table schema_inference_cache con todos los esquemas actualmente en caché y la consulta del sistema SYSTEM CLEAR SCHEMA CACHE [FOR File/S3/URL/HDFS] que permite limpiar la caché de esquemas para todas las fuentes o para una fuente específica.

Ejemplos:

Intentemos inferir la estructura de un conjunto de datos de ejemplo de S3 github-2022.ndjson.gz y veamos cómo funciona la caché de inferencia de esquemas:

DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/github/github-2022.ndjson.gz', NOSIGN)

┌─name───────┬─type─────────────────────────────────────────┐
│ type       │ Nullable(String)                             │
│ actor      │ Tuple(                                      ↴│
│            │↳    avatar_url Nullable(String),            ↴│
│            │↳    display_login Nullable(String),         ↴│
│            │↳    id Nullable(Int64),                     ↴│
│            │↳    login Nullable(String),                 ↴│
│            │↳    url Nullable(String))                    │
│ repo       │ Tuple(                                      ↴│
│            │↳    id Nullable(Int64),                     ↴│
│            │↳    name Nullable(String),                  ↴│
│            │↳    url Nullable(String))                    │
│ created_at │ Nullable(String)                             │
│ payload    │ Tuple(                                      ↴│
│            │↳    action Nullable(String),                ↴│
│            │↳    distinct_size Nullable(Int64),          ↴│
│            │↳    pull_request Tuple(                     ↴│
│            │↳        author_association Nullable(String),↴│
│            │↳        base Tuple(                         ↴│
│            │↳            ref Nullable(String),           ↴│
│            │↳            sha Nullable(String)),          ↴│
│            │↳        head Tuple(                         ↴│
│            │↳            ref Nullable(String),           ↴│
│            │↳            sha Nullable(String)),          ↴│
│            │↳        number Nullable(Int64),             ↴│
│            │↳        state Nullable(String),             ↴│
│            │↳        title Nullable(String),             ↴│
│            │↳        updated_at Nullable(String),        ↴│
│            │↳        user Tuple(                         ↴│
│            │↳            login Nullable(String))),       ↴│
│            │↳    ref Nullable(String),                   ↴│
│            │↳    ref_type Nullable(String),              ↴│
│            │↳    size Nullable(Int64))                    │
└────────────┴──────────────────────────────────────────────┘
5 rows in set. Elapsed: 0.601 sec.

DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/github/github-2022.ndjson.gz', NOSIGN)

┌─name───────┬─type─────────────────────────────────────────┐
│ type       │ Nullable(String)                             │
│ actor      │ Tuple(                                      ↴│
│            │↳    avatar_url Nullable(String),            ↴│
│            │↳    display_login Nullable(String),         ↴│
│            │↳    id Nullable(Int64),                     ↴│
│            │↳    login Nullable(String),                 ↴│
│            │↳    url Nullable(String))                    │
│ repo       │ Tuple(                                      ↴│
│            │↳    id Nullable(Int64),                     ↴│
│            │↳    name Nullable(String),                  ↴│
│            │↳    url Nullable(String))                    │
│ created_at │ Nullable(String)                             │
│ payload    │ Tuple(                                      ↴│
│            │↳    action Nullable(String),                ↴│
│            │↳    distinct_size Nullable(Int64),          ↴│
│            │↳    pull_request Tuple(                     ↴│
│            │↳        author_association Nullable(String),↴│
│            │↳        base Tuple(                         ↴│
│            │↳            ref Nullable(String),           ↴│
│            │↳            sha Nullable(String)),          ↴│
│            │↳        head Tuple(                         ↴│
│            │↳            ref Nullable(String),           ↴│
│            │↳            sha Nullable(String)),          ↴│
│            │↳        number Nullable(Int64),             ↴│
│            │↳        state Nullable(String),             ↴│
│            │↳        title Nullable(String),             ↴│
│            │↳        updated_at Nullable(String),        ↴│
│            │↳        user Tuple(                         ↴│
│            │↳            login Nullable(String))),       ↴│
│            │↳    ref Nullable(String),                   ↴│
│            │↳    ref_type Nullable(String),              ↴│
│            │↳    size Nullable(Int64))                    │
└────────────┴──────────────────────────────────────────────┘

5 rows in set. Elapsed: 0.059 sec.

Como puede ver, la segunda consulta se completó casi al instante.

Probemos a cambiar algunos ajustes que pueden afectar al esquema inferido:

DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/github/github-2022.ndjson.gz', NOSIGN)
SETTINGS input_format_json_try_infer_named_tuples_from_objects=0, input_format_json_read_objects_as_strings = 1

┌─name───────┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ type       │ Nullable(String) │              │                    │         │                  │                │
│ actor      │ Nullable(String) │              │                    │         │                  │                │
│ repo       │ Nullable(String) │              │                    │         │                  │                │
│ created_at │ Nullable(String) │              │                    │         │                  │                │
│ payload    │ Nullable(String) │              │                    │         │                  │                │
└────────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

5 rows in set. Elapsed: 0.611 sec

Como puede ver, no se utilizó el esquema de la caché para el mismo archivo, porque se cambió la configuración que puede afectar al esquema inferido.

Veamos el contenido de la tabla system.schema_inference_cache:

SELECT schema, format, source FROM system.schema_inference_cache WHERE storage='S3'

┌─schema──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─format─┬─source───────────────────────────────────────────────────────────────────────────────────────────────────┐
│ type Nullable(String), actor Tuple(avatar_url Nullable(String), display_login Nullable(String), id Nullable(Int64), login Nullable(String), url Nullable(String)), repo Tuple(id Nullable(Int64), name Nullable(String), url Nullable(String)), created_at Nullable(String), payload Tuple(action Nullable(String), distinct_size Nullable(Int64), pull_request Tuple(author_association Nullable(String), base Tuple(ref Nullable(String), sha Nullable(String)), head Tuple(ref Nullable(String), sha Nullable(String)), number Nullable(Int64), state Nullable(String), title Nullable(String), updated_at Nullable(String), user Tuple(login Nullable(String))), ref Nullable(String), ref_type Nullable(String), size Nullable(Int64)) │ NDJSON │ datasets-documentation.s3.eu-west-3.amazonaws.com443/datasets-documentation/github/github-2022.ndjson.gz │
│ type Nullable(String), actor Nullable(String), repo Nullable(String), created_at Nullable(String), payload Nullable(String)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 │ NDJSON │ datasets-documentation.s3.eu-west-3.amazonaws.com443/datasets-documentation/github/github-2022.ndjson.gz │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Como puede ver, hay dos esquemas diferentes para el mismo archivo.

Podemos limpiar la caché de esquemas mediante una consulta del sistema:

SYSTEM CLEAR SCHEMA CACHE FOR S3

Ok.

SELECT count() FROM system.schema_inference_cache WHERE storage='S3'

┌─count()─┐
│       0 │
└─────────┘

​

Formatos de texto

En los formatos de texto, ClickHouse lee los datos fila por fila, extrae los valores de las columnas según el formato y luego utiliza algunos analizadores sintácticos recursivos y heurísticas para determinar el tipo de cada valor. El número máximo de filas y bytes leídos de los datos durante la inferencia de esquemas se controla con los ajustes input_format_max_rows_to_read_for_schema_inference (25000 de forma predeterminada) y input_format_max_bytes_to_read_for_schema_inference (32Mb de forma predeterminada). De forma predeterminada, todos los tipos inferidos son Nullable, pero puedes cambiarlo configurando schema_inference_make_columns_nullable (consulta ejemplos en la sección de ajustes).

​

Formatos JSON

En los formatos JSON, ClickHouse analiza los valores según la especificación JSON y luego intenta determinar el tipo de dato más adecuado para cada uno.

Veamos cómo funciona, qué tipos se pueden inferir y qué configuraciones específicas se pueden utilizar en los formatos JSON.

Ejemplos

En este y los siguientes ejemplos se utilizará la función de tabla format.

Integers, Floats, Bools, Strings:

DESC format(JSONEachRow, '{"int" : 42, "float" : 42.42, "string" : "Hello, World!"}');

┌─name───┬─type──────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ int    │ Nullable(Int64)   │              │                    │         │                  │                │
│ float  │ Nullable(Float64) │              │                    │         │                  │                │
│ bool   │ Nullable(Bool)    │              │                    │         │                  │                │
│ string │ Nullable(String)  │              │                    │         │                  │                │
└────────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Fechas, DateTimes:

DESC format(JSONEachRow, '{"date" : "2022-01-01", "datetime" : "2022-01-01 00:00:00", "datetime64" : "2022-01-01 00:00:00.000"}')

┌─name───────┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ date       │ Nullable(Date)          │              │                    │         │                  │                │
│ datetime   │ Nullable(DateTime)      │              │                    │         │                  │                │
│ datetime64 │ Nullable(DateTime64(9)) │              │                    │         │                  │                │
└────────────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Arrays:

DESC format(JSONEachRow, '{"arr" : [1, 2, 3], "nested_arrays" : [[1, 2, 3], [4, 5, 6], []]}')

┌─name──────────┬─type──────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ arr           │ Array(Nullable(Int64))        │              │                    │         │                  │                │
│ nested_arrays │ Array(Array(Nullable(Int64))) │              │                    │         │                  │                │
└───────────────┴───────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Si un array contiene null, ClickHouse utilizará los tipos de los demás elementos del array:

DESC format(JSONEachRow, '{"arr" : [null, 42, null]}')

┌─name─┬─type───────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ arr  │ Array(Nullable(Int64)) │              │                    │         │                  │                │
└──────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Si un array contiene valores de distintos tipos y el parámetro input_format_json_infer_array_of_dynamic_from_array_of_different_types está habilitado (habilitado por defecto), tendrá el tipo Array(Dynamic):

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

┌─name─┬─type───────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ arr  │ Array(Dynamic) │              │                    │         │                  │                │
└──────┴────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Tuplas con nombre:

Cuando está habilitada la configuración input_format_json_try_infer_named_tuples_from_objects, durante la inferencia de esquemas ClickHouse intentará inferir un Tuple con nombre a partir de objetos JSON. El Tuple con nombre resultante contendrá todos los elementos de todos los objetos JSON correspondientes presentes en los datos de muestra.

SET input_format_json_try_infer_named_tuples_from_objects = 1;
DESC format(JSONEachRow, '{"obj" : {"a" : 42, "b" : "Hello"}}, {"obj" : {"a" : 43, "c" : [1, 2, 3]}}, {"obj" : {"d" : {"e" : 42}}}')

┌─name─┬─type───────────────────────────────────────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ obj  │ Tuple(a Nullable(Int64), b Nullable(String), c Array(Nullable(Int64)), d Tuple(e Nullable(Int64))) │              │                    │         │                  │                │
└──────┴────────────────────────────────────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Tuples sin nombre:

Si la configuración input_format_json_infer_array_of_dynamic_from_array_of_different_types está desactivada, consideramos los Arrays con elementos de distintos tipos como Tuples sin nombre en los formatos JSON.

SET input_format_json_infer_array_of_dynamic_from_array_of_different_types = 0;
DESC format(JSONEachRow, '{"tuple" : [1, "Hello, World!", [1, 2, 3]]}')

┌─name──┬─type─────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ tuple │ Tuple(Nullable(Int64), Nullable(String), Array(Nullable(Int64))) │              │                    │         │                  │                │
└───────┴──────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Si algunos valores son null o están vacíos, usamos los tipos de los valores correspondientes de las demás filas:

SET input_format_json_infer_array_of_dynamic_from_array_of_different_types=0;
DESC format(JSONEachRow, $$
                              {"tuple" : [1, null, null]}
                              {"tuple" : [null, "Hello, World!", []]}
                              {"tuple" : [null, null, [1, 2, 3]]}
                         $$)

┌─name──┬─type─────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ tuple │ Tuple(Nullable(Int64), Nullable(String), Array(Nullable(Int64))) │              │                    │         │                  │                │
└───────┴──────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Maps:

En JSON, podemos leer objetos cuyos valores son del mismo tipo que Map. Nota: solo funcionará cuando las opciones input_format_json_read_objects_as_strings e input_format_json_try_infer_named_tuples_from_objects estén deshabilitadas.

SET input_format_json_read_objects_as_strings = 0, input_format_json_try_infer_named_tuples_from_objects = 0;
DESC format(JSONEachRow, '{"map" : {"key1" : 42, "key2" : 24, "key3" : 4}}')

┌─name─┬─type─────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ map  │ Map(String, Nullable(Int64)) │              │                    │         │                  │                │
└──────┴──────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Tipos complejos anidados:

DESC format(JSONEachRow, '{"value" : [[[42, 24], []], {"key1" : 42, "key2" : 24}]}')

┌─name──┬─type─────────────────────────────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ value │ Tuple(Array(Array(Nullable(String))), Tuple(key1 Nullable(Int64), key2 Nullable(Int64))) │              │                    │         │                  │                │
└───────┴──────────────────────────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Si ClickHouse no puede determinar el tipo de alguna clave porque los datos contienen solo valores nulos/objetos vacíos/arreglos vacíos, se usará el tipo String si la configuración input_format_json_infer_incomplete_types_as_strings está habilitada; de lo contrario, se generará una excepción:

DESC format(JSONEachRow, '{"arr" : [null, null]}') SETTINGS input_format_json_infer_incomplete_types_as_strings = 1;

┌─name─┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ arr  │ Array(Nullable(String)) │              │                    │         │                  │                │
└──────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

DESC format(JSONEachRow, '{"arr" : [null, null]}') SETTINGS input_format_json_infer_incomplete_types_as_strings = 0;

Code: 652. DB::Exception: Received from localhost:9000. DB::Exception:
Cannot determine type for column 'arr' by first 1 rows of data,
most likely this column contains only Nulls or empty Arrays/Maps.
...

​

Ajustes de JSON

input_format_json_try_infer_numbers_from_strings

Al habilitar esta configuración, se pueden inferir números a partir de valores de texto.

Esta configuración está deshabilitada de forma predeterminada.

Ejemplo:

SET input_format_json_try_infer_numbers_from_strings = 1;
DESC format(JSONEachRow, $$
                              {"value" : "42"}
                              {"value" : "424242424242"}
                         $$)

┌─name──┬─type────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ value │ Nullable(Int64) │              │                    │         │                  │                │
└───────┴─────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

input_format_json_try_infer_named_tuples_from_objects

Al activar esta configuración, se pueden inferir Tuples con nombre a partir de objetos JSON. La Tuple con nombre resultante contendrá todos los elementos de todos los objetos JSON correspondientes presentes en los datos de muestra. Puede resultar útil cuando los datos JSON no son dispersos y la muestra de datos contiene todas las claves de objeto posibles.

Esta configuración está habilitada de forma predeterminada.

Ejemplo

Query

SET input_format_json_try_infer_named_tuples_from_objects = 1;
DESC format(JSONEachRow, '{"obj" : {"a" : 42, "b" : "Hello"}}, {"obj" : {"a" : 43, "c" : [1, 2, 3]}}, {"obj" : {"d" : {"e" : 42}}}')

Response

┌─name─┬─type───────────────────────────────────────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ obj  │ Tuple(a Nullable(Int64), b Nullable(String), c Array(Nullable(Int64)), d Tuple(e Nullable(Int64))) │              │                    │         │                  │                │
└──────┴────────────────────────────────────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Query

SET input_format_json_try_infer_named_tuples_from_objects = 1;
DESC format(JSONEachRow, '{"array" : [{"a" : 42, "b" : "Hello"}, {}, {"c" : [1,2,3]}, {"d" : "2020-01-01"}]}')

Response

┌─name──┬─type────────────────────────────────────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ array │ Array(Tuple(a Nullable(Int64), b Nullable(String), c Array(Nullable(Int64)), d Nullable(Date))) │              │                    │         │                  │                │
└───────┴─────────────────────────────────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

input_format_json_use_string_type_for_ambiguous_paths_in_named_tuples_inference_from_objects

Al habilitar esta configuración, se puede usar el tipo String para rutas ambiguas durante la inferencia de Tuples con nombre a partir de objetos JSON (cuando input_format_json_try_infer_named_tuples_from_objects está habilitado) en lugar de generar una excepción. Permite leer objetos JSON como Tuples con nombre incluso cuando hay rutas ambiguas.

Deshabilitada de forma predeterminada.

Ejemplos

Con la configuración deshabilitada:

Query

SET input_format_json_try_infer_named_tuples_from_objects = 1;
SET input_format_json_use_string_type_for_ambiguous_paths_in_named_tuples_inference_from_objects = 0;
DESC format(JSONEachRow, '{"obj" : {"a" : 42}}, {"obj" : {"a" : {"b" : "Hello"}}}');

Response

Code: 636. DB::Exception: The table structure cannot be extracted from a JSONEachRow format file. Error:
Code: 117. DB::Exception: JSON objects have ambiguous data: in some objects path 'a' has type 'Int64' and in some - 'Tuple(b String)'. You can enable setting input_format_json_use_string_type_for_ambiguous_paths_in_named_tuples_inference_from_objects to use String type for path 'a'. (INCORRECT_DATA) (version 24.3.1.1).
You can specify the structure manually. (CANNOT_EXTRACT_TABLE_STRUCTURE)

Con el ajuste habilitado:

Query

SET input_format_json_try_infer_named_tuples_from_objects = 1;
SET input_format_json_use_string_type_for_ambiguous_paths_in_named_tuples_inference_from_objects = 1;
DESC format(JSONEachRow, '{"obj" : "a" : 42}, {"obj" : {"a" : {"b" : "Hello"}}}');
SELECT * FROM format(JSONEachRow, '{"obj" : {"a" : 42}}, {"obj" : {"a" : {"b" : "Hello"}}}');

Response

┌─name─┬─type──────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ obj  │ Tuple(a Nullable(String))     │              │                    │         │                  │                │
└──────┴───────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
┌─obj─────────────────┐
│ ('42')              │
│ ('{"b" : "Hello"}') │
└─────────────────────┘

input_format_json_read_objects_as_strings

Al habilitar esta configuración, se pueden leer objetos JSON anidados como cadenas. Esta configuración se puede usar para leer objetos JSON anidados sin usar el tipo JSON object.

Esta configuración está habilitada de forma predeterminada.

Nota: esta configuración solo surtirá efecto si la configuración input_format_json_try_infer_named_tuples_from_objects está deshabilitada.

SET input_format_json_read_objects_as_strings = 1, input_format_json_try_infer_named_tuples_from_objects = 0;
DESC format(JSONEachRow, $$
                             {"obj" : {"key1" : 42, "key2" : [1,2,3,4]}}
                             {"obj" : {"key3" : {"nested_key" : 1}}}
                         $$)

┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ obj  │ Nullable(String) │              │                    │         │                  │                │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

input_format_json_read_numbers_as_strings

Al habilitar esta configuración, se pueden leer valores numéricos como cadenas.

Esta configuración está habilitada de forma predeterminada.

Ejemplo

SET input_format_json_read_numbers_as_strings = 1;
DESC format(JSONEachRow, $$
                                {"value" : 1055}
                                {"value" : "unknown"}
                         $$)

┌─name──┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ value │ Nullable(String) │              │                    │         │                  │                │
└───────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

input_format_json_read_bools_as_numbers

Al habilitar esta configuración, se pueden leer valores Bool como números.

Esta configuración está habilitada de forma predeterminada.

Ejemplo:

SET input_format_json_read_bools_as_numbers = 1;
DESC format(JSONEachRow, $$
                                {"value" : true}
                                {"value" : 42}
                         $$)

┌─name──┬─type────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ value │ Nullable(Int64) │              │                    │         │                  │                │
└───────┴─────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

input_format_json_read_bools_as_strings

Al habilitar esta configuración, se pueden leer valores Bool como cadenas.

Esta configuración está habilitada de forma predeterminada.

Ejemplo:

SET input_format_json_read_bools_as_strings = 1;
DESC format(JSONEachRow, $$
                                {"value" : true}
                                {"value" : "Hello, World"}
                         $$)

┌─name──┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ value │ Nullable(String) │              │                    │         │                  │                │
└───────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

input_format_json_read_arrays_as_strings

Al habilitar esta configuración, se pueden leer los valores de los arrays JSON como cadenas.

Esta configuración está habilitada de forma predeterminada.

Ejemplo

SET input_format_json_read_arrays_as_strings = 1;
SELECT arr, toTypeName(arr), JSONExtractArrayRaw(arr)[3] from format(JSONEachRow, 'arr String', '{"arr" : [1, "Hello", [1,2,3]]}');

┌─arr───────────────────┬─toTypeName(arr)─┬─arrayElement(JSONExtractArrayRaw(arr), 3)─┐
│ [1, "Hello", [1,2,3]] │ String          │ [1,2,3]                                   │
└───────────────────────┴─────────────────┴───────────────────────────────────────────┘

input_format_json_infer_incomplete_types_as_strings

Al habilitar esta configuración, se puede usar el tipo String para las claves JSON que contienen solo Null/{}/[] en la muestra de datos durante la inferencia de esquemas. En los formatos JSON, cualquier valor puede leerse como String si todas las configuraciones correspondientes están habilitadas (todas lo están de forma predeterminada), y así se evitan errores como Cannot determine type for column 'column_name' by first 25000 rows of data, most likely this column contains only Nulls or empty Arrays/Maps durante la inferencia de esquemas al usar el tipo String para claves con tipos desconocidos.

Ejemplo:

Query

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" : []}}');

Response

┌─name─┬─type───────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ obj  │ Tuple(a Array(Nullable(Int64)), b Nullable(String), c Nullable(String), d Nullable(String), e Array(Nullable(String))) │              │                    │         │                  │                │
└──────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

┌─obj────────────────────────────┐
│ ([1,2,3],'hello',NULL,'{}',[]) │
└────────────────────────────────┘

​

CSV

En formato CSV, ClickHouse extrae los valores de las columnas de la fila según los delimitadores. ClickHouse espera que todos los tipos, excepto números y cadenas, estén entre comillas dobles. Si el valor está entre comillas dobles, ClickHouse intenta analizar los datos dentro de las comillas usando el analizador recursivo y luego intenta encontrar el tipo de dato más adecuado para ellos. Si el valor no está entre comillas dobles, ClickHouse intenta analizarlo como un número; si el valor no es un número, ClickHouse lo trata como una cadena.

Si no quiere que ClickHouse intente determinar tipos complejos usando algunos analizadores y heurísticas, puede deshabilitar la configuración input_format_csv_use_best_effort_in_schema_inference y ClickHouse tratará todas las columnas como Strings.

Si la configuración input_format_csv_detect_header está habilitada, ClickHouse intentará detectar el encabezado con los nombres de las columnas (y, posiblemente, los tipos) mientras infiere el esquema. Esta configuración está habilitada de forma predeterminada.

Ejemplos:

Enteros, flotantes, booleanos, cadenas:

DESC format(CSV, '42,42.42,true,"Hello,World!"')

┌─name─┬─type──────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(Int64)   │              │                    │         │                  │                │
│ c2   │ Nullable(Float64) │              │                    │         │                  │                │
│ c3   │ Nullable(Bool)    │              │                    │         │                  │                │
│ c4   │ Nullable(String)  │              │                    │         │                  │                │
└──────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Cadenas sin comillas:

DESC format(CSV, 'Hello world!,World hello!')

┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(String) │              │                    │         │                  │                │
│ c2   │ Nullable(String) │              │                    │         │                  │                │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Fechas, DateTimes:

DESC format(CSV, '"2020-01-01","2020-01-01 00:00:00","2022-01-01 00:00:00.000"')

┌─name─┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(Date)          │              │                    │         │                  │                │
│ c2   │ Nullable(DateTime)      │              │                    │         │                  │                │
│ c3   │ Nullable(DateTime64(9)) │              │                    │         │                  │                │
└──────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Arrays:

DESC format(CSV, '"[1,2,3]","[[1, 2], [], [3, 4]]"')

┌─name─┬─type──────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Array(Nullable(Int64))        │              │                    │         │                  │                │
│ c2   │ Array(Array(Nullable(Int64))) │              │                    │         │                  │                │
└──────┴───────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

DESC format(CSV, $$"['Hello', 'world']","[['Abc', 'Def'], []]"$$)

┌─name─┬─type───────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Array(Nullable(String))        │              │                    │         │                  │                │
│ c2   │ Array(Array(Nullable(String))) │              │                    │         │                  │                │
└──────┴────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Si un array contiene NULL, ClickHouse usará los tipos de los demás elementos del array:

DESC format(CSV, '"[NULL, 42, NULL]"')

┌─name─┬─type───────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Array(Nullable(Int64)) │              │                    │         │                  │                │
└──────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Maps:

DESC format(CSV, $$"{'key1' : 42, 'key2' : 24}"$$)

┌─name─┬─type─────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Map(String, Nullable(Int64)) │              │                    │         │                  │                │
└──────┴──────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Arrays y Maps anidados:

DESC format(CSV, $$"[{'key1' : [[42, 42], []], 'key2' : [[null], [42]]}]"$$)

┌─name─┬─type──────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Array(Map(String, Array(Array(Nullable(Int64))))) │              │                    │         │                  │                │
└──────┴───────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Si ClickHouse no puede determinar el tipo entre comillas porque los datos solo contienen valores nulos, ClickHouse lo tratará como String:

DESC format(CSV, '"[NULL, NULL]"')

┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(String) │              │                    │         │                  │                │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Ejemplo con la opción input_format_csv_use_best_effort_in_schema_inference deshabilitada:

SET input_format_csv_use_best_effort_in_schema_inference = 0
DESC format(CSV, '"[1,2,3]",42.42,Hello World!')

┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(String) │              │                    │         │                  │                │
│ c2   │ Nullable(String) │              │                    │         │                  │                │
│ c3   │ Nullable(String) │              │                    │         │                  │                │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Ejemplos de detección automática del encabezado (cuando input_format_csv_detect_header está habilitada):

Solo nombres:

SELECT * FROM format(CSV,
$$"number","string","array"
42,"Hello","[1, 2, 3]"
43,"World","[4, 5, 6]"
$$)

┌─number─┬─string─┬─array───┐
│     42 │ Hello  │ [1,2,3] │
│     43 │ World  │ [4,5,6] │
└────────┴────────┴─────────┘

Nombres y tipos:

DESC format(CSV,
$$"number","string","array"
"UInt32","String","Array(UInt16)"
42,"Hello","[1, 2, 3]"
43,"World","[4, 5, 6]"
$$)

┌─name───┬─type──────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ number │ UInt32        │              │                    │         │                  │                │
│ string │ String        │              │                    │         │                  │                │
│ array  │ Array(UInt16) │              │                    │         │                  │                │
└────────┴───────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Ten en cuenta que el encabezado solo puede detectarse si hay al menos una columna con un tipo distinto de String. Si todas las columnas son de tipo String, el encabezado no se detecta:

SELECT * FROM format(CSV,
$$"first_column","second_column"
"Hello","World"
"World","Hello"
$$)

┌─c1───────────┬─c2────────────┐
│ first_column │ second_column │
│ Hello        │ World         │
│ World        │ Hello         │
└──────────────┴───────────────┘

​

Configuración de CSV

input_format_csv_try_infer_numbers_from_strings

Al habilitar esta configuración, se pueden inferir números a partir de valores de tipo cadena.

Esta configuración está deshabilitada de forma predeterminada.

Ejemplo:

SET input_format_json_try_infer_numbers_from_strings = 1;
DESC format(CSV, '42,42.42');

┌─name─┬─type──────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(Int64)   │              │                    │         │                  │                │
│ c2   │ Nullable(Float64) │              │                    │         │                  │                │
└──────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

​

TSV/TSKV

En los formatos TSV/TSKV, ClickHouse extrae de la fila el valor de la columna utilizando delimitadores tabulares y luego analiza el valor extraído con el analizador recursivo para determinar el tipo más apropiado. Si no se puede determinar el tipo, ClickHouse trata este valor como String.

Si no desea que ClickHouse intente determinar tipos complejos mediante algunos analizadores y heurísticas, puede deshabilitar la configuración input_format_tsv_use_best_effort_in_schema_inference y ClickHouse tratará todas las columnas como Strings.

Si la configuración input_format_tsv_detect_header está habilitada, ClickHouse intentará detectar el encabezado con nombres de columnas (y, posiblemente, tipos) al inferir el esquema. Esta configuración está habilitada de forma predeterminada.

Ejemplos:

Enteros, flotantes, booleanos, cadenas:

DESC format(TSV, '42    42.42    true    Hello,World!')

┌─name─┬─type──────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(Int64)   │              │                    │         │                  │                │
│ c2   │ Nullable(Float64) │              │                    │         │                  │                │
│ c3   │ Nullable(Bool)    │              │                    │         │                  │                │
│ c4   │ Nullable(String)  │              │                    │         │                  │                │
└──────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

DESC format(TSKV, 'int=42    float=42.42    bool=true    string=Hello,World!\n')

┌─name───┬─type──────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ int    │ Nullable(Int64)   │              │                    │         │                  │                │
│ float  │ Nullable(Float64) │              │                    │         │                  │                │
│ bool   │ Nullable(Bool)    │              │                    │         │                  │                │
│ string │ Nullable(String)  │              │                    │         │                  │                │
└────────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Fechas, DateTimes:

DESC format(TSV, '2020-01-01    2020-01-01 00:00:00    2022-01-01 00:00:00.000')

┌─name─┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(Date)          │              │                    │         │                  │                │
│ c2   │ Nullable(DateTime)      │              │                    │         │                  │                │
│ c3   │ Nullable(DateTime64(9)) │              │                    │         │                  │                │
└──────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Arrays:

DESC format(TSV, '[1,2,3]    [[1, 2], [], [3, 4]]')

┌─name─┬─type──────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Array(Nullable(Int64))        │              │                    │         │                  │                │
│ c2   │ Array(Array(Nullable(Int64))) │              │                    │         │                  │                │
└──────┴───────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

DESC format(TSV, '[''Hello'', ''world'']    [[''Abc'', ''Def''], []]')

┌─name─┬─type───────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Array(Nullable(String))        │              │                    │         │                  │                │
│ c2   │ Array(Array(Nullable(String))) │              │                    │         │                  │                │
└──────┴────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Si un array contiene NULL, ClickHouse usará los tipos de los demás elementos del array:

DESC format(TSV, '[NULL, 42, NULL]')

┌─name─┬─type───────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Array(Nullable(Int64)) │              │                    │         │                  │                │
└──────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Tuplas:

DESC format(TSV, $$(42, 'Hello, world!')$$)

┌─name─┬─type─────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Tuple(Nullable(Int64), Nullable(String)) │              │                    │         │                  │                │
└──────┴──────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Maps:

DESC format(TSV, $${'key1' : 42, 'key2' : 24}$$)

┌─name─┬─type─────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Map(String, Nullable(Int64)) │              │                    │         │                  │                │
└──────┴──────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Arrays, Tuples y Maps anidados:

DESC format(TSV, $$[{'key1' : [(42, 'Hello'), (24, NULL)], 'key2' : [(NULL, ','), (42, 'world!')]}]$$)

┌─name─┬─type────────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Array(Map(String, Array(Tuple(Nullable(Int64), Nullable(String))))) │              │                    │         │                  │                │
└──────┴─────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Si ClickHouse no puede determinar el tipo porque los datos solo contienen valores NULL, los tratará como String:

DESC format(TSV, '[NULL, NULL]')

┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(String) │              │                    │         │                  │                │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Ejemplo con la configuración input_format_tsv_use_best_effort_in_schema_inference deshabilitada:

SET input_format_tsv_use_best_effort_in_schema_inference = 0
DESC format(TSV, '[1,2,3]    42.42    Hello World!')

┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(String) │              │                    │         │                  │                │
│ c2   │ Nullable(String) │              │                    │         │                  │                │
│ c3   │ Nullable(String) │              │                    │         │                  │                │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Ejemplos de detección automática del encabezado (cuando input_format_tsv_detect_header está habilitado):

Solo nombres:

SELECT * FROM format(TSV,
$$number    string    array
42    Hello    [1, 2, 3]
43    World    [4, 5, 6]
$$);

┌─number─┬─string─┬─array───┐
│     42 │ Hello  │ [1,2,3] │
│     43 │ World  │ [4,5,6] │
└────────┴────────┴─────────┘

Nombres y tipos:

DESC format(TSV,
$$number    string    array
UInt32    String    Array(UInt16)
42    Hello    [1, 2, 3]
43    World    [4, 5, 6]
$$)

┌─name───┬─type──────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ number │ UInt32        │              │                    │         │                  │                │
│ string │ String        │              │                    │         │                  │                │
│ array  │ Array(UInt16) │              │                    │         │                  │                │
└────────┴───────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Tenga en cuenta que el encabezado solo puede detectarse si hay al menos una columna con un tipo distinto de String. Si todas las columnas son de tipo String, el encabezado no se detecta:

SELECT * FROM format(TSV,
$$first_column    second_column
Hello    World
World    Hello
$$)

┌─c1───────────┬─c2────────────┐
│ first_column │ second_column │
│ Hello        │ World         │
│ World        │ Hello         │
└──────────────┴───────────────┘

​

Valores

En el formato Values, ClickHouse extrae el valor de la columna de la fila y luego lo analiza con el analizador recursivo, de forma similar a como se analizan los literales.

Ejemplos:

Enteros, Flotantes, Bools, Strings:

DESC format(Values, $$(42, 42.42, true, 'Hello,World!')$$)

┌─name─┬─type──────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(Int64)   │              │                    │         │                  │                │
│ c2   │ Nullable(Float64) │              │                    │         │                  │                │
│ c3   │ Nullable(Bool)    │              │                    │         │                  │                │
│ c4   │ Nullable(String)  │              │                    │         │                  │                │
└──────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Fechas, DateTimes:

DESC format(Values, $$('2020-01-01', '2020-01-01 00:00:00', '2022-01-01 00:00:00.000')$$)

┌─name─┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(Date)          │              │                    │         │                  │                │
│ c2   │ Nullable(DateTime)      │              │                    │         │                  │                │
│ c3   │ Nullable(DateTime64(9)) │              │                    │         │                  │                │
└──────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Arrays:

DESC format(Values, '([1,2,3], [[1, 2], [], [3, 4]])')

┌─name─┬─type──────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Array(Nullable(Int64))        │              │                    │         │                  │                │
│ c2   │ Array(Array(Nullable(Int64))) │              │                    │         │                  │                │
└──────┴───────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Si un array contiene null, ClickHouse utilizará los tipos de los demás elementos del array:

DESC format(Values, '([NULL, 42, NULL])')

┌─name─┬─type───────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Array(Nullable(Int64)) │              │                    │         │                  │                │
└──────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Tuplas:

DESC format(Values, $$((42, 'Hello, world!'))$$)

┌─name─┬─type─────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Tuple(Nullable(Int64), Nullable(String)) │              │                    │         │                  │                │
└──────┴──────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Maps:

DESC format(Values, $$({'key1' : 42, 'key2' : 24})$$)

┌─name─┬─type─────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Map(String, Nullable(Int64)) │              │                    │         │                  │                │
└──────┴──────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Arrays, Tuples y Maps anidados:

DESC format(Values, $$([{'key1' : [(42, 'Hello'), (24, NULL)], 'key2' : [(NULL, ','), (42, 'world!')]}])$$)

┌─name─┬─type────────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Array(Map(String, Array(Tuple(Nullable(Int64), Nullable(String))))) │              │                    │         │                  │                │
└──────┴─────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Si ClickHouse no puede determinar el tipo porque los datos solo contienen valores nulos, se lanzará una excepción:

DESC format(Values, '([NULL, NULL])')

Code: 652. DB::Exception: Received from localhost:9000. DB::Exception:
Cannot determine type for column 'c1' by first 1 rows of data,
most likely this column contains only Nulls or empty Arrays/Maps.
...

Ejemplo con el ajuste input_format_tsv_use_best_effort_in_schema_inference desactivado:

SET input_format_tsv_use_best_effort_in_schema_inference = 0
DESC format(TSV, '[1,2,3]    42.42    Hello World!')

┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(String) │              │                    │         │                  │                │
│ c2   │ Nullable(String) │              │                    │         │                  │                │
│ c3   │ Nullable(String) │              │                    │         │                  │                │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

​

CustomSeparated

En el formato CustomSeparated, ClickHouse primero extrae todos los valores de columna de la fila según los delimitadores especificados y luego intenta inferir el tipo de dato de cada valor según la regla de escape.

Si la configuración input_format_custom_detect_header está habilitada, ClickHouse intentará detectar el encabezado con los nombres de las columnas (y quizá los tipos) mientras infiere el esquema. Esta configuración está habilitada de forma predeterminada.

Ejemplo

SET format_custom_row_before_delimiter = '<row_before_delimiter>',
       format_custom_row_after_delimiter = '<row_after_delimiter>\n',
       format_custom_row_between_delimiter = '<row_between_delimiter>\n',
       format_custom_result_before_delimiter = '<result_before_delimiter>\n',
       format_custom_result_after_delimiter = '<result_after_delimiter>\n',
       format_custom_field_delimiter = '<field_delimiter>',
       format_custom_escaping_rule = 'Quoted'

DESC format(CustomSeparated, $$<result_before_delimiter>
<row_before_delimiter>42.42<field_delimiter>'Some string 1'<field_delimiter>[1, NULL, 3]<row_after_delimiter>
<row_between_delimiter>
<row_before_delimiter>NULL<field_delimiter>'Some string 3'<field_delimiter>[1, 2, NULL]<row_after_delimiter>
<result_after_delimiter>
$$)

┌─name─┬─type───────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(Float64)      │              │                    │         │                  │                │
│ c2   │ Nullable(String)       │              │                    │         │                  │                │
│ c3   │ Array(Nullable(Int64)) │              │                    │         │                  │                │
└──────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Ejemplo de detección automática del encabezado (cuando input_format_custom_detect_header está habilitada):

SET format_custom_row_before_delimiter = '<row_before_delimiter>',
       format_custom_row_after_delimiter = '<row_after_delimiter>\n',
       format_custom_row_between_delimiter = '<row_between_delimiter>\n',
       format_custom_result_before_delimiter = '<result_before_delimiter>\n',
       format_custom_result_after_delimiter = '<result_after_delimiter>\n',
       format_custom_field_delimiter = '<field_delimiter>',
       format_custom_escaping_rule = 'Quoted'

DESC format(CustomSeparated, $$<result_before_delimiter>
<row_before_delimiter>'number'<field_delimiter>'string'<field_delimiter>'array'<row_after_delimiter>
<row_between_delimiter>
<row_before_delimiter>42.42<field_delimiter>'Some string 1'<field_delimiter>[1, NULL, 3]<row_after_delimiter>
<row_between_delimiter>
<row_before_delimiter>NULL<field_delimiter>'Some string 3'<field_delimiter>[1, 2, NULL]<row_after_delimiter>
<result_after_delimiter>
$$)

┌─number─┬─string────────┬─array──────┐
│  42.42 │ Some string 1 │ [1,NULL,3] │
│   ᴺᵁᴸᴸ │ Some string 3 │ [1,2,NULL] │
└────────┴───────────────┴────────────┘

​

Template

En el formato Template, ClickHouse primero extrae todos los valores de columna de la fila según la plantilla especificada y luego intenta inferir el tipo de dato de cada valor según su regla de escape.

Ejemplo

Supongamos que tenemos un archivo resultset con el siguiente contenido:

<result_before_delimiter>
${data}<result_after_delimiter>

Y un archivo row_format con este contenido:

<row_before_delimiter>${column_1:CSV}<field_delimiter_1>${column_2:Quoted}<field_delimiter_2>${column_3:JSON}<row_after_delimiter>

A continuación, podemos hacer las siguientes consultas:

SET format_template_rows_between_delimiter = '<row_between_delimiter>\n',
       format_template_row = 'row_format',
       format_template_resultset = 'resultset_format'

DESC format(Template, $$<result_before_delimiter>
<row_before_delimiter>42.42<field_delimiter_1>'Some string 1'<field_delimiter_2>[1, null, 2]<row_after_delimiter>
<row_between_delimiter>
<row_before_delimiter>\N<field_delimiter_1>'Some string 3'<field_delimiter_2>[1, 2, null]<row_after_delimiter>
<result_after_delimiter>
$$)

┌─name─────┬─type───────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ column_1 │ Nullable(Float64)      │              │                    │         │                  │                │
│ column_2 │ Nullable(String)       │              │                    │         │                  │                │
│ column_3 │ Array(Nullable(Int64)) │              │                    │         │                  │                │
└──────────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

​

Regexp

Al igual que en Template, en el formato Regexp ClickHouse primero extrae todos los valores de las columnas de la fila según la expresión regular especificada y luego intenta inferir el tipo de dato correspondiente a cada valor de acuerdo con la regla de escape especificada.

Ejemplo

SET format_regexp = '^Line: value_1=(.+?), value_2=(.+?), value_3=(.+?)',
       format_regexp_escaping_rule = 'CSV'

DESC format(Regexp, $$Line: value_1=42, value_2="Some string 1", value_3="[1, NULL, 3]"
Line: value_1=2, value_2="Some string 2", value_3="[4, 5, NULL]"$$)

┌─name─┬─type───────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(Int64)        │              │                    │         │                  │                │
│ c2   │ Nullable(String)       │              │                    │         │                  │                │
│ c3   │ Array(Nullable(Int64)) │              │                    │         │                  │                │
└──────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

​

Configuración de los formatos de texto

​

input_format_max_rows_to_read_for_schema_inference/input_format_max_bytes_to_read_for_schema_inference

Estos ajustes controlan la cantidad de datos que se leerá durante la inferencia del esquema. Cuantas más filas/bytes se lean, más tiempo se dedicará a la inferencia del esquema, pero mayor será la probabilidad de determinar correctamente los tipos (especialmente cuando los datos contienen muchos valores nulos).

Valores predeterminados:

• 25000 para input_format_max_rows_to_read_for_schema_inference.
• 33554432 (32 Mb) para input_format_max_bytes_to_read_for_schema_inference.

​

column_names_for_schema_inference

La lista de nombres de columnas que se usarán para la inferencia del esquema en formatos sin nombres de columna explícitos. Los nombres especificados se usarán en lugar de los predeterminados c1,c2,c3,.... El formato es: column1,column2,column3,....

Ejemplo

DESC format(TSV, 'Hello, World!    42    [1, 2, 3]') settings column_names_for_schema_inference = 'str,int,arr'

┌─name─┬─type───────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ str  │ Nullable(String)       │              │                    │         │                  │                │
│ int  │ Nullable(Int64)        │              │                    │         │                  │                │
│ arr  │ Array(Nullable(Int64)) │              │                    │         │                  │                │
└──────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

​

schema_inference_hints

La lista de nombres y tipos de columnas que se utilizarán en la inferencia del esquema en lugar de los tipos determinados automáticamente. El formato es: ‘column_name1 column_type1, column_name2 column_type2, …’. Esta configuración puede usarse para especificar los tipos de las columnas que no se pudieron determinar automáticamente o para optimizar el esquema.

Ejemplo

DESC format(JSONEachRow, '{"id" : 1, "age" : 25, "name" : "Josh", "status" : null, "hobbies" : ["football", "cooking"]}') SETTINGS schema_inference_hints = 'age LowCardinality(UInt8), status Nullable(String)', allow_suspicious_low_cardinality_types=1

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

​

schema_inference_make_columns_nullable $

Controla si los tipos inferidos se marcan como Nullable durante la inferencia de esquema para formatos que no incluyen información sobre nulabilidad. Posibles valores:

• 0 - el tipo inferido nunca será Nullable,
• 1 - todos los tipos inferidos serán Nullable,
• 2 o ‘auto’: para los formatos de texto, el tipo inferido será Nullable solo si la columna contiene NULL en una muestra analizada durante la inferencia del esquema; para los formatos con tipado fuerte (Parquet, ORC, Arrow), la información sobre nulabilidad se toma de los metadatos del archivo,
• 3 - para formatos de texto, use Nullable; para los formatos con tipado fuerte, use los metadatos del archivo.

Predeterminado: 3.

Ejemplos

SET schema_inference_make_columns_nullable = 1;
DESC format(JSONEachRow, $$
                                {"id" :  1, "age" :  25, "name" : "Josh", "status" : null, "hobbies" : ["football", "cooking"]}
                                {"id" :  2, "age" :  19, "name" :  "Alan", "status" : "married", "hobbies" :  ["tennis", "art"]}
                         $$)

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

SET schema_inference_make_columns_nullable = 'auto';
DESC format(JSONEachRow, $$
                                {"id" :  1, "age" :  25, "name" : "Josh", "status" : null, "hobbies" : ["football", "cooking"]}
                                {"id" :  2, "age" :  19, "name" :  "Alan", "status" : "married", "hobbies" :  ["tennis", "art"]}
                         $$)

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

SET schema_inference_make_columns_nullable = 0;
DESC format(JSONEachRow, $$
                                {"id" :  1, "age" :  25, "name" : "Josh", "status" : null, "hobbies" : ["football", "cooking"]}
                                {"id" :  2, "age" :  19, "name" :  "Alan", "status" : "married", "hobbies" :  ["tennis", "art"]}
                         $$)

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

​

input_format_try_infer_integers

Esta configuración no se aplica al tipo de datos JSON.

Si está habilitada, ClickHouse intentará inferir enteros en lugar de números de coma flotante durante la inferencia de esquemas para formatos de texto. Si todos los números de la columna en los datos de muestra son enteros, el tipo de resultado será Int64; si al menos un número es de coma flotante, el tipo de resultado será Float64. Si los datos de muestra contienen solo enteros y al menos uno es positivo y provoca un desbordamiento de Int64, ClickHouse inferirá UInt64.

Habilitada de forma predeterminada.

Ejemplos

SET input_format_try_infer_integers = 0
DESC format(JSONEachRow, $$
                                {"number" : 1}
                                {"number" : 2}
                         $$)

┌─name───┬─type──────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ number │ Nullable(Float64) │              │                    │         │                  │                │
└────────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

SET input_format_try_infer_integers = 1
DESC format(JSONEachRow, $$
                                {"number" : 1}
                                {"number" : 2}
                         $$)

┌─name───┬─type────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ number │ Nullable(Int64) │              │                    │         │                  │                │
└────────┴─────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

DESC format(JSONEachRow, $$
                                {"number" : 1}
                                {"number" : 18446744073709551615}
                         $$)

┌─name───┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ number │ Nullable(UInt64) │              │                    │         │                  │                │
└────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

DESC format(JSONEachRow, $$
                                {"number" : 1}
                                {"number" : 2.2}
                         $$)

┌─name───┬─type──────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ number │ Nullable(Float64) │              │                    │         │                  │                │
└────────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

​

input_format_try_infer_datetimes

Si está habilitado, ClickHouse intentará inferir el tipo DateTime o DateTime64 a partir de campos de tipo cadena durante la inferencia de esquemas para formatos de texto. Si todos los campos de una columna en los datos de muestra se analizaron correctamente como valores datetime, el tipo resultante será DateTime o DateTime64(9) (si algún valor datetime tenía parte fraccionaria); si al menos un campo no se pudo analizar como datetime, el tipo resultante será String.

Habilitado de forma predeterminada.

Ejemplos

SET input_format_try_infer_datetimes = 0;
DESC format(JSONEachRow, $$
                                {"datetime" : "2021-01-01 00:00:00", "datetime64" : "2021-01-01 00:00:00.000"}
                                {"datetime" : "2022-01-01 00:00:00", "datetime64" : "2022-01-01 00:00:00.000"}
                         $$)

┌─name───────┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ datetime   │ Nullable(String) │              │                    │         │                  │                │
│ datetime64 │ Nullable(String) │              │                    │         │                  │                │
└────────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

SET input_format_try_infer_datetimes = 1;
DESC format(JSONEachRow, $$
                                {"datetime" : "2021-01-01 00:00:00", "datetime64" : "2021-01-01 00:00:00.000"}
                                {"datetime" : "2022-01-01 00:00:00", "datetime64" : "2022-01-01 00:00:00.000"}
                         $$)

┌─name───────┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ datetime   │ Nullable(DateTime)      │              │                    │         │                  │                │
│ datetime64 │ Nullable(DateTime64(9)) │              │                    │         │                  │                │
└────────────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

DESC format(JSONEachRow, $$
                                {"datetime" : "2021-01-01 00:00:00", "datetime64" : "2021-01-01 00:00:00.000"}
                                {"datetime" : "unknown", "datetime64" : "unknown"}
                         $$)

┌─name───────┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ datetime   │ Nullable(String) │              │                    │         │                  │                │
│ datetime64 │ Nullable(String) │              │                    │         │                  │                │
└────────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

​

input_format_try_infer_datetimes_only_datetime64

Si está habilitado, ClickHouse siempre inferirá DateTime64(9) cuando input_format_try_infer_datetimes esté habilitado, incluso si los valores de fecha y hora no contienen una parte fraccionaria.

Está deshabilitado de forma predeterminada.

Ejemplos

SET input_format_try_infer_datetimes = 1;
SET input_format_try_infer_datetimes_only_datetime64 = 1;
DESC format(JSONEachRow, $$
                                {"datetime" : "2021-01-01 00:00:00", "datetime64" : "2021-01-01 00:00:00.000"}
                                {"datetime" : "2022-01-01 00:00:00", "datetime64" : "2022-01-01 00:00:00.000"}
                         $$)

┌─name───────┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ datetime   │ Nullable(DateTime64(9)) │              │                    │         │                  │                │
│ datetime64 │ Nullable(DateTime64(9)) │              │                    │         │                  │                │
└────────────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Nota: El análisis de fechas y horas durante la inferencia del esquema tiene en cuenta la configuración date_time_input_format

​

input_format_try_infer_dates

Si está habilitado, ClickHouse intentará inferir el tipo Date a partir de campos de cadena durante la inferencia del esquema para formatos de texto. Si todos los campos de una columna en los datos de muestra se interpretan correctamente como fechas, el tipo resultante será Date; si al menos un campo no se interpreta como fecha, el tipo resultante será String.

Habilitado de forma predeterminada.

Ejemplos

SET input_format_try_infer_datetimes = 0, input_format_try_infer_dates = 0
DESC format(JSONEachRow, $$
                                {"date" : "2021-01-01"}
                                {"date" : "2022-01-01"}
                         $$)

┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ date │ Nullable(String) │              │                    │         │                  │                │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

SET input_format_try_infer_dates = 1
DESC format(JSONEachRow, $$
                                {"date" : "2021-01-01"}
                                {"date" : "2022-01-01"}
                         $$)

┌─name─┬─type───────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ date │ Nullable(Date) │              │                    │         │                  │                │
└──────┴────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

DESC format(JSONEachRow, $$
                                {"date" : "2021-01-01"}
                                {"date" : "unknown"}
                         $$)

┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ date │ Nullable(String) │              │                    │         │                  │                │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

​

input_format_try_infer_exponent_floats

Si está habilitado, ClickHouse intentará detectar números de punto flotante en notación exponencial en los formatos de texto (excepto en JSON, donde los números en notación exponencial siempre se detectan).

Deshabilitado de forma predeterminada.

Ejemplo

SET input_format_try_infer_exponent_floats = 1;
DESC format(CSV,
$$1.1E10
2.3e-12
42E00
$$)

┌─name─┬─type──────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(Float64) │              │                    │         │                  │                │
└──────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

​

Formatos autodescriptivos

Los formatos autodescriptivos contienen información sobre la estructura de los datos en los propios datos; puede tratarse de algún encabezado con una descripción, un árbol binario de tipos o algún tipo de tabla. Para inferir automáticamente un esquema a partir de archivos en estos formatos, ClickHouse lee una parte de los datos que contiene información sobre los tipos y la convierte en el esquema de la tabla de ClickHouse.

​

Formatos con el sufijo -WithNamesAndTypes

ClickHouse admite algunos formatos de texto con el sufijo -WithNamesAndTypes. Este sufijo significa que los datos incluyen dos filas adicionales con los nombres y tipos de las columnas antes de los datos reales. Durante la inferencia del esquema para estos formatos, ClickHouse lee las dos primeras filas y extrae los nombres y tipos de las columnas.

Ejemplo

DESC format(TSVWithNamesAndTypes,
$$num    str    arr
UInt8    String    Array(UInt8)
42    Hello, World!    [1,2,3]
$$)

┌─name─┬─type─────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ num  │ UInt8        │              │                    │         │                  │                │
│ str  │ String       │              │                    │         │                  │                │
│ arr  │ Array(UInt8) │              │                    │         │                  │                │
└──────┴──────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

​

Formatos JSON con metadatos

Algunos formatos de entrada JSON (JSON, JSONCompact, JSONColumnsWithMetadata) contienen metadatos con nombres y tipos de columna. En la inferencia del esquema para estos formatos, ClickHouse lee esos metadatos.

Ejemplo

DESC format(JSON, $$
{
    "meta":
    [
        {
            "name": "num",
            "type": "UInt8"
        },
        {
            "name": "str",
            "type": "String"
        },
        {
            "name": "arr",
            "type": "Array(UInt8)"
        }
    ],

    "data":
    [
        {
            "num": 42,
            "str": "Hello, World",
            "arr": [1,2,3]
        }
    ],

    "rows": 1,

    "statistics":
    {
        "elapsed": 0.005723915,
        "rows_read": 1,
        "bytes_read": 1
    }
}
$$)

┌─name─┬─type─────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ num  │ UInt8        │              │                    │         │                  │                │
│ str  │ String       │              │                    │         │                  │                │
│ arr  │ Array(UInt8) │              │                    │         │                  │                │
└──────┴──────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

​

Avro

En formato Avro, ClickHouse lee el esquema a partir de los datos y lo convierte al esquema de ClickHouse mediante las siguientes correspondencias de tipos:

Tipo de dato de Avro	Tipo de dato de ClickHouse
boolean	Bool
int	Int32
int (date) *	Date32
long	Int64
float	Float32
double	Float64
bytes, string	String
fixed	FixedString(N)
enum	Enum
array(T)	Array(T)
union(null, T), union(T, null)	Nullable(T)
null	Nullable(Nothing)
string (uuid) *	UUID
binary (decimal) *	Decimal(P, S)

• Tipos lógicos de Avro

No se admiten otros tipos de Avro.

​

Parquet

En formato Parquet, ClickHouse lee el esquema a partir de los datos y lo convierte al esquema de ClickHouse mediante las siguientes correspondencias de tipos:

Tipo de dato de Parquet	Tipo de dato de ClickHouse
BOOL	Bool
UINT8	UInt8
INT8	Int8
UINT16	UInt16
INT16	Int16
UINT32	UInt32
INT32	Int32
UINT64	UInt64
INT64	Int64
FLOAT	Float32
DOUBLE	Float64
DATE	Date32
TIME (ms)	DateTime
TIMESTAMP, TIME (us, ns)	DateTime64
STRING, BINARY	String
DECIMAL	Decimal
LIST	Array
STRUCT	Tuple
MAP	Map

No se admiten otros tipos de Parquet.

​

Arrow

En el formato Arrow, ClickHouse lee el esquema de los datos y lo convierte en un esquema de ClickHouse mediante las siguientes correspondencias de tipos:

Tipo de dato de Arrow	Tipo de dato de ClickHouse
BOOL	Bool
UINT8	UInt8
INT8	Int8
UINT16	UInt16
INT16	Int16
UINT32	UInt32
INT32	Int32
UINT64	UInt64
INT64	Int64
FLOAT, HALF_FLOAT	Float32
DOUBLE	Float64
DATE32	Date32
DATE64	DateTime
TIMESTAMP, TIME32, TIME64	DateTime64
STRING, BINARY	String
DECIMAL128, DECIMAL256	Decimal
LIST	Array
STRUCT	Tuple
MAP	Map

Los demás tipos de Arrow no son compatibles.

​

ORC

En el formato ORC, ClickHouse lee el esquema a partir de los datos y lo convierte al esquema de ClickHouse mediante las siguientes correspondencias de tipos:

Tipo de dato de ORC	Tipo de dato de ClickHouse
Boolean	Bool
Tinyint	Int8
Smallint	Int16
Int	Int32
Bigint	Int64
Float	Float32
Double	Float64
Date	Date32
Timestamp	DateTime64
String, Char, Varchar,BINARY	String
Decimal	Decimal
List	Array
Struct	Tuple
Map	Map
Union	Variant

Otros tipos de ORC no son compatibles.

​

Native

El formato Native se utiliza internamente en ClickHouse e incluye el esquema en los propios datos. En la inferencia de esquemas, ClickHouse lee el esquema a partir de los datos sin aplicar ninguna transformación.

​

Formatos con esquema externo

Estos formatos requieren un esquema que describa los datos en un archivo independiente, en un lenguaje de esquemas específico. Para inferir automáticamente un esquema a partir de archivos en estos formatos, ClickHouse lee el esquema externo desde un archivo independiente y lo convierte en el esquema de una tabla de ClickHouse.

​

Protobuf

En la inferencia de esquemas del formato Protobuf, ClickHouse utiliza las siguientes correspondencias de tipos:

Tipo de dato de Protobuf	Tipo de dato de ClickHouse
bool	UInt8
float	Float32
double	Float64
int32, sint32, sfixed32	Int32
int64, sint64, sfixed64	Int64
uint32, fixed32	UInt32
uint64, fixed64	UInt64
string, bytes	String
enum	Enum
repeated T	Array(T)
message, group	Tuple

​

CapnProto

En la inferencia de esquemas del formato CapnProto, ClickHouse utiliza las siguientes correspondencias de tipos:

Tipo de dato de CapnProto	Tipo de dato de ClickHouse
Bool	UInt8
Int8	Int8
UInt8	UInt8
Int16	Int16
UInt16	UInt16
Int32	Int32
UInt32	UInt32
Int64	Int64
UInt64	UInt64
Float32	Float32
Float64	Float64
Text, Data	String
enum	Enum
List	Array
struct	Tuple
union(T, Void), union(Void, T)	Nullable(T)

​

Formatos binarios con tipado fuerte

En estos formatos, cada valor serializado contiene información sobre su tipo (y posiblemente sobre su nombre), pero no hay información sobre la tabla completa. Para la inferencia de esquemas en estos formatos, ClickHouse lee los datos fila por fila (hasta input_format_max_rows_to_read_for_schema_inference filas o input_format_max_bytes_to_read_for_schema_inference bytes) y extrae el tipo (y posiblemente el nombre) de cada valor a partir de los datos, y luego convierte esos tipos en tipos de ClickHouse.

​

MsgPack

En el formato MsgPack no hay delimitador entre filas; para usar la inferencia de esquema con este formato, debe especificar el número de columnas de la tabla mediante el SETTING input_format_msgpack_number_of_columns. ClickHouse usa las siguientes correspondencias de tipos:

Tipo de dato de MessagePack (INSERT)	Tipo de dato de ClickHouse
int N, uint N, negative fixint, positive fixint	Int64
bool	UInt8
fixstr, str 8, str 16, str 32, bin 8, bin 16, bin 32	String
float 32	Float32
float 64	Float64
uint 16	Date
uint 32	DateTime
uint 64	DateTime64
fixarray, array 16, array 32	Array
fixmap, map 16, map 32	Map

De forma predeterminada, todos los tipos inferidos se envuelven en Nullable, pero esto puede cambiarse mediante el SETTING schema_inference_make_columns_nullable.

​

BSONEachRow

En BSONEachRow, cada fila de datos se presenta como un documento BSON. En la inferencia de esquema, ClickHouse lee los documentos BSON uno por uno y extrae valores, nombres y tipos de los datos, y luego transforma estos tipos en tipos de ClickHouse usando las siguientes correspondencias de tipos:

Tipo de BSON	tipo de ClickHouse
\x08 boolean	Bool
\x10 int32	Int32
\x12 int64	Int64
\x01 double	Float64
\x09 datetime	DateTime64
\x05 binary con subtipo binary \x00, \x02 string, \x0E symbol, \x0D código JavaScript	String
\x07 ObjectId,	FixedString(12)
\x05 binary con subtipo uuid \x04, tamaño = 16	UUID
\x04 array	Array/Tuple (si los tipos anidados son diferentes)
\x03 document	Tupla con nombre/Map (con claves String)

De forma predeterminada, todos los tipos inferidos están dentro de Nullable, pero esto se puede cambiar usando la SETTING schema_inference_make_columns_nullable.

​

Formatos con esquema constante

En estos formatos, los datos siempre tienen el mismo esquema.

​

LineAsString

En este formato, ClickHouse lee la línea completa de los datos en una única columna con el tipo de datos String. El tipo inferido para este formato es siempre String y el nombre de la columna es line.

Ejemplo

DESC format(LineAsString, 'Hello\nworld!')

┌─name─┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ line │ String │              │                    │         │                  │                │
└──────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

​

JSONAsString

En este formato, ClickHouse lee el objeto JSON completo de los datos en una única columna con el tipo de dato String. El tipo inferido para este formato es siempre String y el nombre de la columna es json.

Ejemplo

DESC format(JSONAsString, '{"x" : 42, "y" : "Hello, World!"}')

┌─name─┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ json │ String │              │                    │         │                  │                │
└──────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

​

JSONAsObject

En este formato, ClickHouse lee el objeto JSON completo de los datos en una sola columna con el tipo de dato JSON. El tipo inferido para este formato es siempre JSON y el nombre de la columna es json.

Ejemplo

DESC format(JSONAsObject, '{"x" : 42, "y" : "Hello, World!"}');

┌─name─┬─type─┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ json │ JSON │              │                    │         │                  │                │
└──────┴──────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

​

Modos de inferencia de esquemas

La inferencia de esquemas a partir del conjunto de archivos de datos puede funcionar en 2 modos distintos: default y union. El modo se controla mediante la SETTING schema_inference_mode.

​

Modo predeterminado

En el modo predeterminado, ClickHouse asume que todos los archivos tienen el mismo esquema e intenta inferirlo leyendo los archivos uno por uno hasta lograrlo.

Ejemplo:

Supongamos que tenemos 3 archivos data1.jsonl, data2.jsonl y data3.jsonl con el siguiente contenido:

data1.jsonl:

{"field1" :  1, "field2" :  null}
{"field1" :  2, "field2" :  null}
{"field1" :  3, "field2" :  null}

data2.jsonl:

{"field1" :  4, "field2" :  "Data4"}
{"field1" :  5, "field2" :  "Data5"}
{"field1" :  6, "field2" :  "Data5"}

data3.jsonl:

{"field1" :  7, "field2" :  "Data7", "field3" :  [1, 2, 3]}
{"field1" :  8, "field2" :  "Data8", "field3" :  [4, 5, 6]}
{"field1" :  9, "field2" :  "Data9", "field3" :  [7, 8, 9]}

Probemos a usar la inferencia de esquema con estos 3 archivos:

Query

:) DESCRIBE file('data{1,2,3}.jsonl') SETTINGS schema_inference_mode='default'

Response

┌─name───┬─type─────────────┐
│ field1 │ Nullable(Int64)  │
│ field2 │ Nullable(String) │
└────────┴──────────────────┘

Como podemos ver, no tenemos field3 del archivo data3.jsonl. Esto ocurre porque ClickHouse primero intentó inferir el esquema a partir del archivo data1.jsonl, pero falló porque el campo field2 solo contenía valores nulos, y luego intentó inferir el esquema a partir de data2.jsonl y lo consiguió, por lo que no se leyeron los datos del archivo data3.jsonl.

​

Modo union

En modo union, ClickHouse asume que los archivos pueden tener distintos esquemas, por lo que infiere los esquemas de todos ellos y luego los combina en un esquema común.

Supongamos que tenemos 3 archivos data1.jsonl, data2.jsonl y data3.jsonl con el siguiente contenido:

data1.jsonl:

{"field1" :  1}
{"field1" :  2}
{"field1" :  3}

data2.jsonl:

{"field2" :  "Data4"}
{"field2" :  "Data5"}
{"field2" :  "Data5"}

data3.jsonl:

{"field3" :  [1, 2, 3]}
{"field3" :  [4, 5, 6]}
{"field3" :  [7, 8, 9]}

Probemos a usar la inferencia de esquema con estos 3 archivos:

Query

:) DESCRIBE file('data{1,2,3}.jsonl') SETTINGS schema_inference_mode='union'

Response

┌─name───┬─type───────────────────┐
│ field1 │ Nullable(Int64)        │
│ field2 │ Nullable(String)       │
│ field3 │ Array(Nullable(Int64)) │
└────────┴────────────────────────┘

Como podemos ver, tenemos todos los campos de todos los archivos.

Nota:

• Dado que es posible que algunos archivos no contengan algunas columnas del esquema resultante, el modo union solo es compatible con formatos que admiten leer un subconjunto de columnas (como JSONEachRow, Parquet, TSVWithNames, etc.) y no funcionará con otros formatos (como CSV, TSV, JSONCompactEachRow, etc.).
• Si ClickHouse no puede inferir el esquema de uno de los archivos, se lanzará una excepción.
• Si tiene muchos archivos, leer el esquema de todos ellos puede llevar mucho tiempo.

​

Detección automática del formato

Si no se especifica el formato de los datos y no puede determinarse por la extensión del archivo, ClickHouse intentará detectar el formato del archivo en función de su contenido.

Ejemplos:

Supongamos que tenemos data con el siguiente contenido:

"a","b"
1,"Data1"
2,"Data2"
3,"Data3"

Podemos inspeccionar este archivo y hacer consultas sobre él sin especificar el formato ni la estructura:

:) desc file(data);

┌─name─┬─type─────────────┐
│ a    │ Nullable(Int64)  │
│ b    │ Nullable(String) │
└──────┴──────────────────┘

:) select * from file(data);

┌─a─┬─b─────┐
│ 1 │ Data1 │
│ 2 │ Data2 │
│ 3 │ Data3 │
└───┴───────┘

ClickHouse solo puede detectar algunos formatos, y esta detección lleva algo de tiempo; siempre es mejor especificar el formato explícitamente.