Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: Google Analytics Service Account Support

  1. #1
    Join Date
    Nov 2016
    Posts
    25

    Exclamation Google Analytics Service Account Support

    Hi All,


    I am working on Google Analytics using PDI and have stuck at one step. I am referring the official document of Pentaho - http://wiki.pentaho.com/display/EAI/Google+Analytics . Now, on google console side, when I try to create a new service account, it by default assigns XXXXX@YYYY.iam.gserviceaccount.com . Instead of this I must have XXXXX@developer.gserviceaccount.com


    When asked about this in google analytics official forum - http://stackoverflow.com/questions/4...ervice-account , they suggested that @developer.gserviceaccount.com is an old format and I can't get service account in that format. I will have to use XXXXX@YYYY.iam.gserviceaccount.com. when I go ahead and use XXXXX@YYYY.iam.gserviceaccount.com, PDI shows "Authentication failure occurred when contacting Google analytics. Please verify the credentials in the service account email and key file fields as well as your network connectivity." error. There's no problem with my network.


    Can someone share some insight on whether PDI supports this new format (XXXXX@YYYY.iam.gserviceaccount.com)? If yes, how should I use it? and if no, what should be the workaround for this issue?


    P.S. - I have granted access to this service account email on google analytics side and downloaded .p12 file which was refereed from input step.
    TIA,
    Sanket Kelkar.

    PDI CE 6.1
    Oracle 11g
    Windows Server 2012

  2. #2
    Join Date
    Aug 2011
    Posts
    236

    Default

    Hi Sanket,

    I have to agree , the documentation on the Google side is not that great. Here's how I do it.

    1) Convert your .p12 file to .pem - https://www.sslshopper.com/article-m...-commands.html. You will need 'openssl' installed - i think it come standard with linux. You might be prompted for a password - remember this you will need it.

    2) You need to get an access token from GA. It uses the iam address and pem file. I put this in a script and then parse the output file (json) to get the access token.

    jwt1=`echo -n '{"alg":"RS256","typ":"JWT"}' | openssl base64 -e`
    jwt2=`echo -n '{"iss":"YOUR_ACCOUNT_HERE.iam.gserviceaccount.com","scope":"https://www.googleapis.com/auth/analytics","aud":"https://www.googleapis.com/oauth2/v4/token","exp":'$(($(date +%s)+3600))',"iat":'$(date +%s)'}' | openssl base64 -e`
    jwt3=`echo -n "$jwt1.$jwt2" | tr -d '\n' | tr -d '=' | tr '/+' '_-'`
    jwt4=`echo -n "$jwt3" | openssl sha -sha256 -sign YOUR_PEM_FILE_HERE -passin pass:YOUR_PASSWORD | openssl base64 -e`
    jwt5=`echo -n "$jwt4" | tr -d '\n' | tr -d '=' | tr '/+' '_-'`
    jwt6=''
    curl -H "Content-type: application/x-www-form-urlencoded" -X POST "https://www.googleapis.com/oauth2/v4/token" -d "grant_type=urn%3Aietf%3Aparams%3Aoauth%3Agrant-type%3Ajwt-bearer&assertion=$jwt3.$jwt5" > YOUR_OUTPUT_FILE_HERE

    4) Parse the output file to get the token.

    5) Create your request - tableid,dimensions and metrics. Include your access token - "Bearer " + YOUR_ACCESS_TOKEN.

    6) I use REST client with GET method. This will get you the max number of results for your request.

    7) Get the final request url (includes page number - you might have to loop depending on your data) and use REST/GET again to get final results (JSON) and parse.

    HTH.
    PDI 8.0.0
    MySQL - 5.6.27
    Redshift - 1.0.1485
    PostgreSQL 8.0.2
    OS - Ubuntu 10.04.2

  3. #3

    Default

    Hi Sanket,

    Just wanted to comment that I'm using the "iam.gserviceaccount.com" with a "*.p12" file without issue.

    Have you gone into your Google Analytics account, and added your "iam.gserviceaccount.com" as a user in your Google Analytics properties?

  4. #4
    Join Date
    Nov 2016
    Posts
    25

    Default

    Hi tnewman,
    Thank you for your reply. I tried executing the steps suggested by you and have stuck on #2. Where do I run this script? Just for your information, I am using windows 7 OS.


    If possible, can you please attach your transformation?
    TIA,
    Sanket Kelkar.

    PDI CE 6.1
    Oracle 11g
    Windows Server 2012

  5. #5
    Join Date
    Nov 2016
    Posts
    25

    Default

    Yes matthewtckr, I have added and mapped '@iam.gserviceaccount.com' into my google analytics account. Did you retrieve tableid after clicking on 'get profiles' ?
    TIA,
    Sanket Kelkar.

    PDI CE 6.1
    Oracle 11g
    Windows Server 2012

  6. #6
    Join Date
    Aug 2011
    Posts
    236

    Default

    Hi Sanket,

    I am running on linux. It looks like you can install Openssl on windows - https://www.tbs-certificates.co.uk/F...l-windows.html (or similar googling)

    Give that a go.
    PDI 8.0.0
    MySQL - 5.6.27
    Redshift - 1.0.1485
    PostgreSQL 8.0.2
    OS - Ubuntu 10.04.2

  7. #7

    Default

    Hi matthewtckr,
    I am trying to access with @iam.gserviceaccount.com and .p12 file.But I am getting an error.We have added the service account email in GoogleAnalytics too.I am getting the below error:

    When I try to get profiles it gives an error"Authentication failed".When I click Preview

    I get the below error

    2017/01/25 15:38:32 - Spoon - Save as...
    2017/01/25 15:38:32 - Spoon - Save file as...
    2017/01/25 15:39:48 - Spoon - Save as...
    2017/01/25 15:39:48 - Spoon - Save file as...
    2017/01/25 15:44:45 - Spoon - Save as...
    2017/01/25 15:44:45 - Spoon - Save file as...
    2017/01/25 17:00:33 - /Transformation 1 - Dispatching started for transformation [/Transformation 1]
    2017/01/25 17:00:33 - Google Analytics.0 - ERROR (version 6.1.0.1-196, build 1 from 2016-04-07 12.08.49 by buildguy) : Error accessing google analytics API
    2017/01/25 17:00:33 - Google Analytics.0 - ERROR (version 6.1.0.1-196, build 1 from 2016-04-07 12.08.49 by buildguy) : java.io.IOException: OAuth 2.0 authorization error. Error code is "invalid_grant". Refer to RFC 6749 section 5.2
    2017/01/25 17:00:33 - Google Analytics.0 - at org.pentaho.di.trans.steps.googleanalytics.GaInputStep.init(GaInputStep.java:237)
    2017/01/25 17:00:33 - Google Analytics.0 - at org.pentaho.di.trans.step.StepInitThread.run(StepInitThread.java:69)
    2017/01/25 17:00:33 - Google Analytics.0 - at java.lang.Thread.run(Thread.java:745)
    2017/01/25 17:00:33 - Google Analytics.0 - Caused by: com.google.api.client.auth.oauth2.TokenResponseException: 400 Bad Request
    2017/01/25 17:00:33 - Google Analytics.0 - {
    2017/01/25 17:00:33 - Google Analytics.0 - "error" : "invalid_grant",
    2017/01/25 17:00:33 - Google Analytics.0 - "error_description" : "Invalid JWT Signature."
    2017/01/25 17:00:33 - Google Analytics.0 - }
    2017/01/25 17:00:33 - Google Analytics.0 - at com.google.api.client.auth.oauth2.TokenResponseException.from(TokenResponseException.java:105)
    2017/01/25 17:00:33 - Google Analytics.0 - at com.google.api.client.auth.oauth2.TokenRequest.executeUnparsed(TokenRequest.java:287)
    2017/01/25 17:00:33 - Google Analytics.0 - at com.google.api.client.auth.oauth2.TokenRequest.execute(TokenRequest.java:307)
    2017/01/25 17:00:33 - Google Analytics.0 - at com.google.api.client.googleapis.auth.oauth2.GoogleCredential.executeRefreshToken(GoogleCredential.java:384)
    2017/01/25 17:00:33 - Google Analytics.0 - at com.google.api.client.auth.oauth2.Credential.refreshToken(Credential.java:489)
    2017/01/25 17:00:33 - Google Analytics.0 - at com.google.api.client.auth.oauth2.Credential.intercept(Credential.java:217)
    2017/01/25 17:00:33 - Google Analytics.0 - at com.google.api.client.http.HttpRequest.execute(HttpRequest.java:859)
    2017/01/25 17:00:33 - Google Analytics.0 - at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:419)
    2017/01/25 17:00:33 - Google Analytics.0 - at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:352)
    2017/01/25 17:00:33 - Google Analytics.0 - at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.execute(AbstractGoogleClientRequest.java:469)
    2017/01/25 17:00:33 - Google Analytics.0 - at org.pentaho.di.trans.steps.googleanalytics.GaInputStep.init(GaInputStep.java:233)
    2017/01/25 17:00:33 - Google Analytics.0 - ... 2 more
    2017/01/25 17:00:33 - Google Analytics.0 - ERROR (version 6.1.0.1-196, build 1 from 2016-04-07 12.08.49 by buildguy) : Error initializing step [Google Analytics]
    2017/01/25 17:00:33 - /Transformation 1 - ERROR (version 6.1.0.1-196, build 1 from 2016-04-07 12.08.49 by buildguy) : Step [Google Analytics.0] failed to initialize!


    So I would to like to know how you are abletoconnect .TIA!!

  8. #8

    Default

    Iamabletoconnectnow.

    I was referencing the P12 file

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.