How to use ksql-test-runner to test ksql queries

  • Home
  • /
  • How to use ksql-test-runner to test ksql queries
How to use ksql-test-runner to test ksql queries

How to use ksql-test-runner to test ksql queries

Confluent Kafka Ksql d b 18 Jun 2020 Siva Nadesan

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.

  • Create the KSQL statements which should be tested and save them in a file. Here, we will create a stream on source topic and then create a chained stream which will read data from 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"}
        ]
}
  • 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 sceen as shown above. This is the fastest way to test your ksql queries without writing any extra code.
References


About The Authors
Siva Nadesan

Siva Nadesan is a Principal Data Engineer. His passion includes data and blogging about technologies. He is also the creator and maintainer of www.entechlog.com

LinkedIn

Share: