👋 Hello 🙂 Is it possible to ingest BigQuery metadata, with the bigquery plugin, for datasets in a project in which I can’t submit jobs? i.e. I have datasets in project A, which I’d like to ingest, and I can only submit jobs in project B. I though that by setting credentials.project_id to project B I would be good to go but that doesn’t seem to be the case. I’m on v0.8.38:

source:
    type: bigquery
    config:
        project_id: A
        use_exported_bigquery_audit_metadata: false
        profiling:
            enabled: false
        credential:
            project_id: B
            private_key_id: '${GCP_PRIVATE_KEY_ID}'
            private_key: '${GCP_PRIVATE_KEY}'
            client_id: '${GCP_CLIENT_ID}'
            client_email: '${GCP_CLIENT_EMAIL}'
        domain:
            foo:
                allow:
                    - 'A\..*'
sink:
    type: datahub-rest
    config:
        server: '<https://xxx/api/gms>'
        token: '${GMS_TOKEN}'

Error:

'Forbidden: 403 POST <https://bigquery.googleapis.com/bigquery/v2/projects/A/jobs?prettyPrint=false>: Access Denied: Project '
           'A: User does not have bigquery.jobs.create permission in project A.\n'

This looks to me that this service account doesn't have some necessary permissions to execute specific jobs on project A BigQuery.

That’s correct

Did you try to create another one or give the necessary permissions?

It’s a common pattern in BQ to grant access to somebody to a given dataset but not allow jobs to be submitted in the project where the dataset lives. The user will access that dataset by submitting a job in a project under his own control. This has mostly to do with the way billing works in GCP

Hmm, I see. So, It's necessary to understand how the ingestion is creating the jobs on BigQuery. One thing that I have sure of is that they are using the BigQuery Python Client.

afaik Datahub is using SQLAlchemy to interact with BQ

hmm, so I'm completely wrong kkk Just trying to help

No problem, I appreciate it 🙂

This service account can only submit queries in project B and it can reference datasets in project A in such queries

I see. But I don't have anything in mind now that could be the problem.

it looks like Datahub defaults to submitting the jobs in the project for which the ingestion is configured: https://github.com/datahub-project/datahub/blob/master/metadata-ingestion/src/datahub/ingestion/source/sql/bigquery.py#L337 Where

project_id

comes from here: https://github.com/datahub-project/datahub/blob/master/metadata-ingestion/src/datahub/ingestion/source_config/sql/bigquery.py#L19

So, would be better to use the service account project id to create the jobs but reference the project_id on the root of the config, right? As you mention the service account has permissions on project B but not the same on A.

That’s a possible solution and that was my understanding of what credential.project_id is there for but it seems to be only passed for authentication purposes. Another solution would be an extra field under config to specify a “working_project_id” to be used to submit queries while ingesting datasets in “project_id”

Cool! For me, this seems to be an issue.

I’ll create a feature request 🙂

I already added my thumbs up!

Thank you! 😉

facing same issue. earlier this was working in 0.8.33 but with 0.8.38 it’s not

interesting 🙂 did you try to run a diff on this part of the code between .33 and .38? In my case it’s not something I urgently need so I parked it there for now