Custom SQL function (STRING_AGG)

1

Hey. I need to use a specific function provided by Postgres - string_agg.
I thought it would be enough to supplement the corresponding arp-module, but that was naive on my part :slight_smile: If I add a description of a new function to the arp module, and then try to call it, I get an error:

org.apache.calcite.sql.validate.SqlValidatorException) No match found for function signature STRING_AGG(, )

As far as I can understand, this is due to the fact that dremo uses calcite to parse and optimize queries. In turn, calcite only supports basic sql functions (they have an enumeration of them).

Is there some way to add support for a custom sql function at the dremo level on my own, or will I have to delve into the sources of calcite, etc.?
Do I understand correctly that arp allows you to write adapters that implement only the basic SCL functions (listed in the documentation) and do not imply their declarative extension?

Thx.

2

I see few options:
a) try to define new aggregation function in dremio (u have to get into java codebase and code it there, then also define it for pushdown in ARP)
b) use new feature of dremio, external queries… there u are not limited by calcite or any other dremio function… But u wont take advantage of reflections etc. https://docs.dremio.com/data-sources/external-queries.html

1 Like
3

Thanks, the second option is what I need.

If someone will use this answer - the functionality is available since version 7.3

4

@Arol @froxCZ

In Dremio 4.7 we have added External Queries, with this feature you can write an source level SQL that Dremio will not validate and simply push it to the source, the list of supported sources are listed in the page

Thanks
Bali

5

Found a way to do this. Posted in a similar thread Function string_agg() postgresql data base - #7 by donatobarone

1 Like