uniqMerge function in connector

[vlauzeckas]

hi, Clickhouse have unique and very powerful functionality called Aggregate state. Basically, you can aggregate "count distinct". More about this functionality with examples could found here: https://altinity.com/blog/2017/7/10/clickhouse-aggregatefunctions-and-aggregatestate

But due to Tableau limitations its not possible to use it properly. Its possible to be used by using RAWSQL functions. Example: RAWSQLAGG_INT("uniqMerge(%1)" , [MyKPI])

Problem is that "RAWSQL pass-through functions will not work with published data sources or with Tableau extracts.": https://help.tableau.com/current/pro/desktop/en-us/functions_functions_passthrough.htm

I wonder if it would be possible to create such a function on the connector level? Like you did with "RAND()", "UNHEX([my_string])" and etc.

This aggregatestate are very powerful functionality of clickhouse, it is very pity you can't create data sources using it.

[artshevchenko]

Hi! Of course we can add this function to the dialect. I would be grateful for a complete list of all the necessary functions with the -Merge combinator, so it will be easier to implement this functionality. As far as I understand, the implementation of functions with the -State combinator is not required, is it?

[vlauzeckas]

very glad to hear this! its hard for me to foresee what others may need, but i think those functions should cover needs of most of the cases:

count
min
max
sum
avg
uniq
uniqExact

And yes, only with merge combinators. so we will have uniqMerge, minMerge, sumMege and etc.

[vlauzeckas]

@artshevchenko we badly need this Aggregate state functionality, I am just not 100% sure that creating those functions will solve the problem. It works perfectly fine on Tableau desktop, but stops working once you publish the data source to the server. We are ready to pay to get this functionality, maybe it is possible to hire you to help us solve this? Is there a way to contact you?

[artshevchenko]

@vlauzeckas Hi! At the moment, the problem is not in financing, we have a technical problem. The AggregateFunction data type is not supported by the JDBC driver and this breaks the View Data functionality. When adding a table with an AggregateFunction column to the Data Source, this column is displayed as a String in Tableau, and the only way to use this column is to create a new Calculated Field with an expression like COUNTD_MERGE([AggregateFunction]). Any other scenarios lead to errors. If adding new features will be enough for you, we can soon send a beta version of the connector with this functionality

[vlauzeckas]

Yes, i understand that this should be used very explicitly.

[juliusstep]

Hi @artshevchenko,

As @vlauzeckas noted, we understand the limitations of adding uniqMerge() functionality. And understand that implementation of such function might break expected behavior in Tableau Desktop when previewing data. We could try to adjust tdd file and build taco file by our self. But we can't sign that file. And to use unsigned connector in Tableau Desktop and Server we would need to overwrite settings by disabling plugin signature verification. Which is currently not possible on our side. So we are more than ok if such functions would be added as experimental functionality in upcoming release with a notes in README.

[artshevchenko]

@vlauzeckas @juliusstep Hi! Please share your feedback on this beta version of the connector. In this version, the functions COUNTD_MERGE() and COUNTD_UNIQ_MERGE() have been added. clickhouse_jdbc_0.2.6.1.taco.zip

[vlauzeckas]

We tested it out and it works perfectly. if you could release this, we could update connector and reports on our production servers

[vlauzeckas]

is there any plan to release new version with those changes?