> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-mintlify-fbfa8bee.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Dicionário

> Um dicionário fornece uma representação de dados em chave-valor para buscas rápidas.

export const Image = ({img, alt, size}) => {
  return <Frame>
      <img src={img} alt={alt} />
    </Frame>;
};

Um dicionário no ClickHouse fornece uma representação em memória de [chave-valor](https://en.wikipedia.org/wiki/Key%E2%80%93value_database) de dados de várias [fontes internas e externas](/pt-BR/reference/statements/create/dictionary/sources/overview#dictionary-sources), 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 `JOIN`s
* Enriquecer os dados ingeridos em tempo real sem desacelerar o processo de ingestão

<Image img="https://mintcdn.com/private-7c7dfe99-mintlify-fbfa8bee/qvDh3oxLPI7c74wx/images/dictionary/dictionary-use-cases.png?fit=max&auto=format&n=qvDh3oxLPI7c74wx&q=85&s=01e98f1fefe5fe608beba3645511a276" size="lg" alt="Casos de uso do Dicionário no ClickHouse" width="1600" height="838" data-path="images/dictionary/dictionary-use-cases.png" />

<div id="speeding-up-joins-using-a-dictionary">
  ## Acelerando junções usando um Dicionário
</div>

Dicionários podem ser usados para acelerar um tipo específico de `JOIN`: o tipo [`LEFT ANY`](/pt-BR/reference/statements/select/join#supported-types-of-join), em que a chave da junção precisa corresponder ao atributo-chave do armazenamento subjacente de chave-valor.

<Image img="https://mintcdn.com/private-7c7dfe99-mintlify-fbfa8bee/qvDh3oxLPI7c74wx/images/dictionary/dictionary-left-any-join.png?fit=max&auto=format&n=qvDh3oxLPI7c74wx&q=85&s=d82e882d6c37ca31d8b312bc0a1c4e3e" size="sm" alt="Usando Dicionário com LEFT ANY JOIN" width="680" height="704" data-path="images/dictionary/dictionary-left-any-join.png" />

Se esse for o caso, o ClickHouse pode aproveitar o dicionário para executar um [Direct Join](https://clickhouse.com/blog/clickhouse-fully-supports-joins-direct-join-part4#direct-join). Esse é o algoritmo de junção mais rápido do ClickHouse e se aplica quando o [motor de tabela](/pt-BR/reference/engines/table-engines/index) 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](/pt-BR/reference/engines/table-engines/special/join) (que é basicamente uma tabela hash pré-calculada), [EmbeddedRocksDB](/pt-BR/reference/engines/table-engines/integrations/embedded-rocksdb) e [Dicionário](/pt-BR/reference/engines/table-engines/special/dictionary). 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.

<div id="example">
  ### Exemplo
</div>

Usando o [conjunto de dados do Stack Overflow](/pt-BR/get-started/sample-datasets/stackoverflow), 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`:

```sql theme={null}
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
```

```response theme={null}
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 `PostId`s 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](https://clickhouse.com/blog/clickhouse-fully-supports-joins-part1).

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.

<div id="applying-a-dictionary">
  #### Aplicando um dicionário
</div>

Para demonstrar esses conceitos, usamos um dicionário para nossos dados de votos. Como os dicionários geralmente ficam na memória ([ssd\_cache](/pt-BR/reference/statements/create/dictionary/layouts/ssd-cache) é a exceção), você deve levar em conta o tamanho dos dados. Confirmando o tamanho da nossa tabela `votes`:

```sql theme={null}
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
```

```response theme={null}
┌─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](/pt-BR/reference/statements/create/dictionary/sources/overview#dictionary-sources), incluindo arquivos, http e bancos de dados como o [Postgres](/pt-BR/reference/statements/create/dictionary/sources/postgresql). 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:

```sql theme={null}
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:

```sql theme={null}
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())
```

```response theme={null}
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:

```sql theme={null}
SELECT formatReadableSize(bytes_allocated) AS size
FROM system.dictionaries
WHERE name = 'votes_dict'
```

```response theme={null}
┌─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`:

```sql theme={null}
SELECT dictGet('votes_dict', ('UpVotes', 'DownVotes'), '11227902') AS votes
```

```response theme={null}
┌─votes──────┐
│ (34999,32) │
└────────────┘
```

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

```sql theme={null}
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
```

```response theme={null}
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.

<div id="query-time-enrichment">
  ## Enriquecimento em tempo de consulta
</div>

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:

```sql theme={null}
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:

```sql theme={null}
SELECT
        Id,
        Title,
        dictGet('users_dict', 'Location', CAST(OwnerUserId, 'UInt64')) AS location
FROM posts
WHERE Title ILIKE '%clickhouse%'
LIMIT 5
FORMAT PrettyCompactMonoBlock
```

```response theme={null}
┌───────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:

```sql theme={null}
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
```

```response theme={null}
┌─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.
```

<div id="index-time-enrichment">
  ## Enriquecimento no momento da indexação
</div>

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`:

```sql theme={null}
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:

```sql theme={null}
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\`](/pt-BR/reference/statements/create/table#default_values) (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:

```sql theme={null}
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')
```

```response theme={null}
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:

```sql theme={null}
SELECT Location, count() AS c
FROM posts_with_location
WHERE Location != ''
GROUP BY Location
ORDER BY c DESC
LIMIT 4
```

```response theme={null}
┌─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.
```

<div id="advanced-dictionary-topics">
  ## Tópicos avançados sobre dicionários
</div>

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](/pt-BR/concepts/features/dictionaries/best-practices).

<div id="refreshing-dictionaries">
  ### Atualizando dicionários
</div>

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](/pt-BR/reference/statements/create/dictionary/lifetime).

<div id="other-dictionary-types">
  ### Outros tipos de dicionários
</div>

O ClickHouse também oferece suporte a dicionários [Hierárquicos](/pt-BR/reference/statements/create/dictionary/layouts/hierarchical), [Polygon](/pt-BR/reference/statements/create/dictionary/layouts/polygon) e de [Expressão Regular](/pt-BR/reference/statements/create/dictionary/layouts/regexp-tree).

<div id="more-reading">
  ### Leitura adicional
</div>

* [Boas práticas de dicionários](/pt-BR/concepts/features/dictionaries/best-practices) — seleção de layout, dicionários vs. junções, monitoramento
* [Usando dicionários para acelerar consultas](https://clickhouse.com/blog/faster-queries-dictionaries-clickhouse)
* [Configuração avançada de dicionários](/pt-BR/reference/statements/create/dictionary)