Create a Cloud Run function that uses BigQuery to submit a query and return results.

  
gcloud  
projects  
add-iam-policy-binding  
 PROJECT_ID 
  
 \ 
  
--member = 
serviceAccount: PROJECT_NUMBER 
-compute@developer.gserviceaccount.com  
 \ 
  
--role = 
roles/run.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 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  
   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, europe-west1 .

   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.