Como instalar e utilizar o ClickHouse no Debian 10

O autor selecionou o Free and Open Source Fund para receber uma doação como parte do programa Write for DOnations.

Introdução

O ClickHouse é um banco de dados de análise orientado por colunas de código aberto, elaborado pela Yandex para o OLAP e casos de uso de big data. O suporte para o processamento de consulta em tempo real do ClickHouse torna-o adequado para aplicativos que requerem resultados analíticos rápidos. A linguagem de consulta do ClickHouse é um dialeto do SQL, que oferece capacidades a consultas declarativas poderosas, ao mesmo tempo em que oferece familiaridade e uma curva de aprendizagem menor para o usuário final.

Bancos de dados orientados por coluna armazenam registros em blocos, agrupados por colunas, em vez de linhas. Por não carregar os dados para colunas ausentes na consulta, os bancos de dados orientados por coluna gastam menos tempo lendo os dados ao completar consultas. Como resultado, esses bancos de dados podem calcular e retornar resultados muito mais rapidamente que os sistemas tradicionais baseados em linha para certas cargas de trabalho, como o OLAP.

Os sistemas de Processamento analítico online (OLAP) permitem a organização de grandes quantidades de dados e a realização de consultas complexas. Eles são capazes de gerenciar dados na ordem dos petabytes e retornar rapidamente os resultados da consulta. Desta maneira, o OLAP é útil para o trabalho em áreas como a ciência de dados e a análise de negócios.

Neste tutorial, você instalará o servidor e o cliente do banco de dados do ClickHouse na sua máquina. Você usará o DBMS para tarefas típicas e, de maneira opcional, habilitará o acesso remoto de outro servidor para que consiga se conectar ao banco de dados de outra máquina. Depois, você testará o ClickHouse, modelando e consultando dados exemplares de visita em sites.

Pré-requisitos

• Um Debian 10 com um usuário sudo não root habilitado e um firewall configurado. Você pode seguir o tutorial de configuração inicial do servidor para criar o usuário e configurar o firewall.
• (Opcional) Um Debian 10 secundário com um usuário sudo não root habilitado e um firewall configurado. Você pode seguir o tutorial de configuração inicial do servidor.

Passo 1 — Instalando o ClickHouse

Nesta seção, você instalará o servidor e os programas de cliente do ClickHouse usando o apt.

Primeiro, use o SSH para entrar no seu servidor, executando:

ssh sammy@your_server_ip

O dirmngr é um servidor para gerenciar certificados e chaves. Ele é necessário para adicionar e verificar chaves do repositório remoto, instale-o executando:

sudo apt install dirmngr

O Yandex mantém um repositório APT que possui a versão mais recente do ClickHouse. Adicione a chave GPG do repositório para que você consiga baixar os pacotes do ClickHouse validados com segurança:

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

Você verá um resultado similar ao seguinte:

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

gpg: key C8F1E19FE0C56BD4: public key "ClickHouse Repository Key <milovidov@yandex-team.ru>" imported
gpg: Total number processed: 1
gpg:               imported: 1

O resultado confirma que ele verificou e adicionou a chave com sucesso.

Adicione o repositório à sua lista de repositórios APT, executando:

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

Aqui, você canalizou o resultado do echo para o sudo tee, de forma que esse resultado consiga ser impresso em um arquivo que tenha permissões root.

Agora, execute o apt update para atualizar seus pacotes:

sudo apt update

Agora, os pacotes clickhouse-server e clickhouse-client estarão disponíveis para a instalação.

Considerando a versão 19.13.3 do ClickHouse, certas configurações do OpenSSL 1.1.1, como o do MinProtocol e o CipherVersion, não são lidas corretamente. Para resolver essa incompatibilidade, modifique o arquivo de configuração do OpenSSL e transforme em comentário a linha ssl_conf = ssl_sect em /etc/ssl/openssl.cnf.

Edite o arquivo de configuração executando:

sudo nano /etc/ssl/openssl.cnf

Depois, transforme em comentário a linha que contém ssl_conf = ssl_sect, para que se pareça com o seguinte arquivo:

...

tsa_name		= yes	# Must the TSA name be included in the reply?
				# (optional, default: no)
ess_cert_id_chain	= no	# Must the ESS cert id chain be included?
				# (optional, default: no)
ess_cert_id_alg		= sha1	# algorithm to compute certificate
				# identifier (optional, default: sha1)
[default_conf]
#ssl_conf = ssl_sect

[ssl_sect]
...

Agora que a configuração do OpenSSL foi corrigida, você está pronto para instalar os pacotes de servidor e cliente do ClickHouse. Instale-os com:

sudo apt install clickhouse-server clickhouse-client

Durante a instalação, também será solicitado que você defina uma senha para o usuário padrão do ClickHouse.

Você instalou o servidor e o cliente do ClickHouse com sucesso. Agora, está pronto para iniciar o serviço de banco de dados e garantir que ele esteja funcionando corretamente.

Passo 2 — Inicializando o serviço

O pacote clickhouse-server que você instalou na seção anterior cria um serviço systemd, que executa ações como a inicialização, a interrupção e a reinicialização do servidor do banco de dados. O systemd é um sistema de inicialização para o Linux, usado para inicializar e gerenciar serviços. Nesta seção, você iniciará o serviço e verificará se ele está funcionando corretamente.

Inicie o serviço clickhouse-server, executando:

sudo service clickhouse-server start

O comando anterior não exibirá nenhum resultado. Para verificar se o serviço está funcionando corretamente, execute:

sudo service clickhouse-server status

Você verá um resultado similar ao seguinte:

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 Sat 2018-12-22 07:23:20 UTC; 1h 9min ago
 Main PID: 27101 (ClickHouse-serv)
    Tasks: 34 (limit: 1152)
   CGroup: /system.slice/ClickHouse-server.service
           └─27101 /usr/bin/ClickHouse-server --config=/etc/ClickHouse-server/config.xml

O resultado registra que o servidor está funcionando.

Você iniciou o servidor ClickHouse com sucesso e agora poderá usar o programa CLI clickhouse-client para se conectar ao servidor.

Passo 3 — Criando bancos de dados e tabelas

No ClickHouse, você pode criar e excluir bancos de dados executando as instruções SQL diretamente no prompt interativo do banco de dados. As instruções consistem em comandos que seguem uma sintaxe em particular e dizem ao servidor do banco de dados para realizar uma operação solicitada, junto com quaisquer outros dados necessários. Você pode criar bancos de dados usando a sintaxe CREATE DATABASE table_name. Para criar um banco de dados , primeiro inicialize a sessão do cliente executando o seguinte comando:

clickhouse-client --password

Será solicitado que você digite a senha definida durante a instalação — digite-a para iniciar a sessão do cliente com sucesso.

O comando anterior fará com que você logue-se no prompt do cliente, onde poderá executar as instruções do ClickHouse SQL para realizar ações como:

• Criar, atualizar e excluir bancos de dados, tabelas, indexações, partições e visualizações.

• Execute consultas para recuperar dados filtrados e agrupados de maneira opcional usando várias condições.

Neste passo, com o cliente ClickHouse pronto para inserir os dados, você criará um banco de dados e uma tabela. Para os fins deste tutorial, você criará um banco de dados chamado test, dentro do qual criará uma tabela chamada visits, que rastreia as durações das visitas ao site.

Agora que está dentro do prompt de comando do ClickHouse, crie seu banco de dados test executando:

CREATE DATABASE test;

Você verá o seguinte resultado, que mostra que o banco de dados que foi criado:

Output
CREATE DATABASE test

Ok.

0 rows in set. Elapsed: 0.003 sec.

Uma tabela ClickHouse é semelhante às tabelas em outros bancos de dados relacionais; ela possui uma coleção de dados relacionados em um formato estruturado. Você pode especificar colunas junto com seus tipos, adicionar linhas de dados e executar diferentes tipos de consultas nas tabelas.

A sintaxe para a criação de tabelas no ClickHouse segue esta estrutura de exemplo:

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

Os valores de table_name e column_name podem ser qualquer identificador válido em ASCII. O ClickHouse suporta uma ampla gama de tipos de coluna; alguns dos mais populares são:

• UInt64: usado para armazenar valores inteiros na faixa 0 a 18446744073709551615.

• Float64: usado para armazenar números de ponto flutuante, tal como 2039.23, 10.5, etc.

• String: usado para armazenar caracteres de comprimento de variável. Ele não exige um atributo de comprimento máximo, pois pode armazenar comprimentos arbitrários.

• Data: usado para armazenar datas que seguem o formato YYYY-MM-DD.

• DateTime: usado para armazenar datas, anexadas à hora e segue o formato YYYY-MM-DD HH:MM:SS.

Após as definições de coluna, é especificado o mecanismo usado para a tabela. No ClickHouse, os Mecanismos determinam a estrutura física dos dados subjacentes, as capacidades de consulta da tabela, seus modos de acesso e suporte para indexações. Diferentes tipos de mecanismos são adequados para diferentes requisitos de aplicativo. O tipo de mecanismo mais comumente usado e amplamente aplicável é o MergeTree.

Agora que tem um panorama geral da criação de tabelas, você criará uma tabela. Comece confirmando o banco de dados que estará modificando:

USE test;

Você verá o seguinte resultado, mostrando que você mudou para o banco de dados test do banco de dados padrão:

Output
USE test

Ok.

0 rows in set. Elapsed: 0.001 sec.

O resto deste guia presume que você esteja executando as instruções dentro do contexto deste banco de dados.

Crie sua tabela visits executando este comando:

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

Aqui está um resumo do que o comando faz. Você cria uma tabela chamada visits que tem quatro colunas:

• id: a coluna de chave primária. De maneira similar a outros sistemas RDBMS, uma coluna de chave primária no ClickHouse identifica uma linha; cada linha deve ter um valor único para essa coluna.

• duration: uma coluna float usada para armazenar a duração de cada visita em segundos. As colunas float podem armazenar valores decimais, como 12.50.

• url: uma coluna de strings que armazena o URL visitado, tal como http://example.com.

• created: uma coluna de data e hora que rastreia quando a visita ocorreu.

Após as definições de coluna, você especifica o MergeTree como o mecanismo de armazenamento para a tabela. A família MergeTree de mecanismos é recomendada para os bancos de dados de produção, devido ao seu suporte otimizado para as inserções em tempo real, robustez de uma forma geral e suporte a consultas. Além disso, os mecanismos do MergeTree são compatíveis com a classificação de linhas por chave primária, particionamento de linhas e a replicação e amostragem de dados.

Caso queira usar o ClickHouse para o arquivamento de dados que não são consultados frequentemente para armazenar dados temporários, use a família Log de motores para otimizar esse caso de uso.

Após as definições de coluna, você definirá outras opções de nível de tabela. A cláusula PRIMARY KEY define id como a coluna de chaves primárias e a cláusula ORDER BY armazenará valores ordenados pela coluna id. Uma chave primária identifica exclusivamente uma linha e é usada para acessar eficientemente uma única linha e para a colocação eficiente de linhas.

Ao executar a instrução de criação, você verá o seguinte resultado:

Output
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.

Nesta seção, você criou um banco de dados e uma tabela para rastrear os dados de visita do site. No próximo passo, você inserirá dados na tabela, atualizará os dados existentes e excluirá esses dados.

Passo 4 — Inserindo, atualizando e excluindo dados e colunas

Neste passo, você usará sua tabela visits para inserir, atualizar e excluir dados. O seguinte comando é um exemplo da sintaxe para inserir linhas em uma tabela do ClickHouse:

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

Agora, insira algumas linhas de dados de exemplo de visita de página na sua tabela visits, executando cada uma das seguintes instruções:

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');

Você verá o seguinte resultado repetido para cada instrução de inserção.

Output
INSERT INTO visits VALUES

Ok.

1 rows in set. Elapsed: 0.004 sec.

O resultado para cada linha mostra que você inseriu os dados com sucesso na tabela visits.

Agora, você adicionará uma coluna adicional à tabela visits. Ao adicionar ou excluir colunas de tabelas existentes, o ClickHouse suporta a sintaxe ALTER.

Por exemplo, a sintaxe básica para adicionar uma coluna a uma tabela é a seguinte:

ALTER TABLE table_name ADD COLUMN column_name column_type;

Adicione uma coluna chamada location que armazenará a localização das visitas a um site, executando a seguinte instrução:

ALTER TABLE visits ADD COLUMN location String;

Você verá um resultado similar ao seguinte:

Output
ALTER TABLE visits
    ADD COLUMN
    location String


Ok.

0 rows in set. Elapsed: 0.014 sec.

O resultado mostra que você adicionou a coluna location com sucesso.

A partir da versão 19.13.3, o ClickHouse não é compatível com a atualização e a exclusão de linhas individuais de dados devido a restrições de implantação. No entanto, o ClickHouse tem suporte para atualizações e exclusões em massa e tem uma sintaxe SQL distinta para essas operações para destacar o uso não padrão delas.

A sintaxe a seguir é um exemplo para atualizar linhas em massa:

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

Você executará a seguinte instrução para atualizar a coluna url de todas as linhas que têm um duration inferior a 15. Digite-a no prompt do banco de dados para executar:

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

O resultado da instrução de atualização em massa será o seguinte:

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


Ok.

0 rows in set. Elapsed: 0.003 sec.

O resultado mostra que sua consulta de atualização foi concluída com sucesso. O 0 rows in set no resultado denota que a consulta não retornou qualquer linha; será o caso para qualquer atualização e exclusão de consultas.

A sintaxe de exemplo para a exclusão de linhas em massa é semelhante à atualização de linhas e tem a seguinte estrutura:

ALTER TABLE table_name DELETE WHERE filter_conditions;

Para testar a exclusão de dados, execute a instrução a seguir para remover todas as linhas que têm duration inferior a 5:

ALTER TABLE visits DELETE WHERE duration < 5;

O resultado da instrução de exclusão em massa será semelhante a:

Output
ALTER TABLE visits
    DELETE WHERE duration < 5


Ok.

0 rows in set. Elapsed: 0.003 sec.

O resultado confirma que você excluiu as linhas com um período de duração inferior a cinco segundos.

Para excluir colunas da sua tabela, a sintaxe segue esta estrutura de exemplo:

ALTER TABLE table_name DROP COLUMN column_name;

Exclua a coluna location que você adicionou anteriormente executando o seguinte:

ALTER TABLE visits DROP COLUMN location;

O resultado de DROP COLUMN confirmando que você excluiu a coluna será o seguinte:

Output
ALTER TABLE visits
    DROP COLUMN
    location String


Ok.

0 rows in set. Elapsed: 0.010 sec.

Agora que você inseriu com sucesso, atualizou e excluiu linhas e colunas na sua tabela visits, você continuará com a consulta de dados no próximo passo.

Passo 5 — Consultando dados

A linguagem de consulta do ClickHouse é um dialeto personalizado do SQL com extensões e funções adequadas para cargas de trabalho de análise. Neste passo, você executará consultas de seleção e agregação para recuperar dados e resultados de sua tabela visits.

As consultas de seleção permitem que você recupere linhas e colunas de dados filtradas por condições que você especifica, junto com opções, tal como o número de linhas para retornar. Você pode selecionar linhas e colunas de dados usando a sintaxe SELECT. A sintaxe básica para consultas SELECT é:

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

Execute a instrução a seguir para recuperar os valores url e duration para linhas onde o url é http://example.com.

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

Você verá o seguinte resultado:

Output
SELECT
    url,
    duration
FROM visits
WHERE 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.

O resultado retornou duas linhas que correspondem às condições que você especificou. Agora que você selecionou valores, siga em frente para a execução de consultas de agregação.

As consultas de agregação são consultas que operam em um conjunto de valores e retornam valores únicos de saída. Em bancos de dados de análise, esses consultas são executadas frequentemente e são bem otimizadas pelo banco de dados. Algumas funções de agregação suportadas pelo ClickHouse são:

• count: retorna a contagem de linhas que correspondem às condições especificadas.

• sum: retorna a soma de valores selecionados da coluna.

• avg: retorna a média de valores selecionados da coluna.

Algumas funções de agregação específicas do ClickHouse incluem:

• uniq: retorna um número aproximado de linhas distintas correspondentes.

• topK: retorna uma matriz dos valores mais frequentes de uma coluna específica usando um algoritmo de aproximação.

Para demonstrar a execução de consultas de agregação, você calculará a duração total de visitas executando a consulta sum:

SELECT SUM(duration) FROM visits;

Você verá um resultado similar ao seguinte:

Output
SELECT SUM(duration)
FROM visits

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

1 rows in set. Elapsed: 0.010 sec.

Agora, calcule os dois URLs principais executando:

SELECT topK(2)(url) FROM visits;

Você verá um resultado similar ao seguinte:

Output
SELECT topK(2)(url)
FROM visits

┌─topK(2)(url)──────────────────────────────────┐
│ ['http://example2.com','http://example1.com'] │
└───────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.010 sec.

Agora que você consultou com sucesso sua tabela visits, excluirá tabelas e bancos de dados no próximo passo.

Passo 6 — Excluindo tabelas e bancos de dados

Nesta seção, você excluirá sua tabela visits e o banco de dados test.

A sintaxe para a exclusão de tabelas segue este exemplo:

DROP TABLE table_name;

Para excluir a tabela visits, execute a seguinte instrução:

DROP TABLE visits;

Você verá o seguinte resultado, declarando que você excluiu a tabela com sucesso:

output
DROP TABLE visits

Ok.

0 rows in set. Elapsed: 0.005 sec.

Você pode excluir bancos de dados usando a sintaxe DROP database table_name. Para excluir o banco de dados test, execute a seguinte instrução:

DROP DATABASE test;

O resultado final mostra que você excluiu o banco de dados com sucesso.

Output
DROP DATABASE test

Ok.

0 rows in set. Elapsed: 0.003 sec.

Neste passo, você excluiu tabelas e bancos de dados. Agora que você criou, atualizou e excluiu bancos de dados, tabelas e dados em sua instância do ClickHouse, você habilitará o acesso remoto ao seu servidor de banco de dados na seção seguinte.

Passo 7 — Configurando regras de firewall (Opcional)

Caso queira usar apenas o ClickHouse localmente com aplicativos executados no mesmo servidor, ou não tenha um firewall habilitado no seu servidor, não é necessário completar esta seção. Do contrário, caso esteja se conectando ao servidor de banco de dados do ClickHouse remotamente, é necessário seguir este passo.

Atualmente, seu servidor tem um firewall habilitado que desativa o acesso do seu endereço IP a todas as portas. Você completará os dois passos seguintes para permitir o acesso remoto:

• Modifique a configuração do ClickHouse e permita que ele escute em todas as interfaces.

• Adicione uma regra de firewall, que permite conexões de entrada pela porta 8123, que é a porta HTTP que o servidor do ClickHouse executa.

Caso esteja dentro do prompt do banco de dados, saia dele digitando CTRL+D.

Edite o arquivo de configuração executando:

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

Em seguida, retire o comentário da linha que contém <!-- <listen_host>0.0.0.0</listen_host> -->, como feito no seguinte arquivo:

...
 <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>
    -->
...

Salve o arquivo e saia. Para que a nova configuração seja aplicada, reinicie o serviço executando:

sudo service clickhouse-server restart

Você não verá nenhum resultado deste comando. O servidor do ClickHouse escuta na porta 8123 para conexões HTTP e na porta 9000 para conexões a partir do clickhouse-client. Permita o acesso a ambas as portas para o endereço IP do seu segundo servidor com o seguinte 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  

Você verá o seguinte resultado para ambos os comandos, o que mostra que habilitou o acesso a ambas as portas:

Output
Rule added

Agora, o ClickHouse estará acessível por meio do IP que você adicionou. Caso necessário, sinta-se à vontade para adicionar IPs adicionais, como o endereço da sua máquina local.

Para verificar se pode se conectar ao servidor do ClickHouse por meio de uma máquina remota, primeiro siga os passos no Passo 1 deste tutorial no segundo servidor e certifique-se de que tenha o clickhouse-client instalado nele.

Agora que logou-se no segundo servidor, inicie uma sessão de cliente executando:

clickhouse-client --host your_server_ip --password

Você verá o seguinte resultado, que mostra que você se conectou com sucesso ao servidor:

Output
ClickHouse 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 :)

Neste passo, você habilitou o acesso remoto ao seu servidor de banco de dados do ClickHouse, ajustando as regras do seu firewall.

Conclusão

Você configurou com sucesso uma instância de banco de dados do ClickHouse no seu servidor e criou um banco de dados e tabela, adicionou dados, realizou consultas e excluiu o banco de dados. Dentro da documentação do ClickHouse, você pode ler sobre seus benchmarks em relação a outros bancos de dados de análise comerciais de código aberto e documentos de referência gerais.

Outras funcionalidades do ClickHouse incluem o processamento da consulta distribuída por vários servidores para melhorar o desempenho e proteger contra a perda de dados, armazenando dados em diferentes fragmentos.