Extensions: firestore-bigquery-export: Schema view for maps as dictionaries

[iislucas]

[REQUIRED] Step 2: Extension name

This feature request is for extension: firestore-bigquery-export, and in particular for the GENERATE_SCHEMA_VIEWS.md

What feature would you like to see?

In my application, we use dynamic maps (i.e. dictionaries) where we don't know the keys for the data in advance - they are defined by user-actions in the application (e.g. they might be user-ids). This means we can't list the keys in the schema.

It would be great to be able to treat a map as a pair of arrays. One for the map's keys and one for the maps values. That way we can do all the usual great things with bigquery.

How would you use it?

We'd be able to do things like count the number of owners of a keen (we're using it for staykeen.com) and get summary statistics.

[russellwheatley]

Seems like a potential feature request, @iislucas, thanks for the suggestion.

[jakebern]

Running into a similar issue. Any suggested workaround for this right now eg. JSONifying the map into a string, then putting in a single column?

[mustafaekim]

I need exact same thing. The most important column of the table is a dictionnairy. I can neither "map" it, nor store as a string

[mustafaekim]

It looks like storing some part of the document as a JSON will be the raw/basis solution for this because there are very-very different object structures and I don't think that this JSON Schema approach will cover them.

[nwparker]

I ran into this today. I also think this should be added to the generator, so thanks for opening!

To get some work started there, I've come up with a working solution (this can be coded by hand and adopted later by someone -- sorry, I might not get around to it myself but I still wanted to share)

This will produce an Array with the struct value being your data values along with an added "key" value.

1. Create a custom json_extract_entries function:

CREATE OR REPLACE FUNCTION `fb-proj.my_table.json_extract_entries`(json STRING) RETURNS ARRAY<STRUCT<key STRING, value STRING>> LANGUAGE js AS """
return json ? Object.entries(JSON.parse(json)).map(([k,v]) => ({key: k, value: JSON.stringify(v)})) : [];
""";

1. Use it like so where required:

ARRAY(
  SELECT
    struct(
      -- This key gets added (make sure it doesn't collide)
      d.key as key,
      -- Add in the rest of your "normal" extraction that the generation script can produce
      JSON_EXTRACT_SCALAR(d.value, '$.status') AS status,
      `fb-proj.my_table.firestoreTimestamp`(JSON_EXTRACT(d.value, '$.myDict.startTime')) AS startTime,
      `fb-proj.my_table.firestoreBoolean`(JSON_EXTRACT_SCALAR(d.value, '$.myDict.myBool')) AS myBool
    ) FROM UNNEST(`fb-proj.my_table.json_extract_entries`(JSON_EXTRACT(data, '$.myDict'))) as d
) as myDict

[i14h]

@Salakar @russellwheatley what do you think about this feature request?

[russellwheatley]

@i14h, I think it might be a good idea to allow a json type in the schema perhaps. This would also cover this feature request: https://github.com/firebase/extensions/issues/408:

Example Schema:

{
  "fields": [
    {
      "name": "map_example",
      "type": "json"
    }
  ]
}

map_example objects taken from Firestore would be stringified and put in a BigQuery column.

[jhuleatt]

@russellwheatley that sounds like the way to go.

I think we need to think of a term for "type" that reflects that this is still a Firestore map, just a special kind. dynamic_map? untyped_map? stringified_map?

Anyway, we can figure that out in a PR

[devth]

Would love to see support for this. Until then we can't use any of fs-bq-schema-views because it doesn't propagate the raw JSON data for use with BigQuery's JSON_EXTRACT capabilities.