Where are the queries saved?


#1

Are the saved queries kept in a database table or a place that can be easily exported automatically on a weekly basis?


#2

I suggest you read up on the application database.


#3

I have been doing a little reverse engineering on this a couple of months ago, looking at ways to export questions/dashboards, and where is what I have figured out. (this is off the top of my head so might not have the specifics right). In the application db. they are in a table called report_card, if they are SQL then the SQL will be stored in that table. If they are base on metadata, then they will have references in some json to metabase_field, and metabase_table.

If you use the API you can get at this information a little easier, but you still need to resolve what the fields and tables are from the information from the card (card seems to be the internal name for question)

Hope this helps.


#4

First use a proper database so that you can more easily query the tables. I’m using MySQL.
As mlutze says, the questions are stored in the report_card table. There are two types (query and native). Native means custom SQL.
This is an example of query:
{"type":"query","database":2,"query":{"aggregation":[["sum",["field-id",51]]],"source_table":6,"breakout":[["field-id",47]]}}

This is native (to get a top 15 query from SQL Server):
{"type":"native","database":2,"native":{"query":"SELECT top 15 sum(\"dbo\".\"Customer\".\"LastYearsSales\") AS \"sum\", \"dbo\".\"Customer\".\"Country\" AS \"Country\"\nFROM \"dbo\".\"Customer\"\nGROUP BY \"dbo\".\"Customer\".\"Country\"\nORDER BY sum(\"dbo\".\"Customer\".\"LastYearsSales\") DESC","template_tags":{}}}

The native is helpful, the query not so much. However, the database has a column for ‘table_id’ which relates to the metabase_table table.

Easiest way to approach this is to just dump the data into Excel or your favourite report generator.


#5

Good points @AndrewMBaines!

One comment

Well, not at first look. But I speculate it’s “MBQL” - short for MetaBase Query Language. For MBQL queries it looks like you then also need to lookup field-id and source-table identifies, which you then of course also have to find in the Metabase database.

Update - oh and …

… seems quite relavant to link to in this context as well :smiley: