How to: Connect to BigQuery

An option for generating OAuth2 credentials is to use the OAuth2 Playground. The OAuth2 Playground, in conjunction with the Google API Console, lets you manually create OAuth2 tokens.

Get a client ID and client secret

1. Open the Google API Console Credentials page.

2. From the project drop-down, select an existing project or create a new one.

3. On the Credentials page, select Create credentials, then select OAuth client ID.

4. Under Application type, choose Web application.

5. Under Authorized redirect URIs, add a line with: https://developers.google.com/oauthplayground

6. Click Create.

7. On the Client ID page, take note of the client ID and client secret. You'll need these in the next step.

If you have an existing cloud project, you can reuse it by setting the Authorized redirect URIs as described in step 5.

Generate tokens

1. Go to the OAuth2 Playground.

2. Click the gear icon  in the upper right corner and select the box labeled Use your own OAuth credentials (if it isn't already selected).

3. Make sure that:

   • OAuth flow is set to Server-side.

   • Access type is set to Offline (this ensures you get a refresh token and an access token, instead of just an access token).

4. Enter the OAuth2 client ID and OAuth2 client secret you obtained above.

5. In the section labeled Step 1 - Select & authorize APIs, click BigQuery Connection API v1 from the list and select its scope. Then click Authorize APIs:

6. If prompted, sign in to the account to which you want to grant access and authorization. Otherwise, confirm that the current Google user in the top right corner is the Google Big Query or manager account for whom you want to obtain credentials.

   If you got your client ID and secret only a few minutes ago, and you get an error such as Error: redirect_uri_mismatch, the changes you made may not have propagated. Click the back button in your browser, wait a few minutes, then try clicking Authorize APIs again.

7. A prompt appears indicating your app would like to View ans manage your data in Google BigQuery. Click Accept to continue.

8. In the tab labeled Step 2 - Exchange authorization code for tokens, an Authorization code should appear. Click Exchange authorization code for tokens.

9. If all goes well, the Refresh token and Access token should be filled in for you (you may have to re-expand Step 2 - Exchange authorization code for tokens):

10. Copy the Refresh token and access token into the jdbc url, along with the client ID, client secret and BigQuery Project Id.

CODE

jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;OAuthType=2;ProjectId=YOUR-BIGQUERY-PROJECT-ID;OAuthAccessToken=YOUR-ACCESS-TOKEN;OAuthRefreshToken=YOUR-REFRESH-TOKEN;OAuthClientId=YOUR-CLIENT-ID;OAuthClientSecret=YOUR-SECRET;

Remove the OAuth2 Playground from your client ID

Now that you have a refresh token, you no longer need the OAuth2 Playground to be an authorized redirect URI. To remove it from the list of authorized redirect URIs:

1. Go to the Google API Console Credentials page.

2. From the project drop-down, select your project.

3. On the Credentials page, click the client ID name to edit.

4. Remove https://developers.google.com/oauthplayground from the Authorized redirect URIs.

5. Click Save.