Cómo instalar y utilizar ClickHouse en Ubuntu 20.04

El autor seleccionó el Fondo de Código Libre y Abierto para recibir una donación como parte del programa Write for DOnations.

Introducción

ClickHouse es una base de datos analítica de código abierto y orientada a columnas creada por Yandex para casos de uso de OLAP y big data. La compatibilidad de ClickHouse con el procesamiento de consultas en tiempo real la hace adecuada para aplicaciones que requieren resultados analíticos en menos de un segundo. El lenguaje de consulta de ClickHouse es un dialecto de SQL que permite potentes capacidades de consulta declarativa y, al mismo tiempo, ofrece familiaridad y una curva de aprendizaje más corta para el usuario final.

Las bases de datos orientadas a columnas almacenan registros en bloques agrupados por columnas en lugar de filas. Al no cargar datos de las columnas que no están presentes en la consulta, las bases de datos orientadas a columnas dedican menos tiempo a leer datos mientras completan consultas. Como resultado, estas bases de datos pueden calcular y devolver resultados mucho más rápido que los sistemas tradicionales basados ​​en filas para determinadas cargas de trabajo, como OLAP.

Los sistemas de procesamiento analítico en línea (OLAP) permiten organizar grandes cantidades de datos y realizar consultas complejas. Son capaces de gestionar petabytes de datos y devolver resultados de consultas rápidamente. De esta manera, OLAP resulta útil para trabajar en áreas como la ciencia de datos y el análisis empresarial.

En este tutorial, instalará el servidor y el cliente de la base de datos de ClickHouse en su equipo. Utilizará el DBMS para tareas típicas y, opcionalmente, habilitará el acceso remoto desde otro servidor para poder conectarse a la base de datos desde otra máquina. Luego, probará ClickHouse modelando y consultando datos de visitas a sitios web de ejemplo.

Prerrequisitos

• Un servidor Ubuntu 20.04 con un sudousuario no root habilitado y un firewall configurado. El servidor debe tener al menos 2 GB de RAM. Puedes seguir el tutorial de configuración inicial del servidor para crear el usuario y configurar el firewall.

• (Opcional) Un servidor Ubuntu 20.04 secundario con un sudousuario no root habilitado y una configuración de firewall.

Paso 1: Instalación de ClickHouse

En esta sección, instalará el servidor ClickHouse y los programas cliente mediante apt.

Primero, acceda a su servidor mediante SSH ejecutando:

ssh sammy@your_server_ip

Yandex mantiene un repositorio APT que contiene la última versión de ClickHouse. Agregue la clave GPG del repositorio para poder descargar de forma segura paquetes validados de ClickHouse:

sudo apt-key adv --keyserver keyserver.ubuntu.com --recv E0C56BD4

Verá un resultado similar al siguiente:

OutputExecuting: /tmp/apt-key-gpghome.JkkcKnBAFY/gpg.1.sh --keyserver keyserver.ubuntu.com --recv E0C56BD4gpg: key C8F1E19FE0C56BD4: public key "ClickHouse Repository Key milovidov@yandex-team.ru" importedgpg: Total number processed: 1gpg:               imported: 1

La salida confirma que se ha verificado y agregado correctamente la clave.

Agregue el repositorio a su lista de repositorios APT ejecutando:

echo "deb http://repo.yandex.ru/clickhouse/deb/stable/ main/" | sudo tee /etc/apt/sources.list.d/clickhouse.list

Aquí ha canalizado la salida de echoto sudo teepara que esta salida pueda imprimirse en un archivo propiedad de la raíz.

Ahora, actualiza tus paquetes:

sudo apt update

Los paquetes clickhouse-servery clickhouse-clientestarán ahora disponibles para su instalación. Instálelos con:

sudo apt install clickhouse-server clickhouse-client

Durante la instalación, se le pedirá que establezca una contraseña para el usuario predeterminado de ClickHouse.

Ha instalado correctamente el servidor y el cliente de ClickHouse. Ahora está listo para iniciar el servicio de base de datos y asegurarse de que se ejecuta correctamente.

Paso 2: Iniciar el servicio

El clickhouse-serverpaquete que instaló en la sección anterior crea un systemdservicio que realiza acciones como iniciar, detener y reiniciar el servidor de base de datos. systemdes un sistema de inicio para que Linux inicialice y administre servicios. En esta sección, iniciará el servicio y verificará que se esté ejecutando correctamente.

Inicie el clickhouse-serverservicio ejecutando:

sudo service clickhouse-server start

El comando anterior no mostrará ningún resultado. Para verificar que el servicio se esté ejecutando correctamente, ejecute:

sudo service clickhouse-server status

Verá un resultado similar al siguiente:

Output● clickhouse-server.service - ClickHouse Server (analytic DBMS for big data)     Loaded: loaded (/etc/systemd/system/clickhouse-server.service; enabled; vendor preset: enabled)     Active: active (running) since Wed 2020-09-16 05:18:54 UTC; 5s ago   Main PID: 2697 (clickhouse-serv)      Tasks: 46 (limit: 1137)     Memory: 459.7M     CGroup: /system.slice/clickhouse-server.service             └─2697 /usr/bin/clickhouse-server --config=/etc/clickhouse-server/config.xml --pid-file=/run/clickhouse-server/clickhouse-server.pid

La salida indica que el servidor está ejecutándose.

Ha iniciado correctamente el servidor ClickHouse y ahora podrá usar el clickhouse-clientprograma CLI para conectarse al servidor.

Paso 3: Creación de bases de datos y tablas

En ClickHouse, puede crear y eliminar bases de datos ejecutando instrucciones SQL directamente en el indicador de base de datos interactivo. Las instrucciones consisten en comandos que siguen una sintaxis particular que le indican al servidor de base de datos que realice una operación solicitada junto con los datos necesarios. Puede crear bases de datos utilizando la sintaxis. Para crear una base de datos, primero inicie una sesión de cliente ejecutando el siguiente comando:CREATE DATABASE table_name

clickhouse-client --password

Se le pedirá que ingrese la contraseña que había establecido durante la instalación; ingrésela para iniciar correctamente la sesión del cliente.

El comando anterior lo llevará al símbolo del sistema del cliente donde podrá ejecutar declaraciones SQL de ClickHouse para realizar acciones como:

• Crear, actualizar y eliminar bases de datos, tablas, índices, particiones y vistas.

• Ejecutar consultas para recuperar datos que se filtran y agrupan opcionalmente utilizando varias condiciones.

En este paso, con el cliente de ClickHouse listo para insertar datos, creará una base de datos y una tabla. Para los fines de este tutorial, creará una base de datos llamada testy dentro de ella creará una tabla llamada visitsque rastrea la duración de las visitas al sitio web.

Ahora que está dentro del símbolo del sistema de ClickHouse, cree su testbase de datos ejecutando:

CREATE DATABASE test;

Verá el siguiente resultado que muestra que ha creado la base de datos:

OutputCREATE DATABASE testOk.0 rows in set. Elapsed: 0.003 sec.

Una tabla de ClickHouse es similar a las tablas de otras bases de datos relacionales: contiene una colección de datos relacionados en un formato estructurado. Puede especificar columnas junto con sus tipos, agregar filas de datos y ejecutar diferentes tipos de consultas en las tablas.

La sintaxis para crear tablas en ClickHouse sigue esta estructura de ejemplo:

CREATE TABLE table_name(    column_name1 column_type [options],    column_name2 column_type [options],    ...) ENGINE = engine

Los valores table_namey column_namepueden ser cualquier identificador ASCII válido. ClickHouse admite una amplia gama de tipos de columnas; algunos de los más populares son:

• UInt64: se utiliza para almacenar valores enteros en el rango de 0 a 18446744073709551615.

• Float64: se utiliza para almacenar números de punto flotante como 2039.23, 10.5, etc.

• String: se utiliza para almacenar caracteres de longitud variable. No requiere un atributo de longitud máxima, ya que puede almacenar longitudes arbitrarias.

• Date: se utiliza para almacenar fechas que siguen el YYYY-MM-DDformato.

• DateTime:se utiliza para almacenar fechas junto con la hora y sigue el YYYY-MM-DD HH:MM:SSformato.

Después de las definiciones de columnas, se especifica el motor utilizado para la tabla. En ClickHouse, los motores determinan la estructura física de los datos subyacentes, las capacidades de consulta de la tabla, sus modos de acceso simultáneo y la compatibilidad con índices. Los distintos tipos de motores son adecuados para los distintos requisitos de la aplicación. El tipo de motor más utilizado y de mayor aplicación es MergeTree.

Ahora que tiene una descripción general de la creación de tablas, creará una tabla. Comience por confirmar la base de datos que modificará:

USE test;

Verá el siguiente resultado que indica que ha cambiado testde la base de datos a la defaultbase de datos:

OutputUSE testOk.0 rows in set. Elapsed: 0.001 sec.

El resto de esta guía asumirá que está ejecutando declaraciones dentro del contexto de esta base de datos.

Crea tu visitstabla ejecutando este comando:

CREATE TABLE visits (
 id UInt64,
 duration Float64,
 url String,
 created DateTime
) ENGINE = MergeTree() 
PRIMARY KEY id 
ORDER BY id;

A continuación, se muestra un desglose de lo que hace el comando. Se crea una tabla denominada visitsque tiene cuatro columnas:

• id: La columna de clave principal. De manera similar a otros sistemas RDBMS, una columna de clave principal en ClickHouse identifica de manera única una fila; cada fila debe tener un valor único para esta columna.

• duration:Una columna flotante utilizada para almacenar la duración de cada visita en segundos. floatLas columnas pueden almacenar valores decimales como 12,50.

• url:Una columna de cadena que almacena la URL visitada, como http://example.com.

• created:Una columna de fecha y hora que registra cuándo ocurrió la visita.

Después de las definiciones de columnas, se especifica MergeTreeel motor de almacenamiento para la tabla. La familia de motores MergeTree se recomienda para bases de datos de producción debido a su compatibilidad optimizada con grandes inserciones en tiempo real, solidez general y compatibilidad con consultas. Además, los motores MergeTree admiten la ordenación de filas por clave principal, la partición de filas y la replicación y el muestreo de datos.

Si desea utilizar ClickHouse para archivar datos que no se consultan con frecuencia o para almacenar datos temporales, puede utilizar la familia de motores Log para optimizar ese caso de uso.

Después de las definiciones de columnas, definirá otras opciones a nivel de tabla. La PRIMARY KEYcláusula establece idcomo clave principal la columna y ORDER BYalmacenará los valores ordenados por la idcolumna. Una clave principal identifica de forma única una fila y se utiliza para acceder de forma eficiente a una sola fila y para la colocación eficiente de filas.

Al ejecutar la declaración de creación, verá el siguiente resultado:

OutputCREATE TABLE visits(    id UInt64,    duration Float64,    url String,    created DateTime)ENGINE = MergeTree()PRIMARY KEY idORDER BY idOk.0 rows in set. Elapsed: 0.010 sec.

En esta sección, ha creado una base de datos y una tabla para realizar un seguimiento de los datos de visitas al sitio web. En el siguiente paso, insertará datos en la tabla, actualizará los datos existentes y eliminará esos datos.

Paso 4: inserción, actualización y eliminación de datos y columnas

En este paso, utilizará su visitstabla para insertar, actualizar y eliminar datos. El siguiente comando es un ejemplo de la sintaxis para insertar filas en una tabla de ClickHouse:

INSERT INTO table_name VALUES (column_1_value, column_2_value, ....);

Ahora, inserte algunas filas de datos de ejemplo de visitas a sitios web en su visitstabla ejecutando cada una de las siguientes declaraciones:

INSERT INTO visits VALUES (1, 10.5, 'http://example.com', '2019-01-01 00:01:01');

INSERT INTO visits VALUES (2, 40.2, 'http://example1.com', '2019-01-03 10:01:01');

INSERT INTO visits VALUES (3, 13, 'http://example2.com', '2019-01-03 12:01:01');

INSERT INTO visits VALUES (4, 2, 'http://example3.com', '2019-01-04 02:01:01');

Verá la siguiente salida repetida para cada declaración de inserción.

OutputINSERT INTO visits VALUESOk.1 rows in set. Elapsed: 0.004 sec.

La salida de cada fila muestra que la has insertado correctamente en la visitstabla.

Ahora agregará una columna adicional a la visitstabla. Al agregar o eliminar columnas de tablas existentes, ClickHouse admite la ALTERsintaxis.

Por ejemplo, la sintaxis básica para agregar una columna a una tabla es la siguiente:

ALTER TABLE table_name ADD COLUMN column_name column_type;

Agregue una columna llamada locationque almacenará la ubicación de las visitas a un sitio web ejecutando la siguiente declaración:

ALTER TABLE visits ADD COLUMN location String;

Verá un resultado similar al siguiente:

OutputALTER TABLE visits    ADD COLUMN    location StringOk.0 rows in set. Elapsed: 0.014 sec.

El resultado muestra que ha agregado la locationcolumna correctamente.

A partir de la versión 19.13.3, ClickHouse no admite la actualización y eliminación de filas de datos individuales debido a restricciones de implementación. Sin embargo, ClickHouse admite actualizaciones y eliminaciones en masa y tiene una sintaxis SQL específica para estas operaciones para resaltar su uso no estándar.

La siguiente sintaxis es un ejemplo de actualización masiva de filas:

ALTER TABLE table_name UPDATE  column_1 = value_1, column_2 = value_2 ...  WHERE  filter_conditions;

Ejecutará la siguiente declaración para actualizar la urlcolumna de todas las filas que tengan un durationvalor menor a 15. Introdúzcala en el indicador de la base de datos para ejecutarla:

ALTER TABLE visits UPDATE url = 'http://example2.com' WHERE duration  15;

La salida de la declaración de actualización masiva será la siguiente:

OutputALTER TABLE visits    UPDATE url = 'http://example2.com' WHERE duration  15Ok.0 rows in set. Elapsed: 0.003 sec.

El resultado muestra que la consulta de actualización se completó correctamente. El 0 rows in seten el resultado indica que la consulta no devolvió ninguna fila; esto será así para cualquier consulta de actualización y eliminación.

La sintaxis de ejemplo para eliminar filas en masa es similar a la actualización de filas y tiene la siguiente estructura:

ALTER TABLE table_name DELETE WHERE filter_conditions;

Para probar la eliminación de datos, ejecute la siguiente declaración para eliminar todas las filas que tengan un durationvalor menor que 5:

ALTER TABLE visits DELETE WHERE duration  5;

La salida de la declaración de eliminación masiva será similar a:

OutputALTER TABLE visits    DELETE WHERE duration  5Ok.0 rows in set. Elapsed: 0.003 sec.

La salida confirma que ha eliminado las filas con una duración de menos de cinco segundos.

Para eliminar columnas de su tabla, la sintaxis seguiría esta estructura de ejemplo:

ALTER TABLE table_name DROP COLUMN column_name;

Elimina la locationcolumna que agregaste anteriormente ejecutando lo siguiente:

ALTER TABLE visits DROP COLUMN location;

La DROP COLUMNsalida que confirma que ha eliminado la columna será la siguiente:

OutputALTER TABLE visits    DROP COLUMN    location StringOk.0 rows in set. Elapsed: 0.010 sec.

Ahora que ha insertado, actualizado y eliminado correctamente filas y columnas en su visitstabla, pasará a consultar datos en el siguiente paso.

Paso 5: Consulta de datos

El lenguaje de consulta de ClickHouse es un dialecto personalizado de SQL con extensiones y funciones adecuadas para cargas de trabajo de análisis. En este paso, ejecutará consultas de selección y agregación para recuperar datos y resultados de su visitstabla.

Las consultas de selección le permiten recuperar filas y columnas de datos filtrados por las condiciones que especifique, junto con opciones como la cantidad de filas que se devolverán. Puede seleccionar filas y columnas de datos mediante la SELECTsintaxis. La sintaxis básica para SELECTlas consultas es:

SELECT func_1(column_1), func_2(column_2) FROM table_name WHERE filter_conditions row_options;

Ejecute la siguiente declaración para recuperar urllos durationvalores de las filas donde urlestá http://example.com.

SELECT url, duration FROM visits WHERE url = 'http://example2.com' LIMIT 2;

Verá el siguiente resultado:

OutputSELECT    url,    durationFROM visitsWHERE url = 'http://example2.com'LIMIT 2┌─url─────────────────┬─duration─┐│ http://example2.com │     10.5 │└─────────────────────┴──────────┘┌─url─────────────────┬─duration─┐│ http://example2.com │       13 │└─────────────────────┴──────────┘2 rows in set. Elapsed: 0.013 sec.

El resultado ha devuelto dos filas que coinciden con las condiciones que especificó. Ahora que ha seleccionado los valores, puede continuar con la ejecución de consultas de agregación.

Las consultas de agregación son consultas que operan sobre un conjunto de valores y devuelven valores de salida únicos. En las bases de datos de análisis, estas consultas se ejecutan con frecuencia y están bien optimizadas por la base de datos. Algunas funciones de agregación compatibles con ClickHouse son:

• count: devuelve el recuento de filas que coinciden con las condiciones especificadas.

• sum: devuelve la suma de los valores de la columna seleccionada.

• avg: devuelve el promedio de los valores de la columna seleccionada.

Algunas funciones agregadas específicas de ClickHouse incluyen:

• uniq: devuelve un número aproximado de filas distintas coincidentes.

• topK: devuelve una matriz de los valores más frecuentes de una columna específica utilizando un algoritmo de aproximación.

Para demostrar la ejecución de consultas de agregación, calculará la duración total de las visitas ejecutando la sumconsulta:

SELECT SUM(duration) FROM visits;

Verá un resultado similar al siguiente:

OutputSELECT SUM(duration)FROM visits┌─SUM(duration)─┐│          63.7 │└───────────────┘1 rows in set. Elapsed: 0.010 sec.

Ahora, calcula las dos URL principales ejecutando:

SELECT topK(2)(url) FROM visits;

Verá un resultado similar al siguiente:

OutputSELECT topK(2)(url)FROM visits┌─topK(2)(url)──────────────────────────────────┐│ ['http://example2.com','http://example1.com'] │└───────────────────────────────────────────────┘1 rows in set. Elapsed: 0.010 sec.

Ahora que ha consultado su visitstabla correctamente, eliminará tablas y bases de datos en el siguiente paso.

Paso 6: eliminar tablas y bases de datos

En esta sección, eliminará su visitstabla y testbase de datos.

La sintaxis para eliminar tablas sigue este ejemplo:

DROP TABLE table_name;

Para eliminar la visitstabla, ejecute la siguiente instrucción:

DROP TABLE visits;

Verá el siguiente resultado declarando que ha eliminado la tabla exitosamente:

outputDROP TABLE visitsOk.0 rows in set. Elapsed: 0.005 sec.

Puede eliminar bases de datos mediante la sintaxis. Para eliminar la base de datos, ejecute la siguiente instrucción:DROP database table_nametest

DROP DATABASE test;

El resultado muestra que has eliminado la base de datos correctamente.

OutputDROP DATABASE testOk.0 rows in set. Elapsed: 0.003 sec.

En este paso, ha eliminado tablas y bases de datos. Ahora que ha creado, actualizado y eliminado bases de datos, tablas y datos en su instancia de ClickHouse, habilitará el acceso remoto a su servidor de base de datos en la siguiente sección.

Paso 7: Configuración de reglas de firewall (opcional)

Si solo tiene intención de utilizar ClickHouse de forma local con aplicaciones ejecutándose en el mismo servidor, o no tiene un firewall habilitado en su servidor, no necesita completar esta sección. Si, en cambio, se conectará al servidor de base de datos de ClickHouse de forma remota, debe seguir este paso.

Actualmente, su servidor tiene habilitado un firewall que impide que su dirección IP pública acceda a todos los puertos. Deberá completar los dos pasos siguientes para permitir el acceso remoto:

• Modificar la configuración de ClickHouse y permitirle escuchar en todas las interfaces.

• Agregue una regla de firewall que permita conexiones entrantes al puerto 8123, que es el puerto HTTP que ejecuta el servidor ClickHouse.

Si está dentro del indicador de base de datos, salga escribiendo CTRL+D.

Edite el archivo de configuración ejecutando:

sudo nano /etc/clickhouse-server/config.xml

A continuación, descomente la línea que contiene !-- listen_host0.0.0.0/listen_host --, como en el siguiente archivo:

/etc/clickhouse-server/config.xml

... interserver_http_hostexample.yandex.ru/interserver_http_host    --    !-- Listen specified host. use :: (wildcard IPv6 address), if you want to accept connections both with IPv4 and IPv6 from everywhere. --    !-- listen_host::/listen_host --    !-- Same for hosts with disabled ipv6: --    listen_host0.0.0.0/listen_host    !-- Default values - try listen localhost on ipv4 and ipv6: --    !--    listen_host::1/listen_host    listen_host127.0.0.1/listen_host    --...

Guarde el archivo y salga. Para que se aplique la nueva configuración, reinicie el servicio ejecutando lo siguiente:

sudo service clickhouse-server restart

No verá ningún resultado de este comando. El servidor de ClickHouse escucha en el puerto 8123las conexiones HTTP y en el puerto 9000las conexiones desde clickhouse-client. Permita el acceso a ambos puertos para la dirección IP de su segundo servidor con el siguiente comando:

sudo ufw allow from second_server_ip/32 to any port 8123

sudo ufw allow from second_server_ip/32 to any port 9000

Verá el siguiente resultado para ambos comandos que muestra que ha habilitado el acceso a ambos puertos:

OutputRule added

A partir de ahora se podrá acceder a ClickHouse desde la dirección IP que hayas añadido. Si lo deseas, puedes añadir otras direcciones IP, como la dirección de tu equipo local.

Para verificar que puede conectarse al servidor ClickHouse desde la máquina remota, primero siga los pasos del Paso 1 de este tutorial en el segundo servidor y asegúrese de tenerlo instalado clickhouse-clienten él.

Ahora que ha iniciado sesión en el segundo servidor, inicie una sesión de cliente ejecutando:

clickhouse-client --host your_server_ip --password

Verá el siguiente resultado que muestra que se ha conectado correctamente al servidor:

OutputClickHouse client version 19.13.3.26 (official build).Password for user (default):Connecting to your_server_ip:9000 as user default.Connected to ClickHouse server version 19.13.3 revision 54425.hostname :)

En este paso, habilitó el acceso remoto a su servidor de base de datos de ClickHouse ajustando las reglas de su firewall.

Conclusión

Ha configurado correctamente una instancia de base de datos de ClickHouse en su servidor y ha creado una base de datos y una tabla, ha añadido datos, ha realizado consultas y ha eliminado la base de datos. En la documentación de ClickHouse puede leer acerca de sus comparaciones con otras bases de datos de análisis comerciales y de código abierto, así como documentos de referencia generales.

Otras características que ofrece ClickHouse incluyen el procesamiento de consultas distribuidas entre múltiples servidores para mejorar el rendimiento y proteger contra la pérdida de datos al almacenarlos en diferentes fragmentos.