Node.js code example to access Google BigQuery

To access Google BigQuery from a Node.js application, you can use the @google-cloud/bigquery client library provided by Google. This library simplifies interactions with BigQuery, allowing you to execute queries, load data, and manage datasets.

Here's a step-by-step example of how to set up and use the @google-cloud/bigquery library in a Node.js application:

Step 1: Install the BigQuery Client Library

First, you need to install the @google-cloud/bigquery library:

npm install @google-cloud/bigquery

Step 2: Set Up Authentication

To authenticate your application, you need to set up a Google Cloud service account with the appropriate BigQuery permissions.

1. Create a Service Account: Go to the Google Cloud Console, navigate to IAM & Admin > Service Accounts, and create a new service account. Grant it the BigQuery Admin role or other appropriate roles based on your needs.
2. Generate and Download the Key: Create and download a JSON key file for the service account.

3. Set the GOOGLE_APPLICATION_CREDENTIALS Environment Variable: This variable should point to the path of your service account key file. You can set it in your environment or in your Node.js application.

   export GOOGLE_APPLICATION_CREDENTIALS="/path/to/your-service-account-key.json"
   

Step 3: Write Node.js Code to Access BigQuery

Here's an example of a Node.js script that queries BigQuery and retrieves data:

File: bigquery-example.js

const { BigQuery } = require('@google-cloud/bigquery');

// Create a BigQuery client
const bigquery = new BigQuery();

// Define your SQL query
const query = `
  SELECT
    name,
    COUNT(*) as count
  FROM
    \`my-project.my_dataset.my_table\`
  GROUP BY
    name
  ORDER BY
    count DESC
  LIMIT 10
`;

async function queryBigQuery() {
  try {
    // Run the query
    const [job] = await bigquery.createQueryJob({
      query: query,
      location: 'US', // Replace with your dataset's location
    });

    console.log(`Job ${job.id} started.`);

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

    // Print the results
    console.log('Query Results:');
    rows.forEach(row => console.log(row));
  } catch (error) {
    console.error('Error running query:', error);
  }
}

// Execute the function
queryBigQuery();

Explanation

1. Create BigQuery Client: Initialize the BigQuery client using the BigQuery class from the @google-cloud/bigquery package.
2. Define SQL Query: Set the SQL query you want to run against your BigQuery dataset.
3. Run the Query: Use createQueryJob to start a query job. The location parameter should match the location of your BigQuery dataset.
4. Get Query Results: Use getQueryResults to wait for the query to complete and retrieve the results.
5. Handle Errors: Catch and log any errors that occur during the query execution.

Run the Script

Execute the script using Node.js:

node bigquery-example.js

This script will connect to BigQuery, execute the query, and print the results.

Additional Features

The @google-cloud/bigquery library supports many other features, including:

• Loading data from files into BigQuery.
• Managing datasets and tables.
• Streaming data into BigQuery.

If you don't want to use the environmental variable, you can directly attach its key when you create the bigquery instance:

const { BigQuery } = require('@google-cloud/bigquery');
const path = require('path');
const fs = require('fs');

// Path to your service account key file
const keyFilename = path.join(__dirname, 'path/to/your-service-account-key.json');

// Create a BigQuery client with explicit credentials
const bigquery = new BigQuery({ keyFilename });

const query = `
  SELECT
    name,
    COUNT(*) as count
  FROM
    \`my-project.my_dataset.my_table\`
  GROUP BY
    name
  ORDER BY
    count DESC
  LIMIT 10
`;

async function queryBigQuery() {
  try {
    // Run the query
    const [job] = await bigquery.createQueryJob({
      query: query,
      location: 'US', // Replace with your dataset's location
    });

    console.log(`Job ${job.id} started.`);

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

    // Print the results
    console.log('Query Results:');
    rows.forEach(row => console.log(row));
  } catch (error) {
    console.error('Error running query:', error);
  }
}

// Execute the function
queryBigQuery();

Refer to the official documentation for more details on using the BigQuery client library.