pinotdb version >= 0.3.2 uses the Pinot SQL API (added in Pinot >= 0.3.0) and drops support for PQL API. So this client requires Pinot server version >= 0.3.0 in order to access Pinot.
pinotdb version in 0.2.x uses the Pinot PQL API, which works with pinot version <= 0.3.0, but may miss some new SQL query features added in newer Pinot version.
Usage
Using the DB API to query Pinot Broker directly:
from pinotdb import connect
conn = connect(host='localhost', port=8099, path='/query/sql', scheme='http')
curs = conn.cursor()
curs.execute("""
SELECT place,
CAST(REGEXP_EXTRACT(place, '(.*),', 1) AS FLOAT) AS lat,
CAST(REGEXP_EXTRACT(place, ',(.*)', 1) AS FLOAT) AS lon
FROM places
LIMIT 10
""")
for row in curs:
print(row)
Using SQLAlchemy:
The db engine connection string is formated like this: pinot://:?controller=://:/
Examples with Pinot Quickstart
Clone the Pinot DB repository
Pinot Batch Quickstart
Run below command to start Pinot Batch Quickstart in docker and expose Pinot controller port 9000 and Pinot broker port 8000.
Once pinot batch quickstart is up, you can run the sample code snippet to query Pinot:
Sample Output:
Using parameters:
Pinot Hybrid Quickstart
Run the command below to start Pinot Hybrid Quickstart in docker and expose Pinot controller port 9000 and Pinot broker port 8000.
Below is an example to query against Pinot Quickstart Hybrid:
from pinotdb import connect
conn = connect(host='localhost', port=8000, path='/query/sql', scheme='http')
curs = conn.cursor()
curs.execute("""
SELECT *
FROM baseballStats
WHERE league IN (%(leagues)s)
""", {"leagues": ["AA", "NL"]})
for row in curs:
print(row)
curs.execute("""
SELECT *
FROM baseballStats
WHERE baseOnBalls > (%(score)d)
""", {"score": 0})
for row in curs:
print(row)
