Grouping By Arrays and Structs not supported in Bigquery

[sowusu-ba]

Describe the bug
I tried data-diff for the first time and I got this error.

Got error: 400 Grouping is not defined for arguments of type ARRAY<STRUCT<id STRING

My table has arrays and structs. The command I used is

dbt run --select table_a && data-diff --dbt

I think what's happening is that the SQL generated by data-diff to compare the two tables is unaware that a field is an array of structs. I copied the SQL into the BigQuery console and I found the error here:

   CASE
      WHEN `tmp1`.`array_field` IS DISTINCT FROM `tmp2`.`array_field` THEN 1
    ELSE
    0
  END
    AS `is_diff_array_field`,

[dongchris]

+1, same error

[leoebfolsom]

@sowusu-ba @dongchris we are looking into this, and hope to have a fix shortly--please stay tuned! Thank you for trying out this out and bearing with us as we smooth out any issues.

[brandon-segal]

For BigQuery Could we suggest updating the select_table_schema method to run the following?

SELECT field_path as column_name, data_type, 6 as datetime_precision, 38 as numeric_precision, 9 as numeric_scale 
            f"FROM `{project}`.`{schema}`.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS "
            f"WHERE table_name = '{name}' AND table_schema = '{schema}'"```

[dongchris]

@leoebfolsom any update / ETA on this?

[i-zaykov]

+1 here

[leoebfolsom]

@i-zaykov @dongchris @brandon-segal I've reached out to the team to determine when we may be able to prioritize implementing this fix.

Not to say any of you should take this on, but just as an FYI, we do accept community contributions if anyone felt inspired to spin up a PR. I mention this because I recognize it's been a while (longer than I originally anticipated), and I'm not positive when we'll be able to prioritize this update (but as I mentioned, I've inquired).

[github-actions]

This issue has been marked as stale because it has been open for 60 days with no activity. If you would like the issue to remain open, please comment on the issue and it will be added to the triage queue. Otherwise, it will be closed in 7 days.

[leoebfolsom]

Hi all, just a note that a simplistic implementation was shipped in #533 .

I'd be curious to hear if this meets the need for those of you watching the issue, and if it may be closed, or not. Please note some of the caveats mentioned in the description of the PR.