Перейти к основному содержимому

Ускорение COUNT(DISTINCT) и соединений с AUTO INCREMENT и глобальным словарем

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

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

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

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

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

    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();

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

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

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

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

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

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

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

Решение​

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

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

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

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

В следующем примере используются два примера 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

Процесс​

Этап первый​

Создание таблицы глобального словаря и загрузка значений столбца ID заказа из 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  -- Автоматически назначить ID каждому значению order_uuid.
    )
    PRIMARY KEY (order_uuid)
    DISTRIBUTED BY HASH (order_uuid)
    PROPERTIES("replicated_storage" = "true");

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

    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

ПРИМЕЧАНИЕ

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

Этап второй​

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

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

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

  2. Загрузите данные в целевую таблицу с помощью Stream Load или любых других доступных методов. Поскольку столбец order_id_int имеет атрибут dict_mapping, система автоматически получит ID словаря из 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.

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

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

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

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

Метод первый​

Если вы уже построили глобальный словарь и импортировали данные заказов в 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;

Метод второй​

Если вам не нужно сохранять конкретные данные заказов после создания глобального словаря, и вы хотите загрузить данные непосредственно в таблицу 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;