Pinot uses Calcite SQL Parser to parse queries and uses MYSQL_ANSI dialect. You can see the grammar here.
Pinot does not support Joins or nested Subqueries and we recommend using Presto for queries that span multiple tables. Read Engineering Full SQL support for Pinot at Uber for more info.
No DDL support. Tables can be created via the REST API.
In Pinot SQL:
Double quotes(") are used to force string identifiers, e.g. column name.
Single quotes(') are used to enclose string literals.
Mis-using those might cause unexpected query results:
E.g.
WHERE a='b'
means the predicate on the column a
equals to a string literal value 'b'
WHERE a="b"
means the predicate on the column a
equals to the value of the column b
Use single quotes for literals and double quotes (optional) for identifiers (column names)
If you name the columns as timestamp
, date
, or other reserved keywords, or the column name includes special characters, you need to use double quotes when you refer to them in the query.
//default to limit 10SELECT * FROM myTable​SELECT * FROM myTable LIMIT 100
SELECT COUNT(*), MAX(foo), SUM(bar) FROM myTable
SELECT MIN(foo), MAX(foo), SUM(foo), AVG(foo), bar, baz FROM myTableGROUP BY bar, baz LIMIT 50
SELECT MIN(foo), MAX(foo), SUM(foo), AVG(foo), bar, baz FROM myTableGROUP BY bar, bazORDER BY bar, MAX(foo) DESC LIMIT 50
SELECT COUNT(*) FROM myTableWHERE foo = 'foo'AND bar BETWEEN 1 AND 20OR (baz < 42 AND quux IN ('hello', 'goodbye') AND quuux NOT IN (42, 69))
SELECT COUNT(*) FROM myTableWHERE foo IS NOT NULLAND foo = 'foo'AND bar BETWEEN 1 AND 20OR (baz < 42 AND quux IN ('hello', 'goodbye') AND quuux NOT IN (42, 69))
SELECT * FROM myTableWHERE quux < 5LIMIT 50
SELECT foo, bar FROM myTableWHERE baz > 20ORDER BY bar DESCLIMIT 100
Note: results might not be consistent if column ordered by has same value in multiple rows.
SELECT foo, bar FROM myTableWHERE baz > 20ORDER BY bar DESCLIMIT 50, 100
To count rows where the column airlineName
starts with U
SELECT COUNT(*) FROM myTableWHERE REGEXP_LIKE(airlineName, '^U.*')GROUP BY airlineName LIMIT 10
Pinot supports the CASE-WHEN-ELSE statement.
Example 1:
SELECTCASEWHEN price > 30 THEN 3WHEN price > 20 THEN 2WHEN price > 10 THEN 1ELSE 0END AS price_categoryFROM myTable
Example 2:
SELECTSUM(CASEWHEN price > 30 THEN 30WHEN price > 20 THEN 20WHEN price > 10 THEN 10ELSE 0END) AS total_costFROM myTable
As of now, functions have to be implemented within Pinot. Injecting functions is not allowed yet. The example below demonstrate the use of UDFs. More examples in Transform Function in Aggregation Grouping​
SELECT COUNT(*) FROM myTableGROUP BY DATETIME_CONVERT(timeColumnName, '1:MILLISECONDS:EPOCH', '1:HOURS:EPOCH', '1:HOURS')
Pinot supports queries on BYTES column using HEX string. The query response also uses hex string to represent bytes value.
E.g. the query below fetches all the rows for a given UID.
SELECT * FROM myTableWHERE UID = 'c8b3bce0b378fc5ce8067fc271a34892'
​