CLI

Once you entered CLI, you can see the following help info:

      __  _________________  _________    __  ___
     / / / / ___/_  __/ __ \/ ____/   |  /  |/  /
    / /_/ /\__ \ / / / /_/ / __/ / /| | / /|_/ /
   / __  /___/ // / / _, _/ /___/ ___ |/ /  / /
  /_/ /_//____//_/ /_/ |_/_____/_/  |_/_/  /_/
Command
  :h                           To show these help info
  :q                           To exit command line interface
  :help [sql_operation]        To show full usage of sql statement
SQL STATEMENTS:
  To create a simplest stream:
    CREATE STREAM stream_name;
  To create a query select all fields from a stream:
    SELECT * FROM stream_name EMIT CHANGES;
  To insert values to a stream:
    INSERT INTO stream_name (field1, field2) VALUES (1, 2);

There are two kinds of commands:

1. Basic Cli Operations, start with :
2. SQL statements end with ;

Basic CLI Operations

To Quit current cli session:

> :q

To print out help info over view:

> :h

To show specific usage of some sql statement:

> :help CREATE
  CREATE STREAM <stream_name> [IF EXIST] [AS <select_query>] [ WITH ( {stream_options} ) ];
  CREATE {SOURCE|SINK} CONNECTOR <stream_name> [IF NOT EXIST] WITH ( {connector_options} );
  CREATE VIEW <stream_name> AS <select_query>;

Available sql operations includes: CREATE, DROP, SELECT, SHOW, INSERT, TERMINATE.

SQL Statements

All the processing and storage operations are done via SQL statements.

Stream

There are two ways to create a new data stream.

1. Create an ordinary stream:

CREATE STREAM stream_name;

This will create a stream with no special function. You can SELECT data from the stream and INSERT to via corresponding SQL statement.

1. Create a stream and this stream will also run a query to select specified data from some other stream.

Adding an Select statement after Create with a keyword AS can create a stream will create a stream which processing data from another stream.

For example:

CREATE STREAM stream_name AS SELECT * from demo EMIT CHANGES;

In the example above, by adding an AS followed by a SELECT statement to the normal CREATE operation, it will create a stream which will also select all the data from demo.

After Creating the stream, we can insert values into the stream.

INSERT INTO stream_name (field1, field2) VALUES (1, 2);

There is no restriction on the number of fields a query can insert. Also, the type of value are not restricted. However, you do need to make sure that the number of fields and the number of values are aligned.

Select data from a stream

When we have a stream, we can select data from the stream in real-time. All the data inserted after the select query is created will be print out when the insert operation happens. Select supports real-time processing on the data inserted to the stream.

For example, we can choose the field and filter the data selected from the stream.

SELECT a FROM demo EMIT CHANGES;

This will only select field a from stream demo.

Terminate a query

A query can be terminated if the we know the query id:

TERMINATE QUERY <id>;

We can get all the query information by command SHOW:

SHOW QUERIES;

output just for demonstration :

╭─────────────────┬────────────────┬────────────────┬─────────────────╮
│     queryId     │   queryInfo    │ queryInfoExtra │   queryStatus   │
╞═════════════════╪════════════════╪════════════════╪═════════════════╡
│                 │ createdTime:   │                │                 │
│                 │ 1.626143326e9  │                │ status:         │
│ 810932205589156 │ sqlStatement:  │ PlainQuery:    │ Running         │
│                 │ SELECT  * FROM │ foo            │ timeCheckpoint: │
│                 │ foo       EMIT │                │ 1.626143717e9   │
│                 │ CHANGES;       │                │                 │
╰─────────────────┴────────────────┴────────────────┴─────────────────╯

Find the query to terminate, make sure is id not already terminated, and pass the query id to TERMINATE QUERY

Or under some circumstances, you can choose to TERMINATE ALL ;.

Delete a stream

Deletion command is DROP STREAM <Stream_name> ;, which deletes a stream, and terminate all the queries that depends on the stream.

For example:

SELECT * FROM demo EMIT CHANGES;

will be terminated if the stream demo is deleted;

DROP STREAM demo;

If you try to delete a stream that does not exist, an error message will be returned. To turn it off, you can use add IF EXISTS after the stream_name:

DROP STREAM demo IF EXISTS;

Show all streams

You can also show all streams by using the SHOW STREAMS command.

View

View is a projection of specified data from streams. For example,

CREATE VIEW v_demo AS SELECT SUM(a) FROM demo GROUP BY a EMIT CHANGES;

the above command will create a view which keep track of the sum of a (which have the same values,because of group by) and have the same value from the point this query is executed.

The operations on view are very similar to those on streams.

Except we can not use SELECT ... EMIT CHANGES performed on streams, because a view is static and there are no changes to emit. Instead, for example we select from view with:

SELECT * FROM v_demo WHERE a = 1;

This will print the sum of a when a = 1.

If we want to create a view to record the sum of as, we can:

CREATE STREAM demo2 AS SELECT a, 1 AS b FROM demo EMIT CHANGES;
CREATE VIEW v_demo2 AS SELECT SUM(a) FROM demo2 GROUP BY b EMIT CHANGES;
SELECT * FROM demo2 WHERE b = 1;

CLI admin mode

CLI has an admin mode, in which you can get statistics of streams.

Enter admin mode

> USE ADMIN;
ADMIN>

Admin Commands

Use show tables; to get all the value tables collected from server. It explains all the available data.

ADMIN> show tables;
+-------------------+------------------------------------------+
|       Table       |               Description                |
+-------------------+------------------------------------------+
| streams           | A  table that  lists the streams created |
|                   | in the cluster.                          |
+-------------------+------------------------------------------+
|                   | For   each  server   node,  reports  the |
| read_throughput   | estimated   per-stream  read  throughput |
|                   | over various time periods.               |
+-------------------+------------------------------------------+
|                   | For   each  server   node,  reports  the |
| append_throughput | estimated  per-stream append  throughput |
|                   | over various time periods.               |
+-------------------+------------------------------------------+

describe streams;
+----------------------+--------+------------------------------------------+
|        Column        |  Type  |               Description                |
+----------------------+--------+------------------------------------------+
| node_id              | int    | Node ID this row is for.                 |
+----------------------+--------+------------------------------------------+
| name                 | string | The name of the stream.                  |
+----------------------+--------+------------------------------------------+
| replication_property | string | Replication property configured for this |
|                      |        | stream.                                  |
+----------------------+--------+------------------------------------------+

You can use select intuitively to get and calculate the data you want.

ADMIN> select * from streams;
+---------+------+----------------------+
| node_id | name | replication_property |
+---------+------+----------------------+
| 1       | demo | node:3               |
+---------+------+----------------------+

example

Find the top 5 streams that have had the highest throughput in the last 10 minutes.

SELECT streams.name, sum(append_throughput.throughput_10min) AS total_throughput
FROM append_throughput
LEFT JOIN streams ON streams.name = append_throughput.stream_name
GROUP BY stream_name
ORDER BY total_throughput DESC
LIMIT 0, 5;