Преобразование данных в Google bigquery – извлечение текста, разбиение на несколько столбцов и сворачивание данных

У меня есть некоторые данные веб-журнала в большом запросе, которые мне нужно преобразовать, чтобы упростить использование и запрос. Данные выглядят так:

Big_Query_Table

Я хочу извлечь и преобразовать данные в скрученные скобки после результатов {… ..} (цветной синий). Данные имеют вид '(\ d + ((PQ) | (KL)) + \ d +)' и в массиве результатов может быть 1-20 + записей. Меня интересуют только первые 16 записей.

Я смог извлечь данные в скошенных скобках в новый столбец, используя Substr и regext_extract. Но я не могу SPLIT его в столбцы (иногда есть только один результат, и поэтому разделитель «,» отсутствует. Я новичок в регулярном выражении, возможно, я могу использовать что-то вроде «(\ d + ((PQ) | (KL)) + \ d +) {1} ' и т. Д., Чтобы разбить данные на несколько столбцов и затем развернуть его.

Идеальный выход в моем случае состоял бы в том, чтобы превратить его в нечто вроде:

Ouput Table

В приведенном выше решении каждая строка в исходной таблице повторяется от 1 до 16 раз в зависимости от количества элементов в массиве Results.

Я не совсем уверен, возможно ли это сделать в большом запросе. Буду признателен, если кто-нибудь может мне немного помочь.

Если это невозможно, я могу иметь 16 строк для каждого события с NULL-значениями в Event_details для случаев, когда в массиве результатов меньше 16 записей.

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

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

Проверьте это: разделите строку на несколько столбцов с помощью bigquery . В их случае он ограничен пробелами. замените \ s на ','

что-то вроде:

SELECT Regexp_extract(StringToParse,r'^*{(?:[^,]*,){0}(\d+(?:(?:PQ)|(?:KL))+\d+)\s?') as Word0, Regexp_extract(StringToParse,r'^*{(?:[^,]*,){1}(\d+(?:(?:PQ)|(?:KL))+\d+)\s?') as Word1, Regexp_extract(StringToParse,r'^*{(?:[^,]*,){2}(\d+(?:(?:PQ)|(?:KL))+\d+)\s?') as Word2, Regexp_extract(StringToParse,r'^*{(?:[^,]*,){3}(\d+(?:(?:PQ)|(?:KL))+\d+)\s?') as Word3, FROM (SELECT 'bla{1234PQ5,6789KL0,1234PQ5,6789KL0,123' as StringToParse) 

Используйте SPLIT ()

BQ.

 SELECT Event_ID, Event_UserID, Event_SessionID, Keyword, SPLIT(REGEXP_EXTRACT(Event_details,"Results\{(.*)\}"),",") as Event_details_item FROM mydata.mytable