Cómo instalar y usar ClickHouse en Ubuntu 18.04 LTS

Introducción

ClickHouse es una base de datos analítica de código abierto orientada a columnas creada por Yandex para casos de uso de OLAP y big data. El soporte de ClickHouse para el procesamiento de consultas en tiempo real lo hace adecuado para aplicaciones que requieren resultados analíticos en un segundo. ClickHouse Query Language es un dialecto de SQL que permite poderosas capacidades de consulta declarativa al tiempo que ofrece familiaridad y una curva de aprendizaje más pequeña para el usuario final.

Las bases de datos orientadas a columnas almacenan registros en bloques agrupados por columnas en lugar de por filas. Al no cargar datos para las columnas que están ausentes en la consulta, las bases de datos orientadas a columnas tardan menos en leer los datos durante la finalización de la consulta. Como resultado, estas bases de datos pueden calcular y devolver resultados mucho más rápido que los sistemas tradicionales basados en filas para ciertas cargas de trabajo, como OLAP.

Los sistemas de procesamiento de análisis en línea (OLAP) le permiten organizar grandes cantidades de datos y realizar consultas complejas. Puedo manejar petabytes de datos y devolver rápidamente los resultados de las consultas. De esta manera, OLAP es útil para trabajar en áreas como la ciencia de datos y el análisis empresarial.

En este tutorial, instalará el servidor de base de datos y el cliente ClickHouse en su computadora. Utilizará el DBMS para tareas típicas y, opcionalmente, habilitará el acceso remoto desde otro servidor para que pueda conectarse a la base de datos desde otra máquina. Luego, probará ClickHouse modelando y consultando los datos de visita del sitio web de muestra.

Prerrequisitos

Un servidor Ubuntu 18.04 con un usuario sudo no root habilitado.

Si desea instalar ClickHouse en un servidor remoto, continúe leyendo; de lo contrario, omita el primer párrafo "Conectarse al servidor" y lea el siguiente.

Conexión al servidor

Para acceder al servidor, necesita conocer la dirección IP. También necesitará su nombre de usuario y contraseña para la autenticación. Para conectarse al servidor como root, escriba el siguiente comando:

ssh root@IP_DEL_SERVER

A continuación, deberá ingresar la contraseña del usuario root.

Si no está utilizando el usuario root, puede iniciar sesión con otro nombre de usuario utilizando el mismo comando, luego cambie root a su nombre de usuario:

ssh nome_utente@IP_DEL_SERVER

Luego se le pedirá que ingrese su contraseña de usuario.

El puerto estándar para conectarse a través de ssh es 22, si su servidor usa un puerto diferente, deberá especificarlo usando el parámetro -p, luego escriba el siguiente comando:

ssh nome_utente@IP_DEL_SERVER -p PORTA

Paso 1: Instale ClickHouse

En esta sección, instalará el servidor de ClickHouse y los programas cliente utilizando apt-get.

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

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

Verá un resultado similar al siguiente:

Executing: /tmp/apt-key-gpghome.JkkcKnBAFY/gpg.1.sh --keyserver keyserver.ubuntu.com --recv E0C56BD4

 gpg: key C8F1E19FE0C56BD4: public key "ClickHouse Repository Key <[email protected]>" imported
 gpg: Total number processed: 1
 gpg: imported: 1

El resultado confirma que ha verificado y agregado correctamente la clave.

Agregue el repositorio a la lista de repositorios APT ejecutando:

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

Ahora emita el apt-get update para actualizar sus paquetes:

sudo apt-get update

Los clickhouse-server y clickhouse-client ahora estarán disponibles para su instalación. Instálelos con:

sudo apt-get install -y clickhouse-server clickhouse-client

El servidor y el cliente de ClickHouse se han instalado correctamente. Ahora está listo para iniciar el servicio de base de datos y asegurarse de que esté funcionando correctamente.

Paso 2: iniciar el servicio

El clickhouse-server instalado en la sección anterior crea un systemd que realiza acciones como iniciar, detener y reiniciar el servidor de la base de datos. systemd es un sistema de inicio de Linux para inicializar y administrar servicios. En esta sección iniciarás el servicio y verificarás que funciona correctamente.

Inicie el servicio clickhouse-server ejecutando este comando:

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:

● 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 Mon 2020-03-09 20:19:14 CET; 2s ago
 Main PID: 2549 (clickhouse-serv)
 Tasks: 40 (limit: 2298)
 CGroup: /system.slice/clickhouse-server.service
 └─2549 /usr/bin/clickhouse-server --config=/etc/clickhouse-server/config.xml --pid-file=/run/clickhouse-server/clickhouse-server.pi

La salida indica que el servidor se está ejecutando.

El servidor de ClickHouse se ha iniciado correctamente y ahora podrá utilizar el clickhouse-client para conectarse al servidor.

Paso 3: crear bases de datos y tablas

En ClickHouse, puede crear y eliminar bases de datos ejecutando sentencias SQL directamente en el indicador interactivo de la base de datos. Las instrucciones consisten en comandos que siguen una sintaxis particular que instruyen al servidor de la base de datos para realizar una operación solicitada junto con todos los datos requeridos. Puede crear bases de datos utilizando la sintaxis CREATE DATABASE table_name Para crear una base de datos, primero inicie una sesión de cliente ejecutando el siguiente comando, si ingresó una contraseña durante el proceso de instalación:

clickhouse-client --password

Si no ha ingresado una contraseña, simplemente escriba este comando:

clickhouse-client

Este comando accederá al indicador del cliente donde puede ejecutar instrucciones SQL ClickHouse para realizar acciones como:

Crear, actualizar y eliminar bases de datos, tablas, índices, particiones y vistas.
Consulta para recuperar datos que opcionalmente se filtran y agrupan utilizando varias condiciones.

En este paso, con el cliente ClickHouse listo para la entrada de datos, creará una base de datos y una tabla. Para los propósitos de este tutorial, creará una base de datos llamada test y dentro de ella creará una tabla llamada visits que rastrea la duración de las visitas al sitio web.

Ahora que está en el símbolo del sistema de ClickHouse, cree su base de datos de test

CREATE DATABASE test;

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

CREATE DATABASE test

 Ok.

 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 con sus tipos, agregar filas de datos y realizar diferentes tipos de consultas en 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 table_name y column_name valores pueden ser cualquier identificador ASCII válidos. ClickHouse admite una amplia variedad de tipos de columnas; algunos de los más populares son:

UInt64: se utiliza para almacenar valores enteros entre 0 y 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 siguiendo el YYYY-MM-DD.
DateTime: se utiliza para almacenar las fechas asociadas con la hora y sigue el formato YYYY-MM-DD HH:MM:SS.

Después de las definiciones de columna, 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 concurrente y la compatibilidad con índices. Los diferentes tipos de motores son adecuados para diferentes requisitos de 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 confirmando la base de datos que estará editando:

USE test;

Verá el siguiente resultado que muestra que ha cambiado a la base de datos de test desde la base de datos default

USE test

 Ok.

 0 rows in set. Elapsed: 0.001 sec.

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

Crea tu visits ejecutando este comando:

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

Aquí hay un desglose de lo que hace el comando. Creamos una tabla llamada visits que tiene cuatro columnas:

id: la columna de la clave principal. Al igual que otros sistemas RDBMS, una columna de clave principal en ClickHouse identifica de forma única una fila; cada fila debe tener un valor único para esta columna.
duration: una columna flotante que se utiliza para almacenar la duración de cada visita en segundos. float columnas flotantes pueden almacenar valores decimales como 12,50.
url: una columna de cadena que almacena la URL visitada, por ejemplo, http://example.com.
created: Una columna de fecha y hora que rastrea cuándo ocurrió la visita.

Después de las definiciones de columna, MergeTree se especifica como el motor de almacenamiento para la tabla. La familia de motores MergeTree se recomienda para bases de datos de producción con soporte optimizado para inserciones grandes en tiempo real, solidez general y soporte de consultas. Además, los motores MergeTree admiten la clasificación de filas de claves primarias, la partición de filas y la replicación y muestreo de datos.

Si tiene la intención de utilizar ClickHouse para archivar datos que no se solicitan con frecuencia o para archivar datos temporales, puede utilizar la familia Log Engine para optimizar para ese caso de uso.

Después de las definiciones de columna, definirá otras opciones a nivel de tabla. La PRIMARY KEY id como la columna de clave principal y la ORDER BY almacenará los valores ordenados por columna id Una clave principal identifica de forma única una fila y se utiliza para un acceso eficiente a una sola fila y una ubicación eficiente de la fila.

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

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

 Ok.

 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á los datos en la tabla, actualizará los datos existentes y eliminará esos datos.

Paso 4: inserte, actualice y elimine datos y columnas

En este paso, utilizará la visits 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 visitas al sitio web de muestra en la visits ejecutando cada una de las siguientes instrucciones:

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.

INSERT INTO visits VALUES

 Ok.

 1 rows in set. Elapsed: 0.004 sec.

El resultado de cada fila muestra que lo ingresó correctamente en la tabla de visits

Ahora agregará una columna adicional a la tabla de visits Al agregar o eliminar columnas de tablas existentes, ClickHouse admite la sintaxis ALTER

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 location que almacenará la ubicación de las visitas a un sitio web haciendo la siguiente declaración:

ALTER TABLE visits ADD COLUMN location String;

Verá un resultado similar al siguiente:

ALTER TABLE visits
 ADD COLUMN
 location String


 Ok.

 0 rows in set. Elapsed: 0.014 sec.

El resultado muestra que ha location a la columna.

A partir de la versión 19.3.6, 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 masivas y tiene una sintaxis SQL separada para estas operaciones para resaltar su uso no estándar.

La siguiente sintaxis es un ejemplo de una 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 url de todas las filas que tengan un duration menor a 15. Introdúzcalo en el indicador de la base de datos para ejecutarlo:

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

El resultado de la declaración de actualización masiva será el siguiente:

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


 Ok.

 0 rows in set. Elapsed: 0.003 sec.

El resultado muestra que la consulta de actualización se completó correctamente. Las 0 rows in set salida del conjunto indican que la consulta no devolvió filas; este será el caso con las actualizaciones y eliminaciones de consultas.

La sintaxis de muestra para la eliminación masiva de filas 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 con un duration inferior a 5:

ALTER TABLE visits DELETE WHERE duration < 5;

La salida de la declaración de eliminación masiva se verá así:

ALTER TABLE visits
 DELETE WHERE duration < 5


 Ok.

 0 rows in set. Elapsed: 0.003 sec.

La salida confirma que ha eliminado líneas con una duración de menos de cinco segundos.

Para eliminar columnas de la tabla, la sintaxis debe seguir esta estructura de ejemplo:

ALTER TABLE table_name DROP COLUMN column_name;

Elimine la location que agregó anteriormente haciendo lo siguiente:

ALTER TABLE visits DROP COLUMN location;

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

ALTER TABLE visits
 DROP COLUMN
 location String


 Ok.

 0 rows in set. Elapsed: 0.010 sec.

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

Paso 5: consulta los datos

ClickHouse Query Language es un dialecto SQL personalizado con extensiones y funciones adecuadas para cargas de trabajo analíticas. En este paso, ejecutará consultas seleccionadas y agregadas para recuperar datos y resultados de la tabla de visits

Las consultas de selección le permiten recuperar filas y columnas de datos que se filtran en función de condiciones específicas, junto con opciones como el número de filas para devolver. Puede seleccionar filas y columnas de datos utilizando la sintaxis SELECT La sintaxis básica de las SELECT 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 la url y los duration para las filas donde la url es http://example.com.

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

Verá el siguiente resultado:

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

 ┌─url─────────────────┬─duration─┐
 │ http://example2.com │ 10.5 │
 │ http://example2.com │ 13 │
 └─────────────────────┴──────────┘

 2 rows in set. Elapsed: 0.003 sec.

La salida devolvió dos líneas que coinciden con las condiciones especificadas. Ahora que ha seleccionado los valores, puede continuar con la ejecución de consultas agregadas.

Las consultas agregadas son consultas que operan en 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 son optimizadas por la base de datos. Algunas funciones agregadas admitidas por 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 columna seleccionados.

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 las consultas de agregación, calculará la duración total de las visitas ejecutando la sum la consulta:

SELECT SUM(duration) FROM visits;

Verá un resultado similar al siguiente:

SELECT SUM(duration)
 FROM visits

 ┌─SUM(duration)─┐
 │ 76.7 │
 └───────────────┘

 1 rows in set. Elapsed: 0.005 sec.

Ahora, calcule las dos primeras URL ejecutando:

SELECT topK(2)(url) FROM visits;

Verá un resultado similar al siguiente:

SELECT 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 con éxito su visits, eliminará tablas y bases de datos en el siguiente paso.

Paso 6: eliminar tablas y bases de datos

En esta sección, eliminará la tabla de visits y la base de datos de test

La sintaxis para eliminar tablas sigue este ejemplo:

DROP TABLE table_name;

Para eliminar la visits, ejecute la siguiente declaración:

DROP TABLE visits;

Verá el siguiente resultado que indica que ha eliminado correctamente la tabla:

DROP TABLE visits

 Ok.

 0 rows in set. Elapsed: 0.005 sec.

Puede eliminar bases de datos utilizando la sintaxis DROP database table_nametest Para eliminar la base de datos, ejecute la siguiente declaración:

DROP DATABASE test;

El resultado resultante muestra que ha eliminado correctamente la base de datos.

DROP DATABASE test

 Ok.

 0 rows in set. Elapsed: 0.003 sec.

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

Paso 7: configure las reglas de su firewall (opcional)

Si tiene la intención de utilizar ClickHouse solo localmente con aplicaciones que se ejecutan en el mismo servidor o un firewall no está habilitado en el servidor de Ubuntu, no es necesario que complete esta sección. Si se está conectando al servidor de base de datos de ClickHouse de forma remota, debe seguir este paso.

Su servidor tiene actualmente habilitado un cortafuegos que impide que su dirección IP pública acceda a todos los puertos. Completará los siguientes dos pasos para permitir el acceso remoto:

Cambie la configuración de ClickHouse y permita que escuche en todas las interfaces.
Agregue una regla de firewall que permita conexiones entrantes al puerto 8123, que es el puerto HTTP en el que se ejecuta el servidor ClickHouse.

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

Edite el archivo de configuración ejecutando:

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

Luego, descomente la línea que contiene , como el siguiente archivo:

...
 <interserver_http_host>example.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_host>0.0.0.0</listen_host>

 <!-- Default values - try listen localhost on ipv4 and ipv6: -->
 <!--
 <listen_host>::1</listen_host>
 <listen_host>127.0.0.1</listen_host>
 -->...

Guarde el archivo y salga de nano. Para aplicar la nueva configuración, reinicie el servicio ejecutando:

sudo service clickhouse-server restart

No verá ningún resultado de este comando. El servidor ClickHouse escucha en el puerto 8123 para las conexiones HTTP y en el puerto 9000 para 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:

Rule added

ClickHouse ahora será accesible desde la IP que agregó. No dude en agregar direcciones IP adicionales, como la dirección de su computadora local, si es necesario.

Para verificar que puede conectarse al servidor de ClickHouse desde la computadora remota, primero siga los pasos del paso 1 de este tutorial en el segundo servidor y asegúrese de tener la clickhouse-client en él.

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

clickhouse-client --host your_server_ip

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

ClickHouse client version 19.3.6.
 Connecting to your_server_ip:9000 as user default.
 Connected to ClickHouse server version 19.3.6 revision 54415.

 hostname

En este paso, ha habilitado el acceso remoto al servidor de la base de datos de ClickHouse modificando las reglas del firewall.

Conclusión

Ha configurado con éxito una instancia de base de datos ClickHouse en su servidor y ha creado una base de datos y una tabla, ha agregado datos, ha consultado y eliminado la base de datos. En la documentación de ClickHouse puede leer sus puntos de referencia en comparación con otras bases de datos de análisis comerciales y de código abierto y documentos de referencia general.