Содержание: Применение в BigQuery и Google Data Studio Тренажер Синтаксис Примеры Использование в BigQuery Примеры применения в Google BigQuery Шпаргалка Регулярные выражения (regexp, regex) — это инструмент для поиска строк, проверки их на соответствие какому-либо шаблону в заданном тексте. Использование регулярных выражений немного различается в разных языках программирования. Довольно часто regexp используются для извлечения определенных символов/строк из набора беспорядочных данных для последующего применения. Например следующий regexp предназначен для поиска номера телефона в тексте.
\(?([0-9]{3})\)?([ .-]?)([0-9]{3})\2([0-9]{4})
Так как телефонный номер каждый человек записывает по разному мы вынуждены искать номера в которых есть скобки, пробелы между цифрами,точки, тире или номера без каких-либо дополнительных символов. То есть это выражение нашло номер телефона записанный шестью разными способами.
Для проверки работы выражений или тренировок будем использовать сайт - regex101
В первой строке пишем наше выражение, а во второй текст или набор символов в котором мы будем что-то искать. Прежде чем начать тренировку, покажу настройки поиска.
При клике откроется список настроек, которые можно включать или выключать в зависимости от типа тестируемого выражения.
Опишу основные, те которые будем использовать почти всегда. global - нужно искать все вхождения, если его убрать то в тексте будет подсвечиваться только первый найденный вариант.
выключаем опцию
multi line - позволяет использовать ^ и $ - символы начала и конца строки. insensitive - отвечает за регистр буквы. При включении регулярное выражение перестает быть чувствительным к регистру и будет искать как большие буквы так и маленькие. опция включена
опция выключена
unicode - позволяет искать не только по латинице, но и по зашифрованному с помощью юникода тексту, кириллица является юникодом. Для наших тренировок будет достаточно трех флагов: global multi line unicode
Символы бывают двух видов: литералы (обычные) и метасимволы (специальные) . Большинство символов в регулярном выражении представляют сами себя за исключением специальных символов: . - соответствует любому одному символу
Как видим из набора слов нашлись первые три буквы, но если добавить " . ", то добавиться любой один символ стоящий после "при"
[ ] - соответствует одному из символов содержащихся в скобках, просто перечисляется набор символов которые подходит под условие поиска
( ) – группировка символов (где начинается и заканчивается группа) [а-я] - любая буква от а до я (в нижнем регистре) [А-Я] - любая буква от А до Я ( в верхнем регистре) [0-9] или \d - любой цифровой символ \D - любой нецифровой символ ( тоже самое что и [^0-9]) [a-z] - любая латинская буква (в нижнем регистре) [A-Z] - любая латинская буква (в верхнем регистре) [а-яА-Я] - любая из букв в любом регистре [a-zA-Z] - любая из букв латинского алфавита в любом регистре [а-яА-Я0-9 ] или \w - любая буква или цифра [a-zA-Z0-9] или \w - любая буква латинского алфавита или цифра ⚠ Буква «ё» не включается в общий диапазон букв, и её нужно указывать отдельно. \W - любой символ, кроме буквенного или цифрового символа или знака подчеркивания \s - пробел \S - любой символ без пробела
[^ ] - соответствует символам из не содержащихся в квадратных скобках [^0-9] - не соответствует цифре [^а-я] - не соответствует букве от а до я в нижнем регистре
^ - начало текста или строки
$ - конец текста или строки
Они определяют сколько раз предшествующее выражение может встречаться. ? или {0,1} - ноль или одно вхождение * или {0,} - ноль или более вхождений + или {1,} - одно или более вхождений {n} - ровно n раз {n,m} - от n до m включительно {m,} - не меньше чем m {,n} - не больше чем n Разберем на
примере номера телефона: Допустим у нас есть номера такой структуры "88005456644", что их найти нужно написать - "\d+"
Все просто, но такое бывает редко, например, номер формата "8-800-432-3243" уже не найдется. если мы напишем "\d+-\d+-\d+-\d+", то номер с тире мы найдем, но перестанем находить номер без тире
Тут нам поможет " ? ", то есть ставим знак вопроса после каждого тире, оно может быть, а может и не быть
Дальше номера телефонов с скобками, пробелами и т.д. Для этого используем символьные классы. Используем квадратные скобки для перечисления возможных комбинаций
В первых квадратных скобках пишем: "-" (тире) может быть, а может и нет, " " (пробел) может быть, а может и не быть, "(" (открывающая скобка) может быть, а может не быть Во вторых квадратных скобках: ")" (закрывающая скобка) может быть, а может и нет " " (пробел) так же "-" (тире) так же И в третьих скобках: пробел или тире Еще рассмотрим пример номера "+7 800 432 4324" или похожие. Проблема в том что символ "+" один из квантификаторов (означает одно или более вхождений). Для того чтобы регулярное выражение приняло "+" просто как "плюс" его нужно экранировать " \ " . То есть " \+? " - означает плюс может быть, а может и не быть перед цифрой
НО , проблема такого запроса заключается в том, что мы не ограничили количество символов в выражении. Если, например, в тексте есть номер счета или другая числовая последовательность, то наше выражение найдет и их тоже.
Чтобы этого избежать давайте рассмотрим наши номера телефонов, из чего они состоят: плюс цифра пробел, дефис, открывающая скобка трехсимвольный код региона в скобках пробел, дефис, закрывающая скобка три цифры может быть тире четыре цифры По порядку строим выражение
Как видим все равно цифры которые не являются номером телефона тоже выделились, так как, обычно номера телефонов в тексте, заканчиваются знаками препинания - точкой, запятой, переносом строки " \n ", пробелом, давайте добавим в условие.
Понятно, что есть еще варианты написания номера телефона, но смысл остается один и тот же. ‼ Постепенно, шаг за шагом пишем наше регулярное выражение. Чтобы прочитать чужое выражение или свое если забыли, можно просто разбить его на части.
Перечисление Символ " | " в регулярном выражении соответствует логическому ИЛИ (OR). Т. е. могут совпадать либо предыдущее, либо последующее выражения. Например, cpc|cpm|direct ➜ найдет или cpc или cpm или direct. Если внутри слова, то используем еще и круглые скобки () go(o|a)gle ➜ найдет google или goagle, но не найдет goegle для поиска одного символа — используем [] для нескольких символов или целого слова — используем |
Мета-символы Мета-символы - это те, которые имеют особое значение для движка регулярных выражений. Чтобы написать их как символ используем: \ (обратный слеш) - экранирование служебных символов.
Например, a\.? ➜ a. или a a\\\\b ➜ a\\b a\[F\] ➜ a[F] Можно экранировать целую последовательность символов, заключив её между \Q и \E (но не во всех системах) Например, \Q{google / cpc}\E ➜ найдет {google / cpc}
[ ] \ / ^ $ . | ? * + ( ) { }
Пример с почтой Как найти номер телефона в тексте мы уже рассмотрели. Давайте еще найдем email в тексте. Надо понять, что вообще из себя представляет адрес электронной почты. Что там может быть: буквы и/или цифры точка/тире/нижнее подчеркивание @ буквы/цифры точка буквы test@mail.ru test123@mail.ru test-123@mail.info test_fd2@mail.su 3213@432.рф test.test2@gmail.com test23.try.re2@12gmail.com test23.try.re2@12-gmail.com Пишем выражение постепенно: может быть как буквы так и цифры, а также нижнее подчеркивание или тире или точка ➜ [\w\.-]+ дальше идет собака ➜ @ доменной имя может содержать буквы и цифры и иногда тире ➜ [\w-]+ обязательно идет точка ➜ \. (точку экранируем) после точки будут только буквы латинского или кириллического алфавита не менее двух и не более десяти ➜ [a-zа-я]{2,10} Целиком выражение выглядит так:
[\w\.-]+@[\w-]+\.[a-zа-я]{2,10}
Мы нашли все возможные варианты написания почты и отсеяли явно ошибочные или просто обозначающие совсем иное. Конечно, могут быть еще какие-то символы, но в основном в ходу такие варианты.
Пример с ссылками Проблема с url состоит в том что мы не можем это сделать все одним выражением, приходится разбивать на этапы. Например, если мы хотим извлечь ссылку из текста, а так как она может иметь множество форматов сделать это за раз практически не возможно. Допустим в тексте есть такие варианты написания url
При написании regexp мы будем находить то одно, то другое, то все вместе.
http[s]?:\/\/[^\/]+\/
Вроде получилось найти протокол и домен, но не везде и это вообще только часть ссылки. Можно написать выражения типа:
Но и они не смогут поймать все что нам нужно. Поэтому для извлечения ссылок нужно работать поэтапно. Использовать для этого можно много вариантов, но я покажу самый простой и доступный всем. копируем наш текст вставляем его в google docs Правка ➜ Найти и заменить или просто нажать Ctrl+H в первое поле "Найти" вставляем наше регулярное выражение поле "заменить на" оставляем пустым нажимаем на галочку - "Учитывать регулярные выражения." . В тексте уже будет подсвечено что вы хотели получить. кнопка "Заменить все"
^(https?:\/\/)?([\w\.]+)\.([a-z]{2,6}\.?)(\/[\w\.]*)*\/?$/
^((http|https|ftp):\/\/)?(([A-Z0-9][A-Z0-9_-]*)(\.[A-Z0-9][A-Z0-9_-]*)+)
(?http://[-A-Za-z0-9+&@#/%?=~_()|!:,.;]*[-A-Za-z0-9+&@#/%=~_()|]
Готово
1. уберем из ссылок https и http с точками и слешами, они не несут никакой важной для нас информации
http[s]?:\/\/
2. избавимся от "www." это тоже не несет никакой важной информации.
w{3}\.
3. теперь избавимся от "index.php", ".jpg". Может конечно понадобится формат файла при скачивании, но обычно используют название файла для понимания какой именно был скачан файл.
index\.php|\.php|\.jpg
4. Теперь можем достать нужную нам ссылку.
[a-z0-9]+\.\w+\.?\w+\/(\w+)?[\?\/]?(\w+=?\w)?\/?(\w+)?\/?(\w)?
Как это выглядит в документе, на примере
BigQuery — одно из самых популярных облачных хранилищ данных, используемое миллионами людей по всему миру для хранения и анализа данных. В BigQuery очень часто требуется извлечь из строки определенные ее части, проверить формат строки или заменить/удалить определенные символы. В BigQuery есть 6 функций для работы с регулярными выражениями: REGEXP_CONTAINS - дословно "текст содержит". Возвращает строку (TRUE) если она частично или полностью совпадает с регулярным выражением. Обычно используется как условие в WHEN Синтаксис: REGEXP_CONTAINS (название столбца, 'условие поиска') На
пример, у нас есть столбец с названиями:
select distinct title from `business-metric.project.example_final_bitrix24`
И мы хотим найти только те поля в которых есть слово "заявка"
select distinct title from `business-metric.project.example_final_bitrix24`
where REGEXP_CONTAINS (title,'Заявка')
Конечно, можно использовать LIKE, но это что касается данного примера, потенциал regexp практически не ограничен.
Результатом будет те же строки.
2. REGEXP_REPLACE - ищет конкретную структуру из выбранного столбца и заменяет его тем, что мы указали. Синтаксис: REGEXP_REPLACE (название столбца, 'искомое рег. выражение', 'на что меняем найденное значение ' Хотим заменить "Заявка от" на "Клиент"
select distinct title from `business-metric.project.example_final_bitrix24`
where title like ('Заявка%')select distinct title,
REGEXP_REPLACE (title,'Заявка от','Клиент') as new_title
from `business-metric.project.example_final_bitrix24`
where title like ('Заявка%')
3. REGEXP_EXTRACT - извлекает значение из указанной строки. Синтаксис: REGEXP_EXTRACT (название столбца, 'регулярное выражение') Для примера извлечем из ссылки значение "utm_source"
То есть из полноценной ссылки мы изъяли только Источник
select distinct source,
REGEXP_EXTRACT(source, 'utm_source=([\\w\\d-_%.]+)') as new_source
from `business-metric.project.example_final_bitrix24`
4. REGEXP_EXTRACT_ALL - извлекает группу значений из указанной строки с помощью регулярного выражения. Под группами подразумевается совпадающие шаблоны которое присутствуют в тексте. Чаще всего используется для извлечения каждого слова из строки или для извлечения всех чисел из строки.
Синтаксис: REGEXP_EXTRACT_ALL (название столбца, 'регулярное выражение') Основное отличие извлечения групп от простого REGEXP_EXTRACT заключается в том, что просто EXTRACT извлечет первое слово из строки, в то время как ALL извлечет каждое слово.
select title,
REGEXP_EXTRACT (title, r'[А-Яа-я]+') as new_title
from `business-metric.project.example_final_bitrix24`
where title like 'Заявка%'
А вот как будет выглядеть извлечение групп
Примеры использования регулярных выражений в Google BigQuery
Это может быть полезно при работе с ключевыми словами.
5. REGEXP_INSTR - извлекает номер позиции, с которой начинается первый подходящий символ из регулярного выражения. Используется редко. Подробнее можно почитать в справке от google
6. REGEXP_SUBSTR - синоним функции REGEXP_EXTRACT. Также извлекает нужное значение из строки, но может быть уточнено дополнительными параметрами типа позиции символа в самом запросе, подробнее в статье от google . Используется редко.
Возьмем часто встречающуюся задачу, привести ссылки к единому формату, разбить на составляющие и т.д. Допустим есть список ссылок:
Много лишнего, отвлекает. Нужно избавиться от протокола https и от www. Для этого будем использовать регулярные выражения и функцию CASE WHEN
В результате запроса мы получим разбивку на два столбца и читаемый вид, также можно делать и с кампанией, ключевым словом и т.д.
SELECT
source,
--- Определяем источник
CASE
--- Если у нас есть utm метка, то извлекаем из нее название источника
WHEN REGEXP_CONTAINS(source, 'utm_source.*') THEN REGEXP_EXTRACT(source, 'utm_source=([\\w\\d-_%.]+)(?:&|$)')
--- Если у нас есть просто переход из поисковых систем или социальных сетей, то достаем просто название
WHEN REGEXP_CONTAINS(source, '^https?://(?:www\\.)?(yandex|google|facebook|vk|instagram).*') THEN REGEXP_EXTRACT(source, '^https?://(?:www\\.)?(yandex|google|facebook|vk|instagram).*')
--- Если данные отсутствуют, возвращаем direct
WHEN source = '' THEN '(direct)'
ELSE
--- Иначе берем просто домен без лишнего
REGEXP_EXTRACT(source, '^https?://((?:www\\.)?.*)')
END
AS new_source,
--- Определяем канал
CASE
WHEN REGEXP_CONTAINS(source, 'utm_medium.*') THEN REGEXP_EXTRACT(source, 'utm_medium=([\\w\\d-_%.]+)(?:&|$)')
WHEN REGEXP_CONTAINS(source, '^https?://(?:www\\.)?(yandex|google).*') THEN 'organic'
WHEN REGEXP_CONTAINS(source, '^https?://(?:www\\.)?(facebook|vk|instagram).*') THEN 'social'
WHEN source = '' THEN '(none)'
ELSE
REGEXP_EXTRACT(source, '^https?://((?:www\\.)?.*)')
END
AS medium,
FROM
Полный запрос при помощи которого вы можете попрактиковаться именно в BigQuery на своих примерах. Просто замените примеры внизу запроса своими.
Символьные классы + Скобочные выражения Якоря Квантификаторы
SELECT
source,
--- Определяем источник
CASE
--- Если у нас есть utm метка, то извлекаем из нее название источника
WHEN REGEXP_CONTAINS(source, 'utm_source.*') THEN REGEXP_EXTRACT(source, 'utm_source=([\\w\\d-_%.]+)(?:&|$)')
--- Если у нас есть просто переход из поисковых систем или социальных сетей, то достаем просто название
WHEN REGEXP_CONTAINS(source, '^https?://(?:www\\.)?(yandex|google|facebook|vk|instagram).*') THEN REGEXP_EXTRACT(source, '^https?://(?:www\\.)?(yandex|google|facebook|vk|instagram).*')
--- Если данные отсутствуют, возвращаем direct
WHEN source = '' THEN '(direct)'
ELSE
--- Иначе берем просто домен без лишнего
REGEXP_EXTRACT(source, '^https?://((?:www\\.)?.*)')
END
AS new_source,
--- Определяем канал
CASE
WHEN REGEXP_CONTAINS(source, 'utm_medium.*') THEN REGEXP_EXTRACT(source, 'utm_medium=([\\w\\d-_%.]+)(?:&|$)')
WHEN REGEXP_CONTAINS(source, '^https?://(?:www\\.)?(yandex|google).*') THEN 'organic'
WHEN REGEXP_CONTAINS(source, '^https?://(?:www\\.)?(facebook|vk|instagram).*') THEN 'social'
WHEN source = '' THEN '(none)'
ELSE
REGEXP_EXTRACT(source, '^https?://((?:www\\.)?.*)')
END
AS medium,
FROM (
SELECT
'https://facebook.com' AS source
UNION ALL (
SELECT
'https://test.me/credo-vs-tredo?utm_source=ya_poisk_piter_bfl&utm_medium=lidgen&utm_campaign=text2&utm_content=image2' AS source )
UNION ALL (
SELECT
'https://test.me/credo-vs-tredo?utm_source=google&utm_medium=cpc&utm_campaign=text3&utm_content=image0' AS source)
UNION ALL (
SELECT
'https://test.me/credo-vs-tredo?utm_source=yandex&utm_medium=cpm&utm_campaign=text2&utm_content=image1'AS source)
UNION ALL (
SELECT
'https://google.com' AS source)
UNION ALL (
SELECT
'https://direct.yandex.ru' AS source)
UNION ALL (
SELECT
'https://www.yandex.ru' AS source )
UNION ALL (
SELECT
'https://link.2gis.ru'AS source)
UNION ALL (
SELECT
'https://vk.com' AS source)
UNION ALL (
SELECT
'' AS source)
UNION ALL (
SELECT
'https://instagram.com' AS source )) AS table_1
