SQL Like Query Language

To interact with NSDb, a custom SQL like query language must be used. It has been designed to suit NSDb metrics structure and in the meantime to feel familiar with standard SQL. Since the nature of NSDb is different than a classic SQL database, its dialect is conceived to handle time-series data accordingly.


SQL Statements

Similarly to SQL databases, NSDb allows 3 main DML statements:

Unlike standard SQL, NSDb does not support UPDATE statement. The main reason of this is its time series nature itself; it’s extremely unlikely that a time series record has to be updated at a later time.

NOTE: NSDb SQL parser is case-insensitive, so SQL keywords can be both lower-case and upper-case. In this documentation all code example are uppercase for clarity purpose.


The Select Statement

The SELECT statement queries bits from a specific metric. SELECT statements are used to perform data exploration and to subscribe historical or real-time queries.

Simple Syntax

SELECT <dimension_name> [,<dimension_name>, <tag_name>, ... ]
FROM <metric_name>
[ WHERE <expression> ]
[ GROUP BY <tag_name> | <time_range> ]
[ ORDER BY <dimension_name> | <tag_name> [DESC] ]
[ LIMIT v ]

SELECT statements requires a SELECT clause and a FROM clause.

Furthermore WHERE condition can be specified to retrieve bits that meet the WHERE clause condition. See WHERE clause Retrieved data may be ordered using one of the projected dimension.See ORDER BY clause

SELECT clause

The SELECT clause defines which bit’s dimensions are projected.

NOTE: Bit’s value and timestamp are always included in projections.

Syntax

SELECT statements supports different formats:

SELECT * Returns all dimension in the selected metric.

SELECT  * FROM metric

SELECT <dimension_name> applies projection on the selected dimension.

SELECT  value FROM metric

SELECT <dimension_name>, <dimension_name>+ applies projection on the selected dimensions.

SELECT  value, dimension FROM metric

COUNT operators can be applied in a SELECT clause , returning the number of bits in a metric if no WHERE clauses are defined.

SELECT  COUNT(*) FROM metric

DISTINCT operator

Defining the SELECT clause users can apply a DISTINCT operator on the projected dimension. This operator allows to return distinct values for the dimension on which is applied.

Usage example:

SELECT  DISTINCT dimension FROM metric

NOTE: projecting only a single dimension is allowed when DISTINCT operator is used.

WHERE clause

The WHERE clause allows to define a boolean condition, based on which data are filtered.

SELECT <dimension_name>[,<dimension_name>, [...]]
FROM <metric_name>
WHERE [NOT] <expression> [(AND|OR) <expression> [...]]

The WHERE clause supports comparisons against VARCHAR, INT, DECIMAL, BIGINT dimensions datatypes.

Supported numerical string operators:

SELECT dimension FROM metric WHERE numerical_dimension = 1 AND another_numerical_dimension = 2

Supported string operators:

SELECT dimension FROM metric WHERE string_dimension = myStringValue AND another_string_dimension LIKE startWith$

Logical operators

Where conditions can be chained using the logical AND and OR operators. A single condition can be negated using the NOT logical operator. By default, both AND and OR operators are right associative; in order to overcome this behaviour, round brackets can be used to set a custom associativity. The NOT operator si applied to the immediately following condition; round brackets can be also used in this case to apply that operator to a chain of conditions. Examples:

SELECT dimension FROM metric WHERE string_dimension = myStringValue AND another_string_dimension LIKE startWith$ OR a_further_dimension >= 0  
-- is translated into
SELECT dimension FROM metric WHERE string_dimension = myStringValue AND (another_string_dimension LIKE startWith$ OR a_further_dimension >= 0)
-- NOT is applied only to the like condition
SELECT dimension FROM metric WHERE string_dimension = myStringValue AND NOT another_string_dimension LIKE startWith$ OR a_further_dimension >= 0
-- NOT is applied to the whole chain of operators
SELECT dimension FROM metric WHERE NOT (string_dimension = myStringValue AND another_string_dimension LIKE startWith$ OR a_further_dimension >= 0)

LIKE operator

The LIKE operator is used to express WHERE conditions in which user have to match part of the complete string value. Accordingly to common databases standard the character $ is used as placeholder.

SELECT dimension FROM metric WHERE string_dimension LIKE $endWith AND another_string_dimension LIKE startWith$

Time operators

Since NSDb is a time-series database, specific operators handling timestamp field are defined. However, since timestamp is a numerical field the above-mentioned numerical operators are still valid. In addition, time operators are implemented allowing users to express time-dependent expression in a friendly manner. Ad hoc operator NOW is available to express the actual timestamp in milliseconds. Simple arithmetic operations can be applied on this value:

Nullability operators

According to the dynamic nature of NSDb schemas, dimensions can be omitted during data insertions, therefore Null values may be filtered inclusively or exclusively using dedicated operators:

Examples:

SELECT dimension FROM metric WHERE dimension IS NULL
SELECT dimension FROM metric WHERE dimension IS NOT NULL

GROUP BY clause

The GROUP BY clause groups query result using a specified dimension.

SELECT <dimension_name>[,<dimension_name>, [...]]
FROM <metric_name>
[WHERE <expression>]
[GROUP BY <tag_name> | INTERVAL <time_range>]

If the query includes a WHERE clause the GROUP BY clause must appear after the WHERE clause.

NOTE: GROUP BY clause accepts a single tag/field on which apply the grouping. It is not possible to use a dimension for grouping.

When defining a GROUP BY clause value functions can be defined in SELECT clause.

Value Aggregation Functions

Value functions are arithmetic operations applied on value field of bits retrieved by a SELECT statement with a GROUP BY clause. Basic arithmetic functions are available:

Interval Group By

Another peculiarity of a time series database is to allow grouping by time intervals. NSDb supports COUNT aggregations over time interval grouped buckets. A time range can be specified as follows

ORDER BY clause

Query results ordering can be applied defining an ORDER BY . The ORDER BY clause must appear before LIMIT clause and not before WHERE and GROUP BY clauses.

SELECT <select_clause>
FROM <metric_name>
[WHERE <where_clause>]
[GROUP BY <group_by_clause>]
[ORDER BY <dimension_name>[DESC]]

Two ordering are defined:

SELECT dimension1, dimension2 FROM metric ORDER BY value DESC
SELECT dimension1, dimension2 FROM metric WHERE dimension1 >= 1 ORDER BY dimension2 DESC

LIMIT clause

The LIMIT clause allows users to define the maximum number of bits to be retrieved. LIMIT clause definition is not mandatory, but strongly recommended in order to speed up response time.

SELECT <select_clause>
FROM <metric_name>
[WHERE <where_clause>]
[GROUP BY <group_by_clause>]
[ORDER BY <dimension_name>[DESC]]
[LIMIT n]

Using limit clause in combination with order can be very useful to retrieve oldest or youngest records of a time series.

For example, the query below returns the 10 youngest records

SELECT * FROM metric WHERE dimension1 >= 1 ORDER BY timestamp DESC LIMIT 10

while the following

SELECT * FROM metric WHERE dimension1 >= 1 ORDER BY timestamp LIMIT 10

the 10 oldest ones.


The Insert Statement

NSDb allows bit insertion making use of Insert statement, whose syntax is similar to standard SQL insert. By the way there are some small differences meant in time-series concept that introduces value and timestamp fields.

Simple Syntax

 INSERT INTO <metric_name> [TS=<timestamp_value>] DIM ( [<dimension_name> = <dimension_value>*] ) TAGS ( [<tag_name> = <tag_value>*] ) VAL = <value>

The above mentioned syntax inserts a single Bit whose dimensions tuples (name, value) are declared after DIM clause and tag tuples after TAGS clause. Value field is assigned using VAL clause , that accepts a numerical value. Timestamp index definition is not mandatory, but it can be defined using TS clause.

NOTE: if TS clause’s value is not defined, it is assigned the epoch-timestamp of the istant the insertion is performed .

Examples

INSERT INTO metric DIM ( dimension_1 = 1, dimension_2 = myStringValue ) VAL = 1
INSERT INTO metric DIM ( dimension_1 = 1, dimension_2 = 'my String Value' ) VAL = 1

NOTE: user can define VARCHAR dimensions’ values wrapping them inside ' ', this notation is mandatory in case of strings with spaces.

INSERT INTO metric TS = 1522232017 DIM ( dimension_1 = 1, dimension_2 = 'myStringValue' ) VAL = 1.5

Delete Statement

Delete statement allows Bits deletion defining WHERE clause to express the condition based on deletion is performed.

Simple Syntax

DELETE FROM <metric_name> WHERE <where_clause>

NOTE: WHERE clause must be qualified to express deletion condition. To delete all bits belonging to a specific metric user must use DELETE METRIC NSDb command or the tricky query DELETE FROM <metric_name> WHERE timestamp > 0 which works assuming that the metric describes a non-relativistic physics phenomenon.

Examples

DELETE FROM metric WHERE timestamp IN (2,4)
DELETE FROM metric WHERE NOT timestamp >= 2 OR timestamp < 4
Privacy Policy Radicalbit S.r.l. • VAT IT09292580967‬