Les tables Bigquery peuvent contenir des Petabytes de données (1PB = 1024 TB) et les requêtes peuvent donner un résultat en quelques secondes. Cela est possible grâce à la structure spécifique des tables qui sont « en relief ». C’est à dire que ce n’est pas une simple table « à plat », la table BigQuery peut contenir d’autres tables qui peuvent elles-mêmes contenir d’autres tables.
Par exemple :
Il est possible de créer la table suivante dans BigQuery :
- Une table de 1 seule ligne (par exemple, 1 ligne représentant 1 session de plusieurs hits)
- Un champ « Numero_hit » qui contient une table avec les numero de hits (hit1 et hit2)
- Un champ « Index_variables » qui contient une autre table avec l’index des variables de chaque hit
Avec cette requete :
WITH data as (
SELECT [‘Hit1’,’Hit2’] as Numero_hit,
[1,1,2,3,4,5] as Index_variables
)
SELECT *
FROM dataCe qui donne ce resultat :
On constate que tout est imbriqué dans la ligne 1. Le hit 1 a une variable avec l’index 1. Et le hit 2 a 5 variables (index 1, 2, 3, 4, 5).
Desimbriquer les tables avec UNNEST()
Si on veut travailler avec les données d’une table imbriquée, il faut la mettre à plat. Cette desimbrication se fait à l’aide la fonction UNNEST().
Par exemple:
Si on veut travailler avec les index des variables des hits, cela est utile de desimbriquer l’information pour avoir les combinaisons. En réalisant UNNEST() sur le champ Index_variables, cela donne une simple table avec 6 lignes (1 pour chaque valeur : 1,1,2,3,4,5).
Et en le combinant avec l’info du Numero_hit, cela donne les combinaisons suivantes :
WITH data as (
SELECT [‘Hit1’,’Hit2’] as Numero_hit,
[1,1,2,3,4,5] as Index_variables
)
SELECT Numero_hit, unnest_hit
FROM data, UNNEST(Index_variables) as unnest_index
On constate qu’on a alors une table de 6 lignes. Chaque ligne ayant un valeur séparée pour l’index des variables, ce qui est le résultat de la désimbrication. Et le champ Numero_hit est resté imbriqué car UNNEST() ne le concernait pas.
Désimbriquer plusieurs champs en même temps
Pour désimbriquer plusieurs champs en même temps, il s’agira de procéder de manière similaire (via des CROSS JOIN) pour avoir les combinaisons.
Par exemple :
WITH data as (
SELECT [‘Hit1’,’Hit2’] as Numero_hit,
[1,1,2,3,4,5] as Index_variables
)
SELECT *
FROM data, UNNEST(Numero_hit) as unnest_hit, UNNEST(Index_variables) as unnest_index
On obtient une table de 12 lignes. C’est la combinaison des 2 hits avec les 6 valeurs d’index des variables. Et on constate que les 2 premiers champs qui sont ceux de la table initiale sont toujours imbriquées. Alors que les 2 autres qui correspondent aux resultats de UNNEST() sont « à plat » avec 1 valeur par ligne.
Lors de la désimbrication, ne retenir que les bonnes combinaisons
Dans le resultat précédent, on constate que la table contient toutes les combinaisons possibles, mais l’information initiale est déformée. Par exemple : le hit 1 ne devrait être combiné qu’avec la valeur d’index de variable 1 (dans la table initiale les autres index 2, 3, 4, 5 sont liés uniquement au hit 2).
Pour conserver l’info de la table initiale et ne conserver que les bonnes combinaisons, il s’agit de faire un calcul. Le calcul consiste en une regle conditionnelle qui va donner un resultat NULL pour les combinaisons à écarter.
Par exemple, si on souhaite travailler avec la variable d’index 2, cela donne :
WITH data as (
SELECT [‘Hit1’,’Hit2’] as Numero_hit,
[1,1,2,3,4,5] as Index_variables
)
SELECT Numero_hit, unnest_index,
( SELECT MAX(IF(unnest_index=2,unnest_index, NULL)) FROM UNNEST(Index_variables) ) as resultat_regle
FROM data, UNNEST(Numero_hit) as unnest_hit, UNNEST(Index_variables) as unnest_index
La commande MAX(IF(unnest_index=2,unnest_index, NULL)) correspond à la règle conditionnelle. Et on constate dans le résultat que le champ resultat_regle renvoie NULL si unnest_index ne correspond pas à 2, ce qui permet d’ecarter les lignes (combinaisons) non souhaitées avec un simple filtre.
Ainsi, selon le besoin de la requête, il s’agira de faire les règles conditionnelles necessaires. Il est biensur possible de faire plusieurs règles en même temps (dans des champs supplémentaires) et/ou de faire des règles avec d’autres champs à désimbriquer (par exemple avec numero_hit, en plus de Index_variables).
Ci-dessous le rendu dans l’interface BigQuery :
