BigQuery and OAuth2

Question

I'm trying to access Google BigQuery using Service Account approach. My code is as follows:

private static final HttpTransport HTTP_TRANSPORT = new NetHttpTransport();

private static final JsonFactory JSON_FACTORY = new JacksonFactory();

GoogleCredential credentials = new GoogleCredential.Builder()
            .setTransport(HTTP_TRANSPORT)
            .setJsonFactory(JSON_FACTORY)
            .setServiceAccountId("[e-mail]")
            .setServiceAccountScopes(BigqueryScopes.BIGQUERY)
            .setServiceAccountPrivateKeyFromP12File(
                    new File("PATH-TO-privatekey.p12"))
            .build();
    Bigquery bigquery = Bigquery.builder(HTTP_TRANSPORT, JSON_FACTORY).setHttpRequestInitializer(credentials)
            .build();
    com.google.api.services.bigquery.Bigquery.Datasets.List datasetRequest = bigquery.datasets().list(
            "PROJECT_ID");

    DatasetList datasetList = datasetRequest.execute();
    if (datasetList.getDatasets() != null) {
        java.util.List<Datasets> datasets = datasetList.getDatasets();
        System.out.println("Available datasets\n----------------");
        for (Datasets dataset : datasets) {
            System.out.format("%s\n", dataset.getDatasetReference().getDatasetId());
        }
    }

But it throws the following exception:

Exception in thread "main"  com.google.api.client.googleapis.json.GoogleJsonResponseException: 401 Unauthorized
{
  "code" : 401,
  "errors" : [ {
    "domain" : "global",
    "location" : "Authorization",
    "locationType" : "header",
    "message" : "Authorization required",
    "reason" : "required"
  } ],
  "message" : "Authorization required"
}
at com.google.api.client.googleapis.json.GoogleJsonResponseException.from(GoogleJsonResponseException.java:159)
at com.google.api.client.googleapis.json.GoogleJsonResponseException.execute(GoogleJsonResponseException.java:187)
at com.google.api.client.googleapis.services.GoogleClient.executeUnparsed(GoogleClient.java:115)
at com.google.api.client.http.json.JsonHttpRequest.executeUnparsed(JsonHttpRequest.java:112)
at com.google.api.services.bigquery.Bigquery$Datasets$List.execute(Bigquery.java:979)

The exception is fired on this line:

  DatasetList datasetList = datasetRequest.execute();

I'm getting the account ID from Google's API console from the second line on the section that looks like this:

    Client ID:  XXXXX.apps.googleusercontent.com
    Email address:  [e-mail]

What am I missing?

Answer 1

Our error handling code in the Java library needs to be improved a bit!

It looks like the signed JWT for requesting an OAuth access token is failing. You can see this by enabling the logs that @MichaelManoochehri mentioned above.

There's only a few things that I think could be causing this failure:

1. Invalid signature (using the wrong key)
2. Invalid e-mail address for the service account (I think that's been ruled out)
3. Invalid date/time stamp used for generating the signed blob (an issue date, and an expiration date)
4. Invalid scope (I think that's been ruled out)

You should check that your date/time is properly set on your server with the proper timezone -- sync'd to NTP. You can use time.gov to see the official US atomic clock time.

Answer 2

Here is a complete snippet for reference:

import com.google.api.client.http.HttpTransport;
import com.google.api.client.http.javanet.NetHttpTransport;
import com.google.api.client.json.JsonFactory;
import com.google.api.client.json.jackson.JacksonFactory;

import com.google.api.client.googleapis.auth.oauth2.GoogleCredential;
import com.google.api.services.bigquery.Bigquery;
import com.google.api.services.bigquery.Bigquery.Datasets;
import com.google.api.services.bigquery.model.DatasetList;

import java.io.File;
import java.io.IOException;
import java.security.GeneralSecurityException;


public class BigQueryJavaServiceAccount {

  private static final String SCOPE = "https://www.googleapis.com/auth/bigquery";
  private static final HttpTransport TRANSPORT = new NetHttpTransport();
  private static final JsonFactory JSON_FACTORY = new JacksonFactory();

  public static void main(String[] args) throws IOException, GeneralSecurityException {
    GoogleCredential credential = new GoogleCredential.Builder().setTransport(TRANSPORT)
        .setJsonFactory(JSON_FACTORY)
        .setServiceAccountId("[e-mail]")
        .setServiceAccountScopes(SCOPE)
        .setServiceAccountPrivateKeyFromP12File(new File("my_file.p12"))
        .build();

    Bigquery bigquery = Bigquery.builder(TRANSPORT, JSON_FACTORY)
        .setApplicationName("Google-BigQuery-App/1.0")
        .setHttpRequestInitializer(credential).build();

    Datasets.List datasetRequest = bigquery.datasets().list("publicdata");
    DatasetList datasetList = datasetRequest.execute();
    System.out.format("%s\n", datasetList.toPrettyString()); 
}

Answer 3

Eureka! Both Eric's and Michael's code works well.

The error posted in the question can be reproduced by setting the time on the client machine incorrectly. Fortunately, it can be solved by setting the time on the client machine correctly.

Note: For what it's worth, I synchronized the time on a Windows 7 box using the "Update now" button in the "Internet Time Settings" dialog. I figured that should be pretty idiot-proof... but I guess I beat the system. It corrected the seconds but left the machine off by exactly one minute. The BigQuery call failed after that. It succeeded after I manually changed the time.

Answer 4

EDIT: The answer I gave below is relevant to using Google App Engine Service Accounts - leaving here for reference.

Double check that you have added your service account address to your project's team page as an owner.

I'd recommend using the AppIdentityCredential class to handle service account auth. Here's a small snippet that demonstrates this, and I'll add additional documentation about this on the BigQuery API developer page.

Also, make sure that you are using the latest version of the Google Java API client (as of today, it's version "v2-rev5-1.5.0-beta" here).

import java.io.IOException;

import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.google.api.client.googleapis.extensions.appengine.auth.oauth2.AppIdentityCredential;
import com.google.api.client.http.HttpTransport;
import com.google.api.client.http.javanet.NetHttpTransport;
import com.google.api.client.http.json.JsonHttpRequest;
import com.google.api.client.http.json.JsonHttpRequestInitializer;
import com.google.api.client.json.JsonFactory;
import com.google.api.client.json.jackson.JacksonFactory;
import com.google.api.services.bigquery.Bigquery;
import com.google.api.services.bigquery.BigqueryRequest;

@SuppressWarnings("serial")
public class Bigquery_service_accounts_demoServlet<TRANSPORT> extends HttpServlet {

// ENTER YOUR PROJECT ID HERE
private static final String PROJECT_ID = "";

private static final HttpTransport TRANSPORT = new NetHttpTransport();
private static final JsonFactory JSON_FACTORY = new JacksonFactory();
private static final String BIGQUERY_SCOPE = "https://www.googleapis.com/auth/bigquery";


AppIdentityCredential credential = new AppIdentityCredential(BIGQUERY_SCOPE);
Bigquery bigquery = Bigquery.builder(TRANSPORT,JSON_FACTORY)

 .setHttpRequestInitializer(credential)
 .setJsonHttpRequestInitializer(new JsonHttpRequestInitializer() {
   public void initialize(JsonHttpRequest request) {
     BigqueryRequest bigqueryRequest = (BigqueryRequest) request;
     bigqueryRequest.setPrettyPrint(true);
   }
 }).build();    

public void doGet(HttpServletRequest req, HttpServletResponse resp)
    throws IOException {
  resp.setContentType("text/plain");
  resp.getWriter().println(bigquery.datasets()
    .list(PROJECT_ID)
    .execute().toString());
 }
}