The best way to control Claude Code, Codex and more from any computer or phone
Mirrored from https://github.com/Camo67/t3code.git
Synced Nov 21, 04:45 UTC
| 1 | --- |
| 2 | title: BigQuery |
| 3 | description: Create BigQuery datasets and tables, stream rows in, and run parameterized queries from Cloud Run with ReadTable and WriteTable bindings. |
| 4 | --- |
| 5 | |
| 6 | BigQuery is Google's data warehouse. Declare a dataset and a table, |
| 7 | stream rows into it from one service, and query it from another. |
| 8 | |
| 9 | ## Create a dataset and a table |
| 10 | |
| 11 | A table needs its dataset's id, so build it inside an `Effect.gen`: |
| 12 | |
| 13 | ```typescript |
| 14 | // src/resources.ts |
| 15 | import * as GCP from "@alchemy.run/gcp"; |
| 16 | import * as Effect from "effect/Effect"; |
| 17 | |
| 18 | export const Analytics = GCP.BigQuery.Dataset("Analytics", { |
| 19 | forceDestroy: true, |
| 20 | }); |
| 21 | |
| 22 | export const Events = Effect.gen(function* () { |
| 23 | const dataset = yield* Analytics; |
| 24 | return yield* GCP.BigQuery.Table("Events", { |
| 25 | datasetId: dataset.datasetId, |
| 26 | tableId: "events", |
| 27 | schema: [ |
| 28 | { name: "id", type: "STRING", mode: "REQUIRED" }, |
| 29 | { name: "type", type: "STRING", mode: "REQUIRED" }, |
| 30 | { name: "occurredAt", type: "TIMESTAMP", mode: "REQUIRED" }, |
| 31 | { name: "payload", type: "JSON" }, |
| 32 | ], |
| 33 | }); |
| 34 | }); |
| 35 | ``` |
| 36 | |
| 37 | `forceDestroy` deletes the tables with the dataset. A `JSON` column |
| 38 | lets producers add fields without changing the schema. |
| 39 | |
| 40 | ## Write and query from a service |
| 41 | |
| 42 | ```typescript |
| 43 | // src/Api.ts |
| 44 | import * as GCP from "@alchemy.run/gcp"; |
| 45 | import * as Effect from "effect/Effect"; |
| 46 | import { HttpServerRequest } from "effect/http/HttpServerRequest"; |
| 47 | import * as HttpServerResponse from "effect/http/HttpServerResponse"; |
| 48 | import { Events } from "./resources.ts"; |
| 49 | |
| 50 | export default class Api extends GCP.Function<Api>()( |
| 51 | "Api", |
| 52 | { main: import.meta.url, invokerIamDisabled: true }, |
| 53 | Effect.gen(function* () { |
| 54 | const table = yield* Events; |
| 55 | const writer = yield* GCP.BigQuery.WriteTable(table); |
| 56 | const reader = yield* GCP.BigQuery.ReadTable(table); |
| 57 | const tableId = yield* table.tableId; |
| 58 | |
| 59 | return { |
| 60 | fetch: Effect.gen(function* () { |
| 61 | const request = yield* HttpServerRequest; |
| 62 | |
| 63 | if (request.method === "POST") { |
| 64 | const id = crypto.randomUUID(); |
| 65 | yield* writer.insert( |
| 66 | [{ id, type: "signup", occurredAt: new Date(), payload: "{}" }], |
| 67 | { insertIds: [id] }, |
| 68 | ); |
| 69 | return yield* HttpServerResponse.json({ id }, { status: 202 }); |
| 70 | } |
| 71 | |
| 72 | const rows = yield* reader.query( |
| 73 | `SELECT COUNT(*) AS n FROM \`${yield* tableId}\` WHERE type = @type`, |
| 74 | { type: "signup" }, |
| 75 | ); |
| 76 | return yield* HttpServerResponse.json({ count: Number(rows[0]?.n ?? 0) }); |
| 77 | }).pipe(Effect.orDie), |
| 78 | }; |
| 79 | }).pipe( |
| 80 | Effect.provide([GCP.BigQuery.WriteTableHttp, GCP.BigQuery.ReadTableHttp]), |
| 81 | ), |
| 82 | ) {} |
| 83 | ``` |
| 84 | |
| 85 | `insert` takes plain row objects. `insertIds` drops a repeated row |
| 86 | within BigQuery's dedup window, so a retried insert doesn't |
| 87 | double-count. |
| 88 | |
| 89 | `query` runs SQL with named parameters, so input never lands in the |
| 90 | SQL text. Table names resolve against the bound table's dataset, and |
| 91 | rows come back as plain JavaScript. |
| 92 | |
| 93 | `yield* table.tableId` in the constructor gives you an accessor. |
| 94 | `yield*` it again inside the handler to read the value. |
| 95 | |
| 96 | ## What each binding grants |
| 97 | |
| 98 | | Binding | Roles | |
| 99 | | --- | --- | |
| 100 | | `ReadTable` | `bigquery.dataViewer` on the table, `bigquery.jobUser` on the project | |
| 101 | | `WriteTable` | `bigquery.dataEditor` on the table | |
| 102 | | `ReadWriteTable` | both of the above | |
| 103 | |
| 104 | Running a query is a project permission in BigQuery, so `jobUser` |
| 105 | can't be narrower. |
| 106 | |
| 107 | ## Example |
| 108 | |
| 109 | [`examples/gcp-event-pipeline`](https://github.com/alchemy-run/alchemy/tree/main/examples/gcp-event-pipeline) |
| 110 | publishes events to Pub/Sub and drains them into BigQuery with a |
| 111 | Cloud Run job. |
| 112 | [`examples/gcp-cron`](https://github.com/alchemy-run/alchemy/tree/main/examples/gcp-cron) |
| 113 | records scheduled runs in a table. |
| 114 | |
| 115 | ## Where next |
| 116 | |
| 117 | - [Pub/Sub](/gcp/messaging/pubsub) — the ingestion pipeline that feeds |
| 118 | BigQuery. |
| 119 | - [BigQuery reference](/providers/gcp/bigquery#table) — every prop and |
| 120 | attribute. |