Connecting to BigQuery using a service account.

[Alexander Jansen]

Hi,

I'm trying to connect to BigQuery using the Google API Client for Java and using a service account. I've set up the service account in the API console and granted this user access to the appropriate dataset.

I'm creating a GoogleCredential and passing it as a HttpRequestInitializer to Bigquery:

GoogleCredential credential = new GoogleCredential.Builder().setTransport(transport)

                                    .setJsonFactory(factory)

                                    .setServiceAccountId("[[serviceAcountEmail]]")

                                    .setServiceAccountScopes(BigqueryScopes.BIGQUERY)

                                    .setServiceAccountPrivateKey(privateKey)

                                    .build();

Bigquery bq = Bigquery.builder(transport, factory).setHttpRequestInitializer(initializer).build();

The value [[serviceAcountEmail]] is replaced by the actual service account email. The privateKey has been loaded using PrivateKeys.loadFromKeyStore().

But I get the following response when trying to do a query:

[ERROR] com.google.api.client.googleapis.json.GoogleJsonResponseException: 403

[ERROR] {

[ERROR]   "code" : 403,

[ERROR]   "errors" : [ {

[ERROR]     "domain" : "usageLimits",

[ERROR]     "message" : "Access Not Configured",

[ERROR]     "reason" : "accessNotConfigured"

[ERROR]   } ],

[ERROR]   "message" : "Access Not Configured"

[ERROR] }

What steps do I have to take to get this working? A post in another forum (about another API) pointed me towards the API console to set up traffic filtering, but I could not find how to do this.

Thanks,

Alexander

[Jeremy Condit]

Hi Alexander,

There are a couple simple steps to try that can rule out some common problems.  First, go to the Google APIs Console (https://code.google.com/apis/console), select the project that you're using to run the query, select the "Services" tab, and make sure that the BigQuery API is enabled.  Second, go to the "Quotas" tab and try increasing the quotas for BigQuery API calls if at all possible.

If neither of those steps helps, please send me the project ID that you're using to run the query, and I'll see if I can dig further.

Cheers,

Jeremy

[Alexander Jansen]

Hi Jeremy,

I was able to connect using userEmail/password to BigQuery using this same API project. (BigQuery was already enabled). Now I''ve increased the per user limits, but this did not help 
The project ID is 42241429167

Alexander

[Jeremy Condit]

Alexander and I resolved this problem offline, but for anybody seeing this thread in the future, here's the solution:

Go to the Google APIs Console (http://code.google.com/apis/console), select the project that your service account is associated with, select the "API Access" tab, and make sure that there is an API key created for that project.  You don't need to use the API key--it just needs to be present on the project for service account authentication to work.

Jeremy

[Kirill Lebedev]

Hi Jeremy.

  I am trying to use Service Accounts in my project too. I was able to succesfully obtain oAuth2 access token for BigQuery. But when I try to list datasets or tables I was unable to do it with the following error:

401 Unauthorized

{

  "code" : 401,

  "errors" : [ {

    "domain" : "global",

    "location" : "Authorization",

    "locationType" : "header",

    "message" : "User is not a trusted tester",

    "reason" : "authError"

  } ],

  "message" : "User is not a trusted tester"

}

The project ID is: electionear.com:organizer  OR 920037298476

Service User account is added as an owner to the project also as an owner of Dataset. I have no idea what am I doing wrong. 

Kirill

пятница, 20 апреля 2012 г., 14:42:29 UTC-7 пользователь Jeremy Condit написал:

пятница, 20 апреля 2012 г., 14:42:29 UTC-7 пользователь Jeremy Condit написал:

[Michael Manoochehri]

Hi Kirill:

Can you share your App Engine code? After adding your App Engine service account to your project as an owner, a simple snippet in Python for creating a BigQuery service might look like:

SCOPE = 'https://www.googleapis.com/auth/bigquery'

# Create a new API service for interacting with BigQuery

credentials = AppAssertionCredentials(scope=SCOPE)

http = credentials.authorize(httplib2.Http())

bigquery_service = build("bigquery", "v2", http=http)

# Product a list of tables in a dataset

tables = bigquery_service.tables()

result = tables.list(projectId=PROJECT_ID, datasetId=DATASET_ID).execute()

Note that you will also need to deploy your app to App Engine for service accounts to work - they cannot be used locally at the moment.

- Michael

[Kirill Lebedev]

Hi Michael.

  I use latest java client libraries for access. Generally I just modify plus service account example:

GoogleCredential credential = new GoogleCredential.Builder().setTransport(HTTP_TRANSPORT)

           .setJsonFactory(JSON_FACTORY)

           .setServiceAccountId("ACCOUNT EMAIL")

           .setServiceAccountScopes(BigqueryScopes.BIGQUERY)

           .setServiceAccountPrivateKeyFromP12File(new File("key.p12"))

           .build();

Bigquery bigquery = Bigquery.builder(HTTP_TRANSPORT, JSON_FACTORY)

.setHttpRequestInitializer(credential)

.setJsonHttpRequestInitializer(new JsonHttpRequestInitializer() {

   public void initialize(JsonHttpRequest request) {

     BigqueryRequest bigqueryRequest = (BigqueryRequest) request;

     bigqueryRequest.setPrettyPrint(true);

   }

}).build();

System.out.println(bigquery.tables().list("PROJECT_ ID", "DATASET_ID").execute().toPrettyString());

  I know that AppEngine service accounts are not accessible localy. I found it yesterday. For debug purpose I am trying to use application service account. Without local acces we will not be able to use BigQuery in our product. May be there is other methods to localy debug code with BigQuery?

Kirill.

вторник, 24 апреля 2012 г., 13:49:05 UTC-7 пользователь Michael Manoochehri написал:

[lparkinson]

Hi Kirill,

Are you using your App Engine project's service account? If so, it'll be a LOT easier to use the AppIdentityCredential class. Since your app already knows what its robot account's email is and its private key and everything, all you have to do is pass it the scope and give the robot access to the necessary datasets and it takes care of everything else.  For example,

AppIdentityCredential credential = new AppIdentityCredential(BIGQUERY_SCOPE);

Bigquery bigquery = Bigquery.builder(...).setHttpRequestInitializer(credential).....

Does that help?

-Laura

[Kirill Lebedev]

Hi Laura.

  I wrote code with  AppIdentityCredential. Agree that it is easy to use them on appengine, but the problem is local debugging. When You ask AppIdentitiyService about your user ID localy it returns email app_id@localhost. This email can not be added to API console project team. So I have no local access to BigQuery localy. But it is very important for me to debug our project code localy. That is why I am investigating a way to authetificate my code with Service Account using keys.

Kirill

вторник, 24 апреля 2012 г., 14:25:06 UTC-7 пользователь lparkinson написал:

[Kirill Lebedev]

Just try to use  AppIdentityCredential on my AppEngine test account and got following error:

401 { "code" : 401, "errors" : [ { "domain" : "global", "location" : "Authorization", "locationType" : "header", "message" : "User is not a trusted tester", "reason" : "authError" } ], "message" : "User is not a trusted tester" } 

So both local and AppEngine Service account auth seems not too work from Java client.

Is it possible to do something with it?

Kirill

вторник, 24 апреля 2012 г., 14:25:06 UTC-7 пользователь lparkinson написал:

Hi Kirill,