Support Local/CLI variable substitution in SQL commands

Is your feature request related to a problem? Please describe. It would be useful to support variable substitutions in our SQL commands so that I can use my SQL scripts in different environments, such as Prod, Stag, QA, Devel, ..., using different names for topics, streams, etc.

Also, with the improvements being made for ksqlDB migrations and upgrades, having the option to replace variables from my SQL scripts would be necessary if I want to test them in a QA environment with real Kafka and ksqlDB servers, but using different topics, i.e. to avoid collisions with other QA users.

Describe the solution you'd like A new command that sets a new variable and value that I can use in a SQL statement. Some options here:

-- Option 1
SET LOCAL myTopicVar = 'topic-qa';
-- Option 2
VAR myTopicVar = 'topic-qa';

-- Variable substitution
CREATE STREAM myTopic (ID INT) WITH (kafka_topic='${myTopicVar}', value_format='JSON');

This variable should work in both interactive mode (CLI) and in SQL scripts.

The variable scope should be on the current session I am using. No other users should see this variable, and it should be reset (or cleaned) after exiting the CLI or run the SQL script.

The options mentioned above won't have problems with the current SET commands supported in ksqlDB.

SET <property> = <value> used to change allowed server settings
SET CLI <property> = <value> used to change CLI settings (currently supported WRAP and COLUMN-WIDTH)

Describe alternatives you've considered The alternatives use sed or envsubst commands to replaces variables from SQL scripts. I need to write a shell script with the environment variables I need, then execute one of those commands to get a new SQL file. Then execute the file.

The above alternative doesn't work with CLI, so I end up copy/paste the statements in a temporary file, replaces the variables, then copy/paste to the CLI.

confluentinc / ksql

Support Local/CLI variable substitution in SQL commands #6199