Chargement...
Aucun résultat.

Apply your skills in Google Cloud console

Instructions et exigences de configuration de l'atelier
Protégez votre compte et votre progression. Utilisez toujours une fenêtre de navigation privée et les identifiants de l'atelier pour exécuter cet atelier.

Examining Billing data with BigQuery

Atelier 30 minutes universal_currency_alt 5 crédits show_chart Débutant
info Cet atelier peut intégrer des outils d'IA pour vous accompagner dans votre apprentissage.
Ce contenu n'est pas encore optimisé pour les appareils mobiles.
Pour une expérience optimale, veuillez accéder à notre site sur un ordinateur de bureau en utilisant un lien envoyé par e-mail.

Overview

In this lab, you learn how to use BigQuery to analyze billing data.

Objectives

In this lab, you learn how to perform the following tasks:

  • Sign in to BigQuery from the Cloud console
  • Create a dataset
  • Create a table
  • Import data from a billing file stored in a bucket
  • Run complex queries on a larger dataset

Setup and requirements

For each lab, you get a new Google Cloud project and set of resources for a fixed time at no cost.

  1. Click the Start Lab button. If you need to pay for the lab, a pop-up opens for you to select your payment method. On the left is the Lab Details panel with the following:

    • The Open Google Cloud console button
    • Time remaining
    • The temporary credentials that you must use for this lab
    • Other information, if needed, to step through this lab

  2. Click Open Google Cloud console (or right-click and select Open Link in Incognito Window if you are running the Chrome browser).

    The lab spins up resources, and then opens another tab that shows the Sign in page.

    Tip: Arrange the tabs in separate windows, side-by-side.

    Note: If you see the Choose an account dialog, click Use Another Account.

  3. If necessary, copy the Username below and paste it into the Sign in dialog.

    {{{user_0.username | "Username"}}}

    You can also find the Username in the Lab Details panel.

  4. Click Next.

  5. Copy the Password below and paste it into the Welcome dialog.

    {{{user_0.password | "Password"}}}

    You can also find the Password in the Lab Details panel.

  6. Click Next.

    Important: You must use the credentials the lab provides you. Do not use your Google Cloud account credentials. Note: Using your own Google Cloud account for this lab may incur extra charges.

  7. Click through the subsequent pages:

    • Accept the terms and conditions.
    • Do not add recovery options or two-factor authentication (because this is a temporary account).
    • Do not sign up for free trials.

After a few moments, the Google Cloud console opens in this tab.

Note: To view a menu with a list of Google Cloud products and services, click the Navigation menu at the top-left, or type the service or product name in the Search field. Navigation menu icon

Task 1. Use BigQuery to import data

Sign in to BigQuery and create a dataset

In this task, you create a use BigQuery to create a dataset. You then create a table, before finally importing billing data from Cloud Storage.

  1. In the Google Cloud console, in the Navigation menu ( Navigation menu icon), click BigQuery.
  2. If prompted, click Done.
  3. Click on the View actions icon next to your project ID (starts with qwiklabs-gcp) and click Create dataset.
Note: You can export billing data directly to BigQuery as outlined in the Export Cloud Billing data to BigQuery Guide. However, for the purposes of this lab, a sample billing file has been prepared for you. It is located in a Cloud Storage bucket where it is accessible to your student account. You will import this billing information into a BigQuery table and examine it.
  1. Specify the following:
Property Value (type value or select option as specified)
Dataset ID: billing_dataset
Data location: US
Default table expiration (check Enable table expiration): 1 days (Default maximum table age)
  1. Click Create Dataset. You should see billing_dataset in the left pane.

Create a table and import

  1. Click on the View actions icon next to your billing_dataset dataset, and click Open and then click Create Table to create a new table.
  2. For Source, specify the following, and leave the remaining settings as their defaults:
Property Value (type value or select option as specified)
Create table from: Google Cloud Storage
Select file from GCS bucket cloud-training/archinfra/BillingExport-2020-09-18.avro
File format Avro
  1. For Destination, specify the following, and leave the remaining settings as their defaults:
Property Value (type value or select option as specified)
Table name sampleinfotable
Table type Native table
  1. Click Create Table. After the job is completed, the table appears below the dataset in the left pane.

Click Check my progress to verify the objective. Use BigQuery to import data

Task 2. Examine the table

In this task, you examine the data which you imported.

  1. Click sampleinfotable.
Note: This displays the schema that BigQuery automatically created based on the data it found in the imported file. Notice that there are strings, integers, timestamps, and floating values.
  1. Click Details. As you can see in Number of Rows

  1. Click Preview tab.

Task 3. Compose a simple query

In this task, you compose and run a simple query to filter billing data.

When you reference a table in a query, both the dataset ID and table ID must be specified; the project ID is optional.

Note: If the project ID is not specified, BigQuery will default to the current project.

All the information you need is available in the BigQuery interface. In the column on the left, you see the dataset ID (billing_dataset) and table ID (sampleinfotable).

Recall that clicking on the table name brings up the Schema with all of the field names.

Now construct a simple query based on the Cost field.

  1. Click + SQL query.
  2. Paste the following in Query Editor:
SELECT * FROM `billing_dataset.sampleinfotable` WHERE Cost > 0
  1. Click Run.

Click Check my progress to verify the objective. Compose a simple query

Task 4. Analyze a large billing dataset with SQL

In this task, you use BigQuery to analyze a sample dataset with 415,602 lines of billing data.

  1. For New Query, paste the following in Query Editor:
SELECT billing_account_id, project.id, project.name, service.description, currency, currency_conversion_rate, cost, usage.amount, usage.pricing_unit FROM `billing_dataset.sampleinfotable`

  1. Click Run. Verify that the resulting table has 415602 lines of billing data.

  2. To find the latest 100 records where there were charges (cost > 0), for New Query, paste the following in Query Editor:

SELECT service.description, sku.description, location.country, cost, project.id, project.name, currency, currency_conversion_rate, usage.amount, usage.unit FROM `billing_dataset.sampleinfotable` WHERE Cost > 0 ORDER BY usage_end_time DESC LIMIT 100
  1. Click Run.
  2. To find all charges that were more than 10 dollars, for Compose New Query, paste the following in Query Editor:
SELECT service.description, sku.description, location.country, cost, project.id, project.name, currency, currency_conversion_rate, usage.amount, usage.unit FROM `billing_dataset.sampleinfotable` WHERE cost > 10

  1. Click Run.

  2. To find the product with the most records in the billing data, for New Query, paste the following in Query Editor:

SELECT service.description, COUNT(*) AS billing_records FROM `billing_dataset.sampleinfotable` GROUP BY service.description ORDER BY billing_records DESC
  1. Click Run.

  1. To find the most frequently used product costing more than 1 dollar, for New Query, paste the following in Query Editor:
SELECT service.description, COUNT(*) AS billing_records FROM `billing_dataset.sampleinfotable` WHERE cost > 1 GROUP BY service.description ORDER BY billing_records DESC
  1. Click Run.

  1. To find the most commonly charged unit of measure, for Compose New Query, paste the following in Query Editor:
SELECT usage.unit, COUNT(*) AS billing_records FROM `billing_dataset.sampleinfotable` WHERE cost > 0 GROUP BY usage.unit ORDER BY billing_records DESC
  1. Click Run.

  1. To find the product with the highest aggregate cost, for New Query, paste the following in Query Editor:
SELECT service.description, ROUND(SUM(cost),2) AS total_cost FROM `billing_dataset.sampleinfotable` GROUP BY service.description ORDER BY total_cost DESC
  1. Click Run.

Click Check my progress to verify the objective. Analyze a large billing dataset with SQL

Task 5. Review

In this lab, you imported billing data into BigQuery that had been generated as a avro file. You ran a simple query on the file. Then you accessed a shared dataset containing more than 22,000 records of billing information. You ran a variety of queries on that data to explore how you can use BigQuery to ask and answer questions by running queries.

End your lab

When you have completed your lab, click End Lab. Google Skills removes the resources you’ve used and cleans the account for you.

You will be given an opportunity to rate the lab experience. Select the applicable number of stars, type a comment, and then click Submit.

The number of stars indicates the following:

  • 1 star = Very dissatisfied
  • 2 stars = Dissatisfied
  • 3 stars = Neutral
  • 4 stars = Satisfied
  • 5 stars = Very satisfied

You can close the dialog box if you don't want to provide feedback.

For feedback, suggestions, or corrections, please use the Support tab.

Copyright 2026 Google LLC All rights reserved. Google and the Google logo are trademarks of Google LLC. All other company and product names may be trademarks of the respective companies with which they are associated.

Avant de commencer

  1. Les ateliers créent un projet Google Cloud et des ressources pour une durée déterminée.
  2. Les ateliers doivent être effectués dans le délai imparti et ne peuvent pas être mis en pause. Si vous quittez l'atelier, vous devrez le recommencer depuis le début.
  3. En haut à gauche de l'écran, cliquez sur Démarrer l'atelier pour commencer.

Utilisez la navigation privée

  1. Copiez le nom d'utilisateur et le mot de passe fournis pour l'atelier
  2. Cliquez sur Ouvrir la console en navigation privée

Connectez-vous à la console

  1. Connectez-vous à l'aide des identifiants qui vous ont été attribués pour l'atelier. L'utilisation d'autres identifiants peut entraîner des erreurs ou des frais.
  2. Acceptez les conditions d'utilisation et ignorez la page concernant les ressources de récupération des données.
  3. Ne cliquez pas sur Terminer l'atelier, à moins que vous n'ayez terminé l'atelier ou que vous ne vouliez le recommencer, car cela effacera votre travail et supprimera le projet.

Ce contenu n'est pas disponible pour le moment

Nous vous préviendrons par e-mail lorsqu'il sera disponible

Parfait !

Nous vous contacterons par e-mail s'il devient disponible

Un atelier à la fois

Confirmez pour mettre fin à tous les ateliers existants et démarrer celui-ci

Utilisez la navigation privée pour effectuer l'atelier

Le meilleur moyen d'exécuter cet atelier consiste à utiliser une fenêtre de navigation privée. Vous éviterez ainsi les conflits entre votre compte personnel et le compte temporaire de participant, qui pourraient entraîner des frais supplémentaires facturés sur votre compte personnel.