This message was deleted.
# generals
Slackbot
11/30/2023, 1:22 PMThis message was deleted.
j
Julian Reyes
11/30/2023, 1:28 PMI know it is possible by using JavaScript function but I do not have it enabled in my cluster for security reasons.
Julian Reyes
11/30/2023, 2:48 PMI found this but for some reason it does nothing
Copy code
{
"type": "cascade",
"dimension": "network",
"outputName": "network",
"extractionFns": [
{
"type": "regex",
"expr": "([g,G][a,A][s,S]).*"
},
{
"type": "stringFormat",
"format": "[%s API]"
}
]
}l
Laksh Singla
11/30/2023, 3:11 PMYou can use the CASE..WHEN..THEN to achieve conditional transformation. It will use
case_searched Copy code
SELECT
CASE WHEN network = 'gas.5' THEN 'Gas API' ….. ELSE network AS transformedColumn
FROM tablej
Julian Reyes
11/30/2023, 3:11 PMI need to use native json queries as they are generated by code
l
Laksh Singla
11/30/2023, 3:12 PMYou can use “case_searched” then
Laksh Singla
11/30/2023, 3:12 PM Copy code
case_searched(expr1, result1, [[expr2, result2, ...], else-result]) is similar to CASE WHEN expr1 THEN result1 [ELSE else_result] END in SQLLaksh Singla
11/30/2023, 3:12 PMj
Julian Reyes
11/30/2023, 3:12 PMhow are they applied to native json queries?
Julian Reyes
11/30/2023, 3:14 PMAlso,
gas.5Julian Reyes
11/30/2023, 3:14 PMthats why the regex
l
Laksh Singla
11/30/2023, 3:14 PMif there’s a general pattern, then you can use a regex matching filter then instead of equality.
j
Julian Reyes
11/30/2023, 3:15 PMit is always
gas.XJulian Reyes
11/30/2023, 3:15 PMI thought of using https://druid.apache.org/docs/latest/querying/dimensionspecs/#cascade-extraction-functionhttps://druid.apache.org/docs/latest/querying/dimensionspecs/#cascade-extraction-function because if simplicity but doesnt work it seems
l
Laksh Singla
11/30/2023, 3:20 PMit should have, I guess. though the cascade would append ” API” to all the dimension values.
j
Julian Reyes
11/30/2023, 3:21 PMhmm looks like so although I though it will need to pass first regex func
l
Laksh Singla
11/30/2023, 3:21 PMcase_searched should allow that. You can add the transformation in the transformSpec and refer to that in the dimension section of the query.
Laksh Singla
11/30/2023, 3:22 PMTo make it easier, you can test out the stuff locally using SQL, and then checkout the explain plan for the query, and use that as a template.
j
Julian Reyes
11/30/2023, 3:23 PMbut I would need to touch the ingestion spec, if I just want the native query, is it possible?
l
Laksh Singla
11/30/2023, 3:24 PMj
Julian Reyes
11/30/2023, 3:31 PMI think I got it:
Copy code
"virtualColumns": [
{
"type": "expression",
"name": "virtual_network",
"expression": "case_searched(like(\"network\",'gas.%'),'Gas API',\"network\")",
"outputType": "STRING"
}
]
"dimensions": [
{
"type": "default",
"dimension": "method",
"outputName": "method"
},
{
"type": "default",
"dimension": "virtual_network",
"outputName": "network"
},
],