API Query Limits to 2000 rows

Get Help
1

Whenever I am trying to fetch data using the Metabase API, I am getting the JSON limited up to 2000 rows only.

Code:

res_query = requests.post('http://metabase.frontrow.co.in/api/dataset',
                          headers = {'Content-Type': 'application/json',
                                     'X-Metabase-Session': token
                                    },
                          json=query
                         )

How can I get the full table result?

2

Hi @kritwish
There’s a hardcoded limit of 2000 results. You can export data, which will return 1 million rows:
https://www.metabase.com/docs/latest/api-documentation.html#post-apidatasetexport-format
For more context, read this:
https://github.com/metabase/metabase/issues/4564 - upvote by clicking :+1: on the first post

1 Like
3

Hey @flamber,

I tried downloading the data using the API call but it gave me an error stating

{
  "errors": {
    "query": "value must be a valid JSON string."
  }
}

Here is the code:

query= {
  "database": 2,
  "type": "native",
  "native": {
    "query": "SELECT * FROM user_install_referral_history"
  }
} 

res_query = requests.post('http://metabase.frontrow.co.in/api/dataset/csv',
                          headers = {'Content-Type': 'application/json',
                                     'X-Metabase-Session': token
                                     },
                          json=query
                         )
4

@kritwish
The best way to learn the API, is to just use Metabase while having your browser developer Network-tab open and looking at the request, and what data is being send/received.
https://www.metabase.com/learn/developing-applications/advanced-metabase/metabase-api.html

5

I saw the data being sent while downloading the CSV and applied the same on my API call. But in web-browser the Response Headers have 'Content-Type' : 'text/csv' whereas in my API call the response headers generates 'Content-Type': 'application/json;charset=utf-8'

How to fix this?

6

@kritwish You are downloading CSV data. Otherwise change export format to JSON.
But the response has nothing to do with the error you are seeing.
Please copy the cURL from your browser developer Network-tab to see the difference between your code and how the export code should be.

POST /api/dataset needs the following payload:

{"type":"native","native":{"query":"SELECT * FROM ORDERS LIMIT 2","template-tags":{}},"database":1,"parameters":[]}

POST /api/dataset/csv needs the following payload:

query=%7B%22type%22%3A%22native%22%2C%22native%22%3A%7B%22query%22%3A%22SELECT+*+FROM+ORDERS+LIMIT+2%22%2C%22template-tags%22%3A%7B%7D%7D%2C%22database%22%3A1%2C%22middleware%22%3A%7B%22js-int-to-string%3F%22%3Atrue%2C%22add-default-userland-constraints%3F%22%3Atrue%7D%7D
7

Hello flamber

I tried your way but it not working

payload_1 = '''{"database":6,"native":{"template-tags":{},"query":"SELECT\n    tracking_id\n    ,dest_hub_name\n\nFROM vn_views.transit_time_report\n\nLIMIT 20"},"type":"native","parameters":[]}'''

payload_2 = '''query=%7B%22database%22%3A6%2C%22native%22%3A%7B%22template-tags%22%3A%7B%7D%2C%22query%22%3A%22SELECT%5Cn++++tracking_id%5Cn++++%2Cdest_hub_name%5Cn%5CnFROM+vn_views.transit_time_report%5Cn%5CnLIMIT+20%22%7D%2C%22type%22%3A%22native%22%2C%22middleware%22%3A%7B%22js-int-to-string%3F%22%3Atrue%2C%22add-default-userland-constraints%3F%22%3Atrue%7D%7D'''

When I try this way, It OK but limit 2000 rows

res_1 = requests.post(f'https://metabase.mydomain.co/api/dataset, headers={'Content-Type': 'application/json','X-Metabase-Session': cookie}, data=payload_1)

When I try this quay, I got error response: HTTPError: 400 Client Error: Bad Request for url:

res_2 = requests.post(f'https://metabase.mydomain.co/api/dataset/json', headers={'Content-Type': 'application/json','X-Metabase-Session': cookie}, data=payload_2)
8

@tranngocminhhieu That's two different endpoints.
Open your browser developer Network-tab, look at the requests or copy as cURL.