The best way to learn something is to try it yourself. So, we've turned a bunch of the demos our instructors do into Do-It-Now activities, where students can try each concept out in quick, bite-size activities.

If you are using these as part of an Instructor Led Training (ILT), make sure to listen to your instructor and coordinate your efforts with their direction.

Overview

1. Many of these activities will cost money

Sample data

2. Many of the activities use data in the roi-bq-demos.bq_demo dataset
3. Other activities rely on additional sample tables that need to be derived from the tables in the bq_demo dataset. You can use the directions found here to generate the required tables.
4. There is a much smaller dataset which will work with the demos: roi-bq-demos.bq_demo_small. Queries will be much faster with this dataset, so aren't as effective at illustrating query speed benefits.

Setting up

1. Log into your lab-provided project with your lab user account.
2. Navigate to the BigQuery UI.
3. In a separate browser tab, open https://github.com/roitraining/gcp-demos/blob/master/bigquery/wiki_query_example.sql.

Running a query

4. Enter the query from the github file into the BigQuery code editor, and change the term you are searching for to whatever word you like.
5. Run the query. Check the Job Information pane and note the query execution time and the bytes processed.

Feel the power!

6. Modify your query, changing the table name from 1M to 10M. Rerun the query and note the job info.
7. Repeat the process with the 100M, 1B, 10B and 100B tables.

Setting up

1. Open the console
2. Go to BigQuery page
3. In the Explorer pane, expand your project; note that there are no resources as yet

Starring projects (docs)

4. Star the bigquery-samples project by name
5. Star the bigquery-public-data project by name
6. Star the roi-bq-demos project by name
7. Star any additional projects suggested by your instructor

Exploring datasets

8. Expand bigquery-samples project in the Explorer pane.
9. Note the datasets; expand one or two of the datasets. A dataset is a collection of assets (can be secured as a unit; lives in a specific location)
10. Click on wikipedia_benchmark in the Explorer pane; look at the metadata displayed in the right pane.
11. Click on the three dot menu to the right of a dataset in Explorer and note the available actions

Creating a dataset

12. In your project, create a new dataset called class

Exploring tables

13. Review tables in the bigquery-samples.wikipedia_benchmark dataset
14. Click on 100B table
15. Review the table schema shown on the right
16. Look at table details
17. Look at preview data
18. Check out three dot menu next to the table name on the left

Setting up

1. Make sure you have the BigQuery console open.
2. In a separate browser tab, open https://github.com/roitraining/gcp-demos/blob/master/bigquery/views_example.sql

Making the case for views

3. Enter the query with subquery query into the BigQuery editor
4. Review the query - what does it do?
5. Examine just the subquery
6. Run just the subquery and note results (CMD-e)
7. Next, run the whole query
   a. It runs the subquery first, and generates an in-memory table
   b. It then runs the outer query against the in-memory table
   c. Run the query and check the results
8. Rather than using subqueries, you can use WITH clauses. This behaves effectively the same. Try running the with clause query in BigQuery

What if you like that subquery a lot, and would like to use it in many other queries? Rather than copy/paste over and over, you can create a view. A view is just a saved, shared subquery.

9. Run the create view query in BigQuery
10. Check out the view in the UI
11. Now try running the query view query, which queries the view

See the jobs you've run

12. Click on the history links at the bottom of the page and review the queries that you've run

Setting up

1. Make sure you have the BigQuery console open.

Working with cache

2. Run this query:

select * from `roi-bq-demos.bq_demo.customer` where cust_state = "AK"

3. Look at JOB INFORMATION
4. Note the query duration and bytes processed
5. Click on the link for the Destination table. This temp table has the results of your query and lasts for 24 hours
6. Re-run the query
7. Note duration and bytes processed
8. Disable cache and re-run query

Setting up

1. Make sure you have the BigQuery console open.

Working with saved queries

2. Write a query to find orders on one day in 2018 (from roi-bq-demos.bq_demo.orders)
3. Save the query
4. Close all the editor tabs
5. Click on your saved query in the Explorer panel
6. Run the query again

Setting up

1. Make sure you have the BigQuery console open
2. Open the Cloud Shell

Creating the time travel table

3. In a new browser tab, open https://github.com/roitraining/gcp-demos/blob/master/bigquery/time_travel.sh
4. Review the contents of the file
   

5. Copy the contents of the file and paste them into your Cloud Shell terminal window.

Running queries against the current, fully populated table

6. In a new browser tab, open https://github.com/roitraining/gcp-demos/blob/master/bigquery/time_travel_example.sql
7. Run the -- view up-to-date table query. This should show you the total number of orders per month, with values for Jan. and Feb.

Running a time travel query, finding results from specific time

8. Copy the -- view table with only initial load query from the SQL file in Github and paste it into your BigQuery editor.
9. Replace target with the time travel target value that was output in your Cloud Shell window. It should look like this: 1654541783
10. Run the query. This should show you the total number of orders per month, but from the table as it looked after its initial load when only January data was stored.

Restore a previous version of the table to a new table

11. Copy the -- create restoration table query from the SQL file in Github and paste it into your BigQuery editor.
12. Replace target with the time travel target value that was output in your Cloud Shell window. It should look like this: 1654541783
13. Run the query. Once the table has been created, check out the Details and Preview data. Verify that only January orders exist.
    

Setting up

1. Make sure you have the BigQuery console open.
2. Make sure you have the bigquery-public-data.noaa_gsod pinned.
3. In the Explorer pane, expand bigquery-public-data.noaa_gsod.

Working with sharded tables

4. Write a query that finds all the entries for stn 038110 in 1929
5. How would you write a query that finds all the 03110 entries for 1929, 1930, 1931?

Working with wildcard table queries

6. Review https://cloud.google.com/bigquery/docs/querying-wildcard-tables
7. Write a query that finds all the 038110 entries for 1929-1939 using a wildcard table
8. If you wanted to write a query to find 038110 entries in ALL the tables in the dataset?

Setting up

1. Make sure you have the BigQuery console open.
2. In a new browser tab, open https://github.com/roitraining/gcp-demos/blob/master/bigquery/external_hive_example.sql
3. In another new browser tab, open https://console.cloud.google.com/storage/browser/jwd-gcp-demos/orders_partitioned

Create an external table

4. In the BigQuery UI's Explorer pane, select your dataset
5. From three-dot menu, select create table
6. In Create table from, select Google Cloud Storage
7. In Select file... enter jwd-gcp-demos/orders_partitioned/*
8. In File format select parquet
9. Check Source data partitioning
10. In Select Source... enter gs://jwd-gcp-demos/orders_partitioned/
11. In Table, enter ext_part
12. In Table type select External table
13. In Schema, select Auto detect
14. Click CREATE TABLE
15. Check your table details

Query the external table

16. Run the query the external table query

17. Run the query external table with where clause query;

18. Run the query external table on partition query;

Setting up

1. Make sure you have the BigQuery console open.
2. In a separate browser tab, open https://github.com/roitraining/gcp-demos/blob/master/bigquery/udf_examples.sql

Messy numbers

3. Look at the messy_text table in the roi-bq-demos.bq_demo dataset; check out the data in the preview tab.
4. Run the trim strings query. What does it do? How does it do it?
5. Review and run the create udf query
6. Select the tidy_string function in the routines section of the Explorer pane; review the details.
7. Run the query with query with SQL UDF query

Getting numbers from text

8. Review the number_string table in the roi-bq-demos.bq_demo dataset; check out the data in the preview tab.
9. Review and run the create javascript udf query
10. Review the get_numbers UDF in the routines section
11. Run the query with javascript udf query
12. Look at documentation for stored procedures: https://cloud.google.com/bigquery/docs/procedures

Setting up

1. Make sure you have the BigQuery console open.
2. In a new browser tab, open https://github.com/roitraining/gcp-demos/blob/master/bigquery/schema-demo/norm_query.sql
3. Copy the first query into the BQ query editor

Try out query with joins

4. Run the query and review the Job Information and Execution Details

Setting up

1. Make sure you have the BigQuery console open.
2. Explore the denorm table in the project shared with you by your instructor

3. In a new browser tab, open https://github.com/roitraining/gcp-demos/blob/master/bigquery/schema-demo/denorm_query.sql
4. Copy the query into the BQ query editor
5. Replace <project-id> with the instructor's project name

Try out query with joins

6. Run the query and review the Job Information and Execution Details

Setting up

1. Make sure you have the BigQuery console open.
2. In a new browser window, open https://github.com/roitraining/gcp-demos/blob/master/bigquery/arrays_examples.sql

Populating Arrays

3. Read and run the populate arrays explicitly query, and review the results
4. Read and run the populate arrays using array_agg query, and review the results

Array lengths

5. In the Explorer pane, drill down to bigquery-public-data.github_repos.commits.

6. Read and run the report array length query, and review the results.
7. Write a query to find all the rows where the length of the difference array is equal to five (hint, you can use the find by array length query from the git file).

UNNEST

8. Review and run the select basic array from array query. Review the results.
9. Click on the JSON tab in the Query results area, and note the structure of the results. There's an array with a single row object. That object has a single column, which is a four-element array.
10. Review and run the select table from array query. Review the results.
11. Click on the JSON tab in the Query results area, and note the structure of the results. There are four rows, each with one column which is a scalar value. Unnest flattens the array into a table.
12. Review and run the calculate average of array query.

13. Review and run the basic correlated cross join query. The CTE at the top is just creating an initial arrays table with two rows and two columns. Review the standard results output and the JSON output.

14. Review and run the comma correlated cross join query.

15. It turns out, that if you're doing a correlated cross join, you don't even need to explicitly do the UNNEST, it's done for you implicitly. Review and run the comma implicit unnest query.

Querying on array contents

16. Review and run the -- find row where num_array contains 2 - take 1 query. Review the results.

17. Edit the query, modifying the CTE so that the first row in the arrays table is [2, 2, 3, 4]. Re-run the query and note the results.

18. Edit the query to search for rows that have an 8 in the array. Run the query and review the results? Are they correct?
19. Run the -- find row where num_array contains 2 - take 2 query.

20. Run the -- find row where num_array contains 2 - take 3 query.

All three of these queries return the same results, but there are differences in performance on large datasets. Let's explore that.

21. Run each of the find commits that... queries, noting the time taken for each to run.

Setting up

1. Make sure you have the BigQuery console open.
2. View the nested_once table in your instructor's project

3. In a new browser window, open https://github.com/roitraining/gcp-demos/blob/master/bigquery/schema-demo/nested_queries.sql

Querying the nested table

4. Read and run the find sales/zip for march from nested_once table query (replace with the instructor's project ID)

Setting up

1. Make sure you have the BigQuery console open.
2. Review the table_nested_partitioned table in your instructor's project

3. In a new browser window, open https://github.com/roitraining/gcp-demos/blob/master/bigquery/schema-demo/nested_queries.sql

Querying the partitioned table

4. Read and run the find sales/zip for march from nested/partitioned table query (replace with the instructor's project name)

Setting up

1. Make sure you have the BigQuery console open.
2. Review the table_nested_partitioned_clustered table in your instructor's project

3. In a new browser window, open https://github.com/roitraining/gcp-demos/blob/master/bigquery/schema-demo/nested_queries.sql

Querying the partitioned table

4. Find the total sales for the 8754 zip code by running the find sales for 6 months in 8754 from nested query (replace with the instructor's project name). Note the duration and data processed.
5. Run the find for 6 months in 8754 from nested/partitioned query (replace with the instructor's project name) and note the duration and data processed.
6. Run the find for 6 months in 8754 from nested/partitioned/clustered query (replace with the instructor's project name) and note the duration and data processed.

Setting up

1. Make sure you have the BigQuery console open.

Querying the partitioned table

2. Write and run a query to find all the March 2018 orders from the instructor's nested/repeated table, projecting all the columns except the customer email and phone number, and storing the results in a derived table in your class dataset.
3. Write and run a query to sum sales for AK customers orders in March 2018, using the full nested/repeated table. Note the query duration and bytes processed.
4. Write and run a query to sum sales for AK customers orders in March 2018, using the derived table. Note the query duration and bytes processed.

5. Schedule the query to run daily at 12.01am, overwriting the previous contents of the derived table.

Setting up

1. Make sure you have the BigQuery console open.
2. Review the roi-bq-demos.bq_demo.order_mv materialized view
   

3. In a new browser window, open https://github.com/roitraining/gcp-demos/blob/master/bigquery/mv_example.sql

Querying the materialized view

4. Run the -- query against original tables... query.

Querying the original tables

5. Run the -- query against original tables... query.

Setting up

1. Make sure you have the BigQuery console open.
2. In a new browser window, open https://github.com/roitraining/gcp-demos/blob/master/bigquery/approx_example.sql

Run exact and approx queries side-by-side

3. Run the -- First query - exact query in one editor tab. This query will find the number of unique article titles in the 106B row wikipedia table
4. Open a 2nd editor tab, and run the -- Second query - approx query. This will approximate the number of unique article titles in the 106B row wikipedia table.

Compare results

5. Calculate the % difference in query execution time between the two queries
6. Calculate the % difference in reported number of unique titles