https://pinot.apache.org/ logo
Join Slack
Powered by
Hi Team, This is regarding, queries fired by Supe...
# troubleshooting
a
Hi Team, This is regarding, queries fired by Superset over Pinot. We observed that charts are loading with high latency, but when hitting SQL queries its fast. Upon checking the query fired by Superset, we found that superset is using DATETIMECONVERT in select statement ( for timeseries charts ). Can someone advice?
s
hi Anish — it looks like the pinot database engine in Superset is using datetimeconvert in some cases: https://github.com/apache/superset/blob/master/superset/db_engine_specs/pinot.py#L65
I’m guessing your goal here is to avoid using 
DATETIMECONVERT()
because it’s extra load / work right?
Generally speaking — this means we need 2 things: • For the column type in Pinot you hope to use for datetime operations to be set to the optimal type (maybe its TIMESTAMP in Pinot? datetime? Someone else can chime in here! I do see 
# In pinot the output is a string since there is no timestamp column like pg
in the comment for the pinot engine in Superset 🤔) • For Superset to “know” / recognize the right column type
do you mind pasting a screenshot here of your Edit Dataset view, that shows the metadata in Superset?
a
Sure will share. The time column datatype is of string type.
image.png
🌟 1
in DATETIME FORMAT: %Y%m%d%H
s
ah yeah this is the issue
Superset either: • expects a datetime-y column type and goes with it • or is forced to convert a string to the datetime-y column type (hence the extra DATETIMECONVERT() SQL function call)
so your best bet is to register the optimal type in your database itself so Superset can avoid doing #2
@Chinmay Soman @Kenny Bastani what’s the correct datetime-y column type in Pinot here that y’all recommend? 🤔 I’m not a Pinot expert so I thought I’d ask y’all!
c
One way would be to create a new column of format epoch ms during ingestion . That way it’ll make the query side fast
s
this would be timestamp with epoch[ms] right?
c
This can be used during ingestion transform
To create a new column
m
s
boom love it!
c
Nice ! I forgot about this - great job @Mark Needham!
j
I think Superset might still run into problems later on down the line in the SQLAlchemy dialect for Pinot component. I believe the TIMESTAMP data type is not yet supported.
a
So, having a timestamp datatype column wont work currently?
j
I’ve just started my journey with Superset and this is what I have observed: • If you use the Superset SQL editor and run SQL statements against Pinot tables that contain columns with a TIMESTAMP data type, this works fine. • However, I get an error when trying to import a Pinot table that contain columns with a TIMESTAMP data type into a Superset dataset.
a
@Xiang Fu @Kishore G will it be possible to check this out please?
x
You are right, we need to add new data types support in python lib
a
any plan to pick this up?
c
Just to be clear - we're not suggesting use a different type for the new column. You can create a new column of type long that happens to store epoch values. That should work with Superset - unless if I missed smoething ? @Xiang Fu @Jeff Moszuti
x
You are right, cause pinot introduces TIMESTAMP BOOLEAN and JSON type in 0.8, when you create a table with these types, old pinot python client cannot understand the type
So we need to upgrade python client
c
👍
a
Hey @Xiang Fu, any plans to upgrade the py client ?
x
It’s already upgraded, please check
a
Okay thanks. This module is part of Pinot ?
x
no, you can find pinotdb on pypi
it’s just a pinot python client
a
got it.
trying this out.
In Pinot, we have column with type Timestamp and we are storing epoch milliseconds. So after changing the py client version, superset dataset accepted the timestamp column but it is considering it as LONG.
image.png
image.png
Superset is making the following query, this is expected ? @Xiang Fu
Copy code
SELECT DATETIMECONVERT(statsdatehour_epoch, '1:MILLISECONDS:EPOCH', '1:MILLISECONDS:EPOCH', '1:DAYS'),
       sum(metric1) AS sum_1
FROM reporting_aggregations
WHERE statsdatehour_epoch >= 1646812948000
  AND statsdatehour_epoch < 1647417748000
GROUP BY DATETIMECONVERT(statsdatehour_epoch, '1:MILLISECONDS:EPOCH', '1:MILLISECONDS:EPOCH', '1:DAYS')
ORDER BY sum(metric1) DESC
LIMIT 100;
x
yes, superset still treat timestamp column as long millis value
a
So, DATETIMECONVERT will be always invoked by superset? The actual issue reported in this thread was DATETIMECONVERT is causing query latency.
x
yes, that’s the issue from superset
we need to update that later on
we are also working on improving the query perf
3 Views
Open in Slack
PreviousNext
Powered by