GSP410

Présentation

BigQuery est la base de données d'analyse NoOps, économique et entièrement gérée de Google. Avec BigQuery, vous pouvez interroger plusieurs téraoctets de données sans avoir à gérer d'infrastructure ni faire appel à un administrateur de base de données. Basé sur le langage SQL et le modèle de paiement à l'usage, BigQuery vous permet de vous concentrer sur l'analyse des données pour en dégager des informations pertinentes.

Vous allez utiliser un ensemble de données d'e-commerce comprenant des millions d'enregistrements Google Analytics pour le Google Merchandise Store, chargé dans BigQuery. Vous disposez d'une copie de cet ensemble de données pour cet atelier, et vous allez explorer les champs et lignes qu'il contient afin d'obtenir des informations.

Dans cet atelier, vous allez apprendre à créer des tables de génération de rapports permanentes et des vues logiques à partir d'un ensemble de données d'e-commerce existant.

Préparation

Avant de cliquer sur le bouton "Démarrer l'atelier"

Lisez ces instructions. Les ateliers sont minutés, et vous ne pouvez pas les mettre en pause. Le minuteur, qui démarre lorsque vous cliquez sur Démarrer l'atelier, indique combien de temps les ressources Google Cloud resteront accessibles.

Cet atelier pratique vous permet de suivre vous-même les activités dans un véritable environnement cloud, et non dans un environnement de simulation ou de démonstration. Nous vous fournissons des identifiants temporaires pour vous connecter à Google Cloud le temps de l'atelier.

Pour réaliser cet atelier :

• vous devez avoir accès à un navigateur Internet standard (nous vous recommandons d'utiliser Chrome) ;

Remarque : Ouvrez une fenêtre de navigateur en mode incognito/navigation privée pour effectuer cet atelier. Vous éviterez ainsi les conflits entre votre compte personnel et le temporaire étudiant, qui pourraient entraîner des frais supplémentaires facturés sur votre compte personnel.

• vous disposez d'un temps limité ; une fois l'atelier commencé, vous ne pouvez pas le mettre en pause.

Remarque : Si vous possédez déjà votre propre compte ou projet Google Cloud, veillez à ne pas l'utiliser pour réaliser cet atelier afin d'éviter que des frais supplémentaires ne vous soient facturés.

Démarrer l'atelier et se connecter à la console Google Cloud

1. Cliquez sur le bouton Démarrer l'atelier. Si l'atelier est payant, un pop-up s'affiche pour vous permettre de sélectionner un mode de paiement. Sur la gauche, vous trouverez le panneau Détails concernant l'atelier, qui contient les éléments suivants :

   • Le bouton Ouvrir la console Google
   • Le temps restant
   • Les identifiants temporaires que vous devez utiliser pour cet atelier
   • Des informations complémentaires vous permettant d'effectuer l'atelier

2. Cliquez sur Ouvrir la console Google. L'atelier lance les ressources, puis ouvre la page Se connecter dans un nouvel onglet.

   Conseil : Réorganisez les onglets dans des fenêtres distinctes, placées côte à côte.

   Remarque : Si la boîte de dialogue Sélectionner un compte s'affiche, cliquez sur Utiliser un autre compte.

3. Si nécessaire, copiez le nom d'utilisateur inclus dans le panneau Détails concernant l'atelier et collez-le dans la boîte de dialogue Se connecter. Cliquez sur Suivant.

4. Copiez le mot de passe inclus dans le panneau Détails concernant l'atelier et collez-le dans la boîte de dialogue de bienvenue. Cliquez sur Suivant.

   Important : Vous devez utiliser les identifiants fournis dans le panneau de gauche. Ne saisissez pas vos identifiants Google Cloud Skills Boost. Remarque : Si vous utilisez votre propre compte Google Cloud pour cet atelier, des frais supplémentaires peuvent vous être facturés.

5. Accédez aux pages suivantes :

   • Acceptez les conditions d'utilisation.
   • N'ajoutez pas d'options de récupération ni d'authentification à deux facteurs (ce compte est temporaire).
   • Ne vous inscrivez pas aux essais offerts.

Après quelques instants, la console Cloud s'ouvre dans cet onglet.

Remarque : Vous pouvez afficher le menu qui contient la liste des produits et services Google Cloud en cliquant sur le menu de navigation en haut à gauche.

Ouvrir la console BigQuery

1. Dans la console Google Cloud, sélectionnez le menu de navigation > BigQuery.

Le message Bienvenue sur BigQuery dans Cloud Console s'affiche. Il contient un lien vers le guide de démarrage rapide et les notes de version.

2. Cliquez sur OK.

La console BigQuery s'ouvre.

Tâche 1 : Créer un ensemble de données pour stocker les tables

1. Dans BigQuery, cliquez sur l'icône Afficher les actions à côté de l'ID de votre projet et sélectionnez Créer un ensemble de données.

2. Définissez l'ID de l'ensemble de données sur ecommerce et conservez la valeur par défaut de toutes les autres options (Emplacement des données, Expiration de la table par défaut).

3. Cliquez sur CRÉER UN ENSEMBLE DE DONNÉES.

Cliquez sur Vérifier ma progression pour valider l'objectif. Créer un ensemble de données pour stocker les tables

Tâche 2 : Corriger des instructions CREATE TABLE

Votre équipe d'analystes vous a fourni les instructions de requête ci-dessous, qui permettent de créer une table permanente dans votre nouvel ensemble de données d'e-commerce. Malheureusement, elles ne fonctionnent pas correctement.

Pour chaque requête, diagnostiquez le problème et proposez une solution.

Règles de création de tables avec SQL dans BigQuery

Lisez ces règles de création de tables, qui vous serviront de référence pour corriger les requêtes erronées :

• La requête doit inclure la liste de colonnes spécifiée ou des colonnes inférées à partir d'une clause AS query_statement (ou les deux).
• Lorsque ces deux éléments sont présents, BigQuery ignore les noms figurant dans la clause AS query_statement et met les colonnes en correspondance avec celles de la liste de colonnes en fonction de leur position.
• Lorsque seule la clause AS query_statement est spécifiée, BigQuery détermine le nom et le type des colonnes à l'aide de la clause AS query_statement.
• Les noms des colonnes doivent être spécifiés à l'aide de la liste de colonnes ou de la clause AS query_statement.
• Les noms de colonnes en double ne sont pas autorisés.

Requête 1 : Colonnes, colonnes, colonnes

• Ajoutez cette requête à l'éditeur BigQuery, exécutez-la, diagnostiquez l'erreur, puis répondez aux questions qui suivent :

#standardSQL # copy one day of ecommerce data to explore CREATE OR REPLACE TABLE ecommerce.all_sessions_raw_20170801 OPTIONS( description="Raw data from analyst team into our dataset for 08/01/2017" ) AS SELECT fullVisitorId, * FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE date = '20170801' #56,989 records ;

Error: CREATE TABLE has columns with duplicate name fullVisitorId at [7:2]

Quelle règle de création de table la requête ci-dessus enfreint-elle ?

Requête 2 : Colonnes revisitées

• Ajoutez cette requête à l'éditeur BigQuery, exécutez-la, diagnostiquez l'erreur, puis répondez aux questions qui suivent :

#standardSQL # copy one day of ecommerce data to explore CREATE OR REPLACE TABLE ecommerce.all_sessions_raw_20170801 #schema ( fullVisitorId STRING OPTIONS(description="Unique visitor ID"), channelGrouping STRING OPTIONS(description="Channel e.g. Direct, Organic, Referral...") ) OPTIONS( description="Raw data from analyst team into our dataset for 08/01/2017" ) AS SELECT * FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE date = '20170801' #56,989 records ;

Error: The number of columns in the column definition list does not match the number of columns produced by the query at [5:1]

Quelle règle de création de table la requête ci-dessus enfreint-elle ?

Remarque : Vous ne pouvez pas spécifier pour une nouvelle table un schéma de champs qui ne corresponde pas au nombre de colonnes renvoyé par l'instruction de requête. La requête ci-dessus spécifie un schéma à deux colonnes avec fullVisitorId et channelGrouping, alors que l'instruction de la requête demande de renvoyer toutes les colonnes (\*).

Requête 3 : Valide, ou presque…

• Ajoutez cette requête à l'éditeur BigQuery, exécutez-la, diagnostiquez l'erreur, puis répondez aux questions qui suivent :

#standardSQL # copy one day of ecommerce data to explore CREATE OR REPLACE TABLE ecommerce.all_sessions_raw_20170801 #schema ( fullVisitorId STRING OPTIONS(description="Unique visitor ID"), channelGrouping STRING OPTIONS(description="Channel e.g. Direct, Organic, Referral...") ) OPTIONS( description="Raw data from analyst team into our dataset for 08/01/2017" ) AS SELECT fullVisitorId, city FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE date = '20170801' #56,989 records ;

Valid: This query will process 1.1 GiB when run.

Souvenez-vous de la Règle 2 : lorsque la liste des colonnes et la clause AS query_statement sont toutes deux présentes, BigQuery ignore les noms figurant dans la clause AS et met les colonnes en correspondance avec la liste de colonnes en fonction de leur position.

Cliquez sur Vérifier ma progression pour valider l'objectif. Créer une table

Requête 4 : Le portier

• Exécutez la requête ci-dessous dans l'éditeur BigQuery, diagnostiquez l'erreur, puis répondez aux questions qui suivent :

#standardSQL # copy one day of ecommerce data to explore CREATE OR REPLACE TABLE ecommerce.all_sessions_raw_20170801 #schema ( fullVisitorId STRING NOT NULL OPTIONS(description="Unique visitor ID"), channelGrouping STRING NOT NULL OPTIONS(description="Channel e.g. Direct, Organic, Referral..."), totalTransactionRevenue INT64 NOT NULL OPTIONS(description="Revenue * 10^6 for the transaction") ) OPTIONS( description="Raw data from analyst team into our dataset for 08/01/2017" ) AS SELECT fullVisitorId, channelGrouping, totalTransactionRevenue FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE date = '20170801' #56,989 records ;

Valid: This query will process 907.52 MiB when run.

Corrigez la requête, puis exécutez-la à nouveau pour vérifier qu'elle fonctionne correctement.

Requête 5 : Fonctionne comme prévu

1. Exécutez cette requête dans l'éditeur BigQuery, et répondez aux questions qui suivent :

#standardSQL # copy one day of ecommerce data to explore CREATE OR REPLACE TABLE ecommerce.all_sessions_raw_20170801 #schema ( fullVisitorId STRING NOT NULL OPTIONS(description="Unique visitor ID"), channelGrouping STRING NOT NULL OPTIONS(description="Channel e.g. Direct, Organic, Referral..."), totalTransactionRevenue INT64 OPTIONS(description="Revenue * 10^6 for the transaction") ) OPTIONS( description="Raw data from analyst team into our dataset for 08/01/2017" ) AS SELECT fullVisitorId, channelGrouping, totalTransactionRevenue FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE date = '20170801' #56,989 records ;

2. Parcourez le panneau de votre ensemble de données d'e-commerce afin de vérifier que la table all_sessions_raw_(1) y figure.

Pourquoi le nom complet de la table n'apparaît-il pas ?

Réponse : le suffixe 20170801 de la table est partitionné automatiquement par jour. Si nous avions créé davantage de tables pour d'autres jours, il existerait autant de noms all_sessions_raw_(N) que de nombre de jours de données distincts (N). Les différentes méthodes permettant de partitionner vos tables de données sont abordées dans un autre atelier.

Cliquez sur Vérifier ma progression pour valider l'objectif. Fonctionne comme prévu

Requête 6 : À vous de jouer

Objectif : dans l'Éditeur de requête, créez une table permanente qui stocke toutes les transactions du 1er août 2017 associées à des revenus.

Référez-vous aux règles ci-dessous :

• Créez une table intitulée revenue_transactions_20170801 dans votre ensemble de données d'e-commerce. Si cette table existe déjà, remplacez-la.
• Utilisez la table data-to-insights.ecommerce.all_sessions_raw comme source de données brutes.
• Divisez le champ des revenus par 1 000 000, puis stockez-le en tant que champ de type FLOAT64 plutôt que de type INTEGER.
• Incluez uniquement les transactions associées à des revenus dans votre table finale (indice : utilisez une clause WHERE).
• Incluez uniquement les transactions du 1er août 2017.
• Incluez les champs suivants :
  • fullVisitorId en tant que champ obligatoire (REQUIRED) de type chaîne.
  • visitId en tant que champ obligatoire (REQUIRED) de type chaîne (indice : vous devrez effectuer une conversion du type).
  • channelGrouping en tant que champ obligatoire (REQUIRED) de type chaîne.
  • totalTransactionRevenue en tant que champ de type FLOAT64.
• Ajoutez les descriptions courtes des quatre champs ci-dessus en vous reportant au schéma.
• Veillez à dédupliquer les enregistrements associés aux mêmes fullVisitorId et visitId (indice : utilisez la clause DISTINCT).

1. Rédigez la réponse à la demande ci-dessus dans BigQuery, puis comparez-la à la réponse ci-dessous.

Réponse possible :

#standardSQL # copy one day of ecommerce data to explore CREATE OR REPLACE TABLE ecommerce.revenue_transactions_20170801 #schema ( fullVisitorId STRING NOT NULL OPTIONS(description="Unique visitor ID"), visitId STRING NOT NULL OPTIONS(description="ID of the session, not unique across all users"), channelGrouping STRING NOT NULL OPTIONS(description="Channel e.g. Direct, Organic, Referral..."), totalTransactionRevenue FLOAT64 NOT NULL OPTIONS(description="Revenue for the transaction") ) OPTIONS( description="Revenue transactions for 08/01/2017" ) AS SELECT DISTINCT fullVisitorId, CAST(visitId AS STRING) AS visitId, channelGrouping, totalTransactionRevenue / 1000000 AS totalTransactionRevenue FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE date = '20170801' AND totalTransactionRevenue IS NOT NULL #XX transactions ;

2. Lorsque vous aurez exécuté la requête correctement, vérifiez que la nouvelle table revenue_transactions_20170801 est présente dans votre ensemble de données ecommerce, puis sélectionnez-la.

3. Vérifiez que le schéma correspond à l'exemple ci-dessous. Notez le type, le caractère obligatoire et la description facultative des champs :

Gérer les mises à jour des données source en amont

Comment éviter les données obsolètes ?

Il existe deux façons d'éviter les données obsolètes dans les tables de création de rapports :

1. La première consiste à actualiser périodiquement les tables permanentes en réexécutant les requêtes qui insèrent de nouveaux enregistrements. Pour cela, utilisez des requêtes BigQuery programmées ou un workflow Cloud Dataprep/Cloud Dataflow.
2. La seconde consiste à réexécuter une requête stockée à l'aide de vues logiques, chaque fois que la vue est sélectionnée.

La suite de cet atelier sera consacrée à la création de vues logiques.

Cliquez sur Vérifier ma progression pour valider l'objectif. Créer une table

Tâche 3 : Créer des vues

Une vue est une requête enregistrée qui est exécutée chaque fois que la vue en question est appelée. Dans BigQuery, les vues sont logiques, elles ne sont pas matérialisées. Seule la requête est stockée dans la vue, pas les données sous-jacentes.

Demander les 100 dernières transactions

1. Copiez la requête ci-dessous, collez-la dans BigQuery, puis exécutez-la :

#standardSQL SELECT DISTINCT date, fullVisitorId, CAST(visitId AS STRING) AS visitId, channelGrouping, totalTransactionRevenue / 1000000 AS totalTransactionRevenue FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE totalTransactionRevenue IS NOT NULL ORDER BY date DESC # latest transactions LIMIT 100 ;

2. Parcourez les résultats. Quelle a été la dernière transaction supérieure à 2 000 $ ?

Réponse :

date	fullVisitorId	visitId	channelGrouping	totalTransactionRevenue
20170801	9947542428111966715	1501608078	Referral	2934.61

Si de nouveaux enregistrements étaient ajoutés à cet ensemble de données d'e-commerce public, la dernière transaction serait également mise à jour.

3. Pour gagner du temps et favoriser une organisation et une collaboration plus efficaces, vous pouvez enregistrer des requêtes courantes de création de rapport sous forme de vues, comme indiqué ci-dessous :

#standardSQL CREATE OR REPLACE VIEW ecommerce.vw_latest_transactions AS SELECT DISTINCT date, fullVisitorId, CAST(visitId AS STRING) AS visitId, channelGrouping, totalTransactionRevenue / 1000000 AS totalTransactionRevenue FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE totalTransactionRevenue IS NOT NULL ORDER BY date DESC # latest transactions LIMIT 100 ; Remarque : Il est souvent difficile de déterminer si la sélection s'applique à une table ou à une vue uniquement à partir du nom. Pour remédier à cela, vous pouvez, par exemple, ajouter le préfixe vw_, ou le suffixe _vw ou _view au nom de votre vue.

Vous pouvez aussi associer une description et des libellés à votre vue à l'aide d'OPTIONS.

4. Copiez la requête ci-dessous, collez-la dans BigQuery, puis exécutez-la :

#standardSQL CREATE OR REPLACE VIEW ecommerce.vw_latest_transactions OPTIONS( description="latest 100 ecommerce transactions", labels=[('report_type','operational')] ) AS SELECT DISTINCT date, fullVisitorId, CAST(visitId AS STRING) AS visitId, channelGrouping, totalTransactionRevenue / 1000000 AS totalTransactionRevenue FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE totalTransactionRevenue IS NOT NULL ORDER BY date DESC # latest transactions LIMIT 100 ;

5. Cherchez la nouvelle table vw_latest_transactions dans votre ensemble de données ecommerce, puis sélectionnez-la.

6. Sélectionnez l'onglet DÉTAILS.

7. Vérifiez que la description et les libellés de votre vue apparaissent bien dans l'interface utilisateur de BigQuery.

Vous pouvez aussi afficher la requête qui définit la vue sur la page des détails. Cela peut vous aider à comprendre la logique des vues que vous et les membres de votre équipe avez créées.

Cliquez sur Vérifier ma progression pour valider l'objectif. Créer une vue

8. À présent, exécutez cette requête pour créer une vue :

#standardSQL # top 50 latest transactions CREATE VIEW ecommerce.vw_latest_transactions # CREATE OPTIONS( description="latest 50 ecommerce transactions", labels=[('report_type','operational')] ) AS SELECT DISTINCT date, fullVisitorId, CAST(visitId AS STRING) AS visitId, channelGrouping, totalTransactionRevenue / 1000000 AS totalTransactionRevenue FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE totalTransactionRevenue IS NOT NULL ORDER BY date DESC # latest transactions LIMIT 50 ;

Error: Already Exists: Table project-name:ecommerce.vw_latest_transactions

Vous obtiendrez probablement une erreur si vous avez déjà créé cette vue précédemment. Comprenez-vous pourquoi ?

Réponse : après modification, l'instruction de création de la vue indique uniquement CREATE, et pas CREATE OR REPLACE. Donc, elle ne vous permet pas de remplacer les tables ou les vues existantes. Une troisième possibilité serait d'utiliser l'instruction CREATE VIEW IF NOT EXISTS afin de créer la vue ou la table uniquement si elle n'existe pas. Dans le cas contraire, l'instruction est ignorée et ne génère aucune erreur.

Créer une vue : À vous de jouer

Scénario : votre équipe antifraude vous demande de créer un rapport listant les 10 dernières transactions concernant une commande de 1 000 $ ou plus, afin de les contrôler manuellement.

Tâche : créez une vue renvoyant les 10 transactions les plus récentes, associées à un montant supérieur à 1 000 $ et effectuées à partir du 1er janvier 2017.

Référez-vous à ces règles :

• Créez une vue intitulée "vw_large_transactions" dans votre ensemble de données d'e-commerce. Si cette vue existe déjà, remplacez-la.

• Donnez-lui la description "large transactions for review" (transactions importantes à contrôler).

• Attribuez à cette vue le libellé [("org_unit", "loss_prevention")] (unité organisationnelle, prévention des pertes).

• Utilisez la table data-to-insights.ecommerce.all_sessions_raw comme source de données brutes.

• Divisez le champ des revenus par 1 000 000.

• Incluez uniquement les transactions associées à des revenus supérieurs ou égaux à 1 000.

• Incluez uniquement les transactions effectuées à partir du 1er janvier 2017, triées de la plus récente à la plus ancienne.

• Incluez uniquement le code de devise currencyCode = 'USD'.

• Renvoyez les champs suivants :

  • date
  • fullVisitorId
  • visitId
  • channelGrouping
  • totalTransactionRevenue AS revenue
  • currencyCode
  • v2ProductName

• Veillez à dédupliquer les enregistrements (indice : utilisez la clause DISTINCT).

• À vous d'essayer :

/* Rédigez la réponse à la demande ci-dessus dans BigQuery, puis comparez-la à la réponse donnée ci-dessous */

Solution possible :

#standardSQL CREATE OR REPLACE VIEW ecommerce.vw_large_transactions OPTIONS( description="large transactions for review", labels=[('org_unit','loss_prevention')] ) AS SELECT DISTINCT date, fullVisitorId, visitId, channelGrouping, totalTransactionRevenue / 1000000 AS revenue, currencyCode #v2ProductName FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE (totalTransactionRevenue / 1000000) > 1000 AND currencyCode = 'USD' ORDER BY date DESC # latest transactions LIMIT 10 ;

Notez que vous devez répéter la division dans la clause WHERE, car vous ne pouvez pas filtrer les données à l'aide d'alias de noms de champs.

Cliquez sur Vérifier ma progression pour valider l'objectif. Créer une vue renvoyant 10 transactions récentes

Bonus

Scénario : votre service antifraude vous remercie d'avoir créé cette requête qu'il exécute quotidiennement pour identifier d'éventuelles commandes suspectes. Il vous demande à présent d'inclure un aperçu des produits figurant dans chaque commande, en plus des résultats que vous renvoyiez précédemment.

À l'aide de la fonction d'agrégation de chaînes STRING_AGG de BigQuery et du champ v2ProductName, modifiez votre précédente requête de sorte qu'elle renvoie 10 des noms de produits associés à chaque commande, par ordre alphabétique.

Solution possible :

#standardSQL CREATE OR REPLACE VIEW ecommerce.vw_large_transactions OPTIONS( description="large transactions for review", labels=[('org_unit','loss_prevention')] ) AS SELECT DISTINCT date, fullVisitorId, visitId, channelGrouping, totalTransactionRevenue / 1000000 AS totalTransactionRevenue, currencyCode, STRING_AGG(DISTINCT v2ProductName ORDER BY v2ProductName LIMIT 10) AS products_ordered FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE (totalTransactionRevenue / 1000000) > 1000 AND currencyCode = 'USD' GROUP BY 1,2,3,4,5,6 ORDER BY date DESC # latest transactions LIMIT 10

Notez ici les deux ajouts : STRING_AGG() qui permet d'agréger la liste des produits de chaque commande et, puisque vous procédez à une agrégation, l'instruction requise GROUP BY pour les autres champs.

Limiter l'accès aux données dans les vues avec SESSION_USER()

Scénario : le responsable de votre équipe de gestion des données vous demande de trouver un moyen de limiter la visibilité, au sein de votre organisation, des données fournies par la vue que vous venez de créer. Les informations relatives aux commandes sont particulièrement sensibles et ne doivent être partagées qu'avec des utilisateurs ayant besoin d'en avoir connaissance.

Tâche : modifiez la vue précédemment créée de sorte que seuls les utilisateurs connectés à une session sur le domaine qwiklabs.net puissent voir les données sous-jacentes de la vue. (Remarque : Vous créerez des listes d'autorisation pour des groupes d'utilisateurs spécifiques dans un prochain atelier. Pour le moment, vous validez en fonction du domaine de l'utilisateur associé à la session.)

1. Pour afficher les informations de connexion de votre propre session, exécutez la requête ci-dessous, qui utilise SESSION_USER() :

#standardSQL SELECT SESSION_USER() AS viewer_ldap;

Vous verrez apparaître : xxxx@qwiklabs.net.

2. Modifiez la requête ci-dessous de manière à ajouter un filtre autorisant uniquement les utilisateurs du domaine qwiklabs.net à afficher les résultats :

#standardSQL SELECT DISTINCT SESSION_USER() AS viewer_ldap, REGEXP_EXTRACT(SESSION_USER(), r'@(.+)') AS domain, date, fullVisitorId, visitId, channelGrouping, totalTransactionRevenue / 1000000 AS totalTransactionRevenue, currencyCode, STRING_AGG(DISTINCT v2ProductName ORDER BY v2ProductName LIMIT 10) AS products_ordered FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE (totalTransactionRevenue / 1000000) > 1000 AND currencyCode = 'USD' # add filter here GROUP BY 1,2,3,4,5,6,7,8 ORDER BY date DESC # latest transactions LIMIT 10

Solution possible :

#standardSQL SELECT DISTINCT SESSION_USER() AS viewer_ldap, REGEXP_EXTRACT(SESSION_USER(), r'@(.+)') AS domain, date, fullVisitorId, visitId, channelGrouping, totalTransactionRevenue / 1000000 AS totalTransactionRevenue, currencyCode, STRING_AGG(DISTINCT v2ProductName ORDER BY v2ProductName LIMIT 10) AS products_ordered FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE (totalTransactionRevenue / 1000000) > 1000 AND currencyCode = 'USD' AND REGEXP_EXTRACT(SESSION_USER(), r'@(.+)') IN ('qwiklabs.net') GROUP BY 1,2,3,4,5,6,7,8 ORDER BY date DESC # latest transactions LIMIT 10

3. Exécutez la requête ci-dessus pour vérifier que vous pouvez voir les enregistrements renvoyés.

À présent, supprimez tous les domaines du filtre IN REGEXP_EXTRACT(SESSION_USER(), r'@(.+)') IN (''), réexécutez la requête, puis vérifiez qu'aucun zéro n'a été renvoyé.

4. Recréez et remplacez la vue vw_large_transactions par la nouvelle requête ci-dessus. Ajoutez le paramètre OPTIONS expiration_timestamp de sorte que la vue complète couvre les 90 derniers jours :

expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 90 DAY).

Solution possible :

#standardSQL CREATE OR REPLACE VIEW ecommerce.vw_large_transactions OPTIONS( description="large transactions for review", labels=[('org_unit','loss_prevention')], expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 90 DAY) ) AS #standardSQL SELECT DISTINCT SESSION_USER() AS viewer_ldap, REGEXP_EXTRACT(SESSION_USER(), r'@(.+)') AS domain, date, fullVisitorId, visitId, channelGrouping, totalTransactionRevenue / 1000000 AS totalTransactionRevenue, currencyCode, STRING_AGG(DISTINCT v2ProductName ORDER BY v2ProductName LIMIT 10) AS products_ordered FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE (totalTransactionRevenue / 1000000) > 1000 AND currencyCode = 'USD' AND REGEXP_EXTRACT(SESSION_USER(), r'@(.+)') IN ('qwiklabs.net') GROUP BY 1,2,3,4,5,6,7,8 ORDER BY date DESC # latest transactions LIMIT 10; Remarque : Vous pouvez également appliquer l'option expiration_timestamp à des tables permanentes.

Cliquez sur Vérifier ma progression pour valider l'objectif. Exécuter une requête dans les vues avec session_user pour limiter l'accès aux données

5. Avec l'instruction SELECT ci-dessous, vérifiez que vous pouvez voir les données renvoyées dans la vue (compte tenu de votre accès au domaine) ainsi que le code temporel d'expiration dans les détails de la vue :

#standardSQL SELECT * FROM ecommerce.vw_large_transactions;

Félicitations !

Vous avez créé des tables et des vues à accès contrôlé avec SQL DDL (Data Definition Language) dans BigQuery.

Terminer votre quête

Cet atelier d'auto-formation fait partie de la quête BigQuery for Marketing Analysts. Une quête est une série d'ateliers associés qui constituent un parcours de formation. Si vous terminez cette quête, vous obtenez un badge attestant de votre réussite. Vous pouvez rendre publics les badges que vous recevez et ajouter leur lien dans votre CV en ligne ou sur vos comptes de réseaux sociaux. Inscrivez-vous à cette quête pour obtenir immédiatement les crédits associés. Découvrez toutes les quêtes disponibles dans le catalogue Google Cloud Skills Boost.

Atelier suivant

Poursuivez votre quête avec l'atelier Ingérer de nouveaux ensembles de données dans BigQuery ou l'une de nos suggestions :

• Créer des entrepôts de données à l'aide des opérateurs JOIN et UNION
• Créer des tables partitionnées par date dans BigQuery

Étapes suivantes et informations supplémentaires

Vous possédez un compte Google Analytics et souhaitez interroger vos propres ensembles de données dans BigQuery ? Suivez ce guide d'exportation.

Dernière mise à jour du manuel : 24 janvier 2023

Dernier test de l'atelier : 24 janvier 2023

Copyright 2024 Google LLC Tous droits réservés. Google et le logo Google sont des marques de Google LLC. Tous les autres noms d'entreprises et de produits peuvent être des marques des entreprises auxquelles ils sont associés.