Is there a way to avoid the data type conversion from STRING to STRUCT<string STRING, text STRING, provided STRING> for Datastore imports to BigQuery?

user_17107086 0
Viewed 69

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?

google-bigquery google-cloud-datastore
1 Answers

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

Source file defines the schema

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.

user_112797690
Related