Generate Avro schema from AnVIL schema

[nadove-ucsc]

Description: #6109 (comment)

[dsotirho-ucsc]

Assignee to consider next steps.

[hannes-ucsc]

The current implementation generates the schema dynamically, based on the replica documents in the manifest currently being generated. This means that the schema could be different between manifests, depending on which files match the filter and which parts of the schema are expressed by the replicas connected to those files. This could lead to missing tables and missing columns in the workspace the manifest is exported to.

While that may be considered by some a cosmetic issue or even a feature, entity relations can only be reliably expressed in the AvroPFB schema when deriving it from the foreign-key relationships in the expressed AnVIL schema. IOW, this is blocking #6066.

[hannes-ucsc]

For demo, perform two small handovers (the fewer rows, the better) for two different datasets, into two distinct workspaces. Show that the table schema is identical in both workspaces.

[nadove-ucsc]

@hannes-ucsc: "This was successfully demoed, albeit with different instructions. We had to look at the PFB schema and contents because Terra appears to drop empty tables and empty columns. An empty column is a scalar nullable column that is null for all rows. Assignee to contact the Broad to inquire as to whether this is intentional because it more or less counteracts our intent behind this issue."

[nadove-ucsc]

Screencaps of two Terra workspaces showing inconsistent tables and columns:

Manifests reproducible in catalog anvil6 using the filters
{"biosamples.anatomical_site": {"is": ["Umbilical Cord"]}}
and
{"biosamples.anatomical_site": {"is": ["Not Listed"]}}
respectively.

Creating manifests with the same filters via the Swagger UI and inspecting the resulting PFB files on the command line confirmed that their schemas were identical.

The tables missing from the first workspace (anvil_activity and anvil_diagnosis) are defined in that manifest's schema:

$ pfb show -i umbilical-cord.avro schema | jq '.[] | select(.name == "anvil_activity")'
{
  "type": "record",
  "name": "anvil_activity",
  "fields": [
    {
      "namespace": "anvil_activity",
      "name": "activity_id",
      "type": "string"
    },
    {
...

And the missing columns from the biosample table are present in both the schema and the biosample entities:

$ pfb show -i umbilical-cord.avro schema | jq '.[] | select(.name == "anvil_biosample") | .fields[].name'
"anatomical_site"
"apriori_cell_type"
"biosample_id"
"biosample_type"
"datarepo_row_id"
"disease"
"donor_age_at_collection_lower_bound"
"donor_age_at_collection_unit"
"donor_age_at_collection_upper_bound"
"donor_id"
"part_of_dataset_id"
"source_datarepo_row_ids"

$ pfb show -i umbilical-cord.avro | jq 'select(.name == "anvil_biosample") | .object | keys'
[
  "anatomical_site",
  "apriori_cell_type",
  "biosample_id",
  "biosample_type",
  "datarepo_row_id",
  "disease",
  "donor_age_at_collection_lower_bound",
  "donor_age_at_collection_unit",
  "donor_age_at_collection_upper_bound",
  "donor_id",
  "part_of_dataset_id",
  "source_datarepo_row_ids"
]
[
...

[hannes-ucsc]

I've alerted the BI about this on Slack. From the Azul perspective, this feature is complete and the issue can be closed, after reassigning the implementor and moving it to Stable.