Подключение внешних таблиц к Greenplum

Как легко создавать подключение между таблицами, размещенными в различных СУБД и ускорять работу с ними.

Наверняка каждый из нас сталкивался с тем, что запросы в базы данных исполняются чрезвычайно долгое время, и никто не хочет ждать результата по 15-20 мин. Да, безусловно, есть много определяющих факторов, влияющих на время запроса: объем запрашиваемых данных, структура запроса, загруженность сервера и т.д. К примеру, в Apache Hive, среднее время ожидания результата запроса к БД измеряется десятками секунд, а иногда и минутами.

С самого начала работы с СУБД Greenplum была отмечена высокая скорость работы с данными, соответственно, возник вопрос подключения внешних таблиц Hive для более продуктивной работы.

Далее я расскажу, как удалось решить задачу по работе с таблицами из Apache Hive в СУБД Greenplum. Данный способ успешно испытан и применяется в DBeaver.

В первую очередь, чтобы показать наглядно разницу в скорости выполнения запросов, приведем сравнение скорости работы двух систем на одинаковых запросах, созданных одновременно. Запросы сформированы по таблице из 176 млн. строк и добавлено условие выборки по конкретному значению одного из полей:

Как можно заметить, разница более чем существенна.

Теперь опишем механизм подключения таблиц из Apache Hive.

Прочитать данные подписки в GP можно через внешнюю таблицу. Для создания внешней таблицы необходимо:

·       Определить имя схемы и таблицы с данными подписки в Hive (для проксированных и материализованных подписок). Схему можно найти в Hue по системному имени подписки, указанному при оформлении.

·       Получить список полей (и их типов) таблицы, материализованной на кластере DDA.

С учетом необходимости приведения типов данных Apache Hive и Greenplum согласно схеме:

1)  напишем процедуру, подключающую исходную таблицу к СУБД и приводящую типы данных в соответствие с СУБД:

CREATE OR REPLACE FUNCTION s_grnplm_ld_audit_da_sandbox_oaop.create_external_table(     in schema_name text,     in table_name text,     in text_in text,     out text_out text ) AS $$ begin     text_in := replace(text_in, '`', '');     text_in := replace(text_in , concat(schema_name, '.'), '');     schema_name := replace(schema_name, 'prx_', '');     schema_name := replace(schema_name, 'cap_', '');     text_in := replace(text_in, ' tinyint', ' int2');     text_in := replace(text_in, '''', '');     text_in := replace(text_in, ' timestamp', ' timestamp');     text_in := replace(text_in, ' string', ' text');     text_in := replace(text_in, ' smallint', ' int2');     text_in := replace(text_in, ' float', ' float4');     text_in := replace(text_in, ' double', ' float8');     text_in := replace(text_in, ' boolean', ' bool');     text_in := replace(text_in, ' binary', ' bytea');     text_in := replace(text_in, ' bigint', ' int8');     text_in := replace(text_in, ' decimal', ' numeric');     text_in := replace(text_in, 'CREATE EXTERNAL TABLE ', 'CREATE EXTERNAL TABLE s_grnplm_ld_audit_da_sandbox_oaop.');     text_in :=  concat(text_in, 'location');     text_in := replace(text_in, '<схема>', schema_name);     text_in := replace(text_in, '<таблица>', table_name);     text_out:= text_in;     execute text_out;     RETURN; END $$ LANGUAGE plpgsql;

2) Создаем подключение к внешней таблице:

select s_grnplm_ld_audit_da_sandbox_oaop.create_external_table(     '<схема>',     '<таблица>',     '<show create table .....>');

Для успешного подключения, необходимо получить список столбцов и типы данных из исходной таблицы:

show create table ...

И далее из результата запроса нас интересуют:

На этом этапе уже можно работать с данными в СУБД, они не занимают дисковое пространство сервера, но все еще обрабатываются Apache Hive.

Скорость исполнения запроса из нематериализованной базы можно сравнить с ранее полученными значениями:

Для создания независимой БД необходимо материализовать данные уже в среде СУБД:
3)Создаем таблицу для материализации данных:

create table s_grnplm_ld_audit_da_sandbox_oaop.<таблица>_m (like s_grnplm_ld_audit_da_sandbox_oaop.<таблица>) distributed randomly 

4) Копируем данные в таблицу:

insert into s_grnplm_ld_audit_da_sandbox_oaop.<таблица>_m select * from s_grnplm_ld_audit_da_sandbox_oaop.<таблица> where <при необходимости>;

Таким образом, я показал рабочее решение по уменьшению времени обработки запросов к БД посредством использования СУБД Greenplum, используя инструмент материализации таблиц.