Module 2: Homework – BigQuery Data Structures

🪜 Step-by-Step Instructions for Students

0. One-time Setup

1. Sign in with your Google account, then open bigquery.cloud.google.com.
   
2. If prompted, click “Start Sandbox” to enable the always-free BigQuery tier (no billing info needed).
   

1️⃣ Create a Dataset

1. In the left sidebar, hover over your project name → click ⋮ → Create dataset.
   
2. Dataset ID: mod2_homework
   
3. Leave location and encryption defaults → Create.
   
4. Screenshot #1: The dataset appears in the Resources pane.
   

2️⃣ Structured Data – products.csv

1. Click the mod2_homework dataset → Create table.
   
2. Source: Upload → Select file → choose products.csv.
   
3. File format: CSV
   
4. Auto-detect: Off
   
5. Schema: Enter columns exactly:
   

6. Table name: stg_products → Create table.
   
7. After it loads, open the Schema tab then Preview tab.
   
8. Screenshot #2: Schema tab showing correct data types.
   

3️⃣ Semi-Structured Data – transactions.json

1. Create table again.
   
2. Source: Upload → select transactions.json.
   
3. File format: JSON
   
4. Tick Auto-detect schema.
   
5. Table name: stg_transactions → Create table.
   
6. When done, open Schema; click the ▶︎ arrows to expand the nested items array.
   
7. Screenshot #3: Expanded schema tree with items.quantity visible.
   

4️⃣ Unstructured Data – Image “Object Table”

1. In the Google Cloud Console left nav, open Storage → Create bucket
   
   • Bucket name example: ae-mod2-<your-initials> (must be globally unique).
     
   • Region: any. Leave defaults → Create.
     
2. Drag all three product_*.jpg files into a folder inside the bucket (e.g., product_photos/).
   
3. Back in BigQuery, click Create table.
   
4. Table type: Object table (beta)
   
5. Source: Cloud Storage URI → gs://YOUR_BUCKET/product_photos/*
   
6. Table name: obj_product_photos → Create table.
   
7. Open Preview—you should see file name, size, and mime-type rows.
   
8. Screenshot #4: Preview listing the three image files.

Submit Your Work

Upload all four screenshots to the assignment page and click Submit.