Фильтровать столбцы Google BigQuery с помощью фильтра регулярных выражений

В настоящее время я пытаюсь создать быстрый и оптимизированный запрос в Google Big Query.

Предположим, имея таблицу, имеющую 10 тыс. Строк и 4 тыс. Столбцов:

    | TP_001_A | TP_001_B | TP_002_A | TP_002   |    ...   | TP_099_B | 
----+----------+----------+----------+----------+----------+----------+
 1  |   0.33   |   0.33   |   0.33   |   0.33   |   0.33   |   0.37   | 
 2  |   0.33   |   0.33   |   0.31   |   0.33   |   0.33   |   0.33   |
 3  |   0.33   |   0.33   |   0.33   |   0.33   |   0.33   |   0.33   |
 4  |   0.35   |   0.33   |   0.33   |   0.33   |   0.34   |   0.33   |
...
9999|   0.33   |   0.33   |   0.33   |   0.33   |   0.33   |   0.33   |

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

    | TP_001_A | TP_001_B |
----+----------+----------+
 1  |   0.33   |   0.33   |
 2  |   0.33   |   0.33   |
 3  |   0.33   |   0.33   |
 4  |   0.35   |   0.33   |
...
9999|   0.33   |   0.33   | 

К настоящему времени я обошел все вокруг и запросил всю таблицу, а затем отфильтровал ее, используя медленную библиотеку панд из-за времени загрузки и общей медлительности панд в больших таблицах.

После поиска в Интернете я обнаружил, что имена столбцов могут быть получены с помощью запроса, как:

SELECT column_name 
FROM my_view.INFORMATION_SCHEMA.COLUMNS 
WHERE table_name="my_table"

Где я могу использовать функцию REGEXP_EXTRACT_ALL и фильтровать правильные имена столбцов, но это дает мне отдельную таблицу, которую сложно использовать в основном запросе.

Но у этого решения есть одна проблема: он обрабатывает много данных (индикация показывает 10 МБ, но выглядит много), несмотря на то, что это довольно быстро.

Есть ли какой-либо правильный способ сделать такой запрос или другой способ оптимизировать получение только отфильтрованных столбцов?

Всего 1 ответ


Есть ли какой-либо правильный способ сделать такой запрос (Фильтровать столбцы Google BigQuery по фильтру регулярных выражений) ...

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

Между тем, вы можете использовать любую клиентскую библиотеку BigQuery по вашему выбору, чтобы программно построить необходимый оператор выбора и выполнить его - очень простой и выполнимый вариант

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

Если по какой-то причине вы решили использовать чистый BQ SQL - я вижу только один вариант - выравнивание, как в примере ниже (со слегка измененными фиктивными данными из вашего вопроса)

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, 0.3301 TP_001_A, 0.3305 TP_001_B, 0.3309 TP_002_A, 0.3313 TP_002, 0.3317 TP_003, 0.3721 TP_099_B UNION ALL
  SELECT 2, 0.3302, 0.3306, 0.3110, 0.3314, 0.3318, 0.3322 UNION ALL
  SELECT 3, 0.3303, 0.3307, 0.3311, 0.3315, 0.3319, 0.3323 UNION ALL
  SELECT 4, 0.3504, 0.3308, 0.3312, 0.3316, 0.3420, 0.3324 
)
SELECT id, TRIM(kv.key, '"') col, kv.value
FROM `project.dataset.table` t,
UNNEST(ARRAY(
  SELECT AS STRUCT SPLIT(kv, ':')[OFFSET(0)] key, SPLIT(kv, ':')[OFFSET(1)] value
  FROM UNNEST(SPLIT(TRIM(TO_JSON_STRING(t), '{}'))) kv
  WHERE STARTS_WITH(SPLIT(kv, ':')[OFFSET(0)], '"TP_001') 
)) kv
-- ORDER BY id   

с результатом

Row id  col         value    
1   1   TP_001_A    0.3301   
2   1   TP_001_B    0.3305   
3   2   TP_001_A    0.3302   
4   2   TP_001_B    0.3306   
5   3   TP_001_A    0.3303   
6   3   TP_001_B    0.3307   
7   4   TP_001_A    0.3504   
8   4   TP_001_B    0.3308   

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

Очевидно, что вы можете заменить STARTS_WITH(..., '"TP_001') на REGEXP_CONTAINS, если у вас более сложный фильтр


Есть идеи?

10000