Kafka KSQL: Streaming SQL for Kafka Made Easy

Many Fortune 100 brands such as Twitter, LinkedIn, Airbnb, and several others have been using Apache Kafka for multiple projects and communications. This sudden credibility shift to Kafka sure makes one question the reason for this growth.

Kafka since its inception through LinkedIn in 2010, has been of service for diverse uses than it was originally intended for. It has been popularly used for stream processing by targeted streaming services as well as other brands dealing with copious amounts of data. Ever wondered what streamlines this process of stream processing? It’s Kafka KSQL.

Kafka provides an SQL streaming engine and offers several robust features that are easy to use as well as impactful for stream processing.  In this article, you will read about the applications of Kafka KSQL and its peripheral concepts, commands, and operations. The article concludes with a peek into the limitations of Kafka KSQL.

Table of Contents

• Introduction to Apache Kafka
• Introduction to Kafka KSQL
• What is KSQL Good For?
• Where is KSQL Used?
• KSQL and the Current Data Paradigm
• Why SQL for Kafka?
• Difference between Kafka Streams and KSQL
• KSQL Commands and Operations 
  • CREATE STREAM
  • CREATE TABLE
• Limitations of Kafka KSQL
• Conclusion

Introduction to Apache Kafka

Apache Kafka is a platform that evolved from an adept messaging system with the capabilities to optimize event streaming and processing. It is popularly used for its scalability and fault tolerance mechanisms which are lacking in other transmission technologies. 

What makes Kafka stand out is the mechanism of writing messages to a topic. Topics are considered for allotting every message which can then be derived and retained over a long period of time.

Kafka offers some distinct benefits over standard messaging brokers:

• Kafka enhances the stream efficiency and reduces the buffering time on the user’s end.
• Data security is also optimized, owing to the fact that no actual deletion takes place. Everything can be restored and reprocessed with great ease using Kafka reducing the risk of data loss.
• It allows de-bulking of the load as no indexes are required to be kept for the message.

Due to these performance characteristics and scalability factors, Kafka has become an effective Big Data solution for big companies, such as Netflix, as a primary source of ingestion.

The streaming SQL brings even more functionalities to the table for powerful stream processing.

Introduction to Kafka KSQL

KSQL is an SQL-based streaming engine for Kafka that brings into use the benefits of an SQL platform. It’s an easy-to-use yet powerful technology that lets you define continuous interactive SQL queries against data in Kafka. And, like Kafka, KSQL is elastically scalable, fault-tolerant, distributed, does all the work in real-time. And most importantly, it works for you regardless of what programming language you use in the rest of your stack. It also supports a wide range of powerful stream processing operations including aggregations, joins, windowing, sessionization, and much more. The unique use case of Kafka KSQL can be seen in an easy-to-use platform that is also extremely interactive as an SQL interface for stream processing on Kafka.

Using KSQL you can omit a cumbersome code writing or script running procedure in any language.  It is an open-source and licensed streaming SQL engine that is made available to all for streaming simplification, scalability, elasticity, and fault tolerance.

What is KSQL Good For?

KSQL is the streaming SQL engine for Apache Kafka to write real-time applications in SQL. It enables real-time data processing against Apache Kafka with its easy-to-use, yet powerful interactive SQL interface.

• KSQL provides automated scalability and out-of-the-box functionalities for streaming queries.
• KSQL can be used to join streams of data coming from different sources.
• KSQL supports various streaming operations, such as Data Filtering, Data Transformations, Data Aggregation, Joins, Windowing, and Sessionization.
• KSQL is scalable, elastic, and fault-tolerant.

Where is KSQL Used?

When used in tandem with Kafka connectors, KSQL can effectively enable what would have been batch data integration into practically online data integration.  You can utilize stream table duality to embed metadata into your data streams which can be transmitted and loaded into another system securely. 

To represent a topic in Kafka you can either use a stream or a table in KSQL. This would depend on the use case and your intended purpose for stream processing. For instance, a stream could be used when you want to create a mechanism where values can be read independently. A KSQL table would be better suited for a purpose that demands the reading of frequently evolved and updated data sets. For instance, Twitter uses Kafka KSQL to transmit tables with the latest metadata, and thus, the most updated information.

Let’s look at the common use cases of Apache Kafka KSQL.

• Real-Time Data Processing: Many institutions including Financial Services require data to be processed as soon as it becomes available to block fraudulent transactions immediately. Forecasting Models should also constantly analyze streams of data metrics to predict marketing trends. Kafka KSQL comes in handy here to transmit data.
• Application Activity Tracking: Kafka was rendered by a LinkedIn team, originally for this very use case. Each event that occurs can be sent to the dedicated Kafka topic. KSQL effectively enables online data integration.
• Logging and/or Monitoring System: Kaka KSQL can be used for logging or monitoring as well. It is possible to publish logs into Kafka topics. The logs can be stored in a Kafka cluster for some time. There, they can be aggregated or processed.

KSQL and the Current Data Paradigm

With every organization generating a tremendous amount of data from various sources, it has become a vital process for each business to analyze and transform that data into business-critical information. This will allow businesses to understand their customers better, their needs, their characteristics, and their buying patterns. This, in turn, will help the businesses to come up with personalized marketing processes for enhanced customer experience.

In today’s world, there is a constant environment for monitoring customers’ behavior and adapting to their requirements. This allows businesses to act faster on potential opportunities and avoid any threats as they occur. With Kafka, you can do just that. Kaka stores data and processes it to build numerous applications for a variety of use cases in real-time. In addition to that, KSQL, the streaming SQL engine of Kafka, allows data processing for almost everyone with SQL knowledge. And to do this, you don’t need to write any code in a programming language such as Python or Java.

Why SQL for Kafka?

SQL is most often used for accessing Relational Databases, whereas, Kafka, on the other hand, is quite a different streaming platform. But sometimes you really want to know what’s going on with your data when it’s inside Kafka. And to do that, most of us might reach for the command-line tools that ship with Kafka. But in today’s world, where even a small project typically involves a plethora of technologies, it becomes impractical to know every command-line tool that is available. And, this is where falling back on SQL becomes appealing.

Data flowing in Kafka at the early phase of a Data Pipeline is raw and rich in information. And it can be accessed prior to landing in a Data Warehouse. SQL, being the language involved here, can be used directly by users that aren’t comfortable with Kafka or any other command-line tool.

With SQL, you have a secure yet accessible interface in place between the user and Kafka. The data access is protected through a namespaces-based security model. 

Difference between Kafka Streams and KSQL

KSQL is built on top of Kafka Streams, a super robust world-class horizontally scalable messaging system. Kafka Streams is an easy data processing and transformation library within Kafka that helps developers produce applications to interact with Kafka in an easier way. But this library requires programming experience to operate. To simplify the process, KSQL allows data processing for almost everyone with SQL knowledge, a simpler alternative to Kafka Data Processing.

KSQL is basically used by people that need to quickly fetch and process the data in a simple way. Kafka Streams is preferred for programming applications that require more complexity for processing these data streams.

KSQL Commands and Operations

The two primary KSQL commands consist of creating a STREAM and a TABLE. These are referred to as the core abstractions in KSQL that are necessary for data manipulation in Kafka.

• CREATE STREAM
• CREATE TABLE

CREATE STREAM 

A stream in Kafka records the history and sequence of all the events. In KSQL, you can create streams from Kafka topics, and also create streams of query results from other streams. The “CREATE STREAM” command allows a sequence of data sets to be inserted in the stream. These can be created from a topic or existing tables and streams.

CREATE STREAM pageviews (viewtime BIGINT, userid VARCHAR, pageid VARCHAR) 
WITH (kafka_topic='pageviews', value_format=’JSON’);

You can also use “CREATE STREAM AS SELECT” to create a new stream for the Kafka topic by using the following syntax:

CREATE STREAM stream_name
  [WITH ( property_name = expression [, ...] )]
  AS SELECT  select_expr [, ...]
  FROM from_item
  [ LEFT JOIN join_table ON join_criteria ]
  [ WHERE condition ]
  [PARTITION BY column_name];

“SHOW STREAMS” is used for listing all the existing streams in the KSQL cluster.

ksql> show streams;

CREATE TABLE 

A table in Kafka is an aggregation of the history of world events, and this aggregation keeps on changing in real-time. The TABLE commands can create specified columns and properties including a variety of data types as per your data. Using this can help implement several additional features such as the KEY property for KSQL to navigate through the table.

Use the “CREATE TABLE” command to create a table from a Kafka topic as shown below:

CREATE TABLE users (registertime BIGINT, gender VARCHAR, regionid VARCHAR, userid  VARCHAR) 
WITH (kafka_topic='users', value_format='DELIMITED');

You can also use “CREATE TABLE AS SELECT” to create a new table for the Kafka topic by using the following syntax:

CREATE TABLE table_name
  [WITH ( property_name = expression [, ...] )]
  AS SELECT  select_expr [, ...]
  FROM from_item
  [ WINDOW window_expression ]
  [ WHERE condition ]
  [ GROUP BY grouping_expression ]
  [ HAVING having_expression ];

You can further inspect the table by using the “SHOW TABLES” command.

ksql> SHOW TABLES;

You can go with KSQL Table for performing aggregation queries like average, mean, maximum, minimum, etc on your datasets. And, if you want to have series of events, a dashboard/analysis showing the change, then you can go with KSQL Stream.

Other KSQL CLI commands and operations can be run as per the requirement to specify output formats, timeouts, and other server details. A detailed CLI command script would look something like this:

NAME
        ksql - KSQL CLI
 
SYNOPSIS
        ksql [ --config-file <configFile> ] [ {-h | --help} ]
                [ --output <outputFormat> ]
                [ --query-row-limit <streamedQueryRowLimit> ]
                [ --query-timeout <streamedQueryTimeoutMs> ] [--] <server>
 
OPTIONS
        --config-file <configFile>
            A file specifying configs for Ksql and its underlying Kafka Streams
            instance(s). Refer to KSQL documentation for a list of available
            configs.
 
        -h, --help
            Display help information
 
        --output <outputFormat>
            The output format to use (either 'JSON' or 'TABULAR'; can be changed
            during REPL as well; defaults to TABULAR)
 
        --query-row-limit <streamedQueryRowLimit>
            An optional maximum number of rows to read from streamed queries
 
            This options value must fall in the following range: value >= 1
 
 
        --query-timeout <streamedQueryTimeoutMs>
            An optional time limit (in milliseconds) for streamed queries
 
            This options value must fall in the following range: value >= 1
 
 
        --
            This option can be used to separate command-line options from the
            list of arguments (useful when arguments might be mistaken for
            command-line options)
 
        <server>
            The address of the Ksql server to connect to (ex:
            http://confluent.io:9098)
 
            This option may occur a maximum of 1 times

Limitations of Kafka KSQL

Despite a range of comprehensive features, there are some limitations to the use of Kafka KSQL. While some pretty robust mechanisms for data stream processing can be accessed via KSQL, two vital features are absent which make it stagnant in operational uses.

• Extended periods of downtime cannot be accessed on a real-time system. This could be an inconvenience when some downtime would be expected for standard systems to function.
• Shuffle sorts cannot be implemented with KSQL for your clusters. In KSQL a plan has to be run and introduced before every query and the topic creations cannot be kept a tab on.

It’s for these two main reasons that some of your clients would not want to use Kafka KSQL in their critical paths or in production.

Conclusion 

Using Kafka KSQL requires a clear idea of what kind of data your business is going to implement. This will make the transition from batch to real-time much easier for your business use. While there are some very useful functionalities of KSQL, the architectural limitations must be considered before planning any use. If you understand the architecture and its features well, Kafka KSQL can be a great platform to enhance stream processing for your specific use case.

In this article, you were provided with a detailed guide on Kafka KSQL, an SQL-based streaming engine. You were introduced to the important Kafka KSQL concepts and were shown how to create tables and streams from Kafka topics. You were also taken through some KSQL queries on streams and tables useful for daily operations. The article concluded with a peek into the limitations of Kafka KSQL.

However, If you are looking for a fully automated solution, then definitely try Hevo Data. Hevo Data, with its strong integration with 100+ Data Sources & BI tools such as Kafka (Free Data Source), allows you to not only export data from sources & load data in the destinations, but also transform & enrich your data, & make it analysis-ready so that you can focus only on your key business needs and perform insightful analysis using BI tools.

Visit our Website to Explore Hevo

Explore more about Hevo! Sign Up for a 14-day free trial today. Hevo offers plans & pricing for different use cases and business needs, check them out!

Do you use Kafka? Share your thoughts on Kafka KSQL in the comments below!