BigQuery REST API call using JWT

This post looks at calling BigQuery from devices through REST API. Devices often have a small footprint and have constraints and may not be possible to install Client Libraries. By using JWT directly as a bearer token, rather than an OAuth 2.0 access token, you can avoid having to make a network request to Google’s authorization server before making an API call.

1. Create a service account, assign it necessary BigQuery IAM permissions and download the JSON key file. JSON key file structure is like this:

{
 “type”: “service_account”,
 “project_id”: “<PROJECT-ID>”,
 “private_key_id”: “<PRIVATE_KEY_ID”,
 “private_key”: “<PRIVATE_KEY>”,
 “client_email”: “<SA_NAME>@<PROJECT-ID>.iam.gserviceaccount.com”,
 “client_id”: “<CLIENT_ID>”,
 “auth_uri”: “https://accounts.google.com/o/oauth2/auth",
 “token_uri”: “https://oauth2.googleapis.com/token",
 “auth_provider_x509_cert_url”: “https://www.googleapis.com/oauth2/v1/certs",
 “client_x509_cert_url”: “https://www.googleapis.com/robot/v1/metadata/x509/xxxx.iam.gserviceaccount.com"
}

2. Create the JWT token. I used PyJWT library to create one. As per the documentation, fill the JWT fields in this python file (jsonwt.py):

$ pip install PyJWT
$ pip install cryptography
$ cat jsonwt.py
import time, jwt
iat = time.time()
exp = iat + 3600
PRIVATE_KEY_ID_FROM_JSON = "<private_key_id from json key file>"
PRIVATE_KEY_FROM_JSON = "<private_key from json key file>"
payload = {'iss': '<client_email field from json key file>',
           'sub': '<client_email field from json key file>',
           'aud': 'https://bigquery.googleapis.com/',
           'iat': iat,
           'exp': exp}
additional_headers = {'kid': PRIVATE_KEY_ID_FROM_JSON}
signed_jwt = jwt.encode(payload, PRIVATE_KEY_FROM_JSON, headers=additional_headers,algorithm='RS256')
print(signed_jwt)

3. Run the python file:

$ python jsonwt.py
b’xxxxx.xxxxxxxxxxxxx.xxxxxxxxxxxxxxxxxxxxxx’

4. Now test a BigQuery API. Make sure the project name and dataset name are the ones on which IAM access permission was given to the service account.

curl -H "Authorization: Bearer <generated JWT>" https://bigquery.googleapis.
com/bigquery/v2/projects/<project name>/datasets/<dataset name>

5. Key Rotation: It is recommended that the service account key is rotated periodically, which in the case of devices may mean that every firmware/software release on the device uses a new key, therefore, a new JWT token.

More details on google documentation.