output of structured data in a table should include field name an should be better formatted

[Ognian]

My first impression of querybook is really quite good. Of course there is always room for improvement... My use case is querying mongodb via trino with sql and this leads to things that normally are not a big problem with relational databases. One of these is, that columns consist of quite complicated json structs... In this example the property names are not visible, the same looks in compass (mongoldb's own ui ) like this: or in table mode they have a drilldown feature looking like this: The above pictures should only illustrate what I mean ...

[czgu]

Hi @Ognian this is going to be a bit hard to repro, I suspect it is because issues with Python serialization. Can you confirm your setup are the following?

1. Mongodb
2. Trino with mongodb connector
3. Querybook with sqlalchemy-trino

[Ognian]

I use:

1. mongodb > 4.2 (4.2.7)
2. trino (354) with mongodb connector and in config.properties protocol.v1.alternate-header-name=Presto
3. querybook query-engine presto executor presto

I've chosen the above config since i expected that the native presto driver should deliver the most features and best compatibility (I had no time to test different combinations yet...)

[a-pertsev]

Hello, i would like to bump this issue, it could be reproduced using trino executor with underlying hive tables. How to reproduce:

1. create table (hive) create table apertsev.test(col struct<id:int,val:string>);
2. insert data (hive) insert into apertsev.test select named_struct('id', 1, 'val', 'value_str');
3. select data from table (querybook) select * from apertsev.test

Result:

Trino cursor returns all information about structures, for this example query cursor.description will be like:

[('col', 'row(id integer, val varchar)', None, None, None, None, None)]

But i believe this info gets lost here: https://github.com/pinterest/querybook/blob/86bc628796676f57b5e1f9cf8b11622d23d4d6ea/querybook/server/lib/query_executor/clients/trino.py#L76

[froukees]

Hi! We were solving this in my company for the Presto client, I guess it'll be very similar for Trino.

In the end, we ended with something like this:

It has only "disadvantage" that it makes not only structs browsable, but also strings in JSON format:

• Pros: It's handy for JSONs
• Cons: One doesn't recognize struct from JSON string directly
  • Doesn't have to be an issue, our data scientists never complained

If you are interested, I can provide PR for this, it will just take few weeks (to apply it on Trino, and some polishing of our company version will be needed for Presto as well).