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

Большую часть прошлого года я работал с 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 файл за основу.