блог о bi, №1 в рунете
Загрузка данных из внешних файловых источников с помощью gpfdist
Greenplum
Greenplum предоставляет обширный функционал по работе с данными во внешних источниках, и при выборе способа доступа к данным необходимо учитывать источник, объем и цель использования.
Для загрузки больших объемов данных из файлов, расположенных на удаленных хостах, в документации Greenplum рекомендуется использовать gpfdist. Что это такое и почему это хороший выбор будет рассказано в статье.
Сначала проведем краткий обзор внешних таблиц, gpfdist и gpload. Пройдемся по Best Practices загрузки данных, и, наконец, приведем пример алгоритма загрузки.
Внешние таблицы
Внешняя таблица – это таблица, метаданные которой хранятся в базе, а сами данные за ее пределами. Внешние таблицы позволяют работать с источниками за пределами Greenplum так, словно они являются таблицами в базе данных.
В Greenplum внешняя таблица создается с помощью предложения CREATE EXTERNAL TABLE с указанием параметров LOCATION и FORMAT.
В случае использования gpfdist, LOCATION будет выглядеть следующим образом
Для загрузки больших объемов данных из файлов, расположенных на удаленных хостах, в документации Greenplum рекомендуется использовать gpfdist. Что это такое и почему это хороший выбор будет рассказано в статье.
Сначала проведем краткий обзор внешних таблиц, gpfdist и gpload. Пройдемся по Best Practices загрузки данных, и, наконец, приведем пример алгоритма загрузки.
Внешние таблицы
Внешняя таблица – это таблица, метаданные которой хранятся в базе, а сами данные за ее пределами. Внешние таблицы позволяют работать с источниками за пределами Greenplum так, словно они являются таблицами в базе данных.
В Greenplum внешняя таблица создается с помощью предложения CREATE EXTERNAL TABLE с указанием параметров LOCATION и FORMAT.
В случае использования gpfdist, LOCATION будет выглядеть следующим образом
LOCATION('gpfdist://<hostname>:[<port>]/<file_pattern>')
При использовании защищенного подключения gpfdist меняется на gpfdists.
gpfdist может использовать маски файлов. Например, если file_pattern указан как *.txt, будут загружены все файлы с расширение .txt.
Когда маске соответствует несколько файлов, загрузка происходит последовательно, по одному файлу.
Параметр FORMAT определяет формат исходного файла и может принимать следующие значения: CSV, TEXT, CUSTOM. При указании CSV или TEXT, далее, в круглых скобках, можно указать характеристики форматирования текста:
gpfdist может использовать маски файлов. Например, если file_pattern указан как *.txt, будут загружены все файлы с расширение .txt.
Когда маске соответствует несколько файлов, загрузка происходит последовательно, по одному файлу.
Параметр FORMAT определяет формат исходного файла и может принимать следующие значения: CSV, TEXT, CUSTOM. При указании CSV или TEXT, далее, в круглых скобках, можно указать характеристики форматирования текста:
FORMAT 'TEXT'
[( [HEADER]
[DELIMITER [AS] '<delimiter>' | 'OFF']
[NULL [AS] '<null string>']
[ESCAPE [AS] '<escape>' | 'OFF']
[NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
[FILL MISSING FIELDS] )]
| 'CSV'
[( [HEADER]
[QUOTE [AS] '<quote>']
[DELIMITER [AS] '<delimiter>']
[NULL [AS] '<null string>']
[FORCE NOT NULL <column> [, ...]]
[ESCAPE [AS] '<escape>']
[NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
[FILL MISSING FIELDS] )]
| 'CUSTOM' (Formatter=<<formatter_specifications>>)
[ ENCODING '<encoding>' ]
[ [LOG ERRORS [PERSISTENTLY]] SEGMENT REJECT LIMIT <count>
[ROWS | PERCENT] ]
- HEADER – наличие заголовочной строки
- DELIMITER – символ, используемый для разделения данных. По умолчанию, для CSV – запятая; для TEXT – символ табуляции. В случае, когда производится загрузка неформатированного текста в таблицу с одной колонкой, можно указать OFF.
- NULL – символ, используемый для представления NULL-значений. По умолчанию, для CSV – пустая строка не заключенная в кавычки; для TEXT – \N
- ESCAPE – символ, используемый для экранирования escape-последовательностей: \n, \t, \100 и т.д. По умолчанию – обратный слэш.
- NEWLINE – символ перевода строки. По умолчанию определяется по первой строке. Указать возможно следующие варианты: CR, LF, CRLF
- QUOTE – ограничитель текста в CSV. Двойные кавычки по умолчанию.
- FORCE NOT NULL – В CSV перечисленные колонки обрабатываются так, словно они заключены в кавычки. NULL-значение по умолчанию, пустая строка без кавычек, будет интерпретировано как пустая строка.
- FILL MISSING FIELD – заполнение отсутствующих полей в конце строки NULL-значениями вместо поднятия ошибки.
ENCODING позволяет указать кодировку файла-источника. Перечень наборов символов, поддерживаемых в Greenplum, предоставлен по ссылке: https://docs.vmware.com/en/VMware-Greenplum/6/greenplum-database/ref_guide-character_sets.html
С помощью опции SEGMENT REJECT LIMIT можно установить количество (в абсолютном или процентном значении) строк на одном сегменте, в которых допустимы ошибки загрузки. Если количество ошибок превысит установленное значение, транзакция отменяется.
При использовании конструкции LOG ERRORS SEGMENT REJECT LIMIT, возникающие ошибки будут записаны и доступны для просмотра с помощью функции gp_read_error_log(). Ключевое слово PERSISTENTLY позволяет сохранять информацию об ошибках после того после удаления внешней таблицы.
Документация по внешним таблицам доступна для изучения по ссылке: https://docs.vmware.com/en/VMware-Greenplum/6/greenplum-database/ref_guide-sql_commands-CREATE_EXTERNAL_TABLE.html
gpfdist
gpfdist – это файл-серверная утилита, использующая HTTP-протокол для параллельной передачи данных в Greenplum или из него. Преимуществом gpfdist является то, что с его помощью в работе со внешними источниками параллельно задействованы все сегменты кластера Greenplum.
Для установки нужно лишь скопировать утилиту на ETL-сервер, откуда будет производиться загрузка, и добавить gpfdist в переменную PATH. После этого достаточно запустить gpfdist в директории с файлами, доступ к которым мы желаем предоставить.
gpfdist -d <директория с файлами данных> -p <порт подключения> -l <путь к файлу логов>
Где:
- d – локальный путь к директории
- p – порт, через который будет работать утилита (по умолчанию 8080)
- l – опциональный параметр, указывающий куда записывать логи работы.
Важно отметить, что gpfdist совместим в пределах одной мажорной версии Greenplum – gpfdist для Greenplum 5.x.x не будет работать вместе с Greenplum 6.x.x.
В дальнейшем, под инстансом будет подразумеваться один запущенный процесс утилиты gpfdist, а под хостом выделенный для ETL-процессов сервер.
Один инстанс gpfdist передает данные со скоростью до 200 МБ/с и по умолчанию поддерживает до 64 подключений от сегментов Greenplum.
За количество сегментов, обслуживаемых одним инстансом gpfdist отвечает параметр конфигурации gp_external_max_segs, изменить значение которого можно в файле postgresql.conf на мастер-ноде Greenplum. Значение данного параметра всегда должно быть четным числом, кратным количеству инстансов gpfdist.
Инстансы gpfdist можно запустить на множестве хостов, на каждом из хостов может работать несколько инстансов, что, при правильном проектировании, позволяет достичь большой скорости загрузки или выгрузки данных за счет максимального использования пропускной способности сети и параллелизма Greenplum.
Рисунок 1. Пример работы двух инстансов gpfdist, запущенных на разных сетевых интерфейсах одного ETL-хоста
С помощью gpfdist возможно выполнять трансформацию данных – для этого необходимо написать спецификацию трансформаций с использованием любого подходящего инструмента, создать YAML-файл с конфигурацией и передать его в параметрах утилиты. Это позволяет работать такими форматами данных, как, например, JSON или XML.
При чтении сжатых файлов с расширениями .gz, .bz2 или .zst файлы распаковываются "на лету". Однако необходимо отметить, что работа со сжатыми файлами не поддерживается на платформах Windows.
С параметрами утилиты можно ознакомиться в документации по ссылке https://docs.vmware.com/en/VMware-Greenplum/6/greenplum-database/utility_guide-ref-gpfdist.html
Gpload
gpload является скриптом на python для автоматизации ETL-задач c использованием gpfdist и внешних таблиц.
Так как gpload, по сути своей, является оберткой для gpfdist, не будем уделять ему слишком много внимания в рамках данной статьи и лишь отметим основные моменты.
С помощью gpload автоматизируются следующие шаги:
1) Запуск процессов gpfdist
2) Создание временной внешней таблицы на основании данных в файле-источнике и формата, указанного в управляющем файле.
3) Произведение операций INSERT, UPDATE или MERGE для обновления данных в целевой таблице
4) Удаление временной внешней таблицы
5) Завершение процессов gpfdist.
Все действия gpload проходят в рамках одной транзакции.
Для запуска gpload необходимо написать управляющий файл в формате YAML 1.1.
Структура управляющего файла и описание параметров предоставлены в документации по ссылке: https://gpdb.docs.pivotal.io/6-3/utility_guide/ref/gpload.html
Best Practices
Для повышения производительности загрузки и последующих запросов существуют некоторые рекомендации:
Пример загрузки
Предположим, есть таблица fact_example c индексом fact_example_id_idx. В нее необходимо загрузить данные из двух текстовых файлов, example_new_1.txt и example_new_2.txt, оба файла находятся на одном хосте с адресом 192.168.1.10.
Файлы имеют заголовочную строку, NULL-значения представлены пустой строкой ' ', разделителем является знак '|', для перевода строки используется последовательность CR+LF, а кодировкой является UTF-8.
Шаги для загрузки будут такими:
1) Располагаем файлы в различных директориях, например, example_new_1.txt будет располагаться в /var/load_1, а example_new_2.txt в /var/load_2
2) Запускаем в каждой директории на различных портах по инстансу gpfdist.
gpfdist -d /var/load_1 -p 8081
gpfdist -d /var/load_2 -p 8082
3) Создаем внешнюю таблицу
При чтении сжатых файлов с расширениями .gz, .bz2 или .zst файлы распаковываются "на лету". Однако необходимо отметить, что работа со сжатыми файлами не поддерживается на платформах Windows.
С параметрами утилиты можно ознакомиться в документации по ссылке https://docs.vmware.com/en/VMware-Greenplum/6/greenplum-database/utility_guide-ref-gpfdist.html
Gpload
gpload является скриптом на python для автоматизации ETL-задач c использованием gpfdist и внешних таблиц.
Так как gpload, по сути своей, является оберткой для gpfdist, не будем уделять ему слишком много внимания в рамках данной статьи и лишь отметим основные моменты.
С помощью gpload автоматизируются следующие шаги:
1) Запуск процессов gpfdist
2) Создание временной внешней таблицы на основании данных в файле-источнике и формата, указанного в управляющем файле.
3) Произведение операций INSERT, UPDATE или MERGE для обновления данных в целевой таблице
4) Удаление временной внешней таблицы
5) Завершение процессов gpfdist.
Все действия gpload проходят в рамках одной транзакции.
Для запуска gpload необходимо написать управляющий файл в формате YAML 1.1.
Структура управляющего файла и описание параметров предоставлены в документации по ссылке: https://gpdb.docs.pivotal.io/6-3/utility_guide/ref/gpload.html
Best Practices
Для повышения производительности загрузки и последующих запросов существуют некоторые рекомендации:
- Удаляйте индексы перед загрузкой. Создание индекса быстрее, чем постепенное обновление при добавлении строк. При необходимости можно увеличить значение параметра конфигурации maintenance_work_mem на время построения индекса. Однако, это может повлиять на скорость загрузки. Удалять и создавать индексы стоит только тогда, когда с системой не работают конечные пользователи.
- Если загрузка производилась в хип-таблицу, используйте VACUUM. Также VACUUM следует использовать на системных таблицах. Обязательно используйте ANALYZE на обновленной таблице. Актуальная статистика позволяет оптимизатору принимать наилучшие решения, что оказывает серьезное влияние на производительность запросов.
- Если при загрузке произошла ошибка, воспользуйтесь VACUUM для освобождения места на диске.
- Отключите автоматический сбор статистики на время загрузки, установив значение параметра конфигурации gp_autostats_mode в NONE.
- Распределяйте данные между хостами и запущенными на них инстансами gpfdist максимально равномерно, чтобы не допустить падения производительности из-за одного перегруженного элемента.
Пример загрузки
Предположим, есть таблица fact_example c индексом fact_example_id_idx. В нее необходимо загрузить данные из двух текстовых файлов, example_new_1.txt и example_new_2.txt, оба файла находятся на одном хосте с адресом 192.168.1.10.
Файлы имеют заголовочную строку, NULL-значения представлены пустой строкой ' ', разделителем является знак '|', для перевода строки используется последовательность CR+LF, а кодировкой является UTF-8.
Шаги для загрузки будут такими:
1) Располагаем файлы в различных директориях, например, example_new_1.txt будет располагаться в /var/load_1, а example_new_2.txt в /var/load_2
2) Запускаем в каждой директории на различных портах по инстансу gpfdist.
gpfdist -d /var/load_1 -p 8081
gpfdist -d /var/load_2 -p 8082
3) Создаем внешнюю таблицу
CREATE EXTERNAL TABLE ext_fact_example (like dim_goods_test)
LOCATION (
'gpfdist://192.168.1.10:8081/example_new_csv_1.txt',
'gpfdist://192.168.1.10:8082/example_new_csv_2.txt'
)
FORMAT 'TEXT' (
HEADER
DELIMITER AS '|'
NULL ''
NEWLINE 'CRLF'
)
ENCODING 'UTF8';
Допустим, что данные находятся в подготовленном виде, поэтому при создании внешней таблицы можно скопировать структуру целевой таблицы используя like
4) Отключаем автоматический сбор статистики, установив параметр конфигурации gp_autostats_mode в NONE
5) Удаляем существующие индексы на целевой таблице
4) Отключаем автоматический сбор статистики, установив параметр конфигурации gp_autostats_mode в NONE
5) Удаляем существующие индексы на целевой таблице
DROP INDEX fact_example_id_idx
6) Производим вставку данных в целевую таблицу
INSERT INTO fact_example
SELECT * FROM ext_fact_example
7) Возвращаем базу к состоянию до загрузки: восстанавливаем значение gp_autostats_mode, пересоздаем удаленные индексы.
CREATE INDEX fact_example_id_idx ON fact_example (id)
8) Если загрузка производилась в хип-таблицу, следует запустить VACUUM на ней. Также VACUUM следует использовать на системных таблицах. Использовать VACUUM на append-oriented таблицах не обязательно. На всех таблицах следует использовать ANALYZE.
9) В случае загрузки большого количества данных стоит проверить не появилось ли перекоса.
Это можно сделать с помощью следующего запроса:
9) В случае загрузки большого количества данных стоит проверить не появилось ли перекоса.
Это можно сделать с помощью следующего запроса:
SELECT gp_segment_id, count(*)
FROM fact_example
GROUP BY gp_segment_id
ORDER BY 1;
Заключение
Итак, в статье было рассмотрено как использовать gpfdist и, что не менее важно, почему его стоит использовать.
Надеемся, что мы смогли дать представление о механизмах работы внешних таблиц, gpfdist и принципах загрузки больших объемов данных в Greenplum.
Источники
https://docs.vmware.com/en/VMware-Greenplum/6/greenplum-database/admin_guide-external-g-gpfdist-protocol.html
https://docs.vmware.com/en/VMware-Greenplum/6/greenplum-database/admin_guide-external-g-using-the-greenplum-parallel-file-server--gpfdist-.html
https://docs.vmware.com/en/VMware-Greenplum/6/greenplum-database/utility_guide-ref-gpfdist.html
https://docs.vmware.com/en/VMware-Greenplum/6/greenplum-database/ref_guide-sql_commands-CREATE_EXTERNAL_TABLE.html
https://greenplum.docs.pivotal.io/6-4/best_practices/data_loading.html
https://gpdb.docs.pivotal.io/6-3/ref_guide/character_sets.html
https://gpdb.docs.pivotal.io/6-9/admin_guide/load/topics/g-optimizing-data-load-and-query-performance.html
https://docs.vmware.com/en/VMware-Greenplum/6/greenplum-database/admin_guide-load-topics-transforming-xml-data.html
https://docs.vmware.com/en/VMware-Greenplum/6/greenplum-database/ref_guide-sql_commands-CREATE_EXTERNAL_TABLE.html
https://gpdb.docs.pivotal.io/6-3/utility_guide/ref/gpload.html
https://cloud.yandex.ru/docs/managed-greenplum/operations/external-tables
Итак, в статье было рассмотрено как использовать gpfdist и, что не менее важно, почему его стоит использовать.
Надеемся, что мы смогли дать представление о механизмах работы внешних таблиц, gpfdist и принципах загрузки больших объемов данных в Greenplum.
Источники
https://docs.vmware.com/en/VMware-Greenplum/6/greenplum-database/admin_guide-external-g-gpfdist-protocol.html
https://docs.vmware.com/en/VMware-Greenplum/6/greenplum-database/admin_guide-external-g-using-the-greenplum-parallel-file-server--gpfdist-.html
https://docs.vmware.com/en/VMware-Greenplum/6/greenplum-database/utility_guide-ref-gpfdist.html
https://docs.vmware.com/en/VMware-Greenplum/6/greenplum-database/ref_guide-sql_commands-CREATE_EXTERNAL_TABLE.html
https://greenplum.docs.pivotal.io/6-4/best_practices/data_loading.html
https://gpdb.docs.pivotal.io/6-3/ref_guide/character_sets.html
https://gpdb.docs.pivotal.io/6-9/admin_guide/load/topics/g-optimizing-data-load-and-query-performance.html
https://docs.vmware.com/en/VMware-Greenplum/6/greenplum-database/admin_guide-load-topics-transforming-xml-data.html
https://docs.vmware.com/en/VMware-Greenplum/6/greenplum-database/ref_guide-sql_commands-CREATE_EXTERNAL_TABLE.html
https://gpdb.docs.pivotal.io/6-3/utility_guide/ref/gpload.html
https://cloud.yandex.ru/docs/managed-greenplum/operations/external-tables