I m hoping someone might have some ideas on how to troulesho

Question

I m hoping someone might have some ideas on how to trouleshoot an issue I m having with prefect gcp specifically the bigquery load cloud storage function I m trying to load some files I have in a GoogleCloudStorage bucket into a BigQuery dataset with this function I ve used some example scripts from the prefect gcp docs and am getting some mixed results in them finding my BQ dataset The script below for loading a file from a GCS bucket to BQ fails with a 404 error about not being able to find my BQ dataset ```from prefect import flow from prefect gcp import GcpCredentials from prefect gcp bigquery import bigquery load cloud storage flow def example bigquery load cloud storage flow gcp credentials = GcpCredentials project= mpls 311 result = bigquery load cloud storage dataset= mpls 311 staging table= stg mpls 311data uri= <gs data lake mpls 311 data pq mpls 311data 2023 parquet> gcp credentials=gcp credentials return result example bigquery load cloud storage flow ``` The script below for creating a table in my BQ dataset using bigquery create table successfully finds my dataset and builds a table in it As far as I can see I have it set up the same related to identifying my GCP Project and BQ Dataset ```from prefect import flow from prefect gcp import GcpCredentials from prefect gcp bigquery import bigquery create table from google cloud bigquery import SchemaField flow def example bigquery create table flow gcp credentials = GcpCredentials project= mpls 311 schema = SchemaField number field type= INTEGER mode= REQUIRED SchemaField text field type= STRING mode= REQUIRED SchemaField bool field type= BOOLEAN result = bigquery create table dataset= mpls 311 staging table= test table schema=schema gcp credentials=gcp credentials return result example bigquery create table flow ``` I m stumped Anyone have any ideas on what I might be doing wrong I ll include the full traceback in the thread

Mike Cole · Answer

The traceback error I get for the first script using bigquery load cloud storage ```13 40 30 518 | ERROR | Flow run foamy dog Finished in state Failed Flow run encountered an exception google api core exceptions NotFound 404 POST Not found Dataset mpls 311 mpls 311 staging n Traceback most recent call last File home mike git mpls 311 data flows bq test2 py line 16 in lt module gt example bigquery load cloud storage flow File home mike anaconda3 envs mpls311 lib python3 9 site packages prefect flows py line 448 in call return enter flow run engine from flow call File home mike anaconda3 envs mpls311 lib python3 9 site packages prefect engine py line 164 in enter flow run engine from flow call return anyio run begin run File home mike anaconda3 envs mpls311 lib python3 9 site packages anyio core eventloop py line 68 in run return asynclib run func args backend options File home mike anaconda3 envs mpls311 lib python3 9 site packages anyio backends asyncio py line 204 in run return native run wrapper debug=debug File home mike anaconda3 envs mpls311 lib python3 9 asyncio runners py line 44 in run return loop run until complete main File home mike anaconda3 envs mpls311 lib python3 9 asyncio base events py line 647 in run until complete return future result File home mike anaconda3 envs mpls311 lib python3 9 site packages anyio backends asyncio py line 199 in wrapper return await func args File home mike anaconda3 envs mpls311 lib python3 9 site packages prefect client utilities py line 47 in with injected client return await fn args kwargs File home mike anaconda3 envs mpls311 lib python3 9 site packages prefect engine py line 244 in create then begin flow run return await state result fetch=True File home mike anaconda3 envs mpls311 lib python3 9 site packages prefect states py line 89 in get state result raise await get state exception state File home mike anaconda3 envs mpls311 lib python3 9 site packages prefect engine py line 636 in orchestrate flow run result = await run sync flow call File home mike anaconda3 envs mpls311 lib python3 9 site packages prefect utilities asyncutils py line 156 in run sync in interruptible worker thread tg start soon File home mike anaconda3 envs mpls311 lib python3 9 site packages anyio backends asyncio py line 597 in aexit raise exceptions 0 File home mike anaconda3 envs mpls311 lib python3 9 site packages anyio to thread py line 33 in run sync return await get asynclib run sync in worker thread File home mike anaconda3 envs mpls311 lib python3 9 site packages anyio backends asyncio py line 877 in run sync in worker thread return await future File home mike anaconda3 envs mpls311 lib python3 9 site packages anyio backends asyncio py line 807 in run result = context run func args File home mike anaconda3 envs mpls311 lib python3 9 site packages prefect utilities asyncutils py line 135 in capture worker thread and result result = fn args kwargs File home mike git mpls 311 data flows bq test2 py line 8 in example bigquery load cloud storage flow result = bigquery load cloud storage File home mike anaconda3 envs mpls311 lib python3 9 site packages prefect tasks py line 436 in call return enter task run engine File home mike anaconda3 envs mpls311 lib python3 9 site packages prefect engine py line 926 in enter task run engine return run async from worker thread begin run File home mike anaconda3 envs mpls311 lib python3 9 site packages prefect utilities asyncutils py line 177 in run async from worker thread return anyio from thread run call File home mike anaconda3 envs mpls311 lib python3 9 site packages anyio from thread py line 47 in run return asynclib run async from thread func args File home mike anaconda3 envs mpls311 lib python3 9 site packages anyio backends asyncio py line 906 in run async from thread return f result File home mike anaconda3 envs mpls311 lib python3 9 concurrent futures base py line 446 in result return self get result File home mike anaconda3 envs mpls311 lib python3 9 concurrent futures base py line 391 in get result raise self exception File home mike anaconda3 envs mpls311 lib python3 9 site packages prefect engine py line 1064 in get task call return value return await future result File home mike anaconda3 envs mpls311 lib python3 9 site packages prefect futures py line 237 in result return await final state result raise on failure=raise on failure fetch=True File home mike anaconda3 envs mpls311 lib python3 9 site packages prefect states py line 89 in get state result raise await get state exception state File home mike anaconda3 envs mpls311 lib python3 9 site packages prefect engine py line 1438 in orchestrate task run result = await task fn args kwargs File home mike anaconda3 envs mpls311 lib python3 9 site packages prefect gcp bigquery py line 406 in bigquery load cloud storage result = await to thread run sync partial load File home mike anaconda3 envs mpls311 lib python3 9 site packages anyio to thread py line 33 in run sync return await get asynclib run sync in worker thread File home mike anaconda3 envs mpls311 lib python3 9 site packages anyio backends asyncio py line 877 in run sync in worker thread return await future File home mike anaconda3 envs mpls311 lib python3 9 site packages anyio backends asyncio py line 807 in run result = context run func args File home mike anaconda3 envs mpls311 lib python3 9 site packages prefect gcp bigquery py line 40 in result sync result = func args kwargs result File home mike anaconda3 envs mpls311 lib python3 9 site packages google cloud bigquery client py line 2375 in load table from uri load job begin retry=retry timeout=timeout File home mike anaconda3 envs mpls311 lib python3 9 site packages google cloud bigquery job base py line 693 in begin api response = client call api File home mike anaconda3 envs mpls311 lib python3 9 site packages google cloud bigquery client py line 813 in call api return call File home mike anaconda3 envs mpls311 lib python3 9 site packages google api core retry py line 349 in retry wrapped func return retry target File home mike anaconda3 envs mpls311 lib python3 9 site packages google api core retry py line 191 in retry target return target File home mike anaconda3 envs mpls311 lib python3 9 site packages google cloud http init py line 494 in api request raise exceptions from http response response google api core exceptions NotFound 404 POST Not found Dataset mpls 311 mpls 311 staging```

Mike Cole · Answer

Figured this out after more troubleshooting and including what I ve found if it helps others later I tracked the issue down to locations in GCP My existing datasets are in the us central1 region When I created a new test database in location for US the bigquery load cloud storage could successfully find the dataset Looks like the prefect gcp functions default to Location=US so I m still not sure how bigquery create table could create a table in my us central1 region but not the bigquery load cloud storage function

Mike Cole · Answer

If I call the bigquery load cloud storage function with a blank parameter for Location it ends up finding my dataset in us central1 and appends data from a file in GCS to the BQ dataset table Script below for reference that works ``` from prefect import flow from prefect gcp import GcpCredentials from prefect gcp bigquery import bigquery load cloud storage flow def example bigquery load cloud storage flow gcp credentials = GcpCredentials project= mpls 311 job config = write disposition WRITE APPEND source format PARQUET result = bigquery load cloud storage dataset= mpls 311 staging table= stg mpls 311data uri= <gs data lake mpls 311 data pq mpls 311data 2023 parquet> gcp credentials=gcp credentials job config=job config location= return result example bigquery load cloud storage flow ```