Is it possible to get raw data from a Metabase MBQL / SQL query via the REST API?

Question

Is there a Metabase REST API that takes a MBQL/SQL query and returns the raw data?

I can perform a MBQL query via the API in a 2-step process by doing the intermediate step of creating a Question via the Metabase web app UI and then querying the Question, but I haven't figured how how to combine MBQL with the REST API in a single step.

Some items I'd like to accomplish by having the MBQL in the API request instead of a UI-generated Question:

1. better version management as the MBQL query can be checked into source control with the code
2. better isolation as the API call won't be dependent on the question which can change

Here's some info on how to perform the 2-step process.

2-Step Process

The two step process is:

1. Use web app to create a MBQL/SQL Metabase Question
2. Use REST API to query existing Question created in web app using the Card API

Step 1) Creating Question via Web UI

Log into the web app and click the "New Question" button in the top menu.

Once your question has been created you will be directed to a URL like the following where :question-id is an integer.

• Web UI endpoint: GET /question/:question-id

Note this value and use it in the API in the next step.

Note: an alternative for creating the card is to use the POST /api/card API endpoint per YakovL. This can be useful in some scenarios were UI questions/cards are desirable, but I'm also trying to avoid creating creating cards / questions in the first place, since I'm not planning on using the Metabase UI to consume them. Reasons to avoid cards for me include needing to perform extra work to verify the card query definitions haven't changed but still having the SQL in the code to create the cards, and generate a lot of unneeded question cards in the UI.

Step 2) REST API for Question Data

The API uses the term "card" for the Web UI "question" object, so make an API call to the following Card API:

• API endpoint: POST /api/card/:card-id/query/:export-format

In this URL:

• :card-id is the :question-id from the Web UI URL
• :export-format can be json or another format

More information on the API is available in the API Documentation:

https://github.com/metabase/metabase/blob/master/docs/api-documentation.md

Question

Is there a way to do this directly by sending the MBQL/SQL query in the API request in a single step without a pre-existing Question/Card?

Epeirogeny

Your question will be even more helpful for others, if you share how you do the second part – Knowledgeable

Thanks for the feedback. I've updated the question with information on how to accomplish this using the 2-step approach and why I prefer to have a 1-step approach. – Epeirogeny

Thanks so much! I was thinking I have to use the GET /api/embed/card/:token/query endpoint and had hard times figuring where to get the token from. Really appreciated! – Knowledgeable

By the way, have you tried the POST /api/card/ endpoint? It is described to create a new card, so probably you'll be able to do this in 2 steps, but both via API – Knowledgeable

Thanks for the note @YakovL. That is indeed a way to automate the creation of cards and useful to note so I added it to the main question above. In certain situations, it may be a good option. For my particular situation, there's no benefit of using cards so I'd like avoid them per the reasons mentioned above. – Epeirogeny

If the job is to automate the creation of cards or dashboards, there is a new Clojure open source library for doing this, Embedit. – Sanguineous

Answer 1

Querying via raw SQL and MBQL are both available via the POST /api/dataset/ API. The documentation for the endpoint mentions the query request definition but does not define it.

I ended up doing some more research and asking on the Metabase Discourse forum. The following examples were posted by sbelak.

Raw SQL Query

I was able to successfully make a native SQL query using the go-metabase SDK to make the following request:

POST /api/dataset
Content-Type: application/json
X-Metabase-Session: <sessionId>

{
  "database": 1,
  "native": {
    "query": "SELECT COUNT(*) FROM orders"
  },
  type: "native"
}

Notes:

1. The POST /api/dataset does not set the response Content-Type header.
2. There is a POST /api/dataset/json endpoint, but that does not seem to accept the native property.
3. To set X-Metabase-Session see github.com/goauth/metabase.

MBQL

POST /api/dataset
Content-Type: application/json
X-Metabase-Session: <sessionId>

{
  "database": 1,
  "type": "query",
  "query": {
    "source-table": 2, 
    "breakout": [
      [
        "binning-strategy", ["field-id", 14], "default"
      ]
    ],
    "aggregation": [["avg", ["field-id", 17]]]
  }
}

Notes:

1. To set X-Metabase-Session see github.com/goauth/metabase.

Epeirogeny