Bcp

Однажды мне потребовалось забирать регулярно относительно большие объемы данных в MS SQL из PostgreSQL. Неожиданно выяснилось, что самый очевидный способ, через Linked Server на родные ODBC к PostgreSQL, очень медленный.

История вопроса

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

SET STATISTICS TIME ONDECLARE  @sql_str nvarchar(max)DROP TABLE IF EXISTS #tCREATE TABLE #t (  N int,  T datetime)SELECT @sql_str='  SELECT N, T  FROM generate_series(1,1000,1) N  CROSS JOIN generate_series($$2020-01-01$$::timestamp,    $$2020-12-31$$::timestamp, $$1 day$$::interval) T'INSERT #t (N, T)EXEC (@sql_str) AT LINKED_SERVER_TO_POSTGRES

Такой простейший пример выборки всего 366 тысяч записей оказался жутко медленным:

SQL Server Execution Times:   CPU time = 8187 ms,  elapsed time = 14793 ms.

Решение

В первую очередь, захотелось исключить самый подозрительный элемент - ODBC. К тому времени MS уже предоставлял утилиту bcp для Linux. Поэтому bcp был установлен на сервер, где работал PostgreSQL и проведен следующий тест:

SET STATISTICS TIME ONDECLARE  @sql_str        nvarchar(max),  @proxy_account  sysname='proxy_account',  @proxy_password sysname='111111'DROP TABLE IF EXISTS ##tCREATE TABLE ##t (  N int,  T datetime)SELECT @sql_str='  COPY (    SELECT N, T    FROM generate_series(1,1000,1) N    CROSS JOIN generate_series($$2020-01-01$$::timestamp,      $$2020-12-31$$::timestamp, $$1 day$$::interval) T )  TO PROGRAM $pgm$ tmp_file=$'+'(mktemp /tmp/pgsql_bcp_to_mssql.XXXXXXXXX); '    +'cat > $tmp_file; /opt/mssql-tools/bin/bcp ''##t'' '    +'in $tmp_file -S '+REPLACE(@@SERVERNAME,'','\')    +' -U '+@proxy_account+' -P '''    +@proxy_password+''' -c -b 10000000 -a 65535; '    +'rm $tmp_file $pgm$ NULL $nil$$nil$;'EXEC (@sql_str) AT LINKED_SERVER_TO_POSTGRES

Результат сразу порадовал, причем сильно:

SQL Server Execution Times:   CPU time = 0 ms,  elapsed time = 881 ms.

Реализация

Не сложно заметить, что такой подход требует явного указания логина и пароля. Причем, bcp для Linux до сих пор не умеет авторизоваться через Kerberos. Поэтому использовать его можно только указывая кредентиалы в командной строке.

Вторая проблема в том, что в обычную временную таблицу bcp записать не может. Он ее просто не увидит. Значит нужно использовать постоянную таблицу или глобальную временную.

Давать права пользователю, кредентиалы которого открытым текстом видны в SQL запросе, на таблицы своей БД совершенно не хочется. Тем более на запись. Поэтому остается только вариант с глобальной временной таблицей.

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

Итоговое решение следующее:

DECLARE  @sql_str        nvarchar(max),  @proxy_account  sysname='proxy_account',  @proxy_password sysname='111111'SELECT @sql_str='  DROP TABLE IF EXISTS ##proxy_table_'+CONVERT(nvarchar(max),@@SPID)+'  CREATE TABLE ##proxy_table_'+CONVERT(nvarchar(max),@@SPID)+' (    N int,    T datetime  )'EXEC (@sql_str)SELECT @sql_str='  COPY (    SELECT N, T    FROM generate_series(1,1000,1) N    CROSS JOIN generate_series($$2020-01-01$$::timestamp,      $$2020-12-31$$::timestamp, $$1 day$$::interval) T )  TO PROGRAM $pgm$ tmp_file=$'+'(mktemp /tmp/pgsql_bcp_to_mssql.XXXXXXXXX); '    +'cat > $tmp_file; /opt/mssql-tools/bin/bcp ''##proxy_table_'''    +CONVERT(nvarchar(max),@@SPID)+' '    +'in $tmp_file -S '+REPLACE(@@SERVERNAME,'\','\\')    +' -U '+@proxy_account+' -P '''    +@proxy_password+''' -c -b 10000000 -a 65535; '    +'rm $tmp_file $pgm$ NULL $nil$$nil$;'EXEC (@sql_str) AT LINKED_SERVER_TO_POSTGRES

Пояснения

В PostgreSQL команда COPY может писать в файл или на стандартный ввод вызываемой ей программы. В данном случае вместо программы использован скрипт на sh. Вывод COPY, поступающий на стандартный ввод, записывается во временный файл с уникальным именем, форимруемым mktemp. К сожалению, bcp не умеет читать данные со стандартного ввода, поэтому приходится ему создавать файл.

Для совместимости формата, формируемого командой COPY и формата, ожидаемого bcp, обязательно следует указывать в COPY параметр NULL $nil$$nil$

Остальные параметры bcp:

• -c - символьный формат, так как бинарный формат PostgreSQL не совместим с бинарным форматом MS SQL и мы вынуждены использовать только символьный;

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

• -a - размер пакета. В нашем случае лучше указывать сразу максимальный. Если сервер не поддерживает указанную длину пакета, то просто будет использована максимальная длина пакета, поддерживаемая сервером.

Если кто-то знает более быстрый способ получения данных на MS SQL из PostgreSQL - буду очень рад увидеть описание этого способа в комментариях.