10 ms·

KSQL: Open Source Streaming SQL for Apache Kafka

278 points by uptown 9y ago

lima 9y ago
Combine this with Debezium[1] and you get real-time SQL queries on your MySQL/PostgreSQL/MongoDB database! RethinkDB, as far as I understand, does the same thing - their changefeed mechanism would consume the DB log and run the queries against the log. [1]: http://debezium.io http://debezium.io
- nehanarkhede 9y ago
  Yes, exactly. We plan to add support for various Kafka connectors to KSQL so you can ingest/export streams from external systems, like database, and then do stream processing all using KSQL queries. The purpose is to bring Streaming ETL and Stream Processing together to make the user's life easy.
  - edan0 9y ago
    If you want streaming ANSI standard SQL support for both ingest and egress to and from a vast number of systems including (1) open source databases MySQL, PostgreSQL, and MongoDB; (2) enterprise relational databases Oracle, Teradata, Microsoft SQL Server, IBM DB2, Actian Vectorwise, Ingres, and ParAccel; (3) message buses including Kafka, AWS Kinesis, ActiveMQ/AMQP, IBM WebSphere MQ, and Teradata Listener; (4) natural streaming data including HTTP, web sockets, network sockets, and files; and (5) cloud data warehouses including Amazon Redshift and Snowflake, you can download and use SQLstream Blaze (http://www.sqlstream.com/download http://www.sqlstream.com/download) today. You can even JOIN streams from AWS Kinesis against streams in Kafka to combine cloud and on-premise data via a single query.
- edan0 9y ago
  You’re absolutely right. Integration with the whole ecosystem can provide real-time streaming SQL queries against not only open source databases MySQL, PostgreSQL, and MongoDB, but also (1) enterprise relational databases Oracle, Teradata, Microsoft SQL Server, and IBM DB2; (3) message buses including Kafka, AWS Kinesis, ActiveMQ/AMQP, IBM WebSphere MQ, and Teradata Listener; (3) natural streaming data including HTTP, web sockets, network sockets, and files; and (4) cloud data warehouses including Amazon Redshift and Snowflake. Imagine, for example, writing a query that would join a Kafka stream against a Kinesis stream and enrich it with a database lookup. You'll find all of that today with SQLstream Blaze.
cbsmith 9y ago
This is the Kafka answer to Pipelinedb. I love it.
- cmollis 9y ago
  PipelineDB is great..and the guys are super helpful.. influxdb has something similar, and now so does AWS Kinesis (with their Kinesis Analytics product).
  - Fergi 9y ago
    PipelineDB will become a standard, open-source PostgreSQL extension this year, enabling anybody using PostgreSQL to leverage continuous SQL queries in combination with all other PostgreSQL functionality. https://www.pipelinedb.com/blog/pipelinedb-0-9-8-postgresql-extension-refactor-nearing-completion https://www.pipelinedb.com/blog/pipelinedb-0-9-8-postgresql-...
    - _pmf_ 9y ago
      > PipelineDB will become a standard, open-source PostgreSQL extension What does this imply?
  - cbsmith 9y ago
    Are InfluxDB's continuous queries really like this? It's so much less of a streaming solution I hadn't realized it did this.
  - stuartaxelowen 9y ago
    Doesn't PipelinDB limit you to postgres' scaling capabilities?
    - Fergi 9y ago
      Not for streaming analytic workloads, because PipelineDB fundamentally adds continuous queries to PostgreSQL, so data is continuously distilled and aggregated as it arrives, before it is stored, which drastically reduces the amount of data stored in PipelineDB (or soon, in PostgreSQL via the extension refactor). PipelineDB also offers a clustering extension for large workloads (see: http://enterprise.pipelinedb.com/docs/ http://enterprise.pipelinedb.com/docs/) But in terms of ad hoc, exploratory analytics workloads, yes - the scaling limitations would be the same, since for ad hoc, exploratory analytics PipelineDB and PostgreSQL are the same. But with that said, the processed, aggregated data that gets stored is generally much smaller than large volumes of granular data, so there is much less data to comb through with PipelineDB.
CSDude 9y ago
Apache Flink is also a good alternative, and works very well. We have used it in production for a while for generating live reports. I made simple example [1] and have a look at the docs if you are more interested [2]. Gonna definetely try Kafka's version, its version of stream processing [3] also interesting as well. [1] https://medium.com/@mustafaakin/flink-streaming-sql-example-6076c1bc91c1 https://medium.com/@mustafaakin/flink-streaming-sql-example-... [2] https://ci.apache.org/projects/flink/flink-docs-release-1.3/dev/table/sql.html https://ci.apache.org/projects/flink/flink-docs-release-1.3/... [3] http://docs.confluent.io/current/streams/index.html http://docs.confluent.io/current/streams/index.html
- nehanarkhede 9y ago
  I'm one of the authors of Kafka. I've outlined some differences between Flink's support for streaming SQL and KSQL in this Twitter thread - https://twitter.com/juliusvolz/status/902283513382051840 https://twitter.com/juliusvolz/status/902283513382051840 Here's a summary: - KSQL has a completely Interactive SQL interface, so you don't have to switch between DSL code and SQL. - KSQL upports local, distributed and embedded modes. Is tightly integrated with Kafka's Streams API and Kafka itself; doesn't reinvent the wheel. So is simple to use and deploy. - KSQL doesn't have external dependencies, for orchestration, deployment etc. - KSQL has native support for Kafka's exactly once processing semantics, supports and stream-table joins.
  - sandGorgon 9y ago
    @neha - where do you think kafka is going to evolve in the world of data processing. I'm very bullish on kafka. Today we have Spark for batch data computation and have already switched some of our streaming stuff to Kafka. Do you see yourselves entering into the batch processing space anytime ? Google has officially said that Flink is "compelling" because of its compatibility with the Beam model. If I can step on thin ice... is it easier for Flink to commandeer Kafka or for Kafka to win over batch processing ?
    - blain_the_train 9y ago
filereaper 9y ago
I really wish frameworks offering SQL would upfront say what level of SQL compatibility they have, is it SQL 2011, is it Postgres etc... Anyways, if anyone's wondering, here's the Github page. [1] Also from FAQ [2]: Is KSQL fully compliant to ANSI SQL? KSQL is a dialect inspired by ANSI SQL. It has some differences because it is geared at processing streaming data. For example, ANSI SQL has no notion of “windowing” for use cases such as performing aggregations on data grouped into 5-minute windows, which is a commonly required functionality in the streaming world. [1] https://github.com/confluentinc/ksql/blob/0.1.x/docs/syntax-reference.md#syntax-reference https://github.com/confluentinc/ksql/blob/0.1.x/docs/syntax-... [2] https://github.com/confluentinc/ksql/blob/0.1.x/docs/faq.md#frequently-asked-questions https://github.com/confluentinc/ksql/blob/0.1.x/docs/faq.md#...
- sixdimensional 9y ago
  ANSI SQL should be extended to standardize streaming syntax. There are certainly enough vendors that have it now... for example, sqlstream.com. It's too bad that SQL:2011 didn't get these features since it had a lot of temporal-related extensions.
- joaodlf 9y ago
  I'm assuming that most "SQL" implementations in distributed systems don't actually follow the SQL standard. They just offer a SQL like syntax to solve common problems. I could be wrong, but that's the impression I get working with tools like Cassandra/Spark.
- edan0 9y ago
  Confluent doesn't understand the ANSI SQL standard. Support for aggregating over windows with standard syntax goes back to SQL:99, so it's been around for 18 years. Any company playing in the SQL space should make a point of understanding the standard. There is a good explanation of ANSI SQL standard windowing at http://sqlstream.com/docs/conc_applicationdesign.html?zoom_highlightsub=rolling%2Bwindow%2Baggregation http://sqlstream.com/docs/conc_applicationdesign.html?zoom_h.... The document describes both tumbling and rolling windows as well as how they are achieved with standard syntax. SQLstream could not agree with you more that an up-front explanation of SQL compatibility level is necessary to evaluate a product. SQLstream Blaze is SQL:2011 compliant.
daddykotex 9y ago
I've had the question for a while so I'll ask it here, maybe someone can help me. Suppose you modeled your domain with events and your stack is build on top of it. As stuff happens in your application, events are generated and appended to the stream. The stream is consumed by any number of consumers and awesome stuff is produced with it. The stream is persisted and you have all events starting from day 1. Over time, things have changed and you have evolved your events to include some fields and deprecate others. You could do this without any downtime whatsoever by changing your events in a way that is backward compatible way. What is the good approach to what I'd call a `replay`? When you want to replay all events, the version of your apps that will consume the events may not know about the fields that were in the event for day one.
- zjaffee 9y ago
  The best way I've seen it done, is to version all of your schemas and have a database that signals all the transformations needed to be done for any given schema version. That way, when your reading a particular event, you can query for all the operations needed to be done on such event and perform them.
- daddykotex 9y ago
  I'm also wondering: how one deal with changes to events when using KSQL?
  - nehanarkhede 9y ago
    Mind clarifying what you mean by changes to events? If you create a STREAM or TABLE using KSQL, it makes sure that they are kept updated with every single event that arrives on the source Kafka topics. That's what you'd expect in a true event-at-a-time Streaming SQL engine, which is what KSQL is.
    - daddykotex 9y ago
      Suppose you build a STREAM or TABLE from a topic and assume a field in the event is `id`. Later on, you introduce an update to this event where where your replace `id` by `user_id`, how is KSQL reacting?
coolio222 9y ago
Does anyone knows details about the KSQL-engine that computes the queries? According to their git, there can be multiple KSQL-engines in a Client-Server configuration. Is the workload for one query distributed? Is the SQL translated into a program using the Kafka streaming API?
- hojjat 9y ago
  Yes, queries are translated into Kafka Streams API. In the client-server(cluster) mode each query will run on every instance of engine the same way kafka streams apps run on multiple instances.
andmarios 9y ago
At Landoop we submitted our proposal to present our KSQL at the Kafka summit but we were rejected. Ah, if we only knew... :)
- rollulus 9y ago
  Hmm, that's "interesting". Can you elaborate a bit on how yours and theirs differ, if any?
  - stefansb 9y ago
    There is a difference of course. While we are still learning about their implementation I think the statements below are true 1. We don't support just stream. You can throw a SQL at a kafka topic as easy as SELECT * FROM `topic` [WHERE ] 2. We support selecting or filter on the record metadata: offset/timestamp/partition (Haven't seen something similar in Confluent KSQL) 3. We integrate with Schema Registry for Avro. We hope to support Hortonworks schema registry soon as well as protobuf. 4. We allow for injecting fields in the Kafka Key part. For example: SELECT _offset as `_key.offset`, field2 * field3 - abs(field4.field5) as total FROM `magic-topic` 5. Just quickly looking at the Confluent KSQL "abs" function i see it accepts Double only. It doublt that everything is converted to Double before it hits the method and then converted back. (too short of a time to understand the whole implementation). 6. Filters: is related to point 2. We allow filter on message metadata. For example: SELECT * FROM topicA WHERE (a.d.e + b) /c = 100 and _offset > 2000 and partition in (2,6,9) Also not sure if they have customers yet using it. We do.
  - nehanarkhede 9y ago
    There's a pretty big difference to a point that Landoop's KCQL (Kafka Connect Query Language) and Confluent's KSQL (Streaming SQL for Apache Kafka) are two different products. - KSQL is a full-fledged Streaming SQL engine for all kinds of stream processing operations from windowed aggregations, stream-table joins, sessionization and much more. So it does more powerful stream processing on Kafka than what Landoop's product supports which is simple projections and filters. - KSQL can do that because it supports streams and tables as first-class constructs and tightly integrates with Kafka's Streams API and the Kafka log itself. We are not aware of any other products that do that today, including Landoop's tool. - We will add support for Kafka connectors so you can stream data from different systems into Kafka through KSQL. This will cover what Landoop intended with KCQL (Kafka Connect Query Language. - Confluent works with several very large enterprises and many of the companies that have adopted Kafka. We worked with those customers to learn what would solve real business problems and used that feedback to build KSQL. So it . models on real-world customer feedback. - We'd love to hear feedback. Here's the repository https://github.com/confluentinc/ksql https://github.com/confluentinc/ksql and here's the Slack Channel slackpass.io/confluentcommunity - #ksql Hope that helps!
beagle3 9y ago
ksql was the sql dialect understood by Kx systems k2 and k3 versions. It includes nice things like foreign key chasing.
jasonrhaas 9y ago
Seems like a somewhat similar idea to AWS Aurora.
- chicagobuss 9y ago
  Hmm. I don't see that at all. Aurora is a way to query data in S3. This is a way to query Kafka streams in real time.
  - elvinyung 9y ago
    I think you mean Athena :P This seems to be basically similar to Spark, which lets you perform full SQL queries on Kafka streams.
    - nehanarkhede 9y ago
      Spark SQL is different from KSQL in the following ways: - Spark SQL is not an interactive Streaming SQL interface. To do stream processing, you have to switch between writing code using Java/Scala/Python and SQL statements. KSQL, on the other hand, is a completely interactive Streaming SQL engine. You can do sophisticated stream processing operations interactively using SQL statements alone. - KSQL is a true event-at-a-time Streaming SQL engine. Spark SQL is micro-batch.
      - rxin 9y ago
        What you said about Spark was not true. One can totally interactively query a Kafka stream using Spark, in SQL.
BMarkmann 9y ago
I understand the underlying architecture is fundamentally different, but the end result seems kind of akin to some of the "complex event processing" (CEP) tools out there like Esper (ie. feeding incoming data into established queries instead of executing queries against at-rest data). Would this fit similar sorts of use cases / fit into the CEP market?
- nehanarkhede 9y ago
  Yes KSQL is ideal for use cases similar to those that CEP was initially targeted for and more - from real-time anomaly detection, monitoring, analytics to application development and Streaming ETL. As you alluded to, the big difference is that KSQL is designed as a distributed Streaming SQL engine that can run at Kafka scale.
  - edan0 9y ago
    If you are interested in performing ultra low latency (<1ms) CEP via SQL, take a close look at SQLstream Blaze (http://www.sqlstream.com http://www.sqlstream.com) and it's full implementation of Allen's Interval Algebra (https://en.wikipedia.org/wiki/Allen%27s_interval_algebra https://en.wikipedia.org/wiki/Allen%27s_interval_algebra) via SQL temporal predicates: http://sqlstream.com/docs/sqlrf_planned_feature_temporal_predi.html?zoom_highlightsub=leads http://sqlstream.com/docs/sqlrf_planned_feature_temporal_pre.... This stuff runs at 1,000,000 events per second per core and can scale out in conjunction with Kafka across any number of servers.
guidoism 9y ago
That name is already taken by the San Carlos airport near Oracle: https://en.m.wikipedia.org/wiki/San_Carlos_Airport_(California) https://en.m.wikipedia.org/wiki/San_Carlos_Airport_(Californ...
- jmccarthy 9y ago
  Isn't it great that the name is entirely coincidental? Love that anecdote.
jnordwick 9y ago
This seems like a painfully slow and complex way to do event processing.
- twic 9y ago
  What would be faster and less complex ways? Can you think of any advantages that KSQL has over them?
largote 9y ago
Fun fact: KSQL is the code of the airport closest to Oracle HQ (San Carlos airport).
- iaabtpbtpnn 9y ago
  Came here to post this... do you know if the code is meant to pay homage to Oracle, or were they just trying to make it sound like San QarLos? Edit: Oh, the wiki page explains that it predates Oracle. So probably the latter.
buremba 9y ago
I love the continuous queries over append-only datasets and it's great that Kafka added support for it. It looks like there is one main contributor (https://github.com/confluentinc/ksql/graphs/contributors https://github.com/confluentinc/ksql/graphs/contributors) though, it seems that the other contributors either wrote the documentation or helped for the packaging. Not a great sign considering how big this project is (there are competitors which only do this such Pipelinedb), hopefully you can create a team just for KSQL.
- nehanarkhede 9y ago
  Agreed. We recently had a few more members added to the team and are looking to grow it even more. Also note that KSQL is built on Kafka Streams and we have a team that works just on that as well.
- miguno 9y ago
  To add to what nehanarkhede said: I'd rather see it as a testament to the powerful building blocks of Kafka and its Streams API that you can actually implement a project such as KSQL with very few engineers. Also, the commits in KSQL reflect only parts of the work -- it doesn't include design discussions, code reviews, etc. Lastly, keep in mind that the git repository was cleaned (think: squashed commits) prior to publication, see the very first commit in the repository's timeline. So you don't see the prior work/commits of other Confluent engineers that went into KSQL.
stuartaxelowen 9y ago
The many streaming SQL options are cool - but how do you test streaming SQL as it comes to represent a larger part of your application?
chandanrai 9y ago
this link is broken in the article [step through the demo yourself](https://github.com/confluentinc/ksql/blob/0.1.x/docs/demo.md#demo https://github.com/confluentinc/ksql/blob/0.1.x/docs/demo.md...)
tycho01 9y ago
Now I'm wondering if something like Mondrian could help stream MDX queries over this...

278 points by uptown 9y ago

lima 9y ago

Combine this with Debezium[1] and you get real-time SQL queries on your MySQL/PostgreSQL/MongoDB database! RethinkDB, as far as I understand, does the same thing - their changefeed mechanism would consume the DB log and run the queries against the log. [1]: http://debezium.io http://debezium.io

nehanarkhede 9y ago
Yes, exactly. We plan to add support for various Kafka connectors to KSQL so you can ingest/export streams from external systems, like database, and then do stream processing all using KSQL queries. The purpose is to bring Streaming ETL and Stream Processing together to make the user's life easy.
- edan0 9y ago
  If you want streaming ANSI standard SQL support for both ingest and egress to and from a vast number of systems including (1) open source databases MySQL, PostgreSQL, and MongoDB; (2) enterprise relational databases Oracle, Teradata, Microsoft SQL Server, IBM DB2, Actian Vectorwise, Ingres, and ParAccel; (3) message buses including Kafka, AWS Kinesis, ActiveMQ/AMQP, IBM WebSphere MQ, and Teradata Listener; (4) natural streaming data including HTTP, web sockets, network sockets, and files; and (5) cloud data warehouses including Amazon Redshift and Snowflake, you can download and use SQLstream Blaze (http://www.sqlstream.com/download http://www.sqlstream.com/download) today. You can even JOIN streams from AWS Kinesis against streams in Kafka to combine cloud and on-premise data via a single query.
edan0 9y ago
You’re absolutely right. Integration with the whole ecosystem can provide real-time streaming SQL queries against not only open source databases MySQL, PostgreSQL, and MongoDB, but also (1) enterprise relational databases Oracle, Teradata, Microsoft SQL Server, and IBM DB2; (3) message buses including Kafka, AWS Kinesis, ActiveMQ/AMQP, IBM WebSphere MQ, and Teradata Listener; (3) natural streaming data including HTTP, web sockets, network sockets, and files; and (4) cloud data warehouses including Amazon Redshift and Snowflake. Imagine, for example, writing a query that would join a Kafka stream against a Kinesis stream and enrich it with a database lookup. You'll find all of that today with SQLstream Blaze.

cbsmith 9y ago

This is the Kafka answer to Pipelinedb. I love it.

cmollis 9y ago
PipelineDB is great..and the guys are super helpful.. influxdb has something similar, and now so does AWS Kinesis (with their Kinesis Analytics product).
- Fergi 9y ago
  PipelineDB will become a standard, open-source PostgreSQL extension this year, enabling anybody using PostgreSQL to leverage continuous SQL queries in combination with all other PostgreSQL functionality. https://www.pipelinedb.com/blog/pipelinedb-0-9-8-postgresql-extension-refactor-nearing-completion https://www.pipelinedb.com/blog/pipelinedb-0-9-8-postgresql-...
  - _pmf_ 9y ago
    > PipelineDB will become a standard, open-source PostgreSQL extension What does this imply?
- cbsmith 9y ago
  Are InfluxDB's continuous queries really like this? It's so much less of a streaming solution I hadn't realized it did this.
- stuartaxelowen 9y ago
  Doesn't PipelinDB limit you to postgres' scaling capabilities?
  - Fergi 9y ago
    Not for streaming analytic workloads, because PipelineDB fundamentally adds continuous queries to PostgreSQL, so data is continuously distilled and aggregated as it arrives, before it is stored, which drastically reduces the amount of data stored in PipelineDB (or soon, in PostgreSQL via the extension refactor). PipelineDB also offers a clustering extension for large workloads (see: http://enterprise.pipelinedb.com/docs/ http://enterprise.pipelinedb.com/docs/) But in terms of ad hoc, exploratory analytics workloads, yes - the scaling limitations would be the same, since for ad hoc, exploratory analytics PipelineDB and PostgreSQL are the same. But with that said, the processed, aggregated data that gets stored is generally much smaller than large volumes of granular data, so there is much less data to comb through with PipelineDB.

CSDude 9y ago

Apache Flink is also a good alternative, and works very well. We have used it in production for a while for generating live reports. I made simple example [1] and have a look at the docs if you are more interested [2]. Gonna definetely try Kafka's version, its version of stream processing [3] also interesting as well. [1] https://medium.com/@mustafaakin/flink-streaming-sql-example-6076c1bc91c1 https://medium.com/@mustafaakin/flink-streaming-sql-example-... [2] https://ci.apache.org/projects/flink/flink-docs-release-1.3/dev/table/sql.html https://ci.apache.org/projects/flink/flink-docs-release-1.3/... [3] http://docs.confluent.io/current/streams/index.html http://docs.confluent.io/current/streams/index.html

nehanarkhede 9y ago
I'm one of the authors of Kafka. I've outlined some differences between Flink's support for streaming SQL and KSQL in this Twitter thread - https://twitter.com/juliusvolz/status/902283513382051840 https://twitter.com/juliusvolz/status/902283513382051840 Here's a summary: - KSQL has a completely Interactive SQL interface, so you don't have to switch between DSL code and SQL. - KSQL upports local, distributed and embedded modes. Is tightly integrated with Kafka's Streams API and Kafka itself; doesn't reinvent the wheel. So is simple to use and deploy. - KSQL doesn't have external dependencies, for orchestration, deployment etc. - KSQL has native support for Kafka's exactly once processing semantics, supports and stream-table joins.
- sandGorgon 9y ago
  @neha - where do you think kafka is going to evolve in the world of data processing. I'm very bullish on kafka. Today we have Spark for batch data computation and have already switched some of our streaming stuff to Kafka. Do you see yourselves entering into the batch processing space anytime ? Google has officially said that Flink is "compelling" because of its compatibility with the Beam model. If I can step on thin ice... is it easier for Flink to commandeer Kafka or for Kafka to win over batch processing ?
  - blain_the_train 9y ago

filereaper 9y ago

I really wish frameworks offering SQL would upfront say what level of SQL compatibility they have, is it SQL 2011, is it Postgres etc... Anyways, if anyone's wondering, here's the Github page. [1] Also from FAQ [2]: Is KSQL fully compliant to ANSI SQL? KSQL is a dialect inspired by ANSI SQL. It has some differences because it is geared at processing streaming data. For example, ANSI SQL has no notion of “windowing” for use cases such as performing aggregations on data grouped into 5-minute windows, which is a commonly required functionality in the streaming world. [1] https://github.com/confluentinc/ksql/blob/0.1.x/docs/syntax-reference.md#syntax-reference https://github.com/confluentinc/ksql/blob/0.1.x/docs/syntax-... [2] https://github.com/confluentinc/ksql/blob/0.1.x/docs/faq.md#frequently-asked-questions https://github.com/confluentinc/ksql/blob/0.1.x/docs/faq.md#...

sixdimensional 9y ago
ANSI SQL should be extended to standardize streaming syntax. There are certainly enough vendors that have it now... for example, sqlstream.com. It's too bad that SQL:2011 didn't get these features since it had a lot of temporal-related extensions.
joaodlf 9y ago
I'm assuming that most "SQL" implementations in distributed systems don't actually follow the SQL standard. They just offer a SQL like syntax to solve common problems. I could be wrong, but that's the impression I get working with tools like Cassandra/Spark.
edan0 9y ago
Confluent doesn't understand the ANSI SQL standard. Support for aggregating over windows with standard syntax goes back to SQL:99, so it's been around for 18 years. Any company playing in the SQL space should make a point of understanding the standard. There is a good explanation of ANSI SQL standard windowing at http://sqlstream.com/docs/conc_applicationdesign.html?zoom_highlightsub=rolling%2Bwindow%2Baggregation http://sqlstream.com/docs/conc_applicationdesign.html?zoom_h.... The document describes both tumbling and rolling windows as well as how they are achieved with standard syntax. SQLstream could not agree with you more that an up-front explanation of SQL compatibility level is necessary to evaluate a product. SQLstream Blaze is SQL:2011 compliant.

daddykotex 9y ago

I've had the question for a while so I'll ask it here, maybe someone can help me. Suppose you modeled your domain with events and your stack is build on top of it. As stuff happens in your application, events are generated and appended to the stream. The stream is consumed by any number of consumers and awesome stuff is produced with it. The stream is persisted and you have all events starting from day 1. Over time, things have changed and you have evolved your events to include some fields and deprecate others. You could do this without any downtime whatsoever by changing your events in a way that is backward compatible way. What is the good approach to what I'd call a `replay`? When you want to replay all events, the version of your apps that will consume the events may not know about the fields that were in the event for day one.

zjaffee 9y ago
The best way I've seen it done, is to version all of your schemas and have a database that signals all the transformations needed to be done for any given schema version. That way, when your reading a particular event, you can query for all the operations needed to be done on such event and perform them.
daddykotex 9y ago
I'm also wondering: how one deal with changes to events when using KSQL?
- nehanarkhede 9y ago
  Mind clarifying what you mean by changes to events? If you create a STREAM or TABLE using KSQL, it makes sure that they are kept updated with every single event that arrives on the source Kafka topics. That's what you'd expect in a true event-at-a-time Streaming SQL engine, which is what KSQL is.
  - daddykotex 9y ago
    Suppose you build a STREAM or TABLE from a topic and assume a field in the event is `id`. Later on, you introduce an update to this event where where your replace `id` by `user_id`, how is KSQL reacting?

coolio222 9y ago

Does anyone knows details about the KSQL-engine that computes the queries? According to their git, there can be multiple KSQL-engines in a Client-Server configuration. Is the workload for one query distributed? Is the SQL translated into a program using the Kafka streaming API?

hojjat 9y ago
Yes, queries are translated into Kafka Streams API. In the client-server(cluster) mode each query will run on every instance of engine the same way kafka streams apps run on multiple instances.

andmarios 9y ago

At Landoop we submitted our proposal to present our KSQL at the Kafka summit but we were rejected. Ah, if we only knew... :)

rollulus 9y ago
Hmm, that's "interesting". Can you elaborate a bit on how yours and theirs differ, if any?
- stefansb 9y ago
  There is a difference of course. While we are still learning about their implementation I think the statements below are true 1. We don't support just stream. You can throw a SQL at a kafka topic as easy as SELECT * FROM `topic` [WHERE ] 2. We support selecting or filter on the record metadata: offset/timestamp/partition (Haven't seen something similar in Confluent KSQL) 3. We integrate with Schema Registry for Avro. We hope to support Hortonworks schema registry soon as well as protobuf. 4. We allow for injecting fields in the Kafka Key part. For example: SELECT _offset as `_key.offset`, field2 * field3 - abs(field4.field5) as total FROM `magic-topic` 5. Just quickly looking at the Confluent KSQL "abs" function i see it accepts Double only. It doublt that everything is converted to Double before it hits the method and then converted back. (too short of a time to understand the whole implementation). 6. Filters: is related to point 2. We allow filter on message metadata. For example: SELECT * FROM topicA WHERE (a.d.e + b) /c = 100 and _offset > 2000 and partition in (2,6,9) Also not sure if they have customers yet using it. We do.
- nehanarkhede 9y ago
  There's a pretty big difference to a point that Landoop's KCQL (Kafka Connect Query Language) and Confluent's KSQL (Streaming SQL for Apache Kafka) are two different products. - KSQL is a full-fledged Streaming SQL engine for all kinds of stream processing operations from windowed aggregations, stream-table joins, sessionization and much more. So it does more powerful stream processing on Kafka than what Landoop's product supports which is simple projections and filters. - KSQL can do that because it supports streams and tables as first-class constructs and tightly integrates with Kafka's Streams API and the Kafka log itself. We are not aware of any other products that do that today, including Landoop's tool. - We will add support for Kafka connectors so you can stream data from different systems into Kafka through KSQL. This will cover what Landoop intended with KCQL (Kafka Connect Query Language. - Confluent works with several very large enterprises and many of the companies that have adopted Kafka. We worked with those customers to learn what would solve real business problems and used that feedback to build KSQL. So it . models on real-world customer feedback. - We'd love to hear feedback. Here's the repository https://github.com/confluentinc/ksql https://github.com/confluentinc/ksql and here's the Slack Channel slackpass.io/confluentcommunity - #ksql Hope that helps!

beagle3 9y ago

ksql was the sql dialect understood by Kx systems k2 and k3 versions. It includes nice things like foreign key chasing.

jasonrhaas 9y ago

Seems like a somewhat similar idea to AWS Aurora.

chicagobuss 9y ago
Hmm. I don't see that at all. Aurora is a way to query data in S3. This is a way to query Kafka streams in real time.
- elvinyung 9y ago
  I think you mean Athena :P This seems to be basically similar to Spark, which lets you perform full SQL queries on Kafka streams.
  - nehanarkhede 9y ago
    Spark SQL is different from KSQL in the following ways: - Spark SQL is not an interactive Streaming SQL interface. To do stream processing, you have to switch between writing code using Java/Scala/Python and SQL statements. KSQL, on the other hand, is a completely interactive Streaming SQL engine. You can do sophisticated stream processing operations interactively using SQL statements alone. - KSQL is a true event-at-a-time Streaming SQL engine. Spark SQL is micro-batch.
    - rxin 9y ago
      What you said about Spark was not true. One can totally interactively query a Kafka stream using Spark, in SQL.

BMarkmann 9y ago

I understand the underlying architecture is fundamentally different, but the end result seems kind of akin to some of the "complex event processing" (CEP) tools out there like Esper (ie. feeding incoming data into established queries instead of executing queries against at-rest data). Would this fit similar sorts of use cases / fit into the CEP market?

nehanarkhede 9y ago
Yes KSQL is ideal for use cases similar to those that CEP was initially targeted for and more - from real-time anomaly detection, monitoring, analytics to application development and Streaming ETL. As you alluded to, the big difference is that KSQL is designed as a distributed Streaming SQL engine that can run at Kafka scale.
- edan0 9y ago
  If you are interested in performing ultra low latency (<1ms) CEP via SQL, take a close look at SQLstream Blaze (http://www.sqlstream.com http://www.sqlstream.com) and it's full implementation of Allen's Interval Algebra (https://en.wikipedia.org/wiki/Allen%27s_interval_algebra https://en.wikipedia.org/wiki/Allen%27s_interval_algebra) via SQL temporal predicates: http://sqlstream.com/docs/sqlrf_planned_feature_temporal_predi.html?zoom_highlightsub=leads http://sqlstream.com/docs/sqlrf_planned_feature_temporal_pre.... This stuff runs at 1,000,000 events per second per core and can scale out in conjunction with Kafka across any number of servers.

guidoism 9y ago

That name is already taken by the San Carlos airport near Oracle: https://en.m.wikipedia.org/wiki/San_Carlos_Airport_(California) https://en.m.wikipedia.org/wiki/San_Carlos_Airport_(Californ...

jmccarthy 9y ago
Isn't it great that the name is entirely coincidental? Love that anecdote.

jnordwick 9y ago

This seems like a painfully slow and complex way to do event processing.

twic 9y ago
What would be faster and less complex ways? Can you think of any advantages that KSQL has over them?

largote 9y ago

Fun fact: KSQL is the code of the airport closest to Oracle HQ (San Carlos airport).

iaabtpbtpnn 9y ago
Came here to post this... do you know if the code is meant to pay homage to Oracle, or were they just trying to make it sound like San QarLos? Edit: Oh, the wiki page explains that it predates Oracle. So probably the latter.

buremba 9y ago

I love the continuous queries over append-only datasets and it's great that Kafka added support for it. It looks like there is one main contributor (https://github.com/confluentinc/ksql/graphs/contributors https://github.com/confluentinc/ksql/graphs/contributors) though, it seems that the other contributors either wrote the documentation or helped for the packaging. Not a great sign considering how big this project is (there are competitors which only do this such Pipelinedb), hopefully you can create a team just for KSQL.

nehanarkhede 9y ago
Agreed. We recently had a few more members added to the team and are looking to grow it even more. Also note that KSQL is built on Kafka Streams and we have a team that works just on that as well.
miguno 9y ago
To add to what nehanarkhede said: I'd rather see it as a testament to the powerful building blocks of Kafka and its Streams API that you can actually implement a project such as KSQL with very few engineers. Also, the commits in KSQL reflect only parts of the work -- it doesn't include design discussions, code reviews, etc. Lastly, keep in mind that the git repository was cleaned (think: squashed commits) prior to publication, see the very first commit in the repository's timeline. So you don't see the prior work/commits of other Confluent engineers that went into KSQL.

stuartaxelowen 9y ago

The many streaming SQL options are cool - but how do you test streaming SQL as it comes to represent a larger part of your application?

chandanrai 9y ago

this link is broken in the article [step through the demo yourself](https://github.com/confluentinc/ksql/blob/0.1.x/docs/demo.md#demo https://github.com/confluentinc/ksql/blob/0.1.x/docs/demo.md...)

tycho01 9y ago

Now I'm wondering if something like Mondrian could help stream MDX queries over this...