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:

and a list of data types:

Kafka Timestamps

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);

Metadata Commands

  • 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.

Hyperjoins

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;