clickhouse-sqlalchemy icon indicating copy to clipboard operation
clickhouse-sqlalchemy copied to clipboard

Feature request: support -Merge suffix on AggregateFunction types

Open HacKanCuBa opened this issue 10 months ago • 1 comments

First of all, thanks for all the work done here!

To the point, currently there's no way to deal w/ AggregateFunction types, so we need a way to issue, say, sumMerge instead of sum, uniqMerge instead of uniq, etc.

See this example of an Aggregated Mat View:

CREATE TABLE test.visits
 (
    StartDate DateTime64 NOT NULL,
    CounterID UInt64,
    Sign Nullable(Int32),
    UserID Nullable(Int32)
) ENGINE = MergeTree ORDER BY (StartDate, CounterID);
CREATE MATERIALIZED VIEW test.mv_visits
(
    StartDate DateTime64 NOT NULL,
    CounterID UInt64,
    Visits AggregateFunction(sum, Nullable(Int32)),
    Users AggregateFunction(uniq, Nullable(Int32))
)
ENGINE = AggregatingMergeTree() ORDER BY (StartDate, CounterID)
AS SELECT
    StartDate,
    CounterID,
    sumState(Sign) AS Visits,
    uniqState(UserID) AS Users
FROM test.visits
GROUP BY StartDate, CounterID;

Selecting data:

SELECT
    StartDate,
    sumMerge(Visits) AS Visits,
    uniqMerge(Users) AS Users
FROM test.mv_visits
GROUP BY StartDate
ORDER BY StartDate;

So, doing session.query(MatViewModel.StartDate, func.sum(MatViewModel.Visits), func.uniq(MatViewModel.Users)).group_by(MatViewModel.StartDate).order_by(MatViewModel.StartDate) should produce the correct SQL syntax.

Yes, I know we can do func.sumMerge, but that is not the solution if I'm trying to transparently change the underlying DB w/o changing my query.
On this line, would you know of a workaround for this? I'm currently exploring on letting SQLAlchemy know how to compile sum for CH.

HacKanCuBa avatar Aug 23 '23 20:08 HacKanCuBa