Focus Your REST API Queries if Returned Data Too Large

By default, Aternity's REST APIs can return up to 25,000 entries. For more than 25,000 entries, it returns an error Returned data is too large, but you can work around this by narrowing the focus of your query.

If your query returns too many entries, the system automatically returns the first page (usually 1000 entries), with a link to the next page. In Excel or Power BI, select the displayed link to see the next page. Programmers can reference the rel="next" link returned near the top of each page: <a:link rel="next" href="next_url"/>. To display the number of entries (<m:count>), add $count=true.

To,.. Do this...

To view only specific columns

Add $select= to the URL to make your query more efficient, like ...API_NAME?$select=COL1,COL2,COL3. This has the additional benefit of summarizing (aggregating) the data of your selected columns.

To increase the number of allowed entries from 25,000 to 1 million

Add $select= to the URL to return the data for specific columns only, like ...API_NAME?$select=COL1,COL2,COL3. This has the additional benefit of summarizing (aggregating) the data of your selected columns.

You can also optionally narrow the timeframe of the query, or return only rows where a column has a specific value.

To display the number of entries (<m:count>)

Add $count=true to the URL (which slightly differs from the standard OData implementation).

To view only specific rows when a condition is true

Use $filter to insert conditions which narrow down the data, to return only entries where those conditions are true.

Create conditions with operators: and, or, eq (equals) gt (greater than), ge (greater than or equal), lt (less than), le (less than or equal), ge (greater than or equal to), ne (not equal to), le (less than or equal to), not and contains. Use operators with parentheses to group conditions logically: .../API_NAME?$filter=(COLUMN1 eq 'value1' or COL2 neq 'val2') and (COL3 gt number) and not(COL4 eq 'val4' or contains(COL5,'val5'))

To limit the timeframe of your query

Add $filter=relative_time() like, .../API_NAME?$filter=relative_time(last_24_hours). Possible values are:

  • last_x_hours (like last_6_hours or last_24_hours): Displays data starting from exactly this time several hours ago, displaying it summarized (aggregated) according to the view you selected.

  • today: Displays data starting from 00:00AM today in the time zone of the Aternity Management Server.

  • yesterday: Displays data starting from 00:00AM yesterday according to the time zone of the Aternity Management Server.

  • last_x_days (like last_7_days): Displays data starting from 00:00AM several days ago in the time zone of the Aternity Management Server, displaying it summarized (aggregated) according to the view you selected.

  • this_week: Displays data for this week, beginning from 00:00 AM on Sunday morning according to the time zone of the Aternity Management Server, until the current time.

  • last_week: Displays data from 00:00AM on Sunday morning to 23:59 on the most recent Saturday night according to the time zone of the Aternity Management Server. Use this view to compare consistent weekly results.

Alternatively, you can limit the scope of a query to the period between two static times, by creating a filter of a timeframe greater than or equal to (ge) the start time and less than or equal to (le) the end time.

For example: $filter=((TIMEFRAME gt 2018-06-09T16:00:00+01:00 and TIMEFRAME lt 2018-06-11T18:00:00+01:00) or (TIMEFRAME gt 2018-06-13T16:00:00+01:00 and TIMEFRAME lt 2018-06-15T18:00:00+01:00))

To accumulate detailed data (ETL), like gathering raw measurements for longer than offered by Aternity

Perform an ETL by running a detailed API (like RAW or HOURLY) automatically at regular intervals, like every two hours. Transfer and integrate the new data cumulatively to your target repository.

For example, perform a query like:

<base_url>/<API_name>?$filter=((TIMEFRAME gt <last_entry_transferred> and TIMEFRAME lt <two_hours_ago>) where:

  • <last_entry_transferred> is MAX(timeframe) in the target repository.

  • <two_hours_ago> is the current timeframe minus two hours.

Do NOT use relative_time. Ensure the query follows all next page links to retrieve the complete result set for that time period.

Best practices

  • Build your URL incrementally, adding elements one at a time, then testing it, to locate and fix any syntax errors or typos.

  • Test your URLs in a browser for faster results. Then, use your OData application to view the data in an easier format.

  • Learn more about the OData parameters mentioned in this article by visiting Microsoft's OData documentation.