🐛 firestore-bigquery-export Using the fs-bq-schema-views script for nested arrays of maps

[eigidi]

This feature request is for extension: firestore-bigquery-export, when using the fs-bq-schema-views script

What feature would you like to see?

When using the Firestore to BigQuery extension or the firestore-bigquery-export command, two types of tables are generated, prefix_changelog and prefix_raw, that cannot be used as such. In order to create views, the script fs-bq-schema-views uses user defined json schemas to create views from raw tables. All is explained here.

However, raw data can be pretty complex and the script doesn't cover all cases. In particular, nested objects, like arrays made of maps. This issue also discussed it, but the problem was not solved. You can check the related merge request here. I have tested the format with the use of array_map, see user_complex.json, which did not work at all, and the complexSchema.json, but it only resulted in the json object.

How would you use it?

The feature supports typed arrays, but it does not seem to work for arrays of maps, like in this example:

{
  "fields": [
    {
      "name": "nickname",
      "type": "string"
    },
    {
      "name": "location",
      "type": "geopoint"
    },
    {
      "name": "profile",
      "type": "map",
      "fields": [
        {
          "name": "birthDate",
          "type": "timestamp"
        },
        {
          "name": "hand",
          "type": "string"
        },
        {
          "name": "levels",
          "type": "array",
          "fields": [
            {
              "name": "date",
              "type": "timestamp",
              "column_name": "levelDate"
            },
            {
              "name": "value",
              "type": "number",
              "column_name": "levelValue"
            }
          ]
        }
      ]
    }
  ]
}

[emilioiz]

This would be an extremely useful update! The previous issues and PR say to have address it and inside the code base I also see the "arrays of maps" work completed but unable to use when assigning a type of array_map to the schema.

[jakelivingstonaudiochuck]

Same here. When I generate a schema using the array_map type, It seems like its failing to locate the corresponding UDF and resulting with "undefined AS columnname" undefined AS slides for example