BigQuery NEST() returns 'Error: An internal error occurred'

Question

I'm trying to nest a field in the BigQuery UI (not the API) and continually get hit with an error when trying to output to a table without flattening:

Error: An internal error occurred and the request could not be completed.

I'm using the NEST() function and I've tried this on the public Shakespeare dataset and continue to get the same error.

SELECT corpus, NEST(word) FROM [publicdata:samples.shakespeare] GROUP BY 1

My Job ID is: realself-main:bquijob_1bfb8310_153583ecbc2

score 4 · Accepted Answer · edited May 23 '17 at 11:59

There were tons of question on SO related to how to generate repeated fileds/records in BigQuery And, there were many different answers - ranging

from: NEST is not compatible with unflatten result - as in
Internal error on NEST when not flattening results

to: some solutions to address this issue using JS UDF as in
Nest multiple repeated fields in BigQuery ;
Create a table with Record type column ;
create a table with a column type RECORD

there are more - you can search

But surprisingly enough - recently, I found how to make NEST() work almost as it supposed to work!

Try below to see the trick

SELECT corpus, words 
FROM (
  SELECT corpus, NEST(word) AS words
  FROM [publicdata:samples.shakespeare] 
  GROUP BY 1
) AS a
CROSS JOIN (SELECT 1) AS b

Note, you have to write result to table with Allow Large Results on and Flatten Results off

The trick worked great! I've used the cross join a few other times to trick BQ, didn't think of trying it here.. — pkrengel, Mar 15 '16 at 14:45
i found this after quite heavy experimentation with nest() as it is one of the weakest yet demanded feature — Mikhail Berlyant, Mar 15 '16 at 16:03

BigQuery NEST() returns 'Error: An internal error occurred'

1 Answers1

Linked