BigQuery flattens the selection in a table with GROUP BY even with the "noflatten_results" flag
I have a table with duplicate entries. I want to remove them. I created a column named "hash_code" which is just the sha1 hash of all columns. The duplicated lines will have the same hashcode. Everything is fine, unless I tried to create a new table with a query containing GROUP BY. My table is of the RECORD datatype, but the newly created table flattens it even when I told it not to flatten. It looks like the GROUP BY and the "-noflatten_results" flag does not put much good into it.
Here's an example of the command line I ran on:
bq query --allow_large_results --destination_table mydataset.my_events --noflatten_results --replace
"select hash_code, min(event) as event, min(properties.adgroup_name) as properties.adgroup_name,
min(properties.adid) as properties.adid, min(properties.app_id) as properties.app_id,
min(properties.campaign_name) as properties.campaign_name from mydataset.my_orig_events group each
by hash_code "
In the example above, the properties are the RECORD data type with nested fields. The resulting table has no properties as the RECORD data type. Instead, it translated properties.adgroup_name to properties_adgroup_name, etc.
Any way to get BigQuery to treat the result set as RECORD and not flatten in the GROUP BY?
Thank!
source to share
There are several known cases where query results can be flattened despite a floating query result.
- Requests containing an offer
GROUP BY
- Requests containing an offer
ORDER BY
- Selecting a nested field with a flat alias (for example
SELECT record.record.field AS flat_field
). Note that this only aligns the specific field with the applied alias and only aligns the field if it and its parent records are not duplicated.
The BigQuery query engine always flattens the query results in these cases. As far as I know, there is no workaround for this behavior other than removing these suggestions or aliases from the request.
source to share