Checkpoints
Create a new dataset to store tables
/ 25
Ingest a new Dataset from a CSV
/ 25
Ingest data from Google Cloud Storage
/ 25
Ingest a new dataset from a Google Spreadsheet
/ 25
Ingesting New Datasets into BigQuery
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).
- 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
-
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
-
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. -
If necessary, copy the Username from the Lab Details panel and paste it into the Sign in dialog. Click Next.
-
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. -
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.
Open the BigQuery console
- 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.
- Click Done.
The BigQuery console opens.
Task 1. Create a new dataset to store tables
- To create a dataset, click on the View actions icon next to your Project ID and select Create dataset.
-
Set the Dataset ID to
ecommerce
. Leave the other fields at their default values. -
Click CREATE DATASET.
You'll now see the ecommerce dataset under your project name.
Click Check my progress to verify the objective.
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:
-
Download the following dataset locally onto your computer: Product stock level dataset. (Open and download the file in normal window)
-
To create a table, click on the View actions icon next to the ecommerce dataset and select Open.
-
Click Create Table.
-
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
- Click Create table.
You should now see the products table below the ecommerce dataset.
- Select the products table and view Details.
Click Check my progress to verify the objective.
- 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:
Task 3. Ingest data from Cloud Storage
-
Create a table by clicking on the View actions icon next to the ecommerce dataset, then click Create Table.
-
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
- Click Create table.
Does it work? No
-
Click GO TO JOB when the error message appears, then click the Repeat load job button.
-
In the Create table form, click on Advanced options and in the Write preference dropdown menu, select Overwrite table.
-
Now click Create Table.
Confirm the table was executed successfully.
Click Check my progress to verify the objective.
Task 4. Ingest a new dataset from a Google Spreadsheet
-
Click +COMPOSE NEW QUERY (
).
-
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:
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:
-
In Query results, select SAVE RESULTS, choose Google Sheets from the dropdown.
-
A popup will appear with a link to Open the spreadsheet, select Open.
-
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.
-
Return to your BigQuery tab.
-
Click on the View actions icon next to the ecommerce dataset, then click Create Table.
-
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.
- Click Create table.
Click Check my progress to verify the objective.
Query data from an external spreadsheet
- 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.
SKU |
name |
orderedQuantity |
stockLevel |
restockingLeadTime |
ratio |
comments |
GGOENEBB078899 |
Cam Indoor Security Camera - USA |
2139 |
2615 |
42 |
0.8179732314 |
new shipment on the way |
-
Navigate back to your Google Spreadsheet tab.
-
Type in more comments in the Comments field.
-
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 August 24, 2023
Lab Last Tested August 24, 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.