BigQuery Tutorial

This tutorial demonstrates writing an HTTP Cloud Run function that submits a query to BigQuery.

Objectives

Costs

In this document, you use the following billable components of Google Cloud:

  • Cloud Run functions
  • Cloud Build
  • Artifact Registry

For details, see Cloud Run functions pricing.

To generate a cost estimate based on your projected usage, use the pricing calculator. New Google Cloud users might be eligible for a free trial.

Before you begin

  1. Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

  2. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Go to project selector

  3. Make sure that billing is enabled for your Google Cloud project.

  4. Enable the Cloud Functions, Cloud Build, and Artifact Registry APIs.

    Enable the APIs

  5. Install the Google Cloud CLI.

  6. To initialize the gcloud CLI, run the following command: 

    gcloud init

  7. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Go to project selector

  8. Make sure that billing is enabled for your Google Cloud project.

  9. Enable the Cloud Functions, Cloud Build, and Artifact Registry APIs.

    Enable the APIs

  10. Install the Google Cloud CLI.

  11. To initialize the gcloud CLI, run the following command: 

    gcloud init

    12. If you already have the gcloud CLI installed, update it by running the following command:

    gcloud components update
  12. Prepare your development environment.

    Go to the Node.js setup guide

Preparing the application

  1. Clone the sample app repository to your local machine:

    git clone https://github.com/GoogleCloudPlatform/nodejs-docs-samples.git

    Alternatively, you can download the sample as a zip file and extract it.

  2. Change to the directory that contains the Cloud Run functions sample code:

    cd nodejs-docs-samples/functions/v2/helloBigQuery

  3. Take a look at the sample code. The sample submits a query for words that occur at least 400 times in the specified dataset, and returns the result.

    // Import the Google Cloud client library
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

const functions = require('@google-cloud/functions-framework');

/**
 * HTTP Cloud Function that returns BigQuery query results
 *
 * @param {Object} req Cloud Function request context.
 * @param {Object} res Cloud Function response context.
 */
functions.http('helloBigQuery', async (req, res) => {
  // Define the SQL query
  // Queries the public Shakespeare dataset using named query parameter
  const sqlQuery = `
      SELECT word, word_count
            FROM \`bigquery-public-data.samples.shakespeare\`
            WHERE corpus = @corpus
            AND word_count >= @min_word_count
            ORDER BY word_count DESC`;

  const options = {
    query: sqlQuery,
    // Location must match that of the dataset(s) referenced in the query.
    location: 'US',
    params: {corpus: 'romeoandjuliet', min_word_count: 400},
  };

  // Execute the query
  try {
    const [rows] = await bigquery.query(options);
    // Send the results
    res.status(200).send(rows);
  } catch (err) {
    console.error(err);
    res.status(500).send(`Error querying BigQuery: ${err}`);
  }
});

Deploying the function

To deploy the function with an HTTP trigger, run the following command in the directory that contains the sample code:

gcloud functions deploy nodejs-bq-function \
--gen2 \
--runtime=nodejs22  \
--region=REGION \
--source=. \
--entry-point=helloBigQuery \
--trigger-http \
--allow-unauthenticated

You can use the following values for the --runtime flag to specify your preferred Node.js version:

  • nodejs18 (recommended)
  • nodejs16
  • nodejs14
  • nodejs12
  • nodejs10

The --allow-unauthenticated flag lets you reach the function without authentication. To require authentication, omit the flag.

Triggering the function

  1. When the function finishes deploying, take note of the uri property or find it using the following command:

    gcloud functions describe nodejs-bq-function --gen2 --region=REGION --format="value(serviceConfig.uri)"

  2. Visit this URI in your browser. You should see a list of the words that match the query criteria, and how many times each word appears in the target dataset.

Clean up

To avoid incurring charges to your Google Cloud account for the resources used in this tutorial, either delete the project that contains the resources, or keep the project and delete the individual resources.

Deleting the project

The easiest way to eliminate billing is to delete the project that you created for the tutorial.

To delete the project:

  1. In the Google Cloud console, go to the Manage resources page.

    Go to Manage resources

  2. In the project list, select the project that you want to delete, and then click Delete.
  3. In the dialog, type the project ID, and then click Shut down to delete the project.

Deleting the function

Deleting Cloud Run functions does not remove any resources stored in Cloud Storage.

To delete the function you created in this tutorial, run the following command:

gcloud functions delete nodejs-bq-function --gen2 --region REGION

You can also delete Cloud Run functions from the Google Cloud console.