Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Support nested records in BigQuery #2662

Open
gad2103 opened this Issue May 24, 2016 · 20 comments

Comments

Projects
None yet
9 participants
@gad2103
Copy link

gad2103 commented May 24, 2016

If this is a bug report, please fill in the blanks:

  • I am using the Chrome browser.
  • My computer's OS is OS X Yosemite.
  • I'm running Metabase locally from the JAR.
  • My database BigQuery.
  • My Metabase version v0.17.1.

my bq tables contain nested structures (repeated record fields).

it looks like the question interface can only handle flat schemas for BQ.

@camsaul camsaul changed the title big query repeated records break questions Support nested records in BigQuery May 24, 2016

@camsaul

This comment has been minimized.

Copy link
Member

camsaul commented May 24, 2016

Hi @gad2103, currently the questions interface doesn't support nested fields 😿

Related (Mongo) #1328

@camsaul

This comment has been minimized.

Copy link
Member

camsaul commented May 24, 2016

Also related: #708 (same issue for Postgres)

@gad2103

This comment has been minimized.

Copy link
Author

gad2103 commented May 24, 2016

Is there a technical barrier to this? Or is it just a matter of resources
to add this feature?
On Tue, May 24, 2016 at 5:08 PM Cam Saül notifications@github.com wrote:

Also related: #708 #708
(same issue for Postgres)


You are receiving this because you were mentioned.
Reply to this email directly or view it on GitHub
#2662 (comment)

@salsakran

This comment has been minimized.

Copy link
Contributor

salsakran commented May 24, 2016

@gad2103 largely engineering bandwidth. wanna help? ;)

@gad2103

This comment has been minimized.

Copy link
Author

gad2103 commented May 24, 2016

Could you point me in the right direction? Where in the code should I look?

@camsaul

This comment has been minimized.

Copy link
Member

camsaul commented May 24, 2016

@salsakran do we have any designs for exposing nested fields in the QB?

@gad2103

This comment has been minimized.

Copy link
Author

gad2103 commented May 25, 2016

if you guys point me in the right direction, i'm happy to try my hand at this. i've never worked in clojure but it would be fun to try it out. and we would really like to have this at my company.

@salsakran

This comment has been minimized.

Copy link
Contributor

salsakran commented May 25, 2016

they should look and feel the same as the FK relations do.

eg, whether I have a foreign key to a "user" table or a dictionary that has those denormalized there, it's the same from an end users perspective. the fact they've been denormalized is a "physical schema" level detail.

@gad2103 I'd recommend digging into the query builder code at https://github.com/metabase/metabase/tree/master/frontend/src/query_builder to understand how the front end UI works wrt to foreign keys. It's in react/js so it won't be as strange. Once you've wrapped your head around it, we can talk over (in this issue) how best to expose things, and if we need design assets we can whip some up for you.

@salsakran

This comment has been minimized.

Copy link
Contributor

salsakran commented May 25, 2016

@camsaul remind me - are we ever introspecting JSON blobs to figure out which keys are in them?

@camsaul

This comment has been minimized.

Copy link
Member

camsaul commented May 25, 2016

@salsakran that's open as #708 for Postgres

@camsaul

This comment has been minimized.

Copy link
Member

camsaul commented May 25, 2016

It's probably not super useful to do much with JSON that is just serialized as a string because DBs have no way to query against them. But for DBs that have a native nested record / json type we can do interesting things

@salsakran

This comment has been minimized.

Copy link
Contributor

salsakran commented May 25, 2016

Gotcha, so it sounds like we'd need:

  • some way to introspect json fields that didn't nuke a data warehouse in the event of big tables.
  • some way to make sure we get a good enough sample of all the rows so that we have all the fields used in dicts, not just our current first N rows, as nested field shapes tend to change with time
  • some place to store these keys
  • a way to expose them in the query builder in the same visual style as the current foreign keys.

Am I missing anything?

@camsaul

This comment has been minimized.

Copy link
Member

camsaul commented May 25, 2016

We already more or less have the middle two. That's how we support nested fields in Mongo. The first point has to be done on a driver-by-driver basis; thus far we've only implented it for Mongo. The last point just has to be done once for the new QB. The old QB had it at some point so we just need to bring it back

@Dwar3xwar

This comment has been minimized.

Copy link

Dwar3xwar commented Feb 9, 2017

Any updates on this PR?

@camsaul

This comment has been minimized.

Copy link
Member

camsaul commented Feb 9, 2017

@Dwar3xwar I don't think I've seen a PR for this

@lukabirsa

This comment has been minimized.

Copy link

lukabirsa commented May 30, 2017

I'd love to have this supported in Metabase, as this would enable our non-programmer users to query Google BigQuery. Right now BigQuery integration is mostly unusable for our use case.

@txomon

This comment has been minimized.

Copy link

txomon commented Jun 23, 2017

In our case, we are starting to see aggregated reports that try to make use of this feature. In our case, just having repeated simple fields like arrays of basic types would solve a lot.

@davidfurlong

This comment has been minimized.

Copy link

davidfurlong commented Jun 25, 2018

Is there a way to omit these fields so that BigQuery tables with nested fields can still be used, but without using those fields? I'm getting errors when trying to list Raw Data, but not when doing a count. Thx

@thangbn

This comment has been minimized.

Copy link

thangbn commented Jul 21, 2018

same idea with @davidfurlong

@tuaris

This comment has been minimized.

Copy link

tuaris commented Mar 5, 2019

Would it be possible to just 'flatten' those nested field? For example if you have the field client with nested fields port and ip could Metabase have a column for each like client.ip and client.port? Or if the period is the problem convert it to client_ip and `client_port?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.