ksql-test-runner is a ksqlDB testing tool to test set of KSQL statements. In this article we will see how to use ksql-test-runner with a simple example.

Instructions

• Create the KSQL statements which should be tested and save them in a file. Here, we will create a stream on the source topic and then create a chained stream which will read data from the previous stream and will convert the records to upper case

  CREATE STREAM STM_DEPARTMENTS_RAW (DEPT_NO VARCHAR, DEPT_NAME VARCHAR) WITH (KAFKA_TOPIC='STM_DEPARTMENTS_RAW', VALUE_FORMAT='JSON', PARTITIONS=1, REPLICAS=1, KEY='DEPT_NO');
  
  CREATE STREAM STM_DEPARTMENTS_WITH_KEY AS SELECT UCASE(DEPT_NO) AS DEPT_NO, UCASE(DEPT_NAME) AS DEPT_NAME FROM STM_DEPARTMENTS_RAW PARTITION BY DEPT_NO;
  

• Create input JSON file with test data which will be used for this testing. In addition to the data please remember to update the topic name also based on your use case

  {
    "inputs": [
            {"topic": "STM_DEPARTMENTS_RAW", "timestamp": 1563444000000, "value": {"DEPT_NO": "d001", "DEPT_NAME": "Marketing"}, "key": "d001"},
            {"topic": "STM_DEPARTMENTS_RAW", "timestamp": 1563444000000, "value": {"DEPT_NO": "d002", "DEPT_NAME": "Claims"}, "key": "d002"},
            {"topic": "STM_DEPARTMENTS_RAW", "timestamp": 1563444000000, "value": {"DEPT_NO": "d003", "DEPT_NAME": "Accounting"}, "key": "d003"},
            {"topic": "STM_DEPARTMENTS_RAW", "timestamp": 1563444000000, "value": {"DEPT_NO": "d004", "DEPT_NAME": "Information Technology"}, "key": "d004"}
          ]
  }
  

• Create output JSON file with expected results. In addition to the data please remember to update the topic name also based on your use case.

  {
    "outputs": [
            {"topic": "STM_DEPARTMENTS_WITH_KEY", "timestamp": 1563444000000, "value": {"DEPT_NO": "D001", "DEPT_NAME": "MARKETING"}, "key": "D001"},
            {"topic": "STM_DEPARTMENTS_WITH_KEY", "timestamp": 1563444000000, "value": {"DEPT_NO": "D002", "DEPT_NAME": "CLAIMS"}, "key": "D002"},
            {"topic": "STM_DEPARTMENTS_WITH_KEY", "timestamp": 1563444000000, "value": {"DEPT_NO": "D003", "DEPT_NAME": "ACCOUNTING"}, "key": "D003"},
            {"topic": "STM_DEPARTMENTS_WITH_KEY", "timestamp": 1563444000000, "value": {"DEPT_NO": "D004", "DEPT_NAME": "INFORMATION TECHNOLOGY"}, "key": "D004"}
          ]
  }
  

• ksqlDB test runner is a command line tool, run the ksql-test-runner in your Linux command line using one of the below command

  ksql-test-runner --input-file=/opt/confluent/scripts/ksql-test-runner/demo-input.json --sql-file=/opt/confluent/scripts/ksql-test-runner/demo-ksql-statement.sql --output-file=/opt/confluent/scripts/ksql-test-runner/demo-output.json
  

OR

ksql-test-runner -i=/opt/confluent/scripts/ksql-test-runner/demo-input.json -s=/opt/confluent/scripts/ksql-test-runner/demo-ksql-statement.sql -o=/opt/confluent/scripts/ksql-test-runner/demo-output.json

• You should have your unit testing results on screen as shown above. This is the fastest way to test your ksqlDB queries without writing any extra code

References

• https://docs.ksqldb.io/en/latest/developer-guide/test-and-debug/ksqldb-testing-tool/