Druid lookup query

Apache Druid Q&A Google Groups
#1

Hi,

I have created a lookup and registered with tier name ‘__default’ and lookupId name ‘site_id’.

This is my lookup query:

{
“queryType”: “groupBy”,
“intervals”: [“2018-11-17/2018-11-30”],
“granularity”: “all”,
“metric”: “cost”,
“dataSource”: {
“name”: “throughput”,
“type”: “table”
},
“aggregations”: [{
“fieldName”: “cost”,
“name”: “cost”,
“type”: “doubleSum”
}],
“postAggregations”: ,
“dimension”: {
“type”: “extraction”,
“dimension”: “user_id”,
“outputName”: “user_name”,
“extractionFn”: {
“type”:“registeredLookup”,
“lookup”:“site_id”
},
“replaceMissingValueWith”: “MISSING”,
“retainMissingValue”: false
},
“threshold”: 1000,
“limitSpec”: {
“type”: “default”,
“limit”: 500
}
}

When I am running this query

curl -L -H’Content-Type: application/json’ -XPOST --data-binary @quickstart/users-query.json http://localhost:8082/druid/v2/?pretty

I am not getting any result.

I have also added “`druid.lookup.lookupTier=__default” and “```druid.lookup.lookupTierIsDatasource=false” in broker/runtime.properties.``

Can you please help me where else I have to make changes.

Thanks

#2

Hi Monica :

This query with lookup worked for me. Hope it helps as an example.

{

"queryType": "scan",

"dataSource": {

  "type": "table",

  "name": "wikipedia"

},

"intervals": {

  "type": "intervals",

  "intervals": [

    "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"

  ]

},

"virtualColumns": [

  {

    "type": "expression",

    "name": "v0",

    "expression": "lookup(\"channel\",'countryLookup')",

    "outputType": "STRING"

  }

],

"resultFormat": "compactedList",

"batchSize": 20480,

"limit": 100,

"filter": null,

"columns": [

  "__time",

  "channel",

  "v0"

],

"legacy": false,

"context": {

  "timeout": 40000

},

"descending": false

}

#3

Hi,

What is channel and countryLookup in this:

“expression”: “lookup(“channel”,‘countryLookup’)”

Thanks

#4

Channel is the original column you can find in the wikipedia sample dataSource. And countryLookup is the I defined in the json I POSTed to coordinator

"__default": {

                "countryLookup": {

                        "version": "v1",

Here, the lookup translates the acronyms of channel, such as en , to English , etc.

hope this helps

Ming