Clickhouse¶

SGDB colunar open source de consultas analíticas.

Objetivo¶

Documentar instalação e formas de configurar um sistema de produção em clickhouse

Links¶

Dependências¶

Clickhouse, v24.1.5.6-stable, Sistema de Gerênciamento de Banco de Dados colunar

Instalação (Debian/Ubuntu)¶

Seguir os passos conforme Setup the Debian repository para adicionar o source ao apt.

Após adicionar o source, intale utilizando apt.

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

Finalmente inicie o serviço e acesse o prompt com:

systemctl start clickhouse-server
clickhouse-client

Verifique o uso de Huge Pages

# Temporary setup
echo 'madvise' | sudo tee /sys/kernel/mm/transparent_hugepage/enabled
# Permanent (reboot) /etc/default/grub
GRUB_CMDLINE_LINUX_DEFAULT="transparent_hugepage=madvise ..."
sudo update-grub

Mudar diretório de dados¶

Caso queira alterar o local onde o clickhouse armazena seus dados:

Desligue o cluster

systemctl stop clickhouse-server

copie os dados da instalação padrão em /var/lib/clickhouse para o diretório destino.

rsync -a -H /var/lib/clickhouse /home/

Atualize no arquivo de configuração em /etc/clickhouse-server/config.xml os locais onde possuir /var/lib/clickhouse/ para o novo local de instalação.
Para verificar o local de instalação execute: SELECT * FROM system.databases.

Boas práticas¶

Listar bancos¶

Adicionar a view no banco default com os nomes dos bancos que não fazem parte do sistema:

CREATE VIEW default.databases AS 
SELECT
    database,
    formatReadableSize(SUM(data_compressed_bytes)) AS total_compressed_bytes,
    formatReadableSize(SUM(data_uncompressed_bytes)) AS total_uncompressed_bytes,
    SUM(rows) AS total_rows
FROM system.parts
WHERE database NOT IN ('INFORMATION_SCHEMA', 'system', 'information_schema') 
GROUP BY database
ORDER BY total_compressed_bytes DESC;

Listar tabelas¶

Adicionar a view no banco default com tamanhos das tabelas dos bancos:

CREATE VIEW default.all_tables AS
SELECT
    database,
    table,
    formatReadableSize(sum(data_compressed_bytes) AS size) AS compressed,
    formatReadableSize(sum(data_uncompressed_bytes) AS usize) AS uncompressed,
    round(usize / size, 2) AS compr_rate,
    sum(rows) AS rows,
    count() AS part_count
FROM system.parts
WHERE 
    (active = 1) AND 
    (database LIKE '%') AND 
    (table LIKE '%') AND
    database NOT IN ('INFORMATION_SCHEMA', 'system', 'information_schema') 
GROUP BY
    database,
    table
ORDER BY database ASC, size DESC;

Listar usuários e permissões¶

Adicionar a view no banco default com os usuários e suas permissões.

CREATE VIEW default.users_permissions AS
SELECT
    user_name,
    role_name,
    access_type,
    database,
    table,
    column,
    is_partial_revoke,
    grant_option
FROM system.grants
ORDER BY user_name, database, access_type;

Listar queries¶

Adicionar a view no banco default com as queries que os bancos realizam.

CREATE VIEW default.all_queries AS
SELECT
  query,
  query_id,
  hostname,
  address,
  type,
  user,
  databases,
  tables,
  columns,
  query_duration_ms,
  event_time,
  read_rows,
  result_rows,
  formatReadableSize(memory_usage)
FROM system.query_log
WHERE
  type != 'QueryStart'
ORDER BY event_time DESC;

Listar backups¶

Adicionar a view no banco default com os status das tentativas de backup.

CREATE VIEW default.backup_status AS
SELECT
  name, 
  status,
  start_time, 
  end_time, 
  num_files, 
  formatReadableSize(total_size) AS total_size, 
  formatReadableSize(uncompressed_size) 
  AS uncomp_size, 
  formatReadableSize(compressed_size) AS comp_size, 
  files_read, 
  bytes_read 
FROM system.backups
ORDER BY start_time;

Configurar usuários¶

Clickhouse possui varias configurações interessantes para seus usuários como criptografia, origem de acesso e configuração de permissões.

# Listar usuarios
SHOW users;

# Listar permissoes de um usuario especifico
SHOW GRANTS FOR 'username'

# Criar usuario
CREATE USER 'username' IDENTIFIED BY 'password';
CREATE USER 'username' IDENTIFIED BY 'password' HOST NAME 'host.c3sl.ufpr.br';  # Especifica hostname de origem

# Deletar usuario
DROP USER 'username';

# Adicionar permissoes (Estrutura: GRANT 'permission' ON 'database'.'tables' TO 'username')
GRANT CREATE DATABASE ON *.* TO 'username';
GRANT SHOW ON *.* TO 'username';
GRANT ALL ON *.* TO 'username';  # Admin atribuito admin a um usuario

# Atribuit roles a usuarios
GRANT 'rolename' TO 'username';

# Verificar config dos usuarios
SELECT name, storage, auth_type, host_names, default_database FROM system.users;

Permissões importantes: SHOW, SELECT, INSERT, ALTER, CREATE, DROP, TRUNCATE.

Criar usuario C3SL¶

Para evitar que um usuário crie muitos bancos ou usuarios, permissões como CREATE DATABASE e CREATE USER são dadas apenas a administradores.
OBS: Ao se conectar a instancia do clickhouse, especificar o banco ao entrar ou alterar banco com \c databasename. Do contrario irá acessar o banco default sem nenhuma permissão.
OBS2: Cada usuario tem permissão de deletar seu proprio banco de dados.

# Crie o usuario com o host de origem
CREATE USER 'username' IDENTIFIED BY 'password' HOST NAME 'hostVM.c3sl.ufpr.br';

# Crie o banco de dados
CREATE DATABASE 'databasename';

# Adicione as permissoes
GRANT SHOW     ON databasename.* TO username;
GRANT SELECT   ON databasename.* TO username;
GRANT INSERT   ON databasename.* TO username;
GRANT ALTER    ON databasename.* TO username;
GRANT CREATE   ON databasename.* TO username;
GRANT DROP     ON databasename.* TO username;
GRANT TRUNCATE ON databasename.* TO username;
REVOKE DROP DATABASE ON databasename.* FROM username;

Importar/Exportar CSV¶

OBS: Tabelas no clickhouse são criadas por default como não nulaveis, ou seja, caso seus dados possuam NULL’s deve-se especificar como ‘Nullable’ as colunas na criação da tabela

Importar¶

Primeiro crie as tabelas no banco.
Caso as tabelas estejam em um arquivo, é possível as criar com

clickhouse-client -d <database> --queries-file create_table_file.sql

Para importar um csv, seguindo com base na documentação, utilizando o client:

clickhouse-client -d <database> --format_csv_delimiter="|" --query="INSERT INTO test FORMAT CSV" < data.csv

Caso queira adicionar outras configrações ao formato do csv, adicione elas após a statement do clickhouse-client

Exportar¶

Para exportar dados de uma query em um arquivo CSV utilizando o utilitário clickhouse-client

clickhouse-client -d <database> --query "SELECT <coluna1>, <coluna2> FROM <tabela>" --format CSV

Consulta em CSV¶

É possível realizar consultas em arquivos CSV sem a necessidade de carregá-los em uma intância clickhouse. Para isso pode ser utilizado clickhouse em memória.
OBS: Verifique o separador de acordo com o arquivo
Ex: file.csv

clickhouse local --format_csv_delimiter=";" -q "SELECT name, count(*) FROM file(file.csv, CSVWithNames)"

Create Table em CSV¶

Com base nos dados de um CSV, também é possivel utilizar um clickhouse em memória para criar um CREATE TABLE.

clickhouse local --format_csv_delimiter="|" --multiquery -q "create table T Engine=Memory as select * from file('data.csv', CSVWithNames); show create table T FORMAT CSVWithNames"

PostgreSQL ETL¶

Caso se queira Extrair, Transformar e Carregar dados (ETL) de uma instância do postgres em clickhouse é possível com os seguintes passos.
Primeiro crie a tabela em clickhouse

CREATE TABLE tpch.customer (
    c_custkey numeric NOT NULL,
    c_mktsegment character(10),
    c_nationkey numeric,
    c_name character varying(25),
    c_address character varying(40),
    c_phone character(15),
    c_acctbal numeric,
    c_comment character varying(118)
) ENGINE = MergeTree()
PRIMARY KEY (c_custkey);

Em seguida copie os dados utilizando INSERT INTO e a engine de proxy do postgres do clickhouse.
A ordem dos atributos deve ser a mesma da tabela tanto em postgres quanto em clickhouse

INSERT INTO customer SELECT * FROM PostgreSQL('host:port', 'database', 'tablename', 'username', 'password');

Update table¶

Clickhouse atuamente (06/20204) não tem suporte a atualização de dados entre multiplas tabelas, apenas entre as colunas da própria tabela:

ALTER TABLE tabela UPDATE column1 = coluna2 WHERE <condition>

Assim, consultas do seguinte tipo não funcionam conforme a issue 54645

UPDATE
    bills
SET
    transaction_billed = transaction_id
FROM
    transactions
WHERE  transactions.created_at >= sysdate - 1  
AND    transactions.username = 'test_account'  
AND    transactions.token = bills.final_token;

Assim, para conseguir algo do genêro é necessario utilizar uma tabela temporária com joins conforme segue:

-- Objetivo: Copiar os dados da coluna2 da tabela2 para a coluna1 da tabela1

-- Crie uma tabela temporaria para a tabela que se quer atualizar
CREATE TABLE temp AS tabela1;

-- Adicione a coluna de outra tabela que se quer importar os dados, nulavel caso necessario
ALTER TABLE temp ADD COLUMN coluna2 Nullable(INTEGER);

-- Consulta para popular a tabela temporaria
SELECT a.*, b.coluna2 FROM tabela1 a LEFT JOIN tabela2 b ON a.id = b.id;

-- Popule a tabela temporaria
INSERT INTO temp SELECT a.*, b.coluna2 FROM tabela1 a LEFT JOIN tabela2 b ON a.id = b.id;

-- Copie os dados internamente a tabela temporaria
-- OBS: Where é obrigatorio
ALTER TABLE temp UPDATE coluna1 = coluna2 WHERE year == 2010;

-- Remova a coluna2 para que a tabela temporaria fique como o resultado final da coluna1
ALTER TABLE temp DROP COLUMN column2;

-- Limpe todos os dados da tabela1 e importe da tabela temporaria
-- OBS: VERIFIQUE SE OS DADOS ESTÃO REALMENTE NA TAMPORARIA, ou poderá perder dados
TRUNCATE tabela1;
INSERT INTO tabela1 SELECT * FROM temp;

Remote query¶

É possivel realizar consultas em tabelas de forma remota.
OBS: Todas as requisições são redirecionadas a instancia remota. Para se copiar os dados localmente deve-se criar uma tabela remota, criar uma tabela referenciando a tabela remota porém especificando a engine MergeTre

-- clickhouse
SELECT * FROM remote('clickhouse.c3sl.ufpr.br', 'default.table')
CREATE TABLE remote_table AS remote('clickhouse.c3sl.ufpr.br', 'default.table')
CREATE TABLE local AS remote_table ENGINE MergeTree() ORDER BY column