Access BigQuery table with external data sources using Service Account

Question

How can I query a BigQuery table that is based on an external data source (Google Spreadsheet) using a service account as credential provider?

I initialize the BigQuery java client like this:

GoogleCredentials cred = ServiceAccountCredentials
        .fromStream(credentialsFile.getInputStream());
BigQuery bq = BigQueryOptions.newBuilder()
        .setCredentials(cred).build()
        .getService();

However, when trying to query the table I get the authentication error:

Access Denied: BigQuery BigQuery: No OAuth token with Google Drive scope was found.

When adding the drive scope like this:

GoogleCredentials cred = ServiceAccountCredentials
        .fromStream(credentialsFile.getInputStream())
        .createScoped(Lists.newArrayList("https://www.googleapis.com/auth/drive"));

an Insufficient Permission error is returned instead.

I shared the sheet with the service account id [email protected]. — user10595796
Check if the created service account has drive access on the GCP console — Felipe Hoffa
@FelipeHoffa How can I do that? That's exactly what I can't figure out. — user10595796

SANN3 SANN3 · Accepted Answer · 2019-02-08T13:04:55

I also faced the same issue, I fixed using the following steps

1. Enable Google Drive API in the project

Log in to your Google Cloud Console. Select your project from the top dropdown. From the left navigation, choose APIs & Services, then choose Dashboard. At the top of the page, click Enable APIs and Services. Use the searchbar to search for Google Drive API, select it, and click Enable.

2. Add service account client ID to the Google Sheet

From your Google Cloud Console, select IAM & admin from the left sidebar, then select Service accounts. Copy the value in the Service account ID column. If you don't have a service account yet, follow the instructions above for connecting a BigQuery account.

Open your Google Sheet, and click on the Share button. Click Advanced at the bottom, and in the Invite people text input, enter the Service account ID value you copied earlier.

3. Add "cloud-platform" scope

GoogleCredentials cred = ServiceAccountCredentials
            .fromStream(credentialsFile.getInputStream())
            .createScoped(Arrays.asList("https://www.googleapis.com/auth/drive",
                    "https://www.googleapis.com/auth/cloud-platform"));

Code:

        File credentialsFile = new File("credentials.json");
        GoogleCredentials cred = ServiceAccountCredentials
                .fromStream(new FileInputStream(credentialsFile))
                .createScoped(Arrays.asList("https://www.googleapis.com/auth/drive",
                        "https://www.googleapis.com/auth/cloud-platform"));
        BigQuery bigquery = BigQueryOptions.newBuilder()
                .setCredentials(cred).build()
                .getService();

        QueryJobConfiguration queryConfig =
                QueryJobConfiguration.newBuilder(
                        "SELECT * FROM `my_project.dataset.table1`")
                        // 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();

        QueryResponse response = bigquery.getQueryResults(jobId);

        TableResult result = queryJob.getQueryResults();

        // Print all pages of the results.
        for (FieldValueList row : result.iterateAll()) {
            String date = row.get("Date").getStringValue();
            System.out.printf("Date: %s%n", date);
        }

Reference:

https://support.chartio.com/docs/data-sources/#google-sheets-via-bigquery

Access BigQuery table with external data sources using Service Account

1 Answers