Historical data export: 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 5,000) and has no query timeout. The results are returned as one or more JSON files.

Requirements

Requires Data Plus
To export data, you must be a core or full platform user

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

None of the following data types are available for export:

Metric timeslice data
FedRAMP data
Blob attributes
Array attributes
Percentile attributes

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

SELECT duration, appId FROM Transaction
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 in the query:

Aggregation functions (sum, count, average, max)
Evaluable functions (numeric, log, concat) are supported only in the WHERE clause
FACET
TIMESERIES
COMPARE WITH
JOIN
Nested aggregations
Subqueries
Variable bindings
Cross Account Queries

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 NerdGraph 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

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.

Important

You must escape all instances of single quotes (') in your export NRQL query with a backslash (\'). The curl commands below rely on a leading $ in the --data-raw argument, the functionality of which ANSI-C quoting provides. More information is available through the official GNU manual.

Create export

bash

$curl --location --request POST 'https://api.newrelic.com/graphql' \
>     --header 'Content-Type: application/json' \
>     --header 'API-Key: $USER_API_KEY' \
>     --data-raw $'{"query":"mutation {historicalDataExportCreateExport(accountId: $ACCOUNT_ID, nrql: \\"$NRQL_QUERY_TO_EXPORT\\") {percentComplete nrql status message id}}","variables":{}}'

Cancel export

bash

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

Get details for export

bash

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

Get details for account exports

bash

$curl --location --request POST 'https://api.newrelic.com/graphql' \
>     --header 'Content-Type: application/json' \
>     --header 'API-Key: $USER_API_KEY' \
>     --data-raw $'{"query":"{actor {account(id: $USER_API_KEY) {historicalDataExport {exports {availableUntil eventCount eventTypes id message nrql percentComplete results status}}}}}","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",
      "accountId": YOUR_ACCOUNT_ID,
      "timestamp": 1655174793213
    }
  },
  {
    "attributes": {
      "duration": 3,
      "eventType": "MobileRequest",
      "accountId": YOUR_ACCOUNT_ID,
      "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.

Results link expired

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.

Historical data export: return larger NRQL responses

Requirements .css-21sua1{background:none;border:none;width:0;padding:0;}

Limits and restrictions

Usage limits

Data type restrictions

Time range restrictions

Query syntax requirements

Endpoint details

Example queries

Create export

Example response

Cancel export

Example response

Get details for a specific export

Example response

Get export details for an account

Example response

Use scripts

Important

Create export

Cancel export

Get details for export

Get details for account exports

Export results format

Query and alert on export-related events

Troubleshooting

Account not yet enabled

Results link expired

Requirements