блог о bi, №1 в рунете
Загрузка XML-файлов
в Greenplum
В данной статье будет описан подход к загрузке xml-файлов в Greenplum. Была поставлена задача разработать хранилище данных в Greenplum и загружать данные фактов и справочников непосредственно из xml-файлов, так как данные предоставлялись в файлах формата xml.
Многие хранят данные в xml-файлах, поэтому необходимость загружать данные из файлов такого вида является распространенной задачей.
Данная статья предлагает подход, когда файлы находятся непосредственно на кластере БД и нет необходимости использовать утилиту gpfdist. Для чтения файлов существуют функции pg_read_file и pg_read_binary_file.
Функция pg_read_file возвращает фрагмент текстового файла. Если параметры offset и length опущены, возвращается всё содержимое файла. Прочитанные из файла байты обрабатываются как символы в серверной кодировке.
А функция pg_read_binary_file подобна pg_read_file, но её результат имеет тип bytea; как следствие, никакие проверки кодировки не выполняются. В сочетании с convert_from эту функцию можно применять для чтения файлов в произвольной кодировке.
Чтобы получить значение типа xml из текстовой строки, используется функция xmlparse.
В используемом подходе была создана функция getxmldocument, которая возвращает значение в формате типа xml и входными параметрами являются относительный путь к файлу и название кодировки.
Данная статья предлагает подход, когда файлы находятся непосредственно на кластере БД и нет необходимости использовать утилиту gpfdist. Для чтения файлов существуют функции pg_read_file и pg_read_binary_file.
Функция pg_read_file возвращает фрагмент текстового файла. Если параметры offset и length опущены, возвращается всё содержимое файла. Прочитанные из файла байты обрабатываются как символы в серверной кодировке.
А функция pg_read_binary_file подобна pg_read_file, но её результат имеет тип bytea; как следствие, никакие проверки кодировки не выполняются. В сочетании с convert_from эту функцию можно применять для чтения файлов в произвольной кодировке.
Чтобы получить значение типа xml из текстовой строки, используется функция xmlparse.
В используемом подходе была создана функция getxmldocument, которая возвращает значение в формате типа xml и входными параметрами являются относительный путь к файлу и название кодировки.
CREATE OR REPLACE FUNCTION public.getxmldocument(p_filename varchar, charset varchar)
RETURNS xml
LANGUAGE sql
SECURITY DEFINER
VOLATILE
AS $$
SELECT
XMLPARSE(DOCUMENT convert_from(
pg_read_binary_file($1), $2));
$$
EXECUTE ON ANY;
Чтобы использовать функции типа pg_read_file, нужно быть суперпользователем. Если вы не являетесь им, попросите у своего администратора чтобы дал такую роль, если по причине внутренней политике вы не можете им быть, попросите, чтобы суперпользователь создал функцию подобной той, которая выше, и дал права другим пользователям на ее использование. Чтобы узнать больше про роли, прочитайте короткий раздел документации Managing Roles and Privileges.
После написания функции получения данных из файла, в данном случае в формате типа xml, уже можно проводить конкретные действия, от поставленной задачи, например:
В данной работе будет произведена запись данных в таблицу. Для этого будут использованы функции unnest и xpath. Функция xpath возвращает массив XML-значений, соответствующих набору узлов, полученному при вычислении выражения XPath. А unnest разворачивает массив в набор строк (элементы массива прочитываются в порядке хранения).
Структура XML-файла для примера:
- Создать временную таблицу;
- Загрузить данные во временную таблицу используя функцию загрузки из файла, которая выше;
- Если файл имеет сложную структуру, то возможно придется использовать подзапросы;
- Провести какие-то действия с данными из временной таблицы.
В данной работе будет произведена запись данных в таблицу. Для этого будут использованы функции unnest и xpath. Функция xpath возвращает массив XML-значений, соответствующих набору узлов, полученному при вычислении выражения XPath. А unnest разворачивает массив в набор строк (элементы массива прочитываются в порядке хранения).
Структура XML-файла для примера:
<?xml version="1.0" encoding="UTF-8"?>
<root><success>1</success><type>warehouse</type><count>31</count>
<warehouses>
<warehouse>
<id>1</id>
<number/>
<name>Органик Нева, Лебедева 31</name>
</warehouse>
<warehouse>
<id>2</id>
<number/>
<name>Лебедева 31, карантин</name>
</warehouse>
…
<warehouse>
<id>24</id>
<number>1</number>
<name>НЕВАПРОМСНАБ</name>
</warehouse>
…
</warehouses></root>
Пример загрузки в таблицу представлен ниже.
INSERT INTO tempstore(
store_id,
"name",
store_number
)
select
cast(cast((xpath('//id/text()',wayp.pt))[1] as varchar) as int) as store_id,
cast((xpath('//name/text()',wayp.pt))[1] as varchar(500)) as "name",
cast((xpath('//number/text()',wayp.pt))[1] as varchar(100)) as store_number
from(
select
unnest(xpath('//warehouse',g)) as pt
from getxmldocument('virtualpos/stores.xml','windows-1251') as g) as wayp;
Данная загрузка была разбита на подзапрос из-за наличия пустых элементов number в документе.
Итоговый результат загрузки представлен на скрине.
Итоговый результат загрузки представлен на скрине.
В данной статье была продемонстрирована работа в Greenplum по загрузке xml-файла в таблицу. Полные действия по загрузке данных и их распределению в Greenplum здесь не показываются, так как цель статьи показать возможный вариант загрузки данных из файла.