"Not found: table" for new bigquery table
I am using python sdk to create a new bigquery table:
tableInfo = {
'tableReference':{
'datasetId':datasetId,
'projectId':projectId,
'tableId':targetTableId
},
'schema':schema
}
result = bigquery_service.tables().insert(projectId=projectId,
datasetId=datasetId,
body=tableInfo).execute()
The variable result
contains the created table with etag,id,kind,schema,selfLink,tableReference,type
- so I am assuming the table was created correctly.
After that I even get the table when I call bigquery_service.tables().list(...)
The problem is this: When pasting right after this, I still (often) get the error: Not found: MY_TABLE_NAME
My function call looks like this:
response = bigquery_service.tabledata().insertAll(
projectId=projectId,
datasetId=datasetId,
tableId=targetTableId,
body=body).execute()
I even repeated the insertion several times with 3 seconds of sleep between attempts. Any ideas?
My projectId stylight-bi-testing
There were many failures between 10:00 and 12:00 (UTC time)
source to share
As per your answers to my question regarding using NOT_FOUND as an indicator for table creation, this is intended (albeit admittedly somewhat frustrating) behavior.
The insert stream path caches information about the tables (and user authorization to insert into the table). This is due to the supposed high quality of the QPS API. We also cache some negative responses to protect buggies or abusive clients again. One of these cached negative responses is the absence of a target table. We've always done this on every machine, but recently added an extra centralized cache so that all machines see the negative cache result almost immediately after the first NOT_FOUND response returns.
In general, we recommend that table creation does not occur on a row with insert requests, because on a system that issues thousands of QPS inserts, a missing table can lead to thousands of table creation operations that may be taxed on our system. Instead, if you know in advance the possible set of tables, we recommend a batch process that creates the tables before using them as a streaming destination. If your target tables are more dynamic in nature, you may need to implement a delay after the table is created.
Sorry for the difficulty. We really hope to solve this problem, but we have no time frame for this.
source to share
Sean / Niels, even I have the same problem. I try to stream attach first, and if that fails, I go ahead and create a table. But after creating the table, streaming insert still does not work for a while (30 seconds to 4 minutes) with the "Table not found" error. After this interval, the same stream inserts go through without any problems.
source to share