Focus Your REST API Queries if Returned Data Too Large

Aternity's REST APIs can automatically return up to one million rows of data. The REST API server processes pretty quickly up to 25,000 entries. Processing more than 25,000 entries becomes slow, but brings all rows. You can always narrow the focus of your query with $select

Important

$force_Query_Result function will become obsolete in several months. In any case, using $force_Query_Result=Yes do not return all available rows, but only partial results, a random subset per the allowed number of rows. This function should be used for development and testing only. Use $force_Query_Result=Yes to get a random sample and to decide what fields and values to include or exclude from the query. Then reset the query and run it without $force_Query_Result=Yes.

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.

Note that the amount of returned rows is data-sensitive, and depends on the selected columns. Different data is being aggregated following different rules, so having different columns in different queries will cause different aggregations and so the actual number of returned rows will be different.

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

You can 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. But this is not necessary. You can bring up to 1 million rows with or without $select=

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

For APIs that do not support aggregation, $select= will return the selected columns without aggregation.

For APIs that support aggregation, the returned rows might be aggregated by default (this option is the default for most APIs that support aggregation). If the API does not aggregate by default, then $select will not aggregate, unless you explicitly set $aggregate.

To return the first 25,000 rows of a very broad query

Add $select= 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, displaying it summarized (aggregated) according to the timeframe view you selected.. 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.

Tip

Special characters must be encoded in a percent-encoding format, meaning as a character triplet, consisting of the percent character "%" followed by the two hexadecimal digits. For example, %20 is the percent-encoding for the space character, and %27 for the single-apostrophe character. That is the basic URL encoding (https://tools.ietf.org/html/rfc3986#section-2.1) that applies to REST API in general. If you use a browser, Excel or PowerBI, they will automatically encode any special character for you. But if you copy examples from the articles, make sure to manually add the encoding for spaces and apostrophes. Otherwise, your query might fail.

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.