https://pinot.apache.org/ logo
Join Slack
Powered by
I'm trying to query Pinot using the Python 'pinotd...
# general
m
I'm trying to query Pinot using the Python 'pinotdb' library:
Copy code
version: '3.7'

services:
  pinot:
    image: apachepinot/pinot:0.7.1
    command: "QuickStart -type batch"
    container_name: "pinot-quickstart"
    volumes:
      - ./data:/data
    ports:
      - "9000:9000"
      - "8000:8000"
Copy code
from pinotdb import connect

conn = connect(host='localhost', port=9000, path='/query/sql', scheme='http')
curs = conn.cursor()
curs.execute("""
    SELECT * from cases LIMIT 10
""")
for row in curs:
    print(row)
I get this error when I run the query:
Copy code
Traceback (most recent call last):
  File "query.py", line 5, in <module>
    curs.execute("""
  File "/home/markhneedham/.local/share/virtualenvs/pinot-playground-V0PLiJ36/lib/python3.8/site-packages/pinotdb/db.py", line 44, in g
    return f(self, *args, **kwargs)
  File "/home/markhneedham/.local/share/virtualenvs/pinot-playground-V0PLiJ36/lib/python3.8/site-packages/pinotdb/db.py", line 289, in execute
    self.check_sufficient_responded(
  File "/home/markhneedham/.local/share/virtualenvs/pinot-playground-V0PLiJ36/lib/python3.8/site-packages/pinotdb/db.py", line 253, in check_sufficient_responded
    raise exceptions.DatabaseError(
pinotdb.exceptions.DatabaseError: Query


    SELECT * from cases LIMIT 10
 timed out: Out of -1, only -1 responded, while needed was -1
Am I querying on the right port? In the examples port 8009 is used, but I tried that and got a different error!
m
Are you able to query Pinot using the query console? If so, may be some issue in the python script?
Also, note that query console runs on the controller. However, if you are using a client, then you can directly query broler port.
m
query console meaning the web app at localhost:9000? If so yep that works fine
k
try using broker port usually it's 8000
m
if I change to port 8000:
Copy code
Traceback (most recent call last):
  File "query.py", line 5, in <module>
    curs.execute("""
  File "/home/markhneedham/.local/share/virtualenvs/pinot-playground-V0PLiJ36/lib/python3.8/site-packages/pinotdb/db.py", line 44, in g
    return f(self, *args, **kwargs)
  File "/home/markhneedham/.local/share/virtualenvs/pinot-playground-V0PLiJ36/lib/python3.8/site-packages/pinotdb/db.py", line 303, in execute
    raise exceptions.DatabaseError(msg)
pinotdb.exceptions.DatabaseError: {'errorCode': 410, 'message': 'BrokerResourceMissingError'}
if I change path to `/sql`:
Copy code
Traceback (most recent call last):
  File "/home/markhneedham/.local/share/virtualenvs/pinot-playground-V0PLiJ36/lib/python3.8/site-packages/pinotdb/db.py", line 275, in execute
    payload = r.json()
  File "/home/markhneedham/.local/share/virtualenvs/pinot-playground-V0PLiJ36/lib/python3.8/site-packages/requests/models.py", line 900, in json
    return complexjson.loads(self.text, **kwargs)
  File "/usr/lib/python3.8/json/__init__.py", line 357, in loads
    return _default_decoder.decode(s)
  File "/usr/lib/python3.8/json/decoder.py", line 337, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())
  File "/usr/lib/python3.8/json/decoder.py", line 355, in raw_decode
    raise JSONDecodeError("Expecting value", s, err.value) from None
json.decoder.JSONDecodeError: Expecting value: line 1 column 1 (char 0)

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "query.py", line 5, in <module>
    curs.execute("""
  File "/home/markhneedham/.local/share/virtualenvs/pinot-playground-V0PLiJ36/lib/python3.8/site-packages/pinotdb/db.py", line 44, in g
    return f(self, *args, **kwargs)
  File "/home/markhneedham/.local/share/virtualenvs/pinot-playground-V0PLiJ36/lib/python3.8/site-packages/pinotdb/db.py", line 277, in execute
    raise exceptions.DatabaseError(
pinotdb.exceptions.DatabaseError: Error when querying 
    SELECT * from cases LIMIT 10
 from <http://localhost:8000/sql>, raw response is:
m
This means no broker found for the table.
Copy code
<https://docs.pinot.apache.org/users/api/querying-pinot-using-standard-sql#rest-api-on-the-broker>
Lets first try outside of the python client to eliminate it from the equation
m
curl: (56) Recv failure: Connection reset by peer
My Docker Compose script is like this now:
Copy code
version: '3.7'

services:
  pinot:
    image: apachepinot/pinot:0.7.1
    command: "QuickStart -type batch"
    container_name: "pinot-quickstart"
    volumes:
      - ./data:/data
    ports:
      - "9000:9000"
      - "8099:8099"
      - "8000:8000"
Copy code
$ sudo lsof -i :8099
COMMAND      PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
docker-pr 105198 root    4u  IPv4 612824      0t0  TCP *:8099 (LISTEN)
docker-pr 105206 root    4u  IPv6 607186      0t0  TCP *:8099 (LISTEN)
it works on port 9000:
Copy code
from pinotdb import connect

conn = connect(host='localhost', port=9000, path='/sql', scheme='http')
curs = conn.cursor()
curs.execute("""
    SELECT * from baseballStats LIMIT 10
""")
for row in curs:
    print(row)
I had been getting a 
BrokerResourceMissingError
because I was querying a non existent table. It only just occurred to me what that error message meant!
m
👍
m
is that the port you'd expect to be available?
I got it from looking at the request being made by the query console
m
That port is the controller port. You'd want to query the broker port. The console sits on top of controller because it needs to access other things like ZK. However, for query path, you can/should directly hit the broker rest api.
m
I see this line in the docker logs:
Copy code
pinot-quickstart | Print playerName,runs,homeRuns for 10 records from the table and order them by yearID
pinot-quickstart | Query : select playerName, runs, homeRuns from baseballStats order by yearID limit 10
pinot-quickstart | Executing command: PostQuery -brokerProtocol http -brokerHost 192.168.64.2 -brokerPort 8000 -queryType sql -query select playerName, runs, homeRuns from baseballStats order by yearID limit 10
so that actually suggests the broker port is 8000, which does actually work when I query it at 
/query/sql
But now I'm confused by the pinot Dockerfile, which suggests that the broker listens on 8099! https://github.com/apache/incubator-pinot/blob/master/docker/images/pinot/Dockerfile#L80
@User when I run a query it seems to only return 10 rows. What am I doing wrong or is that by design?
m
Use limit
Default is 10
m
oh, got it! Thanks 🙂
👍 1
Open in Slack
PreviousNext
Powered by