somandal
commented
6 months ago can you set: "pinot.broker.result.rewriter.class.names" to the value "org.apache.pinot.core.query.utils.rewriter.ParentAggregationResultRewriter" and try again?
Open minwoo-jung opened 6 months ago
somandal
commented
6 months ago can you set: "pinot.broker.result.rewriter.class.names" to the value "org.apache.pinot.core.query.utils.rewriter.ParentAggregationResultRewriter" and try again?
minwoo-jung
commented
6 months ago @somandal I haven't tried it yet. I wanted to make sure I was guided correctly before I tried it and added the settings. Since it's a live service, I want to set it up properly the first time.
I'm not sure if I only need to add pinot.broker.result.rewriter.class.names, so I'd like to confirm.
somandal
commented
6 months ago yes only the broker one is enough
minwoo-jung
commented
6 months ago @somandal Thank you for your quick reply. :) I will try adding the settings.
I have an additional question.
pinot.broker.client.queryPort=~~~
pinot.broker.routing.table.builder.class=~~~
... ...
pinot.broker.result.rewriter.class.names=org.apache.pinot.core.query.utils.rewriter.ParentAggregationResultRewriter @somandal I added the following settings to pinot-broker.conf as you suggested.
pinot.broker.result.rewriter.class.names=org.apache.pinot.core.query.utils.rewriter.ParentAggregationResultRewriterQuestion 1
However, I got the same error
I would like to ask you to confirm that the class I need to add is ParentAggregationResultRewriter .
Question 2 Also, I analyzed the code and it looks like the following settings should be added, right?
pinot.broker.query.rewriter.class.names=org.apache.pinot.sql.parsers.rewriter.ExprMinMaxRewriterI double checked the class and the result.rewriter class is correct
Yes you will need to ensure you also update the query.rewriter to include the org.apache.pinot.sql.parsers.rewriter.ExprMinMaxRewriter class.
ExprMinMaxTest has set both and you can reference that to validate the class names. I did verify in the code that only the pinot.broker configs need to be set up for these.
I thought I needed to add 'org.apache.pinot.sql.parsers.rewriter.ExprMinMaxRewriter', but you guided me to set up 'org.apache.pinot.core.query.utils.rewriter.ParentAggregationResultRewriter'. I would like to double-check that this is correct.
Yes you actually need to set up both, I earlier thought that the ExprMinMaxRewriter is included by default but it isn't
Is it an intended guide that requires users to add their own settings(pinot.broker.result.rewriter.class.names) to use EXPR_MIN / EXPR_MAX?
My understanding is that there is some overhead and we don't have exact numbers on how much overhead these rewriters add so we only enabled it for tables where this feature is needed. These rewriters will be called for all queries.
minwoo-jung
commented
6 months ago @somandal First of all, thank you for taking the time out of your busy schedule to double check and provide a detailed answer. I followed your guide and started an instance and ran some tests.
The expr_min and expr_max functions seem to work for the queries I want. However, I noticed a couple of issues.
A LIMIT is not working properly.
I ran the simple query below and noticed that it returns a lot of data even though the limit is 10.
SELECT
EXPR_MAX(hostName, fieldValue, hostName)
FROM systemMetricDouble
limit 10
B I also had intermittent query failures. The logs from the failures are shown below.
select * from systemMetricDouble limit 10AS does not work. When I run the following query, the columns that I specified as AS are not aliased. There is no problem with the resulting data.
SELECT
MAX(fieldValue) AS maxValue,
EXPR_MAX(hostName, fieldValue, hostName),
MIN(fieldValue) AS minValue,
EXPR_MIN(hostName, fieldValue, hostName),
DATETIMECONVERT(eventTime, '1:MILLISECONDS:EPOCH', '1:MILLISECONDS:EPOCH', '12100000:MILLISECONDS') AS eventTime
FROM systemMetricDouble
WHERE tenantId = 'pinpoint'
AND hostGroupName = 'AlphaPinpointWeb'
AND metricName = 'mem'
AND fieldName = 'used'
AND eventTime BETWEEN 1704101884000 AND 1704706684000
GROUP BY eventTime
ORDER BY eventTime asc
LIMIT 201Controller UI image
Cannot use aliases specified by AS in group by and order by. I also checked that the options related to expr_min and expr_max worked fine before I added them.
sql
SELECT
AVG(fieldValue) AS avgValue,
DATETIME_CONVERT(eventTime, '1:MILLISECONDS:TIMESTAMP', '1:MILLISECONDS:EPOCH', '12100000:MILLISECONDS') as avgTime
FROM systemMetricDouble
WHERE tenantId = 'pinpoint'
AND hostGroupName = 'AlphaPinpointWeb'
AND metricName = 'mem'
AND hostName = 'dev-pinpoint-olap02-ncl'
AND fieldName = 'used'
AND eventTime BETWEEN 1704101884000 AND 1704706684000
GROUP BY avgTime
ORDER BY avgTime asc
LIMIT 201error log
Error Code: 150
SQLParsingError:
org.apache.pinot.sql.parsers.SqlCompilationException: 'as(datetimeconvert(eventTime, null, null, null), avgTime)' should appear in GROUP BY clause.
at org.apache.pinot.sql.parsers.CalciteSqlParser.validateGroupByClause(CalciteSqlParser.java:220)
at org.apache.pinot.sql.parsers.CalciteSqlParser.validate(CalciteSqlParser.java:203)
at org.apache.pinot.sql.parsers.CalciteSqlParser.queryRewrite(CalciteSqlParser.java:552)
at org.apache.pinot.sql.parsers.CalciteSqlParser.compileSqlNodeToPinotQuery(CalciteSqlParser.java:486)Duplicate use of DATETIME_CONVERT function without using alias for group by and order by.
sql
SELECT
AVG(fieldValue) AS avgValue,
DATETIME_CONVERT(eventTime, '1:MILLISECONDS:TIMESTAMP', '1:MILLISECONDS:EPOCH', '12100000:MILLISECONDS') as avgTime
FROM systemMetricDouble
WHERE tenantId = 'pinpoint'
AND hostGroupName = 'AlphaPinpointWeb'
AND metricName = 'mem'
AND hostName = 'dev-pinpoint-olap02-ncl'
AND fieldName = 'used'
AND eventTime BETWEEN 1704101884000 AND 1704706684000
GROUP BY DATETIME_CONVERT(eventTime, '1:MILLISECONDS:TIMESTAMP', '1:MILLISECONDS:EPOCH', '12100000:MILLISECONDS')
ORDER BY DATETIME_CONVERT(eventTime, '1:MILLISECONDS:TIMESTAMP', '1:MILLISECONDS:EPOCH', '12100000:MILLISECONDS') asc
LIMIT 201The table schema and definitions can be found in the links below.
somandal
commented
6 months ago Please see this page for a list of limitations: https://docs.pinot.apache.org/configuration-reference/functions/arg_min-arg_max
Some of this still needs to be tested out more. I'm guessing the alias problem results in not being able to use expr_min/max with any AS expression but will need to confirm
minwoo-jung
commented
6 months ago @somandal Thank you for your response. are you one of the maintainers(core developer) for Pinot? If you are, may I ask you to bug fix the above issue?
Without going into details, queries that were working fine are failing when the "Use Multi-Stage Engine" option is enabled. I would like to know if the expr_min and expr_max functions are officially available.^^
Hi, I am an open source APM pinpoint developer. We are applying pinot to pinpoint and using it well to analyze our monitoring data. We will continue to actively use it a lot in the future. I am writing this because I have a problem while using the new features in 1.0.0.
Problem
I upgraded pinot to 1.0.0 to use the EXPR_MIN and EXPR_MAX functions.
I executed a very simple query in the console view of the controller and got the following error message.
Here is the code location where the problem occurred
Analysis
Conclusion&Question