Google IAM Authentication with Hyper-Q and BigQuery

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.

Account Setup

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:

  1. Navigate to "APIs & Services" - "Credentials"
  2. Click "+ Create Credentials" at the top and select "OAuth client ID"
  3. Select "Desktop App" as application type, provide a name, and click "Create"
  4. 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:

  1. 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
  2. Log in with a Google account that has the privileges to enable the application to view and manage data in BigQuery
  3. Allow these permissions
  4. 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:

  1. Submit the following POST request, replacing <AUTHORIZATION_CODE>, <CLIENT_ID>, and <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'
  2. 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.:
    123456789012-1234567890abcdef1234567890abcdef.apps.googleusercontent.com
  • Password: the Client secret followed by four (4) vertical bars (||||) followed by the refresh token, e.g.:
    xxxxxxxxxxxx-xxxxxxxxxxx||||1//yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy

Hyper-Q Configuration

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.

Requirements

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

Configuration

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:
    Catalog=<CATALOG_NAME>;UseNativeQuery=1;SQLDialect=1;
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.