Troubleshooting and Error Handling

Need help with the DataStore API? Look no further.

See troubleshooting recommendations and common errors by symptom with likely cause and fix below. Use the diagnosis flow below when a request fails or the response is empty.

Are you getting an error not listed here? Let us know! Email us at hdx@un.org

Quick Diagnosis Flow

Start with these simple steps to resolve your issues. See more detailed information per error code below.

  1. Test the resource with a minimal native call using limit equals 1.

  2. If it succeeds, add filters gradually.

  3. If native works but SQL fails, validate quoting and URL encoding of the SQL.

  4. If responses are empty, remove offset and lower limit to confirm presence of data.

  5. If performance degrades, page smaller and add filters.

If all else fails, re-confirm resource_id and datastore_active and recheck authentication with a valid API token.

Pagination

Both DataStore APIs (native and SQL) support pagination which is a technique used in API design and development to retrieve large data sets in a structured and manageable manner. The default limit is 100 records with a maximum limitation of 32,000 for both native and SQL queries.

It is recommended to use pagination to retrieve larger datasets and avoid timeouts or memory issues. To further optimize performance, use the HDX API’s filtering capabilities to limit the size of the data payload returned in each request.

Native Query (datastore_search)

Pagination is supported via the limit and offset parameters:

  • limit: Number of records to return per page (default is 100, maximum is 32,000).

  • offset: The starting point (index) for the next page of results.

This example retrieves 100 records starting from the 201st record (offset is zero-based): https://data.humdata.org/api/3/action/datastore_search?id=<resource_id>&limit=100&offset=200

To paginate through the entire dataset:

  1. Start with offset=0.

  2. Increment the offset by the limit (e.g., offset=100, offset=200, etc.) until result["records"] is empty.

SQL Query (datastore_search_sql)

Pagination is supported via SQL's LIMIT and OFFSET clauses:

Example: SELECT * FROM "<resource_id>" WHERE country = 'Kenya' LIMIT 100 OFFSET 200

Encoded URL: https://data.humdata.org/api/3/action/datastore_search_sql?sql=SELECT+*+FROM+"<resource_id>"+WHERE+country='Kenya'+LIMIT+100+OFFSET+200

  • Always use ORDER BY with pagination in SQL to ensure consistent result ordering across pages.

  • SQL-based pagination is more flexible but puts more load on the server; use filters and limits strategically.

  • Use SQL filters to retrieve only the data you need and reduce system load.

Authentication and Permissions

  • Symptom: 403 Forbidden

  • Likely cause: Missing or invalid API token in the Authorization header

  • Fix: Add a valid token and retry. Example with curl:

curl -H "Authorization: <your-api-token>" \ "https://data.humdata.org/api/3/action/datastore_search?resource_id=<id>&limit=1"

If you are testing in a browser, make sure you are logged in to HDX as described in the Authentication section.

Resource and Dataset Issues

  • Symptom: 404 Not Found or { "success": false, "error": "Not found" }

  • Likely cause: Wrong resource_id or the resource was removed or unpublished

  • Fix: Confirm the resource_id from the dataset page or via package_show and verify the resource still exists. Also verify that the resource has DataStore API access enabled. Look for datastore_active in the CKAN metadata.

  • Symptom: Empty records with total greater than zero

  • Likely cause: Using an offset beyond the number of rows or incompatible filter values

  • Fix: Remove filters and set limit to a small value to confirm data is present: .../datastore_search?resource_id=<id>&limit=5

  • Then add filters back one at a time.

SQL Query Errors

  • Symptom: { "success": false, "error": "Error parsing query" }

  • Likely cause: Unquoted resource_id or unencoded SQL

  • Fix: Quote the table name and URL encode the entire SQL: sql=SELECT%20*%20FROM%20"%3Cresource_id%3E"%20WHERE%20country%3D'Kenya'%20LIMIT%205

  • Symptom: Syntax error near ORDER or LIMIT

  • Likely cause: ORDER BY must reference valid column names and comes before LIMIT and OFFSET

  • Fix: Validate column names through a small select first, then add ORDER BY, then LIMIT and OFFSET as shown in the SQL examples.

Performance and Rate Limiting

  • Symptom: 429 Too Many Requests

  • Likely cause: High request concurrency or scraping patterns

  • Fix: Back off and retry with exponential backoff, reduce parallelism, and page through results. The user’s IP is rate limited at 60 requests per minute so plan for modest throughput.

Data Types and Schema Drift

For alerts on any changes to the data resources used within the Datastore API, enable notifications to be alerted about data schema changes. See more information here.

  • Symptom: Type errors or unexpected nulls

  • Likely cause: Mismatched types in filters or recent schema change in the source file

  • Fix: Inspect a sample of rows first to confirm types. If year is stored as text, pass "2024" rather than 2024. If the maintainer updated the schema, refresh your assumptions and adjust the query accordingly.

  • Symptom: Date filters fail to match

  • Likely cause: Inconsistent date formats in source

  • Fix: Use exact string matching that reflects the stored format, or use SQL functions where supported to normalize formats.

PreviousTools and Notebook

Last updated

Was this helpful?