Блог

Главная - Вопросы по программированию - Добавление файлов с разными схемами — BigQuery

Добавление файлов с разными схемами — BigQuery

#google-bigquery

#google-bigquery

Вопрос:

Я пытаюсь добавить несколько файлов CSV в одну таблицу в BigQuery. Проблема в том, что у них нет одинаковой схемы. У них есть ключи, но не одинаковые поля. Вот пример:

файл1.csv

 ID     A       B       C
1       T       T       T
2       F       T       F
3       F       F       F

file2.csv

 ID     A       B       D
1       T       T       T
4       F       T       F
5       F       F       F

Каков наилучший способ объединить эти файлы. 

 ID     A       B       C       D
1      T       T       T       T
2      F       T       F
3      F       F       F
4      F       T               F
5      F       F               F

Комментарии:

1. Что именно вы хотите сделать? Является ли цель в конечном итоге эффективным объединением этих двух файлов, где столбец D обрабатывается так, как если бы он на самом деле назывался C? Или вы хотите, чтобы D обрабатывался как отдельный столбец после загрузки?

2. да, я хочу, чтобы D был разделен новым столбцом. Только что добавлена таблица результатов.

3. и как вы решаете, какие значения использовать для ID = 1? из file1 или file2? или какая-то другая логика?

Ответ №1:

В BigQuery нет понятия ключей, поэтому, если вы «добавите» два файла вместе, которые имеют один и тот же столбец ID, они не будут объединены. Но если вы хотите просто загрузить два файла с разными схемами и добавить в них данные, вы можете сделать это довольно легко. Вы можете указать bigquery выполнить загрузку с опцией обновления схемы, в которой указано разрешить изменения схемы. Вы также должны передать явную схему файлов, которые вы добавляете. Итак, в вашем случае:

Если у вас есть исходные файлы:

 $ cat one.csv
ID,A,B,C
1,T,T,T
2,F,T,F
3,F,F,F

$ cat two.csv
ID,A,B,D
1,T,T,T
4,F,T,F
5,F,F,F

затем вы можете сделать

 $ bq load --source_format=CSV --schema=id,a,b,c --skip_leading_rows=1 temp.test one.csv
Current status: DONE   
$ bq load --source_format=CSV --schema=id,a,b,d --schema_update_option=ALLOW_FIELD_ADDITION --skip_leading_rows=1 temp.test two.csv
Current status: DONE   
$ bq head temp.test
 ---- --- --- ------ ------ 
| id | a | b |  d   |  c   |
 ---- --- --- ------ ------ 
| 1  | T | T | NULL | T    |
| 2  | F | T | NULL | F    |
| 3  | F | F | NULL | F    |
| 1  | T | T | T    | NULL |
| 4  | F | T | F    | NULL |
| 5  | F | F | F    | NULL |
 ---- --- --- ------ ------

Однако это не совсем то, что вы сказали, что хотели; похоже, вы хотите объединить строку с идентификатором 1, чтобы в ней были данные из обоих файлов.

Лучший способ сделать это — загрузить в две отдельные таблицы, а затем выполнить объединение. Если вы загружаете в таблицы temp.t1 и temp.t2, вы можете просто выполнить объединение двух таблиц. Как в

 $ bq load --source_format=CSV --schema=id,a,b,c --skip_leading_rows=1 temp.t1 one.csv
Current status: DONE   
$ bq load --source_format=CSV --schema=id,a,b,d --skip_leading_rows=1 temp.t2 two.csv
Current status: DONE   
$ bq query --nouse_legacy_sql "SELECT IFNULL(t2.id, t1.id) as id, IFNULL(t2.a,  t1.a) as a, IFNULL(t2.b, t1.b) as b, t1.c as c, t2.d as d   FROM temp.t1 as t1 FULL OUTER JOIN temp.t2 as t2 ON t1.id = t2.id ORDER BY id"
Current status: DONE   
 ---- --- --- ------ ------ 
| id | a | b |  c   |  d   |
 ---- --- --- ------ ------ 
| 1  | T | T | T    | T    |
| 2  | F | T | F    | NULL |
| 3  | F | F | F    | NULL |
| 4  | F | T | NULL | F    |
| 5  | F | F | NULL | F    |
 ---- --- --- ------ ------

Ответ №2:

Скорее всего, вам нужна эта итоговая таблица для последующего использования в качестве соединения с некоторыми другими таблицами — в этом случае наличие этой сводной схемы в любом случае не является наиболее эффективным вариантом, поэтому я бы рекомендовал рассмотреть вариант ниже, в котором вы сгладите исходную матрицу в схему ID — column — value

В примере ниже — я предполагаю, что у вас есть наборы функций со значениями True / False, поэтому я могу легко согласовать «конфликтующие» значения, используя логические И или ИЛИ — но тот же подход будет работать, если у вас действительно есть строки типа «T», «F» (конечно, в таком случае приведенный ниже код должен будетнемного скорректировать)

Итак, ниже приведен стандартный SQL BigQuery, и перед применением этого кода вы просто загружаете все свои файлы в отдельные таблицы (file1> table1, file2> table2 и т. Д.) 

 #standardSQL
CREATE TEMP FUNCTION x(t STRING) AS ((
  ARRAY(SELECT AS STRUCT col, val = 'true' val FROM
  UNNEST(REGEXP_EXTRACT_ALL(t, r',"(. ?)":(?:true|false)')) col WITH OFFSET
  JOIN UNNEST(REGEXP_EXTRACT_ALL(t, r',". ?":(true|false)')) val WITH OFFSET
  USING(OFFSET))  
));
SELECT id, col, LOGICAL_OR(val) val
FROM (
  SELECT ID, col, val FROM `project.dataset.table1` t, UNNEST(x(TO_JSON_STRING(t))) 
  UNION ALL
  SELECT ID, col, val FROM `project.dataset.table2` t, UNNEST(x(TO_JSON_STRING(t))) 
)
GROUP BY id, col

Вы можете добавить столько строк ниже, сколько вам нужно 

 UNION ALL
SELECT ID, col, val FROM `project.dataset.tableX` t, UNNEST(x(TO_JSON_STRING(t)))

Вы можете протестировать, поиграть с приведенными выше примерами данных из вашего вопроса, как в примере ниже 

 #standardSQL
CREATE TEMP FUNCTION x(t STRING) AS ((
  ARRAY(SELECT AS STRUCT col, val = 'true' val FROM
  UNNEST(REGEXP_EXTRACT_ALL(t, r',"(. ?)":(?:true|false)')) col WITH OFFSET
  JOIN UNNEST(REGEXP_EXTRACT_ALL(t, r',". ?":(true|false)')) val WITH OFFSET
  USING(OFFSET))  
));
WITH `project.dataset.table1` AS (
  SELECT 1 ID, TRUE A, TRUE B, TRUE C UNION ALL
  SELECT 2, FALSE, TRUE, FALSE UNION ALL
  SELECT 3, FALSE, FALSE, FALSE 
), `project.dataset.table2` AS (
  SELECT 1 ID, TRUE A, TRUE B, TRUE D UNION ALL
  SELECT 4, FALSE, TRUE, FALSE UNION ALL
  SELECT 5, FALSE, FALSE, FALSE 
)
SELECT id, col, LOGICAL_OR(val) val
FROM (
  SELECT ID, col, val FROM `project.dataset.table1` t, UNNEST(x(TO_JSON_STRING(t))) 
  UNION ALL
  SELECT ID, col, val FROM `project.dataset.table2` t, UNNEST(x(TO_JSON_STRING(t))) 
)
GROUP BY id, col
-- ORDER BY id, col

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

 Row id  col val  
1   1   A   true     
2   1   B   true     
3   1   C   true     
4   1   D   true     
5   2   A   false    
6   2   B   true     
7   2   C   false    
8   3   A   false    
9   3   B   false    
10  3   C   false    
11  4   A   false    
12  4   B   true     
13  4   D   false    
14  5   A   false    
15  5   B   false    
16  5   D   false

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

Ответ №3:

При загрузке файлов JSON в BigQuery он с радостью идентифицирует и загружает их в правильные столбцы, поскольку JSON четко указывает в каждой записи столбцы, в которые он хочет загрузить данные. Между тем, с CSV у вас не может быть того же самого: когда вы загружаете таблицы CSV в BigQuery, BigQuery просто сопоставит столбцы с таблицей в том же порядке для таблицы и CSV.

Следовательно, если у вас разные схемы CSV, вам нужно будет загрузить их в разные таблицы BigQuery и сопоставить их позже с помощью вставки или около того.