Use the ksqlDB testing tool to test a set of SQL statements. The ksqlDB testing tool is a command line utility that enables testing SQL statements without requiring any infrastructure, like Apache Kafka® and ksqlDB clusters. The ksqlDB testing tool is a great way to design your ksqlDB pipeline and ensure the expected results are generated. You can collaborate on designing your SQL statements by sharing the test files.
To test a set of SQL statements, you provide three files, one file containing the SQL statements and two JSON files containing the input records and the expected output records.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
Test File Structure¶
The statements file contains the SQL statements to test. The following are the supported statements in the testing tool:
- CREATE STREAM
- CREATE TABLE
- CREATE STREAM AS SELECT
- CREATE TABLE AS SELECT
- INSERT INTO
Here is a sample statements file for the testing tool:
The input file is a JSON file with one array field named
Each element in the array is the representation of input messages.
The input messages array can't be empty. A message should have a topic, a key, a value, and a timestamp. The following is a sample input file for the previous test:
1 2 3 4 5 6 7 8 9
Generating input files automagically from existing topic data¶
You can use
jq in combination to create an input file
based on data already in a Kafka topic:
1 2 3
broker:29092 with your broker host and port, and
my_topic with the
name of your topic. You can limit how many messages are written to the file by
-c flag to the
kafkacat statement—for example,
-c42 would write
the first 42 messages from the topic.
The output file is a JSON file with an array field named
Similar to the input file, each element in the array is the
representation of the expected output messages.
The output messages array can't be empty. An expected output message should have a topic, a key, a value, and a timestamp. The following is a sample expected output file for the previous test:
1 2 3 4 5 6 7 8 9
In the input and output files you can have messages with windowed keys.
Such messages can be generated by windowed aggregations in ksqlDB. To
specify a window for a message you can add a
window field to the
message. A window field has three fields:
- start: the start time for the window.
- end: the end time for the window.
- type: the type of the window. A window type can be
The following is an example expected output file with records that have a window field:
1 2 3 4 5 6 7 8
Currently, in the input files you can have only records with session window types.
Generating output files automagically from existing topic data¶
You can use a similar approach to that shown above for generating input files, but be aware that it won't handle time windows in cases where these are required. In the case of aggregates it will often only have the final value, and not every intermediate value that is generated by the testing tool due to buffering (see discussion in "Input Consumption" below).
Running the testing tool¶
The testing tool indicates the success or failure of a test by printing the corresponding message. The following is the result of a successful test:
Your output should resemble:
Note that the tool may also write verbose log output to the terminal too, in which case you may need to page through it to locate the test status message.
If a test fails, the testing tool will indicate the failure along with the cause. Here is an example of the output for a failing test:
Query Execution in the ksqlDB Testing Tool¶
To use the ksqlDB testing tool effectively, you need to understand the query execution logic in the testing tool. Although the final results should be deterministic, the intermediate results in SQL queries (Kafka Apps) may vary based on several factors, such as order of reading input or config properties like the producer buffer size. In order to make the composition of output for the test cases simpler, the ksqlDB testing tool executes queries in a predictable way. Consider the following guidance when you prepare the output for your tests.
Before processing the next input message, the testing tool processes
input messages for each query one-by-one and writes the generated
message(s) for each input message into the result topic. This means that
queries running in the testing tool have the same behavior as when
cache.max.bytes.buffering = 0. This is especially important in
aggregate queries, where you may not see some of the intermediate results
in real executions because of buffering, but when the testing tool executes,
every possible intermediate result is created.
The ksqlDB testing tool doesn't use a real Kafka cluster. Instead, it simulates the behavior of a cluster with a single broker for the SQL queries. This means that the testing tool ignores configuration settings for the input and output topics, like the number of partitions or replicas.
The testing tool processes the statements in the order that you provide them. So, for a given statement, only the statements before it can potentially affect its results. This behavior differs from a ksqlDB cluster, where statements that are submitted later can affect the output of a query. For example, consider the following set of statements:
1 2 3 4 5 6 7
If you run these statements in a real ksqlDB cluster, you see one result generated for each INSERT INTO statement, and you have five messages in the output. On the other hand, if you run the previous statements in the testing tool, only the INSERT INTO statements before the CSAS query generate results, and the testing tool doesn't run the query for the messages generated by the INSERT INTO statements after the CSAS statement.
Note: Be aware of the the order in which the input data for a query is processed. For a given query, the testing tool first processes the input messages provided in the input file. After fully processing these messages, the testing tool inspects the source topics for the query in the simulated Kafka cluster and processes any messages in these topics. For JOIN queries that have more than one source topic, the testing tool first processes the left-side topic and then processes the right-side topic.