Apache Pinot Docs
latest
Search…
⌃K

Querying Pinot

Learn how to query Pinot using SQL

SQL Interface

Pinot provides SQL interface for querying. It uses the Calcite SQL parser to parse queries and uses MYSQL_ANSI dialect. You can see the grammar in the Calcite documentation.

Limitations

  • The latest Pinot multi-stage supports inner join, left-outer, semi-join, and nested queries out of the box. It is optimized for in-memory process and latency.
    • For queries that require a large amount of data shuffling, or require spill-to-disk, or hitting any other limitations of the multi-stage engine, we still recommend using Presto. For more information, see Multi-Stage Query Engine Page.
  • The latest Pinot also supports simple DDL to insert data into a table from file directly. For more info please see the 0.11.0 release note.
    • More DDL supports will be added in the future. But for now, the most common way for data definition is 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.
Note: Defining decimal literals within quotes preserves precision.

Example Queries

Selection

//default to limit 10
SELECT *
FROM myTable
SELECT *
FROM myTable
LIMIT 100
SELECT "date", "timestamp"
FROM myTable

Aggregation

SELECT COUNT(*), MAX(foo), SUM(bar)
FROM myTable

Grouping on Aggregation

SELECT MIN(foo), MAX(foo), SUM(foo), AVG(foo), bar, baz
FROM myTable
GROUP BY bar, baz
LIMIT 50

Ordering on Aggregation

SELECT MIN(foo), MAX(foo), SUM(foo), AVG(foo), bar, baz
FROM myTable
GROUP BY bar, baz
ORDER BY bar, MAX(foo) DESC
LIMIT 50

Filtering

SELECT COUNT(*)
FROM myTable
WHERE foo = 'foo'
AND bar BETWEEN 1 AND 20
OR (baz < 42 AND quux IN ('hello', 'goodbye') AND quuux NOT IN (42, 69))
For performant filtering of ids in a list, see Filtering with IdSet.

Filtering with NULL predicate

SELECT COUNT(*)
FROM myTable
WHERE foo IS NOT NULL
AND foo = 'foo'
AND bar BETWEEN 1 AND 20
OR (baz < 42 AND quux IN ('hello', 'goodbye') AND quuux NOT IN (42, 69))

Selection (Projection)

SELECT *
FROM myTable
WHERE quux < 5
LIMIT 50

Ordering on Selection

SELECT foo, bar
FROM myTable
WHERE baz > 20
ORDER BY bar DESC
LIMIT 100

Pagination on Selection

Results might not be consistent if the order by column has the same value in multiple rows.
SELECT foo, bar
FROM myTable
WHERE baz > 20
ORDER BY bar DESC
LIMIT 50, 100

Wild-card match (in WHERE clause only)

To count rows where the column airlineName starts with U
SELECT COUNT(*)
FROM myTable
WHERE REGEXP_LIKE(airlineName, '^U.*')
GROUP BY airlineName LIMIT 10

Case-When Statement

Pinot supports the CASE-WHEN-ELSE statement.
Example 1:
SELECT
CASE
WHEN price > 30 THEN 3
WHEN price > 20 THEN 2
WHEN price > 10 THEN 1
ELSE 0
END AS price_category
FROM myTable
Example 2:
SELECT
SUM(
CASE
WHEN price > 30 THEN 30
WHEN price > 20 THEN 20
WHEN price > 10 THEN 10
ELSE 0
END) AS total_cost
FROM myTable

UDF

Functions have to be implemented within Pinot. Injecting functions is not yet supported. The example below demonstrate the use of UDFs.
SELECT COUNT(*)
FROM myTable
GROUP BY DATETIMECONVERT(timeColumnName, '1:MILLISECONDS:EPOCH', '1:HOURS:EPOCH', '1:HOURS')

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.
SELECT *
FROM myTable
WHERE UID = 'c8b3bce0b378fc5ce8067fc271a34892'