The best way to learn something is 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. We have 18 Do-It-Now activities for BigQuery concepts.
2. These activities are all prefixed with BigQuery.
3. Many of these activities will cost money

Sample data

4. Many of the activities use data in the roi-bq-demos.bq_demo dataset
5. 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.
6. 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 Qwiklabs and start the Data to Insights Lab.
2. Using the provided credentials, open the Google Cloud Console, and navigate to the BigQuery UI.
3. In a separate browser tab, open https://github.com/roitraining/gcp-demos/blob/master/bq-demos/wiki_queries.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. Expand your project. If working in Qwiklabs, note that there are no resources as yet

Starring projects

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 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 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/bq-do-it-nows/views.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.
2. In a separate browser tab, open https://github.com/roitraining/gcp-demos/blob/master/bq-do-it-nows/udfs.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.

Taking the tour

2. Notice that you can have multiple tabs open
3. Write a query to find all CA customers from roi-bq-demos.bq_demo. Use tab to autocomplete things like SQL keywords and table references.
4. Add a comment at the top of the query using either # or – and the beginning of comment. Try toggling the comment using CMD-/
5. Copy/paste the contents of https://github.com/roitraining/gcp-demos/blob/master/bq-do-it-nows/udfs.sql into an editor tab
6. Select OR REPLACE
7. Press CTRL/CMD-D several times to multi select
8. Hit backspace or delete to change all the lines
9. Click the shortcut button to get a list of shortcuts
10. Press F1 (or right-click in editor tab and choose show command palette) to see more options (including making the font bigger!)

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 link for 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/bq-do-it-nows/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/bq-do-it-nows/time_travel.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 it's 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. One 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/bq-do-it-nows/bq_hive.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 new browser tab, open https://github.com/roitraining/gcp-demos/blob/master/bq-schema-demo/norm_query.sql
3. Copy the query into the BQ query editor
4. Replace all instances of <project-id> with roi-bq-demos (or whatever project your instructor directs you to use)

Try out query with joins

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

Setting up

1. Make sure you have the BigQuery console open.
2. View 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/bq-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/bq-do-it-nows/arrays.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/bq-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 name)

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/bq-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/bq-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/bq-do-it-nows/mv.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/bq-do-it-nows/approx.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

The following activities cover a range of relevant Data Engineering topics. Enjoy!

1. Overview

This activity is intended to illustrate a variety of techniques, including:

Managing and using PubSub with Python

• Creating topics and subscriptions
• Creating bytestring message bodies
• Sending messages

Various BEAM tricks when processing streams of data

• Decoding bytestring message bodies
• Using one branch for writing each event into a BigQuery table
• Using a second branch for aggregating rows into windows and writing windows to BigQuery
• Writing nested/repeated data from BEAM to BigQuery
• Reading the end of window time

2. Setting up

1. Open the Google Cloud console
2. Activate Cloud Shell
3. Click the button to open Cloud Shell in a new browser window
4. Clone the gcp-demos repository into Cloud Shell with the following command:

git clone https://github.com/roitraining/gcp-demos.git
cd gcp-demos/dflow-bq-stream-python

5. Run the setup.sh script, providing the name of the service account you want the demo code to use. For example (with demo-sa as the service account name):

. ./setup.sh demo-sa

6. Take a few minutes to review and understand the setup script. The diagram below indicates what's happening in the script.

3. Starting the pipeline

7. Make sure that you are in the dflow-bq-stream-python directory in your Cloud Shell window.
8. Deploy the Dataflow job with the follow command (you'll review the code in a minute):

python3 process_events.py \
--runner DataflowRunner \
--region us-central1 \
--project $PROJECT_ID \
--staging_location gs://$PROJECT_ID-dflow-demo/ \
--temp_location gs://$PROJECT_ID-dflow-demo/

4. Starting the event stream

9. Run the following command to start sending messages:

python3 send_events.py \
    --project_id=$PROJECT_ID

5. Understanding the code

10. In a new browser tab, open https://github.com/roitraining/gcp-demos/blob/master/bq-do-it-nows/dflow-bq-stream-python/send_events.py
11. Take a few minutes to review and understand the code. The diagram below highlights some key features:

12. Open https://github.com/roitraining/gcp-demos/blob/master/bq-do-it-nows/dflow-bq-stream-python/process_events.py
13. Take a few minutes to review and understand the code. The diagrams below highlight some key features

7. Check out the results

14. In the console, go to Dataflow and click on the running job.
15. Click on the top node of the pipeline diagram. Wait until you see metrics showing the number of received messages on the right-hand side (it takes a while for the cluster to spin up and start processing messages).
16. Explore the pipeline and the execution metrics in Dataflow.
17. In the console, go to BigQuery and explore your new dflow_demo dataset. Check out the DETAILS and PREVIEW section for both of the tables in that dataset. Note that the nested table has one row per window, with an array of structs, each struct representing one message for that window. Note also that the DETAILS section shows the rows in in the streaming buffer and not the BigQuery storage service.

8. Clean up

18. Stop the Dataflow job via the console
19. Stop the sending of messages by closing the Cloud Shell window
20. Delete the PubSub topic and subscription
21. Delete the BigQuery dataset