Загрузка 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 и входными параметрами являются относительный путь к файлу и название кодировки.

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, уже можно проводить конкретные действия, от поставленной задачи, например:

Создать временную таблицу;
Загрузить данные во временную таблицу используя функцию загрузки из файла, которая выше;
Если файл имеет сложную структуру, то возможно придется использовать подзапросы;
Провести какие-то действия с данными из временной таблицы.

Так как Greenplum основан на PosgreSQL, то функции работы с XML у него такие же: выполняют запросы к XML-документам на языке XPath 1.0.

В данной работе будет произведена запись данных в таблицу. Для этого будут использованы функции 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 здесь не показываются, так как цель статьи показать возможный вариант загрузки данных из файла.

Вернуться к блогу