# Data Snapshot using KSQL

**URL:** <https://forum.confluent.io/t/data-snapshot-using-ksql/3204>\
**Category:** ksqlDB\
**Created:** [26 October 2021 15:17 UTC](https://forum.confluent.io/t/data-snapshot-using-ksql/3204 "2021-10-26T15:17:34Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Vintron](https://avatars.discourse-cdn.com/v4/letter/v/439d5e/32.png) [@Vintron](https://forum.confluent.io/u/Vintron)\
**Post date:** [26 October 2021 15:17 UTC](https://forum.confluent.io/t/data-snapshot-using-ksql/3204/1 "2021-10-26T15:17:34Z")

</div>

Hi there

I am hoping you can help.

Is there any way to terminate & catch the response without using limit?

{  
“ksql”: “select \* from USER\_STREAM WHERE ID \<=10 EMIT CHANGES;”,  
“streamsProperties”: {“auto.offset.reset”:“earliest”}  
}

When in the Confluent Control Centre (CC) or running this via the CLI I can see this stream being populated in real-time.

There are only 4 rows (ID between 1-4) in the stream. So this query will never end unless I append with ‘limit 4’, anything more and the query will keep running which, I appreciate, is intended & fine when working in a CLI or Confluent CC, or in an application that can appear to stream the data.

However, I want to query this stream outside the KSQL CLI or Confluent CC- say in Postman or calling upon the MSXML2.serverXMLHTTP functionality within SQL or VBA, whereby the query needs to terminate to be able to get the response - giving me a snapshot in time of the data on how it currently stands.

Upon a bit of research I understood that I am to “materialize” the stream in to a table, somehting like this:  
CREATE TABLE Materialised\_Users AS  
SELECT  
ID,  
LATEST\_BY\_OFFSET(Firstname) AS Firstname,  
LATEST\_BY\_OFFSET(Lastname) AS Lastname,  
LATEST\_BY\_OFFSET(Email) AS Email  
FROM USER\_STREAM  
GROUP BY ID  
EMIT CHANGES;

Which allows me to perform queries such as  
{  
“ksql”: “select \* from MATERIALISED\_USERS WHERE ID =2;”,  
“streamsProperties”: {“auto.offset.reset”:“earliest”}  
}

Which terminates and gives a response in VBA/Postman etc.

However if I wanted to perform a more ambiguous query such as  
{  
“ksql”: “select \* from MATERIALISED\_USERS WHERE ID \<=10;”,  
“streamsProperties”: {“auto.offset.reset”:“earliest”}  
}

I get the response:

“error\_code”: 40001,

“Bound on ‘ID’ must currently be ‘=’. Add EMIT CHANGES if you intended to issue a push query. Pull queries require a WHERE clause that: - limits the query to a single key, e.g. `SELECT * FROM X WHERE <key-column>=Y;`.”

But adding EMIT CHANGES puts me back to the never terminating issue again and I don’t just want to pull one record back.

I hope this makes sense, happy to provide more info on request but any help would be really appreciated.

Thanks,  
Stu

---

<div class="post-metadata">

**Author:** ![mjsax](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/mjsax/32/3113_2.png) [@mjsax](https://forum.confluent.io/u/mjsax)\
**Post date:** [26 October 2021 16:24 UTC](https://forum.confluent.io/t/data-snapshot-using-ksql/3204/2 "2021-10-26T16:24:05Z")

</div>

Seems you are using an older version of ksqlDB. Since version 0.17.0, range pull queries on tables are supported.

Cf. [Announcing ksqlDB 0.17.0 - New Features and Updates](https://www.confluent.io/blog/announcing-ksqldb-0-17-0-new-features-and-updates/#table-scans)

Btw: we are also working on “pull queries on STREAMS”: [ksql/klip-53-pull-queries-on-streams.md at ecb43e2ffb8450b3902968a2709c86898291dbc7 · confluentinc/ksql · GitHub](https://github.com/confluentinc/ksql/blob/ecb43e2ffb8450b3902968a2709c86898291dbc7/design-proposals/klip-53-pull-queries-on-streams.md) – Ie, it should be allowed to issue `SELECT * FROM stream` without the `EMIT CHANGES` clause and the query will terminate when it reaches the end of the topic, thus the workaround to create a TABLE won’t be necessary any longer.

---

<div class="post-metadata">

**Author:** ![Vintron](https://avatars.discourse-cdn.com/v4/letter/v/439d5e/32.png) [@Vintron](https://forum.confluent.io/u/Vintron)\
**Post date:** [28 October 2021 09:57 UTC](https://forum.confluent.io/t/data-snapshot-using-ksql/3204/3 "2021-10-28T09:57:14Z")

</div>

Hi Matthias,

Thank you for your response. I will look to update the ksqlDB version and try the create table work around again.  
Nice to know you are working on allowing pull queries on streams.

I will aim to feedback after I have updated ksqlDB.

Thanks,  
Stu

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex019/uploads/confluentcommunity/original/1X/c49438c90c9df282e9996fdf6971be890c71b65a.svg) [@system](https://forum.confluent.io/u/system)\
**Post date:** [25 November 2021 15:18 UTC](https://forum.confluent.io/t/data-snapshot-using-ksql/3204/4 "2021-11-25T15:18:08Z")

</div>

Closed on 25 November 2021 15:18 UTC

This topic was automatically closed after 30 days. New replies are no longer allowed.
