sailfork / t3code-github / .repos/ alchemy-effect/ packages/ integration-tests/ test/ GCP/ BigQuery/ Table.test.ts

import * as GCP from "@alchemy.run/gcp";
import { expect } from "@alchemy.run/test";
import * as Test from "@alchemy.run/test/Alchemy";
import * as bigquery from "@distilled.cloud/gcp/bigquery_v2";
import * as Output from "alchemy/Output";
import * as Effect from "effect/Effect";
import { MinimumLogLevel } from "effect/References";
import * as Result from "effect/Result";
import * as Schedule from "effect/Schedule";

const { test } = Test.make({ providers: GCP.providers() });

const logLevel = Effect.provideService(MinimumLogLevel, process.env.DEBUG ? "Debug" : "Info");

const waitUntilGone = (projectId: string, datasetId: string, tableId: string) =>
  bigquery
    .getTables({
      projectId,
      datasetId,
      tableId,
    })
    .pipe(
      Effect.as("found" as const),
      Effect.catchTag("NotFound", () => Effect.succeed("gone" as const)),
      Effect.repeat({
        schedule: Schedule.spaced("1 second"),
        until: (status) => status === "gone",
        times: 10,
      }),
    );

test.provider(
  "create, update, and delete a table",
  (stack) =>
    Effect.gen(function* () {
      yield* stack.destroy();

      const created = yield* stack.deploy(
        Effect.gen(function* () {
          const dataset = yield* GCP.BigQuery.Dataset("Analytics", {
            location: "US-CENTRAL1",
            forceDestroy: true,
          });
          return yield* GCP.BigQuery.Table("Events", {
            datasetId: dataset.datasetId,
            schema: [
              { name: "id", type: "STRING" },
              { name: "created_at", type: "TIMESTAMP" },
            ],
            labels: { env: "test" },
            description: "order events",
            timePartitioning: { type: "DAY", field: "created_at" },
            clustering: { fields: ["id"] },
          });
        }),
      );

      expect(created.tableId).toEqual(expect.any(String));
      expect(created.tableId).toMatch(/^[a-zA-Z0-9_]+$/);
      expect(created.datasetId).toEqual(expect.any(String));
      expect(created.project).toEqual(expect.any(String));
      expect(created.type).toEqual("TABLE");
      expect(created.labels).toMatchObject({ env: "test" });
      expect(created.description).toEqual("order events");
      expect(created.timePartitioning).toMatchObject({
        type: "DAY",
        field: "created_at",
      });
      expect(created.clustering).toEqual({ fields: ["id"] });
      expect(created.schema).toEqual(
        expect.arrayContaining([
          expect.objectContaining({ name: "id", type: "STRING" }),
          expect.objectContaining({ name: "created_at", type: "TIMESTAMP" }),
        ]),
      );

      const fetched = yield* bigquery.getTables({
        projectId: created.project,
        datasetId: created.datasetId,
        tableId: created.tableId,
        view: "FULL",
      });
      expect(fetched.tableReference?.tableId).toEqual(created.tableId);
      expect(fetched.labels?.env).toEqual("test");
      expect(fetched.labels?.["alchemy-id"]).toEqual(expect.any(String));
      expect(fetched.description).toEqual("order events");
      expect(fetched.timePartitioning?.type).toEqual("DAY");
      expect(fetched.timePartitioning?.field).toEqual("created_at");
      expect(fetched.clustering?.fields).toEqual(["id"]);

      const inserted = yield* bigquery.insertAllTabledata({
        projectId: created.project,
        datasetId: created.datasetId,
        tableId: created.tableId,
        body: {
          rows: [{ json: { id: "row-1", created_at: "2024-01-01T00:00:00Z" } }],
        },
      });
      expect(inserted.insertErrors ?? []).toEqual([]);

      const listed = yield* bigquery
        .listTabledata({
          projectId: created.project,
          datasetId: created.datasetId,
          tableId: created.tableId,
          maxResults: 10,
        })
        .pipe(
          Effect.repeat({
            schedule: Schedule.spaced("1 second"),
            until: (page) => (page.rows ?? []).length > 0,
            times: 10,
          }),
        );
      expect((listed.rows ?? []).length).toBeGreaterThan(0);

      const updated = yield* stack.deploy(
        Effect.gen(function* () {
          const dataset = yield* GCP.BigQuery.Dataset("Analytics", {
            datasetId: created.datasetId,
            location: "US-CENTRAL1",
            forceDestroy: true,
          });
          return yield* GCP.BigQuery.Table("Events", {
            datasetId: dataset.datasetId,
            tableId: created.tableId,
            schema: [
              { name: "id", type: "STRING" },
              { name: "created_at", type: "TIMESTAMP" },
              { name: "name", type: "STRING" },
            ],
            labels: { env: "prod", role: "events" },
            description: "order events v2",
            friendlyName: "Order Events",
            timePartitioning: {
              type: "DAY",
              field: "created_at",
              expirationMs: "2592000000",
            },
            clustering: { fields: ["id"] },
          });
        }),
      );

      expect(updated.name).toEqual(created.name);
      expect(updated.tableId).toEqual(created.tableId);
      expect(updated.labels).toMatchObject({ env: "prod", role: "events" });
      expect(updated.description).toEqual("order events v2");
      expect(updated.friendlyName).toEqual("Order Events");
      expect(updated.schema).toEqual(
        expect.arrayContaining([
          expect.objectContaining({ name: "id", type: "STRING" }),
          expect.objectContaining({ name: "created_at", type: "TIMESTAMP" }),
          expect.objectContaining({ name: "name", type: "STRING" }),
        ]),
      );
      expect(updated.timePartitioning).toMatchObject({
        type: "DAY",
        field: "created_at",
        expirationMs: "2592000000",
      });

      const fetchedUpdate = yield* bigquery.getTables({
        projectId: created.project,
        datasetId: created.datasetId,
        tableId: created.tableId,
        view: "FULL",
      });
      expect(fetchedUpdate.labels?.env).toEqual("prod");
      expect(fetchedUpdate.labels?.role).toEqual("events");
      expect(fetchedUpdate.description).toEqual("order events v2");
      expect(fetchedUpdate.friendlyName).toEqual("Order Events");
      expect(fetchedUpdate.timePartitioning?.expirationMs).toEqual("2592000000");
      expect((fetchedUpdate.schema?.fields ?? []).map((field) => field.name)).toEqual(
        expect.arrayContaining(["id", "created_at", "name"]),
      );

      yield* stack.destroy();

      const gone = yield* waitUntilGone(created.project, created.datasetId, created.tableId);
      expect(gone).toEqual("gone");
    }).pipe(logLevel),
  { tags: ["provider:gcp", "provider:gcp:bigquery", "live"], timeout: 90_000 },
);

const tableStatus = (projectId: string, datasetId: string, tableId: string) =>
  bigquery.getTables({ projectId, datasetId, tableId, view: "FULL" });

test.provider(
  "create, update, and replace a view",
  (stack) =>
    Effect.gen(function* () {
      yield* stack.destroy();

      const viewStack = (props: { datasetId?: string; query?: string }) =>
        Effect.gen(function* () {
          const dataset = yield* GCP.BigQuery.Dataset("Analytics", {
            datasetId: props.datasetId,
            location: "US-CENTRAL1",
            forceDestroy: true,
          });
          const events = yield* GCP.BigQuery.Table("Events", {
            datasetId: dataset.datasetId,
            schema: [
              { name: "id", type: "STRING" },
              { name: "n", type: "INT64" },
            ],
          });
          return yield* GCP.BigQuery.Table("EventIds", {
            datasetId: dataset.datasetId,
            ...(props.query === undefined
              ? { schema: [{ name: "id", type: "STRING" }] }
              : {
                  view: {
                    query: Output.interpolate`${props.query} FROM ${events.datasetId}.${events.tableId}`,
                  },
                }),
          });
        });

      const created = yield* stack.deploy(viewStack({ query: "SELECT id" }));
      expect(created.type).toEqual("VIEW");
      expect(created.view?.query).toMatch(/^SELECT id FROM /);
      expect(created.view?.useLegacySql).toEqual(false);

      const fetched = yield* tableStatus(created.project, created.datasetId, created.tableId);
      expect(fetched.type).toEqual("VIEW");
      expect(fetched.view?.useLegacySql).toEqual(false);
      expect((fetched.schema?.fields ?? []).map((field) => field.name)).toEqual(["id"]);

      const updated = yield* stack.deploy(
        viewStack({ datasetId: created.datasetId, query: "SELECT id, n" }),
      );
      expect(updated.tableId).toEqual(created.tableId);
      expect(updated.creationTime).toEqual(created.creationTime);
      expect(updated.view?.query).toMatch(/^SELECT id, n FROM /);

      const fetchedUpdate = yield* tableStatus(created.project, created.datasetId, created.tableId);
      expect(fetchedUpdate.creationTime).toEqual(created.creationTime);
      expect((fetchedUpdate.schema?.fields ?? []).map((field) => field.name)).toEqual(["id", "n"]);

      // A generated name is replaced create-before-delete, under a new name.
      const replaced = yield* stack.deploy(viewStack({ datasetId: created.datasetId }));
      expect(replaced.tableId).not.toEqual(created.tableId);
      expect(replaced.type).toEqual("TABLE");
      expect(replaced.view).toBeUndefined();

      const fetchedReplace = yield* tableStatus(
        replaced.project,
        replaced.datasetId,
        replaced.tableId,
      );
      expect(fetchedReplace.type).toEqual("TABLE");
      expect(fetchedReplace.view).toBeUndefined();

      const viewGone = yield* waitUntilGone(created.project, created.datasetId, created.tableId);
      expect(viewGone).toEqual("gone");

      yield* stack.destroy();

      const gone = yield* waitUntilGone(replaced.project, replaced.datasetId, replaced.tableId);
      expect(gone).toEqual("gone");
    }).pipe(logLevel),
  { tags: ["provider:gcp", "provider:gcp:bigquery", "live"], timeout: 120_000 },
);

test.provider(
  "create and update a hive-partitioned external table over Cloud Storage",
  (stack) =>
    Effect.gen(function* () {
      yield* stack.destroy();

      const externalStack = (props: { datasetId?: string; requirePartitionFilter?: boolean }) =>
        Effect.gen(function* () {
          const bucket = yield* GCP.Storage.Bucket("Data", {
            location: "US-CENTRAL1",
            forceDestroy: true,
          });
          const first = yield* GCP.Storage.Object("DayOne", {
            bucketName: bucket.bucketName,
            key: "events/day=2024-01-01/part-0.json",
            content: '{"id":"a","n":1}\n{"id":"b","n":2}\n',
            contentType: "application/json",
          });
          const second = yield* GCP.Storage.Object("DayTwo", {
            bucketName: bucket.bucketName,
            key: "events/day=2024-01-02/part-0.json",
            content: '{"id":"c","n":3}\n',
            contentType: "application/json",
          });
          const dataset = yield* GCP.BigQuery.Dataset("Analytics", {
            datasetId: props.datasetId,
            location: "US-CENTRAL1",
            forceDestroy: true,
          });
          // Referencing both objects orders the table after its source files,
          // which autodetect reads at create time.
          const table = yield* GCP.BigQuery.Table("Events", {
            datasetId: dataset.datasetId,
            description: "daily events",
            externalDataConfiguration: {
              sourceUris: [Output.interpolate`gs://${first.bucketName}/events/*`],
              sourceFormat: "NEWLINE_DELIMITED_JSON",
              autodetect: true,
              hivePartitioningOptions: {
                mode: "CUSTOM",
                sourceUriPrefix: Output.interpolate`gs://${second.bucketName}/events/{day:DATE}`,
                requirePartitionFilter: props.requirePartitionFilter,
              },
            },
          });
          return { bucket, table };
        });

      const { bucket, table: created } = yield* stack.deploy(externalStack({}));
      expect(created.type).toEqual("EXTERNAL");
      expect(created.externalDataConfiguration).toMatchObject({
        sourceUris: [`gs://${bucket.bucketName}/events/*`],
        sourceFormat: "NEWLINE_DELIMITED_JSON",
        hivePartitioningOptions: {
          mode: "CUSTOM",
          sourceUriPrefix: `gs://${bucket.bucketName}/events/{day:DATE}`,
          requirePartitionFilter: false,
        },
      });

      const queried = yield* bigquery.queryJobs({
        projectId: created.project,
        body: {
          query: `SELECT day, SUM(n) AS total FROM ${created.datasetId}.${created.tableId} GROUP BY day ORDER BY day`,
          useLegacySql: false,
          location: created.location,
        },
      });
      expect(queried.jobComplete).toEqual(true);
      expect((queried.rows ?? []).map((row) => row.f?.map((cell) => cell.v))).toEqual([
        ["2024-01-01", "3"],
        ["2024-01-02", "3"],
      ]);

      const { table: updated } = yield* stack.deploy(
        externalStack({ datasetId: created.datasetId, requirePartitionFilter: true }),
      );
      expect(updated.tableId).toEqual(created.tableId);
      expect(updated.creationTime).toEqual(created.creationTime);
      expect(updated.externalDataConfiguration?.hivePartitioningOptions).toMatchObject({
        requirePartitionFilter: true,
      });

      const fetchedUpdate = yield* tableStatus(created.project, created.datasetId, created.tableId);
      expect(fetchedUpdate.creationTime).toEqual(created.creationTime);
      expect(fetchedUpdate.description).toEqual("daily events");
      expect(fetchedUpdate.externalDataConfiguration).toMatchObject({
        sourceUris: [`gs://${bucket.bucketName}/events/*`],
        sourceFormat: "NEWLINE_DELIMITED_JSON",
        hivePartitioningOptions: {
          mode: "CUSTOM",
          sourceUriPrefix: `gs://${bucket.bucketName}/events/{day:DATE}`,
          requirePartitionFilter: true,
        },
      });
      expect((fetchedUpdate.schema?.fields ?? []).map((field) => field.name)).toEqual(
        expect.arrayContaining(["id", "n", "day"]),
      );

      const filtered = yield* bigquery.queryJobs({
        projectId: created.project,
        body: {
          query: `SELECT SUM(n) AS total FROM ${created.datasetId}.${created.tableId} WHERE day = '2024-01-01'`,
          useLegacySql: false,
          location: created.location,
        },
      });
      expect((filtered.rows ?? []).map((row) => row.f?.map((cell) => cell.v))).toEqual([["3"]]);

      yield* stack.destroy();

      const gone = yield* waitUntilGone(created.project, created.datasetId, created.tableId);
      expect(gone).toEqual("gone");
    }).pipe(logLevel),
  { tags: ["provider:gcp", "provider:gcp:bigquery", "live"], timeout: 180_000 },
);

test.provider(
  "rejects a table that is both a view and an external table",
  (stack) =>
    Effect.gen(function* () {
      yield* stack.destroy();

      const result = yield* stack
        .deploy(
          Effect.gen(function* () {
            const dataset = yield* GCP.BigQuery.Dataset("Analytics", {
              location: "US-CENTRAL1",
              forceDestroy: true,
            });
            return yield* GCP.BigQuery.Table("Both", {
              datasetId: dataset.datasetId,
              view: { query: "SELECT 1 AS id" },
              externalDataConfiguration: {
                sourceUris: ["gs://example-bucket/events/*"],
                sourceFormat: "NEWLINE_DELIMITED_JSON",
              },
            });
          }),
        )
        .pipe(Effect.result);
      expect(Result.isFailure(result)).toBe(true);
      if (Result.isFailure(result)) {
        expect(result.failure).toBeInstanceOf(GCP.BigQuery.ConflictingTableKind);
      }

      yield* stack.destroy();
    }).pipe(logLevel),
  { tags: ["provider:gcp", "provider:gcp:bigquery", "live"], timeout: 90_000 },
);