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 will be made available to you.

This hands-on lab lets you do the lab activities yourself in a real cloud environment, not in a simulation or demo environment. It does so by giving you new, temporary credentials that 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).

Note: Use an Incognito or private browser window to run this lab. This prevents any conflicts between your personal account and the Student account, which may cause extra charges incurred to your personal account.

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

Note: If you already have your own personal Google Cloud account or project, do not use it for this lab to avoid extra charges to your account.

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 pop-up opens for you to select your payment method. On the left is the Lab Details panel with the following:

   • The Open Google 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 Console. 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 from the Lab Details panel and paste it into the Sign in dialog. Click Next.

4. Copy the Password from the Lab Details panel and paste it into the Welcome dialog. Click Next.

   Important: You must use the credentials from the left panel. Do not use your Google Cloud Skills Boost credentials. Note: Using your own Google Cloud account for this lab may incur extra charges.

5. 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 Cloud Console opens in this tab.

Note: You can view the menu with a list of Google Cloud Products and Services by clicking the Navigation menu at the top-left.

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.

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

Tip: Not seeing the checkbox? Ensure the file format is CSV and not Avro.

• 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):

SKU	name	orderedQuantity	stockLevel	restockingLeadTime
GGOEGDHQ014899	20 oz Stainless Steel Insulated Tumbler	499	652	2
GGOEGOAB022499	Satin Black Ballpoint Pen	403	477	2
GGOEYHPB072210	Twill Cap	1429	1997	2
GGOEGEVB071799	Pocket Bluetooth Speaker	214	246	2

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:

#standardSQL SELECT * FROM ecommerce.products ORDER BY stockLevel DESC LIMIT 5

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 GCS bucket: 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:

#standardSQL SELECT *, SAFE_DIVIDE(orderedQuantity,stockLevel) AS ratio FROM ecommerce.products WHERE # include products that have been ordered and # are 80% through their inventory orderedQuantity > 0 AND SAFE_DIVIDE(orderedQuantity,stockLevel) >= .8 ORDER BY restockingLeadTime DESC Note: If you specify a relative project name path like ecommerce.products instead of project_id.ecommerce.products, BigQuery will assume the current project.

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.

Note: There is no load job for creating an externally-linked table since the data is not ingested.

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:

#standardSQL SELECT * FROM ecommerce.products_comments WHERE comments IS NOT NULL

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

SKU	name	orderedQuantity	stockLevel	restockingLeadTime	ratio	comments
GGOENEBB078899	Cam Indoor Security Camera - USA	2139	2615	42	0.8179732314	new shipment on the way

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.

Finish your quest

This self-paced lab is part of the BigQuery for Marketing Analysts quest. A quest is a series of related labs that form a learning path. Completing this quest earns you a badge to recognize your achievement. You can make your badge or badges public and link to them in your online resume or social media account. Enroll in this quest and get immediate completion credit. Refer to the Google Cloud Skills Boost catalog for all available quest

Take your next lab

Continue your quest with How to Build a BI Dashboard Using Google Looker Studio and BigQuery, or check out a different Google Cloud Skills Boost lab, Creating Date-Partitioned Tables in BigQuery.

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 February 7, 2023

Lab Last Tested February 7, 2023

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