Skip to main content
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 supported in the SQL API, check the existing issues on GitHub. If there are no relevant issues, please file a new one.

SQL commands

SELECT

Synopsis:
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 DISTINCT ON (expression [, ...]) is also supported, with the same semantics as PostgreSQL: for each set of rows with matching values in the given expressions, only the first row is kept, using the order specified by ORDER BY (which must start with the DISTINCT ON expressions). Example:

UNION

UNION and UNION ALL combine the results of two or more SELECT statements. UNION ALL keeps all rows. UNION removes duplicate rows. Example:
If all SELECT statements query cubes of the same data source, the SQL API uses query pushdown to run the whole union in that data source. The row limit then applies to the result of the union, not to each SELECT statement. The SQL API also pushes down filters and projections over the union. In these cases, the SQL API runs the union in query post-processing:
  • The SELECT statements query cubes of different data sources. Each SELECT statement is subject to the row limit on its own. See Querying across data sources for details.
  • The data source can’t run set operations: Druid, ksqlDB, and SQLite.
An ORDER BY clause that sorts the whole union runs in post-processing. It sorts the rows that remain after the row limit. Read more about this edge case.

EXPLAIN

Synopsis:
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:
With ANALYZE:

SHOW

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

SET

Synopsis:
The SET command changes a session variable to a new value. Use SET with the cube_cache session variable for cache control. You can also set the session time zone with SET TIME ZONE (or SET TIMEZONE):
Use DEFAULT to reset the time zone to its default. The LOCAL form (SET TIME ZONE LOCAL) is not supported.

CREATE TEMPORARY TABLE

Synopsis:
Creates a table that lives for the duration of the session, either filled by a query or empty, to be filled by COPY. Temporary tables can be joined with cubes, and are dropped with DROP TABLE. Column types are limited to those COPY can load:
  • boolean
  • smallint, integer, bigint
  • real, double precision, numeric (up to a precision of 38)
  • varchar, text, and the other variable-width character types (character varying, nvarchar, string), as well as uuid, json and jsonb, all of which are stored as text
  • date, timestamp (and datetime) without a time zone
A numeric without a precision holds numeric(38, 10), so values are rounded to ten decimal places. Give the precision and scale to keep more of them. Fixed-width character/char columns are not accepted: PostgreSQL pads their values to the declared width and ignores trailing blanks when comparing them, which a text column does not do. Use varchar or text instead. The amount of data held is capped per session and per server, by the CUBESQL_TEMP_TABLE_SESSION_MEM (10 MiB) and CUBESQL_TEMP_TABLE_TOTAL_MEM (100 MiB) environment variables.

COPY

Synopsis:
Loads data sent by the client into a temporary table. Only FROM STDIN is supported: cubes are a read-only data source, so a temporary table is the only place data can go, and a file or a program target would read on the Cube host rather than on the machine running the client. Columns not listed in the statement are left NULL. Repeating the command appends more rows to the table. Supported options: FORMAT (text or csv), DELIMITER, NULL, HEADER, QUOTE, ESCAPE, FORCE_NOT_NULL, FORCE_NULL, and ENCODING (UTF8 only). They behave as in PostgreSQL, including their defaults. The pre-9.0 syntax (e.g. CSV HEADER) is supported as well; the BINARY format is not. Example, using the \copy command of psql to load a CSV file:

SQL functions and operators

SQL API currently implements a subset of functions and operators supported by PostgreSQL. Additionally, it supports a few custom functions.

Comparison operators

Learn more in the relevant section of the PostgreSQL documentation.

Comparison predicates

Learn more in the relevant section of the PostgreSQL documentation.

Mathematical functions

Learn more in the relevant section of the PostgreSQL documentation.
WIDTH_BUCKET pushdown is only available on data sources whose SQL dialect supports it. It is not supported with Apache Pinot, BigQuery, CrateDB, Cube Store, Dremio, Druid, DuckDB, Firebolt, Hive, ksqlDB, Microsoft SQL Server, MySQL, QuestDB, or SQLite.

Trigonometric functions

Learn more in the relevant section of the PostgreSQL documentation.

String functions and operators

Learn more in the relevant section of the PostgreSQL documentation.

Other string functions

Learn more in the relevant section of the PostgreSQL documentation.
SPLIT_PART pushdown is only available on Amazon Redshift, CrateDB, Cube Store, Databricks, Dremio, DuckDB, Firebolt, Materialize, Postgres, and Snowflake. With other data sources, Cube evaluates it after fetching the data.

Pattern matching

Learn more in the relevant section of the PostgreSQL documentation.

Data type formatting functions

Learn more in the relevant section of the PostgreSQL documentation.

Date/time functions

Learn more in the relevant section of the PostgreSQL documentation.
DATE_TRUNC only accepts the default granularities: year, quarter, month, week, day, hour, minute, second. Custom granularities can’t be addressed by name via the SQL API, even though they are exposed in the metadata and can be queried via the REST API. (The GraphQL API can’t address them by name either — its schema only exposes fields for the default granularities.) Using a custom granularity name results in an error:
To query a custom granularity via the SQL API, define a proxy dimension that references it. It is exposed as a regular time dimension and can be selected directly.

Conditional expressions

Learn more in the relevant section of the PostgreSQL documentation.

General-purpose aggregate functions

Learn more in the relevant section of the PostgreSQL documentation.
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.
PERCENTILE_CONT pushdown is only available on data sources whose SQL dialect supports it. It is not supported with Amazon Athena, BigQuery, ClickHouse, Microsoft SQL Server, MySQL, Presto, or Trino.APPROX_PERCENTILE_CONT and APPROX_MEDIAN pushdown is only available with Amazon Athena, Presto, and Trino. With other data sources, use these functions in the outer query of a post-processing query.APPROX_DISTINCT pushdown is only available with Amazon Athena, Amazon Redshift, Apache Pinot, BigQuery, ClickHouse, CrateDB, Databricks, Dremio, DuckDB, Firebolt, Microsoft SQL Server 2019 or later, Oracle, Presto, Snowflake, Trino, and Vertica. With other data sources, use COUNT(DISTINCT) instead.STRING_AGG pushdown is not supported with Microsoft SQL Server; use it in the outer query of a post-processing query. With Hive, ksqlDB, Oracle, and Vertica, queries with STRING_AGG fail.

Aggregate functions for statistics

Learn more in the relevant section of the PostgreSQL documentation.

Window functions

Learn more in the relevant section of the PostgreSQL documentation.
Window functions run with query pushdown or in query post-processing. In post-processing, a window function reads the result of a regular query, so the row limit can cut off rows. Read more about this edge case. Two kinds are supported:
  • Aggregate functions used as window functions — any supported aggregate function (such as SUM, AVG, COUNT, MIN, or MAX) combined with an OVER (...) clause.
  • Built-in window functions:
NTH_VALUE pushdown is not supported with Microsoft SQL Server. Pushdown of built-in window functions is not supported with MongoDB. NTILE is not supported.

Row and array comparisons

Learn more in the relevant section of the PostgreSQL documentation.

Custom functions

See the XIRR function reference and the XIRR recipe for more details.