Inférence de schéma JSON - ClickHouse Documentation

ClickHouse peut déterminer automatiquement la structure de données JSON. Cela permet d’interroger directement des données JSON, par exemple sur disque avec clickhouse-local ou dans des buckets S3, et/ou de créer automatiquement des schémas avant de charger les données dans ClickHouse.

Quand utiliser l’inférence de type

Structure cohérente - Les données à partir desquelles les types seront inférés contiennent toutes les clés qui vous intéressent. L’inférence de type repose sur l’échantillonnage des données jusqu’à un nombre maximal de lignes ou d’octets. Les données situées après l’échantillon, avec des colonnes supplémentaires, seront ignorées et ne pourront pas être interrogées.
Types cohérents - Les types de données associés à certaines clés doivent être compatibles, c’est-à-dire qu’il doit être possible de convertir automatiquement un type en un autre.

Si votre JSON est plus dynamique, avec de nouvelles clés ajoutées et plusieurs types possibles pour le même chemin, consultez “Working with semi-structured and dynamic data”.

Détection des types

La suite suppose que le JSON a une structure cohérente et qu’un seul type est associé à chaque chemin. Dans nos exemples précédents, nous avons utilisé une version simplifiée du Python PyPI dataset au format NDJSON. Dans cette section, nous examinons un jeu de données plus complexe avec des structures imbriquées : le jeu de données arXiv, qui contient 2,5 millions d’articles scientifiques. Chaque ligne de ce jeu de données, fourni au format NDJSON, représente un article scientifique publié. Un exemple de ligne est présenté ci-dessous :

{
  "id": "2101.11408",
  "submitter": "Daniel Lemire",
  "authors": "Daniel Lemire",
  "title": "Number Parsing at a Gigabyte per Second",
  "comments": "Software at https://github.com/fastfloat/fast_float and\n https://github.com/lemire/simple_fastfloat_benchmark/",
  "journal-ref": "Software: Practice and Experience 51 (8), 2021",
  "doi": "10.1002/spe.2984",
  "report-no": null,
  "categories": "cs.DS cs.MS",
  "license": "http://creativecommons.org/licenses/by/4.0/",
  "abstract": "With disks and networks providing gigabytes per second ....\n",
  "versions": [
    {
      "created": "Mon, 11 Jan 2021 20:31:27 GMT",
      "version": "v1"
    },
    {
      "created": "Sat, 30 Jan 2021 23:57:29 GMT",
      "version": "v2"
    }
  ],
  "update_date": "2022-11-07",
  "authors_parsed": [
    [
      "Lemire",
      "Daniel",
      ""
    ]
  ]
}

Ces données nécessitent un schéma bien plus complexe que dans les exemples précédents. Nous décrivons ci-dessous le processus de définition de ce schéma, en introduisant des types complexes tels que Tuple et Array. Ce jeu de données est stocké dans un bucket S3 public à l’emplacement s3://datasets-documentation/arxiv/arxiv.json.gz. Vous pouvez constater que le jeu de données ci-dessus contient des objets JSON imbriqués. Même si vous devez rédiger et versionner vos schémas, l’inférence permet de déduire les types à partir des données. Cela permet de générer automatiquement le DDL du schéma, ce qui évite d’avoir à le construire manuellement et accélère le processus de développement.

Détection automatique du formatEn plus de détecter le schéma, l’inférence de schéma JSON détermine automatiquement le format des données à partir de l’extension du fichier et de son contenu. Le fichier ci-dessus est ainsi automatiquement identifié comme étant au format NDJSON.

L’utilisation de la fonction s3 avec la commande DESCRIBE affiche les types qui seront déduits.

DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/arxiv/arxiv.json.gz')
SETTINGS describe_compact_output = 1

┌─name───────────┬─type────────────────────────────────────────────────────────────────────┐
│ id             │ Nullable(String)                                                        │
│ submitter      │ Nullable(String)                                                        │
│ authors        │ Nullable(String)                                                        │
│ title          │ Nullable(String)                                                        │
│ comments       │ Nullable(String)                                                        │
│ journal-ref    │ Nullable(String)                                                        │
│ doi            │ Nullable(String)                                                        │
│ report-no      │ Nullable(String)                                                        │
│ categories     │ Nullable(String)                                                        │
│ license        │ Nullable(String)                                                        │
│ abstract       │ Nullable(String)                                                        │
│ versions       │ Array(Tuple(created Nullable(String),version Nullable(String)))         │
│ update_date    │ Nullable(Date)                                                          │
│ authors_parsed │ Array(Array(Nullable(String)))                                          │
└────────────────┴─────────────────────────────────────────────────────────────────────────┘

Évitez les valeurs NULLVous pouvez constater que de nombreuses colonnes sont détectées comme Nullable. Nous déconseillons d’utiliser le type Nullable sauf en cas de nécessité absolue. Vous pouvez utiliser schema_inference_make_columns_nullable pour contrôler les cas où Nullable est appliqué.

Nous pouvons constater que la plupart des colonnes ont été automatiquement détectées comme String, et que la colonne update_date a bien été détectée comme Date. La colonne versions a été créée comme Array(Tuple(created String, version String)) pour stocker une liste d’objets, tandis que authors_parsed est défini comme Array(Array(String)) pour les tableaux imbriqués.

Contrôle de la détection des typesLa détection automatique des dates et des valeurs DateTime peut être contrôlée respectivement à l’aide des paramètres input_format_try_infer_dates et input_format_try_infer_datetimes (tous deux activés par défaut). L’inférence d’objets sous forme de tuples est contrôlée par le paramètre input_format_json_try_infer_named_tuples_from_objects. D’autres paramètres qui contrôlent l’inférence de schéma pour le JSON, comme la détection automatique des nombres, sont disponibles ici.

Interroger des données JSON

La suite suppose que le JSON présente une structure cohérente et un seul type pour chaque chemin. Nous pouvons nous appuyer sur l’inférence de schéma pour interroger directement les données JSON. Ci-dessous, nous identifions les principaux auteurs pour chaque année, en tirant parti du fait que les dates et les tableaux sont détectés automatiquement.

SELECT
 toYear(update_date) AS year,
 authors,
    count() AS c
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/arxiv/arxiv.json.gz')
GROUP BY
    year,
 authors
ORDER BY
    year ASC,
 c DESC
LIMIT 1 BY year

┌─year─┬─authors────────────────────────────────────┬───c─┐
│ 2007 │ The BABAR Collaboration, B. Aubert, et al  │  98 │
│ 2008 │ The OPAL collaboration, G. Abbiendi, et al │  59 │
│ 2009 │ Ashoke Sen                                 │  77 │
│ 2010 │ The BABAR Collaboration, B. Aubert, et al  │ 117 │
│ 2011 │ Amelia Carolina Sparavigna                 │  21 │
│ 2012 │ ZEUS Collaboration                         │ 140 │
│ 2013 │ CMS Collaboration                          │ 125 │
│ 2014 │ CMS Collaboration                          │  87 │
│ 2015 │ ATLAS Collaboration                        │ 118 │
│ 2016 │ ATLAS Collaboration                        │ 126 │
│ 2017 │ CMS Collaboration                          │ 122 │
│ 2018 │ CMS Collaboration                          │ 138 │
│ 2019 │ CMS Collaboration                          │ 113 │
│ 2020 │ CMS Collaboration                          │  94 │
│ 2021 │ CMS Collaboration                          │  69 │
│ 2022 │ CMS Collaboration                          │  62 │
│ 2023 │ ATLAS Collaboration                        │ 128 │
│ 2024 │ ATLAS Collaboration                        │ 120 │
└──────┴────────────────────────────────────────────┴─────┘

18 rows in set. Elapsed: 20.172 sec. Processed 2.52 million rows, 1.39 GB (124.72 thousand rows/s., 68.76 MB/s.)

L’inférence de schéma permet d’interroger des fichiers JSON sans avoir à spécifier le schéma, ce qui accélère les tâches d’analyse de données ad hoc.

Création de tables

Nous pouvons nous appuyer sur l’inférence de schéma pour définir le schéma d’une table. La commande CREATE AS EMPTY suivante permet d’inférer le DDL de la table et de créer celle-ci. Cela ne charge aucune donnée :

CREATE TABLE arxiv
ENGINE = MergeTree
ORDER BY update_date EMPTY
AS SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/arxiv/arxiv.json.gz')
SETTINGS schema_inference_make_columns_nullable = 0

Pour vérifier le schéma de la table, nous utilisons la commande SHOW CREATE TABLE :

SHOW CREATE TABLE arxiv

CREATE TABLE arxiv
(
    `id` String,
    `submitter` String,
    `authors` String,
    `title` String,
    `comments` String,
    `journal-ref` String,
    `doi` String,
    `report-no` String,
    `categories` String,
    `license` String,
    `abstract` String,
    `versions` Array(Tuple(created String, version String)),
    `update_date` Date,
    `authors_parsed` Array(Array(String))
)
ENGINE = MergeTree
ORDER BY update_date

Ce qui précède est le schéma correct pour ces données. L’inférence de schéma repose sur l’échantillonnage des données et sur leur lecture ligne par ligne. Les valeurs des colonnes sont extraites selon le format, à l’aide de parseurs récursifs et d’heuristiques permettant de déterminer le type de chaque valeur. Le nombre maximal de lignes et d’octets lus à partir des données lors de l’inférence de schéma est contrôlé par les paramètres input_format_max_rows_to_read_for_schema_inference (25000 par défaut) et input_format_max_bytes_to_read_for_schema_inference (32MB par défaut). Si la détection est incorrecte, vous pouvez fournir des indications comme décrit ici.

Création de tables à partir d’extraits

L’exemple ci-dessus utilise un fichier sur S3 pour créer le schéma de table. Vous souhaiterez peut-être créer un schéma à partir d’un extrait d’une seule ligne. Pour ce faire, utilisez la fonction format, comme indiqué ci-dessous :

CREATE TABLE arxiv
ENGINE = MergeTree
ORDER BY update_date EMPTY
AS SELECT *
FROM format(JSONEachRow, '{"id":"2101.11408","submitter":"Daniel Lemire","authors":"Daniel Lemire","title":"Number Parsing at a Gigabyte per Second","comments":"Software at https://github.com/fastfloat/fast_float and","doi":"10.1002/spe.2984","report-no":null,"categories":"cs.DS cs.MS","license":"http://creativecommons.org/licenses/by/4.0/","abstract":"Withdisks and networks providing gigabytes per second ","versions":[{"created":"Mon, 11 Jan 2021 20:31:27 GMT","version":"v1"},{"created":"Sat, 30 Jan 2021 23:57:29 GMT","version":"v2"}],"update_date":"2022-11-07","authors_parsed":[["Lemire","Daniel",""]]}') SETTINGS schema_inference_make_columns_nullable = 0

SHOW CREATE TABLE arxiv

CREATE TABLE arxiv
(
    `id` String,
    `submitter` String,
    `authors` String,
    `title` String,
    `comments` String,
    `doi` String,
    `report-no` String,
    `categories` String,
    `license` String,
    `abstract` String,
    `versions` Array(Tuple(created String, version String)),
    `update_date` Date,
    `authors_parsed` Array(Array(String))
)
ENGINE = MergeTree
ORDER BY update_date

Chargement de données JSON

La suite suppose que le JSON a une structure cohérente et qu’il n’existe qu’un seul type pour chaque chemin. Les commandes précédentes ont créé une table dans laquelle les données peuvent être chargées. Vous pouvez maintenant insérer les données dans votre table à l’aide de l’instruction INSERT INTO SELECT suivante :

INSERT INTO arxiv SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/arxiv/arxiv.json.gz')

0 rows in set. Elapsed: 38.498 sec. Processed 2.52 million rows, 1.39 GB (65.35 thousand rows/s., 36.03 MB/s.)
Peak memory usage: 870.67 MiB.

Pour des exemples de chargement de données à partir d’autres sources, par exemple un fichier, consultez ici. Une fois les données chargées, nous pouvons les interroger, en utilisant si besoin le format PrettyJSONEachRow pour afficher les lignes dans leur structure d’origine :

SELECT *
FROM arxiv
LIMIT 1
FORMAT PrettyJSONEachRow

{
  "id": "0704.0004",
  "submitter": "David Callan",
  "authors": "David Callan",
  "title": "A determinant of Stirling cycle numbers counts unlabeled acyclic",
  "comments": "11 pages",
  "journal-ref": "",
  "doi": "",
  "report-no": "",
  "categories": "math.CO",
  "license": "",
  "abstract": "  We show that a determinant of Stirling cycle numbers counts unlabeled acyclic\nsingle-source automata.",
  "versions": [
    {
      "created": "Sat, 31 Mar 2007 03:16:14 GMT",
      "version": "v1"
    }
  ],
  "update_date": "2007-05-23",
  "authors_parsed": [
    [
      "Callan",
      "David"
    ]
  ]
}

1 row in set. Elapsed: 0.009 sec.

Gestion des erreurs

Il peut arriver que certaines données soient incorrectes. Par exemple, certaines colonnes peuvent ne pas avoir le bon type, ou un objet JSON peut être mal formaté. Dans ce cas, vous pouvez utiliser les paramètres input_format_allow_errors_num et input_format_allow_errors_ratio pour autoriser l’ignorance d’un certain nombre de lignes si les données provoquent des erreurs d’insertion. De plus, des indications peuvent être fournies pour faciliter l’inférence.

Travailler avec des données semi-structurées et dynamiques

Dans notre exemple précédent, nous utilisions un JSON statique, avec des noms de clés et des types bien définis. Ce n’est toutefois pas toujours le cas : des clés peuvent être ajoutées ou leur type peut changer. C’est courant dans des cas d’usage comme les données d’observabilité. ClickHouse gère ce cas grâce à un type JSON dédié. Si vous savez que votre JSON est très dynamique, avec un grand nombre de clés uniques et plusieurs types possibles pour une même clé, nous vous recommandons de ne pas utiliser l’inférence de schéma avec JSONEachRow pour essayer d’inférer une colonne pour chaque clé, même si les données sont au format JSON délimité par des retours à la ligne. Prenons l’exemple suivant, issu d’une version enrichie du jeu de données Python PyPI dataset présenté plus haut. Nous y avons ajouté une colonne tags arbitraire contenant des paires clé-valeur aléatoires.

{
  "date": "2022-09-22",
  "country_code": "IN",
  "project": "clickhouse-connect",
  "type": "bdist_wheel",
  "installer": "bandersnatch",
  "python_minor": "",
  "system": "",
  "version": "0.2.8",
  "tags": {
    "5gTux": "f3to*PMvaTYZsz!*rtzX1",
    "nD8CV": "value"
  }
}

Un échantillon de ces données est accessible publiquement au format JSON délimité par des sauts de ligne. Si nous tentons une inférence de schéma sur ce fichier, vous constaterez que les performances sont médiocres et que la réponse est extrêmement détaillée :

DESCRIBE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/pypi_with_tags/sample_rows.json.gz')

-- result omitted for brevity

9 rows in set. Elapsed: 127.066 sec.

Le principal problème ici est que le format JSONEachRow est utilisé pour l’inférence. Il tente d’inférer un type de colonne pour chaque clé du JSON — autrement dit, d’appliquer un schéma statique aux données sans utiliser le type JSON. Avec des milliers de colonnes uniques, cette approche de l’inférence est lente. À la place, vous pouvez utiliser le format JSONAsObject. JSONAsObject traite l’entrée entière comme un seul objet JSON et la stocke dans une seule colonne de type JSON, ce qui le rend plus adapté aux payloads JSON très dynamiques ou imbriqués.

DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/pypi_with_tags/sample_rows.json.gz', 'JSONAsObject')
SETTINGS describe_compact_output = 1

┌─name─┬─type─┐
│ json │ JSON │
└──────┴──────┘

1 row in set. Elapsed: 0.005 sec.

Ce format est également essentiel lorsque des colonnes ont plusieurs types qui ne peuvent pas être conciliés. Par exemple, considérons un fichier sample.json contenant le JSON délimité par des retours à la ligne suivant :

{"a":1}
{"a":"22"}

Dans ce cas, ClickHouse peut résoudre le conflit de types par coercition et traiter la colonne a comme un Nullable(String).

DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/json/sample.json')
SETTINGS describe_compact_output = 1

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

1 row in set. Elapsed: 0.081 sec.

Conversion de typeCette conversion de type peut être contrôlée à l’aide d’un certain nombre de paramètres. L’exemple ci-dessus dépend du paramètre input_format_json_read_numbers_as_strings.

Cependant, certains types sont incompatibles. Prenons l’exemple suivant :

{"a":1}
{"a":{"b":2}}

Dans ce cas, aucune conversion de type n’est possible. La commande DESCRIBE échoue donc :

DESCRIBE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/json/conflict_sample.json')

Elapsed: 0.755 sec.

Received exception from server (version 24.12.1):
Code: 636. DB::Exception: Received from sql-clickhouse.clickhouse.com:9440. DB::Exception: The table structure cannot be extracted from a JSON format file. Error:
Code: 53. DB::Exception: Automatically defined type Tuple(b Int64) for column 'a' in row 1 differs from type defined by previous rows: Int64. You can specify the type for this column using setting schema_inference_hints.

Dans ce cas, JSONAsObject considère chaque ligne comme une unique valeur de type JSON (ce qui permet à une même colonne d’avoir plusieurs types). C’est essentiel :

DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/json/conflict_sample.json', JSONAsObject)
SETTINGS enable_json_type = 1, describe_compact_output = 1

┌─name─┬─type─┐
│ json │ JSON │
└──────┴──────┘

1 row in set. Elapsed: 0.010 sec.

Pour aller plus loin

Pour en savoir plus sur l’inférence des types de données, vous pouvez consulter cette page de documentation.

​Quand utiliser l’inférence de type

​Détection des types

​Interroger des données JSON

​Création de tables

​Création de tables à partir d’extraits

​Chargement de données JSON

​Gestion des erreurs

​Travailler avec des données semi-structurées et dynamiques

​Pour aller plus loin

Quand utiliser l’inférence de type

Détection des types

Interroger des données JSON

Création de tables

Création de tables à partir d’extraits

Chargement de données JSON

Gestion des erreurs

Travailler avec des données semi-structurées et dynamiques

Pour aller plus loin