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.
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.
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: