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

Время прочтения: 4 мин.

Наверняка каждый сталкивался с тем, что запросы в базы данных исполняются чрезвычайно долгое время, и никто не хочет ждать результата по 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 .....>');

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

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

CREATE EXTERNAL TABLE `prx_oaop_szb_uvhd_internal_saperpuvhd_sapurp.jest`(       
  `mandt` string,              
  `objnr` string,
  `stat` string,    
  `inact` string,  
  `chgnr` string,
  `dataaging` string,        
  `ctl_action` string,        
  `ctl_loading` bigint,     
  `ctl_validfrom` timestamp,      
  `ctl_filename` string)

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