Comment décharger des données avec une structure imbriquée de Google BigQuery en utilisant l'exemple des paramètres personnalisés de Google Analytics

Google BigQuery est une base de données cloud populaire utilisée par les entreprises du monde entier. Il est particulièrement pratique pour travailler avec des données Google Analytics brutes: dans GA 360, l'intégration avec BigQuery se configure en quelques clics, et pour la version gratuite, il existe des scripts et des modules tiers.

Dans les données brutes de Google Analytics, chaque enregistrement (ligne) correspond à une session. À l'intérieur d'une telle entrée se trouvent des champs imbriqués qui correspondent aux hits de la session:


Une telle structure de données imbriquée est souvent déroutante pour les utilisateurs qui ne savent pas comment l'utiliser et décharger ces données.

En utilisant les paramètres personnalisés de Google Analytics à titre d'exemple, je vais essayer d'expliquer «sur les doigts» comment les données intégrées sont stockées dans Google BigQuery et comment elles peuvent être téléchargées.

Les codes de requête donnés fonctionnent, ils peuvent être utilisés pour résoudre des problèmes, en remplaçant leurs noms de table et les numéros d'index de dimensions personnalisées requis.

• Lignes de déchargement
• Déchargement tout en maintenant la structure d'emboîtement
• Exemple de remplacement des valeurs des paramètres utilisateur

Les bases

BigQuery prend en charge 2 dialectes de SQL: hérité et standard. Google recommande d'utiliser la nouvelle norme SQL, sur laquelle nous écrirons des requêtes de déchargement.
Tous ceux qui ont travaillé avec SQL au moins un peu connaissent la construction de requête standard:

SELECT *  * FROM *  * WHERE * * 

Cette conception fonctionne si la structure du tableau est simple, sans autres champs imbriqués dans les cellules:


Nous considérons les tables avec des champs imbriqués. La structure d'un tel tableau (par exemple, les paramètres utilisateur de Google Analytics):

Options GA personnalisées dans BQ

Dans Google BigQuery, une telle table aura les noms de colonnes suivants (le séparateur "." Affiche la structure d'imbrication):


Alors, comment décharger les données des champs imbriqués?

Déchargement de lignes

Retour au tableau avec un exemple de paramètres personnalisés GA dans BQ.

Les colonnes customDimensions.index et customDimensions.value sont les index et les valeurs de la session et les dimensions personnalisées définies par l'utilisateur .

Les colonnes hits.customDimensions.index et hits.customDimensions.value sont les index et les valeurs des dimensions personnalisées frappées .

Dans Google BigQuery, il existe un autre niveau d'action des paramètres utilisateur: le produit. Les noms et les valeurs des dimensions personnalisées du produit dans Google BigQuery se trouvent dans les colonnes hits.product.customDimensions.index et hits.product.customDimensions.value . Ils sont déchargés par analogie avec les paramètres utilisateur atteints, il vous suffit de prendre en compte un autre niveau d'imbrication.

Options utilisateur au niveau de la session et de l'utilisateur

Que devons-nous faire si nous devons décharger les valeurs des dimensions personnalisées de session (personnalisées) pour chaque date sans enregistrer la structure imbriquée (c'est-à-dire ligne par ligne)?

Pour répondre à la question, examinons de plus près le tableau avec les paramètres utilisateur GA dans BQ.
Il montre que les valeurs des cellules de la colonne customDimensions sont un autre tableau :


Il suffit de faire une sous-requête à cette table dans la requête principale:

 SELECT --   date, --    value (SELECT value --   customDimensions,     t FROM t.customDimensions --       WHERE index = 1) AS customDimensions1 FROM --    t     `project.dataset.tablename` AS t 

La sortie est un tableau:


Si nous devons ajouter une colonne avec la valeur d'un autre paramètre utilisateur, nous faisons une autre sous-requête:

 SELECT date, (SELECT value FROM t.customDimensions WHERE index = 1) AS customDimensions1, --      customDimensions (SELECT value FROM t.customDimensions WHERE index = 2) AS customDimensions2 FROM `project.dataset.tablename` AS t 

Nous obtenons ce qui suit:

Options personnalisées de niveau d'accès

Les paramètres d'utilisateur de hit sont déchargés de la même manière que ceux de session (utilisateur), sauf que la sous-requête doit être effectuée dans la table des hits imbriqués. En d'autres termes, les valeurs des cellules de la colonne des hits du tableau de données brutes de Google Analytics sont un tableau imbriqué dans lequel le tableau customDimensions est imbriqué:


La demande de téléchargement des paramètres utilisateur d'accès ligne par ligne sera la suivante:

 SELECT --   date, --    value (SELECT value --   customDimensions,     h FROM h.customDimensions --       WHERE index = 3) AS customDimensions3 FROM --    t     `project.dataset.tablename` AS t, --   t.hits  h     t.hits AS h 

Le résultat de la requête sera une table:


Vous pouvez décharger plusieurs paramètres utilisateur de hit et ajouter le paramètre hitNumber (le numéro de séquence du hit dans la session):

 SELECT date, h.hitNumber AS hitNumber, (SELECT value FROM h.customDimensions WHERE index = 3) AS customDimensions3, --        h.customDimensions (SELECT value FROM h.customDimensions WHERE index = 4) AS customDimensions4 FROM `project.dataset.tablename` AS t, t.hits AS h 

Obtenez le tableau:

Session (utilisateur) + paramètres utilisateur hit

Si dans une requête, nous voulons décharger la session et accéder aux paramètres utilisateur, il vous suffit de faire les sous-requêtes nécessaires pour les tables principale et imbriquée:

 SELECT date, h.hitNumber AS hitNumber, --     (SELECT value FROM t.customDimensions WHERE index=1) AS customDimensions1, (SELECT value FROM t.customDimensions WHERE index=2) AS customDimensions2, --     (SELECT value FROM h.customDimensions WHERE index=3) AS customDimensions3, (SELECT value FROM h.customDimensions WHERE index=4) AS customDimensions4 FROM `project.dataset.tablename` AS t, t.hits AS h 

La table qui sera obtenue à la suite de la requête:

Déchargement tout en maintenant la structure d'emboîtement

Un tel déchargement peut être nécessaire lors du remplacement des valeurs d'un paramètre utilisateur dans Google BigQuery.

Exemple
Dans Google Analytics, le nom du pays au format complet est transféré au paramètre utilisateur de session avec un index de 12 et au paramètre utilisateur hit avec un index de 25 pour les utilisateurs de Russie: RUSSIE. Il est nécessaire de changer le format du pays en abrégé: RUS.

Pour ce faire, vous devez remplacer les valeurs requises du paramètre utilisateur par le pays de l'utilisateur pour l'historique complet des données dans Google BigQuery.

La procédure pour résoudre le problème:

1. Déchargez toutes les données tout en conservant la structure d'imbrication
2. Remplacez la valeur du paramètre utilisateur par le pays
3. Réécrire le tableau

Pour télécharger des données tout en conservant la structure d'imbrication, vous devez utiliser la fonction ARRAY et la construction SELECT AS STRUCT. Voyons ce que c'est.

La syntaxe de la fonction ARRAY est la suivante:

 ARRAY(**) 

Il renvoie un tableau d'éléments.

Comparaison d'un tableau avec un enregistrement ligne par ligne:

A gauche, un tableau, à droite, un enregistrement en ligne

Si nous voulons enregistrer une structure imbriquée et décharger un tableau avec plusieurs colonnes, nous devons utiliser ARRAY (SELECT AS STRUCT ...) :

Tableau imbriqué

Options utilisateur au niveau de la session et de l'utilisateur

Pour le déchargement tout en conservant la structure des dimensions personnalisées de session (personnalisées), nous utilisons la requête:

 SELECT date, --  ARRAY(SELECT AS STRUCT...)    ARRAY(SELECT AS STRUCT index, value FROM t.customDimensions) AS customDimensions FROM `project.dataset.tablename` AS t 

À la suite de son exécution, une table est obtenue dans laquelle la structure d'imbrication des données Google Analytics «brutes» est enregistrée:

Options personnalisées de niveau d'accès

Pour décharger les valeurs des paramètres utilisateur de hit de Google BigQuery tout en conservant la structure d'imbrication, il est important de noter que la table customDimensions est imbriquée dans la table de hits. En d'autres termes, vous devez effectuer la sous-requête ARRAY 2 fois (SELECT AS STRUCT ...): d'abord vers la table des hits imbriqués, puis vers la table customDimensions imbriquée:

 SELECT date, --    t.hits ARRAY(SELECT AS STRUCT hitNumber, --    h.customDimensions ARRAY(SELECT AS STRUCT index, value FROM h.customDimensions) AS customDimensions FROM t.hits AS h ) AS hits FROM `project.dataset.tablename` AS t 

Le résultat de cette requête sera une table:

Session (utilisateur) + paramètres utilisateur hit

Comme pour le déchargement ligne par ligne, nous devons combiner les sous-requêtes ARRAY (SELECT AS STRUCT ...) dans la même requête aux tables imbriquées souhaitées:

 SELECT date, --  () Custom Dimensions ARRAY(SELECT AS STRUCT index, value FROM t.customDimensions ) AS customDimensions, --  Custom Dimensions ARRAY(SELECT AS STRUCT hitNumber, ARRAY(SELECT AS STRUCT index, value FROM h.customDimensions ) AS customDimensions FROM t.hits AS h) AS hits FROM `project.dataset.tablename` AS t 

Quel est le résultat:

Exemple de remplacement des valeurs des paramètres utilisateur

Revenons à notre exemple .
Dans la section précédente, nous avons reçu une demande de téléchargement de session (utilisateur) et atteint les paramètres utilisateur de Google Analytics tout en conservant la structure d'imbrication.
Nous complétons cette requête avec les constructions SELECT * REPLACE pour le déchargement avec remplacement et CASE pour la mise à jour des valeurs des paramètres utilisateur nécessaires:

 --      t.customDimensions  t.hits SELECT *REPLACE( --  () Custom Dimensions ARRAY(SELECT AS STRUCT index, --      CASE WHEN index=12 AND value='RUSSIA' THEN 'RUS' ELSE value END AS value FROM t.customDimensions) AS customDimensions, --  Custom Dimensions --   t.hits      h.customDimensions ARRAY(SELECT AS STRUCT *REPLACE( ARRAY(SELECT AS STRUCT index, --      CASE WHEN index=25 AND value='RUSSIA' THEN 'RUS' ELSE value END AS value FROM h.customDimensions) AS customDimensions) FROM t.hits AS h) AS hits) FROM `project.dataset.tablename` AS t 

À la suite de cette demande, nous obtiendrons le tableau d'origine avec les données brutes de Google Analytics. Elle conservera complètement la structure d'imbrication d'origine, mais les valeurs des paramètres utilisateur nécessaires seront remplacées par de nouvelles.

Le sujet de l'utilisation d'une structure de données imbriquée dans Google BigQuery n'est pas facile.

J'espère avoir réussi à clarifier ce problème. Mais, permettez-moi de vous le rappeler, la meilleure façon d'apprendre à faire quelque chose est de pratiquer davantage.