Does it support materialized views ?

[snork-alt]

Does it support materialized views ?

[auxten]

Sure, here is an example:

#!/usr/bin/env python3

from chdb import session

sess = session.Session()

sess.query("CREATE DATABASE IF NOT EXISTS db_xxx ENGINE = Atomic")
sess.query("USE db_xxx")
sess.query(
    """
CREATE TABLE download (
  when DateTime,
  userid UInt32,
  bytes Float32
) ENGINE=MergeTree
PARTITION BY toYYYYMM(when)
ORDER BY (userid, when)"""
)
sess.query(
    """
INSERT INTO download
  SELECT
    now() + number * 60 as when,
    25,
    rand() % 100000000
  FROM system.numbers
  LIMIT 5000"""
)
ret = sess.query(
    """
SELECT
  toStartOfDay(when) AS day,
  userid,
  count() as downloads,
  sum(bytes) AS bytes
FROM download
GROUP BY userid, day
ORDER BY userid, day"""
)
print("Result from agg:", ret)

sess.query(
    """CREATE MATERIALIZED VIEW download_daily_mv
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(day) ORDER BY (userid, day)
POPULATE
AS SELECT
  toStartOfDay(when) AS day,
  userid,
  count() as downloads,
  sum(bytes) AS bytes
FROM download
GROUP BY userid, day"""
)
ret = sess.query(
    """SELECT * FROM download_daily_mv
ORDER BY day, userid 
LIMIT 5"""
)
print("Result from mv:", ret)

sess.query(
    """
INSERT INTO download
           SELECT
    now() + number * 60 as when,
    25,
    rand() % 100000000
  FROM system.numbers
  LIMIT 5000"""
)
ret = sess.query(
    """SELECT * FROM download_daily_mv
ORDER BY day, userid
LIMIT 5"""
)
print("Result from mv after insert:", ret)