https://linen.dev logo
Join Slack
Powered by
This message was deleted.
# general
s
This message was deleted.
s
Are you planning to add a new extension or a new SQL function ?
p
> Are you planning to add a new extension or a new SQL function ? yes an SQL function. We have an already defined expression Macro that we use it in native queries. Now we want to use it with SQL.
s
I'll start with taking a look at some merged PRs which add such new SQL functions. Some examples are https://github.com/apache/druid/pull/15212, https://github.com/apache/druid/pull/13061
🙌 1
p
Thanks will take a look at these. But these look from the DataSketches.
^different from DataSketches
s
Yes if the goal is to add a new function since you have an existing macro, you might not need an extension but just add a new SQL function using the pieces that you already have.
p
Sorry did not get your last statement. you mean to say implement all the interfaces that I mentioned in the post above?
Also I see the PR that is referenced by you https://github.com/apache/druid/pull/13061 is adding feature to the druid repository. But my use case is internal and to have a custom sql function independent of changes in druid code.
a
Thanks for the help so far @Soumyava Das. Our situation in more detail: • We have a custom druid extension that we have developed ourselves. • The extension exposes several UDFs (implemented via classes that implement 
ExprMacro
). • We have so far only used these UDFs via native queries, but are interested in exposing them SQL as well so that we can move to SQL queries for everything. What we need help with is understanding how to write calcite extensions to expose these UDFs in sql. For reference our UDFs generally take in 1-2 double sketches + 1-2 longs/doubles as inputs, and have a double as output.
Example native call might look like:
Copy code
"virtualColumns": [
    {
      "type": "expression",
      "name": "wasserstein_distance",
      "expression": "approximateNumericalDrift(\"basesplit.baseline_quantile_sketch_raw\", \"actual_quantile_sketch_raw\", \u0027WASSERSTEIN\u0027)"
    }
  ],
which we want to convert to something like:
Copy code
SELECT approximateNumericalDrift(\"basesplit.baseline_quantile_sketch_raw\", \"actual_quantile_sketch_raw\", \u0027WASSERSTEIN\u0027) as wasserstein_distance"
What would be a good example to look at for this use case? I see that DS_GET_QUANTILE is implemented at https://github.com/apache/druid/blob/5c3391a084d9bb8015bf9304dde4efbc21cd3cb4/exte[…]ches/quantiles/sql/DoublesSketchQuantileOperatorConversion.java, would that be a good example or would the PR you linked above for array quantiles be better?
p
Cc: @Kumar Saket
Thankyou @Soumyava Das with the example you have provided, we were able to progress and create a simple query that works. The new SQL function is 
DATA_QUALITY_OUT_OF_RANGE
Copy code
SELECT DATA_QUALITY_OUT_OF_RANGE(0.5, 0.99, DS_QUANTILES_SKETCH("double_sketch", 128)) FROM "my_table"
WHERE "count" > 1000
But when we try to use a Group By query
Copy code
SELECT my_col, DATA_QUALITY_OUT_OF_RANGE(0.5, 0.99, DS_QUANTILES_SKETCH("double_sketch", 128)) FROM "my_table"
WHERE "count" > 1000
GROUP BY 1
, it gives us the following error
Copy code
Error: Unknown exception
Cannot construct instance of `org.apache.druid.query.topn.TopNQuery`, problem: com/truera/expressions/OutOfRangeExpMacro$1DriftExpr at [Source: (org.eclipse.jetty.server.HttpInputOverHTTP); line: -1, column: 59291]
com.fasterxml.jackson.databind.exc.ValueInstantiationException
Could you help us on this?
s
Can you please run with 
"useApproximateTopN": false
in the query context. Also you can set 
"debug":true
in the query context that will give you a detailed log. Will appreciate it if you can run and share the log
m
Yes, the log will be key. @Phani Sai Chand Gali are you able to run this code in IntelliJ or other debugger? That's probably going to be required if you are going to support this extension long term, and probably turn it over to others eventually?
p
Thanks @Soumyava Das and @Mike Sherman I will try these from my end and will let you know.
Open in Slack
PreviousNext
Powered by