CLI - Interactive Mode
The CLI Interactive mode is intended to execute jobs and receive feedback as the job is being validated, launched and finally executed. This allows you to iterate on your SQL, receive results, and deploy jobs.
Starting Interactive Mode
To start the interactive mode issue the following command:
ev job -c <CLUSTER_NAME> -i
where CLUSTER_NAME is the name or ID of the cluster where you would like the commands, and SQL to be executed against. When you are connected you will get the evsql> prompt like:
Interactive Job Mode ON
To Exit type "!quit"
sqlline version EVSQL
evsql>
Showing available virtual table sources
To display a list of available virtual tables, or sources, to issue SQL against, use the !show tables command:
!show tables
NOTE the use of the ! exclamation point at the beginning. All interactive mode commands start with an ! exclamation point.
Describing a virtual table sources
To describe a virtual table, or source, use the !desc <tablename> command:
!desc airplanes
the command above would generate output similar to the following:
evsql> !desc airplanes
------------------------- Source (Table): "airplanes" --------------------------
ID : 17
Name : airplanes
Type : kafka
Column | Type
-----------------------------------------------------------+--------------------
icao | STRING
flight | STRING
timestamp_verbose | STRING
msg_type | STRING
track | STRING
counter | STRING
lon | STRING
lat | STRING
altitude | INT
vr | INT
speed | INT
tailnumber | STRING
timestamp | LONG
timestamp_ms | LONG
evsql>
Running SQL
To start the job all that is required is that some valid SQL be issued at the command prompt. For example:
SELECT icao, lat, lon, altitude FROM airplanes;
The SQL above assumes we have a source called airplanes, which contains a stream of events. An airplane generates events indicating icao (ID number), lat, lon, and altitude.
evsql> SELECT icao, lat, lon, altitude FROM airplanes;
Created job with ID: 5065, NAME: runner_27c81ea2a66847af823cd7ec24629c43 and SQL : SELECT icao, lat, lon, altitude FROM airplanes
Finding deployment 'SSB'
Finding job with ID: 5065
Found job with ID: 5065, and NAME: runner_27c81ea2a66847af823cd7ec24629c43
Operating with no sink
SOURCES: ['airplanes']
Validating SQL: SELECT icao, lat, lon, altitude FROM airplanes
SQL validated successfully
Making ephemeral sink: '__ssb_output_5065'
Making ephemeral logging destination: '__sbjob_log_5065'
Updating job ID: 5065, NAME: 'runner_27c81ea2a66847af823cd7ec24629c43', with run data: {"restart_strategy": "never", "restart_retry_time": "30", "parallelism": 1, "snapshot": {"is_create": false, "name": null, "retention": null, "is_recreate": true, "key_column_name": null, "is_ignore_nulls": false, "require_restart": false, "api_key": null}}
Verifying version is runnable, SSB Version: 8.0.4-rc1
Submitting Job...
{
"message": "Job with ID: 5065, is starting"
}
Waiting for data... [Press any key to return to command prompt]
"{\"icao\":\"AE058C\",\"lat\":\"\",\"lon\":\"\",\"altitude\":null}"
"{\"icao\":\"A2D674\",\"lat\":\"\",\"lon\":\"\",\"altitude\":null}"
"{\"icao\":\"A582A3\",\"lat\":\"\",\"lon\":\"\",\"altitude\":null}"
"{\"icao\":\"A2D674\",\"lat\":\"30.37594\",\"lon\":\"-98.21750\",\"altitude\":22000}"
"{\"icao\":\"A2D674\",\"lat\":\"\",\"lon\":\"\",\"altitude\":21975}"
"{\"icao\":\"AC0A22\",\"lat\":\"\",\"lon\":\"\",\"altitude\":null}"
"{\"icao\":\"ADFC8A\",\"lat\":\"\",\"lon\":\"\",\"altitude\":12125}"
evsql>
The output above indicates a job named runner_27c81ea2a66847af823cd7ec24629c43 was created with ID 5065, that the SQL validated successfully, and that the job was submitted to the engine for execution. Finally, after the job has started some sample output data can be seen, which is coming directly from the job that was just started.
Semicolon Terminator
To execute a block of SQL entered at the command prompt, a ; semicolon must be added at the end. If no semicolon is added and the ENTER key is pressed, a multilne SQL statement is constructed. To finish the multiline SQL statement and execute it enter a semicolon and press ENTER.
evsql> SELECT
2-semicolon> icao,
3-semicolon> lat,
4-semicolon> lon,
5-semicolon> altitude
6-semicolon> FROM
7-semicolon> airplanes
8-semicolon> ;
History
To see what commands were executed previously, the !history command can be used:
!history
the command above would generate output similar to the following:
evsql> !history
193 19:37:33 !quit
194 23:37:45 select * from airplanes;
195 23:38:07 !quit
196 23:41:31 select * from airplanes;
197 23:41:58 !quit
198 11:36:22 !show tables
199 11:36:31 !desc kg_ms
200 11:36:38 !desc KG_ms
201 11:36:57 !desc sniper
202 11:37:05 !quit
203 08:46:11 !show tables
204 08:46:22 !desc airplanes
205 08:47:03 SELECT icao, lat, lon, altitude FROM airplanes;
206 08:51:05 !quit
207 09:09:32 SELECT icao, lat, lon, altitude FROM airplanes;
208 09:32:53 !desc airplanes
209 09:33:50 !history
evsql>
TAB Autocomplete
If you are not sure what command options are available, some commands provide TAB autocomplete. For example if you enter the command !show and then press the tab key, you would see something similar to the following:
evsql> !show jobs
jobs tables sources
The command will default to the first option as soon as the TAB key is pressed. To change the option simply move the arrow keys on the keyboard to navigate through the options.