Absolute of a value

Signature

ABS(col1)

Usage Examples

select ABS(-12.1) AS value
from ignoreMe

select ABS(12.1) AS value
from ignoreMe

Concatenates two arrays of ints.

Signature

arrayConcatInt('colName1', 'colName2')

Usage Examples

These examples are based on the Hybrid Quick Start.

select DivWheelsOffs, 
       arrayConcatInt(DivWheelsOffs, DivWheelsOns) AS concatIds
from airlineStats 
WHERE arraylength(DivWheelsOffs) >= 2
limit 5

Concatenates two arrays of doubles.

Signature

arrayConcatDouble('colName1', 'colName2')

Usage Examples

This example assumes the multiValueTable columns mvCol1 and mvCol2 are both of type DOUBLE with singleValueField in the table schema set to false.

select mvCol1, 
       arrayConcatDouble(mvCol1, mvCol2) AS concatDoubles
from multiValueTable
WHERE arraylength(mvCol1) >= 2
limit 5

Checks if string value exists in array.

Signature

arrayContainsString('colName', valueToFind)

Usage Examples

These examples are based on the Hybrid Quick Start.

select DivTailNums, 
       arrayContainsString(DivTailNums, 'N7713A') AS index
from airlineStats 
WHERE arraylength(DivTailNums) >= 2
limit 5

Returns unique values in an array of ints.

Signature

arrayDistinctInt('colName')

Usage Examples

These examples are based on the Hybrid Quick Start.

select DivAirportIDs, 
       arrayDistinctInt(DivAirportIDs) AS unique
from airlineStats 
WHERE arraylength(DivAirportIDs) >= 2
limit 5

Concatenates two arrays of floats.

Signature

arrayConcatFloat('colName1', 'colName2')

Usage Examples

This example assumes the multiValueTable columns mvCol1 and mvCol2 are both of type FLOAT with singleValueField in the table schema set to false.

select mvCol1, 
       arrayConcatFloat(mvCol1, mvCol2) AS concatFloats
from multiValueTable
WHERE arraylength(mvCol1) >= 2
limit 5

Returns unique values in an array of strings.

Signature

arrayDistinctString('colName')

Usage Examples

These examples are based on the Hybrid Quick Start.

select DivTailNums, 
       arrayDistinctString(DivTailNums) AS unique
from airlineStats 
WHERE arraylength(DivTailNums) >= 2
limit 5

Sum of at least two values

Signature

ADD(col1, col2, col3...)

Usage Examples

These examples are based on the Batch Quick Start.

select homeRuns, baseOnBalls, ADD(homeRuns, baseOnBalls) AS total
from baseballStats 
WHERE teamID = 'ML1' 
AND yearID = 1956 
AND playerName = 'Henry Louis'

Concatenates two arrays of longs.

Signature

arrayConcatLong('colName1', 'colName2')

Usage Examples

This example assumes the multiValueTable columns mvCol1 and mvCol2 are both of type LONG with singleValueField in the table schema set to false.

select mvCol1, 
       arrayConcatLong(mvCol1, mvCol2) AS concatLongs
from multiValueTable
WHERE arraylength(mvCol1) >= 2
limit 5

Removes value from array of ints.

Signature

arrayRemoveInt('colName', value)

Usage Examples

These examples are based on the Hybrid Quick Start.

select DivAirportIDs, 
       arrayRemoveInt(DivAirportIDs, 12892) AS value
from airlineStats 
WHERE arraylength(DivAirportIDs) >= 2
AND arrayContainsInt(DivAirportIDs, 12892) = 1
limit 5

Removes value from array of strings.

Signature

arrayRemoveString('colName', value)

Usage Examples

These examples are based on the Hybrid Quick Start.

select RandomAirports, 
       arrayRemoveString(RandomAirports, 'SEA') AS value
from airlineStats 
WHERE arraylength(RandomAirports) BETWEEN 2 AND 4
limit 5

Finds the last index of the given value in the array starting at the given index.

Signature

arrayIndexOfInt('colName', valueToFind)

Usage Examples

These examples are based on the Hybrid Quick Start.

select DivAirportIDs, 
       arrayIndexOfInt(DivAirportIDs, 14683) AS index
from airlineStats 
WHERE arraylength(DivAirportIDs) >= 2
limit 5

Reverses array of strings.

Signature

arrayReverseString('colName')

Usage Examples

These examples are based on the Hybrid Quick Start.

select FlightNum, 
       arrayReverseString(RandomAirports) AS reversedAirports, 
       RandomAirports
from airlineStats 
WHERE arraylength(RandomAirports) BETWEEN 2 AND 4
limit 5

Concatenates two arrays of strings.

Signature

arrayConcatString('colName1', 'colName2')

Usage Examples

These examples are based on the Hybrid Quick Start.

select DivTailNums, 
       arrayConcatString(DivTailNums, DivTailNums) AS concatIds
from airlineStats 
WHERE arraylength(DivTailNums) >= 2
limit 5

Finds the last index of the given value in the array starting at the given index.

Signature

arrayIndexOfString('colName', valueToFind)

Usage Examples

These examples are based on the Hybrid Quick Start.

select DivTailNums, 
       arrayIndexOfString(DivTailNums, 'N7713A') AS index
from airlineStats 
WHERE arraylength(DivTailNums) >= 2
limit 5

Sorts array of strings.

Signature

arraySortString('colName')

Usage Examples

These examples are based on the Hybrid Quick Start.

select FlightNum, 
       arraySortString(RandomAirports) AS sortedAirports, 
       RandomAirports
from airlineStats 
WHERE arraylength(RandomAirports) BETWEEN 2 AND 4
limit 5

Checks if int value exists in array.

Signature

arrayContainsInt('colName', valueToFind)

Usage Examples

These examples are based on the Hybrid Quick Start.

select DivAirportIDs, 
       arrayContainsInt(DivAirportIDs, 14683) AS containsValue
from airlineStats 
WHERE arraylength(DivAirportIDs) >= 2
limit 5

Reverses array of ints.

Signature

arrayReverseInt('colName')

Usage Examples

These examples are based on the Hybrid Quick Start.

select DivAirportIDs, 
       arrayReverseInt(DivAirportIDs) AS reversedIds
from airlineStats 
WHERE arraylength(DivAirportIDs) >= 2
limit 5

Returns the length of a multi-value column

Signature

ARRAYLENGTH('colName')

Usage Examples

These examples are based on the Hybrid Quick Start.

select ARRAYLENGTH(RandomAirports) AS length, count(*) 
from airlineStats 
GROUP BY length
ORDER BY count(*) DESC
LIMIT 5

Create a union of two arrays of ints.

Signature

arrayUnionInt('colName1', 'colName2')

Usage Examples

These examples are based on the Hybrid Quick Start.

select DivWheelsOffs, 
       DivWheelsOns,
       arrayUnionInt(DivWheelsOffs, DivWheelsOns) AS unionIds
from airlineStats 
WHERE arraylength(DivWheelsOffs) >= 2
limit 5

Sorts array of ints.

Signature

arraySortInt('colName')

Usage Examples

These examples are based on the Hybrid Quick Start.

select DivAirportIDs, 
       arraySortInt(DivAirportIDs) AS sortedIds
from airlineStats 
WHERE arraylength(DivAirportIDs) >= 2
limit 5

the Unicode codepoint of the first character of the string

Signature

CODEPOINT(col)

Usage Examples

SELECT CODEPOINT('Apache Pinot') AS value
FROM ignoreMe

Concatenate two input strings using the seperator

Signature

CONCAT(col1, col2, seperator)

Usage Examples

SELECT concat('Apache', 'Pinot', ' ') AS value
FROM ignoreMe

SELECT concat('real-time', 'analytics', '__') AS value
FROM ignoreMe

Get the avg of values in a group

Signature

AVGMV(colName)

Usage Examples

These examples are based on the Hybrid Quick Start.

select AVGMV(DivLongestGTimes) AS value
from airlineStats 
where arraylength(DivLongestGTimes) > 1

Returns the values in the array between the start and end positions.

Signature

arraySliceInt('colName', start, end)

Usage Examples

These examples are based on the Hybrid Quick Start.

select FlightNum, 
       arraySliceInt(DivAirportIDs, 0, 1) AS airports, 
	     DivAirportIDs
from airlineStats 
WHERE arraylength(DivAirportIDs) >= 2
limit 5

Create a union of two arrays of strings.

Signature

arrayUnionString('colName1', 'colName2')

Usage Examples

These examples are based on the Hybrid Quick Start.

select DivTailNums, 
       DivAirports,
       arrayUnionString(DivTailNums, DivAirports) AS unionIds
from airlineStats 
WHERE arraylength(DivTailNums) >= 2
limit 5

Rounded up to the nearest integer.

Signature

CEIL(col1)

Usage Examples

select CEIL(12.1) AS value
from ignoreMe

select CEIL(-12.1) AS value
from ignoreMe

Returns the day of the month from the given epoch millis in UTC or specified timezone. The value ranges from 1 to 31.

Signature

day(tsInMillis)

day(tsInMillis, timeZoneId)

dayOfMonth(tsInMillis)

dayOfMonth(tsInMillis, timeZoneId)

Usage Examples

select day(1639351800000) AS day
FROM ignoreMe

select day(1639351800000, 'CET') AS day
FROM ignoreMe

select dayOfMonth(1639351800000) AS day
FROM ignoreMe

select dayOfMonth(1639351800000, 'CET') AS day
FROM ignoreMe

the character corresponding to the Unicode codepoint

Signature

CHR(codepoint)

Usage Examples

SELECT CHR(65) AS value
FROM ignoreMe

Returns the day of the week from the given epoch millis in UTC timezone. The value ranges from 1(Monday) to 7(Sunday).

Signature

dayOfWeek(tsInMillis)

dayOfWeek(tsInMillis, timeZoneId)

dow(tsInMillis)

dow(tsInMillis, timeZoneId)

Usage Examples

select dayOfWeek(1639351800000) AS dayOfWeek
FROM ignoreMe

select dayOfWeek(1639351800000, 'CET') AS dayOfWeek
FROM ignoreMe

select dow(1639351800000) AS dayOfWeek
FROM ignoreMe

select dow(1639351800000, 'CET') AS dayOfWeek
FROM ignoreMe

Get the count of rows in a group

Signature

COUNT(colName)

Usage Examples

These examples are based on the Batch Quick Start.

select count(*) AS value
from baseballStats 

Returns the distinct row values in a group

Signature

DISTINCT(colName)

Usage Examples

These examples are based on the Batch Quick Start.

select DISTINCT league AS value
from baseballStats 

select DISTINCT(league) AS value
from baseballStats 

Returns the population covariance between of 2 numerical columns.

COVAR_POP(col1, col2) = E[col1 * col2] - E[col1]E[col2]

Signatures

COVAR_POP(col1, col2) -> double

Usage Examples

These examples are based on the Batch Quick Start.

SELECT COVAR_POP(numberOfGames, hits) AS covariance 
FROM baseballStats

Returns the sample covariance between of 2 numerical columns.

COVAR_SAMP(col1, col2) = COVAR_POP(col1, col2) * besselCorrection

Signatures

COVAR_SAMP(col1, col2) -> double

Usage Examples

These examples are based on the Batch Quick Start.

SELECT COVAR_SAMP(numberOfGames, AtBatting) AS covariance 
FROM baseballStats

Returns an approximate distinct count using HyperLogLog. It also takes an optional second argument to configure the log2m for the HyperLogLog. For accurate distinct counting, see DISTINCTCOUNT.

Signature

DISTINCTCOUNTHLL(colName, log2m)

Usage Examples

These examples are based on the Batch Quick Start.

select DISTINCTCOUNTHLL(teamID) AS value
from baseballStats 

select DISTINCTCOUNTHLL(teamID, 12) AS value
from baseballStats 

Returns the day of the year from the given epoch millis in UTC or specified timezone. The value ranges from 1 to 366.

Signature

dayOfYear(tsInMillis)

dayOfYear(tsInMillis, timeZoneId)

doy(tsInMillis)

doy(tsInMillis, timeZoneId)

Usage Examples

select dayOfYear(1639351800000) AS dayOfYear
FROM ignoreMe

select dayOfYear(1639351800000, 'CET') AS dayOfYear
FROM ignoreMe

select doy(1639351800000) AS dayOfYear
FROM ignoreMe

select doy(1639351800000, 'CET') AS dayOfYear
FROM ignoreMe

Returns the count of distinct row values in a group

Signature

DISTINCTCOUNT(colName)

Usage Examples

These examples are based on the Batch Quick Start.

select DISTINCTCOUNT(league) AS value
from baseballStats 

select DISTINCTCOUNT(teamID) AS value
from baseballStats 

Returns an approximate distinct count using HyperLogLog in a group

Signature

DISTINCTCOUNTHLLMV(colName)

Usage Examples

These examples are based on the Hybrid Quick Start.

select DISTINCTCOUNTHLLMV(DivLongestGTimes) AS value
from airlineStats 
where arraylength(DivLongestGTimes) > 1

Returns the count of distinct row values in a group. This function is accurate for INT column, but approximate for other cases where hash codes are used in distinct counting and there may be hash collisions. For accurate distinct counting on all column types, see DISTINCTCOUNT.

Signature

DISTINCTCOUNTBITMAP(colName)

Usage Examples

These examples are based on the Batch Quick Start.

select DISTINCTCOUNTBITMAP(league) AS value
from baseballStats 

select DISTINCTCOUNTBITMAP(teamID) AS value
from baseballStats 

Returns the values in the array between the start and end positions.

Signature

arraySliceString('colName', start, end)

Usage Examples

These examples are based on the Hybrid Quick Start.

select FlightNum, 
       arraySliceString(RandomAirports, 0, 2) AS airports, 
       RandomAirports
from airlineStats 
WHERE arraylength(RandomAirports) BETWEEN 2 AND 4
limit 5

Get the count of rows in a group

Signature

COUNTMV(colName)

Usage Examples

These examples are based on the Hybrid Quick Start.

The following query returns the documents that have a DivTailNums with more than one value:

select DivTailNums
from airlineStats 
where arraylength(DivTailNums) > 1

You can count the number of items in these rows by running the following query:

select COUNTMV(DivTailNums) AS value
from airlineStats 
where arraylength(DivTailNums) > 1

Encoding scheme follows java.util.Base64.Encoder

• toBase64 returns Base64 encoded string of input binary data (bytes type).

• fromBase64 returns binary data (represented as a Hex string) from Base64-encoded string.

Signature

toBase64(bytesCol)

fromBase64(stringCol)

Usage Examples

SELECT toBase64(toUtf8('hello!')) AS encoded
FROM ignoreMe

SELECT fromUtf8(fromBase64('aGVsbG8h')) AS decoded
FROM ignoreMe

SELECT fromBase64('aGVsbG8h') AS decoded
FROM ignoreMe

SELECT toBase64('hello!') AS encoded
FROM ignoreMe

This page contains reference documentation for functions in Apache Pinot.

Returns the count of distinct row values in a group

Signature

DISTINCTCOUNTMV(colName)

Usage Examples

These examples are based on the Hybrid Quick Start.

The following query returns the documents that have a DivTailNums with more than one value:

select DivTailNums
from airlineStats 
where arraylength(DivTailNums) > 1

You can count the distinct number of items in these rows by running the following query:

select DISTINCTCOUNTMV(DivTailNums) AS value
from airlineStats 
where arraylength(DivTailNums) > 1

Returns HLL response serialized as string. The serialized HLL can be converted back into an HLL and then aggregated with other HLLs. A common use case may be to merge HLL responses from different Pinot tables, or to allow aggregation after client-side batching.

Signature

DISTINCTCOUNTRAWHLLMV(colName, log2m)

Usage Examples

These examples are based on the Hybrid Quick Start.

select DISTINCTCOUNTRAWHLLMV(DivAirports) AS value
from airlineStats 
where arraylength(DivAirports) > 1

select DISTINCTCOUNTRAWHLLMV(DivAirports, 1) AS value
from airlineStats 
where arraylength(DivAirports) > 1

Returns HLL response serialized as string. The serialized HLL can be converted back into an HLL and then aggregated with other HLLs. A common use case may be to merge HLL responses from different Pinot tables, or to allow aggregation after client-side batching.

Signature

DISTINCTCOUNTRAWHLL(colName, log2m)

Usage Examples

These examples are based on the Batch Quick Start.

select DISTINCTCOUNTRAWHLL(teamID) AS value
from baseballStats 

select DISTINCTCOUNTRAWHLL(teamID, 1) AS value
from baseballStats 

Returns the count of distinct row values in a group. This function is accurate for INT or dictionary encoded column, but approximate for other cases where hash codes are used in distinct counting and there may be hash collision.

Signature

DISTINCTCOUNTBITMAPMV(colName)

Usage Examples

These examples are based on the Hybrid Quick Start.

select DISTINCTCOUNTBITMAPMV(DivLongestGTimes) AS value
from airlineStats 
where arraylength(DivLongestGTimes) > 1

select DISTINCTCOUNTBITMAPMV(DivTailNums) AS value
from airlineStats 
where arraylength(DivTailNums) > 1

Converts a formatted date-time string to milliseconds, based on the provided Joda-Time pattern.

Signature

FromDateTime(dateTimeString, pattern)

Usage Examples

SELECT FromDateTime('2019-08-07', 'yyyy-MM-dd') AS epochMillis
FROM ignoreMe

SELECT FromDateTime(
    '2019-08-07 3:12:13 PM', 
    'yyyy-MM-dd hh:mm:ss a'
    ) AS epochMillis
FROM ignoreMe

SELECT FromDateTime(
    '2019-08-07T15:12:13', 
    'yyyy-MM-dd''T''HH:mm:ss'
    ) AS epochMillis
FROM ignoreMe

SELECT FromDateTime(
    '2019-08-07T07:12:13-0800', 
    'yyyy-MM-dd''T''HH:mm:ssZ'
    ) AS epochMillis
FROM ignoreMe

Takes 2 arguments of type STRING. The first argument is an ipPrefix, and the second argument is a single ipAddress. This function handles both IPv4 and IPv6 arguments.

Returns a boolean value checking if ipAddress is in the subnet of ipPrefix

Signatures

isSubnetOf(ipPrefix, ipAddress) -> boolean

Usage Examples

Please see the following sample queries where isSubnetOf is used in different parts of the query.

SELECT isSubnetOf('192.168.0.1/24', '192.168.0.225') 
AS result
FROM myTable;
---> returns true

SELECT isSubnetOf('1.2.3.128/26', '1.2.5.1') 
AS result
FROM myTable;
---> returns false

SELECT isSubnetOf('2001:4800:7825:103::/64', '2001:4800:7825:103::2050')
AS result
FROM myTable;
---> returns true

SELECT isSubnetOf('7890:db8:113::8a2e:370:7334/127', '7890:db8:113::8a2e:370:7336') 
AS result
FROM myTable;
---> returns false

SELECT count(*) 
FROM myTable 
WHERE isSubnetOf('192.168.0.1/24', ipAddressCol);

SELECT count(*) 
FROM myTable 
WHERE isSubnetOf('192.168.0.1/24', ipAddressCol) 
OR isSubnetOf(ipPrefixCol, '7890:db8:113::8a2e:370:7336');

SELECT 
    CASE 
        WHEN isSubnetOf('105.25.245.115/27', srcIPAddress) THEN 'case1' 
        WHEN isSubnetOf('105.25.245.115/27', dstIPAddress) THEN 'case2'
        ELSE 'case3' 
    END AS differentFlow
FROM myTable;

Returns the hour of the day from the given epoch millis in UTC or specified timezone. The value ranges from 0 to 23.

Signature

hour(tsInMillis)

hour(tsInMillis, timeZoneId)

Usage Examples

select hour(1639351800000) AS hour
FROM ignoreMe

select hour(1639351800000, 'CET') AS hour
FROM ignoreMe

Quotient of two values

Signature

DIV(col1, col2)

Usage Examples

These examples are based on the Batch Quick Start.

select homeRuns, numberOfGames, DIV(homeRuns, numberOfGames) AS total
from baseballStats 
WHERE teamID = 'ML1' 
AND yearID = 1956 
AND playerName = 'Henry Louis'

(Presto) SQL compatible date truncation, equivalent to the Presto function date_trunc.

Converts the value into a specified output granularity seconds since UTC epoch that is bucketed on a unit in a specified timezone.

Signature

DATETRUNC(unit, timeValue)

DATETRUNC(unit, timeValue, inputTimeUnitStr)

DATETRUNC(unit, timeValue, inputTimeUnitStr, timeZone)

DATETRUNC(unit, timeValue, inputTimeUnitStr, timeZone, outputTimeUnitStr)

unit supports the following values:

• millisecond

• second

• minute

• hour

• day

• week

• month

• quarter

• year

inputTimeUnitStr and outputTimeUnitStr support the following values:

• NANOSECONDS

• MICROSECONDS

• MILLISECONDS

• SECONDS

• MINUTES

• HOURS

• DAYS

Usage Examples

Truncates an epoch in milliseconds at WEEK (where a Week starts at Monday UTC midnight):

select dateTrunc('week', 1639480981746) AS ts
FROM ignoreMe

or

select dateTrunc('week', 1639480981746, 'MILLISECONDS') AS ts
FROM ignoreMe

Truncates an epoch in milliseconds at WEEK (where a Week starts at Monday UTC midnight) in the UTC time zone, returning a result in epoch in seconds in UTC timezone:

select dateTrunc(
  'week', 
  1639480981746, 
  'MILLISECONDS', 
  'UTC', 
  'SECONDS'
) AS ts
FROM ignoreMe

Truncates an epoch in milliseconds at WEEK (where a Week starts at Monday UTC midnight) in the CET time zone, returning a result in epoch in seconds in UTC timezone:

select dateTrunc(
  'week', 
  1639480981746, 
  'MILLISECONDS', 
  'CET', 
  'SECONDS'
) AS ts
FROM ignoreMe

Truncates an epoch in milliseconds at QUARTER in the Los Angeles time zone (where a Quarter begins on Jan 1st, April 1st, July 1st, October 1st in Los Angeles timezone), returning a result in hours since UTC epoch:

select dateTrunc(
  'quarter', 
  1639480981746, 
  'MILLISECONDS', 
  'America/Los_Angeles', 
  'HOURS'
) AS ts
FROM ignoreMe

Returns the count of data points that fall within each bin as a vector. The bins are left-inclusive and right-exclusive, i.e. [a, b), except for the last one which is inclusive on both sides [a, b].

Signatures

1. Equal length bins (better performance):

HISTOGRAM(colName, lower, upper, numBins)

1. Arbitrary increasing bin edges:

HISTOGRAM(colName, ARRAY[binEdge1, binEdge2, binEdge3, ...])

Usage Examples

These examples are based on the Batch Quick Start.

1. 10 equal-length bins [0, 20), [20, 30) ... [180, 200]

SELECT HISTOGRAM(numberOfGames, 0, 200, 10) AS histogram
FROM baseballStats 

1. 6 bins (- ∞, 1), [1, 10), [10, 50), [50,100), [100,500), [500, 1000]

select HISTOGRAM(AtBatting, Array['-Infinity', 1, 10, 50, 100, 500, 1000]) AS histogram
from baseballStats

Extracts the object value from jsonField based on 'jsonPath', the result type is inferred based on JSON value. This function can only be used in an ingestion transformation function.

Signature

JSONFORMAT(object)

Usage Examples

The usage examples are based on extracting fields from the following JSON document:

{"timestamp": "2019-10-09 21:25:25", "meta": {"age": 12}}

This function can be used in the table config to extract the meta property into the data column, as described below:

{
   "tableConfig":{
      "ingestionConfig":{
         "transformConfigs":[
            {
               "columnName":"data",
               "transformFunction":"JSONFORMAT(meta)"
            }
         ]
      }
   }
}

Extracts the object value from jsonField based on 'jsonPath', the result type is inferred based on JSON value. This function can only be used in an ingestion transformation function.

Signature

JSONPATH(jsonField, 'jsonPath')

Usage Examples

The usage examples are based on extracting fields from the following JSON document:

{
  "data": {
    "name": "Pete",
    "age": 24,
    "subjects": [
      {
        "name": "maths",
        "homework_grades": [80, 85, 90, 95, 100],
        "grade": "A",
        "score": 90
      },
      {
        "name": "english",
        "homework_grades": [60, 65, 70, 85, 90],
        "grade": "B",
        "score": 70
      }
    ]
  }
}

This function can be used in the table config to extract the name property into the name column and age property into the age column, as described below:

{
   "tableConfig":{
      "ingestionConfig":{
         "transformConfigs":[
            {
               "columnName":"name",
               "transformFunction":"JSONPATHSTRING(data, '$.name')"
            },
            {
               "columnName":"age",
               "transformFunction":"JSONPATHSTRING(data, '$.age')"
            }
         ]
      }
   }
}

Extracts the Double value from jsonField based on 'jsonPath', use optional defaultValuefor null or parsing error. This function can only be used in an .

Signature

JSONPATHDOUBLE(jsonField, 'jsonPath', [defaultValue])

Usage Examples

The usage examples are based on extracting fields from the following JSON document:

{
  "data": {
    "name": "Pete",
    "age": 24,
    "subjects": [
      {
        "name": "maths",
        "homework_grades": [80, 85, 90, 95, 100],
        "grade": "A",
        "score": 90
      },
      {
        "name": "english",
        "homework_grades": [60, 65, 70, 85, 90],
        "grade": "B",
        "score": 70
      }
    ]
  }
}

{
   "tableConfig":{
      "ingestionConfig":{
         "transformConfigs":[
            {
               "columnName":"age",
               "transformFunction":"JSONPATHDOUBLE(data, '$.age')"
            }
         ]
      }
   }
}

Euler’s number(e) raised to the power of col.

Signature

EXP(col1)

Usage Examples

select EXP(1) AS value
from ignoreMe

select EXP(12) AS value
from ignoreMe

The framework enables set operations over a stream of data, and can also be used for cardinality estimation. Pinot leverages the and its extensions from the library org.apache.datasketches:datasketches-java:1.2.0-incubating to perform distinct counting as well as evaluating set operations.

Signature

DISTINCTCOUNTRAWTHETASKETCH(<thetaSketchColumn>, <thetaSketchParams>, predicate1, predicate2..., postAggregationExpressionToEvaluate) -> HexEncoded

• thetaSketchColumn (required): Name of the column to aggregate on.

• thetaSketchParams (required): Parameters for constructing the intermediate theta-sketches.

  • Currently, the only supported parameter is nominalEntries (defaults to 4096).

• predicates (optional)_: _ These are individual predicates of form lhs <op> rhs which are applied on rows selected by the where clause. During intermediate sketch aggregation, sketches from the thetaSketchColumn that satisfies these predicates are unionized individually. For example, all filtered rows that match country=USA are unionized into a single sketch. Complex predicates that are created by combining (AND/OR) of individual predicates is supported.

• postAggregationExpressionToEvaluate (required): The set operation to perform on the individual intermediate sketches for each of the predicates. Currently supported operations are SET_DIFF, SET_UNION, SET_INTERSECT , where DIFF requires two arguments and the UNION/INTERSECT allow more than two arguments.

Usage Examples

These examples are based on the .

select distinctCountRawThetaSketch(teamID) AS value
from baseballStats 

select distinctCountRawThetaSketch(teamID, 'nominalEntries=10') AS value
from baseballStats

We can also provide predicates and a post aggregation expression to compute more complicated cardinalities:

select distinctCountRawThetaSketch(
  yearID, 
  'nominalEntries=4096', 
  'teamID = ''SFN'' AND numberOfGames=28 AND homeRuns=1',
  'teamID = ''CHN'' AND numberOfGames=28 AND homeRuns=1',
  'SET_INTERSECT($1, $2)'
) AS value
from baseballStats 

The framework enables set operations over a stream of data, and can also be used for cardinality estimation. Pinot leverages the and its extensions from the library org.apache.datasketches:datasketches-java:1.2.0-incubating to perform distinct counting as well as evaluating set operations.

Signature

DistinctCountThetaSketch(<thetaSketchColumn>, <thetaSketchParams>, predicate1, predicate2..., postAggregationExpressionToEvaluate) -> Long

• thetaSketchColumn (required): Name of the column to aggregate on.

• thetaSketchParams (required): Parameters for constructing the intermediate theta-sketches.

  • Currently, the only supported parameter is nominalEntries (defaults to 4096).

• predicates (optional)_: _ These are individual predicates of form lhs <op> rhs which are applied on rows selected by the where clause. During intermediate sketch aggregation, sketches from the thetaSketchColumn that satisfies these predicates are unionized individually. For example, all filtered rows that match country=USA are unionized into a single sketch. Complex predicates that are created by combining (AND/OR) of individual predicates is supported.

• postAggregationExpressionToEvaluate (required): The set operation to perform on the individual intermediate sketches for each of the predicates. Currently supported operations are SET_DIFF, SET_UNION, SET_INTERSECT , where DIFF requires two arguments and the UNION/INTERSECT allow more than two arguments.

Usage Examples

These examples are based on the .

select distinctCountThetaSketch(teamID) AS value
from baseballStats 

select distinctCountThetaSketch(teamID, 'nominalEntries=10') AS value
from baseballStats

We can also provide predicates and a post aggregation expression to compute more complicated cardinalities. For example, we could can find the intersection of the following queries:

select yearID
from baseballStats
where teamID = 'SFN' AND numberOfGames = 28 AND homeRuns = 1

select yearID
from baseballStats
where teamID = 'CHN' AND numberOfGames = 28 AND homeRuns = 1

(the yearId 1986 is the only one in common)

By running the following query:

select distinctCountThetaSketch(
  yearID, 
  'nominalEntries=4096', 
  'teamID = ''SFN'' AND numberOfGames=28 AND homeRuns=1',
  'teamID = ''CHN'' AND numberOfGames=28 AND homeRuns=1',
  'SET_INTERSECT($1, $2)'
) AS value
from baseballStats 

Converts the value from a column that contains an epoch timestamp into another time unit and buckets based on the given time granularity.

Signature

DATETIMECONVERT(columnName, inputFormat, outputFormat, outputGranularity)

inputFormat and outputFormat are defined using the following structure:

<time size>:<time unit>:<time format>:<pattern>

where:

• time size - size of the time unit eg: 1, 10

• time unit - DAYS, HOURS, MINUTES, SECONDS, MILLISECONDS, MICROSECONDS, NANOSECONDS

• time format

  • EPOCH

  • SIMPLE_DATE_FORMAT pattern - defined in case of SIMPLE_DATE_FORMAT e.g. yyyy-MM-dd. A specific timezone can be passed using tz(timezone). Timezone can be long or short string format timezone. e.g. Asia/Kolkata or PDT

granularity is specified in the format <time size>:<time unit>.

Usage Examples

These examples are based on the .

created_at_timestamp from milliseconds since epoch to days since epoch, bucketed to 1 day granularity:

select id, 
       created_at_timestamp, 
       cast(created_at_timestamp AS long) AS timeInMs,
       DATETIMECONVERT(
         created_at_timestamp, 
         '1:MILLISECONDS:EPOCH', 
         '1:DAYS:EPOCH', 
         '1:DAYS'
       ) AS convertedTime
from githubEvents
WHERE id = 7044874134

created_at_timestamp bucketed to 15 minutes granularity:

select id, 
       created_at_timestamp, 
       cast(created_at_timestamp AS long) AS timeInMs,
       DATETIMECONVERT(
         created_at_timestamp, 
         '1:MILLISECONDS:EPOCH', 
         '1:MILLISECONDS:EPOCH', 
         '15:MINUTES'
       ) AS convertedTime
from githubEvents
WHERE id = 7044874134

created_at_timestamp to format yyyy-MM-dd, bucketed to 1 days granularity:

select id, 
       created_at_timestamp, 
       cast(created_at_timestamp AS long) AS timeInMs,
       DATETIMECONVERT(
         created_at_timestamp, 
         '1:MILLISECONDS:EPOCH', 
         '1:DAYS:SIMPLE_DATE_FORMAT:yyyy-MM-dd', 
         '1:DAYS'
       ) AS convertedTime
from githubEvents
WHERE id = 7044874134

created_at_timestamp to format yyyy-MM-dd HH:mm, in timezone Pacific/Kiritimati:

select id, 
       created_at_timestamp, 
       cast(created_at_timestamp AS long) AS timeInMs,
       DATETIMECONVERT(
         created_at_timestamp, 
         '1:MILLISECONDS:EPOCH', 
         '1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd HH:mm tz(Pacific/Kiritimati)', 
         '1:MILLISECONDS'
       ) AS convertedTime
from githubEvents
WHERE id = 7044874134

created_at_timestamp to format yyyy-MM-dd, in timezone Pacific/Kiritimati and bucketed to 1 day granularity:

select id, 
       created_at_timestamp, 
       cast(created_at_timestamp AS long) AS timeInMs,
       DATETIMECONVERT(
         created_at_timestamp, 
         '1:MILLISECONDS:EPOCH', 
         '1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd HH:mm tz(Pacific/Kiritimati)', 
         '1:DAYS'
       ) AS convertedTime
from githubEvents
WHERE id = 7044874134

Rounded down to the nearest integer.

Signature

FLOOR(col1)

Usage Examples

select FLOOR(12.1) AS value
from ignoreMe

select FLOOR(-12.1) AS value
from ignoreMe

Extracts an array from jsonField based on 'jsonPath', the result type is inferred based on JSON value. Returns empty array for null or parsing error. This function can only be used in an ingestion transformation function.

Signature

JSONPATHARRAYDEFAULTEMPTY(jsonField, 'jsonPath')

Usage Examples

The usage examples are based on extracting fields from the following JSON document:

{
  "data": {
    "name": "Pete",
    "age": 24,
    "subjects": [
      {
        "name": "maths",
        "homework_grades": [80, 85, 90, 95, 100],
        "grade": "A",
        "score": 90
      },
      {
        "name": "english",
        "homework_grades": [60, 65, 70, 85, 90],
        "grade": "B",
        "score": 70
      }
    ]
  }
}

This function can be used in the table config to extract the name, score, and second value of homework_grades into their respective columns , as described below:

{
   "tableConfig":{
      "ingestionConfig":{
         "transformConfigs":[
            {
               "columnName":"names",
               "transformFunction":"JSONPATHARRAYDEFAULTEMPTY(data, '$.subjects[*].name')"
            },
            {
               "columnName":"ages",
               "transformFunction":"JSONPATHARRAYDEFAULTEMPTY(data, '$.subjects[*].score')"
            },
            {
               "columnName":"homeworkGrades",
               "transformFunction":"JSONPATHARRAYDEFAULTEMPTY(data, '$.subjects[*].homework_grades[1]')"
            }
         ]
      }
   }
}

Extracts the String value from jsonField based on 'jsonPath', use optional defaultValuefor null or parsing error. This function can only be used in an ingestion transformation function.

Signature

JSONPATHSTRING(jsonField, 'jsonPath', [defaultValue])

Usage Examples

The usage examples are based on extracting fields from the following JSON document:

{
  "data": {
    "name": {"full.name": "Peter", "nick.name": "Pete"},
    "age": 24,
    "subjects": [
      {
        "name": "maths",
        "homework_grades": [80, 85, 90, 95, 100],
        "grade": "A",
        "score": 90
      },
      {
        "name": "english",
        "homework_grades": [60, 65, 70, 85, 90],
        "grade": "B",
        "score": 70
      }
    ]
  }
}

This function can be used in the table config to extract the age property into the age column, as described below:

{
   "tableConfig":{
      "ingestionConfig":{
         "transformConfigs":[
            {
               "columnName":"age",
               "transformFunction":"JSONPATHSTRING(data, '$.age')"
            },
            {
               "columnName":"nickName",
               "transformFunction":"JSONPATHSTRING(data, '$.name[\"nick.name\"]')"
            }
         ]
      }
   }
}

Extracts an array from jsonField based on 'jsonPath', the result type is inferred based on JSON value. This function can only be used in an ingestion transformation function.

Signature

JSONPATHARRAY(jsonField, 'jsonPath')

Usage Examples

The usage examples are based on extracting fields from the following JSON document:

{
  "data": {
    "name": "Pete",
    "age": 24,
    "subjects": [
      {
        "name": "maths",
        "homework_grades": [80, 85, 90, 95, 100],
        "grade": "A",
        "score": 90
      },
      {
        "name": "english",
        "homework_grades": [60, 65, 70, 85, 90],
        "grade": "B",
        "score": 70
      }
    ]
  }
}

This function can be used in the table config to extract the name, score, and second value of homework_grades into their respective columns , as described below:

{
   "tableConfig":{
      "ingestionConfig":{
         "transformConfigs":[
            {
               "columnName":"names",
               "transformFunction":"JSONPATHARRAY(data, '$.subjects[*].name')"
            },
            {
               "columnName":"ages",
               "transformFunction":"JSONPATHARRAY(data, '$.subjects[*].score')"
            },
            {
               "columnName":"homeworkGrades",
               "transformFunction":"JSONPATHARRAY(data, '$.subjects[*].homework_grades[1]')"
            }
         ]
      }
   }
}

Extracts all matched JSON field keys based on 'jsonPath' into a STRING_ARRAY.

Signature

JSONEXTRACTKEY(jsonField, 'jsonPath')

Usage Examples

The examples in this section are based on the Batch JSON Quick Start. In particular we'll be querying the row WHERE id = 7044874109.

select id, repo, JSONEXTRACTKEY(repo, '$.*') AS keys
from githubEvents 
WHERE id = 7044874109

Convert epoch to epoch milliseconds. e.g. 10 seconds since epoch or 5 minutes since Epoch. The following time units are supported:

• SECONDS

• MINUTES

• HOURS

• DAYS

Signature

FromEpoch<TIME_UNIT>Bucket(timeInMillis, bucketSize)

Usage Examples

select FromEpochSecondsBucket(1613472303, 1) AS bucket
FROM ignoreMe