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

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

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

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

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

Инструмент	Скорость выполнения запроса, сек
Apache Hive	317,52
Greenplum	1,73

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

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

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

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

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

Тип поля в Hive	Тип поля в GreenPlum
Tinyint	int2
Timestamp	timestamp
String	text
Smallint	int2
Float	float4
Double	float8
Boolean	bool
Binary	byte
Bigint	int8
Decimal	numeric

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.

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

Инструмент	Скорость выполнения запроса, сек.
Apache Hive	317,52
Greenplum (материализованная таблица)	1,73
Greenplum (нематериализованная таблица)	3,74

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

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, используя инструмент материализации таблиц.

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