Ajout de fichiers avec des schémas différents - BigQuery

Question

J'essaie d'ajouter plusieurs fichiers CSV à une table dans BigQuery. Le problème est qu'ils n'ont pas le même schéma. Ils ont des clés, mais pas les mêmes champs. Voici un exemple :

fichier1.csv

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

fichier2.csv

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

Quelle est la meilleure façon de fusionner ces fichiers.

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

Answer 1

BigQuery n'a pas le concept de clés, donc si vous "ajoutez" deux fichiers ensemble qui ont la même colonne ID, ils ne seront pas fusionnés. Mais si ce que vous voulez faire est de charger deux fichiers avec des schémas différents et d'ajouter les données qu'ils contiennent, vous pouvez le faire assez facilement. Vous pouvez indiquer à bigquery d'effectuer le chargement avec une option de mise à jour du schéma qui indique d'autoriser les changements de schéma. Vous devez également passer le schéma explicite des fichiers que vous ajoutez. Donc dans votre cas :

Si vous avez les fichiers sources :

$ 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

vous pouvez alors faire

$ 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 |
+----+---+---+------+------+

Cependant, ce n'est pas exactement ce que vous avez dit que vous vouliez ; il semble que vous vouliez fusionner la ligne avec l'ID 1 afin qu'elle ait les données des deux fichiers.

La meilleure façon de procéder est de charger deux tables distinctes, puis de faire une jointure. Si vous chargez dans les tables temp.t1 et temp.t2, vous pouvez alors simplement faire une jointure des deux tables. Comme dans

$ 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    |
+----+---+---+------+------+

Answer 2

Il est fort probable que vous ayez besoin de ce tableau final pour l'utiliser ensuite comme jointure à d'autres tableaux - dans ce cas, avoir ce schéma de type pivot n'est pas l'option la plus efficace de toute façon - je vous recommanderais donc de considérer l'option ci-dessous où vous aplatirez votre matrice originale dans un schéma ID - colonne - valeur.

Dans l'exemple ci-dessous - je suppose que vous avez des ensembles de caractéristiques avec des valeurs Vrai / Faux afin que je puisse facilement réconcilier les valeurs "conflictuelles" en utilisant ET ou OU logique - mais la même approche fonctionnera si vous avez des chaînes de caractères comme "T" , "F" (bien sûr, dans ce cas, le code ci-dessous devra être légèrement ajusté).

Donc, ci-dessous, c'est pour BigQuery Standard SQL et avant d'appliquer ce code, il suffit de charger tous vos fichiers dans des tables séparées (file1 > table1, file2 > table2, etc.).

#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

Vous pouvez ajouter autant de lignes ci-dessous que vous le souhaitez.

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

Vous pouvez tester, jouer avec ce qui précède en utilisant un échantillon de données de votre question comme dans l'exemple ci-dessous.

#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

avec résultat

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    

D'après mon expérience, dans la plupart des cas, l'utilisation du schéma flatten ci-dessus est plus simple et plus facile que le schéma auquel vous vous attendiez initialement (dans votre question).

Answer 3

Lorsque vous chargez des fichiers JSON dans BigQuery, celui-ci les identifie et les charge dans les bonnes colonnes, car JSON indique clairement dans chaque enregistrement les colonnes dans lesquelles il souhaite que les données soient chargées. En revanche, il n'en va pas de même avec les CSV : lorsque vous chargez des tableaux CSV dans BigQuery, BigQuery se contente de faire correspondre les colonnes au tableau dans le même ordre pour le tableau et le CSV.

Par conséquent, si vous avez différents schémas CSV, vous devrez les charger dans différentes tables BigQuery et les mettre en correspondance plus tard avec une insertion ou autre.