Pivot SQL is an alpha-status feature that should be considered experimental and subject to change or removal at any time. Alpha features are provided "as is," and are not subject to Imply's SLAs.
Measures and Managing data cubes describe how to use Plywood to create custom filters and measures in Pivot data cubes. While Plywood is a powerful and flexible framework for defining data interactions, it can present a learning curve to those who are not familiar with it.
Imply 3.4 introduces Pivot SQL as an alternative to Plywood, giving users a well known, standards-based syntax. With Pivot SQL, users can express dimensions and measures in Pivot using SQL expressions.
Any SQL function supported in Druid SQL is available for use in Pivot SQL expressions.
Enabling Pivot SQL
By default, the Pivot SQL feature is disabled. You can enable Pivot SQL from the Advanced tab in Pivot settings by enabling experimental features.
Working with Pivot SQL
Data cubes must be configured as SQL data cubes to use Pivot SQL features.
Creating SQL data cubes
With experimental features enabled, when creating data cubes, Pivot users can choose to make the data cube a SQL data cube:
SQL data cubes offer auto-fill and introspection from within SQL expression composition fields. As a fallback, Plywood expressions are available in SQL data cubes as well.
Converting existing data cubes to SQL
In certain circumstances, you may wish to convert an existing data cube to a SQL data cube. You can do so from the data cube's General settings.
Before converting data cubes to SQL, note that:
- Data cubes that use advanced options like
altDataSourcecannot be converted
- Dimensions and measures that can’t be converted to SQL will remain in Plywood format
- This operation can’t be reversed and may have unexpected consequences. Imply recommends first duplicating the data cube before converting it to SQL.
Composing SQL expressions
Once a data cube is enabled for Pivot SQL, you can use SQL expressions to define custom dimension and measure formulas, such as the following:
The syntax for querying columns follows the
t.”columnName” format. Inline help in the data cube UI provides troubleshooting information in the event of syntax errors.
See Druid SQL for a complete list of supported SQL functions.
Note the following:
- Pivot SQL supports subset filters, filter tokens, PII masks
- Subset filters can be expressed as SQL; SQL support for filter tokens will be available in the future
- Data cube APIs support both SQL and Plywood on SQL data cubes; SQL data cubes can be created using the property: queryMode: “sql”
- All existing security constraints on user data access applies to SQL queries as well.
Also see the following limitations.
The following limitations apply to the use of SQL in Pivot data cubes:
- JOINs are not currently supported
- Subqueries are not currently supported
altDataSource(also known as native Druid UNION queries) are not currently supported
- Support for lookups is provided only via the LOOKUP Druid SQL function
MilliSecondsInIntervalmagic constant for rate calculations is not currently supported
- Resplit measures (e.g., daily active users) must still be expressed using Plywood
Druid functions requiring extensions, e.g.,
STDDEV, aren't available unless the extension is loaded.