BigQuery Tutorial (2nd gen)

---

Note: This content applies only to Cloud Functions (2nd gen). See Cloud Functions version comparison for more information.

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

Objectives

• Write an HTTP Cloud Function that submits a query to BigQuery.

Costs

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

• Cloud Functions
• Cloud Build
• Artifact Registry

For details, see Cloud 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.

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the 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.

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the 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

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 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=nodejs20  \
--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:

Caution: Deleting a project has the following effects:

• Everything in the project is deleted. If you used an existing project for the tasks in this document, when you delete it, you also delete any other work you've done in the project.
• Custom project IDs are lost. When you created this project, you might have created a custom project ID that you want to use in the future. To preserve the URLs that use the project ID, such as an appspot.com URL, delete selected resources inside the project instead of deleting the whole project.

If you plan to explore multiple architectures, tutorials, or quickstarts, reusing projects can help you avoid exceeding project quota limits.

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 Cloud Function

Deleting Cloud Functions does not remove any resources stored in Cloud Storage.

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

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

You can also delete Cloud Functions from the Google Cloud console.