Использование функций регулярных выражений в PostgreSQL / Greenplum

Начну с баянистой шутки:

«Допустим, у вас есть проблема, и вы хотите решить её с помощью регулярных выражений. Теперь у вас две проблемы».

О том, как именно использовать регулярки, написано уже много статей и туториалов. Другой вопрос, что сами по себе регулярные выражения являются параметром, подающимся на вход какой-нибудь функции. Именно функция осуществляет поиск по указанному регулярному выражению. О функциях, использующих регулярки мы сегодня и поговорим. Возможно, и по самим регуляркам я бахну свой гайд, но, как было сказано в том самом фильме: «Обязательно бахнем и не раз, весь мир в труху! Но потом...»

Проблемный кейс

Итак, я столкнулся с проблемой фильтрации в Greenplum. Не то чтобы это большая проблема, просто привычными мне функциями из других СУБД она не решалась. Если конкретнее, то столбец ИНН в одной из наших витрин данных должен содержать либо цифровые значения (10/12 цифр) либо плейсхолдер XX и цифры. Однако со временем там стали появляться какие-то непонятные текстовые значения ввиду того, что эти данные к нам поступают из CRM, а в саму CRM они вносятся без проверки на формат. И когда ИНН нет, то кредитный менеджер начинает писать туда, что душе угодно. Хотите верьте, хотите – нет, но в таких атрибутах мне попадались даже атрибуты в виде эмодзи-какашек. А такая серьёзная контора с виду… И долгое время бизнес-подразделение настаивало на том, что так и должно быть, но после перехода DWH на новую архитектуру эти требования стали неактуальны. А дальше было, как в том старом мультфильме.

В телеграмме одна только строчка:

«Посторонних убрать! Точка»

Суть проблемы

Итак, в Greenplum возникла необходимость фильтрации по шаблону, привычные мне функции из других СУБД в Greenplum не работали, и, погрузившись в документацию, я понял, что букавмнога, а реально полезная информация вместе с примерами может уместиться на тетрадном листе. У разных СУБД разные функции управления данными. Более того, они могут различаться даже у одной СУБД разных версий, поэтому я сразу оговорюсь, что указанные функции актуальны для PostgreSQL 9.4.26 (и выше), т. к. именно на этой версии построено ядро СУБД Greenplum (6.22), которую мы используем.  

Я напомню, регулярные выражения – это шаблоны, набор правил, по которым мы ищем подстроку в строке. Всё. Ничего сложного.

Основные задачи с, с которыми приходилось сталкиваться, работая с регулярными выражениями в базах данных:

• Понять, соответствует ли строка шаблону

• Найти подстроку, удовлетворяющую некоторому шаблону, в строке

• Разбить строку на массив по некоторому разделителю-шаблону

• Разбить строку на массив по некоторому разделителю-шаблону и вытянуть этот массив в столбец

• Заменить подстроку в строке на новую подстроку

Я понимаю, многие из вас здесь ищут ответ на конкретный вопрос, так что не стану вас томить. Если коротко, то в Greenplum/PostgreSQL мы будем пользоваться следующими выражениями.

like

С выражением like всё просто: это проверка некоторой строки на соответствие упрощённому шаблону по типу ‘Иван%’, где строка начинается со слова «Иван» и продолжается любым количеством символов. Однако бывают случаи, когда таких простых шаблонов недостаточно: нас может интересовать конкретный набор символов, выстроенный по особым правилам. Да, простейшие шаблоны мы ищем с помощью выражения Like, однако оно не работает с регулярными выражениями. В MySql для поиска по регулярному выражению есть, например, выражение rlike, но для Greenplum неактуально.

similar to

Если мы хотим использовать регулярное выражение для фильтрации, то мы используем выражение similar to.

select id, inn, date_from
from schema_dds.clients
where inn similar to '(XX)?[0-9]{10,12}'

id	inn	date_from
1337	1234567890	2022-05-01
1338	XX0987654321	2022-05-01
1339	111222333444	2022-05-01

substring

Функцию substring можно использовать не только в виде substring (атрибут, индекс начального символа, сколько символов взять), но и в виде substring(атрибут, шаблон). Тогда в результате исполнения этой функции мы получим подстроку, соответствующую данному шаблону.

select 
  client,
  info,
  substring(info, '[0-9\-\+ ]{10,}') as number
from schema_dds.clients

client	info	number
541	Любит котиков.
603	Высокий. 83912672222. Дом	83912672222
678	Номер [phone]	[phone]

Если функция с регуляркой не находит подстроку и возвращает NULL, то одно из двух: либо подстрока по нужному шаблону отсутствует, либо (что чаще всего) вы неправильно написали регулярку.

regexp_replace

Regexp_replace используется для того, чтобы взять некоторую строку, найти в ней подстроку по указанному шаблону и заменить на новую подстроку. Всё. А хотя нет, не всё. Вдруг у нас подстрока входит в строку несколько раз?

Важным нюансом, решающим подобную проблему, являются буквенные флаги, которые могут передаваться как опциональный аргумент. Зачем? Да просто они меняют поведение функции. Двумя наиболее часто встречающимися флагами являются ‘g’ и ‘i’. Флаг ‘i’ означает «игнорировать регистр», а если не поставить флаг ‘g’, то регулярка среагирует только на первое появление шаблона в строке. Если мы хотим работать со всеми вхождениями подстроки в строку, то используем флаг ‘g’. Как пример, может возникнуть задача деперсонификации, когда мы должны скрыть какую-то важную информацию:

select regexp_replace('Комната 402. Самая жесть творилась в 402 комнате', '[0-9]{3}', 'XXX') as foobar
-- Результат: Комната XXX. Самая жесть творилась в 402 комнате 

select regexp_replace('Комната 402. Самая жесть творилась в 402 комнате', '[0-9]{3}', 'XXX', 'g') as foobar
-- Результат: Комната XXX. Самая жесть творилась в XXX комнате

regexp_split­_to_array

Часто бывает так, что данные в витрине не удовлетворяют даже первой нормальной форме.

Сейчас все аналитики, выучившие SQL по курсам в интернете, такие: "Что такое первая нормальная форма? Хмммм".

То есть в одном атрибуте в том числе может встречаться строка, которая в сущности представляет собой набор каких-то вещей. Если говорить по-умному, то нарушено требование атомарности. Например, часто ФИО пишут в одном атрибуте, хотя разумнее было бы их разделить.

Regexp_split­_to_array используется для разбиения строки на массив строк по шаблону (как правило, это последовательность пробельных символов). На выходе у нас получается массив, к элементам которого можно обращаться по индексу от 1 до n (в PostgreSQL нумерация элементов массива начинается с 1, а не 0).

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

select fio, 
  regexp_split_to_array(fio, ‘\s+’) as fio_arr,
  (regexp_split_to_array(fio, ‘\s+’))[1] as f,
  (regexp_split_to_array(fio, ‘\s+’))[2] as i,
  (regexp_split_to_array(fio, ‘\s+’))[3] as o
from 
  (select ‘Сидоров Иван  Петрович’ as fio) as foo

Заметьте, в ФИО между именем и отчеством я нарочно указал несколько пробелов. Такое бывает, когда данные вносятся вручную, и кто-то по невнимательности добавил два пробела вместо одного. Поэтому здесь я вставил достаточно простую регулярку \s+, которая обозначает любую последовательность пробельных символов (типа пробелов, табуляции или переноса строки). Таким образом разбиение нашей строки будет по любому числу пробелов. Результат запроса будет выглядеть следующим образом:

fio	fio_arr	f	i	o
Сидоров Иван Петрович	{Сидоров, Иван, Петрович}	Сидоров	Иван	Петрович

regexp_split_to_table

Гораздо интереснее работает функция regexp_split_to_table. Она работает аналогично функции regexp_split­_to_array с той лишь разницей, что она разбивает строку на массив и вытягивает этот массив в столбец. Так, если у нас атрибут по регулярке разделяется на три части, то число строк в результирующей таблице вырастет в 3 раза. Рассмотрим следующий пример. У нас есть витрина продавцов фруктов, в которой мы снова видим нарушение свойства атомарности.

seller_id	seller	fruits
1	Алмазбек	груши яблоки арбузы
2	Виталий	яблоки бананы гуава

Но мы хотим работать с каждым фруктом по отдельности. Тогда используем следующий запрос:

select 
  seller_id,
  seller,
  regexp_split_to_table(fruits, '\s+') as fruit
from the_market
order by 1,2

В результате имеем таблицу, в которой в этом конкретном случае число строк увеличилось в 3 раза:

seller_id	seller	fruit
1	Алмазбек	груши
1	Алмазбек	яблоки
1	Алмазбек	арбузы
2	Виталий	яблоки
2	Виталий	бананы
2	Виталий	гуава

Подведём итоги

• similar to чаще всего используется в фильтрации для выяснения, соответствует ли строка шаблону

• substring используется чтобы вытащить из строки подстроку, соответствующую шаблону

• regexp_split_to_array мы используем, когда хотим разбить строку на массив по определённому разделителю

• regexp_split­_to_table мы используем, когда нам нужен не просто массив, а мы хотим вытянуть его в столбец

• regexp_replace нужна для того, чтобы в строке заменить подстроку, соответствующую определённому шаблону, на новую подстроку.

И ничего сложного в этом нет. В 99% случаев работа с регулярками опирается на эти функции. И я от всей души вам желаю, чтобы регулярные выражения в Greenplum для вас не превращались в проблему №2. Действуйте!