Create a function that returns BigQuery results

This tutorial shows you how to write an HTTP Cloud Run function that submits a query to BigQuery.

Before you begin

1. Make sure you have set up a new project for Cloud Run as described in the setup page.

2. Enable the Artifact Registry, Cloud Build, and Cloud Run Admin API APIs:

    gcloud services enable artifactregistry.googleapis.com \
        cloudbuild.googleapis.com \
        run.googleapis.com
   

3. If you are under a domain restriction organization policy restricting unauthenticated invocations for your project, you will need to access your deployed service as described under Testing private services.

Required roles

To get the permissions that you need to deploy Cloud Run services from source, ask your administrator to grant you the following IAM roles:

• Cloud Run Source Developer (roles/run.sourceDeveloper) on the Cloud Run service
• Service Usage Consumer (roles/serviceusage.serviceUsageConsumer) on the Cloud Run service
• Service Account User (roles/iam.serviceAccountUser) on the service identity
• BigQuery Job User (roles/bigquery.jobUser) on the service identity

For a list of IAM roles and permissions that are associated with Cloud Run, see Cloud Run IAM roles and Cloud Run IAM permissions. If your Cloud Run service interfaces with Google Cloud APIs, such as Cloud Client Libraries, see the service identity configuration guide. For more information about granting roles, see deployment permissions and manage access.

Roles for service account

gcloud projects add-iam-policy-binding PROJECT_ID \
    --member=serviceAccount:PROJECT_NUMBER-compute@developer.gserviceaccount.com \
    --role=roles/cloudbuild.builds.builder

Prepare the application

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

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

   Alternatively, 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}`);
     }
   });

Deploy the function

To deploy the function with an HTTP trigger:

1. Run the following command in the directory that contains the sample code:

   gcloud beta run deploy FUNCTION \
      --source . \
      --function FUNCTION_ENTRYPOINT \
      --base-image BASE_IMAGE \
      --region REGION \
      --allow-unauthenticated

   Replace:

   • FUNCTION with the name of the function you are deploying, for example my-bigquery-function. You can omit this parameter entirely, but you will be prompted for the name if you omit it.

   • FUNCTION_ENTRYPOINT with the entry point to your function in your source code. This is the code Cloud Run executes when your function runs. The value of this flag must be a function name or fully-qualified class name that exists in your source code. The entry point you must specify for the sample function is helloBigQuery.

   • BASE_IMAGE with the base image environment for your function, for example, nodejs22. For details about base images and the packages included in each image, see Runtimes base images.

   • REGION with the Google Cloud region where you want to deploy your function. For example, us-central1.

   Optional:

   • If you are creating a public HTTP function, for example a webhook, specify the --allow-unauthenticated flag. This flag assigns the Cloud Run IAM Invoker role to the special identifier allUser. You can use IAM to edit this setting later after you create the service.

Test the function

1. When the function finishes deploying, copy the uri property.

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

While Cloud Run does not charge when the service is not in use, you might still be charged for storing the container image in Artifact Registry. You can delete your container image or delete your Google Cloud project to avoid incurring charges. Deleting your Google Cloud project stops billing for all the resources used within that 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.