Datometry Hyper-Q integrates with Google IAM to authenticate existing client applications on BigQuery using OAuth 2.0 over the BigQuery's REST interface. This article describes the setup of credentials for use with BigQuery and the configuration of Hyper-Q.
Client credentials need to be created, authorized on BigQuery, and a refresh token generated. At the end of the process we will have the credentials that will be used as username and password in the existing client applications.
Create Client Credentials
On the GCP console, create application client credentials:
- Navigate to "APIs & Services" - "Credentials"
- Click "+ Create Credentials" at the top and select "OAuth client ID"
- Select "Desktop App" as application type, provide a name, and click "Create"
- Save the client ID and client secret displayed for the application and close the dialog with "OK"
More details about OAuth 2.0 client IDs can be found in the GCP documentation.
Authorize Application on BigQuery
Using a browser, authorize the application to access BigQuery:
- Go to the following URL, replacing
<CLIENT_ID>with the client ID obtained in the previous step: https://accounts.google.com/o/oauth2/v2/auth?client_id=<CLIENT_ID>&redirect_uri=urn:ietf:wg:oauth:2.0:oob&state=GBQAUthTest&access_type=offline&scope=https://www.googleapis.com/auth/bigquery&response_type=code
- Log in with a Google account that has the privileges to enable the application to view and manage data in BigQuery
- Allow these permissions
- Save the authorization code displayed
Generate a Refresh Token
Using a HTTP client that can submit arbitrary POST request, generate a refresh token for the application's access to BigQuery:
- Submit the following POST request, replacing
<CLIENT_SECRET>with the values obtained for these placeholders in the previous steps: https://www.googleapis.com/oauth2/v4/token?code=<AUTHORIZATION_CODE>&client_id=<CLIENT_ID>&client_secret=<CLIENT_SECRET>&redirect_uri=urn:ietf:wg:oauth:2.0:oob&grant_type=authorization_code
For example, using curl:
curl --data 'code=<AUTHORIZATION_CODE>&client_id=<CLIENT_ID>&client_secret=<CLIENT_SECRET>&redirect_uri=urn:ietf:wg:oauth:2.0:oob&grant_type=authorization_code' 'https://www.googleapis.com/oauth2/v4/token'
- Save the value of the "refresh_token" received in the response
Construct Client Credentials
The credentials that will be used in the existing client applications are constructed as follows:
- Username: the Client ID, e.g.:
- Password: the Client secret followed by four (4) vertical bars (
||||) followed by the refresh token, e.g.:
The configuration of Hyper-Q need to be performed once per Hyper-Q installation and will apply to any number of application credentials generated using the instructions above.
Ensure the following packages are installed and optionally locked at the correct version. Run pip using
sudo if the current user is not the same user that is running Hyper-Q:
pip install --user google-auth
pip install --user -Iv setuptools==45
Apply the following configuration change to the dtm.ini file:
- Add the gateway option to select OAuth:
"gateway".auth_backend = "bigquery_oauth"
- Ensure the connection string is set for the REST API: