Open GoogleCodeExporter opened 8 years ago
GoogleCodeExporter
commented
8 years ago After further investigation, I was able to get the exact query from one of
these failed jobs to work in the BigQuery UI if I overwrote the table, but got
the same "Internal error" if I tried to append to an existing table...Original comment by de...@derekperkins.com on 16 Sep 2015 at 4:42
GoogleCodeExporter
commented
8 years ago Apologies, it looks like our oncall missed this report. I'd recommend using
Stack Overflow for support in the future; please just tag your question with
"google-bigquery". Stack Overflow is monitored much more regularly by BigQuery
staff.
I looked up a number of the job ids from your initial report and did not find
any of them in our system; this implies that the jobs were not successfully
committed.
Can you describe the method you were using to launch these jobs and get the job
ids?
Original comment by thomasp...@google.com on 24 Nov 2015 at 6:25
GoogleCodeExporter
commented
8 years ago (Note, though, that this issue tracker is better than StackOverflow for bugs or
service issues, since StackOverflow is more intended for "how-to" programming
questions. Both are monitored by Google engineers.)Original comment by jcon...@google.com on 1 Dec 2015 at 4:48
GoogleCodeExporter
commented
8 years ago Quick update - we changed the way some of our job metadata was stored; I have
found your job. They were successfully committed.
Your queries were triggering an internal error involving query schema
generation in our system. I'll create a query to repro since we've fixed a
couple of similar schema issues recently, and get back to you with an update.Original comment by thomasp...@google.com on 30 Dec 2015 at 7:18
GoogleCodeExporter
commented
8 years ago I think we've fixed how the internal errors for these queries are mapped.
For example, a trimmed version of the query from job
nozzle-app:job_NF2myDsMtSGxz4u-ISC5OYB1iUA now returns an actionable error
message "Error: Cannot output multiple independently repeated fields at the
same time. Found Results_Meta_Sitelinks_URL_DomainID and
Results_Meta_ExpandedSitelinks_Rank".
Resolving issue, please re-open if you have any other issues with this.Original comment by thomasp...@google.com on 30 Dec 2015 at 8:41
GoogleCodeExporter
commented
8 years ago So the error that you fixed is not the error that caused them to fail. IIRC,
they were all queries being selected into new tables with flattenResults =
false. My problem was that the schema I was selecting into didn't match the
'required' attributes of the original. After running the query with the GROUP
BY, and not specifying a schema, I ran a diff between my expected schema and
the actual output schema and found that seemingly random fields were
marked/unmarked as required.Original comment by de...@derekperkins.com on 30 Dec 2015 at 9:29
GoogleCodeExporter
commented
8 years ago Thanks for the clarification! I tried the query again with a destination table
set, and without "flatten results", and was able to generate a result
successfully.
We did fix a few issues with schema generation in November, so I wouldn't be
surprised if this issue was addressed by one of our bugfixes. That said, I
only unioned 4 tables and used about a dozen keyword + search id filters, so
the result set was very small, so please do let me know if you run into the
same issue when retrying one of the full queries.Original comment by thomasp...@google.com on 30 Dec 2015 at 10:03
GoogleCodeExporter
commented
8 years ago Was there a schema associated with those job IDs? If so, did your test work
using that schema? The jobs ended up working in Sept after I let the query
decide its own schema. If it works with the schema, that's a way to verify if
the changes you made in Nov fixed the original issue.Original comment by de...@derekperkins.com on 30 Dec 2015 at 10:21
GoogleCodeExporter
commented
8 years ago I don't have write access to your tables, so my query was writing to a new
table. I can verify that there are a few fields that are marked REQUIRED in
the generated schema.
How was the schema of your original destination table generated ? Did you
manually create it with all of the fields set to NULLABLE (or REPEATED) ? I
can create a similar schema if so and retry the load.
Original comment by thomasp...@google.com on 30 Dec 2015 at 10:40
GoogleCodeExporter
commented
8 years ago I verified that if I create a table with all NULLABLE / REPEATED fields and run
the query with "allow large results" + "noflatten" + "append", then I get an
unhelpfully opaque error.
So, I'll address two things here:
1. Plumb the internal error message about schema mismatch through to the client
2. See if we can allow writing REQUIRED source fields to NULLABLE destination
columns
I think we're on the same page now?Original comment by thomasp...@google.com on 30 Dec 2015 at 10:54
GoogleCodeExporter
commented
8 years ago Yes, I think we're on the same page. I could probably dig up the exact schema
from my git history if need be. IIRC, there were some required fields that
turned into nullable fields that threw an error, and some previously nullable
fields that were marked as required. My expectation is that it would read from
and match the source schema.Original comment by de...@derekperkins.com on 31 Dec 2015 at 4:01
GoogleCodeExporter
commented
8 years ago I've filed internal tracking bugs for these two issues, we will work on
resolving them.Original comment by thomasp...@google.com on 4 Jan 2016 at 10:17
GoogleCodeExporter
commented
8 years ago Hi Derek,
I've made a change to address #1 - we will return an error message that says
"Output schema specified in the query request is not compatible with the query
results: Field 'Keyword' is incompatible with the table schema: Label mismatch:
actual 'LABEL_REQUIRED' vs. expected 'LABEL_OPTIONAL'"
for this query; that will at least help to shed light on where the problem is.
That change will go out with our next deployment.
Re: #2 - one of my colleagues dug into this a bit deeper : the problem is that
the source tables [rankings.*] have several RECORD columns listed as REQUIRED
(Keyword, SearchDetails, SearchDetails.Summary, etc). We do have the ability
to write REQUIRED source *leaf* fields to NULLABLE destinations, but due to
some implementation details, we can't currently relax a REQUIRED source
*record* into a NULLABLE destination. If you were to change the RECORDs in the
source table from REQUIRED to NULLABLE, my colleague says it should work.
We do realize that this operation (writing a REQUIRED record to a NULLABLE
destination) makes sense semantically, so we will investigate other solutions
in the future.Original comment by thomasp...@google.com on 7 Jan 2016 at 10:32
Original issue reported on code.google.com by
de...@derekperkins.comon 16 Sep 2015 at 3:20