When I run join queries on cachedNamespace -jdbc- lookups some queries work and others don’t.
- Query 1 (Works Only with GroupBy)
select sr.siteid, lr.v as sitename
from atc_battery_readings sr
inner join lookup.sitename lr on lr.k = cast(sr.siteid as varchar)
WHERE __time >= CURRENT_TIMESTAMP - INTERVAL '1' HOUR
group by 1,2
Without GroupBy --> Error: dataSource is not joinable: LookupDataSource{lookupName='sitename'}
- Query 2 (Works Only with GroupBy)
select siteid, lookup(cast(siteid as varchar),'sitename') as sitename
from atc_battery_readings
WHERE __time >= CURRENT_TIMESTAMP - INTERVAL '1' HOUR
group by 1,2
Without GroupBy --> Error: Unknown exception Lookup [sitename] not found
- Query 3
select sr.siteid, lr.v as sitename
from atc_battery_readings sr
inner join lookup.jdbctest lr on lr.k = cast(sr.siteid as varchar)
WHERE __time >= CURRENT_TIMESTAMP - INTERVAL '1' HOUR
group by 1,2
Error: dataSource is not joinable: LookupDataSource{lookupName='jdbctest'}
Things I've tried
- I made sure these 2 params are defined across the cluster
druid.lookup.lookupTier=__default
druid.lookup.lookupTierIsDatasource=false
- Running a Cron job to populate Map lookups instead of cachedNamespace, everything works fine in that scenario.
Logs
org.apache.druid.sql.http.SqlResource - Failed to handle query: SqlQuery{query='select
druid-broker | sr.siteid,
druid-broker | lookup(cast(sr.siteid as varchar),'sitename') as sitename
druid-broker | from sitedata sr
druid-broker |
druid-broker |
druid-broker | ', resultFormat=ARRAY, header=true, context={sqlOuterLimit=100}, parameters=[]}
druid-broker | org.apache.druid.query.QueryInterruptedException: Lookup [sitename] not found
druid-broker | at org.apache.druid.client.JsonParserIterator.convertException(JsonParserIterator.java:268) ~[druid-server-0.21.1.jar:0.21.1]
druid-broker | at org.apache.druid.client.JsonParserIterator.init(JsonParserIterator.java:183) ~[druid-server-0.21.1.jar:0.21.1]
druid-broker | at org.apache.druid.client.JsonParserIterator.hasNext(JsonParserIterator.java:93) ~[druid-server-0.21.1.jar:0.21.1]
druid-broker | at org.apache.druid.java.util.common.guava.BaseSequence.makeYielder(BaseSequence.java:89) ~[druid-core-0.21.1.jar:0.21.1]