SQL-based Transformations and JSON Columns in Imply Polaris

Oct 12, 2022
Timmy Freese


There’s no “I” in “Team”, but there is a “T” in “ETL”. An important feature of any database is the ability to transform data during ingestion and query. Imply Polaris supports both. As an example, consider the situation where a company records its Total Revenue in a ‘total_revenue’ column and its Total Expenses in a ‘total_expenses’ column. If an analyst at the company wants to know the company’s Net Profits, they can implement a simple transformation of ‘total_revenue – total_expenses’.

Older versions of Imply provided users with the ability to perform transformations during ingestion using a native ingestion spec and during query using SQL. Now, Imply also enables SQL-based transformations during ingestion time. This much simplified user flow has been carried through to Imply’s DBaaS, Polaris.

To transform data using SQL expressions during ingestion, users can POST an ingestion job using the Polaris API or use the UI to enter an expression such as ‘total_revenue – total_expenses’ while editing the table’s schema. Additionally, when working with a rollup table, you can apply a variety of aggregation functions on measures, including MIN, MAX, SUM, and COUNT.

JSON Columns

In addition to being able to transform your data at ingestion, you can also work with nested data in Polaris. The world is a messy place, and capturing events in a fully structured way is often infeasible. Sometimes data comes in a semi-structured form and users will want to give this data additional structure. Polaris supports nested data with the JSON datatype. Additionally, users can add more structure to this JSON datatype using the JSON_VALUE transformation and other SQL JSON functions, available via API and UI. For more information about nested data in Polaris, see Ingest nested data  in the Polaris documentation.

Enabling real time analytics with transformations and JSON columns in Polaris is one of the many ways in which we are powering the next generation of m

Other blogs you might find interesting

No records found...
Jun 12, 2024

Why I Joined Imply

After reviewing the high-level technical overview video of Apache Druid and learning about how the world's leading companies use Apache Druid, I immediately saw the immense potential in the product. Data is...

Learn More
Jun 12, 2024

AWS IoT Core and Imply: Simplifying IoT Management and Data Analytics

The Internet of Things (IoT) is rapidly expanding, with IoT connections growing by 18% in 2022. AWS IoT Core, used by giants like NASA and Volkswagen, offers robust solutions for managing this surge. Discover...

Learn More
May 13, 2024

Tuning into Success: Streaming Music Done the Imply Way

Learn how a top streaming music service uses Imply to provide visibility into audience engagement and other key metrics.

Learn More

Let us help with your analytics apps

Request a Demo