GSP411

Overview

BigQuery is Google's fully managed, NoOps, low cost analytics database. With BigQuery you can query terabytes and terabytes of data without having any infrastructure to manage, or needing a database administrator. BigQuery uses SQL and can take advantage of the pay-as-you-go model. BigQuery allows you to focus on analyzing data to find meaningful insights.

The dataset you'll use is an ecommerce dataset that has millions of Google Analytics records for the Google Merchandise Store loaded into BigQuery. You have a copy of that dataset for this lab and will explore the available fields and row for insights.

In this lab, you will ingest several types of datasets into tables inside of BigQuery.

Setup and requirements

Before you click the Start Lab button

Read these instructions. Labs are timed and you cannot pause them. The timer, which starts when you click Start Lab, shows how long Google Cloud resources are made available to you.

This hands-on lab lets you do the lab activities in a real cloud environment, not in a simulation or demo environment. It does so by giving you new, temporary credentials you use to sign in and access Google Cloud for the duration of the lab.

To complete this lab, you need:

• Access to a standard internet browser (Chrome browser recommended).

• Time to complete the lab—remember, once you start, you cannot pause a lab.

How to start your lab and sign in to the Google Cloud console

1. Click the Start Lab button. If you need to pay for the lab, a dialog opens for you to select your payment method. On the left is the Lab Details pane 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 pane.

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 pane.

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 access Google Cloud products and services, click the Navigation menu or type the service or product name in the Search field.

Open the BigQuery console

1. In the Google Cloud Console, select Navigation menu > BigQuery.

The Welcome to BigQuery in the Cloud Console message box opens. This message box provides a link to the quickstart guide and the release notes.

2. Click Done.

The BigQuery console opens.

Task 1. Create a new dataset to store tables

1. To create a dataset, click on the View actions icon next to your Project ID and select Create dataset.

2. Set the Dataset ID to ecommerce. Leave the other fields at their default values.

3. Click CREATE DATASET.

You'll now see the ecommerce dataset under your project name.

Click Check my progress to verify the objective. Create a new dataset to store tables

Task 2. Ingest a new Dataset from a CSV

Scenario: Your marketing team is looking to you to help guide them with what products should be up for promotions based on inventory stock levels. They have also asked how each product is trending in customer sentiment based on the product reviews.

Your existing ecommerce transactional dataset does not have inventory stock levels or product review data in it, but your operations and marketing teams have provided you with new datasets for you to analyze.

Here is how you get started:

1. Download the following dataset locally onto your computer: Product stock level dataset. (Open and download the file in normal window)

2. To create a table, click on the View actions icon next to the ecommerce dataset and select Open.

3. Click Create Table.

4. Specify the below table options:

Source:

• Create table from: Upload
• Select file: select the file you downloaded locally earlier
• File format: CSV

Destination:

• Table name: products

Leave other settings at their default value.

Schema:

• Check Auto Detect for Schema

• Partition and Cluster settings: Leave at default settings

Advanced Options:

• Leave at default settings

5. Click Create table.

You should now see the products table below the ecommerce dataset.

6. Select the products table and view Details.

Click Check my progress to verify the objective. Ingest a new dataset from a CSV

7. Select Preview and confirm all columns have been loaded (sampled below):

You have successfully loaded a CSV file into a new BigQuery table.

Exploring newly loaded data with SQL

Next, practice with a basic query to gain insights from the new products table.

• In the BigQuery EDITOR, write a query to list the top 5 products with the highest stockLevel:

Task 3. Ingest data from Cloud Storage

1. Create a table by clicking on the View actions icon next to the ecommerce dataset, then click Create Table.

2. Specify the below table options:

Source:

• Create table from: Google Cloud Storage
• Select file from Cloud Storage bucket: cloud-training/data-insights-course/exports/products.csv
• File format: CSV

Destination:

• Table name: products

Leave all other settings as default.

Schema:

• Check Auto Detect for Schema.

Advanced Options:

• Leave at default settings

3. Click Create table.

Does it work? No

4. Click GO TO JOB when the error message appears, then click the Repeat load job button.

5. In the Create table form, click on Advanced options and in the Write preference dropdown menu, select Overwrite table.

6. Now click Create Table.

Confirm the table was executed successfully.

Click Check my progress to verify the objective. Ingest data from Cloud Storage

Task 4. Ingest a new dataset from a Google Spreadsheet

1. Click +COMPOSE NEW QUERY ().

2. Execute this next query to show which products are in the greatest restocking need based on inventory turnover and how quickly they can be resupplied:

Task 5. Saving data to Google Sheets

Scenario: You want to provide your supply chain management team with a way to notate whether or not they have contacted the supplier to reorder inventory, and to make any notes on the items. You decide on using a Google Spreadsheet for a quick survey.

Now you'll create it:

1. In Query results, select SAVE RESULTS, choose Google Sheets from the dropdown.

2. A popup will appear with a link to Open the spreadsheet, select Open.

3. In your spreadsheet, in column G add a new field titled comments and for the first product row type new shipment on the way then press Enter.

In Google Sheets, select Share and click Copy link from get link.

4. Return to your BigQuery tab.

5. Click on the View actions icon next to the ecommerce dataset, then click Create Table.

6. Specify the below table options:

Source:

• Create table from: Drive
• Select Drive URI: put-your-spreadsheet-url-here
• File format: Google Sheet

Destination:

• Table type: Leave as default (External table)
• Table name: products_comments

Schema:

• Check Auto Detect for Schema.

Advanced options:

• Set Header rows to skip: to 1.
• Leave all options at their default.

7. Click Create table.

Click Check my progress to verify the objective. Ingest a new dataset from a Google Spreadsheet

Query data from an external spreadsheet

1. Click +COMPOSE NEW QUERY.

Add the below query then click RUN:

Wait for the query to execute. You will see that the new comments field is now returned.

2. Navigate back to your Google Spreadsheet tab.

3. Type in more comments in the Comments field.

4. Navigate back to BigQuery and execute the query again by clicking RUN.

Confirm the new data properly shows in the results.

You have successfully created an external table connection into BigQuery from Google Spreadsheets.

Task 6. External table performance and data quality considerations

Linking external tables to BigQuery (e.g. Google Spreadsheets or directly from Cloud Storage) has several limitations. Two of the most significant are:

• Data consistency is not guaranteed if the data values in the source are changed while querying.
• Data sources stored outside of BigQuery lose the performance benefits of having BigQuery manage your data storage (including but not limited to auto-optimization of your query execution path, certain wildcard functions are disabled, etc.).

Congratulations!

You've successfully created a new dataset and ingested new external data sources into BigQuery from CSV, Cloud Storage, and Google Drive.

Next steps / Learn more

Already have a Google Analytics account and want to query your own datasets in BigQuery? Follow this export guide.

Google Cloud training and certification

...helps you make the most of Google Cloud technologies. Our classes include technical skills and best practices to help you get up to speed quickly and continue your learning journey. We offer fundamental to advanced level training, with on-demand, live, and virtual options to suit your busy schedule. Certifications help you validate and prove your skill and expertise in Google Cloud technologies.

Manual Last Updated November 11, 2024

Lab Last Tested Novermber 11, 2024

Copyright 2025 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.