CareerByteCode
ByteBlogby CareerByteCode

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.

Logs to BigQuery: Analyse a Month of Logs with SQL

✍️ 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.

🧪⭐ Recommended by CareerByteCode · ByteLabs project bundleCloud and DevOps Realtime Starterโ‚น499Build the projects →

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.

How logs reach BigQuery
Your app writes logs, a sink copies the chosen lines into BigQuery, SQL reads them

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

  1. 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.
  2. Open Cloud Shell (the >_ icon at the top right of the Google Cloud console). It already has gcloud, bq, curl and python3.
  3. Save your project id in a variable. Replace the text in quotes with your own project id:
export PROJECT_ID="your-project-id"
  1. 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 ls
Updated 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_logs

Step 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-bq

The 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
Dataset, sink and role
Steps 1 to 3: the dataset, the sink with its filter, and the writer role

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.py
entries: 600 oldest: 2026-10-19 newest: 2026-11-18

Step 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
Make and send the logs
Steps 4 and 5: 600 lines from 19 Oct to 18 Nov, readable in Cloud Logging

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=json
Created 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 |
+------------+------------+------------+
Only one row arrived
The sink made the table cbc_web, but only the test line arrived, not the 600 lines

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 done
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"
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']
All lines arrived
Sent again: 601 rows (600 web lines + the test line) from 19 Oct to 18 Nov

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 |
+-------------+----------+------------+-----------+-----------+
Traffic and errors
Reports 1 and 2: the home page gets 39 percent of traffic; checkout fails 17.6 percent of the time

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 |
+---------------+----------+--------+
Slow pages and weekly trend
Reports 3 and 4: search has a p95 of 1074 ms; errors per week over the month

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 |
+--------+

![Break it and fix it](images/08-term-break-and-fix.png "status is a FLOAT column, so status = 500 works and status = \"500\" fails")

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
Clean up
0 lab sinks and 0 datasets left

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 seeWhyFix
Not found: Table ...cbc_logs.cbc_webNo line has reached the sink yetWait 1 to 2 minutes; send one test line with gcloud logging write
The table exists but has fewer rows than you sentLines were sent before the writer role took effectWait 2 minutes after the grant, then send the lines again
No matching signature for operator = ... FLOAT64, STRINGA number column compared with textRemove the quotes: jsonPayload.status = 500
Unrecognized name: pathJSON fields live under jsonPayloadUse jsonPayload.path
Permission denied when creating the sinkYour account lacks Logging AdminAsk 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

Prepared by the CareerByteCode Team from our tested lab, with AI help for the write-up.

Read next →OpenTelemetry for Beginners: Traces, Metrics and Logs⏱ 12 min

⭐ Keep learning with CareerByteCode

Hand-picked to go deeper on this topic
News, guides and updates from the CareerByteCode team
🏅 Contributor writer

The CareerByteCode team writes about what is new on careerbytecode.in: courses, labs, exams, events and the community, which is free to join for influencers and content creators in tech.

Get 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.

💬 Comments (0)

Top · Newest

Sign in to comment

More from CareerByteCode Team

🧪⭐ Recommended by CareerByteCode · ByteLabs project bundleCloud and DevOps Realtime Starterโ‚น499Build the projects →