SQL "pagination"
I'm curious about a dynamic process to "paginate" from a SQL source. I was presented with the problem today and I'm curious if I can build something to do that.
So I have a source that can have 10 rows one day and 1M rows the next, however the query can only handle say ~10k rows at a time. So I would like a dynamic chunking/pagination utilizing limit/offset. Given X=10k, query "SELECT * FROM foo OFFSET 0 LIMIT X", if row count = X, "SELECT * FROM foo OFFSET X LIMIT X"... until row count < X. That makes sense right?
The first approach I can think of is to query the source for an aggregate and pass these results to an EXECUTE PIPELINE job that queries in series and then union/gate everything together at the end. I just don't like the aggregate against the source, any better ideas?
Thanks!