我们每天都会自动将数据存储备份加载到 BigQuery 以进行进一步分析并覆盖该表。
当在 BigQuery 中导入至少一个包含长文本实体的数据存储种类时，该字段会自动转换为 STRUCT 而不是 STRING< /code> 字段与所有其他文本/字符串字段一样。然后，这会更改 BigQuery 表的架构，并使任何进一步的处理或分析变得非常困难，因为需要调整查询来解决这一问题。我们无法控制数据存储端的文本长度，因此我们需要找到一种方法至少稳定 BigQuery 端的架构。
关于如何优雅地处理这个问题有什么想法吗？
有什么方法可以避免这种转换，这样 BigQuery 表的架构就不会改变？
We are automatically loading Datastore Backups to BigQuery for further analysis overwriting the table every day.
When a Datastore Kind with at least one Entity with long text is imported in BigQuery, that field is automatically converted to a STRUCT<string STRING, text STRING, provided STRING> instead of a STRING field like all the other text/string fields. This then changes the schema of the BigQuery table and makes any further processing or analysis really hard as queries need to be adapted to account for this. We cannot control the length of text on the Datastore side, so we need to find a way to at least stabilize the schema on the BigQuery side.
Any idea on how to deal with this elegantly?
Any way this conversion can be avoided so the schema of the BigQuery table does not change?
发布评论
评论（1）
在 BigQuery 中无法将架构设置为从数据存储区导出的加载作业。这意味着模式将始终从数据中推断出来。例如，如果您尝试通过 UI 加载它，您将看到一条消息：
In this 链接 您可以了解 Datastore 和 BigQuery 之间类型转换的工作原理。
尝试使用视图作为最终表或创建计划查询以在加载表时读取表并将结果保存在具有正确架构的另一个表中。
Setting a schema to a Load Job from a Datastore export is not possible in BigQuery. It means that the schema will always be inferred from the data. If you try to load it through the UI for example, you will see a message saying
In this link you can find how the type conversion works between Datastore and BigQuery.
Try to use a View as the final table or create a scheduled query to read your table when its loaded and save the results in another table with the right schema.