https://pinot.apache.org/ logo
m

Machhindra

05/27/2021, 6:23 PM
Trying to browse Pinot in Superset. Following query works fine in the Pinot Query console. BUT fails in Superset. Notice the special character in the metric. Is that something because of Pinot SQLAlchemy driver?
Copy code
SELECT DATETIMECONVERT(metricTime, '1:MILLISECONDS:EPOCH', '1:MILLISECONDS:EPOCH', '1:MINUTES'),
       AVG(metricValue) AS "AVG_1"
FROM metric_v6.metric_v6
WHERE metricTime >= 1621555200000
  AND metricTime < 1622160000000
  AND metric = 'CECCP%'
GROUP BY DATETIMECONVERT(metricTime, '1:MILLISECONDS:EPOCH', '1:MILLISECONDS:EPOCH', '1:MINUTES')
LIMIT 10000
Screenshot of the Superset SQL Editor -
m

Mayank

05/27/2021, 7:34 PM
Can you check broker log to see what query was sent to Pinot?
x

Xiang Fu

05/28/2021, 12:24 AM
Is it the % ?
It could be the python client translation
m

Machhindra

05/28/2021, 2:20 PM
AND metric = ‘CECCP%’. My metric name include “%”. I could run this sql successfully in Pinot Console, but fails in superset.
x

Xiang Fu

05/28/2021, 4:42 PM
I see, need to check if it’s an issue in python client
Is it a special char in python ? Do you need to make it %%?
m

Machhindra

05/28/2021, 4:45 PM
let me try… with “metric = ‘CECCCP%%’”
Escaping with % worked in Superset.
x

Xiang Fu

05/28/2021, 5:23 PM
ok
then we will try to fix that in python client
you can use %% for now, we will fix that
m

Machhindra

05/28/2021, 5:24 PM
Perfect.. thank you!
@Xiang Fu Just want to pass on an observation with Pinot-Driver and Superset. This is regarding the same problem reported above (special character % fails in Superset) When I am providing input, I can escape the SQL with ‘%%’. However I can not use the column which has the special character as “Group By <column> as well. Following error is seen in Superset
x

Xiang Fu

06/10/2021, 6:07 PM
what the query look like
m

Machhindra

06/10/2021, 6:25 PM
Here is the picture from Superset. Copying the SQL query generated by Superset (Notice that the ‘metric’ column in where clause is expanded to include a metric with special character automagically) -
Copy code
SELECT DATETIMECONVERT(metricTime, '1:MILLISECONDS:EPOCH', '1:MILLISECONDS:EPOCH', '1:DAYS'),
       product,
       metric,
       count(metricValue) AS count_1
FROM metric_v7.metric_v7
WHERE (product = 'BLAH'
       OR product = 'CLAH'
       AND metric = 'AECCP%')
GROUP BY product,
         metric,
         DATETIMECONVERT(metricTime, '1:MILLISECONDS:EPOCH', '1:MILLISECONDS:EPOCH', '1:DAYS')
LIMIT 10000;
x

Xiang Fu

06/11/2021, 5:29 AM
I think it’s still the issue of python
need to escape % to %%
Copy code
'AECCP%%'