Fix Bug: Differentiate between the Nested and Repeated fields for bigquery when executing datacontract import.

[gregoireAGBG]

Hello,
So in version of datacontract-cli 0.10.24 and preivous versions, there is type mismatch in the schema that is being imported from bigquery for REPEATED RECORD:

when executing
datacontract import --format biquery --biquery-project my_gcp_project --biquery-dataset my_dataset --bigquery-table my_table >> dc_my_gcp_project_my_dataset_my_table.yml

inside my dc_my_gcp_project_my_dataset_my_table.yml the following will be generated for the my_bq_col (which is a nested repeated field) :

  my_bq_col:
    type: object
    required: false
    fields:
      subfield1:
        type: string
        required: true
      subfiel2:
        type: string
        required: false
      subfield3:
        type: string
        required: false

---

After adding the server informations, a manual modification is needed in order to be able to execute :
datacontract test dc_my_gcp_project_my_dataset_my_table.yml --logs

the correct adaptation is as follow:

  my_bq_col:
    type: array
    items:
      type: record
      fields:
        subfield1:
          type: string
          required: true
        subfield2:
          type: string
          required: false
        subfield3:
          type: string
          required: false

---

Below you can find the ouput of executing datacontract test dc_my_gcp_project_my_dataset_my_table.yml --logs
Before the modification:
data contract is invalid, found the following errors:
my_bq_col Check that field my_bq_col has type STRUCT<subfield1 STRING, subfield2 STRING, subfield3 STRING>: Type Mismatch, Expected Type: STRUCT<subfield1 STRING, subfield2 STRING, subfield3 STRING>;
Actual Type: ARRAY<STRUCT<subfield1 STRING, subfield2 STRING, subfield3 STRING>>

After the modification:
passed │ Check that field my_bq_col has type ARRAY<STRUCT<subfield1 STRING, subfield2 STRING, subfield3 STRING>>│ my_bq_col│ │

Explanation:

• So currently datacontract-cli import --format biquery is handling the nested repeated fields as nested fields :
  Nested fields (or Struct ) are defined by: type": "RECORD", "mode": "NULLABLE",
  while nested repeated fields (or Array) are defined by: type": "RECORD", "mode": "REPEATED",
  RECORD type seems to be handled as a unique case and not have different behavior depending on the mode.

To resolve the issue you should check the mode of the field and handle the mode "REPEATED" as described above to enable a valid datacontract schema check.

Best regards.

[gregoireAGBG]

Ok i couple notes on how to fix that:

1. the way to fix nested field issue be replacing if condition at : https://github.com/datacontract/datacontract-cli/blob/main/datacontract/imports/bigquery_importer.py#L138
   with

---

    if field.get("type") == "RECORD":
        if field.get("mode") == "REPEATED" :
            imported_fields[field_name].type = "array"
            imported_fields[field_name].items = Field(type="record",fields=import_table_fields(field.get("fields")))
        else :
            imported_fields[field_name].type = "object"
            imported_fields[field_name].fields = import_table_fields(field.get("fields"))

---

2. the JSON type is not handled correctly as well should be fixed to make the datacontract test command work for json object which look like that after executing datacontract import:

---

    optimization_details:
        type: object
        required: false

---

• as this dict does not contain a fields key adding "JSON" as fallback return value resolves the issue. so https://github.com/datacontract/datacontract-cli/blob/main/datacontract/export/sql_type_converter.py#L338
  is now :

---

if field_type.lower() in ["object", "record", "struct"]:
    nested_fields = []
    if not field.fields:
        return "JSON"
    for nested_field_name, nested_field in field.fields.items():
        nested_field_type = convert_type_to_bigquery(nested_field)
        nested_fields.append(f"{nested_field_name} {nested_field_type}")

---

3. seems that the type
   ARRAY<STRING> and ARRAY<INTEGER> are not currently supported by datacontract-cli
   i have a double nested element: ARRAY<STRUCT<... , steps ARRAY<STRUCT< kind STRING,substeps ARRAY<STRING>>>>>

---

        steps:
          type: array
          required: false
          items:
            type: record
            fields:
              kind:
                type: string
                required: false
              substeps:
                type: string
                required: false

---

substeps is type: STRING mode: REPEATED (thus the ARRAY<STRING>)
this information is lost when importing using datacontract import , and thus when doing the datacontract test command the check schema is :
ARRAY<STRUCT<... , steps ARRAY<STRUCT< kind STRING,substeps STRING>>>>
instead of
ARRAY<STRUCT<... , steps ARRAY<STRUCT< kind STRING,substeps ARRAY<STRING>>>>>
Same for INTEGER fields meaning (ARRAY<INTEGER>)

Best regards.

[jochenchrist]

Thanks for opening this issue! Would you be open to contributing a pull request for this? We’d be happy to support you through the process if needed.

[codeagent-art]

Hello @jochenchrist yes i can do the pull request ( i have it locally but am not allowed to push to the repository) can you add me as a contributor?
Btw this is original author (gregoireAGBG ) had some issue with the other account.

[codeagent-art]

Hello @jochenchrist could you grant me contributor access ?
still getting access denied on the repository.
We would like to integrate the fix inside your repository so that we could use it in production.
`❯ git push --set-upstream origin fix/Issue_737
ERROR: Permission to datacontract/datacontract-cli.git denied to codeagent-art.
fatal: Could not read from remote repository.

Please make sure you have the correct access rights
and the repository exists.
~/Develop/Source/github/datacontract-cli fix/Issue_737 ❯ `

Kind regards. Gregoire.

[jochenchrist]

Please create a fork to your Github Account, then you can create a Pull Request.
Direct Repo Access is for Maintainers, only.

[gregoire-lab]

Hello so i have been doing some test of my fork and have stumble onto another issue:

1. for string type with a fixed length 42 or else: the datacontrat import will generate: a column entry inside the datacontract as follows:

      maxlength_required_string_field:
        type: string
        required: true
        description: a required string field with a maximum length
        maxLength: 42

when doing the datacontract test an error occured:
maxlength_string_field Check that field maxlength_string_field has type STRING: Type Mismatch, Expected Type: STRING;
Actual Type: STRING(42)

so modifying the column to :

     maxlength_required_string_field:
        type: string
        required: true
        description: a required string field with a maximum length
        maxLength: 42
        config:
          bigqueryType: STRING(42)

resolve the issue when testing .

same occurs for numeric and bignumeric fields with precision and scale .

      decimal_field:
        type: numeric
        required: false
        description: a simple decimal field with precision 5 and scale 3
        precision: 5
        scale: 3
        config:
          bigqueryType: NUMERIC(5, 3)

i can add the rendering of the config bigqueryType inside the bigquery_importer but maybe this should be handled inside the type_converter when doing the export for testing.
what is your recommendation on that part ?

[gregoire-lab]

Hello any update on this ? we will be deploying on central orchestrator soon and would be nice to know what should be fixed in the Pull Request #769 (beside rebasing to latest version).
For context the fork based on 0.10.24 is handling the nested fields and complex schema properly.
Cheers Gregoire.