Understanding lookups

Hi all, I’m trying to understand exactly how lookups work…

From what I can tell, we can:

Create lookups, using jdbc as the ‘remote datasource’

My question is, lets say I have table in mysql:

countryId, countryName, countryDescription,countryAreaCode

now in druid I have a datasource,with dimension that is countryId.

Is there a way to define a lookup, or allow the lookup ‘expression’ to define which column to return?

Looking at the example on: https://druid.apache.org/docs/latest/querying/lookups.html

 "site_id": {
      "version": "v0",
      "lookupExtractorFactory": {
        "type": "cachedNamespace",
        "extractionNamespace": {
          "type": "jdbc",
          "connectorConfig": {
            "createTables": true,
            "connectURI": "jdbc:mysql:\/\/localhost:3306\/druid",
            "user": "druid",
            "password": "diurd"
          "table": "lookupTable",
          "keyColumn": "country_id",
          "valueColumn": "country_name",
          "tsColumn": "timeColumn"
        "firstCacheTimeout": 120000,
        "injective": true

I see the lookup explicitly calls out the valueColumn.

so I’m guessing I would in fact have to define 3 lookups correct?

i.e. countryidToName



Then in a query I could just …invoke… all 3 lookups if I needed all 3 returned?

Yes you are correct. You’ll have to create 3 lookups and write a query accordingly.



One …hopefully last follow up question:

The docs indicate cache-looked-ups, are good for 100s to tens of thousands…(since they are in memory)…

my question is where does this cache actually live? in query nodes? or hisoricals?..i.e. what if we had 1 or 2 lookups…that needed say a few 100k entries…

…could we not just increase ram?

Lookups are cached on the Historicals. Possibly on brokers with globally cached lookups. I’m not sure of this.

Lookups use the Java heap unless specified to use off heap using config

druid.lookup.namespace.cache.type (https://druid.apache.org/docs/latest/development/extensions-core/lookups-cached-global.html). So in theory increasing the java heap size should work but I’m not sure to what extent can this be increased to.