Python DB-API and SQLAlchemy dialect for Pinot.
Project description
Python DB-API and SQLAlchemy dialect for Pinot
This module allows accessing Pinot via its SQL API.
Usage
Using the DB API:
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:
from sqlalchemy import *
from sqlalchemy.engine import create_engine
from sqlalchemy.schema import *
engine = create_engine('pinot://localhost:8099/query/sql?server=http://localhost:9000/') # uses HTTP by default :(
# engine = create_engine('pinot+http://localhost:8099/query/sql?server=http://localhost:9000/')
# engine = create_engine('pinot+https://localhost:8099/query/sql?server=http://localhost:9000/')
places = Table('places', MetaData(bind=engine), autoload=True)
print(select([func.count('*')], from_obj=places).scalar())
Examples with Pinot Quickstart
Start Pinot Batch Quickstart
docker run --name pinot-quickstart -p 2123:2123 -p 9000:9000 -p 8000:8000 -d apachepinot/pinot:latest QuickStart -type batch
Once pinot-quickstart is up, you can run below sample code snippet to query Pinot:
python3 examples/pinot-quickstart.py
Sample Output:
Sending SQL to Pinot: SELECT * FROM baseballStats LIMIT 5
[0, 11, 0, 0, 0, 0, 0, 0, 0, 0, '"NL"', 11, 11, '"aardsda01"', '"David Allan"', 1, 0, 0, 0, 0, 0, 0, '"SFN"', 0, 2004]
[2, 45, 0, 0, 0, 0, 0, 0, 0, 0, '"NL"', 45, 43, '"aardsda01"', '"David Allan"', 1, 0, 0, 0, 1, 0, 0, '"CHN"', 0, 2006]
[0, 2, 0, 0, 0, 0, 0, 0, 0, 0, '"AL"', 25, 2, '"aardsda01"', '"David Allan"', 1, 0, 0, 0, 0, 0, 0, '"CHA"', 0, 2007]
[1, 5, 0, 0, 0, 0, 0, 0, 0, 0, '"AL"', 47, 5, '"aardsda01"', '"David Allan"', 1, 0, 0, 0, 0, 0, 1, '"BOS"', 0, 2008]
[0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '"AL"', 73, 3, '"aardsda01"', '"David Allan"', 1, 0, 0, 0, 0, 0, 0, '"SEA"', 0, 2009]
Sending SQL to Pinot: SELECT playerName, sum(runs) FROM baseballStats WHERE yearID>=2000 GROUP BY playerName LIMIT 5
['"Scott Michael"', 26.0]
['"Justin Morgan"', 0.0]
['"Jason Andre"', 0.0]
['"Jeffrey Ellis"', 0.0]
['"Maximiliano R."', 16.0]
Sending SQL to Pinot: SELECT playerName,sum(runs) AS sum_runs FROM baseballStats WHERE yearID>=2000 GROUP BY playerName ORDER BY sum_runs DESC LIMIT 5
['"Adrian"', 1820.0]
['"Jose Antonio"', 1692.0]
['"Rafael"', 1565.0]
['"Brian Michael"', 1500.0]
['"Alexander Emmanuel"', 1426.0]
Below is a sql alchemy example:
python3 examples/pinot-quickstart-sqlalchemy.py
Sending Count(*) SQL to Pinot
97889
Sending SQL: "SELECT playerName, sum(runs) AS sum_runs FROM "baseballStats" WHERE yearID>=2000 GROUP BY playerName ORDER BY sum_runs DESC LIMIT 5" to Pinot
[('"Adrian"', 1820.0), ('"Jose Antonio"', 1692.0), ('"Rafael"', 1565.0), ('"Brian Michael"', 1500.0), ('"Alexander Emmanuel"', 1426.0)]
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
pinotdb-0.3.0.tar.gz
(10.8 kB
view details)
Built Distribution
File details
Details for the file pinotdb-0.3.0.tar.gz.
File metadata
- Download URL: pinotdb-0.3.0.tar.gz
- Upload date:
- Size: 10.8 kB
- Tags: Source
- Uploaded using Trusted Publishing? No
- Uploaded via: twine/3.1.1 pkginfo/1.5.0.1 requests/2.23.0 setuptools/42.0.2 requests-toolbelt/0.9.1 tqdm/4.46.0 CPython/3.7.6
File hashes
| Algorithm | Hash digest | |
|---|---|---|
| SHA256 | 198016a923a2ac07c88ada2a360e31e05719a43e540867c3b7b9c062b621dea1 |
|
| MD5 | 3ffa5d5c8656458cecd79e63450c1082 |
|
| BLAKE2b-256 | 2c88ec75b958e5208a3653e0316b3c304d18381dc0d6c9788af4d5cbaead4677 |
Provenance
File details
Details for the file pinotdb-0.3.0-py2.py3-none-any.whl.
File metadata
- Download URL: pinotdb-0.3.0-py2.py3-none-any.whl
- Upload date:
- Size: 10.6 kB
- Tags: Python 2, Python 3
- Uploaded using Trusted Publishing? No
- Uploaded via: twine/3.1.1 pkginfo/1.5.0.1 requests/2.23.0 setuptools/42.0.2 requests-toolbelt/0.9.1 tqdm/4.46.0 CPython/3.7.6
File hashes
| Algorithm | Hash digest | |
|---|---|---|
| SHA256 | 1911b49c64ee0b1556c219a781d89374c2555cc266f4a5c7d1d926a0ba356171 |
|
| MD5 | 56197cf8debdd0996de543731af09314 |
|
| BLAKE2b-256 | 534387a68a669d049426a5e6cfa3b4345a638fc8e9a36c1629958c41deb372ea |
