SQL is a domain-specific language used in programming and designed for managing data held in a database management system. A standard for the specification of SQL is maintained by the American National Standards Institute (ANSI). Also, there are many variants and extensions to SQL to express more specific programs.
The SQL grammar of HStreamDB is based on a subset of standard SQL with some extensions to support stream operations.
SQL inputs are made up of a series of statements. Each statement is made up of a series of tokens and ends in a semicolon (
A token can be a keyword argument, an identifier, a literal, an operator, or a special character. The details of the rules can be found in the BNFC grammar file. Normally, tokens are separated by whitespace.
The following examples are syntactically valid SQL statements:
SELECT * FROM my_stream; CREATE STREAM abnormal_weather AS SELECT * FROM weather WHERE temperature > 30 AND humidity > 80 WITH (REPLICATE = 3); INSERT INTO weather (cityId, temperature, humidity) VALUES (11254469, 12, 65);
Some tokens such as
WHERE are reserved keywords, which have specific meanings in SQL syntax. Keywords are case insensitive, which means that
select are equivalent. A keyword can not be used as an identifier.
For a complete list of keywords, see the appendix.
Identifiers are tokens that represent user-defined objects such as streams, fields, and other ones. For example,
my_stream can be used as a stream name, and
temperature can represent a field in the stream.
By now, identifiers only support C-style naming rules. It means that an identifier name can only have letters (both uppercase and lowercase letters), digits, and the underscore. Besides, the first letter of an identifier should be either a letter or an underscore.
By now, identifiers are case-sensitive, which means that
MY_STREAM are different identifiers.
An expression is a value that can exist almost everywhere in a SQL query. It can be both a constant whose value is known before execution (such as an integer or a string literal) and a variable whose value is known during execution (such as a field of a stream).
Integers are in the form of
digits are one or more single-digit integers (0 through 9). Negatives such as
-1 are also supported. Note that scientific notation is not supported yet.
Floats are in the form of
<digits>.<digits>. Negative floats such as
-11.514 are supported. Note that
- scientific notation is not supported yet.
- Forms such as
.99are not supported yet.
A boolean value is either
Strings are arbitrary character series surrounded by single quotes (
'), such as
Dates represent a date exact to a day in the form of
DATE '<year>-<month>-<day>', where
<day> are all integer constants. Note that the leading
DATE should not be omitted.
Time constants represent time exact to a second or a microsecond in the form of
TIME '<hour>-<minute>-<second>' or
TIME '<hour>-<minute>-<second>.<microsecond>', where
<microsecond> are all integer constants. Note that the leading
TIME should not be omitted.
Timestamp constants represent values that contain both date and time parts. It can also contain an optional timezone part for convenience. A timestamp is in the form of
TIMESTAMP '<timestamp_str>'. For more information, please refer to ISO 8601.
Intervals represent a time section in the form of
INTERVAL <time_str> <time_unit> or. Note that the leading
INTERVAL should not be omitted.
INTERVAL 5 SECOND(5 seconds)
Arrays represent a list of values, where each one of them is a valid expression. It is in the form of
["aa", "bb", "cc"],
A column(or a field) represents a part of a value in a stream or materialized view. It is similar to column of a table in traditional relational databases. A column is in the form of
<identifier_stream>.<identifier_column>. When a column name is ambiguous(for example it has the same name as a function application) the double quote
" can be used.
A subquery is a SQL clause start with
SELECT, see here.
Function or Operator Application
An expression can also be formed by other expressions by applying functions or operators on them. The details of function and operator can be found in the following parts.
Operators and Functions
Functions are special keywords that mean some computation, such as
MIN. And operators are infix functions composed of special characters, such as
For a complete list of functions and operators, see the appendix.
There are some special characters in the SQL syntax with particular meanings:
- Parentheses (
()) are used outside an expression for controlling the order of evaluation or specifying a function application.
- Brackets (
) are used with maps and arrays for accessing their substructures, such as
some_array. Note that it is not supported yet.
- Commas (
,) are used for delineating a list of objects.
- The semicolons (
;) represent the end of a SQL statement.
- The asterisk (
*) represents "all fields", such as
SELECT * FROM my_stream;.
- The period (
.) is used for accessing a field in a stream, such as
- The double quote (
") represents an "raw column name" in the
SELECTclause to distinguish a column name with functions from actual function applications. For example,
SELECT SUM(a) FROM s;means applying
SUMfunction on the column
s. However if the stream
sactually contains a column called
SUM(a)and you want to take it out, you can use back quotes like
SELECT "SUM(a)" FROM s;.
A single-line comment begins with
// This is a comment
Also, C-style multi-line comments are supported:
/* This is another comment */