Querying Amazon Elasticsearch Service data using Piped Processing Language - Amazon Elasticsearch Service

Querying Amazon Elasticsearch Service data using Piped Processing Language

Piped Processing Language (PPL) is a query language that lets you use pipe (|) syntax to query data stored in Amazon Elasticsearch Service (Amazon ES).

The PPL syntax consists of commands delimited by a pipe character (|) where data flows from left to right through each pipeline. For example, the PPL syntax to find the number of hosts with HTTP 403 or 503 errors, aggregate them per host, and sort them in the order of impact is as follows:

source = kibana_sample_data_logs | where response='403' or response='503' | stats count(request) as request_count by host, response | sort -request_count

PPL requires Elasticsearch 7.9 or later. Full documentation for the feature, including detailed steps and command descriptions, is available in the Open Distro for Elasticsearch documentation.

To get started, choose Query Workbench in Kibana and select PPL. Use the bulk operation to index some sample data:

PUT accounts/_bulk?refresh {"index":{"_id":"1"}} {"account_number":1,"balance":39225,"firstname":"Amber","lastname":"Duke","age":32,"gender":"M","address":"880 Holmes Lane","employer":"Pyrami","email":"amberduke@pyrami.com","city":"Brogan","state":"IL"} {"index":{"_id":"6"}} {"account_number":6,"balance":5686,"firstname":"Hattie","lastname":"Bond","age":36,"gender":"M","address":"671 Bristol Street","employer":"Netagy","email":"hattiebond@netagy.com","city":"Dante","state":"TN"} {"index":{"_id":"13"}} {"account_number":13,"balance":32838,"firstname":"Nanette","lastname":"Bates","age":28,"gender":"F","address":"789 Mady Street","employer":"Quility","city":"Nogal","state":"VA"} {"index":{"_id":"18"}} {"account_number":18,"balance":4180,"firstname":"Dale","lastname":"Adams","age":33,"gender":"M","address":"467 Hutchinson Court","email":"daleadams@boink.com","city":"Orick","state":"MD"}

The following example returns firstname and lastname fields for documents in an accounts index with age greater than 18:

search source=accounts | where age > 18 | fields firstname, lastname
Sample Response
id firstname lastname
0 Amber Duke
1 Hattie Bond
2 Nanette Bates
3 Dale Adams

You can use a complete set of read-only commands like search, where, fields, rename, dedup, stats, sort, eval, head, top, and rare. For descriptions and examples of each command, see Commands.

The PPL plugin supports all SQL functions, including mathematical, trigonometric, date-time, string, aggregate, and advanced operators and expressions. To learn more, seee SQL Functions.