The best way to control Claude Code, Codex and more from any computer or phone

main
mdx 120 lines 3.85 KB
Raw
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.