Day 4: BigQuery for Analytics | Google Cloud in 5 Days

BigQuery Basics

BigQuery is Google's serverless data warehouse. You pay per query (first 1TB/month free). It can analyze terabytes in seconds using standard SQL.

bq CLI

# Create a dataset
bq mk --dataset my_project:my_dataset

# Load a CSV file
bq load \
  --source_format=CSV \
  --autodetect \
  my_dataset.my_table \
  gs://my-bucket/data.csv

# Run a query
bq query --use_legacy_sql=false \
  'SELECT name, COUNT(*) as count FROM my_dataset.my_table GROUP BY name ORDER BY count DESC LIMIT 10'

SQL in BigQuery Console

-- BigQuery supports standard SQL
SELECT
  DATE(created_at) as day,
  COUNT(*) as events,
  SUM(revenue) as total_revenue,
  AVG(revenue) as avg_revenue
FROM `my_project.analytics.events`
WHERE created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND status = 'completed'
GROUP BY day
ORDER BY day DESC

-- Window functions work
SELECT
  user_id,
  event_time,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time) as session_event
FROM `my_project.analytics.events`

💡

Partition your tables by date column when working with time-series data. Queries that filter by date then only scan the relevant partitions instead of the whole table — dramatically cheaper and faster: PARTITION BY DATE(created_at).

📝 Day 4 Exercise

Analyze a Public Dataset

O
p
e
n
B
i
g
Q
u
e
r
y
i
n
t
h
e
G
C
P
c
o
n
s
o
l
e
.
Q
u
e
r
y
a
p
u
b
l
i
c
d
a
t
a
s
e
t
(
B
i
g
Q
u
e
r
y
h
a
s
h
u
n
d
r
e
d
s
)
.
T
r
y
:
<
c
o
d
e
>
b
i
g
q
u
e
r
y
-
p
u
b
l
i
c
-
d
a
t
a
.
g
i
t
h
u
b
_
r
e
p
o
s
.
c
o
m
m
i
t
s
<
/
c
o
d
e
>
o
r
<
c
o
d
e
>
b
i
g
q
u
e
r
y
-
p
u
b
l
i
c
-
d
a
t
a
.
u
s
a
_
n
a
m
e
s
.
u
s
a
_
1
9
1
0
_
2
0
1
3
<
/
c
o
d
e
>
.
W
r
i
t
e
3
q
u
e
r
i
e
s
:
c
o
u
n
t
,
f
i
l
t
e
r
,
a
n
d
a
g
g
r
e
g
a
t
e
.

Day 4 Summary

BigQuery is serverless — no servers to manage. Pay per TB scanned (first 1TB/month free).
Standard SQL. Window functions, subqueries, CTEs, JOINs — it all works.
Partition by date for time-series data. Clustering further improves query performance.
Looker Studio connects directly to BigQuery for free visualization dashboards.

→

← Previous

Day 3: Storage: GCS and Cloud SQL

Day 5: AI APIs and Vertex AI

→