BigQuery NEST() 返回“错误:发生内部错误" [英] BigQuery NEST() returns 'Error: An internal error occurred'

问题描述

我正在尝试在 BigQuery 用户界面(而不是 API)中嵌套一个字段，并在尝试输出到表格而不展平时不断遇到错误:

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.

我正在使用 NEST() 函数，我试过了这在公共莎士比亚数据集上并继续得到相同的错误.

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

我的工作 ID 是:realself-main:bquijob_1bfb8310_153583ecbc2

My Job ID is: realself-main:bquijob_1bfb8310_153583ecbc2

推荐答案

关于如何在 BigQuery 中生成重复的文件/记录，有很多关于 SO 的问题而且，有许多不同的答案 - 范围

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

来自:NEST 与 unflatten 结果不兼容 - 如
未展平结果时 NEST 的内部错误

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

to:一些使用 JS UDF 解决此问题的解决方案，如
在 BigQuery 中嵌套多个重复字段;
创建一个带有记录类型列的表 ;
创建一个列类型为 RECORD 的表

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

但令人惊讶的是 - 最近，我发现了如何让 NEST() 几乎按预期工作！

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

试试下面的技巧

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

注意，你必须在Allow Large Results打开和Flatten Results关闭的情况下将结果写入表格

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

这篇关于BigQuery NEST() 返回“错误:发生内部错误"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！