Большую часть прошлого года я работал с Hexvarium. Базирующаяся в Атертоне, штат Калифорния, компания строит и управляет оптоволоконными сетями. В настоящее время у них есть несколько сетей в районе залива, но у них есть планы по расширению в США.

Моя роль заключается в управлении платформой данных, которая содержит 30 миллиардов записей примерно из 70 источников информации. Эти данные используются инженерами, разрабатывающими оптимальные планы развертывания оптоволоконной сети с помощью LocalSolver. Далее приведен пример одного из таких планов.

Наша платформа данных в основном состоит из PostGIS, ClickHouse и BigQuery. Данные поступают в самых разных, но чаще не в оптимальных форматах. Затем мы формируем и обогащаем данные, часто с помощью PostGIS или ClickHouse, прежде чем отправлять их в BigQuery. Затем инженеры, работающие с LocalSolver, будут получать свои данные из очищенной и актуальной версии в BigQuery.

Анализ подпитывает воображение инженеров, поэтому мы часто визуализируем данные, полученные с помощью Unfolded. Ниже представлена визуализация, которую мы создали для Speedtest от Ookla прошлым летом. Мы сгруппировали записи по уровню масштабирования H3 9 и взяли самую высокую среднюю скорость загрузки из каждого шестиугольника, нанеся их на карту.

Ниже приведена еще одна визуализация, которую мы создали, показывающая разницу в максимальной скорости широкополосного доступа в период с июня по ноябрь прошлого года. Данные в FCC предоставили провайдеры широкополосного доступа из США.

Интересные особенности DuckDB

DuckDB — это прежде всего работа Марка Раасвельдта и Ханнеса Мюлейзена. Он состоит из миллиона строк C++ и работает как отдельный двоичный файл. Разработка идет очень активно: количество коммитов в репозитории GitHub удваивается почти каждый год с момента его запуска в 2018 году. DuckDB использует синтаксический анализатор SQL PostgreSQL, механизм регулярных выражений RE2 от Google и оболочку SQLite.

SQLite поддерживает пять типов данных: NULL, INTEGER, REAL, TEXT и BLOB. Меня всегда это расстраивало, поскольку работа со временем требовала преобразований в каждом операторе SELECT, а невозможность описать поле как логическое означает, что программное обеспечение для анализа не могло автоматически распознавать и предоставлять определенные элементы управления пользовательского интерфейса и визуализацию этих полей.

К счастью, DuckDB поддерживает 25 типов данных из коробки, а дополнительные можно добавить с помощью расширений. Ведется работа над расширением, которое перенесет геопространственные функции PostGIS Пола Рэмси в DuckDB. Исаак Бродский, главный инженер Foursquare, материнской компании Unfolded, также недавно опубликовал расширение H3 для DuckDB. И хотя еще ничего не опубликовано, предпринимаются попытки встроить GDAL через его интерфейс на основе Arrow в DuckDB.

Min/Max индекс создается для каждого сегмента столбца в DuckDB. Именно благодаря этому типу индекса большинство баз данных OLAP так быстро отвечают на запросы агрегирования. Расширения Parquet и JSON поставляются в официальной сборке, и их использование хорошо задокументировано. Для файлов Parquet поддерживаются сжатие Snappy и ZStandard.

Документация DuckDB хорошо организована и очень лаконична, с примерами рядом с большинством описаний.

Запуск DuckDB

Официальная версия DuckDB не содержит расширений Geospatial и H3, используемых в этом посте, поэтому я скомпилирую DuckDB с этими расширениями.

Приведенные ниже команды были выполнены на экземпляре e2-standard-4 в Google Cloud под управлением Ubuntu 20 LTS. Эта виртуальная машина содержит 4 виртуальных ЦП и 16 ГБ ОЗУ. Он был запущен в Лос-Анджелесе, в регионе us-west2-b, и имеет сбалансированный персистентный диск объемом 100 ГБ. Запуск стоил 0,18 доллара в час.

Ниже будут установлены пакеты ПО, используемые в этом посте.

$ sudo apt update
$ sudo apt install \
    awscli \
    build-essential \
    libssl-dev \
    pigz \
    unzip

Для компиляции расширения H3 требуется CMake версии 3.20+, поэтому я сначала соберу его.

$ cd ~
$ wget -c https://github.com/Kitware/CMake/releases/download/v3.20.0/cmake-3.20.0.tar.gz
$ tar -xzf cmake-3.20.0.tar.gz
$ cd cmake-3.20.0
$ ./bootstrap --parallel=$(nproc)
$ make -j$(nproc)
$ sudo make install

Следующие команды будет собирать как DuckDB, так и расширение H3 для него. Расширение geo привязано к коммиту DuckDB c817201, поэтому я также прикреплю к нему расширение H3.

$ git clone https://github.com/isaacbrodsky/h3-duckdb ~/duckdb_h3
$ cd ~/duckdb_h3
$ git submodule update --init

$ cd duckdb
$ git checkout c817201
$ cd ..

$ CMAKE_BUILD_PARALLEL_LEVEL=$(nproc) \
    make release

Далее будет создано расширение, которое перенесет части функциональности PostGIS в DuckDB.

$ git clone https://github.com/handstuyennn/geo ~/duckdb_geo
$ cd ~/duckdb_geo
$ git submodule init
$ git submodule update --recursive --remote

$ mkdir -p build/release

$ cmake \
    ./duckdb/CMakeLists.txt \
    -DEXTERNAL_EXTENSION_DIRECTORIES=../geo \
    -DCMAKE_BUILD_TYPE=RelWithDebInfo \
    -DEXTENSION_STATIC_BUILD=1 \
    -DBUILD_TPCH_EXTENSION=1 \
    -DBUILD_PARQUET_EXTENSION=1 \
    -B build/release

$ CMAKE_BUILD_PARALLEL_LEVEL=$(nproc) \
    cmake --build build/release

Создам шелл скрипт, который запустит двоичный файл DuckDB с поддержкой неподписанных расширений.

$ echo "$HOME/duckdb_geo/build/release/duckdb -unsigned \$@" \
    | sudo tee /usr/sbin/duckdb
$ sudo chmod +x /usr/sbin/duckdb

Настрою файл конфигурации DuckDB для загрузки обоих расширений по умолчанию.

$ vi ~/.duckdbrc

.timer on
LOAD '/home/mark/duckdb_h3/build/release/h3.duckdb_extension';
LOAD '/home/mark/duckdb_geo/build/release/extension/geo/geo.duckdb_extension';

Запущу DuckDB и проверю, работают ли расширения.

$ duckdb

SELECT h3_cell_to_parent(CAST(586265647244115967 AS ubigint), 1) test;
┌────────────────────┐
│        test        │
│       uint64       │
├────────────────────┤
│ 581764796395814911 │
└────────────────────┘

SELECT ST_MAKEPOINT(52.347113, 4.869454) test;
┌────────────────────────────────────────────┐
│                    test                    │
│                 geography                  │
├────────────────────────────────────────────┤
│ 01010000001B82E3326E2C4A406B813D26527A1340 │
└────────────────────────────────────────────┘

Мне кажется полезным, что по умолчанию тип данных отображается под каждым именем поля.

Набор данных Ookla Speedtest

Ookla публикует свои данные Speedtest в AWS в формате Parquet. Ниже я скачаю набор данных 2022 года для мобильных устройств.

$ aws s3 sync \
    --no-sign-request \
    s3://ookla-open-data/parquet/performance/type=mobile/year=2022/ \
    ./

Структура путей к папкам предназначена для поддержки секционирования Apache Hive. Я буду использовать только четыре файла Parquet, поэтому я перемещу их в одну папку.

$ mv quarter\=*/*.parquet ./

Ниже приведены размеры для каждого из файлов. Всего они содержат 15 738 442 строки данных.

$ ls -lht *.parquet

173M  2022-10-01_performance_mobile_tiles.parquet
180M  2022-07-01_performance_mobile_tiles.parquet
179M  2022-04-01_performance_mobile_tiles.parquet
174M  2022-01-01_performance_mobile_tiles.parquet

Ниже приведена схема, используемая в этих файлах Parquet.

$ echo "SELECT name,
               type,
               converted_type
        FROM parquet_schema('2022-10*.parquet');" \
    | duckdb

┌────────────┬────────────┬────────────────┐
│    name    │    type    │ converted_type │
│  varchar   │  varchar   │    varchar     │
├────────────┼────────────┼────────────────┤
│ schema     │ BOOLEAN    │ UTF8           │
│ quadkey    │ BYTE_ARRAY │ UTF8           │
│ tile       │ BYTE_ARRAY │ UTF8           │
│ avg_d_kbps │ INT64      │ INT_64         │
│ avg_u_kbps │ INT64      │ INT_64         │
│ avg_lat_ms │ INT64      │ INT_64         │
│ tests      │ INT64      │ INT_64         │
│ devices    │ INT64      │ INT_64         │
└────────────┴────────────┴────────────────┘

Ookla использовала сжатие Snappy для каждого столбца. DuckDB может проводить диагностику того, как расположены данные, и предоставлять статистическую информацию по каждому столбцу. Ниже приведены сведения о столбце тайла.

$ echo ".mode line
        SELECT *
        EXCLUDE (stats_min_value,
                 stats_max_value)
        FROM parquet_metadata('2022-10-01_*.parquet')
        WHERE path_in_schema = 'tile'
        AND   row_group_id   = 0;" \
    | duckdb


              file_name = 2022-10-01_performance_mobile_tiles.parquet
           row_group_id = 0
     row_group_num_rows = 665938
  row_group_num_columns = 7
        row_group_bytes = 31596007
              column_id = 1
            file_offset = 24801937
             num_values = 665938
         path_in_schema = tile
                   type = BYTE_ARRAY
              stats_min =
              stats_max =
       stats_null_count = 0
   stats_distinct_count =
            compression = SNAPPY
              encodings = PLAIN_DICTIONARY, PLAIN, RLE, PLAIN
      index_page_offset = 0
 dictionary_page_offset = 3332511
       data_page_offset = 3549527
  total_compressed_size = 21469426
total_uncompressed_size = 124569336

Импорт Parquet данных

Теперь могу импортировать все четыре файла Parquet в DuckDB с помощью одного оператора SQL. Команда добавит дополнительный столбец с указанием имени файла, из которого происходит каждая строка данных. Из исходных 706 МБ Parquet и дополнительная колонка превратились в файл DuckDB размером 1,4 ГБ.

$ echo "CREATE TABLE mobile_perf AS
        SELECT *
        FROM read_parquet('*.parquet',
                          filename=true);" \
      | duckdb ~/ookla.duckdb

В качестве альтернативы, используя расширение DuckDB HTTPFS, я могу загружать файлы Parquet непосредственно из S3.

$ echo "INSTALL httpfs;
        CREATE TABLE mobile_perf AS
        SELECT *
        FROM parquet_scan('s3://ookla-open-data/parquet/performance/type=mobile/year=2022/*/*.parquet',
                          FILENAME=1);" \
      | duckdb ~/ookla.duckdb

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

Error: near line 1: IO Error: Failed to download extension "httpfs" at URL "http://extensions.duckdb.org/7813eea926/linux_amd64/httpfs.duckdb_extension.gz"
Extension "httpfs" is an existing extension.

Are you using a development build? In this case, extensions might not (yet) be uploaded.

Структура таблицы DuckDB

Ниже приведена схема таблицы, которую автоматически составляет DuckDB.

$ echo '.schema --indent' \
    | duckdb ~/ookla.duckdb

CREATE TABLE mobile_perf(
  quadkey VARCHAR,
  tile VARCHAR,
  avg_d_kbps BIGINT,
  avg_u_kbps BIGINT,
  avg_lat_ms BIGINT,
  tests BIGINT,
  devices BIGINT,
  filename VARCHAR
);

Ниже приведен пример записи.

$ echo '.mode line
        SELECT *
        FROM mobile_perf
        LIMIT 1' \
    | duckdb ~/ookla.duckdb

   quadkey = 0022133222330023
      tile = POLYGON((-160.043334960938 70.6363054807905, -160.037841796875 70.6363054807905, -160.037841796875 70.6344840663086, -160.043334960938 70.6344840663086, -160.043334960938 70.6363054807905))
avg_d_kbps = 15600
avg_u_kbps = 14609
avg_lat_ms = 168
     tests = 2
   devices = 1
  filename = 2022-10-01_performance_mobile_tiles.parquet

Ниже приведены схемы сжатия, используемые в каждом из первых сегментов полей в первой группе строк.

$ echo "SELECT column_name,
               segment_type,
               compression,
               stats
        FROM pragma_storage_info('mobile_perf')
        WHERE row_group_id = 0
        AND   segment_id   = 0
        AND   segment_type != 'VALIDITY';" \
    | duckdb ~/ookla.duckdb

┌─────────────┬──────────────┬─────────────┬───────────────────────────────────┐
│ column_name │ segment_type │ compression │               stats               │
│   varchar   │   varchar    │   varchar   │              varchar              │
├─────────────┼──────────────┼─────────────┼───────────────────────────────────┤
│ quadkey     │ VARCHAR      │ FSST        │ [Min: 00221332, Max: 02123303, …  │
│ tile        │ VARCHAR      │ FSST        │ [Min: POLYGON(, Max: POLYGON(, …  │
│ avg_d_kbps  │ BIGINT       │ BitPacking  │ [Min: 1, Max: 3907561][Has Null…  │
│ avg_u_kbps  │ BIGINT       │ BitPacking  │ [Min: 1, Max: 917915][Has Null:…  │
│ avg_lat_ms  │ BIGINT       │ BitPacking  │ [Min: 0, Max: 2692][Has Null: f…  │
│ tests       │ BIGINT       │ BitPacking  │ [Min: 1, Max: 1058][Has Null: f…  │
│ devices     │ BIGINT       │ BitPacking  │ [Min: 1, Max: 186][Has Null: fa…  │
│ filename    │ VARCHAR      │ Dictionary  │ [Min: 2022-10-, Max: 2022-10-, …  │
└─────────────┴──────────────┴─────────────┴───────────────────────────────────┘

Обогащение данных

Я запущу DuckDB с только что созданной базой данных, добавлю три столбца для уровней масштабирования 7, 8 и 9, а затем приготовлю значения шестиугольников из столбца "геометрия" тайла .

$ duckdb ~/ookla.duckdb

ALTER TABLE mobile_perf ADD COLUMN h3_7 VARCHAR(15);
ALTER TABLE mobile_perf ADD COLUMN h3_8 VARCHAR(15);
ALTER TABLE mobile_perf ADD COLUMN h3_9 VARCHAR(15);

UPDATE mobile_perf
SET h3_7 =
        printf('%x',
               h3_latlng_to_cell(
                   ST_Y(ST_CENTROID(ST_GEOMFROMTEXT(tile))),
                   ST_X(ST_CENTROID(ST_GEOMFROMTEXT(tile))),
                   7)::bigint),

    h3_8 =
        printf('%x',
               h3_latlng_to_cell(
                   ST_Y(ST_CENTROID(ST_GEOMFROMTEXT(tile))),
                   ST_X(ST_CENTROID(ST_GEOMFROMTEXT(tile))),
                   8)::bigint),

    h3_9 =
        printf('%x',
               h3_latlng_to_cell(
                   ST_Y(ST_CENTROID(ST_GEOMFROMTEXT(tile))),
                   ST_X(ST_CENTROID(ST_GEOMFROMTEXT(tile))),
                   9)::bigint);

Ниже приведен пример расширенной записи.

.mode line
SELECT h3_7,
       h3_8,
       h3_9,
       tile
FROM mobile_perf
LIMIT 1

h3_7 = 8730e0ae9ffffff
h3_8 = 8830e0ae9dfffff
h3_9 = 8930e0ae9d3ffff
tile = POLYGON((126.837158203125 37.5576424267952, 126.842651367188 37.5576424267952, 126.842651367188 37.5532876459577, 126.837158203125 37.5532876459577, 126.837158203125 37.5576424267952))

Когда я попытался выполнить описанное выше на 4-ядерной виртуальной машине GCP e2-standard-4, я оставил задание работать на ночь, и оно еще не было завершено, когда проверил его утром. Позже запустил описанное выше на 16-ядерной виртуальной машине GCP e2-standard-16, и 15,7 млн записей были обогащены за 2 минуты 21 секунду. Использование ОЗУ имело верхнюю границу ~ 7 ГБ. Я поделился этим с Исааком, чтобы понять, есть ли какие-то очевидные исправления, которые можно было бы применить.

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

CREATE TABLE mobile_perf2 AS
    SELECT quadkey,
           tile,
           avg_d_kbps,
           avg_u_kbps,
           avg_lat_ms,
           tests,
           devices,
           filename,

           printf('%x',
                  h3_latlng_to_cell(
                      ST_Y(ST_CENTROID(ST_GEOMFROMTEXT(tile))),
                      ST_X(ST_CENTROID(ST_GEOMFROMTEXT(tile))),
                      7)::bigint) h3_7,

           printf('%x',
                  h3_latlng_to_cell(
                      ST_Y(ST_CENTROID(ST_GEOMFROMTEXT(tile))),
                      ST_X(ST_CENTROID(ST_GEOMFROMTEXT(tile))),
                      8)::bigint) h3_8,

           printf('%x',
                  h3_latlng_to_cell(
                      ST_Y(ST_CENTROID(ST_GEOMFROMTEXT(tile))),
                      ST_X(ST_CENTROID(ST_GEOMFROMTEXT(tile))),
                      9)::bigint) h3_9
    FROM mobile_perf;

Экспорт CSV

Далее создается CSV-файл сжатый с помощью GZIP для содержимого таблицы mobile_perf. Это было сделано для версии таблицы, которая не содержала шестиугольников H3 из приведенного выше обогащения.

$ echo "COPY (SELECT *
              FROM mobile_perf)
        TO 'mobile_perf.csv.gz'
        WITH (HEADER 1,
              COMPRESSION gzip);" \
    | duckdb ~/ookla.duckdb

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

Error: unable to open database "/home/mark/mobile_perf.duckdb": IO Error: Could not set lock on file "/home/mark/mobile_perf.duckdb": Resource temporarily unavailable

Если вы создаете файл для массового распространения, возможно, стоит повторно сжать вышеуказанное с помощью pigz. Его уровень сжатия настраивается, он может использовать преимущества нескольких ядер и создавать файлы меньшего размера, чем GNU GZIP.

Следующая команда завершилась в 1,12 раза быстрее, чем предыдущая, с файлом результата на 2,5 МБ меньше.

$ echo ".mode csv
        SELECT *
        FROM mobile_perf;" \
    | duckdb ~/ookla.duckdb \
    | pigz -9 \
    > mobile_perf.csv.gz

Если размер файла не является такой проблемой, использование параметра -1 создаст файл в 1,6 раза быстрее, только в 1,25 раза больше.

Порядок полей и ключи сортировки сильно влияют на способность GZIP к сжатию. С 8 полями существует 40 320 перестановок порядка полей, поэтому попытка найти наиболее сжимаемый вариант займет слишком много времени. Тем не менее, переупорядочить строки по каждому столбцу и посмотреть, какие из них сжимаются больше всего, можно сделать достаточно быстро.

$ COLUMNS=`echo ".mode list
                 SELECT column_name
                 FROM   information_schema.columns
                 WHERE  table_name = 'mobile_perf';" \
             | duckdb ~/ookla.duckdb \
             | tail -n +2`

$ for COL in $COLUMNS; do
      echo $COL
      echo "COPY (SELECT *
                  FROM mobile_perf
                  ORDER BY $COL)
            TO 'mobile_perf.$COL.csv.gz'
            WITH (HEADER 1,
                  COMPRESSION gzip);" \
        | duckdb ~/ookla.duckdb
  done

Для приведенного выше требуется доступ для записи в базу данных, поэтому я буду выполнять команды последовательно, а не параллельно с xargs.

DuckDB может использовать несколько ядер для большей части вышеуказанной работы, но некоторые задачи должны выполняться последовательно на одном ядре. Возможно, стоит продублировать исходную базу данных DuckDB и протестировать каждый ключ сортировки в своей собственной базе данных, если вы хотите максимально использовать доступные вам процессорные ядра.

При сортировке по avg_u_kbps был получен файл CSV, сжатый с помощью GZIP, размером 841 МБ, а при сортировке по quadkey — файл размером 352 МБ.

Вышеупомянутое потребовало почти всех 16 ГБ ОЗУ, которые у меня были в этой системе. Помните об этом для больших наборов данных и/или сред с ограниченным объемом оперативной памяти.

ZStandard — это более новый компрессор для сжатия без потерь, который должен сжимать так же, как GZIP, но примерно в 3-6 раз быстрее. Я подробно рассказывал про это в своем минималистском руководстве по сжатию без потерь. Если потребители ваших данных могут открывать файлы, сжатые ZStandard, их создание будет намного быстрее.

Следующая команда закончилось в 3,4 раза быстрее, чем его аналог GZIP, при этом вывод был примерно на 6% больше.

$ echo "COPY (SELECT *
              FROM mobile_perf)
        TO 'mobile_perf.csv.zstd'
        WITH (HEADER 1,
              COMPRESSION zstd);" \
    | duckdb ~/ookla.duckdb

Опять же, если вышеперечисленное было отсортировано по avg_u_kbps, будет создан файл CSV со сжатием ZStandard размером 742 МБ, а сортировка по quadkey даст файл размером 367 МБ.

Экспорт Parquet

В моей публикации «Faster PostgreSQL To BigQuery Transfers» в блоге обсуждалось преобразование Microsoft Buildings, набора данных GeoJSON на 130M записей в файл Parquet, сжатый Snappy. В том посте ClickHouse справился с этой задачей в 1,38 раза быстрее, чем его ближайший конкурент. Теперь запускаю ту же рабочую нагрузку через DuckDB.

Используя внешний SSD-накопитель на моем MacBook Pro с процессором Intel 2020 года выпуска, ClickHouse создал файл за 35,243 секунды. После работы над оптимизацией совместно с командой DuckDB, я смог выполнить ту же рабочую нагрузку за 30,826 секунды, и, если бы я был готов обработать восемь файлов Parquet вместо одного, это время сократилось до 25,146 секунды. Это был SQL, который работал быстрее всего.

$ echo "COPY (SELECT *
              FROM read_ndjson_objects('California.jsonl'))
        TO 'cali.duckdb.pq' (FORMAT 'PARQUET',
                             CODEC  'Snappy',
                             PER_THREAD_OUTPUT TRUE);" \
    | duckdb

Обратите внимание на использование read_ndjson_objects вместо read_json_objects, это значительно улучшило производительность. Кроме того, PER_THREAD_OUTPUT TRUE создаст несколько файлов. Уберите этот параметр, если вы хотите создать один файл Parquet.

Но с учетом сказанного, та же рабочая нагрузка, выполняемая на виртуальной машине Google Cloud e2-highcpu-32 с Ubuntu 20 LTS, показала, что ClickHouse превзошел DuckDB в 1,44 раза. Марк Раасвелдт предположил, что на этапе анализа сжатия DuckDB могут быть дальнейшие улучшения, поэтому есть шанс, что в ближайшем будущем мы увидим уменьшение разрыва в производительности по сравнению с ClickHouse.

PS. Ну а если вам не хочется тратить время на настройку компиляции и хотите легко собрать расширения для более свежей версии duckdb с новыми фичами(как например map_entries, map_values, map_keys из #6522), то можете взять этот Docker файл за основу.

Геопространственная DuckDB