Pivot SQL (alpha)

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:

Add annotation

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.

Add annotation

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, you can use SQL expressions to define custom dimension and measure formulas, such as the following:

Add annotation

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.

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