Google Query Authentication Guide

Google Query Authentication Guide


Overview

This is a step-by-step guide to creating an OAuth entry, acquiring credentials and authorising Google connectors for use in Matillion ETL. These services include Google Adwords, Google Analytics, Google BigQuery, Google Sheets, YouTube and YouTube Anayltics.

Important Information

  • Google connectors use an OAuth for third-party authentication.
  • Most third-party apps and services that connect to Google data can be setup for use in Matillion ETL through the Google Developers Console using the same process.
  • While connector properties may differ between Cloud Data Warehouses, the authentication process remains the same.
  • Please be aware, the use of some services with a Google Cloud Platform account may incur costs. For further information, please refer to Google Cloud Platform pricing.

Creating an OAuth Entry in Matillion ETL

  1. In Matillion ETL, on the top left of the screen, click ProjectManage OAuth.

    Please Note

    If a Google query connector has already been added to an Orchestration Job, the Manage OAuth window may also be accessed using the following method:

    1. Click the connector icon to open the Properties panel at the bottom of the screen.
    2. Click ... next to the Authentication input, and finally click Manage in the pop-up window.
    Project dropdown menu

    Project dropdown menu

  2. Copy the Callback URL in the field at the top of the window as this will be required in Acquiring Third-Party Credentials.

  3. Click + in the bottom left of the window to open the Create OAuth Entry window.

    New OAuth entry

    New OAuth entry

  4. Click the Service dropdown menu and select Google. Then, provide a name for the OAuth in the Name field and click OK.

    Create OAuth Entry window

    Create OAuth Entry window

  5. On returning to the Manage OAuth window, check the list of OAuths to ensure the new entry is listed.

    Please Note

    This entry is Not Configured. Configuration of the OAuth entry will be discussed in Authorising for use in Matillion ETL.

    New Entry listed on Manage OAuth window

    New Entry listed on Manage OAuth window


Acquiring Third-Party Credentials

  1. Navigate to the Google Developers Console. The Google login screen will appear immediately. Enter valid login credentials to continue. The browser will then redirect to the API & Services dashboard. Click the project dropdown menu next to Google APIs in the top left of the screen.

    Please Note

    The menu should display the name of the currently selected project, i.e. <Project Name>. If there is no current project, the menu will read Select a project.

    Google API & Services page

    Google API & Services page

  2. The project window will pop up. Click NEW PROJECT in the top right of the window.

    Select a project window

    Select a project window

  3. On the New Project screen, provide details for the following fields:

    • Project name – provide a name for the project
    • Organisation – select an organisation to be associated with the app
    • Location – select a folder where the project will be stored, then click Create

    Create new project

    Create new project

  4. The browser will return to the API & Services dashboard. Now click OAuth consent screen on the sidebar. On the OAuth consent screen, provide details for the following fields:

    • Application name – provide a name for the app
    • Support email – provide an email address to be used to provide support for the app

    Create credentials for OAuth client ID

    Create credentials for OAuth client ID

  5. Next, scroll down the Authorised domains section and provide details for the following fields:

    • Authorised domains – provide all domain URLs associated with the app including the domain URL of the Callback URL (copied from the Manage OAuth window in Matillion ETL earlier)
    • Application Homepage link – provide the homepage URL for the app (it must also be listed among the above Authorised domains), then click Save

    Please Note

    • To protect users, Google restricts OAuth 2.0 applications to specific verified domains, so please ensure all domains associated with the app are listed; including the homepage URL, terms of service URL, privacy policy URL and, especially, the Callback URL.
    • IP Addresses unfortunately are not be accepted and will need to be mapped to a hostname. For further information, please refer to Google Cloud Platform Console Help.
    Authorising domains

    Authorising domains

  6. The browser will return to the API & Services dashboard. Click Credentials on the sidebar. Then, in the Credentials window, click Create credentialsOAuth client ID.

    Create an OAuth Client ID

    Create an OAuth Client ID

  7. Now, in the Create OAuth client ID page, select Web Application under Application type to reveal further options. Then, provide details for the following fields:

    • Name – provide a descriptive name for the client ID
    • Authorised redirect URLs – provide the Callback URL (earlier listed on the OAuth consent screen as one of the Authorised domains)

    Entering details for OAuth Client ID

    Entering details for OAuth Client ID

  8. The browser will return to the API & Services dashboard once again, now with a pop-up window featuring the client ID and client secret. Copy both the client ID and client secret codes from the fields as they will be required in Authorising for use in Matillion ETL.

    Please Note

    • Make sure to copy the client secret right away as it may appear only once.
    • Additionally, when copying the codes, some browsers may add a space to the end of the code. Watch out for this as it will cause the credentials to fail.
    Copy the client ID and secret

    Copy the client ID and secret

  9. A service API will now need to be enabled to work with the newly created credentials. Click Library on the sidebar, and in the API Library window, either type the name the relevant service into the search field at the top of the screen or scroll down and click on the relevant service block.

    Create API key

    API Library

  10. Once a service has been selected, the relevant API window will open. To enable the API for use with the app, simply click Enable.

    Enabling service API

    Enabling service API


Authorising for Use in Matillion ETL

  1. Return to the Manage OAuth window in Matillion ETL and click next to the previously created OAuth entry. This will open the Configure OAuth window.

    Configure OAuth settings

    Configure OAuth settings

  2. Using the codes copied from the Google Developers Console earlier, provide details for the following fields:

    Configure OAuth settings

    Configure OAuth settings

  3. The next window will have an Authorization link. Click the link to authorise Matillion ETL to use the acquired credentials.

    Authorization link

    Authorization link

  4. The browser will then redirect to a login screen to connect the Google account to the app. Click the associated account name to confirm the connection. A prompt will then ask permission to access each feature associated with the Google account. Either Allow or Deny to continue to the next prompt.

    Connect to Google account

    Connect to Google account

  5. Once permission has been allowed or denied to each associated Google feature, a final window will confirm all the permissions together. Click Allow to confirm all is correct.

    Allow access to Google account

    Allow access to Google account

  6. If all is successful, the browser will return to Matillion ETL with a window stating, "Authorization Successful".

    OAuth Authorization successful

    OAuth Authorization successful