Внешние веб-таблицы в Greenplum и 2 способа их создания

Что такое внешние веб-таблицы, зачем они нужны, чем отличаются от обычных external tables и как создать такую таблицу в Greenplum на основе команд и на основе URL.

Зачем нужны внешние веб-таблицы в Greenplum

О том, что в Greenplum есть внешние (external) и сторонние (foreign) таблицы, которые обеспечивают доступ к данным, хранящимся в внешних источниках, мы уже писали здесь. Однако, помимо обычных внешних таблиц, в Greenplum также есть и внешние веб-таблицы (external web-tables), которые позволяют Greenplum обрабатывать динамические источники данных как обычные таблицы. Таким образом, внешние веб-таблицы позволяют подключаться к актуальным данным, размещенным на веб-ресурсах. Это особенно полезно для анализа данных в реальном времени, например, чтобы обрабатывать актуальные курсы валют или котировки акций.

Также внешние веб-таблицы позволяют легко интегрировать данные из разных источников без необходимости заливки данных в локальную базу. К примеру, чтобы понять влияние погодных условий на динамику продаж, можно интегрировать данные о погоде из веб-ресурсов для поиска закономерностей о связи температуры воздуха и осадков с продажами товаров в разных регионах страны. При этом данные обновляются автоматически, без ручного вмешательства. В частности, система управления запасами может автоматически обновлять данные о наличии товаров на складе, получая информацию с веб-сервиса поставщика.

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

Однако, поскольку данные веб-таблиц меняются по мере выполнения запросов, они не подлежат повторному сканированию. Чтобы в Greenplum определить таблицу как внешнюю веб-таблицу, это надо указать при ее создании в команде CREATE:

CREATE EXTERNAL WEB TABLE

Создавать внешние веб-таблицы можно на основе команд или URL, не смешивая эти определения в одной инструкции. Как это сделать, рассмотрим далее.

Внешние веб-таблицы на основе команд

Для создания внешней веб-таблицы на основе команд в инструкцию CREATE добавляется ключевое слово EXECUTE. Например, следующий код создает внешнюю веб-таблицу output, которая содержит один текстовый столбец с данными, возвращаемыми скриптом myprogram.sh при его выполнении:

CREATE EXTERNAL WEB TABLE output (output text)
    EXECUTE 'PATH=/home/gpadmin/programs; export PATH; myprogram.sh' 
    FORMAT 'TEXT';

Команда EXECUTE указывает, что данные для внешней таблицы будут получены путем выполнения указанной команды или скрипта. Сперва, устанавливается переменная окружения PATH, включающая директорию /home/gpadmin/programs. Затем, эта переменная экспортируется, чтобы она была доступна для последующих команд. Наконец, выполняется скрипт myprogram.sh.

А следующий код создает внешнюю веб-таблицу с 2-мя столбцами: целочисленный linenum и текстовый message:

CREATE EXTERNAL WEB TABLE log_output 
    (linenum int, message text) 
    EXECUTE '/var/load_scripts/get_log_data.sh' ON HOST 
    FORMAT 'TEXT' (DELIMITER '|');

Исходные данные для этой таблицы представлены в текстовом формате, и поля в строках разделены символом вертикальной черты. При этом данные будут получены путем выполнения скрипта, хранящегося в директории /var/load_scripts/get_log_data.sh на хосте базы данных. Поскольку скрипт явно ссылается на директорию хоста, на котором он запущен, он должен быть  доступен для выполнения пользователем gpadmin и располагаться в одном и том же месте на хостах координатора или сегмента. Если данные для обработки хранятся на нескольких хостах Greenplum, скрипт придется запустить на каждом хосте сегмента. Например, если каждый хост сегмента запускает 3 основных экземпляра сегмента, которые имеют выходные строки для обработки, команду создания внешней таблицы с оператором EXECUTE надо запустить 3 раза на каждом хосте сегмента или на координаторе. Можно вручную ограничить количество экземпляров сегмента, которые запускают команду создания веб-таблицы. Все сегменты, включенные в определение веб-таблицы в предложении ON, запускают команду создания внешней таблицы с оператором EXECUTE параллельно.

При этом данные актуальны на момент выполнения команды. Команда создания внешней таблицы с оператором EXECUTE запускается из базы данных и не может получить доступ к переменным среды из файлов .bashrc или .profile.

Внешние веб-таблицы на основе URL

Вторым способ создать внешнюю веб-таблицу в Greenplum является указание URL-адресов. Веб-таблица на основе URL-адресов получает доступ к данным с веб-сервера с использованием протокола HTTP. Данные веб-таблицы являются динамическими и не подлежат повторному сканированию. Чтобы создать внешнюю веб-таблицу Greenplum на основе URL-адресов, надо укажите расположение файлов на веб-сервере с помощью http://. Файлы веб-данных должны находиться на веб-сервере, к которому могут получить доступ хосты сегмента Greenplum. Количество указанных URL-адресов соответствует количеству экземпляров сегмента, которые работают параллельно для доступа к веб-таблице. Например, если указать 3 внешних файла для Greenplum с 8-ю основными сегментами, 3 из 8 сегментов будут получать доступ к веб-таблице параллельно во время выполнения запроса.

Следующий код создаёт внешнюю таблицу, которая позволяет получать и обрабатывать данные о корпоративных расходах из нескольких CSV-файлов, размещённых в различных отделах компании:

CREATE EXTERNAL WEB TABLE ext_expenses (name text, 
  date date, amount float4, category text, description text) 
  LOCATION ( 

  'http://intranet.company.com/expenses/sales/file.csv',
  'http://intranet.company.com/expenses/exec/file.csv',
  'http://intranet.company.com/expenses/finance/file.csv',
  'http://intranet.company.com/expenses/ops/file.csv',
  'http://intranet.company.com/expenses/marketing/file.csv',
  'http://intranet.company.com/expenses/eng/file.csv' 

   )
  FORMAT 'CSV' ( HEADER );

Имена столбцов указаны в заголовках CSV-файлов, расположенных на веб-ресурсах компании.

Освойте администрирование и эксплуатацию Greenplum для аналитики больших данных на специализированных курсах в нашем лицензированном учебном центре обучения и повышения квалификации для разработчиков, менеджеров, архитекторов, инженеров, администраторов, Data Scientist’ов и аналитиков Big Data в Москве:

• Greenplum для инженеров данных
• Администрирование Greenplum / Arenadata DB

Источники

1. https://docs.vmware.com/en/VMware-Greenplum/7/greenplum-database/admin_guide-external-g-creating-and-using-web-external-tables.html
2. https://docs.vmware.com/en/VMware-Greenplum/7/greenplum-database/admin_guide-external-g-external-tables.html