CODEWITHSUNDEEP
google-bigquery
delegate
delegate

Reputation: 121

Access denied when copying BigQuery tables between GCP projects

I have projects P1,P2 in europe-west2. In both projects I have same dataset/table structure at same location europe-west2. In P1, I created a service account and added the same service account (SA) to P2, like here: https://gtseres.medium.com/using-service-accounts-across-projects-in-gcp-cf9473fef8f0

In both projects, the SA has role BigQuery Admin.

I want to copy a table from P1 to P2. I do

bq --project_id P1 --service_account_credential_file <path to SA json> cp P1:dataset.table P2:dataset.table

The script seems to find the tables and asks

cp: replace P2:dataset.table? (y/n)

After confirming, cp says:

BigQuery error in cp operation: Access Denied: Project P1: User does not have bigquery.jobs.create permission in project P1.

If I try to copy in the other direction then I get:

BigQuery error in cp operation: Access Denied: Permission bigquery.tables.get denied on table P1:dataset.sessions (or it may not exist).

Upvotes: 0

Views: 1036

Answers (1)

Raul Saucedo
Raul Saucedo

Reputation: 1780

This issue could be about permissions. You need these permissions:

On the source dataset:

  • bigquery.tables.get
  • bigquery.tables.getData

On the destination dataset:

  • bigquery.tables.create.

Permission to run a copy job:

  • bigquery.jobs.create IAM permission.

  • bigquery.datasets.create permission

Also, you need these IAM roles that include the permissions you need to run a copy job:

  • roles/bigquery.user
  • roles/bigquery.jobUser
  • roles/bigquery.admin

You can use this command to copy tables:

bq --location=location cp \
-a -f -n \
project_id:dataset.source_table \
project_id:dataset.destination_table

You can read this documentation.

Upvotes: 0

Related Questions