C#, Excel/PowerQuery/VBA, SQL

Применение регулярных выражений в ETL процессах

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

Регулярные выражения в ETL процессах позволяют существенно облегчить поиск текстовых выражений и/или замену текстовых выражений по заданным шаблонам. Существенное облегчение заключается не только в использовании одной функции, но и скорости обработки текстовых выражений в отличие от использования нескольких встроенных строковых функций таких как SUBSTRING, REPLACE, TRIM и других.

Применение регулярных выражений весьма различно в зависимости от платформы, используемой в ETL процессах.

Например, использование регулярных выражений в ETL процессе с TERADATA возможно с помощью пяти функций REGEXP_SUBSTR, REGEXP_REPLACE, REGEXP_INSTR, REGEXP_SIMILAR, REGEXP_SPLIT_TO_TABLE, а вот в ETL процессе с PL/SQL (Oracle) возможно с помощью функций REGEXP_SUBSTRING, REGEXP_REPLACE, REGEXP_INSTR, REGEXP_LIKE и других.

Но в ходе переноса, уже реализованного ETL процесса, с PL/SQL (Oracle) на инструментарий SSIS пакетов MSSQL возникли сложности в использовании регулярных выражений. Дело в том, что как таковые регулярные выражения в T-SQL отсутствуют, что усложняет его использование в ETL процессах в отличие от TERADATA и PL/SQL (Oracle). Есть возможность обработать текстовые значения через LIKE, но это не полноценная замена регулярных выражений и весьма дорогостоящая операция в части производительности при использовании нескольких и сложных шаблонов. В инструментарии SSIS пакетов имеется возможность использования регулярных выражений через компонент скриптов. Данный компонент использует набор средств Microsoft Visual Studio для работы с приложениями, чтобы записывать скрипты на языке Microsoft Visual Basic или Microsoft Visual C#. Таким образом использовать регулярные выражения можно довольно в широком спектре ETL процесса с SSIS пакетами. Например, такая задача как поиск номера карты в текстовой строке и её последующее маскирование невозможно реализовать в ETL процессе с T-SQL используя функцию LIKE и другие строковые функции. Но в ETL процессе с SSIS пакетом и скриптом на С# данная задача легко реализуется с использованием регулярных выражений, правда с определенными нюансами, при этом все действия выполняются в пределах Microsoft Visual Studio.

Прежде всего, компонент скриптов должен быть настроен в режиме преобразования данных, а параметр ScriptLanguage в режиме Microsoft Visual C#.

 Также необходимо корректно настроить именование как входных и выходных потоков, так и именование входных и выходных столбцов. При этом, если мы делаем фильтрацию строк, т.е. выходных потоков несколько, то обязательно у каждого выходного потока установить параметр ExclusionGroup=1.

Кроме того, у выходных столбцов надо обратить внимание на тип данных и длину значений. Далее необходимо написать код скрипта на языке С#, нажав на кнопку «Изменить скрипт» (выделена зеленым контуром на рисунке выше).

Сами регулярные выражения в C# реализованы в классе Regex пространства имен System.Text.RegularExpressions. Например, вот такой несложный скрипт позволяет замаскировать номер карты, найденный в текстовой строке, при этом будут найдены и замаскированы все номера карт, встреченные в строке.

String out = “входная текстовая строка”;
Regex regex = new Regex(“шаблон номера карты для поиска”);
//Цикл по каждому найденному номеру карты по шаблону
Foreach(Match match in regex.Matches(out))
{
String card = match.Groups[1].Value.Trim();
out = Regex.Replace(out, card, “маскированный номер карты”);
}

Резюмируя возможности ETL процесса с SSIS пакетом и скриптом на С# нужно отметить что, существует перспектива писать внутренние функции (подпрограммы), что значительно расширяет возможности обработки текста и гибкость работы с данными, но в то же время и увеличивает время работы ETL процесса. Причем, из практического опыта, при возрастании сложности кода время работы ETL процесса увеличивается прямо пропорционально, поэтому желательно попробовать различные варианты обработки текста и найти оптимальное время работы SSIS пакета.

В качестве вывода замечу что, несмотря на такие шероховатости в настройке компонента скриптов и его скорости работы, инструментарий SSIS пакета со скриптом на С# имеет отличную функциональность регулярных выражений и позволяет разрабатывать полноценные ETL процессы с возможностью реализации сложных трансформаций данных. И это не голословное утверждение, а выводы, проверенные на практике.

Советуем почитать