The Job fails while reading from a Google BigQuery external source

Problem Description

A Job uses a tBigQueryInput component to read from a Google BigQuery table created using the external source, Google Sheets, but fails with the following error:

Exception in component tBigQueryInput_1
java.lang.NullPointerException
at
pim_dq.bigqueryreadfromextrernaltable_0_1.BigQueryReadFromExtrernalTable.tBigQueryInput_1Process(BigQueryReadFromExtrernalTable.java:2279)
at
pim_dq.bigqueryreadfromextrernaltable_0_1.BigQueryReadFromExtrernalTable.runJobInTOS(BigQueryReadFromExtrernalTable.java:5853)
at
pim_dq.bigqueryreadfromextrernaltable_0_1.BigQueryReadFromExtrernalTable.main(BigQueryReadFromExtrernalTable.java:5228)

However, the tBigQueryInput component works fine if the table is native to BigQuery and no external source is associated with it.

 

Root Cause

This issue is due to the API permissions. To access BigQuery native tables, you need to have access to BigQuery API. However, to access BigQuery table from an external source, you need to have permission on both BigQuery and Google Driver API.

 

Solution

Ensure the client ID has permission to access both BigQuery and Google Drive (Sheets) APIs, enable both the APIs in the Google console. While obtaining the authorization code, be sure to include the Google Drive URL, as shown in the example below:

 

https://accounts.google.com/o/oauth2/auth?redirect_uri=urn%3Aietf%3Awg%3Aoauth%3A2.0%3Aoob&prompt=se... ID>&scope=https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fuserinfo.email+https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fcloud-platform+https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fappengine.admin+https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fcompute+https%3A%2F%2Fwww.googleapis.com%2Fauth%2Faccounts.reauth+https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fdrive&access_type=offline

Version history
Revision #:
7 of 7
Last update:
‎04-13-2019 12:35 PM
Updated by: