Перейти к основному содержимому
Версия: 2.0.x

Ускорение COUNT(DISTINCT) и Join с помощью AUTO INCREMENT и глобального словаря

В этой теме описывается, как ускорить вычисление COUNT(DISTINCT) и операции Join с использованием столбцов AUTO INCREMENT и глобального словаря.

Сценарии использования​

  • Сценарий 1: Предположим, вам нужно выполнить точную дедупликацию на массивных данных (например, розничные заказы или заказы на доставку). Однако столбец для дедупликации имеет тип STRING, что может привести к неоптимальной производительности при подсчёте. Например, в таблице orders столбец order_uuid, представляющий идентификатор заказа, имеет тип STRING, обычно размером от 32 до 36 байт, генерируемый функцией UUID() или аналогичными. В этом случае COUNT(DISTINCT) по STRING-столбцу order_uuid с запросом вида SELECT count(DISTINCT order_uuid) FROM orders WHERE create_date >= CURDATE(); может не обеспечить удовлетворительную производительность. Использование INTEGER-столбца для точной дедупликации значительно повысит производительность.

  • Сценарий 2: Предположим, вы хотите ускорить точную дедупликацию в многомерном анализе с использованием bitmap-функций. Функция bitmap_count() требует INTEGER на входе, но если столбец для дедупликации имеет тип STRING, вам нужно использовать функцию bitmap_hash(). Это может привести к приблизительным, немного заниженным результатам дедупликации, а также может снизить производительность запросов и увеличить требования к хранилищу, поскольку INTEGER-значения, генерируемые bitmap_hash(), более рассеяны по сравнению с последовательно присвоенными INTEGER-значениями.

  • Сценарий 3: Предположим, вам нужно запросить количество заказов с коротким промежутком времени между размещением и оплатой заказа, где время размещения и оплаты заказа могут храниться в разных таблицах, поддерживаемых разными бизнес-командами. Вам может потребоваться объединить эти таблицы по идентификатору заказа, а затем выполнить дедупликацию заказов. Например:

    SELECT count(distinct order_uuid)
    FROM orders_t1 as t1 JOIN orders_t2 as t2
        ON t1.order_uuid = t2.order_uuid
    WHERE t2.payment_time - t1.create_time <= 3600
        AND create_date >= CURDATE();

    Однако использование STRING-столбца order_uuid для соединений менее эффективно, чем использование INTEGER-столбца.

Подход к оптимизации​

Для решения проблем в вышеуказанных сценариях подход к оптимизации включает загрузку данных заказов в целевую таблицу и установление сопоставления между STRING- и INTEGER-значениями. Последующий анализ запросов будет основан на INTEGER-столбце. Этот подход можно разделить на следующие этапы:

  1. Этап 1: Создание глобального словаря и установление сопоставления между STRING- и INTEGER-значениями. В этом словаре ключевой столбец имеет тип STRING, а столбец значений — тип AUTO INCREMENT INTEGER. При загрузке данных система автоматически генерирует уникальный идентификатор для каждого STRING-значения, создавая сопоставление между STRING- и INTEGER-значениями.

  2. Этап 2: Загрузка связи между данными заказов и глобальным словарём в целевую таблицу.

  3. Этап 3: Использование INTEGER-столбца из целевой таблицы для точной дедупликации или соединений при последующем анализе запросов, что может значительно повысить производительность.

  4. Этап 4: Для дальнейшей оптимизации производительности можно использовать bitmap-функции на INTEGER-столбце для ускорения точной дедупликации.

Решение​

До версии v1.5.2 этап 2 мог быть реализован двумя способами:

  • Использование внешней таблицы или внутренней таблицы в качестве промежуточной таблицы для соединения с таблицей словаря для получения соответствующего идентификатора словаря перед загрузкой.
  • Использование таблицы Primary Key для загрузки данных, а затем использование оператора UPDATE с операцией JOIN для обновления идентификатора словаря. Однако этот процесс загрузки данных может быть неудобным и имеет много ограничений.

Начиная с версии v1.5.2, Selena представила функцию dict_mapping(), позволяющую определять столбец идентификатора словаря в целевой таблице как вычисляемый столбец с использованием выражения dict_mapping(). Последующие задачи загрузки данных обрабатываются как обычная загрузка данных, без необходимости использования операторов UPDATE с операциями JOIN для записи идентификаторов словаря. Во время загрузки данных система автоматически связывает исходную таблицу с таблицей словаря и вставляет соответствующий идентификатор словаря, что значительно упрощает процесс загрузки данных с глобальной таблицей словаря, независимо от типов таблиц и с поддержкой различных методов загрузки.

Бизнес-сценарий​

В следующем примере используются два примера CSV-файлов, batch1.csv и batch2.csv, каждый из которых содержит два столбца: id и order_uuid.

  • batch1.csv

    1, a1
    2, a2
    3, a3
    11, a1
    11, a2
    12, a1

  • batch2.csv

    1, a2
    2, a2
    3, a2
    11, a2
    12, a101
    12, a102
    13, a102

Процесс​

Первый этап​

Создайте таблицу глобального словаря и загрузите значения столбца идентификатора заказа из CSV-файлов для установления сопоставления между STRING- и INTEGER-значениями.

  1. Создайте таблицу Primary Key для использования в качестве глобального словаря. Определите Primary Key order_uuid (тип STRING) и столбец значений order_id_int (тип AUTO INCREMENT INTEGER).

    к сведению

    Функция dict_mapping требует, чтобы таблица глобального словаря была таблицей Primary Key.

    CREATE TABLE dict (
        order_uuid STRING,
        order_id_int BIGINT AUTO_INCREMENT  -- Автоматически присваивает идентификатор каждому значению order_uuid.
    )
    PRIMARY KEY (order_uuid)
    DISTRIBUTED BY HASH (order_uuid)
    PROPERTIES("replicated_storage" = "true");

  2. Используйте Stream Load для пакетной загрузки столбца order_uuid из двух CSV-файлов в столбец order_uuid таблицы словаря dict. Убедитесь, что вы используете частичное обновление в режиме столбцов.

    curl --location-trusted -u root: \
        -H "partial_update: true" \
        -H "format: CSV" -H "column_separator:," -H "columns: id, order_uuid" \
        -T batch1.csv \
        -XPUT http://<fe_host>:<fe_http_port>/api/example_db/dict/_stream_load

    curl --location-trusted -u root: \
        -H "partial_update: true" \
        -H "format: CSV" -H "column_separator:," -H "columns: id, order_uuid" \
        -T batch2.csv \
        -XPUT http://<fe_host>:<fe_http_port>/api/example_db/dict/_stream_load

ПРИМЕЧАНИЕ

Если новые данные добавляются в источник данных до перехода к следующему этапу, все новые данные должны быть загружены в таблицу словаря для обеспечения существования сопоставления.

Второй этап​

Создайте целевую таблицу, включающую столбец идентификатора словаря с атрибутом dict_mapping. При загрузке данных заказов в целевую таблицу система автоматически свяжет их с таблицей словаря и вставит соответствующий идентификатор словаря.

  1. Создайте таблицу dest_table, включающую все столбцы из CSV-файлов. Вам также нужно определить INTEGER-столбец order_id_int (обычно BIGINT) для сопоставления со STRING-столбцом order_uuid, имеющий атрибут столбца dict_mapping. Будущий анализ запросов будет основан на этом столбце order_id_int.

    -- В целевой таблице определите BIGINT dict_mapping столбец `order_id_int` для сопоставления со STRING-столбцом `order_uuid`.
    CREATE TABLE dest_table (
        id BIGINT,
        order_uuid STRING, -- Этот столбец записывает STRING-идентификатор заказа.
        batch INT comment 'Используется для различения разных пакетов загрузки',
        order_id_int BIGINT AS dict_mapping('dict', order_uuid) -- Столбец dict_mapping идентификатора словаря соответствует `order_uuid`.
    )
    DUPLICATE KEY (id, order_uuid)
    DISTRIBUTED BY HASH(id);

  2. Загрузите данные в целевую таблицу с помощью Stream Load или любого другого доступного метода. Поскольку столбец order_id_int имеет атрибут dict_mapping, система автоматически получит идентификатор словаря из dict во время загрузки.

    curl --location-trusted -u root: \
        -H "format: CSV" -H "column_separator:," -H "columns: id, order_uuid, batch=1" \
        -T batch1.csv \
        -XPUT http://<fe_host>:<fe_http_port>/api/example_db/dest_table/_stream_load

    curl --location-trusted -u root: \
        -H "format: CSV" -H "column_separator:," -H "columns: id, order_uuid, batch=2" \
        -T batch2.csv \
        -XPUT http://<fe_host>:<fe_http_port>/api/example_db/dest_table/_stream_load

Третий этап​

Во время анализа запросов вы можете выполнять точную дедупликацию или соединения по INTEGER-столбцу order_id_int, что значительно повышает производительность по сравнению с использованием STRING-столбца order_uuid.

-- Точная дедупликация на основе BIGINT-типа order_id_int.
SELECT id, COUNT(DISTINCT order_id_int) FROM dest_table GROUP BY id ORDER BY id;
-- Точная дедупликация на основе STRING-типа order_uuid.
SELECT id, COUNT(DISTINCT order_uuid) FROM dest_table GROUP BY id ORDER BY id;

Вы также можете использовать bitmap-функции для ускорения точной дедупликации.

Использование bitmap-функций для ускорения точной дедупликации​

Для дальнейшего ускорения вычислений вы можете вставить INTEGER-значения столбцов таблицы словаря непосредственно в bitmap-столбец после создания глобального словаря. Впоследствии вы можете использовать bitmap-функции на этом bitmap-столбце для точной дедупликации.

Способ 1​

Если вы создали глобальный словарь и уже импортировали данные заказов в dest_table, выполните следующие шаги:

  1. Создайте Aggregate-таблицу dest_table_bitmap с двумя столбцами: BITMAP-столбец order_id_bitmap для агрегации с использованием функции bitmap_union() и INTEGER-столбец id. Эта таблица не включает исходный STRING-столбец, иначе каждый bitmap содержал бы только одно значение, что сводит на нет преимущество ускорения.

    CREATE TABLE dest_table_bitmap (
        id BIGINT,
        order_id_bitmap BITMAP BITMAP_UNION
    )
    AGGREGATE KEY (id)
    DISTRIBUTED BY HASH(id) BUCKETS 6;

  2. Вставьте данные в dest_table_bitmap. Вставьте данные из столбца id таблицы dest_table в столбец id, а INTEGER-столбец order_id_int из таблицы словаря dict (обработанный функцией to_bitmap()) — в столбец order_id_bitmap.

    INSERT INTO dest_table_bitmap (id, order_id_bitmap)
    SELECT id,  to_bitmap(dict_mapping('dict', order_uuid))
    FROM dest_table
    WHERE dest_table.batch = 1; -- Указывает разные пакеты.

    INSERT INTO dest_table_bitmap (id, order_id_bitmap)
    SELECT id, to_bitmap(dict_mapping('dict', order_uuid))
    FROM dest_table
    WHERE dest_table.batch = 2;

  3. Используйте функцию BITMAP_UNION_COUNT() на BITMAP-столбце для точной дедупликации.

    SELECT id, BITMAP_UNION_COUNT(order_id_bitmap) FROM dest_table_bitmap
    GROUP BY id ORDER BY id;

Способ 2​

Если вам не нужно сохранять конкретные данные заказов после создания глобального словаря и вы хотите загружать данные непосредственно в таблицу dest_table_bitmap, выполните следующие шаги:

  1. Создайте Aggregate-таблицу dest_table_bitmap с двумя столбцами: BITMAP-столбец order_id_bitmap для агрегации с использованием функции bitmap_union() и INTEGER-столбец id. Эта таблица не включает исходный STRING-столбец, иначе каждый bitmap содержал бы только одно значение, что сводит на нет преимущество ускорения.

    CREATE TABLE dest_table_bitmap (
        id BIGINT,
        order_id_bitmap BITMAP BITMAP_UNION
    )
    AGGREGATE KEY (id)
    DISTRIBUTED BY HASH(id) BUCKETS 6;

  2. Вставьте данные в Aggregate-таблицу. Вставьте данные из столбца id CSV-файлов в столбец id, а INTEGER-столбец order_id_int из таблицы словаря dict (обработанный функцией to_bitmap()) — в столбец order_id_bitmap.

    curl --location-trusted -u root: \
        -H "format: CSV" -H "column_separator:," \
        -H "columns: id, order_uuid,  order_id_bitmap=to_bitmap(dict_mapping('dict', order_uuid))" \
        -T batch1.csv \
        -XPUT http://<fe_host>:<fe_http_port>/api/example_db/dest_table_bitmap/_stream_load

    curl --location-trusted -u root: \
        -H "format: CSV" -H "column_separator:," \
        -H "columns: id, order_uuid, order_id_bitmap=to_bitmap(dict_mapping('dict', order_uuid))" \
        -T batch2.csv \
        -XPUT http:///<fe_host>:<fe_http_port>/api/example_db/dest_table_bitmap/_stream_load

  3. Используйте функцию BITMAP_UNION_COUNT() на BITMAP-столбце для точной дедупликации.

    SELECT id, BITMAP_UNION_COUNT(order_id_bitmap) FROM dest_table_bitmap
    GROUP BY id ORDER BY id;