tipos de layout do dicionário range_hashed

range_hashed

O dicionário é armazenado na memória na forma de uma tabela hash com um array ordenado de intervalos e seus respectivos valores. Esse método de armazenamento funciona da mesma forma que hashed e permite usar intervalos de data/hora (tipo numérico arbitrário) além da chave. Exemplo: A tabela contém descontos para cada anunciante no formato:

┌─advertiser_id─┬─discount_start_date─┬─discount_end_date─┬─amount─┐
│           123 │          2015-01-16 │        2015-01-31 │   0.25 │
│           123 │          2015-01-01 │        2015-01-15 │   0.15 │
│           456 │          2015-01-01 │        2015-01-15 │   0.05 │
└───────────────┴─────────────────────┴───────────────────┴────────┘

Para usar uma amostra em intervalos de datas, defina os elementos range_min e range_max na estrutura. Esses elementos devem conter os elementos name e type (se type não for especificado, o tipo padrão será usado: Date). type pode ser qualquer tipo numérico (Date / DateTime / UInt64 / Int32 / outros).

Os valores de range_min e range_max devem estar dentro dos limites do tipo Int64.

Exemplo:

DDL
Arquivo de configuração

CREATE DICTIONARY discounts_dict (
    advertiser_id UInt64,
    discount_start_date Date,
    discount_end_date Date,
    amount Float64
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(TABLE 'discounts'))
LIFETIME(MIN 1 MAX 1000)
LAYOUT(RANGE_HASHED(range_lookup_strategy 'max'))
RANGE(MIN discount_start_date MAX discount_end_date)

<layout>
    <range_hashed>
        <!-- Estratégia para intervalos sobrepostos (min/max). Padrão: min (retorna um intervalo correspondente com o valor de min(range_min -> range_max)) -->
        <range_lookup_strategy>min</range_lookup_strategy>
    </range_hashed>
</layout>
<structure>
    <id>
        <name>advertiser_id</name>
    </id>
    <range_min>
        <name>discount_start_date</name>
        <type>Date</type>
    </range_min>
    <range_max>
        <name>discount_end_date</name>
        <type>Date</type>
    </range_max>
    ...

Para trabalhar com esses dicionários, você precisa passar um argumento adicional para a função dictGet, para o qual será selecionado um intervalo:

dictGet('dict_name', 'attr_name', id, date)

Exemplo de consulta:

SELECT dictGet('discounts_dict', 'amount', 1, '2022-10-20'::Date);

Esta função retorna o valor para os ids especificados e o intervalo de datas que inclui a data fornecida. Detalhes do algoritmo:

Se o id não for encontrado ou não for encontrado um intervalo para o id, retorna o valor padrão do tipo do atributo.
Se houver intervalos sobrepostos e range_lookup_strategy=min, retorna um intervalo correspondente com range_min mínimo; se vários intervalos forem encontrados, retorna um intervalo com range_max mínimo; se ainda assim vários intervalos forem encontrados (vários intervalos tiverem o mesmo range_min e range_max), retorna um intervalo aleatório entre eles.
Se houver intervalos sobrepostos e range_lookup_strategy=max, retorna um intervalo correspondente com range_min máximo; se vários intervalos forem encontrados, retorna um intervalo com range_max máximo; se ainda assim vários intervalos forem encontrados (vários intervalos tiverem o mesmo range_min e range_max), retorna um intervalo aleatório entre eles.
Se range_max for NULL, o intervalo será aberto. NULL é tratado como o maior valor possível. Para range_min, 1970-01-01 ou 0 (-MAX_INT) podem ser usados como valor aberto.

Exemplo de configuração:

DDL
Arquivo de configuração

CREATE DICTIONARY somedict(
    Abcdef UInt64,
    StartTimeStamp UInt64,
    EndTimeStamp UInt64,
    XXXType String DEFAULT ''
)
PRIMARY KEY Abcdef
RANGE(MIN StartTimeStamp MAX EndTimeStamp)

<clickhouse>
    <dictionary>
        ...

        <layout>
            <range_hashed />
        </layout>

        <structure>
            <id>
                <name>Abcdef</name>
            </id>
            <range_min>
                <name>StartTimeStamp</name>
                <type>UInt64</type>
            </range_min>
            <range_max>
                <name>EndTimeStamp</name>
                <type>UInt64</type>
            </range_max>
            <attribute>
                <name>XXXType</name>
                <type>String</type>
                <null_value />
            </attribute>
        </structure>

    </dictionary>
</clickhouse>

Exemplo de configuração com intervalos sobrepostos e abertos:

CREATE TABLE discounts
(
    advertiser_id UInt64,
    discount_start_date Date,
    discount_end_date Nullable(Date),
    amount Float64
)
ENGINE = Memory;

INSERT INTO discounts VALUES (1, '2015-01-01', Null, 0.1);
INSERT INTO discounts VALUES (1, '2015-01-15', Null, 0.2);
INSERT INTO discounts VALUES (2, '2015-01-01', '2015-01-15', 0.3);
INSERT INTO discounts VALUES (2, '2015-01-04', '2015-01-10', 0.4);
INSERT INTO discounts VALUES (3, '1970-01-01', '2015-01-15', 0.5);
INSERT INTO discounts VALUES (3, '1970-01-01', '2015-01-10', 0.6);

SELECT * FROM discounts ORDER BY advertiser_id, discount_start_date;
┌─advertiser_id─┬─discount_start_date─┬─discount_end_date─┬─amount─┐
│             1 │          2015-01-01 │              ᴺᵁᴸᴸ │    0.1 │
│             1 │          2015-01-15 │              ᴺᵁᴸᴸ │    0.2 │
│             2 │          2015-01-01 │        2015-01-15 │    0.3 │
│             2 │          2015-01-04 │        2015-01-10 │    0.4 │
│             3 │          1970-01-01 │        2015-01-15 │    0.5 │
│             3 │          1970-01-01 │        2015-01-10 │    0.6 │
└───────────────┴─────────────────────┴───────────────────┴────────┘

-- RANGE_LOOKUP_STRATEGY 'max' (estratégia de busca por intervalo: máximo)

CREATE DICTIONARY discounts_dict
(
    advertiser_id UInt64,
    discount_start_date Date,
    discount_end_date Nullable(Date),
    amount Float64
)
PRIMARY KEY advertiser_id
SOURCE(CLICKHOUSE(TABLE discounts))
LIFETIME(MIN 600 MAX 900)
LAYOUT(RANGE_HASHED(RANGE_LOOKUP_STRATEGY 'max'))
RANGE(MIN discount_start_date MAX discount_end_date);

select dictGet('discounts_dict', 'amount', 1, toDate('2015-01-14')) res;
┌─res─┐
│ 0.1 │ -- apenas um intervalo corresponde: 2015-01-01 - Null
└─────┘

select dictGet('discounts_dict', 'amount', 1, toDate('2015-01-16')) res;
┌─res─┐
│ 0.2 │ -- dois intervalos correspondem, range_min 2015-01-15 (0.2) é maior que 2015-01-01 (0.1)
└─────┘

select dictGet('discounts_dict', 'amount', 2, toDate('2015-01-06')) res;
┌─res─┐
│ 0.4 │ -- dois intervalos correspondem, range_min 2015-01-04 (0.4) é maior que 2015-01-01 (0.3)
└─────┘

select dictGet('discounts_dict', 'amount', 3, toDate('2015-01-01')) res;
┌─res─┐
│ 0.5 │ -- dois intervalos correspondem, range_min são iguais, 2015-01-15 (0.5) é maior que 2015-01-10 (0.6)
└─────┘

DROP DICTIONARY discounts_dict;

-- RANGE_LOOKUP_STRATEGY 'min' (estratégia de busca por intervalo: mínimo)

CREATE DICTIONARY discounts_dict
(
    advertiser_id UInt64,
    discount_start_date Date,
    discount_end_date Nullable(Date),
    amount Float64
)
PRIMARY KEY advertiser_id
SOURCE(CLICKHOUSE(TABLE discounts))
LIFETIME(MIN 600 MAX 900)
LAYOUT(RANGE_HASHED(RANGE_LOOKUP_STRATEGY 'min'))
RANGE(MIN discount_start_date MAX discount_end_date);

select dictGet('discounts_dict', 'amount', 1, toDate('2015-01-14')) res;
┌─res─┐
│ 0.1 │ -- apenas um intervalo corresponde: 2015-01-01 - Null
└─────┘

select dictGet('discounts_dict', 'amount', 1, toDate('2015-01-16')) res;
┌─res─┐
│ 0.1 │ -- dois intervalos correspondem, range_min 2015-01-01 (0.1) é menor que 2015-01-15 (0.2)
└─────┘

select dictGet('discounts_dict', 'amount', 2, toDate('2015-01-06')) res;
┌─res─┐
│ 0.3 │ -- dois intervalos correspondem, range_min 2015-01-01 (0.3) é menor que 2015-01-04 (0.4)
└─────┘

select dictGet('discounts_dict', 'amount', 3, toDate('2015-01-01')) res;
┌─res─┐
│ 0.6 │ -- dois intervalos correspondem, range_min são iguais, 2015-01-10 (0.6) é menor que 2015-01-15 (0.5)
└─────┘

complex_key_range_hashed

O Dicionário é armazenado na memória na forma de uma tabela hash com um Array ordenado de intervalos e seus respectivos valores (consulte range_hashed). Esse tipo de armazenamento é usado com chaves compostas. Exemplo de configuração:

CREATE DICTIONARY range_dictionary
(
  CountryID UInt64,
  CountryKey String,
  StartDate Date,
  EndDate Date,
  Tax Float64 DEFAULT 0.2
)
PRIMARY KEY CountryID, CountryKey
SOURCE(CLICKHOUSE(TABLE 'date_table'))
LIFETIME(MIN 1 MAX 1000)
LAYOUT(COMPLEX_KEY_RANGE_HASHED())
RANGE(MIN StartDate MAX EndDate);

​range_hashed

​complex_key_range_hashed

range_hashed

complex_key_range_hashed