GCP.BigQuery reference

Dataset

Source: src/GCP/BigQuery/Dataset.ts

A Google BigQuery dataset — a container for tables, views, and routines.

Changing datasetId or location replaces the dataset. Labels, description, expiration, collation, encryption, and access update in place. Destroy fails if the dataset still contains tables unless forceDestroy is set.

Dataset: Creating a Dataset

Generated name

const dataset = yield* GCP.BigQuery.Dataset("Analytics", {});

Explicit id, location, and labels

const dataset = yield* GCP.BigQuery.Dataset("Analytics", {
  datasetId: "app_analytics",
  location: "US-CENTRAL1",
  labels: { env: "prod" },
  description: "application events",
  forceDestroy: true,
});

Dataset: Querying

const query = yield* GCP.BigQuery.Query(dataset);
const result = yield* query({ query: "SELECT 1 AS n" });

InsertAll

Source: src/GCP/BigQuery/InsertAll.ts

Runtime binding for BigQuery tabledata.insertAll.

Bind this operation to a Table in a Function/Action init phase. Provide InsertAllHttp.

InsertAll: Inserting Rows

const insertAll = yield* GCP.BigQuery.InsertAll(events);
yield* insertAll({
  body: { rows: [{ json: { id: "1" } }] },
});

InsertAllHttp

Source: src/GCP/BigQuery/InsertAllHttp.ts Kind: Layer · Provides: GCP.BigQuery.InsertAll

HTTP implementation of InsertAll.

Job

Source: src/GCP/BigQuery/Job.ts

A Google BigQuery job — a one-shot query, load, copy, or extract.

Jobs are immutable after insert. Changing jobId, location, labels, or the job configuration replaces the job. BigQuery job ids cannot be reused even after metadata delete, so replacement always creates a new id then deletes the previous generation. Destroy cancels a running job, then deletes its metadata.

Job: Creating a Job

GoogleSQL query

const job = yield* GCP.BigQuery.Job("Count", {
  query: { query: "SELECT 1 AS n", useLegacySql: false },
  labels: { env: "prod" },
});

Query into a destination table

const dataset = yield* GCP.BigQuery.Dataset("Analytics", {
  forceDestroy: true,
});
const table = yield* GCP.BigQuery.Table("Results", {
  datasetId: dataset.datasetId,
  schema: [{ name: "n", type: "INTEGER" }],
});
const load = yield* GCP.BigQuery.Job("Seed", {
  query: {
    query: "SELECT 1 AS n",
    useLegacySql: false,
    destinationTable: {
      projectId: table.project,
      datasetId: table.datasetId,
      tableId: table.tableId,
    },
    writeDisposition: "WRITE_TRUNCATE",
  },
});

ListTabledata

Source: src/GCP/BigQuery/ListTabledata.ts

Runtime binding for BigQuery tabledata.list.

Bind this operation to a Table in a Function/Action init phase. Provide ListTabledataHttp.

ListTabledata: Listing Rows

const listRows = yield* GCP.BigQuery.ListTabledata(events);
const page = yield* listRows({ maxResults: 100 });

ListTabledataHttp

Source: src/GCP/BigQuery/ListTabledataHttp.ts Kind: Layer · Provides: GCP.BigQuery.ListTabledata

HTTP implementation of ListTabledata.

Query

Source: src/GCP/BigQuery/Query.ts

Runtime binding for BigQuery jobs.query.

Bind this operation to a Dataset in a Function/Action init phase. Provide QueryHttp. Unqualified table names resolve against the bound dataset. GoogleSQL is the default (useLegacySql: false).

Query: Querying

const query = yield* GCP.BigQuery.Query(dataset);
const result = yield* query({ query: "SELECT 1 AS n" });

QueryHttp

Source: src/GCP/BigQuery/QueryHttp.ts Kind: Layer · Provides: GCP.BigQuery.Query

HTTP implementation of Query.

ReadTable

Source: src/GCP/BigQuery/ReadTable.ts

Read access to a BigQuery Table: list rows and run query jobs. Grants roles/bigquery.dataViewer on the table and roles/bigquery.jobUser on the project (job creation is project-only).

Rows are decoded with the schema: INTEGER → number (bigint beyond the safe range), FLOAT → number, BOOLEAN → boolean, TIMESTAMP → Date, BYTES → Uint8Array, JSON → parsed value, RECORD → object, REPEATED → array; NUMERIC, DATE, DATETIME, TIME, and GEOGRAPHY stay strings.

ReadTable: Reading rows

const events = yield* GCP.BigQuery.ReadTable(table);
const { rows, nextPageToken } = yield* events.list({ maxResults: 100 });
// …provided with Effect.provide(GCP.BigQuery.ReadTableHttp)

ReadTable: Querying

const events = yield* GCP.BigQuery.ReadTable(table);
const tableId = yield* table.tableId;
return Effect.gen(function* () {
  const rows = yield* events.query(
    `SELECT id, score FROM \`${yield* tableId}\` WHERE score > @min`,
    { min: 10 },
  );
});

ReadTableHttp

Source: src/GCP/BigQuery/ReadTableHttp.ts Kind: Layer · Provides: GCP.BigQuery.ReadTable

HTTP implementation of ReadTable over the BigQuery REST API.

ReadWriteTable

Source: src/GCP/BigQuery/ReadWriteTable.ts

Read and write access to a BigQuery Table. Grants roles/bigquery.dataEditor on the table and roles/bigquery.jobUser on the project (job creation is project-only).

ReadWriteTable: Reading and writing

const events = yield* GCP.BigQuery.ReadWriteTable(table);
const tableId = yield* table.tableId;
return Effect.gen(function* () {
  yield* events.insert([{ id: "a", score: 3 }]);
  const [total] = yield* events.query(
    `SELECT SUM(score) AS total FROM \`${yield* tableId}\``,
  );
});
// …provided with Effect.provide(GCP.BigQuery.ReadWriteTableHttp)

ReadWriteTableHttp

Source: src/GCP/BigQuery/ReadWriteTableHttp.ts Kind: Layer · Provides: GCP.BigQuery.ReadWriteTable

HTTP implementation of ReadWriteTable over the BigQuery REST API.

Routine

Source: src/GCP/BigQuery/Routine.ts

A Google BigQuery routine — a user-defined function, table-valued function, aggregate function, or stored procedure.

Routines have no labels; Alchemy stamps ownership into the description ([alchemy alchemy-stack=… alchemy-stage=… alchemy-id=…]) so list / pnpm nuke:gcp can find them inside datasets that carry alchemy-* labels. datasetId, routineId, and routineType are immutable (changing them replaces the routine). Body, arguments, description, language, and language-specific options update in place via a full-resource replace.

Routine: Creating a Routine

SQL scalar function

const dataset = yield* GCP.BigQuery.Dataset("Analytics", {
  forceDestroy: true,
});
const add = yield* GCP.BigQuery.Routine("Add", {
  datasetId: dataset.datasetId,
  routineType: "SCALAR_FUNCTION",
  language: "SQL",
  arguments: [
    { name: "x", dataType: { typeKind: "INT64" } },
    { name: "y", dataType: { typeKind: "INT64" } },
  ],
  returnType: { typeKind: "INT64" },
  definitionBody: "x + y",
});

SQL stored procedure

const proc = yield* GCP.BigQuery.Routine("Seed", {
  datasetId: dataset.datasetId,
  routineType: "PROCEDURE",
  language: "SQL",
  definitionBody: "SELECT 1",
});

JavaScript UDF

const multiply = yield* GCP.BigQuery.Routine("Multiply", {
  datasetId: dataset.datasetId,
  routineType: "SCALAR_FUNCTION",
  language: "JAVASCRIPT",
  determinismLevel: "DETERMINISTIC",
  arguments: [
    { name: "x", dataType: { typeKind: "FLOAT64" } },
    { name: "y", dataType: { typeKind: "FLOAT64" } },
  ],
  returnType: { typeKind: "FLOAT64" },
  definitionBody: "return x * y;",
});

Routine: Calling a Routine

const query = yield* GCP.BigQuery.Query(dataset);
const result = yield* query({ query: "SELECT Add(1, 2) AS n" });

RowAccessPolicy

Source: src/GCP/BigQuery/RowAccessPolicy.ts

A Google BigQuery row-access policy — a filter predicate plus IAM members that together hide rows from unauthorized readers.

Row-access policies have no labels, and Alchemy never edits the filter predicate: list / pnpm nuke:gcp return the policies on tables that carry alchemy-* labels, and read reports a policy with an explicit id that Alchemy has no state for as unowned. datasetId, tableId, and policyId are immutable (changing them replaces the policy). filterPredicate updates in place. grantees is create-only.

RowAccessPolicy: Creating a Row Access Policy

Filter non-null rows

const dataset = yield* GCP.BigQuery.Dataset("Analytics", {
  forceDestroy: true,
});
const table = yield* GCP.BigQuery.Table("Events", {
  datasetId: dataset.datasetId,
  schema: [{ name: "nullable_field", type: "STRING" }],
});
const policy = yield* GCP.BigQuery.RowAccessPolicy("Visible", {
  datasetId: dataset.datasetId,
  tableId: table.tableId,
  filterPredicate: "nullable_field IS NOT NULL",
});

Grant a domain

const policy = yield* GCP.BigQuery.RowAccessPolicy("Domain", {
  datasetId: dataset.datasetId,
  tableId: table.tableId,
  filterPredicate: "TRUE",
  grantees: ["domain:example.com"],
});

Table

Source: src/GCP/BigQuery/Table.ts

A Google BigQuery table.

Native tables only — views, materialized views, snapshots, and external tables are not managed by this resource. datasetId, tableId, time partitioning type/field, and kmsKeyName are immutable (changing them replaces the table). Schema, labels, description, clustering, and partition expiration update in place.

Table: Creating a Table

Generated name in an existing dataset

const events = yield* GCP.BigQuery.Table("Events", {
  datasetId: "analytics",
  schema: [
    { name: "id", type: "STRING" },
    { name: "created_at", type: "TIMESTAMP" },
  ],
});

Explicit id, labels, and partitioning

const events = yield* GCP.BigQuery.Table("Events", {
  datasetId: "analytics",
  tableId: "order_events",
  labels: { env: "prod" },
  schema: [
    { name: "id", type: "STRING" },
    { name: "created_at", type: "TIMESTAMP" },
  ],
  timePartitioning: { type: "DAY", field: "created_at" },
  clustering: { fields: ["id"] },
});

Table: Inserting Rows

const insertAll = yield* GCP.BigQuery.InsertAll(events);
yield* insertAll({
  body: { rows: [{ json: { id: "1", created_at: "2024-01-01T00:00:00Z" } }] },
});

WriteTable

Source: src/GCP/BigQuery/WriteTable.ts

Write access to a BigQuery Table: streaming insert. Grants roles/bigquery.dataEditor on the table only.

WriteTable: Writing rows

const events = yield* GCP.BigQuery.WriteTable(table);
yield* events.insert(
  [{ id: "a", score: 1, at: new Date() }],
  { insertIds: ["a"] },
);
// …provided with Effect.provide(GCP.BigQuery.WriteTableHttp)

WriteTableHttp

Source: src/GCP/BigQuery/WriteTableHttp.ts Kind: Layer · Provides: GCP.BigQuery.WriteTable

HTTP implementation of WriteTable over the BigQuery REST API.