Note: Like any alpha feature, Pivot SQL 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 Plywood can be used 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, users can convert existing data cubes to SQL data cubes from the data cube's General settings:

Before converting data cubes to SQL, note that:   

• Data cubes that use advanced options like altDataSource cannot 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, users 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 the Druid SQL documentation for the complete list of supported SQL functions. 

Note the following points: 

• 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. 

Limitations

The following limitation apply to the use of SQL in Pivot data cubes: 

• JOINs are not currently supported
• Subqueries are not currently supported
• altDataSource, aka native Druid UNION queries, are not currently supported 
• Support for lookups is provided only via the LOOKUP Druid SQL function
• MilliSecondsInInterval magic 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.