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 with $select or by forcing it to return the first 25,000 entries with $Force_Query_Result=Yes (see below).

The REST API automatically splits large results into pages. Browsers return only 100 entries per page by default. Excel or PowerBI return up to 10,000 entries per page by default. Each page includes a link to the next page. Programmers can use link near the top of each page: <a:link rel="next" href="next_url"/>.

Tip

To change the default page size insert $page_size into your query. For example, to increase page size in browsers from 100 (default) to 500 entries per page, enter $page_size=500. Similarly, you can reduce the page size in Excel from 10,000 entries (default) to just 500 entries per page with the same $page_size=500.

You can theoretically go up to 10,000 entries per page, but we recommend to use modest page sizes in browsers, as larger pages can cause browsers to crash as they attempt to format the very large XML response.

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 return the first 25,000 rows of a very broad query with no $select=

Add $Force_Query_Result=Yes to the URL.

If your $select= query still returns more than 1 million rows, and you still want to see the first 1 million rows

Add $Force_Query_Result=Yes to the URL.

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 that 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 timeframe view you selected.

  • today: Displays data starting from 00:00AM today according to the time zone of the account.

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

  • 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 timeframe 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 VIEWING, use <base_url>/latest/API_NAME; for INTEGRATIONS, use <base_url>/<version number>/API_NAME (for example, <base_url>/v1/API_NAME, or <base_url>/v1.0/API_NAME, or <base_url>/v2/API_NAME, or <base_url>/v2.0/API_NAME).

For example, perform a query like:

<base_url>/v2.0/<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.