Druid complex Lookup using other dimensions, with native query or Golang

Datasource: items
-----------------
guid | customer_id | name | view_count
-------------------------------------
guid1 | custA | page1 | 12
guid2 | custA | page2 | 8
guid3 | custB | page1 | 15
Lookup 1: custA_metadata
------------------------
key | value
------------------------
guid1_background | dark
guid1_language | en-US
guid2_background | light
guid2_language | he-IL
Lookup 2: custB_metadata
------------------------
key | value
------------------------
guid3_background | dark
...

Creating the lookups

{
"__default": {
"custA_metadata": {
"version": "1",
"lookupExtractorFactory": {
"type": "map",
"map": {
"guid1_background": "dark",
"guid1_language": "en-US",
"guid2_background": "light",
"guid2_language": "he-IL"
}
}
}
}
}

Verify that the lookup was successfully created

GET http://localhost:8888/druid/coordinator/v1/lookups/config
GET http://localhost:8082/druid/listen/v1/lookups

GroupBy aggregation native query

{
"queryType": "groupBy",
"dataSource": {
"type": "table",
"name": "items"
},
"intervals": [
"2022-04-16T00:00:00Z/2022-04-16T23:59:59.999999999Z"
],
"dimensions": [
{
"type": "extraction",
"dimension": "name",
"extractionFn": {
"type": "stringFormat",
"format": "%s"
}
},
{
"type": "extraction",
"dimension": "customer_id",
"extractionFn": {
"type": "stringFormat",
"format": "%s"
}
},
{
"type": "extraction",
"dimension": "guid",
"outputName": "background",
"extractionFn": {
"type": "cascade",
"extractionFns": [
{
"type": "stringFormat",
"format": "%s_background"
},
{
"type": "registeredLookup",
"lookup": "custA_metadata",
"retainMissingValue": true
}
]
}
}
],
"filter": {
"type": "and",
"fields": [
{
"type": "in",
"dimension": "guid",
"values": [
"en-US"
],
"extractionFn": {
"type": "cascade",
"extractionFns": [
{
"type": "stringFormat",
"format": "%s_language"
},
{
"type": "registeredLookup",
"lookup": "custA_metadata",
"retainMissingValue": true
}
]
}
}
]
},
"granularity": "all",
"aggregations": [],
"postAggregations": []
}

GroupBy fetch with Go

dimensions := []builder.Dimension{
dimension.NewExtraction().
SetDimension("guid").
SetOutputName("background").
SetExtractionFn(extractionfn.NewCascade().
SetExtractionFns([]builder.ExtractionFn{
extractionfn.NewStringFormat().SetFormat("%s_background"),
extractionfn.NewRegisteredLookup().SetLookup("custA_metadata").
SetRetainMissingValue(true),
}))}
filters := filter.NewAnd().SetFields([]builder.Filter{
filter.NewIn().SetDimension("guid").
SetValues("en-US").
SetExtractionFn(extractionfn.NewCascade().
SetExtractionFns([]builder.ExtractionFn{
extractionfn.NewStringFormat().SetFormat("%s_language"),
extractionfn.NewRegisteredLookup().SetLookup("custA_metadata"),
}))})
q := query.NewGroupBy().
SetDimensions(dimensions).
SetFilter(filters).
...

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store