Return time as epoch millis before the given period in ISO-8601 duration format (PnDTnHnMn.nS with days considered to be exactly 24 hours).

Examples:

• "PT20.345S" -- parses as "20.345 seconds"

• "PT15M" -- parses as "15 minutes" (where a minute is 60 seconds)

• "PT10H" -- parses as "10 hours" (where an hour is 3600 seconds)

• "P2D" -- parses as "2 days" (where a day is 24 hours or 86400 seconds)

• "P2DT3H4M" -- parses as "2 days, 3 hours and 4 minutes"

• "P-6H3M" -- parses as "-6 hours and +3 minutes"

• "-P6H3M" -- parses as "-6 hours and -3 minutes"

• "-P-6H+3M" -- parses as "+6 hours and -3 minutes"

Signature

ago()

Usage Examples

select ago('P1D') AS oneDayAgo
FROM ignoreMe

This function is typically used in the predicate to filter on timestamps for recent data. e.g. filter data on recent 1 day.

SELECT * 
FROM tableName
WHERE tsInMillis > ago('P1D')

This function scans the given dataset to identify the maximum and minimum values in the specified measuring columns. Once these extreme values (the maxima and minima) are found, the function locates the corresponding entries in the projection column. These entries are associated with the rows where the extreme values were found in the measuring columns. The function then returns these projection column values, providing a way to link the extreme measurements with their corresponding data in another part of the dataset.

Prerequisite

This function has to be used with the following configuration on the broker:

pinot.broker.query.rewriter.class.names: DEFAULT_QUERY_REWRITERS_CLASS_NAMES + ",org.apache.pinot.sql.parsers.rewriter.ExprMinMaxRewriter"
pinot.broker.result.rewriter.class.names: "org.apache.pinot.core.query.utils.rewriter.ParentAggregationResultRewriter"

Signature

EXPR_MIN (projectionCol, measuringCol1, measuringCol2, measuringCol3)

EXPR_MAX (projectionCol, measuringCol1, measuringCol2, measuringCol3)

Usage Examples

Find the user with maximum activity. If there are multiple users, break the tie with their last_activity_date. If still a tie, break with user_id. And project user_id.

SELECT EXPR_MAX(user_id, activity, last_activity_date, user_id)
FROM userEngagmentTable

More useful is that this multiple such aggregation function can be used with GROUP BY

SELECT user_region, EXPR_MAX(user_id, activity, last_activity_date, user_id),
    EXPR_MIN(user_id, user_satisfaction)
FROM userEngagmentTable
GROUP BY user_region

Note:

1. In cases where multiple rows share the same extreme values in the measuring columns, all such rows will be returned by the function.

2. If the goal is to project multiple different columns that correspond to the same set of measuring columns, you can achieve this by invoking the function multiple times, each time specifying a different projection column.

3. This impl does not work with AS clause (e.g. SELECT exprmin(longCol, doubleCol) AS exprmin won't work)

4. Putting exprmin/exprmax column inside order by clause (e.g. SELECT intCol, exprmin(longCol, doubleCol) FROM table GROUP BY intCol ORDER BY exprmin(longCol, doubleCol)) is not supported as semantically ordering multi-column multi-row exprmin/exprmax results doesn't make sense

5. Currently projecting MV bytes column doesn't work for now due to an issue

For more detailed examples, see: https://github.com/apache/pinot/pull/10636

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

This page contains reference documentation for functions in Apache Pinot.

Sum of at least two values

Signature

ADD(col1, col2, col3...)

Usage Examples

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.

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.

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

Concatenates two arrays of strings.

Signature

arrayConcatString('colName1', 'colName2')

Usage Examples

Returns unique values in an array of ints.

Signature

arrayDistinctInt('colName')

Usage Examples

Checks if string value exists in array.

Signature

arrayContainsString('colName', valueToFind)

Usage Examples

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

Signature

arrayIndexOfString('colName', valueToFind)

Usage Examples

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

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

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

Removes value from array of ints.

Signature

arrayRemoveInt('colName', value)

Usage Examples

Reverses array of ints.

Signature

arrayReverseInt('colName')

Usage Examples

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

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

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

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

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

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

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

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

the Unicode codepoint of the first character of the string

Signature

CODEPOINT(col)

Usage Examples

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

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

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

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 

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

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 values depending on boolean expressions. This function can only be used in an ingestion transformation function.

Signature

caseWhen(booleanExpr1, valueIfExpr1True, booleanExpr2, valueIfExpr2True) caseWhen(booleanExpr1, valueIfExpr1True, booleanExpr2, valueIfExpr2True, ... ,valueIfFalse)

Usage Examples

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

{
  "latitude": 1.0
}

This function can be used in the table config to add northernHemisphere column:

{
   "tableConfig":{
      "ingestionConfig":{
         "transformConfigs":[
            {
               "columnName":"northernHemisphereStr",
               "transformFunction":"CASEWHEN(latitude > 0, 'North', 'South')"
            }
         ]
      }
   }
}

the character corresponding to the Unicode codepoint

Signature

CHR(codepoint)

Usage Examples

SELECT CHR(65) AS value
FROM ignoreMe

Rounded up to the nearest integer.

Signature

CEIL(col1)

Usage Examples

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

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

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 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 the count of distinct row values in a group. This function is accurate for an 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

Returns the average of distinct row values in a group

Signature

DISTINCTAVG(colName) or avg(distinct col)

Usage Examples

These examples are based on the Batch Quick Start.

SELECT DISTINCTAVG(runs) AS VALUE
FROM baseballStats

SELECT AVG(DISTINCT AtBatting) AS VALUE
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 average of distinct row values in a group

Signature

DISTINCTAVGMV(colName)

Usage Examples

These examples are based on the Hybrid Quick Start.

SELECT DISTINCTAVGMV(DivLongestGTimes) AS VALUE
FROM airlineStats
WHERE arraylength(DivLongestGTimes) > 1

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

The Theta Sketch framework enables set operations over a stream of data, and can also be used for cardinality estimation. Pinot leverages the Sketch Class and its extensions from the library org.apache.datasketches:datasketches-java:4.2.0 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): Semicolon-separated parameter string for constructing the intermediate theta-sketches.

  • The supported parameters are:

  • nominalEntries: The nominal entries used to create the sketch. (Default 4096)

  • samplingProbability: Sets the upfront uniform sampling probability, p. (Default 1.0)

  • accumulatorThreshold: How many sketches should be kept in memory before merging. (Default 2)

  • 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 Batch Quick Start.

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 

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 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 sum of distinct row values in a group

Signature

DISTINCTSUM(colName) or sum(distinct col)

Usage Examples

The Theta Sketch framework enables set operations over a stream of data, and can also be used for cardinality estimation. Pinot leverages the Sketch Class and its extensions from the library org.apache.datasketches:datasketches-java:4.2.0 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): Semicolon-separated parameter string for constructing the intermediate theta-sketches.

  • The supported parameters are:

  • nominalEntries: The nominal entries used to create the sketch. (Default 4096)

  • samplingProbability: Sets the upfront uniform sampling probability, p. (Default 1.0)

  • accumulatorThreshold: How many sketches should be kept in memory before merging. (Default 2)

• 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 Batch Quick Start.

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 

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'

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 Batch JSON Quick Start.

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

(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):

or

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:

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:

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:

Returns the sum of the distinct row values in a group

Signature

DISTINCTSUMMV(colName)

Usage Examples

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

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

FREQUENTSTRINGSSKETCH is an estimation data-sketch function which can be used to estimate the frequency of an item. It is based on Apache Datasketches library and returns a serialized sketch object which can be merged with other sketches.

Signature

FREQUENTSTRINGSSKETCH(column, maxMapSize=256) -> Base64 encoded sketch object

• column (required): Name of the column to aggregate on. Needs to be a type which can be cast into 'STRING'.

• maxMapSize: This value specifies the maximum physical length of the internal hash map. The maxMapSize must be a power of 2 and the default value is 256.

Usage Example

select FREQUENTSTRINGSSKETCH(AirlineID, 16) from airlineStats

Which can be used, for example in Java as:

byte[] byteArr = Base64.getDecoder().decode(encodedSketch);
ItemsSketch<String> sketch = ItemsSketch.getInstance(Memory.wrap(byteArr), new ArrayOfStringsSerDe());

ItemsSketch.Row[] items = sketch.getFrequentItems(ErrorType.NO_FALSE_NEGATIVES);
for (int i = 0; i < items.length; i++) {
  ItemsSketch.Row item = items[i];
  System.out.printf("Airline: %s, Frequency: %d %n", item.getItem(), item.getEstimate());
}

For more examples on the sketch API, refer to the Datasketches documentation.

Returns the value of dataColumn with the smallest timeColumn value where:

• timeColumn is used to define the time of dataColumn, which can be of type TIMESTAMP, INT, LONG

• dataType specifies the type for dataColumn, which can be BOOLEAN, INT, LONG, FLOAT, DOUBLE, STRING

Signature

FIRSTWITHTIME(dataColumn, timeColumn, 'dataType')

Example

Signature

FromDateTime(dateTimeString, pattern)

Usage Examples