Open codestoned1 opened 5 years ago
codestoned1
commented
5 years ago I believe the main problem here is assigning a key to the incoming messages to the clickthrough_rate_per_page table. I don't see a way to assign the key I want to each message (I only need the key to contain the page_name and window start time, which should be stored in the rowtime).
apurvam
commented
5 years ago To be clear, when you do SELECT *, cast(o.count as double) / cast(v.count as double) FROM user_clicks_per_10_seconds o LEFT JOIN user_views_per_10_seconds v ON o.rowkey=v.rowkey you get results?
apurvam
commented
5 years ago I think the underlying problem is that the messages which are output from the windowed aggregates have binary keys, which can't be used downstream. See https://docs.confluent.io/current/ksql/docs/syntax-reference.html#key-requirements for details.
I am not sure how the plain SELECT * works.
codestoned1
commented
5 years ago @apurvam yep I get results when just using the select.
codestoned1
commented
5 years ago That seems like odd, kind of buggy behavior to reject a key that is created from KSQL statements through a supported feature (table-table joins). Also, I should point out that when running describe extended clickthrough_rate_per_page it shows that there is actually no key field for the table, which leads me to believe it's not being assigned a key at all due to the join?
apurvam
commented
5 years ago @zlex7 the lack of key for the output of the join is a bug and is being fixed by #1697
However, I really don't think you are hitting that here, though you could try building from that branch and seeing if it fixes your problem.
rodesai
commented
5 years ago This is a different bug. We're not setting the serde right for the output when the tables in the join are windowed. Its being set to StringSerde, but the key is of type Windowed.
apurvam
commented
5 years ago That makes sense @rodesai . The reason why the select * worked is because we are not serializing the output to a topic. Marking this as a bug.
big-andy-coates
commented
4 years ago related to https://github.com/confluentinc/ksql/issues/4396?
Hi guys, I'm running into quite an irritating problem. I would be really grateful if you could help me. I am attempting to join two windowed tables (which both have rowkeys) in one table to calculate the clickthrough rate for certain pages. When I run the join as a simple select it succeeds and displays the data. However, when I try to persist this data by using
create table table_name as select...and attempt to select from the created table, it simply hangs and returns no data. I believe this is because no key is being set for the created table. Here are the queries I ran, in order.CREATE STREAM event_and_page_name_where_outclicked AS SELECT data->context['pageName'] AS page_name FROM data_stream WHERE data->event['eventName']='outclick';CREATE STREAM event_and_page_name_where_viewed AS SELECT data->context['pageName'] AS page_name FROM data_stream WHERE data->event['eventName']='page';CREATE TABLE user_clicks_per_10_seconds WITH (KAFKA_TOPIC='user_clicks_per_10_seconds',VALUE_FORMAT='avro') AS SELECT page_name, count(*) AS count FROM event_and_page_name_where_outclicked WINDOW TUMBLING (size 10 seconds) GROUP BY page_name;CREATE TABLE user_views_per_10_seconds WITH (KAFKA_TOPIC='user_views_per_10_seconds',VALUE_FORMAT='json') AS SELECT page_name, count(*) AS count FROM event_and_page_name_where_viewed WINDOW TUMBLING (size 10 seconds) GROUP BY page_name;CREATE TABLE clickthrough_rate_per_page AS SELECT *, cast(o.count as double) / cast(v.count as double) FROM user_clicks_per_10_seconds o LEFT JOIN user_views_per_10_seconds v ON o.rowkey=v.rowkeyIn addition, I do not see a way to use group by here as it requires an aggregation, and I simply want to compute the number of outclicks over the number of views over a specific period of time. Any ideas what could be wrong here? I can post more information if need be.
Logs from KSQL: These errors are repeated many times.