Google BigQuery import - get correct project for a custom SQL query

Problem

• Datameer is running on GCP.
• The Google Storage Datameer private folder is located in project A.
• The Datameer service user belongs to project A.
• The Google BigQuery Connection is configured using the IAM authentication method.
• I'm would like to use custom SQL queries in my ImportJobs configuration.

While it is possible to use the query like SELECT * FROM ProjectA.dataset.table, it is not possible to query data from any other project, even if the Datameer service user has permissions to get data from these projects.  It seems like Datameer always picks up the service user's default project while building a custom query.

The following error message is thrown:

Dataset ProjectA:ProjectB.dataset.table is not found in location US

Cause

While using a custom SQL query for a Google BigQuery import via the Connection configured with IAM authentication, Datameer always pickups the project the service user belongs to.

Workaround

• Create a user in every project Datameer needs data from.
• Create a JSON security key for each user.
• Create a separate Google BigQuery Connection for each project and configured it to use the appropriate security key.
• Uses appropriate Connection to get the data from the project.