REPEATED + REQUIRED field in BigQUery schema

3
votes

According to the BigQuery schema documentation BigQuery supports the following modes for columns: Nullable, Required, and Repeated. Is there any way to make field (with type Record) Required and Repeated at the same time?

At the moment I have:

enter image description here

insert into test (id) values('333')

enter image description here

1
google-bigquery
Arrays cannot be null (only empty) within BigQuery tables, so it doesn't make sense to support the NOT NULL annotation for them. If the question is how to enforce that arrays have at least one element, this is not possible currently. - Elliott Brossard

1 Answers

1
votes

For arrays REQUIRED is not supported. See DDL reference:

(...) supports an optional NOT NULL constraint for types other than ARRAY.

column_schema :=
   {simple_type [NOT NULL] |
    STRUCT<field_list> [NOT NULL] |
    ARRAY<array_element_schema>}
   [OPTIONS(column_option_list)]

So, it's only supported for simple types and STRUCT/RECORD

You could start a feature request, though: https://issuetracker.google.com/issues?q=componentid:187149