How can I receive Data from Google BigQuery with Eclipse and Java?

Question

I’m trying to receive some Data from google BigQuery and show the result in my eclipse console to get started with BigQuery and Java. Basically, I followed the Instructions provided by google, which I found here: https://cloud.google.com/bigquery/docs/reference/libraries

So, my example Java Code looks like the quick start example from google:

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Dataset;
import com.google.cloud.bigquery.DatasetInfo;

public class QuickstartSample {
  public static void main(String... args){
        // Instantiates a client
        BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

        // The name for the new dataset
        String datasetName = "bigquery-public-data:hacker_news.comments";

        // Prepares a new dataset
        Dataset dataset = null;
        DatasetInfo datasetInfo = DatasetInfo.newBuilder(datasetName).build();

        // Creates the dataset
        dataset = bigquery.create(datasetInfo);

        System.out.printf("Dataset %s created.%n", dataset.getDatasetId().getDataset());
  } 
}

But when I run this Code I get the following error:

HTTP ERROR 500

Problem accessing /getbigquery. Reason: 
    Could not get the access token.


Caused by:
com.google.cloud.bigquery.BigQueryException: Could not get the access token.
    at com.google.cloud.bigquery.spi.v2.HttpBigQueryRpc.translate(HttpBigQueryRpc.java:86)
    at com.google.cloud.bigquery.spi.v2.HttpBigQueryRpc.create(HttpBigQueryRpc.java:141)
    at com.google.cloud.bigquery.BigQueryImpl$1.call(BigQueryImpl.java:172)
    at com.google.cloud.bigquery.BigQueryImpl$1.call(BigQueryImpl.java:169)
    at com.google.api.gax.retrying.DirectRetryingExecutor.submit(DirectRetryingExecutor.java:94)
    at com.google.cloud.RetryHelper.runWithRetries(RetryHelper.java:54)
    at com.google.cloud.bigquery.BigQueryImpl.create(BigQueryImpl.java:169)
    at testpackage.dto.QuickstartSample.main(QuickstartSample.java:27)
    at testpackage.getbigquery.doPost(getbigquery.java:27)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:511)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1166)
    at com.google.appengine.api.socket.dev.DevSocketFilter.doFilter(DevSocketFilter.java:74)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
    at com.google.appengine.tools.development.ResponseRewriterFilter.doFilter(ResponseRewriterFilter.java:134)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
    at com.google.appengine.tools.development.HeaderVerificationFilter.doFilter(HeaderVerificationFilter.java:34)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
    at com.google.appengine.api.blobstore.dev.ServeBlobFilter.doFilter(ServeBlobFilter.java:63)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
    at com.google.apphosting.utils.servlet.TransactionCleanupFilter.doFilter(TransactionCleanupFilter.java:48)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
    at com.google.appengine.tools.development.StaticFileFilter.doFilter(StaticFileFilter.java:122)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)

I tried to authenticate with Default Credentials so I ran “gcloud auth application-default login“ in my google SDK console. Although I could login in SDK, eclipse gave me the previous shown error.

Then I tried a few other things like downloading my token as Json file and revering to it with java in my application or setting my environment variable manually but it didn’t work as well.

Does anyone have an idea what I’m doing wrong? Any help is appreciated.

Answer 1

Following should be added to pom.xml of your maven project

    <!--Google Bigquery-->
    <dependency>
        <groupId>com.google.cloud</groupId>
        <artifactId>google-cloud-bigquery</artifactId>
        <version>0.8.0-beta</version>
    </dependency>

Create a service account in your Google Cloud Platform Console->IAM->Service Accounts with necessary permission. You will be allowed to download JSON key file.

    import com.google.api.client.googleapis.auth.oauth2.GoogleCredential;
    import com.google.api.client.http.javanet.NetHttpTransport;
    import com.google.api.client.json.jackson2.JacksonFactory;
    import com.google.api.client.util.Data;
    import com.google.api.services.bigquery.model.*;
    import com.google.api.services.bigquery.Bigquery;
    import com.google.api.services.bigquery.BigqueryScopes;
    import java.io.ByteArrayInputStream;


    //Read the content of the service account key file in following variable. Please take appropriate security measures into the account.
    String credentials = "Content of JSON secret file";

    //Create bigquery client
    NetHttpTransport transport = new NetHttpTransport();
    JacksonFactory jsonFactory = new JacksonFactory();
    GoogleCredential credential = GoogleCredential.fromStream(new ByteArrayInputStream(credentials.getBytes()));
    if (credential.createScopedRequired) credential = credential.createScoped(BigqueryScopes.all);
    Bigquery bigqueryClient  = new Bigquery.Builder(transport, jsonFactory, credential).setApplicationName("Your Application Name").build();

    String projectId = "Your-project-id";
    String datasetId = "NewDatasetName";

    Dataset dataset = new Dataset();

    DatasetReference datasetRef = new DatasetReference();
    datasetRef.setProjectId(projectId);
    datasetRef.setDatasetId(datasetId);

    dataset.setDatasetReference(datasetRef);

    try {
         bigqueryClient.datasets().insert(projectId, dataset).execute();
    } catch (Exception e){
     //Handle exceptions
    }

Answer 2

It sounds like you're hitting a bug in the App Engine SDK that prevents the services from using the gcloud application default credential. There is a workaround: rename (or remove) google-cloud-sdk/platform/google_appengine/google/appengine/tools/java/lib/shared/appengine-remote-api.jar.

You'll also need to configure the GCP Project to be used for storing and accessing the BigQuery datasets. This project can be configured by setting the com.google.appengine.application.id system property in your launch configuration's Arguments tab (via Run > Run Configurations and then add -Dcom.google.appengine.application.id=projectID to the Arguments > VM Arguments section). But if you're accessing datasets from a different project (e.g., the bigquery-public-data datasets), you will want to explicitly configure the BigQuery builder as follows:

BigQueryOptions.newBuilder()
  .setProjectId("projectID")
  .build().getService();

Finally, you must also include the appengine-api-1.0-sdk dependency by adding the following to your pom.xml:

<dependency>
  <groupId>com.google.appengine</groupId>
  <artifactId>appengine-api-1.0-sdk</artifactId>
  <version>1.9.50</version>
</dependency>

(or any later version)

That said, Cloud Tools for Eclipse should make it easier to specify the default application credential and project, so I filed a ticket to improve this situation.