Google Query Authentication Guide
    • Dark
      Light

    Google Query Authentication Guide

    • Dark
      Light

    Article Summary

    Overview

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

    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.
    • The callback URL, and therefore the Matillion ETL instance, must be HTTPS, not HTTP.
    • When setting up an OAuth, Google does not allow specifying of an IP address for redirect URLs. Viable domain addresses must be: .com or .org. For more information about how to modify the Callback URL in your Matillion ETL instance, refer to Changing the Host File.
    • 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 Project, then Manage 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

    2. Copy the Callback URL in the field at the top of the dialog as this will be required in Acquiring Third-Party Credentials. When setting up an OAuth, Google does not allow specifying of an IP address for redirect URLs. Viable domain addresses must be: .com or .org. For more information about how to modify the Callback URL in your Matillion ETL instance, refer to Changing the Host File.

    3. Click in the bottom left of the dialog to open the Add OAuth Entry dialog.

    New OAuth entry

    4. Provide a name for the OAuth in the Name field, click the Service dropdown menu and select Google, then click OK.

    Create OAuth Entry window

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

    New Entry listed on Manage OAuth window

    Please Note

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




    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 APIs & services dashboard. Click the project dropdown menu next to Google Cloud Platform 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

    2. The project window will pop up. Click NEW PROJECT in the top-right of the 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

    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

    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). When setting up an OAuth, Google does not allow specifying of an IP address for redirect URLs. Viable domain addresses must be: .com or .org. For more information about how to modify the Callback URL in your Matillion ETL instance, refer to Changing the Host File.
    • 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 domain URL of the Callback URL.
    • IP Addresses unfortunately are not accepted and will need to be mapped to a hostname. For further information, please refer to Google Cloud Platform Console Help.

    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

    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 URIs – provide the Callback URL (copied from the Manage OAuth dialog in Matillion ETL earlier). When setting up an OAuth, Google does not allow specifying of an IP address for redirect URLs. Viable domain addresses must be: .com or .org. For more information about how to modify the Callback URL in your Matillion ETL instance, refer to Changing the Host File. Click Create to proceed.

    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 Authorizing 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

    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

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

    Enabling service API



    Authorizing for Use in Matillion ETL

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

    Configure OAuth settings

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

    Configure OAuth settings

    Connecting Via a Proxy

    You must add additional connection options to complete the authentication. To do this, select the Advanced Connection Options checkbox. When connecting via a proxy, the following must be set:

    • proxyserver = 10.0.0.141
    • proxyport = 3128

    proxy connection options


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

    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 click Allow or Deny to continue to the next prompt.

    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

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

    OAuth Authorization successful