A Complete Guide for Google BigQuery Authentication

Introduction

In this tutorial, we will walk you through how you can connect to Google BigQuery from your favorite SQL/BI/ETL tools using different authentications supported by Progress DataDirect Google BigQuery JDBC Connector. There are two types of Authentication covered in this document:

  1. Service Account based
  2. OAuth 2.0

Let’s look at how you can authenticate yourself using these two authentication schemes when using Progress DataDirect Google BigQuery JDBC Connector.

Service Account based Authentication

  1. Login to your Google Cloud Console. Open the Burger Menu on the side and Go to IAM -> Service Accounts as shown below.

    Go to ServiceAccount

  2. In the Service Accounts page, Click on the Create Service Account button on the top. You should now see a form to create a service account. Fill in any Service Account Name, Service Account ID, Service Account Description. Below is an example –

    Create Service Account

  3. Click on Create. Now you should see an option to assign Service Account permissions. Under that you should find a drop down. Choose BigQuery-> BigQuery Admin.

    Create Service Account Permissions

  4. Click on Continue. On the next screen, you should now see an option to Create Key. Click on Create Key. You should now see a menu on right asking you to choose between json file .p12 key file. Choose any key format and click Create.

    Create Key

  5. This will start a download of a .json or .p12 file based on your choice. Save the file.
  6. To connect to Google BigQuery using Progress DataDirect BigQuery JDBC Connector, you can use the below URL –


    jdbc:datadirect:googlebigquery:AuthenticationMethod=serviceaccount;Project=your-project-id;Dataset=your-dataset;ServiceAccountEmail=abc@projectid.iam.gserviceaccount.com;ServiceAccountPrivateKey=/path/to/json-key-file


    where
    Project
    - Your Project ID, this can be found in your Google Console
    DataSet
    - The Dataset name you want to connect to
    ServiceAccountEmail
    – Service Account ID you created above. You should find it in IAM->Service Accounts as shown below
    ServiceAccountPrivateKey
    – Path to JSON or .p12 file you saved earlier.

  7. Here is an example URL for your reference:

    jdbc:datadirect:googlebigquery:AuthenticationMethod=serviceaccount;Project=projectzero-249620;Dataset=datadirect;ServiceAccountEmail=googlebigquery@projectzero-249620.iam.gserviceaccount.com;ServiceAccountPrivateKey=C:\Users\sbobba\Downloads\projectzero-249620-215fa9911145.json


  8. Use your favorite tool of choice with the above URL and you should be able to easily query all the data in your Google BigQuery datasets.

OAuth2.0 Authentication

  1. OAuth2.0 is another authentication you can use to access your Google BigQuery data from Progress DataDirect Google BigQuery Connector.
  2. We need two pieces of information to access Google BigQuery data using the Progress DataDirect Google BigQuery JDBC Connector – Access Token and Refresh Token.
  3. To get the Access Token and Refresh token, we need to:
    1. Create an OAuth ClientID to get ClientID and Client Secret.
    2. Send an Authorization request to Google to get the Authorization Code.
    3. Exchange the Authorization Code to get the Access Token and Refresh Token by sending a Token request to Google.
  4. Register OAuth ClientID
    1. To Register a new OAuth ClientID, go to Google Developer Console and login.
    2. Go to the tab OAuth Consent Screen, and fill in the details for your OAuth Client:
      1. Application Name: Your Application Name
      2. Support Email: Your Email/ Support Email
      3. Scopes for Google APIs:
        Add Scope -> BigQuery API ../auth/bigquery
      4. Authorized Domains: Any public facing website
      5. Application HomePage: Any public facing website
      6. Application Privacy Policy Link: Your Privacy policy
    3. Below is an example of the consent screen for your reference –

      OAuth Consent Screen

    4. Click on Save.
    5. Go to Credentials tab, click on Create Credentials, choose OAuth Client ID.

       

    6. Create OAuth Client

    7. Now you should see a screen like below asking for your application type. Choose Other, provide a name and click Create.

      Choose Application Type

    8. You should now see a new popup with your ClientID and Client Secret. Save them, you will need them later.

      Copy your OAuth Credentials
  5. Get Authorization Code


    1. With ClientID and Client Secret in hand, we are ready to make an authorization request to Google to get the Authorization code.
    2. To do this, copy this URL to your notepad and replace clientid with the Client ID you got in the above step.

      https://accounts.google.com/o/oauth2/v2/auth?client_id=<;Your-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

    3. After you have replaced the ClientID in your URL, copy it and paste the URL into your browser. You should now see this page. Select your account.

      Choose Account

    4. As soon as you choose your account, you will see a pop up asking if you want to grant access. Click on Allow.

      Grant Permissions

    5. You should now see a warning if your app isn’t verified. Click on Advanced and you should see another option Go to <your application name>. Click on it.

    6. Now you should see another window asking for confirmation if you want to allow your OAuth Client access to your BigQuery data. Click on Allow.

       
        Allow Permissions

    7. You should now see a page with an authorization code in it as shown below. Copy the code and save it.

      Copy Authorization code
  6. Get Access and Refresh Tokens
    1. With the Authorization Code in your hands, it’s time to get the Access and Refresh Tokens. This is the last step before we can access data in Google BigQuery using Progress DataDirect BigQuery JDBC connector.
    2. Open Postman and send a POST request to Google OAuth Token endpoint to exchange your ClientID, Client Secret and Authorization Code for Access Token and Refresh Token.


      Content-Type: application/x-www-form-urlencoded
       
      code=Your Authorization_code_from_step_17&
      client_id=your_client_id&
      client_secret=your_client_secret&
      redirect_uri=urn:ietf:wg:oauth:2.0:oob&
      grant_type=authorization_code


    3. This will return Access Token and Refresh Token as shown below. Save them.


      {
      "access_token": "your-access-token",
      "expires_in": 3600,
      "refresh_token": "your-refresh-token",
      "token_type": "Bearer"
      }
  7. Accessing Data using Progress DataDirect BigQuery JDBC Connector
    1. To connect to Google BigQuery using Progress Google BigQuery JDBC Connector, you can use the below URL –

      jdbc:datadirect:googlebigquery:Project=projectzero-249620;Dataset=datadirect;AccessToken=your-access-token;RefreshToken=your-refresh_token;ClientID=your-clientid.apps.googleusercontent.com;ClientSecret=your-client-secret

    2. You should be able to connect to Google BigQuery and now you are ready to query all your BigQuery data from your favorite SQL /BI/ETL tools.

    We hope this tutorial helped you to get started with the Progress DataDirect Google BigQuery JDBC Connector and explained how you can work with different authentications. If you have any questions feel free to contact us and we will be happy to help you.

ODBC, JDBC TUTORIAL

A Complete Guide for Google BigQuery Authentication

View all Tutorials

Connect any application to any data source anywhere

A product specialist will be glad to get in
touch with you