opensearch-project / opensearch-project/sql
[FEATURE]New `fieldsummary` PPL command
Open
@YANG-DB is already working on this.
Since Sep 14, 2024.
enhancement
PPL
- Dominant language
- Java
- Stars
- 176
- Forks
- 229
- Avg merge
- 2d 21h
- Merged PRs (30d)
- 43
Description
Describe the solution you'd like
We propose adding a new fieldsummary command to OpenSearch PPL that would provide summary statistics for all fields in the current result set.
This command should:
- Calculate basic statistics for each field (count, distinct count, min, max, avg for numeric fields)
- Determine the data type of each field
- Show the most frequent values and their counts for each field
- Calculate the percentage of events that contain each field
Additionally, the command should support the following key optional parameters:
- includefields:
Specify which fields to include in the summary (e.g.,| fieldsummary includefields="status_code,user_id,response_time") - excludefields:
Specify which fields to exclude from the summary (e.g.,| fieldsummary excludefields="internal_id,debug_info") - topvalues:
Set the number of top values to display for each field (e.g.,| fieldsummary topvalues=5) - maxfields:
Limit the number of fields to display (e.g.,| fieldsummary maxfields=20) - nulls:
Include null/empty value counts (e.g.,| fieldsummary nulls=true)
Example usage:
source = t
| where timestamp >= "2023-01-01" and timestamp < "2023-02-01"
| fieldsummary includefields="status_code,user_id,response_time" topvalues=3 nulls=true
This command would generate a table with summary statistics for the specified fields in the given date range, showing the top 3 values for each field and including null counts.
Example output:
| Field | Count | Distinct | Min | Max | Avg | Type | Top Values | Nulls |
|---|---|---|---|---|---|---|---|---|
| status_code | 10000 | 4 | 200 | 503 | - | short | 200 (8000, 80%) 404 (1500, 15%) 500 (400, 4%) |
0 |
| user_id | 9500 | 1200 | - | - | - | string | user123 (100, 1%) user456 (95, 1%) user789 (90, 0.9%) |
500 |
| response_time | 10000 | 986 | 0.01 | 10.5 | 0.75 | float | 0.5 (2000, 20%) 0.75 (1800, 18%) 1.0 (1500, 15%) |
0 |
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Assessment
This issue has not been assessed yet.