Saltar al contenido principal
Los archivos de valores separados por tabulaciones, o TSV, son comunes y pueden incluir encabezados de campo en la primera línea del archivo. ClickHouse puede ingestar archivos TSV y también consultarlos sin necesidad de ingestarlos. Esta guía cubre ambos casos. Si necesita consultar o ingestar archivos CSV, puede usar las mismas técnicas; simplemente sustituya TSV por CSV en los argumentos de formato. Mientras avanza por esta guía, hará lo siguiente:
  • Investigar: consultar la estructura y el contenido del archivo TSV.
  • Determinar el esquema de destino de ClickHouse: elegir los tipos de datos adecuados y asignar los datos existentes a esos tipos.
  • Crear una tabla de ClickHouse.
  • Preprocesar y transmitir los datos a ClickHouse.
  • Ejecutar algunas consultas en ClickHouse.
El conjunto de datos utilizado en esta guía proviene del equipo de NYC Open Data y contiene datos sobre “todos los delitos graves, menores e infracciones denunciados al Departamento de Policía de la Ciudad de Nueva York (NYPD) y considerados válidos”. En el momento de redactar este texto, el archivo de datos ocupa 166 MB, pero se actualiza regularmente. Fuente: data.cityofnewyork.us Términos de uso: https://www1.nyc.gov/home/terms-of-use.page

​
Requisitos previos

​
Una nota sobre los comandos descritos en esta guía

Hay dos tipos de comandos en esta guía:
  • Algunos de los comandos consultan archivos TSV; estos se ejecutan en la línea de comandos.
  • El resto de los comandos consultan ClickHouse y se ejecutan en clickhouse-client o en la UI de Play.
Los ejemplos de esta guía asumen que ha guardado el archivo TSV en ${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv; ajuste los comandos si es necesario.

​
Familiarícese con el archivo TSV

Antes de empezar a trabajar con la base de datos de ClickHouse, familiarícese con los datos.

​
Observa los campos del archivo TSV de origen

Este es un ejemplo de comando para consultar un archivo TSV, pero no lo ejecutes todavía.
Query
Respuesta de ejemplo
La mayoría de las veces, el comando anterior le indicará qué campos de los datos de entrada son numéricos, cuáles son cadenas y cuáles son tuplas. Sin embargo, no siempre es así. Como ClickHouse se usa habitualmente con datasets que contienen miles de millones de registros, hay un número predeterminado (100) de filas que se examinan para inferir el esquema, con el fin de evitar analizar miles de millones de filas para inferirlo. Es posible que la respuesta que aparece a continuación no coincida con la que vea, ya que el dataset se actualiza varias veces al año. Si consulta el Diccionario de datos, verá que CMPLNT_NUM está definido como texto, no como valor numérico. Si sustituye el valor predeterminado de 100 filas para la inferencia por la configuración SETTINGS input_format_max_rows_to_read_for_schema_inference=2000 podrá hacerse una mejor idea del contenido.Nota: a partir de la versión 22.5, el valor predeterminado es ahora de 25.000 filas para inferir el esquema, así que cambie esta configuración solo si usa una versión anterior o si necesita muestrear más de 25.000 filas.
Ejecute este comando en la línea de comandos. Usará clickhouse-local para consultar los datos del archivo TSV que descargó.
Query
Response
En este punto, debes comprobar que las columnas del archivo TSV coincidan con los nombres y los tipos especificados en la sección Columns in this Dataset de la página web del conjunto de datos. Los tipos de datos no son muy específicos: todos los campos numéricos están definidos como Nullable(Float64) y todos los demás campos como Nullable(String). Cuando crees una tabla de ClickHouse para almacenar los datos, podrás especificar tipos más adecuados y con mejor rendimiento.

​
Determinar el esquema adecuado

Para determinar qué tipos deben usarse en los campos, es necesario saber cómo son los datos. Por ejemplo, el campo JURISDICTION_CODE es numérico: ¿debería ser UInt8, Enum o Float64?
Query
Response
La respuesta de la consulta muestra que JURISDICTION_CODE cabe bien en un UInt8. Del mismo modo, revisa algunos de los campos String y comprueba si serían más adecuados como campos DateTime o LowCardinality(String). Por ejemplo, el campo PARKS_NM se describe como “Nombre del parque, zona de juegos o área verde de NYC donde ocurre el hecho, si corresponde (no se incluyen los parques estatales)”. Los nombres de los parques de la ciudad de Nueva York pueden ser un buen candidato para LowCardinality(String):
Query
Response
Observe algunos de los nombres de los parques:
Query
Response
El conjunto de datos utilizado al redactar este documento tiene solo unos pocos cientos de parques y áreas de juego distintos en la columna PARK_NM. Se trata de una cantidad pequeña según la recomendación de LowCardinality de mantenerse por debajo de 10,000 cadenas distintas en un campo LowCardinality(String).

​
Campos de fecha y hora

Según la sección Columns in this Dataset de la página web del conjunto de datos, hay campos de fecha y hora para el inicio y el fin del evento reportado. Examinar los valores mínimo y máximo de CMPLNT_FR_DT y CMPLT_TO_DT permite hacerse una idea de si esos campos siempre están rellenados o no:
Query
Response
Query
Response
Query
Response
Query
Response

​
Elabora un plan

Basándote en la investigación anterior:
  • JURISDICTION_CODE debe convertirse en UInt8.
  • PARKS_NM debe convertirse en LowCardinality(String)
  • CMPLNT_FR_DT y CMPLNT_FR_TM siempre están rellenos (posiblemente con una hora predeterminada de 00:00:00)
  • CMPLNT_TO_DT y CMPLNT_TO_TM pueden estar vacíos
  • Las fechas y las horas se almacenan en campos separados en el origen
  • Las fechas tienen el formato mm/dd/yyyy
  • Las horas tienen el formato hh:mm:ss
  • Las fechas y las horas pueden concatenarse en tipos DateTime
  • Hay algunas fechas anteriores al 1 de enero de 1970, lo que significa que necesitamos un DateTime de 64 bits
Hay muchos más cambios que hacer en los tipos; todos pueden determinarse siguiendo los mismos pasos de investigación. Observa la cantidad de cadenas distintas en un campo, el mínimo y el máximo de los valores numéricos, y toma tus decisiones. El esquema de la tabla que se proporciona más adelante en la guía tiene muchas cadenas de baja cardinalidad y campos enteros sin signo, y muy pocos valores numéricos de coma flotante.

​
Concatenar los campos de fecha y hora

Para concatenar los campos de fecha y hora CMPLNT_FR_DT y CMPLNT_FR_TM en un único String que pueda convertirse en DateTime, seleccione ambos campos unidos por el operador de concatenación: CMPLNT_FR_DT || ' ' || CMPLNT_FR_TM. Los campos CMPLNT_TO_DT y CMPLNT_TO_TM se tratan de forma similar.
Query
Response

​
Convertir la cadena de fecha y hora a un tipo DateTime64

Anteriormente en la guía vimos que hay fechas en el archivo TSV anteriores al 1 de enero de 1970, lo que significa que necesitamos un tipo DateTime de 64 bits para esas fechas. Las fechas también deben convertirse del formato MM/DD/YYYY al formato YYYY/MM/DD. Ambas cosas pueden hacerse con parseDateTime64BestEffort().
Query
Las líneas 2 y 3 anteriores contienen la concatenación del paso anterior, y las líneas 4 y 5 anteriores convierten las cadenas en DateTime64. Como no se garantiza que exista la hora de finalización de la reclamación, se usa parseDateTime64BestEffortOrNull.
Response
Las fechas que aparecen arriba como 1925 se deben a errores en los datos. Hay varios registros en los datos originales con fechas en los años 1019 - 1022 que deberían ser 2019 - 2022. Se almacenan como 1 de enero de 1925, ya que esa es la fecha más antigua compatible con un DateTime de 64 bits.

​
Crear una tabla

Las decisiones tomadas anteriormente sobre los tipos de datos utilizados para las columnas se reflejan en el esquema de la tabla que se muestra a continuación. También debemos decidir qué ORDER BY y PRIMARY KEY se usarán en la tabla. Al menos uno de ORDER BY o PRIMARY KEY debe especificarse. A continuación, se ofrecen algunas pautas para decidir qué columnas incluir en ORDER BY; encontrarás más información en la sección Próximos pasos al final de este documento.

​
Cláusulas ORDER BY y PRIMARY KEY

  • La tupla ORDER BY debe incluir los campos que se usan en los filtros de las consultas
  • Para maximizar la compresión en disco, la tupla ORDER BY debe ordenarse por cardinalidad ascendente
  • Si existe, la tupla PRIMARY KEY debe ser un subconjunto de la tupla ORDER BY
  • Si solo se especifica ORDER BY, se usará la misma tupla como PRIMARY KEY
  • El índice de la clave primaria se crea usando la tupla PRIMARY KEY si se especifica; de lo contrario, la tupla ORDER BY
  • El índice PRIMARY KEY se mantiene en la memoria principal
Al observar el conjunto de datos y las preguntas que podrían responderse al consultarlo, podríamos decidir analizar los tipos de delitos reportados a lo largo del tiempo en los cinco distritos de la ciudad de Nueva York. Estos campos podrían incluirse entonces en el ORDER BY: Consultando el archivo TSV para obtener la cardinalidad de las tres columnas candidatas:
Query
Response
Al ordenar por cardinalidad, ORDER BY queda así:
La siguiente tabla usará nombres de columna más fáciles de leer; los nombres anteriores se corresponderán con
Al combinar los cambios en los tipos de datos y la tupla ORDER BY, se obtiene esta estructura de la tabla:

​
Cómo encontrar la clave primaria de una tabla

La base de datos system de ClickHouse, en concreto system.table, contiene toda la información sobre la tabla que acabas de crear. Esta consulta muestra el ORDER BY (clave de ordenación) y la PRIMARY KEY:
Respuesta

​
Preprocesamiento e importación de datos

Usaremos la herramienta clickhouse-local para el preprocesamiento de los datos y clickhouse-client para cargarlos.

​
Argumentos de clickhouse-local utilizados

table='input' aparece en los argumentos de clickhouse-local que se muestran a continuación. clickhouse-local toma la entrada proporcionada (cat ${HOME}/NYPD_Complaint_Data_Current__Year_To_Date_.tsv) e inserta esos datos en una tabla. De forma predeterminada, la tabla se llama table. En esta guía, el nombre de la tabla se establece en input para que el flujo de datos quede más claro. El argumento final de clickhouse-local es una consulta que selecciona datos de la tabla (FROM input) y luego se pasa por una tubería a clickhouse-client para rellenar la tabla NYPD_Complaint.

​
Valida los datos

El conjunto de datos cambia una o varias veces al año, por lo que es posible que tus recuentos no coincidan con lo que aparece en este documento.
Query
Response
El tamaño del conjunto de datos en ClickHouse es apenas el 12 % del archivo TSV original; compara el tamaño del archivo TSV original con el de la tabla:
Query
Response

​
Ejecuta algunas consultas

​
Consulta 1. Compare el número de quejas por mes

Query
Response

​
Consulta 2. Comparar el número total de quejas por distrito

Query
Response

​
Siguientes pasos

Una introducción práctica a los índices primarios dispersos en ClickHouse explica en qué se diferencia la indexación en ClickHouse de la de las bases de datos relacionales tradicionales, cómo ClickHouse crea y utiliza un índice primario disperso, y las buenas prácticas de indexación.
Última modificación el 3 de julio de 2026