Google Bigquery 401 unauthorized error using Service Account

Question

I ran into "401 Unauthorized" problem when I tried the bigquery snippet by Michael from this post on my computer. As suggested by ryguyrg, I synchronized my computer time (by clicking "update now"） and the code worked. But the problem is, after a while, maybe several minutes, when I reran the code, it failed again with 401 Error. So I have to manually synch my computer time almost every time I want to run a big query request.

I am pretty sure my computer is working well, and the time shouldn't differ by more than a couple of millisecond with respect to the server. So what exactly is causing the problem? Shall I try something to synch the time from code before the request or is there a better way around?

Below is the 401 error message for reference:

Exception in thread "main" com.google.api.client.googleapis.json.GoogleJsonResponseException: 401 Unauthorized
    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$Jobs$Insert.executeUnparsed(Bigquery.java:1418)
    at com.google.api.services.bigquery.Bigquery$Jobs$Insert.execute(Bigquery.java:1442)
    at BigQueryJavaServiceAccount.main(BigQueryJavaServiceAccount.java:83)

Answer 1

I had same problem during this example. https://cloud.google.com/bigquery/docs/quickstarts/quickstart-client-libraries

i was trying to below command but 401 error occured

export GOOGLE_APPLICATION_CREDENTIALS=/Users/mattheu/credentialFileName.json

I solved credential problem using below

String jsonPath = "/Users/mattheu/credentialFileName.json";
        GoogleCredentials credentials = GoogleCredentials.fromStream(new FileInputStream(jsonPath));

package com;

import com.google.auth.oauth2.GoogleCredentials;
import com.google.cloud.bigquery.*;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.UUID;

public class App {
    public static void main(String[] args) throws InterruptedException, IOException {
        String jsonPath = "/Users/mattheu/credentialFileName.json";
        GoogleCredentials credentials = GoogleCredentials.fromStream(new FileInputStream(jsonPath));

        BigQuery bigquery = BigQueryOptions.newBuilder()
                .setCredentials(credentials)
                .build().getService();

        QueryJobConfiguration queryConfig =
                QueryJobConfiguration.newBuilder(
                        "SELECT "
                                + "CONCAT('https://stackoverflow.com/questions/', CAST(id as STRING)) as url, "
                                + "view_count "
                                + "FROM `bigquery-public-data.stackoverflow.posts_questions` "
                                + "WHERE tags like '%google-bigquery%' "
                                + "ORDER BY favorite_count DESC LIMIT 10")
                        // Use standard SQL syntax for queries.
                        // See: https://cloud.google.com/bigquery/sql-reference/
                        .setUseLegacySql(false)
                        .build();

        // Create a job ID so that we can safely retry.
        JobId jobId = JobId.of(UUID.randomUUID().toString());
        Job queryJob = bigquery.create(JobInfo.newBuilder(queryConfig).setJobId(jobId).build());

        // Wait for the query to complete.
        queryJob = queryJob.waitFor();

        // Check for errors
        if (queryJob == null) {
            throw new RuntimeException("Job no longer exists");
        } else if (queryJob.getStatus().getError() != null) {
            // You can also look at queryJob.getStatus().getExecutionErrors() for all
            // errors, not just the latest one.
            throw new RuntimeException(queryJob.getStatus().getError().toString());
        }

        QueryResponse response = bigquery.getQueryResults(jobId);

        TableResult result = queryJob.getQueryResults();

        // Print all pages of the results.
        for (FieldValueList row : result.iterateAll()) {
            String url = row.get("url").getStringValue();
            long viewCount = row.get("view_count").getLongValue();
            System.out.printf("url: %s views: %d%n", url, viewCount);
        }
    }
}

result:
url: What is Google's Dremel? How is it different from Mapreduce? views: 27736
url: Unable to access BigQuery from local App Engine development server views: 4732
url: How do I use the TABLE_QUERY() function in BigQuery? views: 9674
url: Migrating from non-partitioned to Partitioned tables views: 3549
url: How can I undelete a BigQuery table? views: 3332
url: Google App Engine: Using Big Query on datastore? views: 6928
url: Random Sampling in Google BigQuery views: 11635
url: How to use Bigquery streaming insertall on app engine & python views: 4279
url: Delete duplicate rows from a BigQuery table views: 8552
url: How to improve performance of GeoIP query in BigQuery? views: 3213

Answer 2

A couple milliseconds shouldn't effect it.

The biggest chance of failure would be if the time on your requests is in the future - Google's servers would most certainly reject those requests.

I agree with Michael's suggestion of syncing via NTP.

We can also look into making our OAuth services slightly more lenient - but it's always tough to balance security and usability

Answer 3

I would first make sure that your time is synced using ntpd, and that it is set to your correct time zone: http://www.ntp.org/