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).
...

--

--

--

Java, Golang, PHP, JavaScript, Databases

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Top 8 Ways to Speed up the Development Process

Python — Extracting Email addresses and domain names from strings

Migration to Azure Cloud: when a correct challenge mapping leads to increased availability

Best Circuit Simulators

Validator Nodes | Era Swap Mission & Vision

Python — Retrieve matching rows from two Dataframes

How to Export Data on Excel from the EOS Public Database

Publish your Progressive Web App to the Google Play Store

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
Eli Segev

Eli Segev

Java, Golang, PHP, JavaScript, Databases

More from Medium

What is go.mod?

Golang language variable declaration methods and use scenarios

Echo 101: Get service metadata from Web UI

Search Location with Golang and Elasticsearch Geo Location (pt. 2)