Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
This section contains reference documentation for the arrayConcatLong function.
This section contains reference documentation for the arrayConcatFloat function.
This section contains reference documentation for the arrayConcatDouble function.
This section contains reference documentation for the arrayConcatInt function.
Concatenates two arrays of ints.
arrayConcatInt('colName1', 'colName2')
These examples are based on the Hybrid Quick Start.
This section contains reference documentation for the ADD function.
Sum of at least two values
ADD(col1, col2, col3...)
These examples are based on the Batch Quick Start.
This page contains reference documentation for functions in Apache Pinot.
This page contains reference documentation for functions in Apache Pinot.
This section contains reference documentation for the arrayContainsInt function.
Checks if int value exists in array.
arrayContainsInt('colName', valueToFind)
These examples are based on the Hybrid Quick Start.
This section contains reference documentation for the arrayConcatString function.
Concatenates two arrays of strings.
arrayConcatString('colName1', 'colName2')
These examples are based on the Hybrid Quick Start.
This section contains reference documentation for the ARRAYLENGTH function.
This section contains reference documentation for the day function.
This section contains reference documentation for base64 encode and decode functions.
toBase64
returns Base64 encoded string of input binary data (bytes
type).
fromBase64
returns binary data (represented as a Hex string) from Base64-encoded string.
toBase64(bytesCol)
fromBase64(stringCol)
Note that the following query will throw compilation error as string is not a valid input type for toBase64
.
This section contains reference documentation for the dayOfYear function.
This section contains reference documentation for the COUNTMV function.
This section contains reference documentation for the dayOfWeek function.
DivWheelsOffs | concatIds |
---|---|
homeRuns | baseOnBalls | total |
---|---|---|
value |
---|
value |
---|
DivAirportIDs | containsValue |
---|---|
DivTailNums | concatIds |
---|---|
These examples are based on the .
DivTailNums | unique |
---|
These examples are based on the .
DivTailNums | index |
---|
These examples are based on the .
DivAirportIDs | index |
---|
These examples are based on the .
DivAirportIDs | value |
---|
These examples are based on the .
DivAirportIDs | unique |
---|
These examples are based on the .
DivAirportIDs | value |
---|
These examples are based on the .
FlightNum | airports | DivAirportIDs |
---|
These examples are based on the .
length | count(*) |
---|
These examples are based on the .
DivAirportIDs | reversedIds |
---|
These examples are based on the .
FlightNum | reversedAirports | RandomAirports |
---|
These examples are based on the .
DivTailNums | index |
---|
These examples are based on the .
FlightNum | sortedAirports | RandomAirports |
---|
These examples are based on the .
DivWheelsOffs | DivWheelsOns | unionIds |
---|
These examples are based on the .
FlightNum | airports | RandomAirports |
---|
These examples are based on the .
value |
---|
day |
---|
day |
---|
day |
---|
day |
---|
value |
---|
value |
---|
value |
---|
These examples are based on the .
DivTailNums | DivAirports | unionIds |
---|
Encoding scheme follows
For better readability, the following examples converts string hello!
into BYTES using function and converts the decoded BYTES into string using .
encoded |
---|
decoded |
---|
Note that without UTF8 string conversion, returned BYTES will be represented as a Hex string following Pinot's . See the example below.
decoded |
---|
dayOfYear |
---|
dayOfYear |
---|
dayOfYear |
---|
dayOfYear |
---|
value |
---|
value |
---|
These examples are based on the .
DivTailNums |
---|
value |
---|
These examples are based on the .
DivAirportIDs | sortedIds |
---|
These examples are based on the .
value |
---|
value |
---|
dayOfWeek |
---|
dayOfWeek |
---|
dayOfWeek |
---|
dayOfWeek |
---|
1453,1731
1453,1731,1415,1623
1908,1758
1908,1758,1339,2310
1453,1731
1453,1731,1415,1623
1908,1758
1908,1758,1339,2310
26
37
63
12.1
12.1
13891,12892
false
14683,14683
true
12339,12339
false
13487,13930
false
13029,11292
false
N7713A,N7713A
N7713A,N7713A,N7713A,N7713A
N344AA,N344AA
N344AA,N344AA,N344AA,N344AA
N344AA,N344AA
N344AA,N344AA,N344AA,N344AA
N7713A,N7713A
N7713A,N7713A,N7713A,N7713A
N7713A,N7713A | N7713A |
N344AA,N344AA | N344AA |
N344AA,N344AA | N344AA |
N7713A,N7713A | N7713A |
N7713A,N7713A | true |
N344AA,N344AA | false |
N7713A,N7713A | true |
13891,12892 | -1 |
14683,14683 | 0 |
12339,12339 | -1 |
13487,13930 | -1 |
13029,11292 | -1 |
SEA,PSC | PSC |
SEA,PSC,PHX,MSY | PSC,PHX,MSY |
SEA,PSC,PHX,MSY | PSC,PHX,MSY |
SEA,PSC | PSC |
SEA,PSC | PSC |
15016,11066 | 15016,11066 |
10620,14869 | 10620,14869 |
13891,12892 | 13891,12892 |
12264,10397 | 12264,10397 |
11066,12892 | 11066,12892 |
13891,12892 | 13891 |
13198,12892 | 13198 |
11066,12892 | 11066 |
13198,12892 | 13198 |
13891,12892 | 13891 |
1531 | 13891 | 13891,12892 |
19 | 14683 | 14683,14683 |
829 | 12339 | 12339,12339 |
24 | 13198 | 13198,10721 |
548 | 10721 | 10721,12478 |
1 | 5382 |
37 | 267 |
33 | 223 |
17 | 166 |
22 | 160 |
13891,12892 | 12892,13891 |
14683,14683 | 14683,14683 |
12339,12339 | 12339,12339 |
13487,13930 | 13930,13487 |
13029,11292 | 11292,13029 |
1206 | PSC,SEA | SEA,PSC |
5300 | PSC,SEA | SEA,PSC |
3359 | MSY,PHX,PSC,SEA | SEA,PSC,PHX,MSY |
1023 | PHX,PSC,SEA | SEA,PSC,PHX |
963 | MSY,PHX,PSC,SEA | SEA,PSC,PHX,MSY |
N7713A,N7713A | 0 |
N344AA,N344AA | -1 |
N7713A,N7713A | 0 |
3846 | PSC,SEA | SEA,PSC |
3635 | MSY,PHX,PSC,SEA | SEA,PSC,PHX,MSY |
429 | MSY,PHX,PSC,SEA | SEA,PSC,PHX,MSY |
1206 | PSC,SEA | SEA,PSC |
5300 | PSC,SEA | SEA,PSC |
1453,1731 | 1415,1623 | 1453,1731,1415,1623 |
1908,1758 | 1339,2310 | 1908,1758,1339,2310 |
1453,1731 | 1415,1623 | 1453,1731,1415,1623 |
1908,1758 | 1339,2310 | 1908,1758,1339,2310 |
671 | SEA,PSC | SEA,PSC,PHX,MSY |
1767 | SEA,PSC | SEA,PSC,PHX |
2522 | SEA,PSC | SEA,PSC |
424 | SEA,PSC | SEA,PSC,PHX,MSY |
3162 | SEA,PSC | SEA,PSC,PHX,MSY |
18.465753424657535 |
12 |
13 |
12 |
13 |
A |
13 |
-12 |
N7713A,N7713A | IND,IND | N7713A,IND |
N344AA,N344AA | MCI,BOS | N344AA,MCI,BOS |
N7713A,N7713A | IND,IND | N7713A,IND |
N344AA,N344AA | MCI,BOS | N344AA,MCI,BOS |
aGVsbG8h |
hello! |
68656c6c6f21 |
346 |
347 |
346 |
347 |
Apache Pinot |
real-time__analytics |
N7713A,N7713A |
N344AA,N344AA |
N344AA,N344AA |
N7713A,N7713A |
8 |
13891,12892 | 12892,13891 |
14683,14683 | 14683,14683 |
12339,12339 | 12339,12339 |
13198,10721 | 10721,13198 |
10721,12478 | 10721,12478 |
97889 |
65 |
7 |
1 |
7 |
1 |
This section contains reference documentation for the DISTINCTCOUNT function.
Returns the count of distinct row values in a group
DISTINCTCOUNT(colName)
These examples are based on the Batch Quick Start.
This section contains reference documentation for the DISTINCTCOUNTHLLMV function.
Returns an approximate distinct count using HyperLogLog in a group
DISTINCTCOUNTHLLMV(colName)
These examples are based on the Hybrid Quick Start.
This section contains reference documentation for the DISTINCTCOUNTBITMAP function.
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.
DISTINCTCOUNTBITMAP(colName)
These examples are based on the Batch Quick Start.
This section contains reference documentation for the DISTINCTCOUNTRAWHLLMV function.
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.
DISTINCTCOUNTRAWHLLMV(colName, log2m)
These examples are based on the Hybrid Quick Start.
This section contains reference documentation for the DISTINCTCOUNTBITMAPMV function.
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.
DISTINCTCOUNTBITMAPMV(colName)
These examples are based on the Hybrid Quick Start.
This section contains reference documentation for the DISTINCTCOUNTTHETASKETCH function.
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:1.2.0-incubating
to perform distinct counting as well as evaluating set operations.
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.
These examples are based on the Batch Quick Start.
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:
(the yearId 1986
is the only one in common)
By running the following query:
This section contains reference documentation for the DISTINCTCOUNTHLL function.
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.
DISTINCTCOUNTHLL(colName, log2m)
These examples are based on the Batch Quick Start.
This section contains reference documentation for the DISTINCT function.
Returns the distinct row values in a group
DISTINCT(colName)
These examples are based on the Batch Quick Start.
This section contains reference documentation for the DISTINCTCOUNTRAWTHETASKETCH function.
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:1.2.0-incubating
to perform distinct counting as well as evaluating set operations.
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.
These examples are based on the Batch Quick Start.
We can also provide predicates and a post aggregation expression to compute more complicated cardinalities:
This section contains reference documentation for the DISTINCTCOUNTRAWHLL function.
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.
DISTINCTCOUNTRAWHLL(colName, log2m)
These examples are based on the Batch Quick Start.
This section contains reference documentation for the DISTINCTCOUNTMV function.
Returns the count of distinct row values in a group
DISTINCTCOUNTMV(colName)
These examples are based on the Hybrid Quick Start.
The following query returns the documents that have a DivTailNums
with more than one value:
You can count the distinct number of items in these rows by running the following query:
This section contains reference documentation for the DATETIMECONVERT function.
Converts the value from a column that contains an epoch timestamp into another time unit and buckets based on the given time granularity.
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>
.
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:
created_at_timestamp
bucketed to 15 minutes granularity:
created_at_timestamp
to format yyyy-MM-dd
, bucketed to 1 days granularity:
created_at_timestamp
to format yyyy-MM-dd HH:mm
, in timezone Pacific/Kiritimati
:
created_at_timestamp
to format yyyy-MM-dd
, in timezone Pacific/Kiritimati
and bucketed to 1 day granularity:
This section contains reference documentation for the DIV function.
Quotient of two values
DIV(col1, col2)
These examples are based on the Batch Quick Start.
This section contains reference documentation for the HISTOGRAM function.
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]
.
Equal length bins (better performance):
HISTOGRAM(colName, lower, upper, numBins)
Arbitrary increasing bin edges:
HISTOGRAM(colName, ARRAY[binEdge1, binEdge2, binEdge3, ...])
These examples are based on the Batch Quick Start.
10 equal-length bins [0, 20), [20, 30) ... [180, 200]
6 bins (- ∞, 1), [1, 10), [10, 50), [50,100), [100,500), [500, 1000]
This section contains reference documentation for the fromEpochBucket functions.
This section contains reference documentation for the DATETRUNC function.
(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.
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
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:
This section contains reference documentation for the FromDateTime function.
Converts a formatted date-time string to milliseconds, based on the provided Joda-Time pattern.
FromDateTime(dateTimeString, pattern)
This section contains reference documentation for the JSONPATHLONG function.
Extracts the Long value from jsonField
based on 'jsonPath'
, use optional defaultValue
for null or parsing error. This function can only be used in an ingestion transformation function.
JSONPATHLONG(jsonField, 'jsonPath', [defaultValue])
'jsonPath'
` is a literal. Pinot uses single quotes to distinguish them from identifiers.
You can use the Jayway JsonPath Evaluator Tool to test JSON expressions before you import any data.
The usage examples are based on extracting fields from the following JSON document:
This function can be used in the table config to extract the age
property into the age
column, as described below:
This section contains reference documentation for the JSONEXTRACTKEY function.
This section contains reference documentation for the JSONEXTRACTSCALAR function.
Evaluates the 'jsonPath' on jsonField, returns the result as the type 'resultsType', use optional defaultValuefor null or parsing error.
JSONEXTRACTSCALAR(jsonField, 'jsonPath', 'resultsType', [defaultValue])
'jsonPath'
and`` ``
'results_type'
are literals. Pinot uses single quotes to distinguish them from identifiers.
The following examples show how to use the JSONEXTRACTSCALAR
function:
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
yearID |
---|
yearID |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
DivTailNums |
---|
value |
---|
id | created_at_timestamp | timeInMs | convertedTime |
---|---|---|---|
id | created_at_timestamp | timeInMs | convertedTime |
---|---|---|---|
id | created_at_timestamp | timeInMs | convertedTime |
---|---|---|---|
id | created_at_timestamp | timeInMs | convertedTime |
---|---|---|---|
id | created_at_timestamp | timeInMs | convertedTime |
---|---|---|---|
homeRuns | numberOfGames | total |
---|---|---|
value |
---|
value |
---|
histogram |
---|
histogram |
---|
hour |
---|
hour |
---|
epochMillis |
---|
epochMillis |
---|
epochMillis |
---|
epochMillis |
---|
bucket |
---|
bucket |
---|
bucket |
---|
bucket |
---|
bucket |
---|
ts |
---|
ts |
---|
ts |
---|
ts |
---|
epochMillis |
---|
epochMillis |
---|
epochMillis |
---|
epochMillis |
---|
value |
---|
value |
---|
Arguments | Description |
---|---|
Expression | Value |
---|---|
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 .
Arguments | Description |
---|
'jsonPath'
` is a literal. Pinot uses single quotes to distinguish them from identifiers.
You can use the to test JSON expressions before you import any data.
Expression | Value |
---|
This function can be used in the to extract the name
, score
, and second value of homework_grades
into their respective columns , as described below:
Arguments | Description |
---|
The examples in this section are based on the . In particular we'll be querying the row WHERE id = 7044874109
.
id | repo | keys |
---|
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 .
Expression | Value |
---|
This function can be used in the to extract the meta
property into the data
column, as described below:
Arguments | Description |
---|
The examples in this section are based on the . In particular we'll be querying the row WHERE id = 7044874109
:
repo |
---|
id | name |
---|
id | name |
---|
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 .
Arguments | Description |
---|
'jsonPath'
` is a literal. Pinot uses single quotes to distinguish them from identifiers.
You can use the to test JSON expressions before you import any data.
Expression | Value |
---|
This function can be used in the to extract the name
property into the name
column and age
property into the age
column, as described below:
Extracts the String value from jsonField
based on 'jsonPath'
, use optional defaultValue
for null or parsing error. This function can only be used in an .
Arguments | Description |
---|
'jsonPath'
` is a literal. Pinot uses single quotes to distinguish them from identifiers.
You can use the to test JSON expressions before you import any data.
Expression | Value |
---|
This function can be used in the to extract the age
property into the age
column, as described below:
Extracts the Double value from jsonField
based on 'jsonPath'
, use optional defaultValue
for null or parsing error. This function can only be used in an .
Arguments | Description |
---|
'jsonPath'
` is a literal. Pinot uses single quotes to distinguish them from identifiers.
You can use the to test JSON expressions before you import any data.
Expression | Value |
---|
This function can be used in the to extract the age
property into the age
column, as described below:
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 .
Arguments | Description |
---|
'jsonPath'
` is a literal. Pinot uses single quotes to distinguish them from identifiers.
You can use the to test JSON expressions before you import any data.
Expression | Value |
---|
This function can be used in the to extract the name
, score
, and second value of homework_grades
into their respective columns , as described below:
value |
---|
7
149
34
7
148
00000008000000ac00000000000000000000000500000020000000000030000202108000040000010000000300010400000000000000000000000463000000000000000000010001041000200000002000000000000000000a00000000028001000000010800000000010000001008000000804000000000020000040000880000000000000000000000000000000000000000000000800000000800020004000000840000000002000000000000000000001400
0000000100000004000000e4
34
2
149
146
1986
1985
1937
2003
1979
1900
1986
1978
2012
1
158
149
NL
UA
AL
NA
PL
AA
FL
NL
UA
AL
NA
PL
AA
FL
AgMDAAAKzJOVAAAAAACAPwDAATjfLK5fBJQy2rIU1GYLOK5a09G+XQ1UHWt00/NwFTC4EwzexhE3CHBSU+YIUzkM0goIADEeFViAmzCRcx5FeHrMHfGsU/qrFvMP+Q87UYRC7LFzZ0FV3PIfAF1FMFsM+E9XRwZRYoR79VdK7z1jAD/WClziDmb4Cosm3ctidcRl9VxfNTR47OUFqFP4dYQkZwXIEZtEhngdkGfqkQCKZPX85HITAZrwVDpI4TY6paDTZwLQNiemHFCUlEZCKcOMpkXuYypOxjzXi1ES+07IIH7EqrQeKcssHvOUh2gpzIDajYdQ4UTS6IBoXPB6AtbomPBiMalFURDzh+xppzrg5HcUTMW4Iuzgv5Mz/xIm73yOe7seghzwmH+zXUfda/mkaBqU6XQEAQFagTkndhYHHcjLb0OeQg4BGDAHtRIDD8EqsonkilQT6TZq2uM3CRXJQTlaYewzFvHsKivVomgcQRojVnPKBh0d0GgYeF4eIEXtD1bZTw43eVR1Dk6sBj3pjleOW21dRsUCRmyEDGdIfWQVJXouaUnZqaC9gi1oSrG7GT8HO2xXeb32OzfiHVx5s9+5bGpFXoXTu1n7g2Jone8JMyGuam2x7Bt55a1JdtFCFxhZ2Gd7IajHY4lNBH2lDfUoJed4f7kGUEXmlU6BCfwOkJ1CIoWBTQY+NToDhpmmmPY+rVOH5coybBHlH4vpfPBbbQsOjl0YBSC9uEmZ3WubqnV0KZ1p5d7wq/F0p7Wgo8y4JVXAobKCB+hsVckBNIA4XrYMzdWVSWeQsXHSuR+mWmJPftadyrMlfvoy2mVr8R4Dih7k3XNhXZwjBeuNJQA5Dtci6w0uIUczvEL+nY+9CSHEPQhuT//aluJ2De4Fk94cfWgaxqhYyh10TTIWZFmsDxJeOMaPT1BCwVRF6taOjftNbVDC5Fy1BtVzVIIUOGeBcj5VbhHtqowIB1qGEDIJy9ZBXD73iFBN5kVgvicaFGSKHGQqeIVsgOFdcFKITQTuV2d0pkljkPXKUIc68M0KPpU6iZYuaBA4+hGR9nri0tVnbJZOM1Z/fi01ou5YLYCoHTqkImozpJMYXLCqKtTBm2o7sc5oQATXUBC9dqM8xQoGL8OmltUWc1cX35rtD2D2zHL2IncEKMzsN/c6S31W74VTBtcbJfP9rHENp7yO453qYhA7m++jl2MKFzdvtkHqGDUcs9FKisV9Hx+ruhaGsLkdISszkZ3sYykjx3NH6BbbaCZf9jTswuxHKheTbaEDmSgrx7BfK+Z2My4jdMqCrEtKMSuJqEJ22AM5U8MNFVkCPTobkCEdJx0ZQJu+Tk73t1v3nqLUQH4PbFJzcUrr9yZFZ0u+1mzNNQ5o0w+v1dSRLGsXsPyRqGkQchuz/DKyrjJzf9Vb8HY4Ni63XiaXwgJrjq9rgAp6EmWV2xXUOI9CWZa7HsuRWO95m58nIq9K8VCkO+T/rWwrPqZ/tCgEtkshqecNhszQiki0d5Kf26o/YcATx4ZkJ655y4PTVr+kY0Xbb/UwEo2pPd3Hyd4hVz1I5N9TpYaJk2Lok1+7N+3LG+3Lj3KZtd5/+j8RujEmogI=
AwMDAAAKzJMQAAAAAACAP4vpfPBbbQsO5N1zYV2cIwWFgU0GPjU6A4Z4HZBn6pEAyQE0gDhetgyKZPX85HITAQ4BGDAHtRIDEDub76OXYwoxK4moQnbYA9LogGhc8HoCE+k2atrjNwlVbhHtqowIBzd5VHUOTqwG+aRoGpTpdAT6PxG6MSaiAnshqMdjiU0EHEEaI1ZzygY=
AQMDAAA6zJN8QPYIsvHMNQ==
00000008000000ac00000800000084000210000000000020001020220030042002100420002010020210000300008020040180400001300310001863024004220870800004400421040104610220080000020000040000030000800002108420000110400800000106000060000080020000082000218c0002000000020000010200100000018c0006000400022004a0000088000200800000320820021000000221842000000000025088000220080100009420
000000010000000400000106
N7713A,N7713A
N344AA,N344AA
N344AA,N344AA
N7713A,N7713A
2
7044874109
2018-01-01 11:00:00.0
1514804402000
17532
7044874109
2018-01-01 11:00:00.0
1514804402000
1514804400000
7044874109
2018-01-01 11:00:00.0
1514804402000
2018-01-01
7044874109
2018-01-01 11:00:00.0
1514804402000
2018-01-02 01:00
7044874109
2018-01-01 11:00:00.0
1514804402000
2018-01-02 00:00
26
153
0.16993464052287582
2.718281828459045
162754.79141900392
32348,21519,11359,7587,5488,5360,6282,7361,585,0
13520,16506,18375,12403,28591,8494
23
0
1613472303000
1613472300000
1613469600000
1613433600000
1613472303000
3226944606000
1613472000000
1613466000000
1613088000000
1639353600000
1639353600
1639350000
453631
1565136000000
1565190733000
1565190733000
1565190733000
12
-13
jsonField
An Identifier/Expression contains JSON documents.
'jsonPath'
Follows JsonPath Syntax to read values from JSON documents.
JSONPATHLONG(data, '$.age')
24
|
|
|
|
|
|
|
|
7044874109 | {"id":115911530,"name":"LimeVista/Tapes","url":"https://api.github.com/repos/LimeVista/Tapes"} |
|
|
|
{"id":115911530,"name":"LimeVista/Tapes","url":"https://api.github.com/repos/LimeVista/Tapes"} |
7044874109 | LimeVista/Tapes |
7044874109 | dummyValue |
|
|
|
|
|
|
| "Pete" |
|
|
|
|
|
|
|
|
5 |
| An Identifier/Expression contains JSON documents. |
|
| An Identifier/Expression contains JSON documents. |
|
| An Identifier/Expression contains JSON documents. |
|
| One of the Pinot supported data types:
|
| An Identifier/Expression contains JSON documents. |
|
| An Identifier/Expression contains JSON documents. |
|
| An Identifier/Expression contains JSON documents. |
|
| An Identifier/Expression contains JSON documents. |
|
This section contains reference documentation for the max function.
Get the maximum value in a group
MAX(colName)
These examples are based on the Batch Quick Start.
This section contains reference documentation for the MAXMV function.
Get the maximum value in a group
MAXMV(colName)
These examples are based on the Hybrid Quick Start.
This section contains reference documentation for the min function.
Get the minimum value in a group
MIN(colName)
These examples are based on the Batch Quick Start.
This section contains reference documentation for the MINMAXRANGEMV function.
Returns the max - min value in a group
MINMAXRANGEMV(colName)
These examples are based on the Hybrid Quick Start.
This section contains reference documentation for the MD5 function.
Return MD5 digest of binary column(bytes
type) as hex string
MD5(bytesCol)
These examples are based on the Real time Quick Start.
The row returned will be different if you run this example as the data is ingested in real-time.
This section contains reference documentation for the mode function.
Get the most frequent value in a group. When multiple modes are present it gives the minimum of all the modes. This behavior can be overridden to get the maximum or the average mode.
MODE(colName, [reducerType])
These examples are based on the Batch Quick Start.
This section contains reference documentation for the MULT function.
Product of at least two values
MULT(col1, col2, col3...)
These examples are based on the Batch Quick Start.
This section contains reference documentation for the MINMV function.
Get the minimum value in a group
MINMV(colName)
These examples are based on the Hybrid Quick Start.
This section contains reference documentation for the minmaxrange function.
Returns the max
- min
value in a group
MINMAXRANGE(colName)
These examples are based on the Batch Quick Start.
This section contains reference documentation for the PERCENTILETDigest function.
Returns the Nth percentile of the group using T-digest algorithm.
PERCENTILETDigest(colName, percentile)
These examples are based on the Batch Quick Start.
This section contains reference documentation for the percentile function.
Returns the max
- min
value in a group
percentile(colName, percentile)
These examples are based on the Batch Quick Start.
This section contains reference documentation for the percentileest function.
Returns the Nth percentile of the group using Quantile Digest algorithm.
percentileest(colName, percentile)
These examples are based on the Batch Quick Start.
This section contains reference documentation for the PERCENTILETDIGESTMV function.
Returns the Nth percentile of the group using T-digest algorithm.
PERCENTILETDIGESTMV(colName, N)
These examples are based on the Hybrid Quick Start.
This section contains reference documentation for the regexpReplace function
Find and replace a string or regexp pattern with a target string or regexp pattern. If matchStr is not found, inputStr will be returned. By default, all occurrences of match pattern in the input string will be replaced. Default matching mode is case sensitive.
regexpReplace(inputStr, matchRegexp, replaceRegexp)
regexpReplace(inputStr, matchRegexp, replaceRegexp, matchStartPos)
regexpReplace(inputStr, matchRegexp, replaceRegexp, matchStartPos, occurrence)
regexpReplace(inputStr, matchRegexp, replaceRegexp, matchStartPos, occurrence, flag)
inputStr
The input string or the column name on which regexpReplace function should be applied.
matchRegexp
The regular expression or string used to match against the input string or column value.
replaceRegexp
The regular expression or string to replace if a match is found.
matchStartPos
Index of inputStr from where matching should start. Counting starts and 0. Default value is 0 if not specified.
occurrence
Controls which occurence of the matched pattern must be replaced. Counting starts at 0. Default value is -1 if not specified
flag
Single character flag that controls how the regex finds matches in inputStr. If an incorrect flag is specified, the function applies default case sensitive match. Only one flag can be specified. Supported flags are:
i
-> case insensitive match
In the example below, shows a simple string find and replace example where all occurrences of the matched string o
is replaced with string x
.
The example below shows how a regexp pattern containing consecutive digits is found and replaced with a simple string bar
.
The example below shows how a regexp pattern containing consecutive non-digits is found and replaced with a simple string bar
.
The following example demonstrates how replaceStr
can contain backreferences to substrings captured by the matchStr
regular expression. Backreferences are indicated by $n where n can range from 0-9. In the example below, every character in the input is replaced by the character appended with a space.
This example shows how regexpReplace can be used to remove extra whitespaces between words in an input string.
This example shows the power of backreferencing can be used in regexpReplace to format phone numbers.
This example shows how the matchStartPos
parameter can be used. Since the matchStartPos
is set to 4, pattern matching against the inputStr
begins at index 4 there by leading to the string healthy
not being replaced.
This example shows how the occurence
parameter can be used. In the example below, the matchStr regular expression matches against three instances in the input - healthy
, wealthy
and stealthy
. As the occurence is specified to 2, the second occurence (counting from zero) stealthy
is replaced with something
The example below shows the usage of the flag
parameter. Here the case insensitive flag i
is specified.
The examples below show some sample queries using regexpReplace in there WHERE
clause of a query.
This section contains reference documentation for the PERCENTILEMV function.
Returns the Nth percentile of the group where N is a decimal number between 0 and 100 inclusive
PERCENTILEMV(colName, N)
These examples are based on the Hybrid Quick Start.
This section contains reference documentation for the PERCENTILEESTMV function.
Returns the Nth percentile of the group using Quantile Digest algorithm.
PERCENTILEESTMV(colName, N)
These examples are based on the Hybrid Quick Start.
This section contains reference documentation for the round function.
Round the given time value to nearest bucket start value.
round(timeValue, bucketSize)
Round seconds epoch value to the start value of the 30 seconds bucket to which it belongs.
Round milliseconds epoch value to the start value of the 5,000 milliseconds bucket to which it belongs.
Follows to read values from JSON documents.
Follows to read values from JSON documents.
Follows to read values from JSON documents.
Follows to read values from JSON documents.
Follows to read values from JSON documents.
Follows to read values from JSON documents.
Follows to read values from JSON documents.
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
name |
---|
millisecond |
---|
millisecond |
---|
value |
---|
value |
---|
value |
---|
value |
---|
event_id | location | hash |
---|---|---|
notTrimmed | trimmed |
---|---|
minute |
---|
minute |
---|
value |
---|
value |
---|
value |
---|
value |
---|
homeRuns | baseOnBalls | total |
---|---|---|
value |
---|
value |
---|
month |
---|
month |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
quarter |
---|
quarter |
---|
quarter |
---|
now |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
value |
---|
rounded |
---|
rounded |
---|
name |
---|
value |
---|
notTrimmed | trimmed |
---|
0
2.4849066497880004
********Hello, World
73
108
pinot
0
0
1871
2
0
106
282776561
80406178a3d70a3d714041d5c28f5c28f6
92a8b787e81672261aad8afcf9de3aee
" Pinot with spaces "
"Pinot with spaces "
30
0
2008
2010
2008
2012
26
37
962
2
142
9
10
0
3.6571905392487856
46.26787306220119
0
4
46
3
3
4
1639150454255
0
4
46
foo
123
foo
foo bar
bar
defaultValue
10
44
108
fxx
foobar
bar123
f o o
Pinot is blazing fast
1-(123) 456-7898
healthy, something, something and wise
healthy, wealthy, something and wise
something, wealthy, stealthy and wise
10
44
108
10
44
108
bar sheep
1639144260 |
1639144270000 |
toniP |
Hello, World******** |
|
|
value |
---|
value |
---|
Goodbye, World
Hello, World