bigquery-plugin

This library was generated with Nx.

Building

Run nx build bigquery-plugin to build the library.

Running unit tests

Run nx test bigquery-plugin to execute the unit tests via Jest.

How to use it

Use this docker-compose.yml file to emulate BigQuery in your local environment.

services:

  bigquery:
    container_name: bigquery
    image: ghcr.io/goccy/bigquery-emulator:0.2.10
    command: --project=local --dataset=dev
    ports:
      - 9050:9050
      - 9060:9060

Create your settings file for the cloud component(for example: bigquery_default_settings.json):

{
  "projectId": "local",
  "dataset": "dev",
  "apiEndpoint": "http://localhost:9050"
}

Use the plugin in your code:

import { BigQuery, BigQueryPlugin } from '@team_seki/bigquery-plugin'

export class Service {
  private plugin = new BigQueryPlugin()
  private bigQuery: BigQuery = this.plugin.getClient()
  private datasetId = this.plugin.getDataset()
  private tableId = "employees"
  private tableSchema = [
    {name: 'Name', type: 'STRING', mode: 'REQUIRED'},
    {name: 'Age', type: 'INTEGER'},
    {name: 'Weight', type: 'FLOAT'}
  ]

  async bigquery_plugin_example(): Promise<{ [key: string]: string }> {
    await this.listDatasets()
    await this.listTables()
    await this.createTable()
    await this.insertRows()
    console.log(`------ run query on ${this.datasetId}.${this.tableId} table ------`)
    await this.runQuery(`${this.datasetId}.${this.tableId}`)

    return {
      status: `ok`
    }
  }

  private async listDatasets() {
    // Lists all datasets in the specified project
    const [datasets] = await this.bigQuery.getDatasets()
    console.log('--- Datasets ---')
    datasets.forEach(dataset => console.log(dataset.id))
    console.log('--- -------- ---')
  }

  private async listTables() {
    // List all tables in the dataset
    const [tables] = await this.bigQuery.dataset(this.datasetId).getTables();

    console.log('--- Tables ---');
    tables.forEach(table => console.log(table.id));
    console.log('--- ------ ---');
  }

  private async createTable(): Promise<void> {
    // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
    const options = {
      schema: this.tableSchema,
      location: 'US',
    }

    const [tables] = await this.bigQuery.dataset(this.datasetId).getTables();
    if (!tables.some(t => t.id === this.tableId)) {
      // Create a new table in the dataset
      const [table] = await this.bigQuery
        .dataset(this.datasetId)
        .createTable(this.tableId, options)
      console.log(`Table ${table.id} created.`)
    } else {
      console.log(`Table(${this.tableId}) already exists`)
    }
  }

  private async insertRows() {
    const rows = [
      {Name: 'Tom', Age: 30},
      {Name: 'Jane', Age: 32},
    ]

    // Insert data into a table
    await this.bigQuery
      .dataset(this.datasetId)
      .table(this.tableId)
      .insert(rows)
    console.log(`Inserted ${rows.length} rows`)
  }

  private async runQuery(tableName: string) {
    // Queries the U.S. given names dataset for the state of Texas.
    const query = `SELECT * FROM \`${tableName}\` LIMIT 100`

    // For all options, see https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query
    const options = {
      query: query,
      // Location must match that of the dataset(s) referenced in the query.
      location: this.bigQuery.location//'US',
    }

    // Run the query as a job
    const [job] = await this.bigQuery.createQueryJob(options)
    console.log(`Job ${job.id} started.`)

    // Wait for the query to finish
    const [rows] = await job.getQueryResults()

    // Print the results
    console.log('--- Query result ---')
    console.log('Rows:')
    rows.forEach(row => console.log(row))
  }