Dicionário

Um dicionário no ClickHouse fornece uma representação em memória de chave-valor de dados de várias fontes internas e externas, otimizada para consultas de busca com latência extremamente baixa. Os dicionários são úteis para:

Melhorar o desempenho das consultas, especialmente quando usados com JOINs
Enriquecer os dados ingeridos em tempo real sem desacelerar o processo de ingestão

Acelerando junções usando um Dicionário

Dicionários podem ser usados para acelerar um tipo específico de JOIN: o tipo LEFT ANY, em que a chave da junção precisa corresponder ao atributo-chave do armazenamento subjacente de chave-valor. Se esse for o caso, o ClickHouse pode aproveitar o dicionário para executar um Direct Join. Esse é o algoritmo de junção mais rápido do ClickHouse e se aplica quando o motor de tabela da tabela do lado direito oferece suporte a solicitações de chave-valor de baixa latência. O ClickHouse tem três motores de tabela que oferecem isso: Join (que é basicamente uma tabela hash pré-calculada), EmbeddedRocksDB e Dicionário. Vamos descrever a abordagem baseada em dicionário, mas o funcionamento é o mesmo para os três motores. O algoritmo de junção direta exige que a tabela da direita seja baseada em um dicionário, de modo que os dados dessa tabela a serem unidos já estejam presentes na memória na forma de uma estrutura de dados de chave-valor de baixa latência.

Exemplo

Usando o conjunto de dados do Stack Overflow, vamos responder à pergunta: Qual é o post sobre SQL mais controverso no Hacker News? Vamos considerar controversos os posts com números semelhantes de votos positivos e negativos. Calculamos essa diferença absoluta, em que um valor mais próximo de 0 indica maior controvérsia. Vamos supor que o post deva ter pelo menos 10 votos positivos e 10 negativos — posts em que ninguém vota não são muito controversos. Com nossos dados normalizados, esta consulta atualmente exige um JOIN entre as tabelas posts e votes:

WITH PostIds AS
(
         SELECT Id
         FROM posts
         WHERE Title ILIKE '%SQL%'
)
SELECT
    Id,
    Title,
    UpVotes,
    DownVotes,
    abs(UpVotes - DownVotes) AS Controversial_ratio
FROM posts
INNER JOIN
(
    SELECT
         PostId,
         countIf(VoteTypeId = 2) AS UpVotes,
         countIf(VoteTypeId = 3) AS DownVotes
    FROM votes
    WHERE PostId IN (PostIds)
    GROUP BY PostId
    HAVING (UpVotes > 10) AND (DownVotes > 10)
) AS votes ON posts.Id = votes.PostId
WHERE Id IN (PostIds)
ORDER BY Controversial_ratio ASC
LIMIT 1

Row 1:
──────
Id:                     25372161
Title:                  How to add exception handling to SqlDataSource.UpdateCommand
UpVotes:                13
DownVotes:              13
Controversial_ratio: 0

1 rows in set. Elapsed: 1.283 sec. Processed 418.44 million rows, 7.23 GB (326.07 million rows/s., 5.63 GB/s.)
Peak memory usage: 3.18 GiB.

Use conjuntos de dados menores no lado direito de JOIN: Esta consulta pode parecer mais verbosa do que o necessário, com a filtragem de PostIds ocorrendo tanto na consulta externa quanto nas subconsultas. Esta é uma otimização de desempenho que garante um tempo de resposta rápido para a consulta. Para obter o melhor desempenho, sempre garanta que o lado direito do JOIN seja o menor conjunto possível. Para ver dicas sobre como otimizar o desempenho de JOIN e entender os algoritmos disponíveis, recomendamos esta série de artigos no blog.

Embora esta consulta seja rápida, ela exige que escrevamos o JOIN com cuidado para obter um bom desempenho. O ideal seria simplesmente filtrar os posts para aqueles que contêm “SQL” antes de analisar as contagens de UpVote e DownVote para o subconjunto de blogs e calcular nossa métrica.

Aplicando um dicionário

Para demonstrar esses conceitos, usamos um dicionário para nossos dados de votos. Como os dicionários geralmente ficam na memória (ssd_cache é a exceção), você deve levar em conta o tamanho dos dados. Confirmando o tamanho da nossa tabela votes:

SELECT table,
        formatReadableSize(sum(data_compressed_bytes)) AS compressed_size,
        formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size,
        round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) AS ratio
FROM system.columns
WHERE table IN ('votes')
GROUP BY table

┌─table───────────┬─compressed_size─┬─uncompressed_size─┬─ratio─┐
│ votes           │ 1.25 GiB        │ 3.79 GiB          │  3.04 │
└─────────────────┴─────────────────┴───────────────────┴───────┘

Os dados serão armazenados sem compressão em nosso dicionário, portanto precisaríamos de pelo menos 4 GB de memória se fôssemos armazenar todas as colunas (não vamos) em um dicionário. O dicionário será replicado em todo o nosso cluster, então essa quantidade de memória precisa ser reservada por nó.

No exemplo abaixo, os dados do nosso dicionário vêm de uma tabela do ClickHouse. Embora essa seja a fonte mais comum de dicionários, há várias fontes compatíveis, incluindo arquivos, http e bancos de dados como o Postgres. Como veremos, os dicionários podem ser atualizados automaticamente, o que é ideal para garantir que pequenos conjuntos de dados sujeitos a mudanças frequentes estejam disponíveis para direct joins.

Nosso dicionário exige uma chave primária sobre a qual os lookups serão feitos. Conceitualmente, isso é idêntico à chave primária de um banco de dados transacional e deve ser único. Nossa consulta acima exige um lookup na chave de join — PostId. Por sua vez, o dicionário deve ser preenchido com o total de votos positivos e negativos por PostId da nossa tabela votes. Aqui está a consulta para obter esses dados do dicionário:

SELECT PostId,
   countIf(VoteTypeId = 2) AS UpVotes,
   countIf(VoteTypeId = 3) AS DownVotes
FROM votes
GROUP BY PostId

Para criar nosso Dicionário, é preciso usar o DDL a seguir — observe o uso da consulta acima:

CREATE DICTIONARY votes_dict
(
  `PostId` UInt64,
  `UpVotes` UInt32,
  `DownVotes` UInt32
)
PRIMARY KEY PostId
SOURCE(CLICKHOUSE(QUERY 'SELECT PostId, countIf(VoteTypeId = 2) AS UpVotes, countIf(VoteTypeId = 3) AS DownVotes FROM votes GROUP BY PostId'))
LIFETIME(MIN 600 MAX 900)
LAYOUT(HASHED())

0 rows in set. Elapsed: 36.063 sec.

Na versão OSS autogerenciada, o comando acima precisa ser executado em todos os nós. No ClickHouse Cloud, o dicionário será replicado automaticamente para todos os nós. Isso foi executado em um nó do ClickHouse Cloud com 64GB de RAM, levando 36s para carregar.

Para confirmar a memória consumida pelo nosso dicionário:

SELECT formatReadableSize(bytes_allocated) AS size
FROM system.dictionaries
WHERE name = 'votes_dict'

┌─size─────┐
│ 4.00 GiB │
└──────────┘

Agora, é possível recuperar os votos positivos e negativos de um PostId específico com uma simples função dictGet. Abaixo, recuperamos os valores da postagem 11227902:

SELECT dictGet('votes_dict', ('UpVotes', 'DownVotes'), '11227902') AS votes

┌─votes──────┐
│ (34999,32) │
└────────────┘

Ao aproveitar isso na consulta anterior, podemos remover o JOIN:

WITH PostIds AS
(
        SELECT Id
        FROM posts
        WHERE Title ILIKE '%SQL%'
)
SELECT Id, Title,
        dictGet('votes_dict', 'UpVotes', Id) AS UpVotes,
        dictGet('votes_dict', 'DownVotes', Id) AS DownVotes,
        abs(UpVotes - DownVotes) AS Controversial_ratio
FROM posts
WHERE (Id IN (PostIds)) AND (UpVotes > 10) AND (DownVotes > 10)
ORDER BY Controversial_ratio ASC
LIMIT 3

3 rows in set. Elapsed: 0.551 sec. Processed 119.64 million rows, 3.29 GB (216.96 million rows/s., 5.97 GB/s.)
Peak memory usage: 552.26 MiB.

Esta consulta não é apenas muito mais simples, mas também mais de duas vezes mais rápida! Isso poderia ser otimizado ainda mais carregando no dicionário apenas posts com mais de 10 votos positivos e negativos e armazenando apenas um valor de controvérsia pré-calculado.

Enriquecimento em tempo de consulta

Dicionários podem ser usados para consultar valores em tempo de consulta. Esses valores podem ser retornados nos resultados ou usados em agregações. Suponha que criemos um dicionário para mapear IDs de usuário para sua localização:

CREATE DICTIONARY users_dict
(
  `Id` Int32,
  `Location` String
)
PRIMARY KEY Id
SOURCE(CLICKHOUSE(QUERY 'SELECT Id, Location FROM stackoverflow.users'))
LIFETIME(MIN 600 MAX 900)
LAYOUT(HASHED())

Podemos usar este dicionário para enriquecer os resultados posteriores:

SELECT
        Id,
        Title,
        dictGet('users_dict', 'Location', CAST(OwnerUserId, 'UInt64')) AS location
FROM posts
WHERE Title ILIKE '%clickhouse%'
LIMIT 5
FORMAT PrettyCompactMonoBlock

┌───────Id─┬─Title─────────────────────────────────────────────────────────┬─Location──────────────┐
│ 52296928 │ Comparison between two Strings in ClickHouse                  │ Spain                 │
│ 52345137 │ How to use a file to migrate data from mysql to a clickhouse? │ 中国江苏省Nanjing Shi   │
│ 61452077 │ How to change PARTITION in clickhouse                         │ Guangzhou, 广东省中国   │
│ 55608325 │ Clickhouse select last record without max() on all table      │ Moscow, Russia        │
│ 55758594 │ ClickHouse create temporary table                             │ Perm', Russia         │
└──────────┴───────────────────────────────────────────────────────────────┴───────────────────────┘

5 rows in set. Elapsed: 0.033 sec. Processed 4.25 million rows, 82.84 MB (130.62 million rows/s., 2.55 GB/s.)
Peak memory usage: 249.32 MiB.

Assim como no exemplo de join acima, podemos usar o mesmo dicionário para determinar com eficiência de onde se origina a maioria dos posts:

SELECT
        dictGet('users_dict', 'Location', CAST(OwnerUserId, 'UInt64')) AS location,
        count() AS c
FROM posts
WHERE location != ''
GROUP BY location
ORDER BY c DESC
LIMIT 5

┌─location───────────────┬──────c─┐
│ India                  │ 787814 │
│ Germany                │ 685347 │
│ United States          │ 595818 │
│ London, United Kingdom │ 538738 │
│ United Kingdom         │ 537699 │
└────────────────────────┴────────┘

5 rows in set. Elapsed: 0.763 sec. Processed 59.82 million rows, 239.28 MB (78.40 million rows/s., 313.60 MB/s.)
Peak memory usage: 248.84 MiB.

Enriquecimento no momento da indexação

No exemplo acima, usamos um dicionário em tempo de consulta para eliminar um join. Os dicionários também podem ser usados para enriquecer linhas no momento da inserção. Isso normalmente é apropriado se o valor do enriquecimento não muda e está em uma fonte externa que pode ser usada para preencher o dicionário. Nesse caso, enriquecer a linha no momento da inserção evita a busca no dicionário em tempo de consulta. Vamos supor que a Location de um usuário no Stack Overflow nunca mude (na prática, muda) — especificamente, a coluna Location da tabela users. Suponha que queremos fazer uma consulta analítica na tabela de posts por localização. Ela contém um UserId. Um dicionário fornece um mapeamento do ID do usuário para a localização, com base na tabela users:

CREATE DICTIONARY users_dict
(
    `Id` UInt64,
    `Location` String
)
PRIMARY KEY Id
SOURCE(CLICKHOUSE(QUERY 'SELECT Id, Location FROM users WHERE Id >= 0'))
LIFETIME(MIN 600 MAX 900)
LAYOUT(HASHED())

Omitimos usuários com Id < 0, o que nos permite usar o tipo de dicionário Hashed. Usuários com Id < 0 são usuários do sistema.

Para aproveitar esse dicionário no momento da inserção na tabela Posts, precisamos modificar o schema:

CREATE TABLE posts_with_location
(
    `Id` UInt32,
    `PostTypeId` Enum8('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8),
     ...
    `Location` MATERIALIZED dictGet(users_dict, 'Location', OwnerUserId::'UInt64')
)
ENGINE = MergeTree
ORDER BY (PostTypeId, toDate(CreationDate), CommentCount)

No exemplo acima, Location é declarada como uma coluna MATERIALIZED. Isso significa que o valor pode ser fornecido como parte de uma consulta INSERT e será sempre calculado.

O ClickHouse também oferece suporte a colunas DEFAULT` (em que o valor pode ser inserido ou calculado, caso não seja fornecido).

Para preencher a tabela, podemos usar o habitual INSERT INTO SELECT a partir do S3:

INSERT INTO posts_with_location SELECT Id, PostTypeId::UInt8, AcceptedAnswerId, CreationDate, Score, ViewCount, Body, OwnerUserId, OwnerDisplayName, LastEditorUserId, LastEditorDisplayName, LastEditDate, LastActivityDate, Title, Tags, AnswerCount, CommentCount, FavoriteCount, ContentLicense, ParentId, CommunityOwnedDate, ClosedDate FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/*.parquet')

0 rows in set. Elapsed: 36.830 sec. Processed 238.98 million rows, 2.64 GB (6.49 million rows/s., 71.79 MB/s.)

Agora podemos obter o nome do local de onde vem a maioria das postagens:

SELECT Location, count() AS c
FROM posts_with_location
WHERE Location != ''
GROUP BY Location
ORDER BY c DESC
LIMIT 4

┌─Location───────────────┬──────c─┐
│ India                  │ 787814 │
│ Germany                │ 685347 │
│ United States          │ 595818 │
│ London, United Kingdom │ 538738 │
└────────────────────────┴────────┘

4 rows in set. Elapsed: 0.142 sec. Processed 59.82 million rows, 1.08 GB (420.73 million rows/s., 7.60 GB/s.)
Peak memory usage: 666.82 MiB.

Tópicos avançados sobre dicionários

Para orientações sobre como escolher layouts de dicionários, quando usar dicionários em vez de junções e como monitorar o uso de dicionários, consulte Boas práticas de dicionários.

Atualizando dicionários

Especificamos um LIFETIME para o dicionário de MIN 600 MAX 900. LIFETIME é o intervalo de atualização do dicionário, e esses valores fazem com que ele seja recarregado periodicamente em um intervalo aleatório entre 600 e 900s. Esse intervalo aleatório é necessário para distribuir a carga na origem do dicionário ao atualizar em um grande número de servidores. Durante as atualizações, a versão antiga de um dicionário ainda pode ser consultada; apenas a carga inicial bloqueia consultas. Observe que definir (LIFETIME(0)) impede a atualização dos dicionários. Os dicionários podem ser recarregados à força usando o comando SYSTEM RELOAD DICTIONARY. Para origens de banco de dados, como ClickHouse e Postgres, você pode configurar uma consulta que atualizará os dicionários apenas se eles realmente tiverem mudado (a resposta da consulta determina isso), em vez de em um intervalo periódico. Mais detalhes podem ser encontrados aqui.

Outros tipos de dicionários

O ClickHouse também oferece suporte a dicionários Hierárquicos, Polygon e de Expressão Regular.

Leitura adicional

Boas práticas de dicionário — seleção de layout, dicionários vs junções, monitoramento
Usando Dicionários para Acelerar Consultas
Configuração avançada de Dicionários

Fundamentos

Conceitos do ClickHouse

Boas práticas

Acelerando junções usando um Dicionário

Exemplo

Aplicando um dicionário

Enriquecimento em tempo de consulta

Enriquecimento no momento da indexação

Tópicos avançados sobre dicionários

Atualizando dicionários

Outros tipos de dicionários

Leitura adicional

​Acelerando junções usando um Dicionário

​Exemplo

​Aplicando um dicionário

​Enriquecimento em tempo de consulta

​Enriquecimento no momento da indexação

​Tópicos avançados sobre dicionários

​Atualizando dicionários

​Outros tipos de dicionários

​Leitura adicional

Acelerando junções usando um Dicionário

Exemplo

Aplicando um dicionário

Enriquecimento em tempo de consulta

Enriquecimento no momento da indexação

Tópicos avançados sobre dicionários

Atualizando dicionários

Outros tipos de dicionários

Leitura adicional