SQL Data Manipulation

Supported functionality related to Data Manipulation Language of SQL.

Syntax is case insensitive following this template:

KEYWORDS required_input [optional_input];

Escaping Keywords

If you need to escape database or table or column, because it is named the same as our keyword, (e.g. if you have column named 'count' in your table and COUNT() is also a function), use brackets [] to escape it. See the example below:

SELECT t.[count] FROM My_Table AS t LIMIT 10;

SHOW Statement

SHOW DATABASES

Shows loaded databases in memory.

SHOW DATABASES;

SHOW TABLES

Shows tables from database. It is not needed to use database to execute this query, but it is possible.

SHOW TABLES FROM my_database;

Also this syntax is valid:

SHOW TABLES IN my_database;

SHOW COLUMNS

Shows columns from table. It is needed to use database to execute this query.

SHOW COLUMN FROM my_table;

Also this syntax is valid:

SHOW COLUMNS IN my_table;

SELECT Statement

SELECT expression [AS alias] [, expression [AS alias] ...]
FROM table [AS alias]
[WHERE expression]
[GROUP BY expression, ...]
[ORDER BY <expression> [ ASC | DESC ] , ...]
[LIMIT number [OFFSET number]];

AS

Allows you to tag column or table with alias - custom string that you can use from that point instead of original name.

GROUP BY

Allows you to group result rows into groups with the same values of the columns defined after GROUP BY keywords. Often used with aggregate functions.

LIMIT

Allows you to get the specified number of records from the whole result.

OFFSET

Allows you to skip the specified number of records from the result.

ORDER BY

Sorts the result of a query according to the columns defined after ORDER BY keyword in ascending or descending order (can be specified). Default is ascending order.

WHERE

Allows you to filter the records according to the specified conditions.

INNER JOIN [Experimental; Under Development]

The INNER JOIN keyword selects records that have matching values in both tables. This is the default type of JOIN so it is not needed to write keyword 'INNER'. This implementation of INNER JOIN is very slow, because it is implemented as nested loop join. Also, there are some bugs, e.g. in some combinations of data types, it returns wrong results. We have decided not to improve this type of join but to rewrite it completely so it is much faster and bug free. The completely new type of JOIN will be released in December 2019 (see roadmap).

SELECT column_1 [, column_2, ...]
FROM table1
[INNER] JOIN table2
ON table_1.column_name = table_2.column_name;

INSERT INTO Statement

INSERT INTO my_table (column_1 [, column_2, ...])
VALUES (value_1 [, value_2, ...]);

INSERT INTO String Values

When inserting string values via INSERT INTO, use double quotation marks.

INSERT INTO my_table (string_column) VALUES ("string value");

Operators

Operator

Functionality

+

Add

-

Subtract

*

Multiply

/

Divide

%

Modulo

&

Bitwise AND

|

Bitwise OR

^

Bitwise XOR

=

Equal

>

Greater

<

Less

>=

Greater Or Equal

<=

Less Or Equal

<>

Not Equal

!=

Not Equal

>>

Left Shift

<<

Right Shift

AND

Logical AND

OR

Logical OR

NOT

Logical NOT

Geospatial Functions

Geospatial functions currently work in planar (Euclidean) mode. Supported types are Point and Polygon. When geo column is selected, it is returned and displayed in well known text format, e.g. POINT(10.8 14), POLYGON((30.89 10, 40 40.2, 20 40, 10 20, 30.89 10)).

Note: Point and Polygon are keywords in SQL so tables or columns have to be named differently.

GEO CONTAINS

Returns bool value whether point is in polygon. Both combinations are valid: GEO_CONTAINS(wkt_point_or_column, wkt_polygon_or_column) GEO_CONTAINS(wkt_polygon_or_column, wkt_point_or_column).

Example:

SELECT geo_point FROM geo_table
WHERE GEO_CONTAINS(
POLYGON((17.0779 48.1303, 17.0912 48.1303, 17 48, 17.0779 48.1303)),
geo_point);

GEO INTERSECT

Calculates intersection of two polygons. Meaning that every point in the intersection appears in both first and second polygon. Returning type is Polygon.

GEO_INTERSECT(wkt_polygon_or_column, wkt_polygon_or_column)

Example:

SELECT GEO_INTERSECT(
POLYGON((17.0779 48.1303, 17.0912 48.1303, 17 48, 17.0779 48.1303)),
geo_polygon)
FROM geo_table;

GEO UNION

Calculates union of two polygons. Returning type is polygon.

GEO_UNION(wkt_polygon_or_column, wkt_polygon_or_column)

Typical usage:

SELECT GEO_UNION(
POLYGON((17.0779 48.1303, 17.0912 48.1303, 17 48, 17.0779 48.1303)),
geo_polygon)
FROM geo_table;

Numeric Functions

ABS

Calculates the absolute value of the numeric expression or number.

ABS(numeric_expression)

ACOS

Calculates arc cosine of the numeric expression or number. This numeric expression or number needs to be greater than -1 and less than 1.

ACOS(numeric_expression)

ASIN

Calculates arc sine of the numeric expression or number. This numeric expression or number needs to be greater than -1 and less than 1.

ASIN(numeric_expression)

ATAN

Calculates arc tangent of the numeric expression or number.

ATAN(numeric_expression)

ATN2

Calculates arc tangent of two numeric expressions or numbers.

ATN2(numeric_expression_1, numeric_expression_2)

AVG

Calculates average value of the numeric expression.

AVG(numeric_expression)

CEIL

Calculates an integer value from the numeric expression or number, so the new value is the smallest integer value that is greater or equal to the input numeric expression or number.

CEIL(numeric_expression)

COUNT

Counts the number of records.

COUNT(expression)

COS

Calculates cosine of the numeric expression or number.

COS(numeric_expression)

COT

Calculates cotangent of the numeric expression or number.

COT(numeric_expression)

EXP

Calculates Euler's number raised to the power of the input numeric expression or number.

EXP(numeric_expression)

FLOOR

Calculates an integer value from the numeric expression or number, so the new value is the greatest integer value that is less or equal to input numeric expression or number.

FLOOR(numeric_expression)

LOG

Calculates the natural logarithm of the input numeric expression or number or if there are two input numeric expressions or numbers, calculates logarithm of the first numeric expression or number to the base defined by the second numeric expression or number. The input numeric expression or number needs to be greater than 0 and base numeric expression or number needs to be greater than 1.

LOG(numeric_expression[, base])

LOG10

Calculates the logarithm of the input numeric expression or number to the base of 10. The input numeric expression or number needs to be greater than 0.

LOG10(numeric_expression)

MAX

Returns the maximum value.

MAX(numeric_expression)

MIN

Returns the minimum value.

MIN(numeric_expression)

PI

Provides the value of π (3.141592F).

PI()

POW

Calculates the first input numeric expression or number raised to the power of the second numeric expression or number.

POW(base, exponent)

ROUND

Rounds the input numeric expression or number to the number of decimal input.

ROUND(numeric_expression, decimal)

ROOT

Calculates the n-th root from the input numeric expression or number.

ROOT(numeric_expression, n)

SIGN

Returns the sign of the numeric expression or number.

SIGN(numeric_expression)

SIN

Calculates sine of the numeric expression or number.

SIN(numeric_expression)

SQRT

Calculates square root of the input numeric expression or number.

SQRT(numeric_expression)

SQUARE

Calculates square of the input numeric expression or number.

SQUARE(numeric_expression)

SUM

Calculates the sum of the input numeric expression.

SUM(numeric_expression)

TAN

Calculates tangent of the input numeric expression or number.

TAN(numeric_expression)

Advanced Functionality

CAST

Converts a value to a specified data type. The possible casting combinations are:

  • Bool → Bool, Double, Float, Int, Long, String

  • Double → Bool, Double, Float, Int, Long, String

  • Float → Bool, Double, Float, Int, Long, String

  • Int→ Bool, Double, Float, Int, Long, String

  • Long → Bool, Double, Float, Int, Long, String

  • String → Double, Float, Geo_Point, Int, Long, String

  • Geo_Point → String

  • Geo_Polygon → String

CAST(some_value AS data_type)

Two more casting combinations will be added in the near future (string to bool, string to geo_polygon).

IS NOT NULL

Allows you to get the not null values.

SELECT column_1 [, column_2, ...]
FROM table
WHERE column IS NOT NULL;

IS NULL

Allows you to get the null values.

SELECT column_1 [, column_2, ...]
FROM table
WHERE column IS NULL;

Date Functions

FORMAT OF THE DATE TYPE: year-month-day hour:minute:second

DATE

Returns the datetime in human readable format (year-month-day hour:minute:second) from Integer64 data type.

DATE(datetime_represented_as_integer64)

Also addition of two datetimes represented as Integer64 would work inside this function like this:

DATE(datetime1_represented_as_integer64 + datetime2_represented_as_integer64)

DAY

Returns the day from the input date, which is in the range from 1 to 31.

DAY(date)

HOUR

Returns the hour from the input date, which is in the range from 1 to 24.

HOUR(date)

MINUTE

Returns the minute from the input date, which is in the range from 1 to 60.

MINUTE(date)

MONTH

Returns the month from the input date, which is in the range from 1 to 12.

MONTH(date)

NOW

Returns the current date and time.

NOW()

SECOND

Returns the seconds from the input date, which is in the range from 1 to 60.

SECOND(date)

String Functions

Syntax consists from:

KEYWORDS required_input [optional input];

CONCAT

Merges two strings together.

CONCAT(string_1, string_2)

LEFT

Extracts specified number of chars from left side from the input string.

LEFT(string, charCount)

LEN

Returns the length of the input string.

LEN(string)

LOWER

Transforms the input string to lower case.

LOWER(string)

LTRIM

Removes leading spaces from the input string.

LTRIM(string)

REVERSE

Reverses the input string.

REVERSE(string)

Extracts the specified number of characters from the right side from the input string.

RIGHT(string, characters_count)

RTRIM

Removes trailing spaces from the input string.

RTRIM(string)

UPPER

Transforms the input string to upper case.

UPPER(string)