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=://:/
from sqlalchemy import *
from sqlalchemy.engine import create_engine
from sqlalchemy.schema import *
engine = create_engine('pinot://localhost:8099/query/sql?controller=http://localhost:9000/') # uses HTTP by default :(
# engine = create_engine('pinot+http://localhost:8099/query/sql?controller=http://localhost:9000/')
# engine = create_engine('pinot+https://localhost:8099/query/sql?controller=http://localhost:9000/')
places = Table('places', MetaData(bind=engine), autoload=True)
print(select([func.count('*')], from_obj=places).scalar())
Examples with Pinot Quickstart
Clone the Pinot DB repository
git clone git@github.com:python-pinot-dbapi/pinot-dbapi.git
cd pinot-dbapi
Pinot Batch Quickstart
Run below command to start Pinot Batch Quickstart in docker and expose Pinot controller port 9000 and Pinot broker port 8000.
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)
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.
