Firestore -> BigQuery -> Metabase issue with joining and displaying foreign key name value

Hello Metabase Community,

We have a web application built on Google Cloud Firestore in Datastore Mode (NoSQL Database) for data storage. We would love to use Metabase so that our charity staff can question the data, build dashboards, etc.

It looks as if efforts to write a Metabase Firestore driver were redirected towards using BigQuery instead which makes a lot of sense.

salsakran commented on Mar 15, 2017:

For what it's worth, for firebase specifically, we'd strongly suggest getting your database and/or analytics events into BigQuery somehow vs coding against the database rest api directly.

I'm leaving this issue open in case to not squash anyone's motivation, but even if it does get addressed in a PR, I'd still recommend using BQ =)

We have created a data set in BigQuery with tables populated from Firestore. The schema is inferred for a Firestore export so we don't have much control here.

Metabase connected to BigQuery using a service account and for a moment it looked as if everything was good to go. However we have very quickly hit an issue where Metabase can't look up the display values to show the name instead of the foreign key and we are unable to perform any joins when building Questions or Models in Metabase.

My guess is that because BigQuery creates a RECORD data type for the key – with nested fields for each piece of information – Metabase is struggling to make the joins.

Key property Description BigQuery data type
__key__.app The Firestore app name. STRING
__key__.id The document's ID, or null if __key__.name is set. INTEGER
__key__.kind The document's collection ID. STRING
__key__.name The document's name, or null if __key__.id is set. STRING
__key__.namespace Firestore does not support custom namespaces. The default namespace is represented by an empty string. STRING
__key__.path The path of the document: the sequence of the document and the collection pairs from the root collection. For example: "Country","USA", "PostalCode", 10011, "Route", 1234. STRING

Metabase sees the keys coming through from BigQuery as a blob, for example:

Foreign key:

[FieldValue{attribute=PRIMITIVE, value=}, FieldValue{attribute=PRIMITIVE, value=g~app-name}, FieldValue{attribute=PRIMITIVE, value="Country", 5145599846383619}, FieldValue{attribute=PRIMITIVE, value=Country}, FieldValue{attribute=PRIMITIVE, value=null}, FieldValue{attribute=PRIMITIVE, value=5145599846383619}]

Primary key

[FieldValue{attribute=PRIMITIVE, value=}, FieldValue{attribute=PRIMITIVE, value=g~app-name}, FieldValue{attribute=PRIMITIVE, value="Country", 5145599846383619}, FieldValue{attribute=PRIMITIVE, value=Country}, FieldValue{attribute=PRIMITIVE, value=null}, FieldValue{attribute=PRIMITIVE, value=5145599846383619}]


The key blobs are the same so I was hopeful that Metabase would be able to match them.

Has anyone else encountered this issue with the Metabase BigQuery driver and Firestore data? Is there a way to get this working either by making changes in BigQuery or Metabase? Hopefully I'm missing something simple!

Exactly, what you need to do is create a view in bigquery so you can transform the rows yo some other data type that we support

Thanks for the rapid response @Luiggi

Can you point me to any guidance that would help with making the view to ensure compatibility with Metabase.

Do you think there is scope that in the future the BigQuery driver in Metabase could support these nested record types?

Noting here that there seem to be a few of us that would benefit from Metabase driver support for BigQuery RECORD columns.

Turns out I was massively overcomplicating this in my own mind. Creating a BigQuery View with the following statement did the trick and field lookups/joins are now working in Metabase:

SELECT 
__key__.id AS country_id,
name,
numberOfRegions,
numberOfLocations,
 FROM `app-name.reports.Countries`

Noting for my own reference that using dot notation (i.e. field.element_name) works because the Firestore key columns are RECORD type and in NULLABLE mode.