CODEWITHSUNDEEP
apache-kafkaksqldb
JavaDuke
JavaDuke

Reputation: 143

How can I select only data within a specific window in KSQL?

I have a table with tumbling window, e.g.

CREATE TABLE total_transactions_per_1_days AS
SELECT
    sender,
    count(*) AS count,
    sum(amount) AS total_amount,
    histogram(recipient) AS recipients
FROM
    completed_transactions
WINDOW TUMBLING (
    SIZE 1 DAYS
)

Now I need to only select data from the current window, i.e. windowstart <= current time and windowend <= current time. Is it possible? I could not find any example.

Upvotes: 1

Views: 737

Answers (1)

Andrew Coates
Andrew Coates

Reputation: 1893

Depends what you mean when you say 'select data' ;)

ksqlDB supports two main query types, (see https://docs.ksqldb.io/en/latest/concepts/queries/).

If what you want is a pull query, i.e. a traditional sql query where you want to pull back the current window as a one time result, then what you want may be possible, though pull queries are a recent feature and not fully featured yet. As of version 0.10 you can only look up a known key. For example, if sender is the key of the table, you could run a query like:

SELECT * FROM total_transactions_per_1_days
   WHERE sender = some_value
   AND WindowStart <= UNIX_TIMESTAMP()
   AND WindowEnd >= UNIX_TIMESTAMP();

This would require the table to have processed data with a timestamp close to the current wall clock time for it to pull back data, i.e. if the system was lagging, or if you were processing historic or delayed data, this would not work.

Note: the above query will work on ksqlDB v0.10. Your success on older versions may vary.

There are plans to extend the functionality of pull queries. So keep an eye for updates to ksqlDB.

Upvotes: 1

Related Questions