Denodo - Creating a Google BigQuery Data Source (Web App)

This document describes the process to create a Denodo data source against Google BigQuery using the OAuth client ID pre-generated token scheme. In general, the data source is configured using a Google service account, but there are instances where an OAuth client ID approach is necessary.

Note: The Desktop App Configuration is recommended by U-M. Use that model, if possible.

This document assumes use of the JDBC Simba BigQuery driver.

Data Owner Configures The GCP Project

Within the GCP project:

  1. Navigate to APIs & Services > OAuth consent
  2. Configure the consent screen granting the following scope required by the Simba driver: https://www.googleapis.com/auth/bigquery
  3. Navigate to APIs & Services > Credentials
  4. Create credentials of type OAuth client ID:
    • Application type: Web application
    • Authorized redirect URIs: Use the redirect URL of any Denodo environment. Example Dev: https://iq-dv-dev1.dsc.umich.edu:9443/oauth/2.0/redirectURL.jsp
    • Click Create and copy the Client ID and Client secret

Create OAuth Access and Refresh Tokens

In Denodo VDP client:

  1. Open the VDP client.
  2. Connect to the Denodo environment matching the redirect specified above in step 4. 
  3. Navigate to Tools > OAuth credentials wizards > OAuth 2.0 wizard and set the following:
    • Authentication grant: Authorization code grant
    • Client identifier: Client ID from #4 in previous section
    • Client secret: Client Secret from #4 in previous section
    • Authentication method: Include the client credentials in the body of the request
    • Token endpoint URL: https://oauth2.googleapis.com/token
    • Authorization server URL: https://accounts.google.com/o/oauth2/v2/auth
    • Redirect URI: Redirect URI used in #4 in previous section
    • Click Generate the authorization URL, select and copy/paste into a text editor
  4. Append the following to the end of the URL: &access_type=offline&prompt=consent&scope=https://www.googleapis.com/auth/bigquery
  5. Open a browser and paste the URL into a browser.
  6. Give consent when prompted.
  7. Copy the URL returned and paste it into the Paste the authorization response URL field in the Denodo OAuth credentials wizard.
  8. Click Obtain the OAuth 2.0 credentials.
  9. Copy the credentials to the clipboard and store in a secure location.

Create a JDBC Denodo data source

  1. Navigate to New > Data source > JDBC, and configure the following:
    • Database adapter: Google BigQuery
    • Database URI (substitute the values from #8 above): jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=<Project ID>;OAuthType=2;OAuthAccessToken=<Access Token>;OAuthRefreshToken=<Refresh Token>;OAuthClientId=<Client ID>;OAuthClientSecret=<Client Secret>;Timeout=1200
  2. Click Test to verify the data source connection.
Tags: 
Last Updated: 
Monday, January 4, 2021