Search code examples
google-bigquerygoogle-apiazure-data-factoryazure-synapse

Azure Synapse Copy Data from BigQuery, Source ERROR [HY000] [Microsoft][BigQuery] (131) Unable to authenticate with Google BigQuery Storage API

I am getting this error at the Source tab at the Use query (Table, Query) Query, when doing a copy data activity at the Azure Synapse pipeline.

Unable to authenticate with Google BigQuery Storage API:

Unable to authenticate with Google BigQuery Storage API .

The strange thing is I can preview data at the Source dataset, I can also preview data when select the Use query Table option.

can preview table

I can even run query to select the table's schema

can query schema

SELECT
*
FROM
`3082`.INFORMATION_SCHEMA.TABLES
WHERE table_type = 'BASE TABLE'

but I get this authentication error when selecting columns

SELECT
*
FROM
`3082.gcp_billing_export_v1_019F74_6EA5E8_C96548`;
Solution

  • ERROR [HY000] [Microsoft][BigQuery] (131) Unable to authenticate with Google BigQuery Storage API. Check your account permissions

    The above error is due to issue in authentication of BigQuery Storage API. The permission required to access data from BigQuery are,

    • bigquery.readsessions.create
    • bigquery.readsessions.getData
    • bigquery.readsessions.update

    The role BigQuery User will help in giving above permissions.

    Reference:

    1. Google cloud doc on Access Control - BigQuery User.
    2. MS doc on Google BigQuery connector issue