SQL API reference

SQL API supports the following commands as well as functions and operators.

If you'd like to propose a function or an operator to be implemented by the SQL API, check the existing issues on GitHub (opens in a new tab). If there are no relevant issues, please file a new one (opens in a new tab).

SQL commands

`SELECT`

Synopsis:

SELECT select_expr [, ...]
  FROM from_item
    CROSS JOIN join_item
    ON join_criteria]*
  [ WHERE where_condition ]
  [ GROUP BY grouping_expression ]
  [ HAVING having_expression ]
  [ LIMIT number ] [ OFFSET number ];

SELECT retrieves rows from a cube.

The FROM clause specifies one or more source cube tables for the SELECT. Qualification conditions can be added (via WHERE) to restrict the returned rows to a small subset of the original dataset.

Example:

SELECT COUNT(*), orders.status, users.city
FROM orders
  CROSS JOIN users
WHERE city IN ('San Francisco', 'Los Angeles')
GROUP BY orders.status, users.city
HAVING status = 'shipped'
LIMIT 1 OFFSET 1;

`EXPLAIN`

Synopsis:

EXPLAIN [ ANALYZE ] statement

The EXPLAIN command displays the query execution plan that the Cube planner will generate for the supplied statement.

The ANALYZE will execute statement and display actual runtime statistics, including the total elapsed time expended within each plan node and the total number of rows it actually returned.

Example:

EXPLAIN WITH cte AS (
  SELECT o.count as count, p.name as product_name, p.description as product_description
  FROM orders o
    CROSS JOIN products p
)
SELECT COUNT(*) FROM cte;
   plan_type   |                                plan
---------------+---------------------------------------------------------------------
 logical_plan  | Projection: #COUNT(UInt8(1))                                       +
               |   Aggregate: groupBy=[[]], aggr=[[COUNT(UInt8(1))]]                +
               |     CubeScan: request={                                            +
               |   "measures": [                                                    +
               |     "orders.count"                                                 +
               |   ],                                                               +
               |   "dimensions": [                                                  +
               |     "products.name",                                               +
               |     "products.description"                                         +
               |   ],                                                               +
               |   "segments": []                                                   +
               | }
 physical_plan | ProjectionExec: expr=[COUNT(UInt8(1))@0 as COUNT(UInt8(1))]        +
               |   HashAggregateExec: mode=Final, gby=[], aggr=[COUNT(UInt8(1))]    +
               |     HashAggregateExec: mode=Partial, gby=[], aggr=[COUNT(UInt8(1))]+
               |       CubeScanExecutionPlan                                        +
               |
(2 rows)

With ANALYZE:

EXPLAIN ANALYZE WITH cte AS (
  SELECT o.count as count, p.name as product_name, p.description as product_description
  FROM orders o
    CROSS JOIN products p
)
SELECT COUNT(*) FROM cte;
     plan_type     |                                                                                plan
-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Plan with Metrics | ProjectionExec: expr=[COUNT(UInt8(1))@0 as COUNT(UInt8(1))], metrics=[output_rows=1, elapsed_compute=541ns, spill_count=0, spilled_bytes=0, mem_used=0]           +
                   |   HashAggregateExec: mode=Final, gby=[], aggr=[COUNT(UInt8(1))], metrics=[output_rows=1, elapsed_compute=6.583µs, spill_count=0, spilled_bytes=0, mem_used=0]     +
                   |     HashAggregateExec: mode=Partial, gby=[], aggr=[COUNT(UInt8(1))], metrics=[output_rows=1, elapsed_compute=13.958µs, spill_count=0, spilled_bytes=0, mem_used=0]+
                   |       CubeScanExecutionPlan, metrics=[]                                                                                                                           +
                   |
(1 row)

`SHOW`

Synopsis:

SHOW name
SHOW ALL

Returns the value of a runtime parameter using name, or all runtime parameters if ALL is specified.

Example:

SHOW timezone;
 setting
---------
 GMT
(1 row)
 
SHOW ALL;
            name             |    setting     | description
-----------------------------+----------------+-------------
 max_index_keys              | 32             |
 max_allowed_packet          | 67108864       |
 timezone                    | GMT            |
 client_min_messages         | NOTICE         |
 standard_conforming_strings | on             |
 extra_float_digits          | 1              |
 transaction_isolation       | read committed |
 application_name            | NULL           |
 lc_collate                  | en_US.utf8     |
(9 rows)

SQL functions and operators

SQL API currently implements a subset of functions and operators supported by PostgreSQL (opens in a new tab).

Comparison operators

You can learn more in the relevant section (opens in a new tab) of the PostgreSQL documentation.

Function	Description	Pushdown	Post-processing
`<`	Returns `TRUE` if the first value is less than the second	✅ Yes	✅ Outer ✅ Inner (selections) ✅ Inner (projections)
`>`	Returns `TRUE` if the first value is greater than the second	✅ Yes	✅ Outer ✅ Inner (selections) ✅ Inner (projections)
`<=`	Returns `TRUE` if the first value is less than or equal to the second	✅ Yes	✅ Outer ✅ Inner (selections) ✅ Inner (projections)
`>=`	Returns `TRUE` if the first value is greater than or equal to the second	✅ Yes	✅ Outer ✅ Inner (selections) ✅ Inner (projections)
`=`	Returns `TRUE` if the first value is equal to the second	✅ Yes	✅ Outer ✅ Inner (selections) ✅ Inner (projections)
`<>` or `!=`	Returns `TRUE` if the first value is not equal to the second	✅ Yes	✅ Outer ✅ Inner (selections) ✅ Inner (projections)

Comparison predicates

You can learn more in the relevant section (opens in a new tab) of the PostgreSQL documentation.

Function	Description	Pushdown	Post-processing
`BETWEEN`	Returns `TRUE` if the first value is between the second and the third	❌ No	✅ Outer ✅ Inner (selections) ❌ Inner (projections)
`IS NULL`	Test whether value is `NULL`	✅ Yes	✅ Outer ✅ Inner (selections) ✅ Inner (projections)
`IS NOT NULL`	Test whether value is not `NULL`	✅ Yes	✅ Outer ✅ Inner (selections) ✅ Inner (projections)

Mathematical functions

You can learn more in the relevant section (opens in a new tab) of the PostgreSQL documentation.

Function	Description	Pushdown	Post-processing
`ABS`	Absolute value	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`CEIL`	Nearest integer greater than or equal to argument	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`DEGREES`	Converts radians to degrees	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`EXP`	Exponential (`e` raised to the given power)	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`FLOOR`	Nearest integer less than or equal to argument	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`LN`	Natural logarithm	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`LOG`	Base 10 logarithm	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`LOG10`	Base 10 logarithm (same as `LOG`)	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`PI`	Approximate value of `π`	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`POWER`	`a` raised to the power of `b`	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`RADIANS`	Converts degrees to radians	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`ROUND`	Rounds `v` to `s` decimal places	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`SIGN`	Sign of the argument (`-1`, `0`, or `+1`)	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`SQRT`	Square root	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`TRUNC`	Truncates to integer (towards zero)	✅ Yes	✅ Outer ✅ Inner (selections) ❌ Inner (projections)

Trigonometric functions

You can learn more in the relevant section (opens in a new tab) of the PostgreSQL documentation.

Function	Description	Pushdown	Post-processing
`ACOS`	Inverse cosine, result in radians	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`ASIN`	Inverse sine, result in radians	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`ATAN`	Inverse tangent, result in radians	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`ATAN2`	Inverse tangent of `y/x`, result in radians	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`COS`	Cosine, argument in radians	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`COT`	Cotangent, argument in radians	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`SIN`	Sine, argument in radians	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`TAN`	Tangent, argument in radians	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)

String functions and operators

You can learn more in the relevant section (opens in a new tab) of the PostgreSQL documentation.

Function	Description	Pushdown	Post-processing
`\|\|`	Concatenates two strings	✅ Yes	✅ Outer ✅ Inner (selections) ❌ Inner (projections)
`BTRIM`	Removes the longest string containing only characters in `characters` from the start and end of `string`	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`BIT_LENGTH`	Returns number of bits in the string (8 times the `OCTET_LENGTH`)	✅ Yes	✅ Outer ❌ Inner (selections) ❌ Inner (projections)
`CHAR_LENGTH` or `CHARACTER_LENGTH`	Returns number of characters in the string	✅ Yes	✅ Outer ❌ Inner (selections) ❌ Inner (projections)
`LOWER`	Converts the string to all lower case	✅ Yes	✅ Outer ✅ Inner (selections) ❌ Inner (projections)
`LTRIM`	Removes the longest string containing only characters in `characters` from the start of `string`	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`OCTET_LENGTH`	Returns number of bytes in the string	✅ Yes	✅ Outer ❌ Inner (selections) ❌ Inner (projections)
`POSITION`	Returns first starting index of the specified `substring` within `string`, or zero if it's not present	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`RTRIM`	Removes the longest string containing only characters in `characters` from the end of `string`	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`SUBSTRING`	Extracts the substring of `string`	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`TRIM`	Removes the longest string containing only characters in `characters` from the start, end, or both ends of string	✅ Yes	✅ Outer ❌ Inner (selections) ❌ Inner (projections)
`UPPER`	Converts the string to all upper case	✅ Yes	✅ Outer ❌ Inner (selections) ❌ Inner (projections)

Other string functions

You can learn more in the relevant section (opens in a new tab) of the PostgreSQL documentation.

Function	Description	Pushdown	Post-processing
`ASCII`	Returns the numeric code of the first character of the argument	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`CONCAT`	Concatenates the text representations of all the arguments	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`LEFT`	Returns first `n` characters in the string, or when `n` is negative, returns all but last `ABS(n)` characters	✅ Yes	✅ Outer ✅ Inner (selections) ✅ Inner (projections)
`REPEAT`	Repeats string the specified number of times	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`REPLACE`	Replaces all occurrences in `string` of substring `from` with substring `to`	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`RIGHT`	Returns last `n` characters in the string, or when `n` is negative, returns all but first `ABS(n)` characters	✅ Yes	✅ Outer ✅ Inner (selections) ✅ Inner (projections)
`STARTS_WITH`	Returns `TRUE` if string starts with prefix	✅ Yes	✅ Outer ✅ Inner (selections) ❌ Inner (projections)

Pattern matching

You can learn more in the relevant section (opens in a new tab) of the PostgreSQL documentation.

Function	Description	Pushdown	Post-processing
`LIKE`	Returns `TRUE` if the string matches the supplied pattern	✅ Yes	✅ Outer ✅ Inner (selections) ✅ Inner (projections)
`REGEXP_SUBSTR`	Returns the substring that matches a POSIX regular expression pattern	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)

Data type formatting functions

You can learn more in the relevant section (opens in a new tab) of the PostgreSQL documentation.

Function	Description	Pushdown	Post-processing
`TO_CHAR`	Converts a timestamp to string according to the given format	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)

Date/time functions

You can learn more in the relevant section (opens in a new tab) of the PostgreSQL documentation.

Function	Description	Pushdown	Post-processing
`DATE_ADD`	Add an interval to a timestamp with time zone	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`DATE_TRUNC`	Truncate a timestamp to specified precision	✅ Yes	✅ Outer ✅ Inner (selections) ✅ Inner (projections)
`DATEDIFF`	From Redshift (opens in a new tab). Returns the difference between the date parts of two date or time expressions	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`EXTRACT`	Retrieves subfields such as year or hour from date/time values	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`LOCALTIMESTAMP`	Returns the current date and time without time zone	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`NOW`	Returns the current date and time with time zone	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)

Conditional expressions

You can learn more in the relevant section (opens in a new tab) of the PostgreSQL documentation.

Function, expression	Description	Pushdown	Post-processing
`CASE`	Generic conditional expression	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`COALESCE`	Returns the first of its arguments that is not `NULL`	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`NULLIF`	Returns `NULL` if both arguments are equal, otherwise returns the first argument	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`GREATEST`	Select the largest value from a list of expressions	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)
`LEAST`	Select the smallest value from a list of expressions	✅ Yes	✅ Outer ❌ Inner (selections) ✅ Inner (projections)

General-purpose aggregate functions

You can learn more in the relevant section (opens in a new tab) of the PostgreSQL documentation.

Function	Description	Pushdown	Post-processing
`AVG`	Computes the average (arithmetic mean) of all the non-`NULL` input values	✅ Yes	✅ Outer ✅ Inner (selections) ✅ Inner (projections)
`COUNT`	Computes the number of input rows in which the input value is not `NULL`	✅ Yes	✅ Outer ✅ Inner (selections) ✅ Inner (projections)
`COUNT(DISTINCT)`	Computes the number of input rows containing unique input values	✅ Yes	✅ Outer ✅ Inner (selections) ✅ Inner (projections)
`MAX`	Computes the maximum of the non-`NULL` input values	✅ Yes	✅ Outer ✅ Inner (selections) ✅ Inner (projections)
`MIN`	Computes the minimum of the non-`NULL` input values	✅ Yes	✅ Outer ✅ Inner (selections) ✅ Inner (projections)
`SUM`	Computes the sum of the non-`NULL` input values	✅ Yes	✅ Outer ✅ Inner (selections) ✅ Inner (projections)
`MEASURE`	Works with measures of any type	✅ Yes	❌ Outer ✅ Inner (selections) ✅ Inner (projections)

In projections in inner parts of post-processing queries:

AVG, COUNT, MAX, MIN, and SUM can only be used with measures of compatible types.
If COUNT(*) is specified, Cube will query the first measure of type count of the relevant cube.

Aggregate functions for statistics

You can learn more in the relevant section (opens in a new tab) of the PostgreSQL documentation.

Function	Description	Pushdown	Post-processing
`COVAR_POP`	Computes the population covariance	✅ Yes	✅ Outer ✅ Inner (selections) ✅ Inner (projections)
`COVAR_SAMP`	Computes the sample covariance	✅ Yes	✅ Outer ✅ Inner (selections) ✅ Inner (projections)
`STDDEV_POP`	Computes the population standard deviation of the input values	✅ Yes	✅ Outer ✅ Inner (selections) ✅ Inner (projections)
`STDDEV_SAMP`	Computes the sample standard deviation of the input values	✅ Yes	✅ Outer ✅ Inner (selections) ✅ Inner (projections)
`VAR_POP`	Computes the population variance of the input values	✅ Yes	✅ Outer ✅ Inner (selections) ✅ Inner (projections)
`VAR_SAMP`	Computes the sample variance of the input values	✅ Yes	✅ Outer ✅ Inner (selections) ✅ Inner (projections)

Row and array comparisons

You can learn more in the relevant section (opens in a new tab) of the PostgreSQL documentation.

Function	Description	Pushdown	Post-processing
`IN`	Returns `TRUE` if a left-side value matches any of right-side values	✅ Yes	✅ Outer ✅ Inner (selections) ✅ Inner (projections)
`NOT IN`	Returns `TRUE` if a left-side value matches none of right-side values	✅ Yes	✅ Outer ✅ Inner (selections) ✅ Inner (projections)

lkml2cube REST API