SQL Lookup query

Hi there!

I’ve created Lookup using JSON like this:

{
“__default”: {
“group_tov”: {
“version”: “v1”,
“lookupExtractorFactory”: {
“type”: “map”,
“map”: {‘test’: ‘test1’}
}
}
}
}

``

It’s successfully created. The response of GET request to http://localhost:8081/druid/coordinator/v1/lookups/config is:

["__default"]

So i want to use this lookup in my SQL query:

SELECT "BasePrice",
       LOOKUP(CAST(id_tov_cl AS VARCHAR), "group_tov")
FROM "druid"."vv_checklines"
LIMIT 10

``

But the answer is:

Unknown exception (org.apache.calcite.tools.ValidationException): org.apache.calcite.runtime.CalciteContextException: From line 2, column 43 to line 2, column 51: Column 'group_tov' not found in any table

``

Maybe someone can help me?

Thanks!

Hey Андрей,

It should be ‘group_tov’ (with single quotes) not “group_tov”.

Damn! Thanks! But now i have another error:

Unknown exception (java.lang.NullPointerException): Lookup [group_tov] not found

``

But i’ve created the lookup… Why it can’t see it?

Ok, i found in https://stackoverflow.com/questions/31697828/docker-run-name-is-already-in-use-by-container

You can try few of the things to get it resolve.

Delete all the lookups which you are not using
either use “__default” as tier name or mention you tier whatever you want but don’t forget to mention in ALL your runtime.properties with as druid.lookup.lookupTierIsDatasource=false druid.lookup.lookupTier=_default_tier
make sure GET is working on that lookup (see the documentation for get api for lookup )

``

  1. I’ve created own tier and now it work. But the question is why by default ‘__default’ tier not working?

Is it possible that when you first created the lookup you did not initialize the lookups?

In http://druid.io/docs/latest/querying/lookups.html see “If you have NEVER configured lookups before, you MUST post an empty json object {} to …”

If so maybe you initialized them by your second usage?

Maybe, yes. Now it’s work pretty fine.