Each activity on the left is self-contained: it illustrates one BigQuery concept, brings its own setup, and can be done on its own. Work them in any order, or do just the ones you need.

Cost

Every activity shows its cost under the title — 🟢 No cost, 💰 Low cost, or 💸 Higher cost.

Sample data

• Many of the activities use data in the roi-bq-demos.bq_demo dataset
• 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.
• 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.

💸Higher costscans up to 100B rows

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.

🟢No cost

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

💰Low cost

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.
    

💰Low cost

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

💸Higher costjoins the full 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/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

💸Higher costscans large tables

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

💸Higher costscans large tables

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)

💰Low cost

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.

💰Low cost

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)

💸Higher costscans large tables

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.

💰Low cost

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.

💸Higher costjoins the full 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/execution_details_example.sql

Run a heavyweight query

3. Copy the -- heavyweight query query into the BigQuery editor and run it. It will take a couple of minutes; while it runs, don't touch anything, just watch the elapsed time tick.
4. When the query completes, click on Job Information and note the Duration (elapsed time) and Bytes processed.
5. Click on Execution Details and note the Slot time consumed, Bytes shuffled, and Bytes spilled to disk.
6. Write down your five numbers: elapsed time, slot time consumed, bytes processed, bytes shuffled, bytes spilled to disk.

Work vs. wait

7. Divide your slot time consumed by your elapsed time (make sure both are in seconds). The result is roughly the number of slots that were working on your query at once.

Run a lightweight query

8. Copy the -- lightweight query query into the BigQuery editor and run it.
9. Collect the same five numbers from Job Information and Execution Details.

💸Higher costforces a large shuffle

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/shuffle_spill_example.sql

Watch nothing move

3. Copy the -- scan and filter query query into the BigQuery editor and run it.
4. Open Execution Details and note the Bytes shuffled.

Move a little

5. Copy the -- aggregation query query into the BigQuery editor and run it.
6. Check Bytes shuffled again. It's bigger than zero this time, but still small.

Move a lot

7. If you completed the five numbers activity, don't pay for that query twice: click View all jobs at the bottom of the page.
8. Select the most recent job that shows a Slot contention insight, click View in editor, and skip to step 10.
9. If you don't have that job, copy the -- join query query into the BigQuery editor and run it. It will take a couple of minutes.
10. Open Execution Details and note the Bytes shuffled. Compare it to the first two queries.

Find the guilty stage

11. With the join query's results open, click on Execution Graph.
12. Click through the stages and find the stage that wrote the most data to shuffle. Which table was it reading?

The check-engine light

13. For each of your queries, check Bytes spilled to disk in Execution Details. The first two queries should show 0 B. The join query should show terabytes spilled.

💰Low costno bytes billed, but real slot time

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/insights_example.sql

Trip the alarm

3. Review the -- sales joined to price history query query. It builds two tables with GENERATE_ARRAY (so it bills zero bytes): 100,000 sales of 100 products, and a price history holding every price each product has ever had. It joins them on prod_code to compute total revenue.
4. Copy the query into the BigQuery editor and run it.
5. Look at the result: 100,000,000 rows came out of a 100,000 row sales table, and total revenue is in the billions for products that cost under $100.
6. Click on Execution Graph and find the flagged stage. Click it and review the notice: This stage has join(s) that produce significantly more rows than it consumes. The rows from one join side match multiple rows from the other. The console has diagnosed the fan-out for you; BigQuery calls this insight a high-cardinality join.

Fix what the insight named

7. Review and run the -- sales joined to current price query query. Before joining, it reduces the price history to one row per product: the price currently in effect.
8. Check the result: 100,000 rows (one per sale), a sane revenue total, and no flag in the Execution Graph.

Read the diagnosis on a real query

9. If you completed the shuffle and spill activity: click View all jobs at the bottom of the page, select the join query job with the Slot contention insight, and click View in editor.
10. Open its Execution Graph and review the performance insights shown for that job.

🟢No costqueries job metadata only

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/information_schema_examples.sql

See the whole project's bill

3. Copy the -- find the queries worth optimizing query into the BigQuery editor and run it. Every job's stats land in INFORMATION_SCHEMA.JOBS in real time, with 180 days of history.
4. Review the results. The jobs you ran in the earlier activities should be near the top.
5. Edit the query to sort by total_bytes_processed instead, and re-run it. Does the same job top both lists?

Hunt flagged queries at project scale

6. Copy the -- find jobs the console flagged query into the BigQuery editor and run it. If you completed the performance insights activity, your high-cardinality join job appears, with the insight recorded in the job's metadata.

Explore further

7. The github file has more examples: top spenders by user, table compression ratios, full table scans, and tables nobody has queried in 90 days. Pick one, read it, and run it.

🟢No costestimates only — do not run the SELECT * 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/bigquery/select_star_example.sql

Read the meter before you run

3. Copy the -- select star query query into the BigQuery editor, but do not run it.
4. Look at the estimate in the lower left of the editor: This query will process 2.44 TB when run. That is the price of touching every column.
5. Replace it with the -- two columns query query. The estimate drops to about 13 GB: roughly 190x cheaper for the same table and the same rows.

The WHERE column counts too

6. Replace the query with the -- filtered two columns query query. The estimate goes up slightly, even though the query returns fewer rows.

Nearly all columns, without the blob

7. Replace the query with the -- select star except query query and check the estimate: about 15.5 GB for every column except one.

🟢No costestimates only, nothing to run

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/pruning_example.sql

Partition pruning

3. Paste the -- unpartitioned table query query into the editor and read the estimate: about 112 GB to count one month's customers.
4. Replace it with the -- partitioned table query query. Same filter, but the table is partitioned by order_date: about 9.3 GB. Eleven months of partitions were never opened.
5. Replace it with the -- broken partition filter query query. It asks for exactly the same rows, but the estimate is back to 112 GB.

Cluster pruning

6. Paste the -- unclustered table query query and read the estimate: about 70 GB.
7. Replace it with the -- clustered table query query. Same data, but the table is clustered on cust_state: about 1.4 GB. Within the storage, blocks that can't contain AK are skipped.
8. Replace it with the -- broken cluster filter query query: UPPER(cust_state) turns block skipping off, and the estimate returns to about 70 GB.

💰Low cost

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/join_types_example.sql

The join you get for free

3. Review the -- broadcast join query query: 3.75B line items joined to a 10,000 row product table.
4. Copy the query into the BigQuery editor and run it. It will take a minute or so.
5. Open Execution Graph and look at the shape of the plan. One data source feeds its own Input stage (the 10K records edge tells you it's the product table). The other data source (the line item table) has no input stage at all: its arrow runs straight into the Join+ stage, because the 3.75B row scan happens inside the join. That is also why Join+ holds nearly all the query's slot time.
6. Click the join stage and review the step list. The + in Join+ means extra steps were fused into the stage: this one READs, JOINs, and pre-AGGREGATEs in a single pass, which is why it outputs millions of partial results rather than billions of joined rows.
7. Find the JOIN step in that list: it reads EACH WITH ALL.
8. Note the Bytes shuffled in Execution Details.

When both sides are big

9. Review the -- hash join query query: 375M orders joined to 7.5M customers.
10. Copy the query into the BigQuery editor and run it.
11. Open Execution Graph: this time both tables get input stages feeding the join.
12. Click the join stage and find the JOIN step: it reads EACH WITH EACH.
13. Compare Bytes shuffled to the broadcast query. The hash join shuffled dozens of times more, on far less data.

💰Low cost

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/aggregate_first_example.sql

Join at raw grain

3. Review the -- join at raw grain query query. The question is "orders per state", and the join exists only to look up each customer's state. (This is the same query the broadcast and hash activity used for its hash join; if you just ran it there, you can reuse that job's stats via View all jobs.)
4. Run it, then note the Slot time consumed and Bytes shuffled in Execution Details.
5. In the Execution Graph, click the join stage and note how many rows arrived at it: all 375M order rows, plus the customers.

Aggregate to the question's grain, then join

6. Review the -- aggregate first query query. Before the join, it collapses the orders to one row per customer: COUNT(*) grouped by cust_id. The count then survives the join as SUM(n).
7. Run it and compare the results to the first query: identical.
8. Compare Slot time consumed and Bytes shuffled: roughly 4x less slot time and 9x less shuffle. Check the join stage in the Execution Graph: about 15M rows arrived instead of 382M.

💰Low cost

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/constraints_example.sql

A join that cannot change the answer

3. Review the -- join before declaring anything query query. It asks for order numbers on a single day. Look at the SELECT list: every column comes from the order table. The customer table is joined, but nothing from it is selected.
4. Run the query and note the Slot time consumed in Execution Details.
5. Open the Execution Graph. There is a Join stage, and about 12M customer rows arrive at it, purely to confirm that each order has a customer.

Tell the optimizer what you know

6. Review the -- declare the primary key query and -- declare the foreign key query queries. These declare that cust_id uniquely identifies a customer, and that every order's cust_id points at one.
7. Note the NOT ENFORCED on both. It is required, and it means exactly what it says: BigQuery will never check the claim.
8. Run both statements against your own copies of the tables in the class dataset, replacing your_project. They finish almost instantly and bill nothing: no data is read or rewritten, only metadata is updated.
9. Run the -- check what you declared query query to see the two constraints listed.

Run the same query again

10. Run the -- join before declaring anything query query a second time, unchanged, against your declared tables.
11. Compare the results to the first run: identical rows.
12. Open the Execution Graph and compare it to the first run. The Join stage is gone entirely, and the customer table is never read.
13. Compare Slot time consumed: roughly a quarter less, on a query whose text you never touched.

What a false promise costs you

14. Review the -- build a table pair that breaks the promise query query. Employee 3 sits in department 99, which does not exist. Run it to create the two small tables.
15. Run the -- the honest answer query query. It returns employees 1 and 2. Employee 3 is dropped, correctly, because an inner join has nothing to match it to.
16. Run the -- declare the false foreign key query query. It succeeds. Nothing was validated, and no warning was given.
17. Run the -- the honest answer query query again, completely unchanged.
18. Read the results: employee 3 is now included, in a join to a department that does not exist.

💰Low cost

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

💸Higher costscans large tables

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

🟢No costbatch loads are free

Setting up

1. Make sure you have the BigQuery console open.
2. Make sure your project has a dataset named class (you created one in the exploring datasets activity).

Loading with the console

3. In the Explorer panel, click the three-dot menu next to your class dataset and select Create table.
4. Set Create table from to Google Cloud Storage.
5. In the file field, enter jwd-gcp-demos/ingest_demo/daily/usage_2026-07-20.csv (no gs:// prefix).
6. Confirm the File format is CSV.
7. Name the table usage.
8. In the Schema section, check Auto detect.
9. Click Create table and wait for the load to finish.
10. Click the new usage table and open its Details tab: Number of rows is 1,000,000.
11. Check the Schema tab: minutes came out INTEGER and event_date came out DATE. Autodetect chose well here.

Appending with the CLI

12. Tuesday's file has arrived. Open the Cloud Shell and load it into the same table:

bq load --autodetect --source_format=CSV \
  class.usage \
  gs://jwd-gcp-demos/ingest_demo/daily/usage_2026-07-21.csv

13. Back in the console, refresh the Details tab: 2,000,000 rows. Loads append by default; --replace would have truncated the table first.
14. Click View all jobs and look at the two most recent jobs: both are Load jobs, identical in kind. The console form and the bq load command are two front ends for the same thing.

🟢No costbatch loads are free

Setting up

1. Make sure you have the BigQuery console open.
2. Open the Cloud Shell.
3. Make sure your project has a dataset named class (you created one in the exploring datasets activity).
4. In a new browser tab, open https://github.com/roitraining/gcp-demos/blob/master/bigquery/autodetect_demo.sh
5. Copy the contents of the file and paste them into your Cloud Shell terminal. This creates two daily usage drops from a vendor, plus a schema file.
6. Look at the first few rows of Monday's file:

head -3 daily_usage_mon.csv

7. The vendor writes N/A in the minutes column for meetings that never started. Find those rows in both files:

grep -n "N/A" daily_usage_mon.csv daily_usage_tue.csv

The loud failure

8. Load Monday's file, letting autodetect choose the types:

bq load --autodetect --source_format=CSV class.daily_usage daily_usage_mon.csv

9. The load fails. Read the error: Unable to parse. Autodetect sampled the start of the file, saw only numbers in minutes, chose INTEGER, and then row 90,000 said N/A.

The silent failure

10. Now load Tuesday's file the same way:

bq load --autodetect --source_format=CSV class.daily_usage daily_usage_tue.csv

11. This load succeeds. Check what autodetect decided:

bq show --schema class.daily_usage

12. Note the type of the minutes column: STRING. Tuesday's file is small enough that the N/A fell inside autodetect's sample, so it picked the only type that fits everything.
13. In the BigQuery editor, try to use the data:

SELECT SUM(minutes) AS total_minutes FROM class.daily_usage

14. The query fails: no version of SUM accepts a STRING.

Pin the schema, name the marker

15. Look at the schema file:

cat daily_usage_schema.json

16. Reload Monday's file, pinning the schema and telling BigQuery what the vendor means by N/A:

bq load --replace --source_format=CSV \
  --schema=daily_usage_schema.json \
  --skip_leading_rows=1 \
  --null_marker='N/A' \
  class.daily_usage daily_usage_mon.csv

17. Load Tuesday's file the same way, without --replace (loads append by default):

bq load --source_format=CSV \
  --schema=daily_usage_schema.json \
  --skip_leading_rows=1 \
  --null_marker='N/A' \
  class.daily_usage daily_usage_tue.csv

18. In the BigQuery editor, verify the loads:

SELECT
  COUNT(*) AS rows_loaded,
  COUNTIF(minutes IS NULL) AS null_minutes,
  SUM(minutes) AS total_minutes
FROM class.daily_usage

19. 101,000 rows loaded, exactly two NULL minutes (the meetings that never started), and SUM works.

🟢No costbatch loads are free

Setting up

1. Make sure you have the BigQuery console open.
2. Make sure your project has a dataset named class.
3. Open the Cloud Shell.

The same data, staged two ways

4. Sixty million rows of usage history sit in the demo bucket twice: once as a single gzipped CSV, once as 100 Parquet files. Look at both:

gcloud storage ls -l gs://jwd-gcp-demos/ingest_demo/big/

gcloud storage du -s --readable-sizes gs://jwd-gcp-demos/ingest_demo/big_parquet

5. The gzipped CSV is about 317 MiB; the Parquet files total about 829 MiB. Place your bet: which loads faster?

Load the gzipped CSV

6. Load the gzipped file, and keep an eye on the elapsed-seconds counter bq prints while it waits:

bq load --source_format=CSV \
  --skip_leading_rows=1 \
  --schema=meeting_id:STRING,user_id:STRING,minutes:INTEGER,event_date:DATE \
  class.usage_history_csv \
  gs://jwd-gcp-demos/ingest_demo/big/usage_big.csv.gz

7. Note how long it took: around two minutes.

Load the Parquet files

8. Now load the Parquet copy of the same data. No --schema this time; Parquet files carry their own:

bq load --source_format=PARQUET \
  class.usage_history_pq \
  'gs://jwd-gcp-demos/ingest_demo/big_parquet/usage_big-*.parquet'

9. Done in seconds, not minutes, despite being over twice the bytes.
10. Confirm both tables hold the same data:

bq show class.usage_history_csv

bq show class.usage_history_pq

11. Both show 60,000,000 in Total Rows, and the Parquet-loaded table got the right types (INTEGER, DATE) from the files themselves.

🟢No costbatch loads are free

Setting up

1. Make sure you have the BigQuery console open.
2. Make sure your project has a dataset named class.
3. In a new browser tab, open https://github.com/roitraining/gcp-demos/blob/master/bigquery/load_data_example.sql

The load job goes SQL

4. Review the -- load monday query query. It re-does the console load from the basic loading activity, as a SQL statement: the column list pins the schema the way --schema did, and the FROM FILES options mirror the bq load flags.
5. Run it in the query editor. It finishes in a few seconds and reports that it created the usage_sql table.
6. Review the -- load tuesday query query: no column list this time, because the table now exists. LOAD DATA INTO appends; LOAD DATA OVERWRITE would replace.
7. Run it.
8. Run the -- verify query query: each of the two dates has 1,000,000 meetings.

🟢No coststreaming bills per GiB; these rows are under the free tier

Setting up

1. Make sure you have the BigQuery console open.
2. Make sure your project has a dataset named class.
3. Open the Cloud Shell.
4. In a new browser tab, open https://github.com/roitraining/gcp-demos/blob/master/bigquery/storage_write_example.js

Set up the stream

5. Create the table the events will land in:

bq mk --table class.live_usage \
  meeting_id:STRING,user_id:STRING,minutes:INTEGER,event_ts:TIMESTAMP

6. Make a working directory and move into it:

mkdir ~/stream_demo && cd ~/stream_demo

7. Install the client library:

npm install @google-cloud/bigquery-storage

8. Download the demo script:

curl -sO https://raw.githubusercontent.com/roitraining/gcp-demos/master/bigquery/storage_write_example.js

9. Review the script in the browser tab you opened. It connects to the table's default stream, wraps the connection in a JSONWriter, then appends one JSON row per second for 30 seconds, as if meetings were ending live.

Watch rows arrive

10. Run the script:

node storage_write_example.js

11. While it prints appended once a second, switch to the BigQuery editor and run this a few times:

SELECT COUNT(*) AS so_far FROM class.live_usage

12. The count climbs while the script runs. A row is queryable about a second after the append; there is no waiting for a batch.
13. When the script finishes, look at the last few events:

SELECT * FROM class.live_usage ORDER BY event_ts DESC LIMIT 5

💸Higher costscans large tables

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.

💰Low cost

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;