How to set up an API with a Cloud Function backend that queries BigQuery on GCP
This article will show you how to create an API with API Gateway and a Cloud Functions backend that queries BigQuery. It will return all rows in a BigQuery table for a date specified in the API query.
When I had to set up an API that returned the contents of a BigQuery table for a given date, I couldn’t find a tutorial that gave me enough specific information, even in Google’s docs, so I wanted to write up how I did it in the hope that it might help someone else save some time!
Make two new service accounts
I always forget to do this step first and have to go back and make the service accounts, don’t be like me!
We need one service account that we will use in the API Gateway setup, and another that we will use for the Cloud Function that will have access to the BigQuery table.
Service account 1 (for API Gateway):
In keeping with the Principle of Least Privilege, just make a vanilla service account with no specific roles. We will give it the access it needs later on.
Service Account 2 (for the Cloud Function):
This service account needs roles assigned to it that will allow it to create BigQuery jobs and read sessions.
Write the Python code that the Cloud Function will run
Here is the code I used to create my function:
You will paste this code into the editor of the cloud function in the next step.
Create your Cloud Function that queries BigQuery
Go to the Cloud Function console and click Create Function.
Here, I have called the function get_my_table. Remember what you call this because you will need it later in the API setup. Click Save.
Under Variables, Networking and Advanced Settings, select the second service account you created that has Bigquery jobs user and read session user roles.
Paste your code into the Cloud Function code editor and designate the entry point of your function: get_my_table in my case:
In the requirements.txt file, I added the following dependencies (it is best to add the versions for each, but I got lazy):
Deploy your function.
Click on your successfully deployed function and go to the permissions tab. We need to give the Service Account attached to the API gateway permission to invoke the Cloud Function. Click Add and add the email address of the first service account you created. Select the role Cloud Functions Invoker. Save.
Give your Cloud Function’s service account permission to read your BigQuery table.
Navigate to your table in BigQuery. Click Share Table.
Add the email address of the second service account you created, the one linked to the Cloud Function. Give it the role BigQuery Data Viewer. Save.
Create the API Configuration file
The example below is based on Google’s docs. We are going to use an API key, which we will specify in the configuration file.
The title api123 is what we will name our API in the next step.
/get_my_table is the name of my cloud function. Make sure you specify your location and project id in the address field.
Note that if you specify that the api key will be passed in the header, you must name it x-api-key.
Create this file locally and save it. We will upload this in the next step.
Set up an API with API Gateway.
Search in the cloud console for API Gateway, and click ‘Create gateway’.
The API ID (api123 here) is the same as the “title” from your API configuration file.
Next is where you will upload your api configuration file from earlier. Also select the first service account that you created.
Give your gateway a name and select the location closest to you (or where your data and cloud function will be). Click Create Gateway.
Enable your API and create an API key
Navigate to the APIs & Services page. Select Library.
Search for your API — in this case named api123. Enable the API.
Click Manage, and on the side menu, click Credentials. Under Create Credentials select API key.
You will use this API key to access your API.
Test your API!
The URL that you will use for your API is found under in the Gateways tab of your API Gateway configuration:
curl -X GET -H "x-api-key: <YOUR_API_KEY>"
"https://<YOUR_GATEWAY_URL>/get_my_table?date=2021-03-12"Also test that the security is working by using the wrong API key to confirm that a 400 error is returned.
And there you have it! An API with API Gateway that queries a BigQuery table. Great job!
