Ingestion Transformations

Raw source data often needs to undergo some transformations before it is pushed to Pinot.
Transformations include extracting records from nested objects, applying simple transform functions on certain columns, filtering out unwanted columns, as well as more advanced operations like joining between datasets.
A preprocessing job is usually needed to perform these operations. In streaming data sources you might write a Samza job and create an intermediate topic to store the transformed data.
For simple transformations, this can result in inconsistencies in the batch/stream data source and increase maintenance and operator overhead.
To make things easier, Pinot supports transformations that can be applied via the table config.
Transformation Functions
Pinot supports the following functions:
1.Groovy functions
2.Inbuilt functions
A transformation function cannot mix Groovy and inbuilt functions - you can only use one type of function at a time.
Groovy functions
Groovy functions can be defined using the syntax:
1
Groovy({groovy script}, argument1, argument2...argumentN)
Copied!
Any valid Groovy expression can be used.
​⚠
 Disabling Groovy
Allowing execuatable Groovy in ingestion transformation can be a security vulnerability. If you would like to disable Groovy for ingestion, you can set the following controller config.
controller.disable.ingestion.groovy=true
If not set, Groovy for ingestion transformation is enabled by default.
Inbuilt Pinot functions
There are also several inbuilt functions that can be used directly as ingestion transform functions
DateTime functions
These functions enable time transformations.
toEpochXXX
Converts from epoch milliseconds to a higher granularity.
Function name
Description
toEpochSeconds
Converts epoch millis to epoch seconds.
Usage:"toEpochSeconds(millis)"
toEpochMinutes
Converts epoch millis to epoch minutes
Usage: "toEpochMinutes(millis)"
toEpochHours
Converts epoch millis to epoch hours
Usage: "toEpochHours(millis)"
toEpochDays
Converts epoch millis to epoch days
Usage: "toEpochDays(millis)"
toEpochXXXRounded
Converts from epoch milliseconds to another granularity, rounding to the nearest rounding bucket. For example, 1588469352000 (2020-05-01 42:29:12) is 26474489 minutesSinceEpoch. `toEpochMinutesRounded(1588469352000) = 26474480 (2020-05-01 42:20:00)
Function Name
Description
toEpochSecondsRounded
Converts epoch millis to epoch seconds, rounding to nearest rounding bucket"toEpochSecondsRounded(millis, 30)"
toEpochMinutesRounded
Converts epoch millis to epoch seconds, rounding to nearest rounding bucket"toEpochMinutesRounded(millis, 10)"
toEpochHoursRounded
Converts epoch millis to epoch seconds, rounding to nearest rounding bucket"toEpochHoursRounded(millis, 6)"
toEpochDaysRounded
Converts epoch millis to epoch seconds, rounding to nearest rounding bucket"toEpochDaysRounded(millis, 7)"
fromEpochXXX
Converts from an epoch granularity to milliseconds.
Function Name
Description
fromEpochSeconds
Converts from epoch seconds to milliseconds
"fromEpochSeconds(secondsSinceEpoch)"
fromEpochMinutes
Converts from epoch minutes to milliseconds
"fromEpochMinutes(minutesSinceEpoch)"
fromEpochHours
Converts from epoch hours to milliseconds
"fromEpochHours(hoursSinceEpoch)"
fromEpochDays
Converts from epoch days to milliseconds
"fromEpochDays(daysSinceEpoch)"
Simple date format
Converts simple date format strings to milliseconds and vice-a-versa, as per the provided pattern string.
Function name
Description
​ToDateTime​
Converts from milliseconds to a formatted date time string, as per the provided pattern
"toDateTime(millis, 'yyyy-MM-dd')"
​FromDateTime​
Converts a formatted date time string to milliseconds, as per the provided pattern
"fromDateTime(dateTimeStr, 'EEE MMM dd HH:mm:ss ZZZ yyyy')"
Note
Letters that are not part of Simple Date Time legend (https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html) need to be escaped. For example:
"transformFunction": "fromDateTime(dateTimeStr, 'yyyy-MM-dd''T''HH:mm:ss')"
JSON functions
Function name
Description
json_format
Converts a JSON/AVRO complex object to a string. This json map can then be queried using jsonExtractScalar function.
"json_format(jsonMapField)"
Types of transformation
Filtering
Records can be filtered as they are being ingested. A filter function can be specified in the filterConfigs in the ingestionConfigs of the table config.
1
"tableConfig": {
2
    "tableName": ...,
3
    "tableType": ...,
4
    "ingestionConfig": {
5
        "filterConfig": {
6
            "filterFunction": "<expression>"
7
        }
8
    }
9
}
Copied!
If the expression evaluates to true, the record will be filtered out. The expressions can use any of the transform functions described in the previous section.
Consider a table that has a column timestamp. If you want to filter out records that are older than timestamp 1589007600000, you could apply the following function:
1
"ingestionConfig": {
2
    "filterConfig": {
3
        "filterFunction": "Groovy({timestamp < 1589007600000}, timestamp)"
4
    }
5
}
Copied!
Consider a table that has a string column campaign and a multi-value column double column prices. If you want to filter out records where campaign = 'X' or 'Y' and sum of all elements in prices is less than 100, you could apply the following function:
1
"ingestionConfig": {
2
    "filterConfig": {
3
        "filterFunction": "Groovy({(campaign == \"X\" || campaign == \"Y\") && prices.sum() < 100}, prices, campaign)"
4
    }
5
}
Copied!
Column Transformation
Transform functions can be defined on columns in the ingestion config of the table config.
1
{ "tableConfig": {
2
    "tableName": ...,
3
    "tableType": ...,
4
    "ingestionConfig": {
5
        "transformConfigs": [{
6
          "columnName": "fieldName",
7
          "transformFunction": "<expression>"
8
        }
9
    ],
10
}
Copied!
For example, imagine that our source data contains the prices and timestamp fields. We want to extract the maximum price and store that in the maxPrices field and convert the timestamp into the number of hours since the epoch and store it in the hoursSinceEpoch field. You can do this by applying the following transformation:
pinot-table-offline.json
1
{
2
"tableName": "myTable",
3
...
4
"ingestionConfig": {
5
    "transformConfigs": [{
6
      "columnName": "maxPrice",
7
      "transformFunction": "Groovy({prices.max()}, prices)" // groovy function
8
    },
9
    {
10
      "columnName": "hoursSinceEpoch",
11
      "transformFunction": "toEpochHours(timestamp)" // inbuilt function
12
    }]
13
  }
14
}
Copied!
Below are some examples of commonly used functions.
String concatenation
Concat firstName and lasName to get fullName
1
"ingestionConfig": {
2
    "transformConfigs": [{
3
      "columnName": "fullName",
4
      "transformFunction": "Groovy({firstName+' '+lastName}, firstName, lastName)" 
5
    }]
6
}
Copied!
Find an element in an array
Find max value in array bids
1
"ingestionConfig": {
2
    "transformConfigs": [{
3
      "columnName": "maxBid",
4
      "transformFunction": "Groovy({bids.max{ it.toBigDecimal() }}, bids)" 
5
    }]
6
}
Copied!
Time transformation
Convert timestamp from MILLISECONDS to HOURS
1
"ingestionConfig": {
2
    "transformConfigs": [{
3
      "columnName": "hoursSinceEpoch",
4
      "transformFunction": "Groovy({timestamp/(1000*60*60)}, timestamp)" 
5
    }]
6
}
Copied!
Column name change
Change name of the column from user_id to userId
1
"ingestionConfig": {
2
    "transformConfigs": [{
3
      "columnName": "userId",
4
      "transformFunction": "Groovy({user_id}, user_id)" 
5
    }]
6
}
Copied!
Extract value from a column containing space
Pinot doesn't support columns that have spaces, so if a source data column has a space, we'll need to store that value in a column with a supported name. To extract the value from first Name into the column firstName, run the following:
1
"ingestionConfig": {
2
    "transformConfigs": [{
3
      "columnName": "firstName",
4
      "transformFunction": "\"first Name \"" 
5
    }]
6
}
Copied!
Ternary operation
If eventType is IMPRESSION set impression to 1. Similar for CLICK.
1
"ingestionConfig": {
2
    "transformConfigs": [{
3
      "columnName": "impressions",
4
      "transformFunction": "Groovy({eventType == 'IMPRESSION' ? 1: 0}, eventType)" 
5
    },
6
    {
7
      "columnName": "clicks",
8
      "transformFunction": "Groovy({eventType == 'CLICK' ? 1: 0}, eventType)" 
9
    }]
10
}
Copied!
AVRO Map
Store an AVRO Map in Pinot as two multi-value columns. Sort the keys, to maintain the mapping.
1) The keys of the map as map_keys
2) The values of the map as map_values
1
"ingestionConfig": {
2
    "transformConfigs": [{
3
      "columnName": "map2_keys",
4
      "transformFunction": "Groovy({map2.sort()*.key}, map2)" 
5
    },
6
    {
7
      "columnName": "map2_values",
8
      "transformFunction": "Groovy({map2.sort()*.value}, map2)" 
9
    }]
10
}
Copied!
Chaining transformations
Transformations can be chained. This means that you can use a field created by a transformation in another transformation function.
For example, we might have the following JSON document in the data field of our source data:
1
{
2
  "userId": "12345678__foo__othertext"
3
}
Copied!
We can apply one transformation to extract the userId and then another one to pull out the numerical part of the identifier:
1
"ingestionConfig": {
2
    "transformConfigs": [
3
      {
4
        "columnName": "userOid",
5
        "transformFunction": "jsonPathString(data, '$.userId')"
6
      },
7
      {
8
        "columnName": "userId",
9
        "transformFunction": "Groovy({Long.valueOf(userOid.substring(0, 8))}, userOid)"
10
      }
11
   ]
12
}
Copied!
Flattening
There are 2 kinds of flattening:
One record into many
This is not natively supported as of yet. You can write a custom Decoder/RecordReader if you want to use this. Once the Decoder generates the multiple GenericRows from the provided input record, a List<GenericRow> should be set into the destination GenericRow, with the key $MULTIPLE_RECORDS_KEY$. The segment generation drivers will treat this as a special case and handle the multiple records case.
Extract attributes from complex objects
Feature TBD
Data Ingestion Overview
Null Value Support
Last modified 1mo ago
Copy link
Contents
Transformation Functions
Groovy functions
Inbuilt Pinot functions
Types of transformation
Filtering
Column Transformation
Flattening