Как легко создавать подключение между таблицами, размещенными в различных СУБД и ускорять работу с ними.
Наверняка каждый из нас сталкивался с тем, что запросы в базы данных исполняются чрезвычайно долгое время, и никто не хочет ждать результата по 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, используя инструмент материализации таблиц.