google-bigquery

BigQuery Omni, write data to aws S3 : this operation cannot write within the same region aws-eu-west-1

I created a gcp bigquery external connection :

bq mk --connection --connection_type='AWS' \
--iam_role_id=arn:aws:iam::XXXXXXXXX:role/role_super \
--location=europe-west1 \
export_to_s3

and then a schema and an external table

CREATE SCHEMA super_schema
OPTIONS (location = 'aws-eu-west-1');

CREATE EXTERNAL TABLE `project_name.super_schema.super_table`
WITH CONNECTION `project_name.aws-eu-west-1.export_to_s3`
OPTIONS (
        format = 'PARQUET',
        uris = ["s3://xxxxxxxxxxxxxxxx/YYYY/*.parquet"]
  );

I can read the data of the table in bigquery ->

select * from `project_name.super_schema.super_table`;

but I can't add data

INSERT INTO `project_name.super_schema.super_table` (a, b) VALUES (10,10);

it fail with

For BigQuery Omni, this operation cannot write within the same region aws-eu-west-1

Solution

  • The reason why you are getting the error: ‘operation cannot write within the same region aws-eu-west-1’ is because of the limitation of the BigQuery Omni in AWS S3.

    BigQuery Omni only allows querying data stored in external systems like AWS S3 but does not support writing back to that external source. The workaround is you can write or insert data to BigQuery and manage it in BigQuery then export it back to AWS S3. Please be mindful also of the cost the will incur in writing the data to BigQuery and you must have the necessary IAM permission to read in your AWS S3 bucket.