Calcite patterns command brain pattern method by songkant-aws · Pull Request #3570 · opensearch-project/sql

songkant-aws · 2025-04-22T02:41:28Z

Description

This aims to resolve #3569

BRAIN pattern method of Patterns command is implemented by combined UDF and UDAF.

Related Issues

Resolves #[Issue number to be closed when this PR is merged]

Check List

New functionality includes testing.
New functionality has been documented.
New functionality has javadoc added.
New functionality has a user manual doc added.
API changes companion pull request created.
Commits are signed per the DCO using --signoff.
Public documentation issue/PR created.

By submitting this pull request, I confirm that my contribution is made under the terms of the Apache 2.0 license.
For more information on following Developer Certificate of Origin and signing off your commits, please check here.

Signed-off-by: Songkan Tang <songkant@amazon.com>

penghuo · 2025-06-04T16:47:11Z

docs/user/ppl/cmd/patterns.rst

+* byClause: optional. The log groups to be labeled or aggregated. It could be fields and scalar functions.
+* pattern_method: optional. Specify pattern method to be simple_pattern. By default, it's simple_pattern if the setting ``plugins.ppl.default.pattern.method`` is not specified.
+* pattern_mode: optional. label mode or aggregation mode. Default is label mode.
+* pattern_max_sample_count: optional. The max sample logs to be returned per pattern in aggregation mode.


if it is optional, add default value in doc.

Done. Added default value

penghuo · 2025-06-04T16:49:30Z

docs/user/ppl/cmd/patterns.rst

+* pattern_method: optional. Specify pattern method to be brain. By default, it's simple_pattern if the setting ``plugins.ppl.default.pattern.method`` is not specified.
+* pattern_mode: optional. label mode or aggregation mode. Default is label mode.
+* pattern_max_sample_count: optional. The max sample logs to be returned per pattern in aggregation mode.
+* pattern_buffer_limit: optional. This is a special safeguard parameter for BRAIN algorithm to limit internal temporary buffer to hold processed logs.


remove "special"

penghuo · 2025-06-04T16:50:57Z

docs/user/ppl/cmd/patterns.rst

+* pattern_max_sample_count: optional. The max sample logs to be returned per pattern in aggregation mode.
+* pattern_buffer_limit: optional. This is a special safeguard parameter for BRAIN algorithm to limit internal temporary buffer to hold processed logs.


if it is optional, add default value in doc.

Done. Added default value.

penghuo · 2025-06-04T16:54:07Z

docs/user/ppl/cmd/patterns.rst

 or

-patterns [new_field=<new-field-name>] [pattern=<pattern>] <field> SIMPLE_PATTERN
+patterns <field> [by byClause...] pattern_method=SIMPLE_PATTERN [pattern_mode=LABEL | AGGREGATION] [pattern_max_sample_count=integer] [new_field=<new-field-name>] [pattern=<pattern>]


nit,
all options are under pattern command, we can simpliy it, for instance pattern_method -> method, pattern_mode -> mode

Renamed those options

penghuo · 2025-06-04T16:58:41Z

docs/user/ppl/cmd/patterns.rst


+* field: mandatory. The field must be a text field.
+* byClause: optional. The log groups to be labeled or aggregated. It could be fields and scalar functions.
+* pattern_method: optional. Specify pattern method to be brain. By default, it's simple_pattern if the setting ``plugins.ppl.default.pattern.method`` is not specified.


brain or BRAIN, is it case-sensitive?

I think it should be case-insensitive. @songkant-aws please double check. I suggest to use lower case in syntax doc.

It's case-insensitive. Now they are all in lower cases in syntax doc.

penghuo · 2025-06-04T17:00:26Z

docs/user/ppl/cmd/patterns.rst


+* field: mandatory. The field must be a text field.
+* byClause: optional. The log groups to be labeled or aggregated. It could be fields and scalar functions.
+* pattern_method: optional. Specify pattern method to be brain. By default, it's simple_pattern if the setting ``plugins.ppl.default.pattern.method`` is not specified.


By default, it's simple_pattern if the setting plugins.ppl.default.pattern.method is not specified.

The default value is configured by the setting plugins.ppl.default.pattern.method.

penghuo · 2025-06-04T17:01:09Z

docs/user/ppl/cmd/patterns.rst

+* field: mandatory. The field must be a text field.
+* byClause: optional. The log groups to be labeled or aggregated. It could be fields and scalar functions.
+* pattern_method: optional. Specify pattern method to be brain. By default, it's simple_pattern if the setting ``plugins.ppl.default.pattern.method`` is not specified.
+* pattern_mode: optional. label mode or aggregation mode. Default is label mode.


ditto, The default value is configured by the setting?

penghuo · 2025-06-04T17:06:44Z

docs/user/ppl/cmd/patterns.rst

+    +-------------------------------------------------------------------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
+    | patterns_field                                                          | pattern_count | sample_logs                                                                                                                                                                                                                                                                                                                                                                                                               |
+    |-------------------------------------------------------------------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
+    | <*IP*> - <*> [<*>/Sep/<*>:<*>:<*>:<*> <*>] <*> <*> HTTP/<*><*>" <*> <*> | 4             | [177.95.8.74 - upton5450 [28/Sep/2022:10:15:57 -0700] "HEAD /e-business/mindshare HTTP/1.0" 404 19927,127.45.152.6 - pouros8756 [28/Sep/2022:10:15:57 -0700] "GET /architectures/convergence/niches/mindshare HTTP/1.0" 100 28722,118.223.210.105 - - [28/Sep/2022:10:15:57 -0700] "PATCH /strategize/out-of-the-box HTTP/1.0" 401 27439,210.204.15.104 - - [28/Sep/2022:10:15:57 -0700] "POST /users HTTP/1.1" 301 9481] |


In aggregation mode, does the pattern command collect sample values of IP addresses?

What is the output syntax of the pattern_fields? Can it be used directly in search queries?

in IT, the detected pattern is PacketResponder failed <token1> blk_<token2> what does IP means, is it token?

Yes, when Calcite is enabled, the sample logs will be converted to sample tokens in different position.

pattern_field will be string in format like PacketResponder failed <token1> blk_<token2>. tokens will be a map like {token1: [...], token2: [...]}. Not sure what does it mean by using them directly in search queries?

<IP> is one of variable placeholder of BrainLogParser's output. Yes, it's token in V2 output format.

I have updated the syntax docs with more examples. When Calcite is enabled, the output syntax is a pattern string with <token*> placeholder plus a map of corresponding tokens. User can leverage those two output columns for further query.

Not sure what does it mean by using them directly in search queries?

For instance,
As user, the detected pattern is <token1> - <token2> [<token3>/Sep/<token4>:<token5>:<token6>:<token7> <token8>] <token9> <token10> HTTP/<token11><token12>\" <token13> <token14>, I want to search logs match this pattern.
I think it can not been directly used, need to rewise the query, it is out of scope this PR.

LantaoJin

Please update the code base and address the latest commends.

LantaoJin · 2025-06-07T02:13:34Z

docs/user/ppl/cmd/patterns.rst


+* field: mandatory. The field must be a text field.
+* byClause: optional. The log groups to be labeled or aggregated. It could be fields and scalar functions.
+* pattern_method: optional. Specify pattern method to be brain. By default, it's simple_pattern if the setting ``plugins.ppl.default.pattern.method`` is not specified.


I think it should be case-insensitive. @songkant-aws please double check. I suggest to use lower case in syntax doc.

LantaoJin · 2025-06-07T02:20:17Z

ppl/src/main/antlr/OpenSearchPPLLexer.g4

+PATTERN_MODE:                       'PATTERN_MODE';
+PATTERN_METHOD:                     'PATTERN_METHOD';
+PATTERN_MAX_SAMPLE_COUNT:           'PATTERN_MAX_SAMPLE_COUNT';
+PATTERN_BUFFER_LIMIT:               'PATTERN_BUFFER_LIMIT';


How above simplify the arguments by remove pattern_ prefix?

Removed pattern_ prefix

LantaoJin · 2025-06-07T02:23:56Z

common/src/main/java/org/opensearch/sql/common/setting/Settings.java

    DEFAULT_PATTERN_METHOD("plugins.ppl.default.pattern.method"),
+    DEFAULT_PATTERN_MODE("plugins.ppl.default.pattern.mode"),
+    DEFAULT_PATTERN_MAX_SAMPLE_COUNT("plugins.ppl.default.pattern.max.sample.count"),
+    DEFAULT_PATTERN_BUFFER_LIMIT("plugins.ppl.default.pattern.buffer.limit"),


Can we remove the default part? It's meaningless IMO.
plugins.ppl.default.pattern.method -> plugins.ppl.pattern.method

Removed default part

Signed-off-by: Songkan Tang <songkant@amazon.com>

penghuo · 2025-06-10T16:38:04Z

docs/user/ppl/cmd/patterns.rst

+* max_sample_count: optional. Max sample logs returned per pattern in aggregation mode (default: 10). The max_sample_count is configured by the setting ``plugins.ppl.pattern.max.sample.count``.
+* buffer_limit: optional. Safeguard parameter for ``brain`` algorithm to limit internal temporary buffer size (default: 100,000, min: 50,000). The buffer_limit is configured by the setting ``plugins.ppl.pattern.buffer.limit``.
+* new_field: Alias of the output pattern field. (default: "patterns_field").
+* algorithm parameters: optional. Algorithm-specific tuning:


nit, this line is in italic format, is it expected? https://github.com/opensearch-project/sql/blob/8f468a5b92b4b3a2ca5fb2798f78f329c88b4581/docs/user/ppl/cmd/patterns.rst#syntax

penghuo · 2025-06-10T16:39:02Z

docs/user/ppl/cmd/patterns.rst

+* new_field: Alias of the output pattern field. (default: "patterns_field").
+* algorithm parameters: optional. Algorithm-specific tuning:
+    - ``simple_pattern`` : Define regex via "pattern".
+    - ``brain`` : Adjust sensitivity with variable_count_threshold (int > 0) and frequency_threshold_percentage (double 0.0 - 1.0).


nit, explain what is variable_count_threshold and frequency_threshold_percentage

penghuo · 2025-06-10T16:47:55Z

docs/user/ppl/cmd/patterns.rst

+    +-------------------------------------------------------------------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
+    | patterns_field                                                          | pattern_count | sample_logs                                                                                                                                                                                                                                                                                                                                                                                                               |
+    |-------------------------------------------------------------------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
+    | <*IP*> - <*> [<*>/Sep/<*>:<*>:<*>:<*> <*>] <*> <*> HTTP/<*><*>" <*> <*> | 4             | [177.95.8.74 - upton5450 [28/Sep/2022:10:15:57 -0700] "HEAD /e-business/mindshare HTTP/1.0" 404 19927,127.45.152.6 - pouros8756 [28/Sep/2022:10:15:57 -0700] "GET /architectures/convergence/niches/mindshare HTTP/1.0" 100 28722,118.223.210.105 - - [28/Sep/2022:10:15:57 -0700] "PATCH /strategize/out-of-the-box HTTP/1.0" 401 27439,210.204.15.104 - - [28/Sep/2022:10:15:57 -0700] "POST /users HTTP/1.1" 301 9481] |


Not sure what does it mean by using them directly in search queries?

For instance,
As user, the detected pattern is <token1> - <token2> [<token3>/Sep/<token4>:<token5>:<token6>:<token7> <token8>] <token9> <token10> HTTP/<token11><token12>\" <token13> <token14>, I want to search logs match this pattern.
I think it can not been directly used, need to rewise the query, it is out of scope this PR.

penghuo · 2025-06-10T18:29:10Z

core/src/main/java/org/opensearch/sql/calcite/udf/udaf/LogPatternAggFunction.java

+import org.opensearch.sql.common.patterns.PatternUtils.ParseResult;
+
+public class LogPatternAggFunction implements UserDefinedAggFunction<LogParserAccumulator> {
+  private int bufferLimit = 100000;


does bufferLimit needed?

penghuo · 2025-06-10T18:43:47Z

core/src/main/java/org/opensearch/sql/calcite/udf/udaf/LogPatternAggFunction.java

+  }
+
+  public static class LogParserAccumulator implements Accumulator {
+    private final List<String> logMessages;


Access to logMessages is threadsafe?

* Revert simple_pattern window function change to recover pushdown ability Signed-off-by: Songkan Tang <songkant@amazon.com> * Add SIMPLE_PATTERN patterns command support based on parse command Signed-off-by: Songkan Tang <songkant@amazon.com> * Address minor comments Signed-off-by: Songkan Tang <songkant@amazon.com> * Address comments part 2 Signed-off-by: Songkan Tang <songkant@amazon.com> * Make allowCast for pattern VARCHAR literal Signed-off-by: Songkan Tang <songkant@amazon.com> * Fix spotless Signed-off-by: Songkan Tang <songkant@amazon.com> * Minor ut failure fix Signed-off-by: Songkan Tang <songkant@amazon.com> * Brain patterns command in Calcite with combined UDF and UDAF Signed-off-by: Songkan Tang <songkant@amazon.com> * Revert debug flag Signed-off-by: Songkan Tang <songkant@amazon.com> * Minor ut failure fix Signed-off-by: Songkan Tang <songkant@amazon.com> * Minor ut failure fix part2 Signed-off-by: Songkan Tang <songkant@amazon.com> * Pick missing ast Window from main Signed-off-by: Songkan Tang <songkant@amazon.com> * Support agg and label mode and new model for patterns command Signed-off-by: Songkan Tang <songkant@amazon.com> * Remove unnecessary files and comments Signed-off-by: Songkan Tang <songkant@amazon.com> * Use uncollect_patterns table function to flatten patterns list Signed-off-by: Songkan Tang <songkant@amazon.com> * Fix partial UT Signed-off-by: Songkan Tang <songkant@amazon.com> * Add 3570 yaml tests Signed-off-by: Songkan Tang <songkant@amazon.com> * Fix plans in explain ITs Signed-off-by: Songkan Tang <songkant@amazon.com> * Fix pushdown ITs failure Signed-off-by: Songkan Tang <songkant@amazon.com> * Fix doctest examples for V2 engine results Signed-off-by: Songkan Tang <songkant@amazon.com> * Minor fix after rebasing Signed-off-by: Songkan Tang <songkant@amazon.com> * Uncomment build.gradle change Signed-off-by: Songkan Tang <songkant@amazon.com> * Address minor comment Signed-off-by: Songkan Tang <songkant@amazon.com> * Address patterns doc comments and fix conflicts Signed-off-by: Songkan Tang <songkant@amazon.com> * Fix doctest Signed-off-by: Songkan Tang <songkant@amazon.com> * Reuse expand command plan to replace hacky uncollect_patterns UDTF Signed-off-by: Songkan Tang <songkant@amazon.com> * Minor fix after resolving merge conflicts Signed-off-by: Songkan Tang <songkant@amazon.com> * Refactor duplicate building expand rel node logic Signed-off-by: Songkan Tang <songkant@amazon.com> * Fix the issue of expand command plan executing main query twice Signed-off-by: Songkan Tang <songkant@amazon.com> --------- Signed-off-by: Songkan Tang <songkant@amazon.com> Signed-off-by: xinyual <xinyual@amazon.com>

songkant-aws added 9 commits April 16, 2025 11:31

Revert simple_pattern window function change to recover pushdown ability

c06d3c3

Signed-off-by: Songkan Tang <songkant@amazon.com>

Add SIMPLE_PATTERN patterns command support based on parse command

39d99e6

Signed-off-by: Songkan Tang <songkant@amazon.com>

Address minor comments

81d0606

Signed-off-by: Songkan Tang <songkant@amazon.com>

Address comments part 2

8438c8e

Signed-off-by: Songkan Tang <songkant@amazon.com>

Make allowCast for pattern VARCHAR literal

f8e8945

Signed-off-by: Songkan Tang <songkant@amazon.com>

Fix spotless

48dcf64

Signed-off-by: Songkan Tang <songkant@amazon.com>

Minor ut failure fix

828685c

Signed-off-by: Songkan Tang <songkant@amazon.com>

Brain patterns command in Calcite with combined UDF and UDAF

f664da2

Signed-off-by: Songkan Tang <songkant@amazon.com>

Revert debug flag

1e1a3aa

Signed-off-by: Songkan Tang <songkant@amazon.com>

songkant-aws requested review from GumpacG, LantaoJin, MaxKsyunz, Swiddis, YANG-DB, Yury-Fridlyand, acarbonetto, anirudha, dai-chen, derek-ho, forestmvey, joshuali925, kavithacm, mengweieric, noCharger, penghuo, ps48, qianheng-aws, seankao-az and ykmr1224 as code owners April 22, 2025 02:41

Minor ut failure fix

89103c7

Signed-off-by: Songkan Tang <songkant@amazon.com>

songkant-aws added 4 commits May 26, 2025 11:35

Add 3570 yaml tests

07b8700

Signed-off-by: Songkan Tang <songkant@amazon.com>

Fix plans in explain ITs

05c6288

Signed-off-by: Songkan Tang <songkant@amazon.com>

Fix pushdown ITs failure

9889c88

Signed-off-by: Songkan Tang <songkant@amazon.com>

Fix doctest examples for V2 engine results

f372437

Signed-off-by: Songkan Tang <songkant@amazon.com>

songkant-aws requested review from LantaoJin and penghuo May 26, 2025 09:33

songkant-aws added 4 commits June 4, 2025 17:24

Merge branch 'main' into calcite-patterns-command-brain-pattern-method

070a378

Minor fix after rebasing

c7c249b

Signed-off-by: Songkan Tang <songkant@amazon.com>

Uncomment build.gradle change

e5898de

Signed-off-by: Songkan Tang <songkant@amazon.com>

Address minor comment

363e80f

Signed-off-by: Songkan Tang <songkant@amazon.com>

penghuo reviewed Jun 4, 2025

View reviewed changes

songkant-aws added 2 commits June 6, 2025 11:32

Merge branch 'main' into calcite-patterns-command-brain-pattern-method

c4bc1a2

Merge branch 'main' into calcite-patterns-command-brain-pattern-method

7e409c0

LantaoJin reviewed Jun 7, 2025

View reviewed changes

songkant-aws added 10 commits June 9, 2025 15:05

Address patterns doc comments and fix conflicts

e3ae665

Signed-off-by: Songkan Tang <songkant@amazon.com>

Fix doctest

2db8c68

Signed-off-by: Songkan Tang <songkant@amazon.com>

Reuse expand command plan to replace hacky uncollect_patterns UDTF

2a1a5b0

Signed-off-by: Songkan Tang <songkant@amazon.com>

Merge branch 'main' into calcite-patterns-command-brain-pattern-method

9a65548

Minor fix after resolving merge conflicts

fd6c839

Signed-off-by: Songkan Tang <songkant@amazon.com>

Merge branch 'main' into calcite-patterns-command-brain-pattern-method

eaa3862

Merge branch 'main' into calcite-patterns-command-brain-pattern-method

a72c5a7

Refactor duplicate building expand rel node logic

154b705

Signed-off-by: Songkan Tang <songkant@amazon.com>

Fix the issue of expand command plan executing main query twice

728dd83

Signed-off-by: Songkan Tang <songkant@amazon.com>

Merge branch 'main' into calcite-patterns-command-brain-pattern-method

8f468a5

penghuo reviewed Jun 10, 2025

View reviewed changes

penghuo approved these changes Jun 10, 2025

View reviewed changes

LantaoJin approved these changes Jun 11, 2025

View reviewed changes

LantaoJin merged commit e6ab4fb into opensearch-project:main Jun 11, 2025
22 checks passed

qianheng-aws mentioned this pull request Mar 18, 2026

Add songkant-aws as maintainer #5244

Merged

		* pattern_max_sample_count: optional. The max sample logs to be returned per pattern in aggregation mode.
		* pattern_buffer_limit: optional. This is a special safeguard parameter for BRAIN algorithm to limit internal temporary buffer to hold processed logs.

Conversation

songkant-aws commented Apr 22, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Description

Related Issues

Check List

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

songkant-aws Jun 9, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

LantaoJin left a comment

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

songkant-aws commented Apr 22, 2025 •

edited

Loading

songkant-aws Jun 9, 2025 •

edited

Loading