Querying Pinot

Search…

latest

Querying Pinot

Learn how to query Pinot using SQL

SQL Dialect
Pinot uses the Calcite SQL parser to parse queries and uses MYSQL_ANSI dialect. You can see the grammar in the Calcite documentation.
Limitations
Pinot does not support joins or nested subqueries. We recommend using Presto for queries that span multiple tables. For more information, see Engineering Full SQL support for Pinot at Uber.
There is no DDL support. Tables can be created via the REST API.
Identifier vs Literal
In Pinot SQL:
Double quotes(") are used to force string identifiers, e.g. column names
Single quotes(') are used to enclose string literals. If the string literal also contains a single quote, escape this with a single quote e.g '''Pinot''' to match the string literal 'Pinot'
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
If your column names use reserved keywords (e.g. timestamp or date) or special charactesr, you will need to use double quotes when referring to them in queries.
Example Queries
Selection
1
//default to limit 10
2
SELECT * 
3
FROM myTable 
4
​
5
SELECT * 
6
FROM myTable 
7
LIMIT 100
Copied!
1
SELECT "date", "timestamp"
2
FROM myTable 
Copied!
Aggregation
1
SELECT COUNT(*), MAX(foo), SUM(bar) 
2
FROM myTable
Copied!
Grouping on Aggregation
1
SELECT MIN(foo), MAX(foo), SUM(foo), AVG(foo), bar, baz 
2
FROM myTable
3
GROUP BY bar, baz 
4
LIMIT 50
Copied!
Ordering on Aggregation
1
SELECT MIN(foo), MAX(foo), SUM(foo), AVG(foo), bar, baz 
2
FROM myTable
3
GROUP BY bar, baz 
4
ORDER BY bar, MAX(foo) DESC 
5
LIMIT 50
Copied!
Filtering
1
SELECT COUNT(*) 
2
FROM myTable
3
  WHERE foo = 'foo'
4
  AND bar BETWEEN 1 AND 20
5
  OR (baz < 42 AND quux IN ('hello', 'goodbye') AND quuux NOT IN (42, 69))
Copied!
For performant filtering of ids in a list, see Filtering with IdSet.
Filtering with NULL predicate
1
SELECT COUNT(*) 
2
FROM myTable
3
  WHERE foo IS NOT NULL
4
  AND foo = 'foo'
5
  AND bar BETWEEN 1 AND 20
6
  OR (baz < 42 AND quux IN ('hello', 'goodbye') AND quuux NOT IN (42, 69))
Copied!
Selection (Projection)
1
SELECT * 
2
FROM myTable
3
  WHERE quux < 5
4
  LIMIT 50
Copied!
Ordering on Selection
1
SELECT foo, bar 
2
FROM myTable
3
  WHERE baz > 20
4
  ORDER BY bar DESC
5
  LIMIT 100
Copied!
Pagination on Selection
Results might not be consistent if the order by column has the same value in multiple rows.
1
SELECT foo, bar 
2
FROM myTable
3
  WHERE baz > 20
4
  ORDER BY bar DESC
5
  LIMIT 50, 100
Copied!
Wild-card match (in WHERE clause only)
To count rows where the column airlineName starts with U
1
SELECT COUNT(*) 
2
FROM myTable
3
  WHERE REGEXP_LIKE(airlineName, '^U.*')
4
  GROUP BY airlineName LIMIT 10
Copied!
Case-When Statement
Pinot supports the CASE-WHEN-ELSE statement.
Example 1:
1
SELECT
2
    CASE
3
      WHEN price > 30 THEN 3
4
      WHEN price > 20 THEN 2
5
      WHEN price > 10 THEN 1
6
      ELSE 0
7
    END AS price_category
8
FROM myTable
Copied!
Example 2:
1
SELECT
2
  SUM(
3
    CASE
4
      WHEN price > 30 THEN 30
5
      WHEN price > 20 THEN 20
6
      WHEN price > 10 THEN 10
7
      ELSE 0
8
    END) AS total_cost
9
FROM myTable
Copied!
UDF
Functions have to be implemented within Pinot. Injecting functions is not yet supported. The example below demonstrate the use of UDFs.
1
SELECT COUNT(*)
2
FROM myTable
3
GROUP BY DATETIMECONVERT(timeColumnName, '1:MILLISECONDS:EPOCH', '1:HOURS:EPOCH', '1:HOURS')
Copied!
For more examples, see Transform Function in Aggregation Grouping.
BYTES column
Pinot supports queries on BYTES column using HEX string. The query response also uses HEX string to represent bytes values.
e.g. the query below fetches all the rows for a given UID.
1
SELECT * 
2
FROM myTable
3
WHERE UID = 'c8b3bce0b378fc5ce8067fc271a34892'
Copied!