Logs to BigQuery: Analyse a Month of Logs with SQL
What you build: a log sink from Cloud Logging to BigQuery and four SQL reports on a month of web traffic, errors and slow pages, in your own Google Cloud account. Every step tested.

✍️ Translate this blog into Hindi, Tamil or Telugu
In this lab you send a month of web server logs into Google Cloud, route them into BigQuery with a log sink, and answer four questions with SQL: which pages get the traffic, which page fails, which page is slow, and how errors move week by week. You finish by deleting everything.
Who it is for: learners who have seen Cloud Logging and know basic SQL, and want to turn logs into reports. Time: about 1 hour. Cost: free tier (Cloud Logging gives 50 GB of logs a month at no charge, BigQuery gives 1 TB of query reading and 10 GB of storage a month; this lab uses well under 1 MB).
Every command below was run for real by the CareerByteCode Team on a fresh Google Cloud project, and every output and screenshot is the real result we got, including one surprise we met on the way (it is in the lab, with the fix). We ran the commands from a terminal with the Google Cloud CLI (gcloud, bq, curl and python3); Cloud Shell in your browser has the same tools ready.
Part A: logs to BigQuery in plain English
Cloud Logging collects the log lines of everything in your project. It is great for finding one line ("what happened at 10:42?") but it is not built for adding up a month of lines. BigQuery is built exactly for that. A log sink connects the two.

Five words you will use in this lab:
- Log entry: one line in the log, with a time, a severity (INFO, ERROR) and a payload. Ours is JSON:
path,status,latency_ms,country. - Log sink: a rule of two parts: a filter (which lines) and a destination (where to copy them). Real-life example: a mail room rule "every invoice goes to the finance tray".
- Writer identity: the service account the sink uses to write into BigQuery. It needs a role on your project or dataset, like a courier who needs a key to the finance room.
- Partitioned table: the sink creates one table split by day of the log time, so a query for one day reads only that day.
- Latency percentile (p95): 95 percent of requests were faster than this number. It shows the slow tail that an average hides.
Important: a sink copies only lines that arrive after it exists and can write. It never goes back and copies older lines. Keep that in mind; you will see it in Step 6.
Before you start
- A Google Cloud account (the free trial works) and a project with billing linked. Create one at https://console.cloud.google.com/projectcreate if you do not have one.
- Open Cloud Shell (the
>_icon at the top right of the Google Cloud console). It already hasgcloud,bq,curlandpython3. - Save your project id in a variable. Replace the text in quotes with your own project id:
export PROJECT_ID="your-project-id"- You need the Owner role on the project (or Logging Admin plus BigQuery Admin plus Project IAM Admin) to create the sink and grant its role.
Part B: the lab
Step 1: turn on the APIs and make a dataset
Why: the sink needs a BigQuery dataset to write into. We call it cbc_logs.
gcloud config set project $PROJECT_ID
gcloud services enable logging.googleapis.com bigquery.googleapis.com
bq --location=US mk --dataset --description "CareerByteCode logs lab" $PROJECT_ID:cbc_logs
bq lsUpdated property [core/project].
Operation "operations/acat.p2-PROJECT_NUMBER-aa85ee62-752b-418e-9575-9b6b3fa87b86" finished successfully.
Dataset 'your-project-id:cbc_logs' successfully created.
datasetId
-----------
cbc_logsStep 2: create the log sink
Why: this is the heart of the lab. The destination is the dataset; the filter keeps only our web log, named cbc-web. --use-partitioned-tables makes one table split by day instead of one new table per day.
gcloud logging sinks create cbc-web-to-bq \
bigquery.googleapis.com/projects/$PROJECT_ID/datasets/cbc_logs \
--log-filter="logName=\"projects/$PROJECT_ID/logs/cbc-web\"" \
--use-partitioned-tables
gcloud logging sinks describe cbc-web-to-bq --format="yaml(name,destination,filter,bigqueryOptions)"Created [https://logging.googleapis.com/v2/projects/your-project-id/sinks/cbc-web-to-bq].
Please remember to grant `serviceAccount:service-PROJECT_NUMBER@gcp-sa-logging.iam.gserviceaccount.com` the BigQuery Data Editor role on the dataset.
More information about sinks can be found at https://cloud.google.com/logging/docs/export/configure_export
bigqueryOptions:
usePartitionedTables: true
usesTimestampColumnPartitioning: true
destination: bigquery.googleapis.com/projects/your-project-id/datasets/cbc_logs
filter: logName="projects/your-project-id/logs/cbc-web"
name: cbc-web-to-bqThe message "Please remember to grant ... the BigQuery Data Editor role" is the sink telling you it has no right to write yet. That is the next step.
Step 3: let the sink write into the dataset
Why: the sink writes as its own service account (its writer identity). We give that account the BigQuery Data Editor role. We print only the start of its name; it contains your project number.
SINK_SA=$(gcloud logging sinks describe cbc-web-to-bq --format="value(writerIdentity)")
echo "sink writes as: ${SINK_SA%%@*}@..."
gcloud projects add-iam-policy-binding $PROJECT_ID --member="$SINK_SA" --role="roles/bigquery.dataEditor" --condition=None --format="value(etag)" > /dev/null && echo "role bigquery.dataEditor granted"sink writes as: serviceAccount:service-PROJECT_NUMBER@...
Updated IAM policy for project [your-project-id].
role bigquery.dataEditor granted
Step 4: a script that makes a month of web logs
Why: we need realistic logs with real dates. This small Python script makes 600 web requests spread over the last 30 days: five pages, mostly fast, a checkout page that fails now and then, and a search API that is sometimes slow. random.seed(7) means you get the same 600 lines we got.
cat > make_logs.py <<EOF
# Makes 600 web access log entries spread over the last 30 days (fixed seed = same data every time)
import json, random, datetime, os
random.seed(7)
pid = os.environ["PROJECT_ID"]
now = datetime.datetime.now(datetime.timezone.utc)
paths = ["/", "/courses", "/login", "/checkout", "/api/search"]
entries = []
for i in range(600):
t = now - datetime.timedelta(minutes=random.randint(5, 30 * 24 * 60))
path = random.choices(paths, weights=[40, 25, 15, 8, 12])[0]
status = 200
if path == "/checkout" and random.random() < 0.18: status = 500
elif random.random() < 0.04: status = 404
latency = round(random.uniform(20, 180) + (900 if path == "/api/search" and random.random() < 0.3 else 0), 1)
entries.append({"timestamp": t.isoformat(), "severity": "ERROR" if status >= 500 else "INFO",
"jsonPayload": {"path": path, "status": status, "latency_ms": latency,
"country": random.choice(["IN", "IN", "IN", "US", "BE", "AE"])}})
body = {"logName": f"projects/{pid}/logs/cbc-web", "resource": {"type": "global"}, "entries": entries}
json.dump(body, open("logs.json", "w"))
print("entries:", len(entries), "oldest:", min(e["timestamp"] for e in entries)[:10], "newest:", max(e["timestamp"] for e in entries)[:10])
EOF
python3 make_logs.pyentries: 600 oldest: 2026-10-19 newest: 2026-11-18Step 5: send the logs to Cloud Logging
Why: gcloud logging write can only write one line with the time "now". To send 600 lines with their own past times in one call, we use the Cloud Logging API directly with curl. gcloud auth print-access-token gives a short-lived token for your own sign-in; it is never stored in a file. {} is the API saying "done, no errors".
curl -s -X POST -H "Authorization: Bearer $(gcloud auth print-access-token)" \
-H "Content-Type: application/json" -d @logs.json \
https://logging.googleapis.com/v2/entries:write && echo "write call done"
gcloud logging read "logName=\"projects/$PROJECT_ID/logs/cbc-web\"" --freshness=31d --limit=3 \
--format="table(timestamp, severity, jsonPayload.path, jsonPayload.status, jsonPayload.latency_ms)"Then read three lines back to see that Cloud Logging has them:
gcloud logging read "logName=\"projects/$PROJECT_ID/logs/cbc-web\"" --freshness=31d --limit=3 \
--format="table(timestamp, severity, jsonPayload.path, jsonPayload.status, jsonPayload.latency_ms)"TIMESTAMP SEVERITY PATH STATUS LATENCY_MS
2026-11-18T02:59:14.352197Z INFO /courses 200 103.7
2026-11-17T23:32:14.352197Z INFO /api/search 200 67.8
2026-11-17T22:38:14.352197Z INFO / 200 131.3
Step 6: check BigQuery (and the surprise we met)
We also sent one test line with gcloud logging write while we waited, to see whether the sink worked at all:
gcloud logging write cbc-web '{"path":"/probe","status":200,"latency_ms":1.0,"country":"IN"}' --payload-type=jsonCreated log entry.Then we asked BigQuery what it had:
bq query --use_legacy_sql=false "SELECT table_name FROM cbc_logs.INFORMATION_SCHEMA.TABLES"
bq query --use_legacy_sql=false "SELECT COUNT(*) AS rows_in_bq, MIN(DATE(timestamp)) AS oldest, MAX(DATE(timestamp)) AS newest FROM cbc_logs.cbc_web"+------------+
| table_name |
+------------+
| cbc_web |
+------------+
+------------+------------+------------+
| rows_in_bq | oldest | newest |
+------------+------------+------------+
| 1 | 2026-11-18 | 2026-11-18 |
+------------+------------+------------+
What happened: we sent the 600 lines about 20 seconds after granting the role in Step 3. A new role can take a minute or two to spread, and the sink skipped lines it could not write then. The test line, sent minutes later, arrived. A sink never copies old lines later, so those 600 lines stay only in Cloud Logging. Fix: send the lines again now that the sink can write. (In real life, wait 2 minutes after the grant before you rely on a new sink, and check with one test line, as we did.)
curl -s -X POST -H "Authorization: Bearer $(gcloud auth print-access-token)" \
-H "Content-Type: application/json" -d @logs.json \
https://logging.googleapis.com/v2/entries:write && echo "write call done"{}
write call donebq query --use_legacy_sql=false "SELECT COUNT(*) AS rows_in_bq, MIN(DATE(timestamp)) AS oldest, MAX(DATE(timestamp)) AS newest FROM cbc_logs.cbc_web"
bq show --schema cbc_logs.cbc_web | python3 -c "import json,sys; print([c[\"name\"] for c in json.load(sys.stdin)])"+------------+------------+------------+
| rows_in_bq | oldest | newest |
+------------+------------+------------+
| 601 | 2026-10-19 | 2026-11-18 |
+------------+------------+------------+
['logName', 'resource', 'textPayload', 'jsonPayload', 'timestamp', 'receiveTimestamp', 'severity', 'insertId', 'httpRequest', 'operation', 'trace', 'spanId', 'traceSampled', 'sourceLocation', 'split', 'errorGroups', 'apphub', 'apphubDestination', 'apphubSource']
Now BigQuery has 601 rows (the 600 lines plus the test line), from 19 October to 18 November. The second line of the output lists the columns of the table the sink made: the same fields as a log entry, and our JSON lives under jsonPayload.
POC: four SQL reports on a month of logs
Report 1: traffic per page
Why: which pages matter most? SUM(COUNT(*)) OVER () is the total of all rows, so we can show each page as a percent.
bq query --use_legacy_sql=false \
"SELECT jsonPayload.path AS page, COUNT(*) AS requests,
ROUND(100 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct
FROM cbc_logs.cbc_web GROUP BY page ORDER BY requests DESC"+-------------+----------+------+
| page | requests | pct |
+-------------+----------+------+
| / | 235 | 39.1 |
| /courses | 162 | 27.0 |
| /login | 81 | 13.5 |
| /api/search | 71 | 11.8 |
| /checkout | 51 | 8.5 |
| /probe | 1 | 0.2 |
+-------------+----------+------+Report 2: error rate per page
Why: a page with 9 errors out of 51 is much worse than a page with 9 out of 5000. COUNTIF counts only the rows that match.
bq query --use_legacy_sql=false \
"SELECT jsonPayload.path AS page, COUNT(*) AS requests,
COUNTIF(jsonPayload.status >= 500) AS errors_5xx,
COUNTIF(jsonPayload.status = 404) AS not_found,
ROUND(100 * COUNTIF(jsonPayload.status >= 500) / COUNT(*), 1) AS error_pct
FROM cbc_logs.cbc_web GROUP BY page ORDER BY error_pct DESC"📝⭐ Recommended by CareerByteCode · Certification examGoogle Analytics Certification (GA4) PracticeTake the examTake the exam →+-------------+----------+------------+-----------+-----------+
| page | requests | errors_5xx | not_found | error_pct |
+-------------+----------+------------+-----------+-----------+
| /checkout | 51 | 9 | 3 | 17.6 |
| /probe | 1 | 0 | 0 | 0.0 |
| / | 235 | 0 | 12 | 0.0 |
| /login | 81 | 0 | 1 | 0.0 |
| /api/search | 71 | 0 | 1 | 0.0 |
| /courses | 162 | 0 | 3 | 0.0 |
+-------------+----------+------------+-----------+-----------+
Finding: /checkout fails on 17.6 percent of requests (9 of 51) while every other page has 0 server errors. That is the page to fix first, and it is also the page that loses sales.
Report 3: slow pages
Why: an average hides slow requests. APPROX_QUANTILES(x, 100)[OFFSET(95)] gives the 95th percentile.
bq query --use_legacy_sql=false \
"SELECT jsonPayload.path AS page,
ROUND(APPROX_QUANTILES(jsonPayload.latency_ms, 100)[OFFSET(50)]) AS p50_ms,
ROUND(APPROX_QUANTILES(jsonPayload.latency_ms, 100)[OFFSET(95)]) AS p95_ms,
ROUND(MAX(jsonPayload.latency_ms)) AS max_ms
FROM cbc_logs.cbc_web GROUP BY page ORDER BY p95_ms DESC"+-------------+--------+--------+--------+
| page | p50_ms | p95_ms | max_ms |
+-------------+--------+--------+--------+
| /api/search | 138.0 | 1074.0 | 1078.0 |
| / | 100.0 | 176.0 | 180.0 |
| /checkout | 101.0 | 174.0 | 180.0 |
| /login | 94.0 | 168.0 | 180.0 |
| /courses | 83.0 | 167.0 | 180.0 |
| /probe | 1.0 | 1.0 | 1.0 |
+-------------+--------+--------+--------+Report 4: errors per week
Why: is it getting better or worse? DATE_TRUNC(..., WEEK(MONDAY)) puts each line into the week it belongs to.
bq query --use_legacy_sql=false \
"SELECT DATE_TRUNC(DATE(timestamp), WEEK(MONDAY)) AS week_starting,
COUNT(*) AS requests, COUNTIF(severity = \"ERROR\") AS errors
FROM cbc_logs.cbc_web GROUP BY week_starting ORDER BY week_starting"+---------------+----------+--------+
| week_starting | requests | errors |
+---------------+----------+--------+
| 2026-10-19 | 148 | 5 |
| 2026-10-26 | 134 | 0 |
| 2026-11-02 | 139 | 1 |
| 2026-11-09 | 134 | 2 |
| 2026-11-16 | 46 | 1 |
+---------------+----------+--------+
Finding: /api/search has a normal median (138 ms) but a p95 of 1074 ms: about 1 in 3 searches is very slow. The average alone would not have shown it. The weekly table shows errors in every week, so the checkout problem is not new. (The last week is short because the month ends today.)
Break it and fix it
JSON numbers in the log become numbers in BigQuery. A common mistake is to compare them with text, like status = "500":
bq query --use_legacy_sql=false \
"SELECT COUNT(*) AS errors FROM cbc_logs.cbc_web WHERE jsonPayload.status = \"500\""Error in query string: Error processing job 'your-project-id:bqjob_r5658d18380d041a_000001a1ed452709_1': No matching signature
for operator = for argument types: FLOAT64, STRING
Signature: T1 = T1
Unable to find common supertype for templated argument <T1>
Input types for <T1>: {DOUBLE, STRING} at [1:55]The error says it cannot compare FLOAT64 with STRING. Fix: look at the column types, then compare a number with a number.
bq show --schema cbc_logs.cbc_web | python3 -c "import json,sys; [print(f[\"name\"], f[\"type\"]) for c in json.load(sys.stdin) if c[\"name\"]==\"jsonPayload\" for f in c[\"fields\"]]"
bq query --use_legacy_sql=false \
"SELECT COUNT(*) AS errors FROM cbc_logs.cbc_web WHERE jsonPayload.status = 500"path STRING
status FLOAT
latency_ms FLOAT
country STRING
+--------+
| errors |
+--------+
| 9 |
+--------+
Note that status became FLOAT, not INTEGER: JSON has only one kind of number, so BigQuery stores it as a decimal number. status = 500 still works.
Clean up (delete everything)
Why: a sink keeps copying lines for ever, and stored rows add up. We remove the role, the sink, the dataset and the log itself.
SINK_SA=$(gcloud logging sinks describe cbc-web-to-bq --format="value(writerIdentity)")
gcloud projects remove-iam-policy-binding $PROJECT_ID --member="$SINK_SA" --role="roles/bigquery.dataEditor" --condition=None --format="value(etag)" > /dev/null && echo "role removed"
gcloud logging sinks delete cbc-web-to-bq --quiet
bq rm -r -f -d $PROJECT_ID:cbc_logs
gcloud logging logs delete cbc-web --quiet
echo "lab sinks left: $(gcloud logging sinks list --format="value(name)" | grep -vc "^_")"
echo "datasets left: $(bq ls --format=json | grep -c datasetId)"Updated IAM policy for project [your-project-id].
role removed
Deleted [https://logging.googleapis.com/v2/projects/your-project-id/sinks/cbc-web-to-bq].
Deleted [cbc-web].
lab sinks left: 0
datasets left: 0
lab sinks left: 0 and datasets left: 0 are your proof. The _Default and _Required sinks are Google's own and stay.
FAQ
Why not just search the logs in Logs Explorer? Logs Explorer is perfect for one problem at one time. For "error rate per page over a month" or a weekly trend, SQL in BigQuery is faster and repeatable.
Can I send old logs that are already in Cloud Logging? Not with a sink: it only copies new lines. Logging has a "copy logs" feature for logs in a log bucket to Cloud Storage, and you can load those files into BigQuery. The other option is a log bucket with Log Analytics, which you can query with SQL directly.
What is Log Analytics? A Cloud Logging feature that lets you run SQL on a log bucket without a sink. It is simpler for a quick look; a sink to your own dataset gives you full BigQuery control (joins with other tables, scheduled queries, longer storage).
How long do logs stay? In Cloud Logging, 30 days by default in the _Default bucket. In BigQuery, as long as you keep the table, or set a partition expiry to delete old days for you.
Does the sink cost anything? Routing logs is free. You pay for BigQuery storage above 10 GB and for queries above 1 TB a month.
Why did my sink not write anything? Usually the role is missing on the writer identity, the filter does not match, or the role had not spread yet. Send one test line and check after 2 minutes.
Troubleshooting
| You see | Why | Fix |
|---|---|---|
Not found: Table ...cbc_logs.cbc_web | No line has reached the sink yet | Wait 1 to 2 minutes; send one test line with gcloud logging write |
| The table exists but has fewer rows than you sent | Lines were sent before the writer role took effect | Wait 2 minutes after the grant, then send the lines again |
No matching signature for operator = ... FLOAT64, STRING | A number column compared with text | Remove the quotes: jsonPayload.status = 500 |
Unrecognized name: path | JSON fields live under jsonPayload | Use jsonPayload.path |
Permission denied when creating the sink | Your account lacks Logging Admin | Ask the project owner for roles/logging.configWriter or Owner |
bq ls crashes on a Windows PC with OSError: [Errno 22] | A bug in the bq tool with table expiry times on Windows (we met it) | Use Cloud Shell, or list tables with SELECT table_name FROM cbc_logs.INFORMATION_SCHEMA.TABLES |
What next
- Read our tested lab on logs themselves: Cloud Logging Hands-on: Find Any Error in Seconds.
- More hands-on posts from the community: ByteBlog.
- Practice with guided projects in ByteLabs and see the full courses.
Prepared by the CareerByteCode Team from our tested lab, with AI help for the write-up.
⭐ Keep learning with CareerByteCode
Hand-picked to go deeper on this topicGet CareerByteCode Team's next blog by email
One email a week when they publish. Add the weekly Best of ByteBlog too. No spam, unsubscribe with one click.
We email you once to confirm. By subscribing you agree to the privacy policy.
More from CareerByteCode Team
CareerByteCode TeamOpenTelemetry for Beginners: Traces, Metrics and Logs
What you build: an instrumented app sending traces to Cloud Trace, in your own Google Cloud account. Every step, command and screenshot explained for intermediates.
#beginners
CareerByteCode TeamMonitor Linux Servers with Node Exporter and Ops Agent
What you build: CPU, memory and disk alerts for VMs, in your own Google Cloud account. Every step, command and screenshot explained for beginners.
#beginners
CareerByteCode TeamCloud Logging Hands-On: Find Any Error in Seconds
What you build: log queries, log-based metrics and sinks, in your own Google Cloud account. Every step, command and screenshot explained for beginners.
#bigquery
CareerByteCode TeamBigQuery ML: Train a Model with Just SQL
What you build: a churn prediction model written in SQL with BigQuery ML, scored and used for predictions, in your own Google Cloud account. Every step tested.
💬 Comments (0)
Top · NewestSign in to comment