Google BigQuery generates an error when you query nested data. For queries that reference fields that are at different levels of detail, BigQuery automatically flattens the table data before returning it. Tableau users may encounter such errors when building complex visualizations, viewing underlying data, or creating an extract.
The BigQuery error is made more difficult to diagnose because in identifying the conflicting levels of detail, Google may identify the fields differently than in the table definition. Specifically, the period characters '.' that qualify components in a field name are converted to underscores '_'. So where there are the following two fields in the data source:
- payload.comment.id
- payload.comment_id
Google converts the first field name to be the same as the second field name.
A possible workaround is to hide any fields in the Data window that are natively stored in BigQuery as nested, repeated fields. Once these fields are hidden in Tableau, they will never be referenced in queries, such as those used to view underlying data or create an extract.
Background
BigQuery supports data sets that contain multiple levels of detail, which are represented as nested, repeated fields. While BigQuery can automatically handle multiple levels of detail that exist within a single hierarchy of nested fields, different hierarchies cannot be used in combination. For more details on these restrictions, see the Google BigQuery documentation on this topic: Nested and Repeated Data.