• Log inStart now

NerdGraph: use historical data exports to return larger NRQL responses

When running a NRQL query, you're restricted by various limits, such as a limit on the number of data points returned in a response, and a query timeout. Our historical data export feature can be used to run a NRQL query that returns 200,000,000 data points in a response (instead of the usual limit of 2000) and has no query timeout. The results are returned as one or more JSON files.

Requirements

This feature requires Data Plus.

This feature requires the use of our NerdGraph API.

Limits and restrictions

Here are some limits and restrictions on the feature:

Usage limits

The following are the default usage limits for an export:

  • Exports should be estimated to return fewer than 200 million events
  • Exports should be estimated to inspect fewer than 5 billion events
  • No more than two concurrent exports per account

If you'd like higher limits, talk to your account representative.

Data type restrictions

Some data types are not available for export:

  • Metric timeslice data
  • Dimensional metrics (the Metric data type)
  • Infrastructure events SystemSample, ProcessSample, StorageSample, NetworkSample, ContainerSample

Time range restrictions

The historical data export feature doesn't support querying live data or data from the preceding 12 hours. The time range must end at least 12 hours in the past.

Query syntax requirements

This feature supports selecting specific attribute names, and wildcards (SELECT *). For example, we support queries like these:

SELECT * FROM MobileRequest SINCE 3 days ago until 2 days ago
WHERE appName = 'interesting-application' SINCE '2022-06-11 11:05:00-0600'
UNTIL '2022-06-11 11:10:00-0600'

The following NRQL components cannot be used:

  • Aggregation functions (sum, count, average, max)
  • Evaluable functions (numeric, log, concat)
  • FACET
  • TIMESERIES
  • COMPARE WITH
  • Nested aggregations

Endpoint details

The historical data export feature uses our NerdGraph API and makes use of three endpoints:

  • The create endpoint allows a user to specify the account ID and the NRQL they'd like to run as an export.
  • The get details for export endpoint allows a user to specify an account ID and export ID (found in the response body from the create endpoint), and use that to retrieve the status of the export. When the export is complete, the results, in the form of one or more download URLs, can be found in the response of this endpoint.
  • The get details for account exports endpoint allows a user to specify an account ID and retrieve the details for all active, non-expired exports for that account.

Example queries

Create export

One way to use NerdGraph is with the NerdGraph explorer. The instructions in this section will focus on using the explorer. If you're interested in running your own scripts, see Scripts.

In the NerdGraph schema, the historicalDataExportCreateExport endpoint can be found under mutation > historicalDataExport. Use a query like the one below to run an export.

It's recommended to select ID for the response body, as this will be used to get details for the export, and retrieve results.

mutation {
  historicalDataExportCreateExport(accountId: YOUR_ACCOUNT_ID, nrql: "FROM Transaction SELECT duration, appId WHERE appName = 'interesting-application' SINCE '2022-06-11 11:05:00-0600' UNTIL '2022-06-11 11:10:00-0600'") {
    percentComplete
    nrql
    status
    id
    message
  }
}

#### Example response [#create-export-response]

Here's an example response for creating an export: 
{
  "data": {
    "historicalDataExportCreateExport": {
      "id": "609b6916-8ca9-417c-bbf8-02e4cdc3afd2",
      "message": null,
      "nrql": "FROM Transaction SELECT duration, appId WHERE appName = 'interesting-application' SINCE '2022-06-11 11:05:00-0600' UNTIL '2022-06-11 11:10:00-0600'",
      "percentComplete": 5,
      "status": "WAITING"
    }
  }
}

Cancel export

In the NerdGraph schema, the historicalDataExportCancelExport endpoint can be found under mutation > historicalDataExport. Use a query like the one below to run an export.

It's recommended to select the status in the response body to verify that the export was cancelled successfully.

mutation {
  historicalDataExportCancelExport(accountId: YOUR_ACCOUNT_ID, id: "YOUR_EXPORT_ID") {
    status
    id
  }
}

Example response

Here's an example response for cancelling an export:

{
  "data": {
    "historicalDataExportCancelExport": {
      "id": "YOUR_EXPORT_ID",
      "status": "CANCELED"
    }
  }
}

Get details for a specific export

Use the export ID found in the response from the create endpoint to query details for the export, as shown below. This endpoint can be found in NerdGraph under query > actor > account > historicalDataExport > export.

{
  actor {
    account(id: YOUR_ACCOUNT_ID) {
      historicalDataExport {
        export(id: "YOUR_EXPORT_ID") {
          availableUntil
          eventCount
          eventTypes
          id
          message
          nrql
          percentComplete
          results
          status
        }
      }
    }
  }
}

Example response

Here's the response for getting the details for a specific export:

{
"data": {
"actor": {
"account": {
"historicalDataExport": {
"export": {
"availableUntil": 1655499642845,
"eventCount": 1291,
"eventTypes": [
"MobileRequest"
],
"id": "4776677a-1e0f-4ad5-a790-cdbe40a1f348",
"message": null,
"nrql": "SELECT * FROM MobileRequest SINCE 3 days ago UNTIL 2 days ago",
"percentComplete": 100,
"results": [
"downloadLink1",
"downloadLink2"
],
"status": "COMPLETE_SUCCESS"
}
}
}
}
}
}

Get export details for an account

You can use the account ID to get the details for all exports from that account, as shown below. The endpoint can be found in the NerdGraph schema under query > actor > account > historicalDataExport > exports.

{
  actor {
    account(id: YOUR_ACCOUNT_ID) {
      historicalDataExport {
        exports {
          availableUntil
          eventCount
          eventTypes
          id
          message
          nrql
          percentComplete
          results
          status
        }
      }
    }
  }
}

Example response

Here's an example response for getting export details for an account:

{
"data": {
"actor": {
"account": {
"historicalDataExport": {
"exports": [
{
"availableUntil": 1655499642845,
"eventCount": 1291,
"eventTypes": [
"MobileRequest"
],
"id": "4776677a-1e0f-4ad5-a790-cdbe40a1f348",
"message": null,
"nrql": "SELECT * FROM MobileRequest SINCE 3 days ago UNTIL 2 days ago",
"percentComplete": 100,
"results": [
"downloadLink1",
"downloadLink2"
],
"status": "COMPLETE_SUCCESS"
}
]
}
}
}
}
}

Use scripts

It might be useful to query historical data exports programmatically, from a script. The following curl commands may be useful to get started. For response examples, see the responses in the Example queries section.

Create export

curl --location --request POST 'https://api.newrelic.com/graphql' \
--header 'Content-Type: application/json' \
--header 'API-Key: YOUR_USER_KEY \
--data-raw '{"query":"mutation {\n  historicalDataExportCreateExport(accountId: YOUR_ACCOUNT_ID, nrql: "FROM Transaction SELECT duration, appId WHERE appName = 'interesting-application' SINCE '2022-06-11 11:05:00-0600' UNTIL '2022-06-11 11:10:00-0600'") {\n    percentComplete\n    nrql\n    status\n    message\n    id\n }","variables":{}}'

Cancel export

curl --location --request POST 'https://api.newrelic.com/graphql' \
--header 'Content-Type: application/json' \
--header 'API-Key: YOUR_USER_KEY \
--data-raw '{"query":"mutation {\n historicalDataExportCancelExport(accountId: YOUR_ACCOUNT_ID, id: "YOUR_EXPORT_ID") {\n status\n id\n }","variables":{}}'

Get details for export

curl --location --request POST 'https://api.newrelic.com/graphql' \
--header 'Content-Type: application/json' \
--header 'API-Key: YOUR_USER_KEY' \
--data-raw '{"query":"{\n  actor {\n    account(id: YOUR_ACCOUNT_ID) {\n      historicalDataExport {\n        export(id: \"YOUR_EXPORT_ID\") {\n          availableUntil\n          eventCount\n          eventTypes\n          id\n          message\n          nrql\n          percentComplete\n          results\n          status\n        }\n      }\n    }\n  }\n}","variables":{}}'

Get details for account exports

curl --location --request POST 'https://api.newrelic.com/graphql' \
--header 'Content-Type: application/json' \
--header 'API-Key: YOUR_USER_KEY' \
--data-raw '{"query":"{\n  actor {\n    account(id: YOUR_ACCOUNT_ID) {\n      historicalDataExport {\n        exports {\n          availableUntil\n          eventCount\n          eventTypes\n          id\n          message\n          nrql\n          percentComplete\n          results\n          status\n        }\n      }\n    }\n  }\n}\n","variables":{}}'

Export results format

Results from the queries are in the results field from either of the two get details endpoints. They are in the form of one or more download links. The result files themselves are valid for a week from when the export was run and contain approximately 100MB or less of GZIP compressed JSON. Each link is pre-signed to be valid for 6 hours. If the link expires, you can get a fresh one by requerying the export details object in NerdGraph for the results.

An example unzipped results file is below:

[
{
"attributes": {
"duration": 36,
"eventType": "Transaction",
"account": ACCOUNTID,
"timestamp": 1655174793213
}
},
{
"attributes": {
"duration": 3,
"eventType": "MobileRequest",
"account": ACCOUNTID,
"timestamp": 1655174793215
}
}
]

Query and alert on export-related events

This feature will generate custom events in the New Relic account in which the export was run. It may be useful to query or create alerts on these events to get information about the exports which have been run in an account.

The HistoricalDataExport event type contains information such as the status of the export (Created, Completed, Failed, Canceled), the export ID, the NRQL string the export is generated from, and more.

The following queries, which you can run in the query builder or add to a dashboard, will return all the exports created in the last week and the number of those which were completed successfully and didn't have an error:

FROM HistoricalDataExport SELECT * WHERE status = 'Created' SINCE 1 WEEK AGO
FROM HistoricalDataExport SELECT count(*) WHERE status != 'Completed' FACET status SINCE 1 WEEK AGO

Troubleshooting

Account not yet enabled

When attempting to create an export, you may see an error message that reads:

Cannot query field \"historicalDataExportCreateExport\" on type \"RootMutationType\".

If you're getting this, it likely means that the historical data export feature has not yet been enabled for the account you're trying to export from. If you have questions, review the requirements and talk to your account representative to ask about access.

When attempting to download results using a pre-signed URL, you may see an error like this:

<Error>
<Code>AccessDenied</Code>
<Message>Request has expired</Message>
<X-Amz-Expires>120</X-Amz-Expires>
<Expires>2022-06-24T15:16:45Z</Expires>
<ServerTime>2022-06-24T17:56:40Z</ServerTime>
<RequestId>1234567890ABC</RequestId>
<HostId>ABCD1234HOST-ID098765-XYZ</HostId>
</Error>

An error like this indicates that the results URL has expired and you'll need to requery the export object for a fresh results url. You don't need to rerun the export, just get a new set of URLs for the results.

Copyright © 2022 New Relic Inc.

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.