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

jamesgreenblue · February 22, 2023, 5:42am

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!

Luiggi · February 22, 2023, 12:30pm

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

jamesgreenblue · February 22, 2023, 2:11pm

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?

jamesgreenblue · February 23, 2023, 12:48am

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

github.com/metabase/metabase

Support nested records in BigQuery

opened 07:44PM - 24 May 16 UTC

gad2103

Priority:P2 Type:New Feature Database/BigQuery Querying/GUI

@luizarakaki edit (Dec 2022): BigQuery differs from other databases as nested f…ields are part of the table definition. We don't need to parse JSON fields to get the schema, it is defined in the table schema. https://cloud.google.com/bigquery/docs/information-schema-tables So, instead of using the standard SQL JDBC driver implementation [`describe-nested-field-columns`](https://github.com/metabase/metabase/blob/d8486fccb407b4ef936e04198b97a077ba896990/src/metabase/driver/sql_jdbc/sync/describe_table.clj#L374), we can get the schema from the table metadata. The syntax to query nested fields is also different: it uses the dot notation: ``` SELECT nested.field_1, nested.field_2 FROM table ``` https://scosco.github.io/learn-bigquery/foundation/structs.html **This issue DOESN'T entail supporting arrays (aka repeated). It also doesn't entail working with the new [BigQuery JSON support](https://cloud.google.com/bigquery/docs/release-notes#January_06_2022), this totally unstructured JSON support is new and not frequent (yet).** :arrow_down: Please click the :+1: reaction instead of leaving a `+1` or `update?` comment

github.com/metabase/metabase

Bigquery: support usable display of RECORD columns

opened 01:54PM - 16 Jun 19 UTC

allan-simon

Type:New Feature Database/BigQuery

As of 0.32 if you have in bigquery a column of type 'RECORD' , (here in my case… a Record `plan` with subfields 'name', 'value', 'currency' , 'metadata' ) metabase will return you a weird array like this in the cell : ```json { "f": [ {"v":"plan"}, {"v":"30"}, {"v":"eur"}, {"v":"java.lang.Object@2d1ac5e1"} ] } ``` I imagine it's just the due to bigquery API of `getF` for [TableRow](https://developers.google.com/resources/api-libraries/documentation/bigquery/v2/java/latest/com/google/api/services/bigquery/model/TableRow.html) and `getV` for tableCell ? And the java.LangObject is a recurence of this bug: https://jira.talendforge.org/browse/TBD-1592 ? (i.e a null value) As bigquery already provides the schema at the beginning, we should be able to either create dedicated columns (like biquery website displays to you) i.e in my case plan.name | plan.value | plan.currency | plan.metadata ---------------|-----------------|---------------------|---------------------- plan | 30 | eur | null or, if easier plan | ------| {"name": "plan", "value": 30, "currency" : "eur" , "metadata": null} | I've started checking the code and it seems to be handled here https://github.com/metabase/metabase/blob/11884ebeabd313cc22ca8015995773737599ff6e/modules/drivers/bigquery/src/metabase/driver/bigquery.clj#L218 and here https://github.com/metabase/metabase/blob/11884ebeabd313cc22ca8015995773737599ff6e/modules/drivers/bigquery/src/metabase/driver/bigquery.clj#L238-L256 I don't know enough clojure, but to what i understand the code missing is one that handle going one level deeper in getting schema/getting F and V when iterating over the schema and finding a field of type "RECORD" am I correct ? This issue is really blocking us in the adoption of metabase, as we have a lot of RECORD field in our bigquery tables and people can't just discover the schema by first doing a `SELECT *` and then using the result to 'discover' the subfields If somebody can provide me a bit of guiding, I'm ready to handle the task of implementing it (I started discussing on the gitter, but didn't get any response :/ ) ( I have already a working dev environment and local version of metabase) :arrow_down: Please click the :+1: reaction instead of leaving a `+1` or `update?` comment

jamesgreenblue · February 23, 2023, 6:25am

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.