Troubleshooting and error handling

Need help with the Tabular Data endpoints? Look no further.

Pagination

The Tabular Data Endpoints (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 HDX 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 Tabular Data endpoint 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 Tabular Data Endpoints, 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.