DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
Building Scalable Real-Time Apps with AstraDB and Vaadin
Register Now

Trending

  • Managing Data Residency, the Demo
  • 13 Impressive Ways To Improve the Developer’s Experience by Using AI
  • What Is mTLS? How To Implement It With Istio
  • How To Design Reliable IIoT Architecture

Trending

  • Managing Data Residency, the Demo
  • 13 Impressive Ways To Improve the Developer’s Experience by Using AI
  • What Is mTLS? How To Implement It With Istio
  • How To Design Reliable IIoT Architecture
  1. DZone
  2. Data Engineering
  3. Data
  4. Introducing NRQL: A Query Language Designed for Analytics

Introducing NRQL: A Query Language Designed for Analytics

Fredric Paul user avatar by
Fredric Paul
·
Apr. 19, 15 · Interview
Like (0)
Save
Tweet
Share
5.89K Views

Join the DZone community and get the full member experience.

Join For Free

[This article was written by Alex Kroman]

The Insights team is excited to announce the public beta of Insights for all paying customers starting today. We’ve been using Insights internally for the past several months and it’s proven to be an invaluable tool that helps us unlock the mystery of how customers use our product as well as helping us dig into our trickiest performance issues.

One of the major innovations of Insights is NRQL which stands for the New Relic Query Language (we pronounce it like “nerkel”). NRQL is the SQL-like language we created specifically for analytics that allows for simple aggregation and filtering over a defined time period. In this post we will go over building your first few NRQL queries. After working through these examples you’ll be well on your way to becoming a NRQL expert and asking your application all sorts of interesting questions.

EXPLORING YOUR DATA

The first thing you’ll want to do is look at the data that is being collected out of the box with Insights. We’re collecting two types of events: Transaction events are sent to Insights via your application agent while PageView events are sent from browser monitoring. Let’s start by exploring our PageView data by entering the following query.

SELECT * FROM PageView

InsightsNRQL_1

This gives you a list of browser page views over the last hour. You can see the detail of each event and what attributes are being collected. If you just want to look at the last PageView event you can add a limit.

SELECT * FROM PageView LIMIT 1

InsightsNRQL_2

COUNT AND UNIQUE COUNT

Now let’s try adding a simple aggregation function to the PageView query above. The query below will count the total number of page views over the last day:

SELECT count(*) FROM PageView SINCE 1 day AGO

InsightsNRQL3

If you want to count the number of unique sessions you can use the uniqueCount function on the session attribute.

SELECT uniqueCount(session) FROM PageView SINCE 1 day AGO

InsightsNRQL_4

MORE AGGREGATION FUNCTIONS: PERCENTILES, AVERAGE, AND HISTOGRAM

There are several other aggregation functions that you can experiment with but here are two of my favorites.

See the 50th and 95th percentiles over the last day:

SELECT percentile(duration, 50, 95) FROM PageView

InsightsNRQL_5

View a histogram of response times:

SELECT histogram(duration) FROM PageView SINCE 1 day AGO

InsightsNRQL_6

CONTROLLING THE TIME WINDOW WITH SINCE

You can use the SINCE keyword to change the time window for any query. During our open beta everyone has access to one week of data. You can combine the SINCE and UNTIL keywords to get a specific time window. The query below gives you a result for two days ago.

SELECT percentile(duration, 95) FROM PageView SINCE 2 days AGO UNTIL 1 day AGO

InsightsNRQL_7

You can also use the SINCE keyword to look at a specific period of time. The following query looks at data between 9 and 10am on March 15th.

SELECT percentile(duration, 95) FROM PageView SINCE ‘2014-03-15 09:00′ UNTIL ‘2014-13-15 10:00′

InsightsNRQL_8

FILTERING WITH THE WHERE CLAUSE

You can filter your data in different ways using the WHERE keyword which works a lot like standard SQL. Here’s an example of using a WHERE clause to show a percentile for people in the US who aren’t using the Safari browser:

SELECT percentile(duration, 95) FROM PageView WHERE countryCode = ‘US’ AND userAgentName != ‘Safari’

InsightNRQL_9

You can search on fields using LIKE. The following gives you a percentile for all transactions that match ‘deployments’.

SELECT percentile(duration, 95) FROM PageView WHERE name LIKE ‘%deployments%’

InsightsNRQL_11

FACETING

Another helpful way to slice your data is with the FACET command which groups results. This query gives you the geographic breakdown of your visitors by country.

SELECT percentile(duration, 95) FROM PageView FACET countryCode

InsightsNRQL_12

COMPARISONS

You can also compare values. Here’s an example of comparing session count between today and yesterday.

SELECT average(duration) FROM PageView SINCE 2 hours AGO COMPARE WITH 1 day AGO

Insights__Home

TIMESERIES

One of the most powerful parts of NRQL is the TIMESERIES keyword which allows you to graph any result over time. Here’s an example of graphing a percentile query over time. You can add the TIMESERIES keyword to most queries in NRQL.

SELECT percentile(duration, 95) FROM PageView SINCE 1 day AGO TIMESERIES

InsightsNRQL_14

ADDING MORE DATA TO INSIGHTS

So far we’ve learned how to aggregate data, select a time window, and slice and dice with facets and where clauses. You can combine these concepts to construct powerful queries. Once you get the hang of NRQL it’s time to start adding more data to Insights. The more attributes and events you add the richer questions you can ask.

Adding custom attributes

The first thing you’ll want to do is to add custom attributes to each Transaction and PageView event that gets sent into Insights. Want to be able to facet over any attribute you like? Add those interesting attributes to Insights.

Here is some documentation to get started with custom attributes: https://docs.newrelic.com/docs/insights/custom-attributes

Adding custom events

Are there events that are not being captured in the Transaction or PageView event? Use our insert API to add more events of your own: https://docs.newrelic.com/docs/insights/inserting-events


Database Query language Analytics

Published at DZone with permission of Fredric Paul, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Trending

  • Managing Data Residency, the Demo
  • 13 Impressive Ways To Improve the Developer’s Experience by Using AI
  • What Is mTLS? How To Implement It With Istio
  • How To Design Reliable IIoT Architecture

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com

Let's be friends: