Poll updates from Materialized View #36899
Unanswered
caueteixeira
asked this question in
Q&A
Replies: 0 comments
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
-
Hi,
I am implementing a process to calculate the number of products a client add to their inventory in real time. I have a base query that polls the results from a materialized view every x seconds and I want it to return only the updates between two different polls and not the full set every poll. I am aware of the live view, however I don't want to use experimental features in production.
Simple representation of my current set-up:
Source Table:
CREATE TABLE order
(
client_id UInt64,
product_id UInt32,
quantity Float64,
transaction_time DateTime
)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(transaction_time)
ORDER BY (transaction_time, client_id, product_id)
Target Table:
CREATE TABLE order_agg
(
client_id UInt64,
product_id UInt32,
total_quantity SimpleAggregateFunction(sum, Float64)
)
ENGINE=AggregatingMergeTree()
PARTITION BY tuple()
ORDER BY (client_id, product_id)
Materialized View:
CREATE MATERIALIZED VIEW order_mv
TO order_agg
AS
SELECT
client_id,
product_id,
sum(quantity) as total_quantity
FROM
order
GROUP BY
client_id,
product_id
Polling Query:
SELECT
client_id,
product_id,
sum(total_quantity) AS final_quantity
FROM
order_mv
GROUP BY
client_id,
product_id
Is it possible to configure the polling query so it just return the new updates every poll?
Thanks,
Caue
Beta Was this translation helpful? Give feedback.
All reactions