Much of the data stored within New Relic relates to other data: Transactions and Transaction Errors, PageViews and PageActions, Logs and Infrastructure, and more. Now you can finally perform analysis and calculate correlations between these events using subquery joins.
Subquery join basics
A subquery is a query that is nested inside another query. With subquery joins, you can combine the result of a subquery with the result of its outer query based on a key, allowing for analysis and enrichment across datasets.
Example subquery join
Consider this example of joining infrastructure data with transaction data:
In this example there are two datasets: the average CPU percentage of infrastructure containers (
ProcessSample) and the average duration of app transactions by container. Often, data from different sources is correlated. In this case you can determine if a container's higher CPU usage is causing slower transactions by using the following subquery join:
FROM TransactionJOIN (FROM ProcessSample SELECT average(cpuPercent) AS cpuFACET containerId LIMIT MAX) ON containerIdSELECT average(duration)/latest(cpu) FACET containerId, containerName
With this query, you can see the containers that have a higher average transaction duration given their CPU usage, and investigate outliers to see if there is a bug to fix or optimizations to be made.
How to write a subquery join
A subquery join requires three components: two datasets and a primary key to link the two together.
FROM Event [INNER|LEFT] JOIN (subquery) ON [key =] key SELECT ...
There are a few simple rules to the syntax:
- The JOIN clause must always follow immediately after the FROM clause.
- The JOIN can be prefixed with the join type. INNER or LEFT is optional, and defaults to INNER when omitted.
- Parentheses containing a subquery must immediately follow the JOIN clause.
- The ON clause must immediately follow the subquery and has two forms (more details below).
- The joined subquery will continue to have a default
LIMITof 10, with a maximum
LIMITof 2000. Note that the outer query's
LIMITdoes not affect the inner query.
- The use of
TIMESERIESin the joined subquery is not supported. If your outer query uses
TIMESERIES, keep in mind that the joined subquery will provide a single result for the full query timespan.
- The use of
COMPARE WITHin the joined subquery is not supported. If your outer query uses
COMPARE WITH, keep in mind that the joined subquery will provide a single result based on the queries base timespan, and will not provide a separate value for the outer queries compare with timespan.
- Like all subqueries, joined subqueries cannot be used in alert conditions.
- While SELECT * is supported in the parent query, it is not supported in the joined subquery.
- The cardinality of the join is limited to 1:100, meaning a single join key cannot map to more than one hundred rows in the subquery result.
- The ON clause only supports equality conditions.
- The JOIN key cannot be a complex attribute, like a metric value.
- We do no coercion of attribute types in the JOIN condition. The left side of the JOIN's ON condition needs to be the same type as the right side of the ON condition.
- Metric wildcards are not supported in the JOIN's ON condition
- The subquery cannot be a Metric row-wise query.
- The right-hand side of the JOIN's ON condition must be an identifier projected by the query. It cannot use a function or mathematical operation.
- The joined subquery cannot project a
Example subquery joins
Here are some example subquery joins: