Google third-party authentication using service accounts
    • Dark
      Light

    Google third-party authentication using service accounts

    • Dark
      Light

    Article Summary

    Note

    For other options when authenticating Google third Party services, read Google Query Authentication Guide.

    Additional guides include Google Ad Manager Query authentication guide and Google Custom Search Query authentication guide.

    Overview

    Users can perform ETL/ELT on their data from numerous Google services:

    Google allows OAuth flows using user credentials as well as service accounts.

    This page explores using a service account for OAuth instead of a user account to pull data from Google Analytics using the Google Analytics Query component.

    The section titled Google Configuration describes steps taken on the Google Cloud Platform (GCP) to create the service account and gather required information.

    The section titled Matillion ETL Configuration then focuses on using the information gathered to configure an example Google Analytics Query component to use the service account created in Google Configuration.


    Google configuration

    Enable the Google Analytics API

    1. Log in to the Google Cloud Platform console.
    2. Click the "hamburger" menu in the upper-left of the console UI.
    3. Click APIs and services.
    4. Click Library.
    5. Use the search bar to find "Google Analytics API". Click the search result of the same name.
    6. You should now see the product details page for the Google Analytics API. Click Manage.
    7. If you see a button that reads Disable API, ignore it. This means you have already enabled this API. Alternatively, click Enable if this button is available.

    Create a service account

    1. Create a service account and note the email address.
    2. Download a .p12 file for this service account, and note its password.
    3. Ensure this service account has access (permissions) to relevant services. For example, to give the service account access to Google Analytics, log in to Google Analytics and add the service account email address to the allowed users.

    Create an OAuth app

    1. Click the "hamburger" menu in the upper-left of the console UI.
    2. Click APIs and services.
    3. Click Credentials.
    4. Click Create credentials.
    5. Click OAuth client ID.
    6. Set the application type.
    7. Complete the fields and click Create.
    8. Once created, gather your app's Client ID and Client Secret credentials.

    Matillion ETL configuration

    1. Copy the .p12 file for your created service account to the Matillion ETL server. For example, to the /etc/tomcat/ folder or any other folder on the Matillion ETL server that the Tomcat user has access to. Please ensure the Tomcat user has Read access to this file.
    2. Create a new Google OAuth entry in Matillion ETL.
      i. Click ProjectManage OAuth.
      ii. Click + to add a new OAuth entry.
      iii. Name the entry.
      iv. Select Google from the drop-down menu of services.
      v. Click OK. The OAuth entry's status will read "Not configured".
      vi. Click OK.
    3. Add a new Google Analytics Query component to the job canvas.
    4. In the Connection Options parameter, set the following connection options:
      • InitiateOAuth: Set this to GETANDREFRESH.
      • OAuthClientId: The Client ID in your app settings.
      • OAuthClientSecret: The Client Secret in your app settings.
      • OAuthJWTIssuer: Email address of your service account.
      • OAuthJWTCertType: Set this to PFXFILE.
      • OAuthJWTCert: The path to the .p12 file on the Matillion ETL server.
      • OAuthJWTCertPassword: The password of the .p12 file.
      • OAuthJWTCertSubject: Set this to * to pick the first certificate in the certificate store.
      • Profile: The Google Analytics profile or view you want to connect to. This value can be retrieved from the Profiles table. If this is not specified, the first profile returned will be used.
    5. Configure the remaining parameters of the Google Analytics Query component and then run the job. Matillion ETL will use the connection options to authenticate using the service account instead of the user-based OAuth entry.