Aperture link to Googlesheet thru BigQuery

George_Stephen

I am in Aperture Version 2.8.8.27
I am trying to get connected to Googlesheet thru BigQuery.
BigQuery is set up with link to Googlesheet and connection to BigQuery from Aperture seems to work ok.
External system Connection is OK, using Service Account with .jason Private Key File.

But when I try to add dataset in aperture table named ‘Response’ I am getting the following error message:

[ApertureDataStudio][GoogleBigQuery JDBC Driver][GoogleBigQuery]Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials. in statement [SELECT `Timestamp`,`ID`,`Site`,`Region`,`Division`,`Country`,`Date`,`Reporter`,`Place`,`Eventtitle`,`Description` FROM `Response`]

The service account email is granted access to the googlesheet as well as the table in BigQuery, but the above error is not going away.

Any clues anyone?

Henry Simms

Hi @George_Stephen , I have reproduced the same set-up as you, and get the same result when I try to add the sheet-backed BigQuery table as a Dataset:

Looking at the service account's logs in the GCP console, I see that this is a permissions issue in GCP, "BigQuery: Permission denied while getting Drive credentials".

Searching suggests that this is an error hit often by people trying to connect apps like Aperture Data Studio to a BigQuery table connected to a GSheet, but none of the solutions suggested in responses to these questions have worked for me. Switching the driver to use OAuth authentication rather than Service Account, and then defining Scope appropriately may work but I have not tested it

George_Stephen

@Henry Simms Thanks for trying.

What is next? Will it be taken up by Experian to find a fix?

Henry Simms

Hi @George_Stephen , the problem is within GCP - Providing the service account with access to the relevant Drive GSheet - so I think Google support should be able to advise on the permissions / access needed. I see similar questions from people looking to connect in this way using a variety of tools, but none of the suggested solutions worked for me:

• https://support.google.com/looker-studio/thread/178478960/data-studio-error-bigquery-permission-denied-while-getting-drive-credentials?hl=en
• https://cloud.google.com/bigquery/docs/troubleshoot-queries#drive_permission_denied

I suggested trying OAuth authentication as an alternative because it was suggested on several forums.

James T Sidebotham

@Henry Simms Hi Henry, I saw you answered this thread and was wondering if you could help. I am trying to set up an external connection Google Big Query, but struggling on settings. I am using the Project and the dataset field. Is it best to use custom connection string or specific user connection properties.

Thanks

henry_

It's been a while since I set up a BigQuery connection, but I used the standard user connection properties and OAuth2. More here: https://docs.experianaperture.io/data-quality/aperture-data-studio-v2/get-started/configure-external-systems/#google-big-query~jdbc-databases

The Progress documentation linked from that page will have much more detail on availability connection options.

What specific error are you seeing?

James T Sidebotham

Thanks, I will review the docs and see if I have anything specific after that. Much appreciated.

James T Sidebotham

OK, I checked the log and can see:

"[ApertureDataStudio][GoogleBigQuery JDBC Driver][GoogleBigQuery]For Service Account authentication, "Service Account email" and "Service Account Private Key" must be provided in the connection string."

However, I am putting these in the connection properties.