Supported SQL Dialect and SQL Functions

SQL dialect

Contiamo supports most functionality for the standard SQL queries (SELECT statements), with some system specific extensions.
Read the specification below to get more detail on the syntax:
1
query:
2
values
3
| WITH withItem [ , withItem ]* query
4
| {
5
select
6
| selectWithoutFrom
7
| query UNION [ ALL | DISTINCT ] query
8
}
9
[ ORDER BY orderItem [, orderItem ]* ]
10
[ LIMIT { [ start, ] count | ALL } ]
11
[ OFFSET start { ROW | ROWS } ]
12
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
13
14
withItem:
15
name
16
[ '(' column [, column ]* ')' ]
17
AS '(' query ')'
18
19
orderItem:
20
expression [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
21
22
select:
23
SELECT [ ALL | DISTINCT ]
24
{ * | projectItem [, projectItem ]* }
25
FROM tableExpression
26
[ WHERE booleanExpression ]
27
[ GROUP BY { groupItem [, groupItem ]* } ]
28
[ HAVING booleanExpression ]
29
[ WINDOW windowName AS windowSpec [, windowName AS windowSpec ]* ]
30
31
selectWithoutFrom:
32
SELECT [ ALL | DISTINCT ]
33
{ * | projectItem [, projectItem ]* }
34
35
projectItem:
36
expression [ [ AS ] columnAlias ]
37
| tableAlias . *
38
39
tableExpression:
40
tableReference [, tableReference ]*
41
| tableExpression [ NATURAL ] [ ( LEFT | RIGHT | FULL ) [ OUTER ] ] JOIN tableExpression [ joinCondition ]
42
| tableExpression CROSS JOIN tableExpression
43
44
joinCondition:
45
ON booleanExpression
46
| USING '(' column [, column ]* ')'
47
48
tableReference:
49
tablePrimary
50
[ [ AS ] alias [ '(' columnAlias [, columnAlias ]* ')' ] ]
51
52
tablePrimary:
53
[ [ catalogName . ] schemaName . ] tableName
54
'(' TABLE [ [ catalogName . ] schemaName . ] tableName ')'
55
56
groupItem:
57
expression
58
| '(' ')'
59
| '(' expression [, expression ]* ')'
60
61
window:
62
windowName
63
| windowSpec
64
65
windowSpec:
66
'('
67
[ windowName ]
68
[ ORDER BY orderItem [, orderItem ]* ]
69
[ PARTITION BY expression [, expression ]* ]
70
')'
Copied!

Functions

The following functions are supported in Contiamo. If possible their execution will be pushed down to the underyling data source (see list of exceptions in "Support in data sources").

Schema functions

Utility functions to extract connection and metadata information:
  • CURRENT_DATABASE() -> Returns the Project ID of your Contiamo project, which groups your data sources
  • VERSION() -> Returns the version of the Postgres-like query engine from Contiamo

Array functions

Functions for dealing with arrays:
  • ARRAY_POSITION(array, anyelement [, integer]) -> Returns the first occurrence of the second argument in the array, starting at the element indicated by the third argument or at the first element. Returns null if no element is found.
  • ARRAY_TO_STRING(array, separator varchar) -> Returns array as String where elements are separated by the specified separator.

String functions

Functions:
  • CHAR_LENGTH(varchar) -> Returns the length of the passed string
  • CHARACTER_LENGTH(varchar) -> Returns the length of the passed string
  • INITCAP(varchar) Returns string with the first letter of each word converted to upper case and the rest to lower case.
  • LEFT(string varchar, num_chars integer) -> Returns the specified number of chars
    (num_chars) from the left of the specified string (string)
  • LENGTH(varchar) -> Returns the length of the passed string.
  • LOWER(varchar) -> Returns the passed string in lower case letters
  • POSITION(a varchar IN b varchar) -> Returns the position of the first occurrence of string a in string b
  • QUOTE_IDENT(string text) -> Return the given string suitably quoted to be used as an identifier in an SQL statement string. Quotes are added only if necessary and embedded quotes are properly doubled.
  • RIGHT(string varchar, num_chars integer) -> Returns the specified number of chars (num_chars) from the right of the specified string (string)
  • SPLIT_PART(string varchar, delimiter varchar, position integer) -> splits a string on a specified delimiter and returns the nth substring
  • SUBSTRING(a varchar FROM start integer) -> Returns a substring of string a starting at a given point start
  • SUBSTRING(a varchar FROM start integer FOR num_chars integer) -> Returns a substring of string a starting at a given point start for specified number of characters num_chars
  • UPPER(varchar) -> Returns the passed string in upper case letters
Operators:
  • Like varchar LIKE varchar -> Comparison of strings, where the wildcard character % can be used to compare only part of the string.
  • Pipe varchar || varchar -> Concatenates 2 strings
  • Tilde varchar [!]~[*] varchar -> Regex matching operator. First argument is the string to match. Second is a POSIX regular expression. Regex syntax can slightly differ depending on actual datasource implementation. ! and * are optional. ! negates matching result, * enables case-insensitive matching.

Numeric functions

  • POWER(numeric1, numeric2) -> Returns numeric1 raised to the power of numeric2
  • ABS(numeric) -> Returns the absolute value of numeric
  • MOD(numeric1, numeric2) -> Returns the remainder (modulus) of numeric1 divided by numeric2. The result is negative only if numeric1 is negative
  • SQRT(numeric) -> Returns the square root of numeric
  • LN(numeric) -> Returns the natural logarithm (base e) of numeric
  • LOG10(numeric) -> Returns the base 10 logarithm of numeric
  • CEIL(numeric) -> Rounds numeric up, returning the smallest integer that is greater than or equal to numeric
  • FLOOR(numeric) -> Rounds numeric down, returning the largest integer that is less than or equal to numeric
  • RAND([seed]) -> Generates a random double between 0 and 1 inclusive, optionally initialising the random number generator with seed
  • ACOS(numeric) -> Returns the arc cosine of numeric
  • ASIN(numeric) -> Returns the arc sine of numeric
  • ATAN(numeric) -> Returns the arc tangent of numeric
  • ATAN2(numeric, numeric) -> Returns the arc tangent of the numeric coordinates
  • COS(numeric) -> Returns the cosine of numeric
  • SIN(numeric) -> Returns the sine of numeric
  • TAN(numeric) -> Returns the tangent of numeric
  • COT(numeric) -> Returns the cotangent of numeric
  • DEGREES(numeric) -> Converts numeric from radians to degrees
  • RADIANS(numeric) -> Converts numeric from degrees to radians
  • ROUND(numeric1 [, numeric2]) -> Rounds numeric1 to optionally numeric2 (if not specified 0) places right to the decimal point
  • TRUNC(numeric1 [, numeric2]) -> Truncates numeric1 towards zero to a whole number, or optionally to the specified number of decimal places

Date and time functions

  • CURRENT_DATE -> Returns the current date in the session time zone, in a value of datatype DATE
  • CURRENT_TIMESTAMP -> Returns the current date and time in the session time zone, in a value of datatype * TIMESTAMP WITH TIME ZONE
  • DATE_TRUNC('timeUnit', datetime) -> Truncate datetime to specified timeUnit. Note that timeUnit value must be passed as a string (wrapped in single quotes). Similar to FLOOR(datetime TO timeUnit)
  • EXTRACT(timeUnit FROM datetime) -> Extracts and returns the value of a specified datetime field from a datetime value expression
  • FLOOR(datetime TO timeUnit) -> Rounds datetime down to timeUnit. Similar to DATE_TRUNC('timeUnit', datetime)
  • YEAR(date) -> Equivalent to EXTRACT(YEAR FROM date). Returns an integer.
  • QUARTER(date) -> Equivalent to EXTRACT(QUARTER FROM date). Returns an integer between 1 and 4.
  • MONTH(date) -> Equivalent to EXTRACT(MONTH FROM date). Returns an integer between 1 and 12.
  • WEEK(date) -> Equivalent to EXTRACT(WEEK FROM date). Returns an integer between 1 and 53.
  • DAYOFYEAR(date) -> Equivalent to EXTRACT(DOY FROM date). Returns an integer between 1 and 366.
  • DAYOFMONTH(date) -> Equivalent to EXTRACT(DAY FROM date). Returns an integer between 1 and 31.
  • DAYOFWEEK(date) -> Equivalent to EXTRACT(DOW FROM date). Returns an integer between 1 and 7.
  • HOUR(date) -> Equivalent to EXTRACT(HOUR FROM date). Returns an integer between 0 and 23.
  • MINUTE(date) -> Equivalent to EXTRACT(MINUTE FROM date). Returns an integer between 0 and 59.
  • SECOND(date) -> Equivalent to EXTRACT(SECOND FROM date). Returns an integer between 0 and 59.
  • TIMESTAMPADD(timeUnit, integer, datetime) -> Returns datetime with an interval of (signed) integer timeUnits added. Equivalent to datetime + INTERVAL 'integer' timeUnit
timeUnit can be one of the following MILLENNIUM, CENTURY, DECADE, YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND

Window functions

Functions that can be used in an over clause in the query:
  • ROW_NUMBER() -> number of the current row within its partition, counting from 1
  • RANK() -> rank of the current row with gaps; same as row_number of its first peer
  • DENSE_RANK() -> rank of the current row without gaps; this function counts peer groups
  • NTILE(num_buckets integer) -> integer ranging from 1 to the argument value, dividing the partition as equally as possible
  • LAG(value any [, offset integer [, default any]]) -> returns value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead return default (which must be of the same type as value). Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null
  • LEAD(value any [, offset integer [, default any]]) -> returns value evaluated at the row that is offset rows after the current row within the partition; if there is no such row, instead return default (which must be of the same type as value). Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null
  • FIRST_VALUE(value any) -> returns value evaluated at the row that is the first row of the window frame
  • LAST_VALUE(value any) -> returns value evaluated at the row that is the last row of the window frame

System functions

Some functions are only supported for Postgres metadata compatibility. Currently they can only be used when querying pg_catalog and information_schema tables. They are generally needed by visualization, reporting, and other BI and database querying tools.
  • PG_GET_EXPR(pg_node_tree varchar, relation_oid varchar) -> Decompiles Postgres internal form of an expression, assuming that any Vars in it refer to the relation indicated by the second parameter
  • PG_GET_USERBYID(oid integer) -> Returns "contiamo".
  • PG_TABLE_IS_VISIBLE(oid integer) -> Returns true.
  • PG_ENCODING_TO_CHAR(encoding integer) -> Returns "UTF8" if the encoding is UTF8.
  • OBJ_DESCRIPTION(oid integer, catalogName varchar) -> Returns an empty String.

Support in data sources

Not all the SQL functions can be run on all data sources. In the following list you can see which functions not supported for push-down.
  • varchar [!]~[*] varchar -> DB2 supports from version 11.1 on, Clickhouse ignores case-insensitive flag.
  • CHAR_LENGTH -> DB2 and Oracle
  • CHARACTER_LENGTH -> DB2 and Oracle
  • DAYOFMONTH() -> Oracle
  • DAYOFWEEK() -> Bigquery, Clickhouse, DB2, HSQLDB and Oracle
  • DAYOFYEAR() -> Bigquery, Clickhouse, DB2, HSQLDB and Oracle
  • DENSE_RANK() -> Clickhouse and DB2
  • EXTRACT(... FROM ...) -> Oracle
  • FIRST_VALUE() -> Clickhouse
  • FLOOR() on DATE, DATETIME, TIMESTAMP -> Bigquery, Clickhouse and Oracle
  • FULL OUTER JOIN -> Bigquery and Clickhouse
  • HOUR() -> Oracle
  • INITCAP() -> Bigquery, Clickhouse
  • LAG() -> Clickhouse and DB2
  • LEAD() -> Clickhouse and DB2
  • LEFT() -> Bigquery and Oracle
  • MINUTE() -> Oracle
  • MONTH() -> Oracle
  • ORDER BY ... NULLS FIRST -> Clickhouse
  • ORDER BY ... NULLS LAST -> Bigquery and Clickhouse
  • POSITION(... IN ...) -> Bigquery, Clickhouse, DB2 and Oracle
  • QUARTER() -> DB2 and Oracle
  • RANK() -> Clickhouse and DB2
  • RIGHT() -> Bigquery and Oracle
  • ROW_NUMBER() -> Clickhouse and DB2
  • SECOND() -> Oracle
  • SUBSTING(... FROM ...) -> Bigquery and DB2
  • SUBSTING(... FROM ... FOR ...) -> Bigquery and DB2
  • TIMESTAMPADD() -> Bigquery, Clickhouse, DB2 and Oracle
  • WEEK() -> Clickhouse, DB2, HSQLDB and Oracle
  • YEAR() -> Oracle
Last modified 1yr ago