SQLStreamBuilder SQL Syntax Reference
SQLStreamBuilder utilizes Apache Calcite compatible streaming SQL. Beyond that there are metadata commands, and some built-ins.
Some supported operations are:
FOO || 'baz'
- Tumble, Hopping window expressions
- Filter after aggregation
- Logical operators
- Arithmetic operators and functions
- Character string operators and functions
- Binary string operators and functions
- Date/Time functions
- Type conversion
- Aggregate functions
- Grouped window functions
- Grouped Auxiliary functions
and a list of data types:
Kafka as of 0.10.0 has supported timestamps as part of the messageformat. This is exposed to SQLStreamBuilder via the keyword
eventTimestamp. The time in the message is the time at message creation. SQLStreamBuilder also respects
current_timestamp which is the current timestamp at query execution start time.
Thus, queries can be constructed like:
SELECT sensor_name FROM sensors WHERE eventTimestamp > current_timestamp;
and can be used in window queries like:
SELECT SUM(CAST(amount AS numeric)) AS payment_volume, CAST(TUMBLE_END(eventTimestamp, interval '1' hour) AS varchar) AS ts FROM payments GROUP BY TUMBLE(eventTimestamp, interval '1' hour);
show tables- list virtual tables.
desc <vtable>- describe the specified virtual table, showing columns and types.
show jobs- list current running SQL jobs.
show history- show SQL query history (only successfully parsed/executed).
help- show help.
Joins are considered “hyperjoins” because SQLStreamBuilder has the ability to join multiple virtual tables in a single query, and because a virtual table is created from a data provider, these joins can span multiple clusters/connect strings, but also multiple types of sources (join Kafka and a database for instance). So something like this is possible:
SELECT us_west.user_score+ap_south.user_score FROM kafka_in_zone_us_west us_west FULL OUTER JOIN kafka_in_zone_ap_south ap_south ON us_west.user_id = ap_south.user_id;