Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

KSQL tables are not grouping data by key #1876

Closed
ikovalyov opened this issue Sep 12, 2018 · 2 comments
Closed

KSQL tables are not grouping data by key #1876

ikovalyov opened this issue Sep 12, 2018 · 2 comments
Labels
question

Comments

@ikovalyov
Copy link

ikovalyov commented Sep 12, 2018 •
edited
Loading

  1. write data to the topic (I used avro)
  2. make sure topic created
ksql> print `timestamp-test` from beginning;
Format:AVRO
9/12/18 3:26:42 PM UTC, bd7715d5-97e8-43cd-9bf0-feb1df52f8e9, {"uuid": "bd7715d5-97e8-43cd-9bf0-feb1df52f8e9", "userId": 1, "organisationId": 27567, "timestamp": 1536765992, "type": "app:used", "payload": {"startTime": 1536765992, "endTime": 1536765993, "deviceId": 20, "metadata": "127.0.0.2"}}
9/12/18 3:26:42 PM UTC, d4f34e52-d772-4d90-9e8f-e33f8245d993, {"uuid": "d4f34e52-d772-4d90-9e8f-e33f8245d993", "userId": 1, "organisationId": 27567, "timestamp": 1536765993, "type": "app:used", "payload": {"startTime": 1536765993, "endTime": 1536765994, "deviceId": 20, "metadata": "127.0.0.1"}}
9/12/18 3:26:42 PM UTC, d58cea43-4548-4570-95a7-4ea641bd3114, {"uuid": "d58cea43-4548-4570-95a7-4ea641bd3114", "userId": 1, "organisationId": 27567, "timestamp": 1536765994, "type": "app:used", "payload": {"startTime": 1536765994, "endTime": 1536765995, "deviceId": 20, "metadata": "127.0.0.2"}}
9/12/18 3:26:42 PM UTC, 36863ea0-3b4c-43c6-8c0a-21ea0fa8b055, {"uuid": "36863ea0-3b4c-43c6-8c0a-21ea0fa8b055", "userId": 1, "organisationId": 27567, "timestamp": 1536765995, "type": "app:used", "payload": {"startTime": 1536765995, "endTime": 1536765996, "deviceId": 20, "metadata": "127.0.0.1"}}
9/12/18 3:26:42 PM UTC, a5385de5-3a10-4833-b0b3-d63bb33f049e, {"uuid": "a5385de5-3a10-4833-b0b3-d63bb33f049e", "userId": 1, "organisationId": 27567, "timestamp": 1536765996, "type": "app:used", "payload": {"startTime": 1536765996, "endTime": 1536765997, "deviceId": 20, "metadata": "127.0.0.2"}}
9/12/18 3:26:42 PM UTC, 91830dbc-2b89-47a3-abf5-14c8afb91de5, {"uuid": "91830dbc-2b89-47a3-abf5-14c8afb91de5", "userId": 1, "organisationId": 27567, "timestamp": 1536765997, "type": "app:used", "payload": {"startTime": 1536765997, "endTime": 1536765998, "deviceId": 20, "metadata": "127.0.0.1"}}
9/12/18 3:26:42 PM UTC, b01d3322-1b29-4a30-a95c-57539dfc2a04, {"uuid": "b01d3322-1b29-4a30-a95c-57539dfc2a04", "userId": 1, "organisationId": 27567, "timestamp": 1536765998, "type": "app:used", "payload": {"startTime": 1536765998, "endTime": 1536765999, "deviceId": 20, "metadata": "127.0.0.2"}}
9/12/18 3:26:42 PM UTC, ccf0e1fb-b43f-45e0-be42-28976461e280, {"uuid": "ccf0e1fb-b43f-45e0-be42-28976461e280", "userId": 1, "organisationId": 27567, "timestamp": 1536765999, "type": "app:used", "payload": {"startTime": 1536765999, "endTime": 1536766000, "deviceId": 20, "metadata": "127.0.0.1"}}
9/12/18 3:26:42 PM UTC, e95f1213-4f7c-4814-8a6e-5b7285b5cc6d, {"uuid": "e95f1213-4f7c-4814-8a6e-5b7285b5cc6d", "userId": 1, "organisationId": 27567, "timestamp": 1536766000, "type": "app:used", "payload": {"startTime": 1536766000, "endTime": 1536766001, "deviceId": 20, "metadata": "127.0.0.2"}}
9/12/18 3:26:42 PM UTC, 1009bebe-793a-4417-9fa7-315ec7131469, {"uuid": "1009bebe-793a-4417-9fa7-315ec7131469", "userId": 1, "organisationId": 27567, "timestamp": 1536766001, "type": "app:used", "payload": {"startTime": 1536766001, "endTime": 1536766002, "deviceId": 20, "metadata": "127.0.0.1"}}
9/12/18 3:26:42 PM UTC, bd7715d5-97e8-43cd-9bf0-feb1df52f8e9, {"uuid": "bd7715d5-97e8-43cd-9bf0-feb1df52f8e9", "userId": 1, "organisationId": 27567, "timestamp": 1536765992, "type": "app:used", "payload": {"startTime": 1536765992, "endTime": 1536765993, "deviceId": 20, "metadata": "data erased"}}
9/12/18 3:26:42 PM UTC, d4f34e52-d772-4d90-9e8f-e33f8245d993, {"uuid": "d4f34e52-d772-4d90-9e8f-e33f8245d993", "userId": 1, "organisationId": 27567, "timestamp": 1536765993, "type": "app:used", "payload": {"startTime": 1536765993, "endTime": 1536765994, "deviceId": 20, "metadata": "data erased"}}
9/12/18 3:26:42 PM UTC, d58cea43-4548-4570-95a7-4ea641bd3114, {"uuid": "d58cea43-4548-4570-95a7-4ea641bd3114", "userId": 1, "organisationId": 27567, "timestamp": 1536765994, "type": "app:used", "payload": {"startTime": 1536765994, "endTime": 1536765995, "deviceId": 20, "metadata": "data erased"}}
9/12/18 3:26:42 PM UTC, 36863ea0-3b4c-43c6-8c0a-21ea0fa8b055, {"uuid": "36863ea0-3b4c-43c6-8c0a-21ea0fa8b055", "userId": 1, "organisationId": 27567, "timestamp": 1536765995, "type": "app:used", "payload": {"startTime": 1536765995, "endTime": 1536765996, "deviceId": 20, "metadata": "data erased"}}
9/12/18 3:26:42 PM UTC, a5385de5-3a10-4833-b0b3-d63bb33f049e, {"uuid": "a5385de5-3a10-4833-b0b3-d63bb33f049e", "userId": 1, "organisationId": 27567, "timestamp": 1536765996, "type": "app:used", "payload": {"startTime": 1536765996, "endTime": 1536765997, "deviceId": 20, "metadata": "data erased"}}
9/12/18 3:26:42 PM UTC, 91830dbc-2b89-47a3-abf5-14c8afb91de5, {"uuid": "91830dbc-2b89-47a3-abf5-14c8afb91de5", "userId": 1, "organisationId": 27567, "timestamp": 1536765997, "type": "app:used", "payload": {"startTime": 1536765997, "endTime": 1536765998, "deviceId": 20, "metadata": "data erased"}}
9/12/18 3:26:42 PM UTC, b01d3322-1b29-4a30-a95c-57539dfc2a04, {"uuid": "b01d3322-1b29-4a30-a95c-57539dfc2a04", "userId": 1, "organisationId": 27567, "timestamp": 1536765998, "type": "app:used", "payload": {"startTime": 1536765998, "endTime": 1536765999, "deviceId": 20, "metadata": "data erased"}}
9/12/18 3:26:42 PM UTC, ccf0e1fb-b43f-45e0-be42-28976461e280, {"uuid": "ccf0e1fb-b43f-45e0-be42-28976461e280", "userId": 1, "organisationId": 27567, "timestamp": 1536765999, "type": "app:used", "payload": {"startTime": 1536765999, "endTime": 1536766000, "deviceId": 20, "metadata": "data erased"}}
9/12/18 3:26:42 PM UTC, e95f1213-4f7c-4814-8a6e-5b7285b5cc6d, {"uuid": "e95f1213-4f7c-4814-8a6e-5b7285b5cc6d", "userId": 1, "organisationId": 27567, "timestamp": 1536766000, "type": "app:used", "payload": {"startTime": 1536766000, "endTime": 1536766001, "deviceId": 20, "metadata": "data erased"}}
9/12/18 3:26:42 PM UTC, 1009bebe-793a-4417-9fa7-315ec7131469, {"uuid": "1009bebe-793a-4417-9fa7-315ec7131469", "userId": 1, "organisationId": 27567, "timestamp": 1536766001, "type": "app:used", "payload": {"startTime": 1536766001, "endTime": 1536766002, "deviceId": 20, "metadata": "data erased"}}
  1. create table CREATE TABLE timestamp_table WITH (kafka_topic='timestamp-test', value_format='AVRO', KEY='UUID');
  2. get the table info
ksql> describe extended timestamp_table;

Name                 : TIMESTAMP_TABLE
Type                 : TABLE
Key field            : UUID
Key format           : STRING
Timestamp field      : Not set - using <ROWTIME>
Value format         : AVRO
Kafka topic          : timestamp-test (partitions: 1, replication: 1)

 Field          | Type                                                                                   
---------------------------------------------------------------------------------------------------------
 ROWTIME        | BIGINT           (system)                                                              
 ROWKEY         | VARCHAR(STRING)  (system)                                                              
 UUID           | VARCHAR(STRING)                                                                        
 USERID         | INTEGER                                                                                
 ORGANISATIONID | INTEGER                                                                                
 TIMESTAMP      | INTEGER                                                                                
 TYPE           | VARCHAR(STRING)                                                                        
 PAYLOAD        | STRUCT<STARTTIME INTEGER, ENDTIME INTEGER, DEVICEID INTEGER, METADATA VARCHAR(STRING)> 
---------------------------------------------------------------------------------------------------------

Local runtime statistics
------------------------


(Statistics of the local KSQL server interaction with the Kafka topic timestamp-test)
  1. As you can see, records are inserted, format in known, uuid has type string and match the rowkey
  2. select data from the table
ksql> SELECT * FROM TIMESTAMP_TABLE;
1536766002288 | bd7715d5-97e8-43cd-9bf0-feb1df52f8e9 | bd7715d5-97e8-43cd-9bf0-feb1df52f8e9 | 1 | 27567 | 1536765992 | app:used | {STARTTIME=1536765992, ENDTIME=1536765993, DEVICEID=20, METADATA=127.0.0.2}
1536766002288 | d4f34e52-d772-4d90-9e8f-e33f8245d993 | d4f34e52-d772-4d90-9e8f-e33f8245d993 | 1 | 27567 | 1536765993 | app:used | {STARTTIME=1536765993, ENDTIME=1536765994, DEVICEID=20, METADATA=127.0.0.1}
1536766002288 | d58cea43-4548-4570-95a7-4ea641bd3114 | d58cea43-4548-4570-95a7-4ea641bd3114 | 1 | 27567 | 1536765994 | app:used | {STARTTIME=1536765994, ENDTIME=1536765995, DEVICEID=20, METADATA=127.0.0.2}
1536766002288 | 36863ea0-3b4c-43c6-8c0a-21ea0fa8b055 | 36863ea0-3b4c-43c6-8c0a-21ea0fa8b055 | 1 | 27567 | 1536765995 | app:used | {STARTTIME=1536765995, ENDTIME=1536765996, DEVICEID=20, METADATA=127.0.0.1}
1536766002288 | a5385de5-3a10-4833-b0b3-d63bb33f049e | a5385de5-3a10-4833-b0b3-d63bb33f049e | 1 | 27567 | 1536765996 | app:used | {STARTTIME=1536765996, ENDTIME=1536765997, DEVICEID=20, METADATA=127.0.0.2}
1536766002288 | 91830dbc-2b89-47a3-abf5-14c8afb91de5 | 91830dbc-2b89-47a3-abf5-14c8afb91de5 | 1 | 27567 | 1536765997 | app:used | {STARTTIME=1536765997, ENDTIME=1536765998, DEVICEID=20, METADATA=127.0.0.1}
1536766002288 | b01d3322-1b29-4a30-a95c-57539dfc2a04 | b01d3322-1b29-4a30-a95c-57539dfc2a04 | 1 | 27567 | 1536765998 | app:used | {STARTTIME=1536765998, ENDTIME=1536765999, DEVICEID=20, METADATA=127.0.0.2}
1536766002288 | ccf0e1fb-b43f-45e0-be42-28976461e280 | ccf0e1fb-b43f-45e0-be42-28976461e280 | 1 | 27567 | 1536765999 | app:used | {STARTTIME=1536765999, ENDTIME=1536766000, DEVICEID=20, METADATA=127.0.0.1}
1536766002288 | e95f1213-4f7c-4814-8a6e-5b7285b5cc6d | e95f1213-4f7c-4814-8a6e-5b7285b5cc6d | 1 | 27567 | 1536766000 | app:used | {STARTTIME=1536766000, ENDTIME=1536766001, DEVICEID=20, METADATA=127.0.0.2}
1536766002288 | 1009bebe-793a-4417-9fa7-315ec7131469 | 1009bebe-793a-4417-9fa7-315ec7131469 | 1 | 27567 | 1536766001 | app:used | {STARTTIME=1536766001, ENDTIME=1536766002, DEVICEID=20, METADATA=127.0.0.1}
1536766002289 | bd7715d5-97e8-43cd-9bf0-feb1df52f8e9 | bd7715d5-97e8-43cd-9bf0-feb1df52f8e9 | 1 | 27567 | 1536765992 | app:used | {STARTTIME=1536765992, ENDTIME=1536765993, DEVICEID=20, METADATA=data erased}
1536766002289 | d4f34e52-d772-4d90-9e8f-e33f8245d993 | d4f34e52-d772-4d90-9e8f-e33f8245d993 | 1 | 27567 | 1536765993 | app:used | {STARTTIME=1536765993, ENDTIME=1536765994, DEVICEID=20, METADATA=data erased}
1536766002289 | d58cea43-4548-4570-95a7-4ea641bd3114 | d58cea43-4548-4570-95a7-4ea641bd3114 | 1 | 27567 | 1536765994 | app:used | {STARTTIME=1536765994, ENDTIME=1536765995, DEVICEID=20, METADATA=data erased}
1536766002289 | 36863ea0-3b4c-43c6-8c0a-21ea0fa8b055 | 36863ea0-3b4c-43c6-8c0a-21ea0fa8b055 | 1 | 27567 | 1536765995 | app:used | {STARTTIME=1536765995, ENDTIME=1536765996, DEVICEID=20, METADATA=data erased}
1536766002289 | a5385de5-3a10-4833-b0b3-d63bb33f049e | a5385de5-3a10-4833-b0b3-d63bb33f049e | 1 | 27567 | 1536765996 | app:used | {STARTTIME=1536765996, ENDTIME=1536765997, DEVICEID=20, METADATA=data erased}
1536766002289 | 91830dbc-2b89-47a3-abf5-14c8afb91de5 | 91830dbc-2b89-47a3-abf5-14c8afb91de5 | 1 | 27567 | 1536765997 | app:used | {STARTTIME=1536765997, ENDTIME=1536765998, DEVICEID=20, METADATA=data erased}
1536766002289 | b01d3322-1b29-4a30-a95c-57539dfc2a04 | b01d3322-1b29-4a30-a95c-57539dfc2a04 | 1 | 27567 | 1536765998 | app:used | {STARTTIME=1536765998, ENDTIME=1536765999, DEVICEID=20, METADATA=data erased}
1536766002289 | ccf0e1fb-b43f-45e0-be42-28976461e280 | ccf0e1fb-b43f-45e0-be42-28976461e280 | 1 | 27567 | 1536765999 | app:used | {STARTTIME=1536765999, ENDTIME=1536766000, DEVICEID=20, METADATA=data erased}
1536766002289 | e95f1213-4f7c-4814-8a6e-5b7285b5cc6d | e95f1213-4f7c-4814-8a6e-5b7285b5cc6d | 1 | 27567 | 1536766000 | app:used | {STARTTIME=1536766000, ENDTIME=1536766001, DEVICEID=20, METADATA=data erased}
1536766002289 | 1009bebe-793a-4417-9fa7-315ec7131469 | 1009bebe-793a-4417-9fa7-315ec7131469 | 1 | 27567 | 1536766001 | app:used | {STARTTIME=1536766001, ENDTIME=1536766002, DEVICEID=20, METADATA=data erased}

As you can see ids in the list are duplicated.
@ikovalyov
Copy link
Author

ikovalyov commented Sep 13, 2018 •
edited
Loading

My question is if it possible to use ksql tables to get last values based on keys?

@miguno
Copy link
Contributor

miguno commented Sep 14, 2018

This is the currently expected behavior for a SELECT * on a table, as it is a continuous query on the table which will therefore also show subsequent changes to the table (and to keys).

The feature request to add the functionality you described is tracked at #530. Feel free to upvote (+1) that ticket.

@miguno miguno closed this as completed Oct 2, 2018
@apurvam apurvam mentioned this issue Aug 22, 2019
Merged
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
question
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@miguno @ikovalyov