Google 3rd Party Oauth using Service Accounts
For other options on authenticating Google 3rd Party services, see here.
Matillion provides various components to pull data from Google Services like Adwords, Google Analytics, Youtube, etc. One has to register Matillion with Google OAuth to access these services - as documented here.
Google allows OAuth flows using User-credentials as well as Service accounts. However, the approach prescribed/documented in Matillion works only with User credentials.
In this article we will look into 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 Google Configuration describes steps taken on the GCP Platform to create the service account and gather required information.
The section Matillion Configuration then focuses on using the information gathered to configure a Google Analytics component to use the service account create in previous step.
Ensure the relevant API’s are enabled under “API’s & Services→Library”. For example, the following image shows the Analytics API being enabled.
Create a Service Account in and note its email. Download a P12 file for this service account and note its password.
- Create an OAuth App and note the ClientID and ClientSecret.
Visit “APIs and Services→Credentials” then click “Create Credentials->OAuth Client Id”. Select application type, give it a name and click “Create”.
- Ensure this service account has access to relevant services. For example, to give access to Google analytics, Login to analytics and add the service-account’s email address to allowed users.
Copy the “P12” file for the service account to the matillion server. For example to /etc/tomcat8/ folder or any other folder on the matillion server that the tomcat user has access to. Please ensure tomcat user has Read access to this file.
- Create an new Google Oauth entry and cancel out of the configuration screen. A new OAuth entry is created with status “Not Configured” - that's fine! Its required to bypass component validation and not for oauth itself.
- Add a new Google Analytics component to a job canvas and add the following under 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 service account
- OAuthJWTCertType: Set this to "PFXFILE".
- OAuthJWTCert: The path to the .p12 file on matillion server.
- OAuthJWTCertPassword: The password of the .p12 file.
- OAuthJWTCertSubject: Set this to "*" to pick the first certificate in the certificate store.
- OAuthJWTSubject: The email address of the user for whom the application is requesting delegate access. Note that delegate access must be granted by an administrator.
- 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.
Now configure the rest of the properties on the component and run it. Matillion will use the connection options provided (above) to authenticate using the service account instead of the usual user-based oauth.