Python Google cloud API call with Python "Cannot read and write in different locations"

[fyzeri]

Hi,

I'm trying to make a API call to read data from google storage file and then to write that data to google bigquery. For both currently the region/location is set to EU but somehow I still receive the following API error "google.api_core.exceptions.BadRequest: 400 Cannot read and write in different locations: source: EU, destination: US"

Here is the python file:

from google.cloud import bigquery

client = bigquery.Client()

external_config = bigquery. ExternalConfig("CSV")
external_config.source_uris = [
"gs/😛athwrittenhere"
]
external_config.schema = [
bigquery.SchemaField("name", "STRING"),
bigquery.SchemaField("post_abbr", "STRING"),
]

external_config.csv_options.skip_leading_rows = 1

table_id = "us_states_table"



sql = 'SELECT * FROM `{0}` WHERE name LIKE "W%"'.format(table_id)

job_config = bigquery.QueryJobConfig(table_definitions={table_id: external_config})
query_job = client.query(sql, job_config =job_config)

w_states = list(query_job)

print (w_states)

Is there anyway to fix this or do I have to set up new storage and bigquery projects?
Thank you a lot in advance, Im quite new with these API calls to cloud! All the help is really appreciated.

 

[saramar]

hi fyzeri
To fix the error "google.api_core.exceptions.BadRequest: 400 Cannot read and write in different locations: source: EU, destination: US", you can either change the location of the file in Cloud Storage to be the same as the location of the table in BigQuery, or change the location of the table in BigQuery to be the same as the location of the file in Cloud Storage.

If you choose to change the location of the file in Cloud Storage, you can do this by moving the file to a bucket in a different location. If you choose to change the location of the table in BigQuery, you can do this by creating a new table in a different location and then inserting the data from the file in Cloud Storage into the new table.

It is generally recommended to keep data in the same location as the resources that will be accessing it, to minimize latency and improve performance. If you have to access data across locations, it may be more efficient to copy the data to a location closer to the resources that will be accessing it, rather than reading the data from a remote location.