Bertrand Fabre“Dans certaines tables sur BigQuery je vois des enregistrements sur une ligne qui ont plusieurs lignes dans un champs 😱 ! Et des fois, je vois des UNNEST() dans le code des autres mais en vrai je n’y comprend pas grand chose et je m’éloigne de son utilisation 🫣.”
La première fois que l’on se lance dans l’utilisation des ARRAY() cela peut paraître déroutant et manquer de clarté.
Alors, déjà, c’est quoi un ARRAY ?
Un ARRAY est un ensemble de valeur du même type. Alors que chaque colonne dans une table ne contient qu’une valeur, une colonne de type ARRAY peut contenir plusieurs valeurs.
Les syntaxes
Créer une table avec un champ ARRAY
CREATE OR REPLACE TABLE `datayoka-dev.TESTS.howToUseArrays` (
transactionId INT64,
productId ARRAY<INT64> -- Il peut y avoir d'autres types de données
);☝️ Au moment de la création de la colonne ARRAY<type>, il est aussi possible de spécifier d’autres types de données dedans comme STRING ou DATE, etc.
Manipulation des ARRAYs dans un SELECT
SELECT
1 AS transactionId,
ARRAY[1, 2, 3] AS productId
;
-- Alternative
SELECT
1 AS transactionId,
[1, 2, 3] AS productId
;☝️ Le mot ARRAY n’est pas obligatoire pour créer un ARRAY. Ce sont surtout les crochets qui portent le type de données.
Voici l’affichage dans l’interface BigQuery :
transactionId | productId |
1 | 1 |
2 | |
3 |
Comment manipuler ces ARRAYs ?
Accéder aux éléments
Il est possible d’accéder directement à un élément dans un ARRAY en utilisant les crochets à la fin du nom de la colonne de type ARRAY et en spécifiant le numéro de l’élément souhaité.
💡 Le premier élément d’un ARRAY est indexé à 0.
WITH cte_data AS (
SELECT 1 AS transactionId,
ARRAY[1, 2, 3] AS productId
UNION ALL
SELECT 2 AS transactionId,
ARRAY[3, 5] AS productId
)
SELECT
transactionId,
productId[1] AS productId -- 2ème élément
FROM cte_data
;Le résultat de l’exemple :
transactionId | productId |
1 | 2 |
2 | 5 |
On voit que ce sont les deuxième élément de chaque ARRAY qui ont été affiché.
⚠️ Si on essaye d’accéder à un élément qui n’existe pas dans l’ARRAY (ex : on veut le 3ème élément d’un ARRAY qui n’en compte que deux) il y a un message d’erreur : Array index XX is out of bounds (overflow).
Utilisation de UNNEST
Dans la pratique, l’accès aux éléments d’un ARRAY un par un n’est pas des plus pratique même si cela optimise le stockage (évite les données redondantes) et donc la volumétrie traitée selon les données utilisées.
S’il devient nécessaire de manipuler ces ARRAY dans la table de manière plus classique (table plate), il est possible d’utiliser UNNEST() sur la colonne de type ARRAY.
WITH cte_data AS (
SELECT 1 AS transactionId,
ARRAY[1, 2, 3] AS productId
UNION ALL
SELECT 2 AS transactionId,
ARRAY[3, 5] AS productId
)
SELECT
cte.transactionId,
productIdFromArray
FROM cte_data AS cte
,UNNEST(productId) AS productIdFromArray
;Ce qu’il faut retenir de l’exemple :
- Dans le FROM il faut bien faire référence à la table où se trouve l’ARRAY ;
- Toujours dans le FROM on utilise UNNEST(), après une virgule (pas toujours, mais restons simple aujourd’hui 😝), en précisant la colonne de type ARRAY en paramètre.
- Il est important de nommer ce champ “unnesté”.
- Ensuite, on fait référence à ce nouveau champ dans le SELECT.
Voici le résultat :
transactionId | productIdFromArray |
1 | 1 |
1 | 2 |
1 | 3 |
2 | 3 |
2 | 5 |
Et avec STRUCT ?
Il y a tellement à dire sur les ARRAY !
Je termine par vous suggérer de faire des tests sur des schémas de données plus complexes comme des ARRAY de STRUCT !
Voici un code d’exemple :
WITH cte_data AS (
SELECT 1 AS transactionId,
STRUCT(
ARRAY[1, 2, 3] AS productId,
ARRAY[2, 1, 5] AS quantity,
ARRAY[10.00, 7.00, 5.00] AS salesAmount
) AS transactionDetail
UNION ALL
SELECT 2 AS transactionId,
STRUCT(
ARRAY[3, 5] AS productId,
ARRAY[1, 2] AS quantity,
ARRAY[1.00, 20.00] AS salesAmount
) AS transactionDetail
)
SELECT
transactionId,
transactionDetail
FROM cte_data
;Dans ce code, la table a une granularité transaction (un achat, un ticket). Mais dans cette transaction il y un champ avec une granularité plus fine : transactionDetail.
Ce dernier permet d’avoir le détail des produits acheté avec la quantité et le chiffre d’affaires généré.
Le type STRUCT a été utilisé pour regrouper les éléments qui détaillent la transaction et le type ARRAY a été utilisé pour permettre d’avoir plusieurs lignes de produit acheté dans une seule transaction.
Le résultat de la requête :
Ligne | transactionId | transactionDetail.productId | transactionDetail.quantity | transactionDetail.salesAmount |
1 | 1 | 1 | 2 | 10.0 |
2 | 1 | 7.0 | ||
3 | 5 | 5.0 | ||
2 | 2 | 3 | 1 | 1.0 |
5 | 2 | 20.0 |
Merci de votre lecture !
