Trabajar con JSON

Carga de JSON

Los siguientes ejemplos muestran una forma muy sencilla de cargar datos JSON estructurados y semiestructurados. Para JSON más complejos, incluidas las estructuras anidadas, consulte la guía Diseño de esquemas JSON.

​

Carga de JSON estructurado

En esta sección, asumimos que los datos JSON están en formato NDJSON (JSON delimitado por saltos de línea), conocido como JSONEachRow en ClickHouse, y que están bien estructurados, es decir, los nombres de las columnas y los tipos son fijos. NDJSON es el formato preferido para cargar JSON gracias a su concisión y uso eficiente del espacio, aunque también se admiten otros formatos tanto para entrada como para salida.

Considere el siguiente ejemplo de JSON, que representa una fila del conjunto de datos Python PyPI:

{
  "date": "2022-11-15",
  "country_code": "ES",
  "project": "clickhouse-connect",
  "type": "bdist_wheel",
  "installer": "pip",
  "python_minor": "3.9",
  "system": "Linux",
  "version": "0.3.0"
}

Para cargar este objeto JSON en ClickHouse, es necesario definir un esquema de tabla.

En este caso sencillo, la estructura es estática, los nombres de las columnas son conocidos y sus tipos están bien definidos.

Si bien ClickHouse admite datos semiestructurados mediante un tipo JSON, donde los nombres de clave y sus tipos pueden ser dinámicos, esto no es necesario en este caso.

Prefiera esquemas estáticos siempre que sea posible

Cuando sus columnas tengan nombres y tipos fijos, y no se prevea la aparición de nuevas columnas, priorice siempre un esquema definido estáticamente en producción.

El tipo JSON es la opción preferida para datos muy dinámicos, donde los nombres y tipos de las columnas pueden cambiar. Este tipo también resulta útil para la creación de prototipos y la exploración de datos.

A continuación se muestra un esquema sencillo para esto, donde las claves JSON se mapean a nombres de columnas:

CREATE TABLE pypi (
  `date` Date,
  `country_code` String,
  `project` String,
  `type` String,
  `installer` String,
  `python_minor` String,
  `system` String,
  `version` String
)
ENGINE = MergeTree
ORDER BY (project, date)

Claves de ordenación

Aquí hemos seleccionado una clave de ordenación mediante la cláusula ORDER BY. Para más detalles sobre las claves de ordenación y cómo elegirlas, consulte aquí.

ClickHouse puede cargar datos JSON en varios formatos, infiriendo automáticamente el tipo a partir de la extensión y el contenido. Podemos leer archivos JSON para la tabla anterior mediante la función S3:

SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/json/*.json.gz')
LIMIT 1

┌───────date─┬─country_code─┬─project────────────┬─type────────┬─installer────┬─python_minor─┬─system─┬─version─┐
│ 2022-11-15 │ CN           │ clickhouse-connect │ bdist_wheel │ bandersnatch │              │        │ 0.2.8 │
└────────────┴──────────────┴────────────────────┴─────────────┴──────────────┴──────────────┴────────┴─────────┘

1 row in set. Elapsed: 1.232 sec.

Nótese que no es necesario especificar el formato del archivo. En su lugar, se utiliza un glob pattern para leer todos los archivos *.json.gz del bucket. ClickHouse infiere automáticamente que el formato es JSONEachRow (ndjson) a partir de la extensión y el contenido del archivo. El formato puede especificarse manualmente mediante funciones con parámetros en caso de que ClickHouse no pueda detectarlo.

SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/json/*.json.gz', JSONEachRow)

Archivos comprimidos

Los archivos anteriores también están comprimidos. ClickHouse lo detecta y lo gestiona automáticamente.

Para cargar las filas de estos archivos, podemos usar un INSERT INTO SELECT:

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

Ok.

0 rows in set. Elapsed: 10.445 sec. Processed 19.49 million rows, 35.71 MB (1.87 million rows/s., 3.42 MB/s.)

SELECT * FROM pypi LIMIT 2

┌───────date─┬─country_code─┬─project────────────┬─type──┬─installer────┬─python_minor─┬─system─┬─version─┐
│ 2022-05-26 │ CN           │ clickhouse-connect │ sdist │ bandersnatch │              │        │ 0.0.7 │
│ 2022-05-26 │ CN           │ clickhouse-connect │ sdist │ bandersnatch │              │        │ 0.0.7 │
└────────────┴──────────────┴────────────────────┴───────┴──────────────┴──────────────┴────────┴─────────┘

2 rows in set. Elapsed: 0.005 sec. Processed 8.19 thousand rows, 908.03 KB (1.63 million rows/s., 180.38 MB/s.)

Las filas también pueden cargarse directamente mediante la cláusula FORMAT, p. ej.

INSERT INTO pypi
FORMAT JSONEachRow
{"date":"2022-11-15","country_code":"CN","project":"clickhouse-connect","type":"bdist_wheel","installer":"bandersnatch","python_minor":"","system":"","version":"0.2.8"}

Estos ejemplos suponen el uso del formato JSONEachRow. También se admiten otros formatos JSON comunes, con ejemplos de cómo cargarlos aquí.

​

Carga de JSON semiestructurado

En el ejemplo anterior cargamos un JSON estático con nombres de clave y tipos bien conocidos. Sin embargo, muchas veces no es así: pueden añadirse claves o cambiar sus tipos. Esto es habitual en casos de uso como los datos de observabilidad.

ClickHouse resuelve esto mediante un tipo JSON específico.

Considere el siguiente ejemplo de una versión ampliada del Python PyPI dataset anterior. Aquí hemos añadido una columna tags arbitraria con pares clave-valor aleatorios.

{
  "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"
  }
}

La columna tags aquí es impredecible y, por lo tanto, nos resulta imposible modelarla. Para cargar estos datos, podemos usar el esquema anterior, pero añadiendo una columna tags adicional de tipo JSON:

SET enable_json_type = 1;

CREATE TABLE pypi_with_tags
(
    `date` Date,
    `country_code` String,
    `project` String,
    `type` String,
    `installer` String,
    `python_minor` String,
    `system` String,
    `version` String,
    `tags` JSON
)
ENGINE = MergeTree
ORDER BY (project, date);

Poblamos la tabla usando el mismo enfoque que para el conjunto de datos original:

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

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

Ok.

0 rows in set. Elapsed: 255.679 sec. Processed 1.00 million rows, 29.00 MB (3.91 thousand rows/s., 113.43 KB/s.)
Peak memory usage: 2.00 GiB.

SELECT *
FROM pypi_with_tags
LIMIT 2

┌───────date─┬─country_code─┬─project────────────┬─type──┬─installer────┬─python_minor─┬─system─┬─version─┬─tags─────────────────────────────────────────────────────┐
│ 2022-05-26 │ CN           │ clickhouse-connect │ sdist │ bandersnatch │              │        │ 0.0.7 │ {"nsBM":"5194603446944555691"}                           │
│ 2022-05-26 │ CN           │ clickhouse-connect │ sdist │ bandersnatch │              │        │ 0.0.7 │ {"4zD5MYQz4JkP1QqsJIS":"0","name":"8881321089124243208"} │
└────────────┴──────────────┴────────────────────┴───────┴──────────────┴──────────────┴────────┴─────────┴──────────────────────────────────────────────────────────┘

2 rows in set. Elapsed: 0.149 sec.

Observe la diferencia de rendimiento al cargar los datos. La columna JSON requiere inferencia de tipos en el momento de la inserción, además de almacenamiento adicional si hay columnas con más de un tipo. Aunque el tipo JSON puede configurarse (consulte Diseño de esquemas JSON) para ofrecer un rendimiento equivalente al de declarar las columnas explícitamente, está diseñado intencionadamente para ser flexible de forma predeterminada. Sin embargo, esta flexibilidad tiene un coste.

​

Cuándo usar el tipo JSON

Use el tipo JSON cuando sus datos:

• Tengan claves impredecibles que puedan cambiar con el tiempo.
• Contengan valores de tipos distintos (p. ej., una ruta a veces puede contener una cadena y otras, un número).
• Requieran flexibilidad de esquema cuando el tipado estricto no sea viable.

Si la estructura de sus datos es conocida y consistente, rara vez será necesario usar el tipo JSON, incluso si sus datos están en formato JSON. En concreto, si sus datos tienen:

• Una estructura plana con claves conocidas: use tipos de columna estándar, p. ej., String.
• Anidamiento predecible: use los tipos Tuple, Array o Nested para estas estructuras.
• Una estructura predecible con tipos variables: considere usar los tipos Dynamic o Variant en su lugar.

También puede combinar ambos enfoques, como hemos hecho en el ejemplo anterior, usando columnas estáticas para las claves predecibles de nivel superior y una única columna JSON para una sección dinámica del payload.