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:
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
SELECTstatements query cubes of different data sources. EachSELECTstatement 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.
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:
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:
ANALYZE:
SHOW
Synopsis:
name, or all runtime parameters
if ALL is specified.
Example:
SET
Synopsis:
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):
DEFAULT to reset the time zone to its default. The LOCAL form
(SET TIME ZONE LOCAL) is not supported.
CREATE TEMPORARY TABLE
Synopsis:
COPY. Temporary tables can be joined
with cubes, and are dropped with DROP TABLE.
Column types are limited to those COPY can load:
booleansmallint,integer,bigintreal,double precision,numeric(up to a precision of 38)varchar,text, and the other variable-width character types (character varying,nvarchar,string), as well asuuid,jsonandjsonb, all of which are stored as textdate,timestamp(anddatetime) without a time zone
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:
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.
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, andSUMcan only be used with measures of compatible types.- If
COUNT(*)is specified, Cube will query the first measure of typecountof 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.
- Aggregate functions used as window functions — any supported aggregate
function (such as
SUM,AVG,COUNT,MIN, orMAX) combined with anOVER (...)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.