I am trying to run a relatively long and complex query of BigQuery that brings results from different datasets of multiple projects. For example, the query has multiple where clauses that fetches from project A, dataset A, and project B, dataset B, in GCP’s BigQuery. I had to make Data Source connections for every GCP projects in Redash with different Processing Locations.
Since I cannot select multiple Data Sources at once, I had to select one BigQuery data source, let’s say project_B, and run the query. However, I encounter following error message:
Error running query: Access Denied: Table project_A:Dataset_A.Table_A*: User does not have permission to query table project_A:Dataset_A.Table_A*. at [8:1]
The thing is I already have made Data Source connection with project_A in Redash, and if I select project_A as the data source in new query window then run the said query, then it says that I do not have the permission to query table project_B:Dataset_B.Table_B.
Is there anyway to integrate all BigQuery projects into a single Data Source, or is there any other way that I can run the query without getting the permission related error?