Skip to content

📈 Open source, privacy-first web analytics — a cookie-less Google Analytics alternative with built-in error tracking and performance monitoring. Self-host it or use our cloud. Get started for free!

16.7 KB
Raw Blame
import { Injectable } from '@nestjs/common'
import { createHash } from 'crypto'
import { createReadStream } from 'fs'
import { setTimeout as sleep } from 'timers/promises'
import { JWT } from 'google-auth-library'

import {
  assertBigQueryConfig,
  assertWarehouseDay,
  BigQueryConfig,
  ServiceAccountCredentials,
  WarehouseExportError,
  WarehouseField,
  WarehouseKind,
  WAREHOUSE_KINDS,
  WAREHOUSE_SCHEMA,
} from './data-export.schema'
import {
  WarehouseFile,
  WarehouseSourceService,
} from './warehouse-source.service'

const API = 'https://bigquery.googleapis.com/bigquery/v2'
const UPLOAD_API = 'https://bigquery.googleapis.com/upload/bigquery/v2'
const REQUEST_TIMEOUT = 120_000
const JOB_TIMEOUT = 15 * 60_000
const UPLOAD_CHUNK_BYTES = 8 * 1024 * 1024

interface BigQueryJob {
  status?: {
    state?: string
    errorResult?: { reason?: string }
  }
  statistics?: { load?: { outputRows?: string } }
}

@Injectable()
export class BigQueryService {
  constructor(private readonly source: WarehouseSourceService) {}

  async validate(
    config: BigQueryConfig,
    credentials: ServiceAccountCredentials,
  ) {
    assertBigQueryConfig(config)
    const auth = this.auth(credentials)
    const dataset = await this.json<{ location?: string }>(
      auth,
      `${API}/${this.datasetPath(config)}`,
    )
    if (dataset.location?.toLowerCase() !== config.location.toLowerCase()) {
      throw new WarehouseExportError(
        'The dataset location does not match the selected location.',
      )
    }
    await this.json(auth, `${API}/projects/${config.gcpProjectId}/jobs`, {
      method: 'POST',
      body: JSON.stringify({
        jobReference: {
          projectId: config.gcpProjectId,
          location: config.location,
        },
        configuration: {
          dryRun: true,
          query: { query: 'SELECT 1', useLegacySql: false },
        },
      }),
    })
    for (const kind of WAREHOUSE_KINDS) {
      await this.ensureTable(auth, config, kind)
    }
  }

  async exportDay(
    config: BigQueryConfig,
    credentials: ServiceAccountCredentials,
    day: string,
    runId: string,
    signal?: AbortSignal,
  ): Promise<number> {
    assertBigQueryConfig(config)
    assertWarehouseDay(day)
    const auth = this.auth(credentials)
    const exported = await this.source.exportDay(config.projectId, day, signal)
    try {
      let rows = 0
      for (const file of exported.files) {
        signal?.throwIfAborted()
        await this.ensureTable(auth, config, file.kind, signal)
        rows += await this.loadPartition(auth, config, file, day, runId, signal)
      }
      return rows
    } finally {
      await exported.cleanup()
    }
  }

  private auth(credentials: ServiceAccountCredentials) {
    return new JWT({
      email: credentials.client_email,
      key: credentials.private_key,
      scopes: ['https://www.googleapis.com/auth/bigquery'],
      transporterOptions: { timeout: 30_000, retry: false },
    })
  }

  private datasetPath(config: BigQueryConfig) {
    return `projects/${config.gcpProjectId}/datasets/${config.datasetId}`
  }

  private description(config: BigQueryConfig, kind: WarehouseKind) {
    return `Swetrix warehouse export v1; project=${config.projectId}; kind=${kind}`
  }

  private async ensureTable(
    auth: JWT,
    config: BigQueryConfig,
    kind: WarehouseKind,
    signal?: AbortSignal,
  ) {
    const tableId = `${config.tablePrefix}_${kind}`
    const path = `${API}/${this.datasetPath(config)}/tables/${tableId}`
    let table: any
    try {
      table = await this.json(auth, path, {}, signal)
    } catch (error) {
      if (!(error instanceof WarehouseExportError) || error.status !== 404)
        throw error
      try {
        await this.json(
          auth,
          `${API}/${this.datasetPath(config)}/tables`,
          {
            method: 'POST',
            body: JSON.stringify({
              tableReference: {
                projectId: config.gcpProjectId,
                datasetId: config.datasetId,
                tableId,
              },
              description: this.description(config, kind),
              schema: { fields: WAREHOUSE_SCHEMA },
              timePartitioning: { type: 'DAY', field: 'event_date' },
            }),
          },
          signal,
        )
      } catch (createError) {
        if (
          !(createError instanceof WarehouseExportError) ||
          createError.status !== 409
        ) {
          throw createError
        }
      }
      table = await this.json(auth, path, {}, signal)
    }
    if (
      table.type !== 'TABLE' ||
      table.description !== this.description(config, kind) ||
      table.timePartitioning?.type !== 'DAY' ||
      table.timePartitioning?.field !== 'event_date' ||
      !this.schemaMatches(table.schema?.fields, WAREHOUSE_SCHEMA)
    ) {
      throw new WarehouseExportError(
        'The destination tables are not compatible Swetrix export tables. Choose an unused table prefix.',
      )
    }
  }

  private schemaMatches(
    actual: WarehouseField[] | undefined,
    expected: WarehouseField[],
  ): boolean {
    return (
      Array.isArray(actual) &&
      actual.length === expected.length &&
      expected.every((field) => {
        const found = actual.find((candidate) => candidate.name === field.name)
        return (
          found?.type === field.type &&
          (found.mode || 'NULLABLE') === field.mode &&
          (!field.fields || this.schemaMatches(found.fields, field.fields))
        )
      })
    )
  }

  private async loadPartition(
    auth: JWT,
    config: BigQueryConfig,
    file: WarehouseFile,
    day: string,
    runId: string,
    signal?: AbortSignal,
  ): Promise<number> {
    const digest = createHash('sha256')
      .update(
        JSON.stringify([
          config.projectId,
          config.gcpProjectId,
          config.datasetId,
          config.location,
          config.tablePrefix,
          day,
          file.kind,
          runId,
        ]),
      )
      .digest('hex')
    for (let attempt = 0; attempt < 3; attempt += 1) {
      const jobId = `swetrix_${digest}_${attempt}`
      let job = await this.findJob(auth, config, jobId, signal)
      if (!job) {
        try {
          await this.submitJob(auth, config, file, day, jobId, signal)
        } catch (error) {
          job = await this.findJob(auth, config, jobId, signal)
          if (!job) throw error
        }
      }
      job = await this.waitForJob(auth, config, jobId, signal)
      if (!job.status?.errorResult) {
        return Number(job.statistics?.load?.outputRows || 0)
      }
      const reason = job.status.errorResult.reason
      if (
        ![
          'backendError',
          'internalError',
          'rateLimitExceeded',
          'jobRateLimitExceeded',
        ].includes(reason)
      ) {
        throw new WarehouseExportError(
          'BigQuery rejected the export job. Check destination permissions, schema and quotas, then retry.',
        )
      }
      await sleep(1000 * 2 ** attempt, undefined, { signal })
    }
    throw new WarehouseExportError(
      'BigQuery is temporarily unavailable or rate limited. Retry the export later.',
    )
  }

  private async submitJob(
    auth: JWT,
    config: BigQueryConfig,
    file: WarehouseFile,
    day: string,
    jobId: string,
    signal?: AbortSignal,
  ) {
    const tableId = `${config.tablePrefix}_${file.kind}`
    const jobReference = {
      projectId: config.gcpProjectId,
      location: config.location,
      jobId,
    }
    if (file.bytes === 0) {
      await this.json(
        auth,
        `${API}/projects/${config.gcpProjectId}/jobs`,
        {
          method: 'POST',
          body: JSON.stringify({
            jobReference,
            configuration: {
              jobTimeoutMs: String(JOB_TIMEOUT),
              query: {
                query: `DELETE FROM \`${config.gcpProjectId}.${config.datasetId}.${tableId}\` WHERE event_date = @day`,
                useLegacySql: false,
                parameterMode: 'NAMED',
                queryParameters: [
                  {
                    name: 'day',
                    parameterType: { type: 'DATE' },
                    parameterValue: { value: day },
                  },
                ],
              },
            },
          }),
        },
        signal,
      )
      return
    }

    const response = await this.request(
      auth,
      `${UPLOAD_API}/projects/${config.gcpProjectId}/jobs?uploadType=resumable`,
      {
        method: 'POST',
        headers: {
          'x-upload-content-type': 'application/octet-stream',
          'x-upload-content-length': String(file.bytes),
        },
        body: JSON.stringify({
          jobReference,
          configuration: {
            jobTimeoutMs: String(JOB_TIMEOUT),
            load: {
              destinationTable: {
                projectId: config.gcpProjectId,
                datasetId: config.datasetId,
                tableId: `${tableId}$${day.replace(/-/g, '')}`,
              },
              sourceFormat: 'NEWLINE_DELIMITED_JSON',
              schema: { fields: WAREHOUSE_SCHEMA },
              createDisposition: 'CREATE_NEVER',
              writeDisposition: 'WRITE_TRUNCATE',
              maxBadRecords: 0,
              ignoreUnknownValues: false,
            },
          },
        }),
      },
      signal,
    )
    const location = response.headers.get('location')
    await response.body?.cancel()
    if (!location || !this.isGoogleUploadUrl(location)) {
      throw new WarehouseExportError(
        'BigQuery returned an invalid upload session. Retry the export.',
      )
    }
    await this.upload(auth, location, file, signal)
  }

  private isGoogleUploadUrl(value: string) {
    try {
      const url = new URL(value)
      return (
        url.protocol === 'https:' &&
        ['bigquery.googleapis.com', 'www.googleapis.com'].includes(
          url.hostname,
        ) &&
        !url.username &&
        !url.password &&
        (!url.port || url.port === '443') &&
        (url.pathname.startsWith('/upload/bigquery/') ||
          url.pathname.startsWith('/resumable/upload/bigquery/'))
      )
    } catch {
      return false
    }
  }

  private async upload(
    auth: JWT,
    url: string,
    file: WarehouseFile,
    signal?: AbortSignal,
  ) {
    let offset = 0
    let failures = 0
    while (failures < 5) {
      const previousOffset = offset
      const end = Math.min(offset + UPLOAD_CHUNK_BYTES, file.bytes) - 1
      const stream = createReadStream(file.path, { start: offset, end })
      try {
        const response = await this.request(
          auth,
          url,
          {
            method: 'PUT',
            headers: {
              'content-type': 'application/octet-stream',
              'content-length': String(end - offset + 1),
              'content-range': `bytes ${offset}-${end}/${file.bytes}`,
            },
            body: stream as unknown as BodyInit,
            duplex: 'half',
          } as RequestInit,
          signal,
          true,
        )
        await response.body?.cancel()
        if (response.status !== 308) return
        offset = this.uploadOffset(response, file.bytes)
      } catch (error) {
        if (signal?.aborted) throw error
        if (
          error instanceof WarehouseExportError &&
          error.status &&
          error.status < 500 &&
          error.status !== 429
        ) {
          throw error
        }
        await sleep(1000 * 2 ** failures, undefined, { signal })
        const status = await this.request(
          auth,
          url,
          {
            method: 'PUT',
            headers: {
              'content-length': '0',
              'content-range': `bytes */${file.bytes}`,
            },
          },
          signal,
          true,
        )
        await status.body?.cancel()
        if (status.status !== 308) return
        offset = this.uploadOffset(status, file.bytes)
      } finally {
        stream.destroy()
      }
      failures = offset > previousOffset ? 0 : failures + 1
    }
    throw new WarehouseExportError(
      'The BigQuery upload was interrupted. Retry the export.',
    )
  }

  private uploadOffset(response: Response, size: number) {
    const range = response.headers.get('range')
    const match = range?.match(/^(?:bytes=)?0-(\d+)$/)
    const offset = match ? Number(match[1]) + 1 : 0
    if (
      (range && !match) ||
      !Number.isSafeInteger(offset) ||
      offset < 0 ||
      offset >= size
    ) {
      throw new WarehouseExportError(
        'BigQuery returned an invalid upload offset. Retry the export.',
      )
    }
    return offset
  }

  private async findJob(
    auth: JWT,
    config: BigQueryConfig,
    id: string,
    signal?: AbortSignal,
  ) {
    try {
      return await this.json<BigQueryJob>(
        auth,
        this.jobUrl(config, id),
        {},
        signal,
      )
    } catch (error) {
      if (error instanceof WarehouseExportError && error.status === 404)
        return null
      throw error
    }
  }

  private jobUrl(config: BigQueryConfig, id: string) {
    return `${API}/projects/${config.gcpProjectId}/jobs/${id}?location=${encodeURIComponent(config.location)}`
  }

  private async waitForJob(
    auth: JWT,
    config: BigQueryConfig,
    id: string,
    signal?: AbortSignal,
  ) {
    const deadline = Date.now() + JOB_TIMEOUT
    while (Date.now() < deadline) {
      const job = await this.findJob(auth, config, id, signal)
      if (job?.status?.state === 'DONE') return job
      await sleep(1500, undefined, { signal })
    }
    throw new WarehouseExportError(
      'BigQuery is still processing this export. Retry to resume checking the job.',
    )
  }

  private async json<T = unknown>(
    auth: JWT,
    url: string,
    init: RequestInit = {},
    signal?: AbortSignal,
  ): Promise<T> {
    const response = await this.request(auth, url, init, signal)
    try {
      return (await response.json()) as T
    } catch {
      throw new WarehouseExportError(
        'BigQuery returned an invalid response. Retry the export.',
      )
    }
  }

  private async request(
    auth: JWT,
    url: string,
    init: RequestInit,
    signal?: AbortSignal,
    allowIncomplete = false,
  ) {
    let target: URL
    try {
      target = new URL(url)
    } catch {
      throw new WarehouseExportError('Invalid BigQuery request URL.', 400)
    }
    const isUpload =
      target.pathname.startsWith('/upload/bigquery/v2/') ||
      target.pathname.startsWith('/resumable/upload/bigquery/v2/')
    if (
      target.protocol !== 'https:' ||
      (target.hostname !== 'bigquery.googleapis.com' &&
        target.hostname !== 'www.googleapis.com') ||
      target.port ||
      target.username ||
      target.password ||
      target.hash ||
      !/^[\x21-\x7e]+$/.test(url) ||
      url.includes('\\') ||
      !/^\/[A-Za-z0-9_/-]+$/.test(target.pathname) ||
      (!isUpload &&
        (target.hostname !== 'bigquery.googleapis.com' ||
          !target.pathname.startsWith('/bigquery/v2/')))
    ) {
      throw new WarehouseExportError('Invalid BigQuery request URL.', 400)
    }
    const requestUrl = new URL(
      target.hostname === 'bigquery.googleapis.com'
        ? 'https://bigquery.googleapis.com'
        : 'https://www.googleapis.com',
    )
    requestUrl.pathname = target.pathname
    requestUrl.search = target.search

    let headers: Headers
    try {
      headers = await auth.getRequestHeaders()
    } catch {
      throw new WarehouseExportError(
        'Google authentication failed. Replace the service account key and check that it is active.',
      )
    }
    new Headers(init.headers).forEach((value, key) => headers.set(key, value))
    if (!headers.has('content-type'))
      headers.set('content-type', 'application/json')
    let response: Response
    try {
      response = await fetch(requestUrl, {
        ...init,
        headers,
        redirect: 'error',
        signal: signal
          ? AbortSignal.any([signal, AbortSignal.timeout(REQUEST_TIMEOUT)])
          : AbortSignal.timeout(REQUEST_TIMEOUT),
      })
    } catch {
      throw new WarehouseExportError(
        'Unable to reach BigQuery. Retry the export.',
      )
    }
    if (!response.ok && !(allowIncomplete && response.status === 308)) {
      await response.body?.cancel()
      const message =
        response.status === 401 || response.status === 403
          ? 'BigQuery access was denied. Check the service account key, dataset Data Editor role and project Job User role.'
          : response.status === 404
            ? 'The BigQuery dataset or resource was not found. Check the project, dataset and location.'
            : response.status === 429
              ? 'BigQuery is rate limited. Retry the export later.'
              : 'BigQuery could not complete the request. Check the destination settings and retry.'
      throw new WarehouseExportError(message, response.status)
    }
    return response
  }
}