https://pinot.apache.org/ logo
Join Slack
Powered by
hey y’all! <@U03BVKN0L06> and I were having some t...
# troubleshooting
s
hey y’all! @User and I were having some trouble getting Pinot connected to Superset and would love any help here. We tried both the URI’s in the superset docs (https://superset.apache.org/docs/databases/pinot) and the Pinot docs (https://docs.pinot.apache.org/integrations/superset) and neither seemed to work for us. I specifically tested with the pinot quickstart docker image and kept getting generic SQLalchemy errors, which makes it hard to debug! Hugh’s trying to test this PR (https://github.com/apache/superset/pull/19724) in the Superset repo, that improves the Pinot capabilities in Superset ⚡ cc @User @User @User
h
I’m currently running pinot locally with docker on 9000
and this the connection string that i’ve created: 
<pinot://localhost:8000/query/sql?controller=http://localhost:9000/>
when i try connect just using the 
pinotdb
i’ve been getting this error:
Copy code
~/src/superset/venv/lib/python3.8/site-packages/pinotdb/db.py in check_sufficient_responded(self, query, queried, responded)
    261             needed = fraction
    262         if responded < 0 or responded < needed:
--> 263             raise exceptions.DatabaseError(
    264                 f"Query\n\n{query} timed out: Out of {queried}, only"
    265                 f" {responded} responded, while needed was {needed}"

DatabaseError: Query


    select * from baseballStats
 timed out: Out of -1, only -1 responded, while needed was -1
let me know if anyone can help me debug so i can verify my changes work as intended
k
Try 
pinot+<http://pinot:8000/query?server=http%3A%2F%2Fpinot%3A9000%2F>
for the connection string
h
same error
Copy code
2-04-19 10:21:16,806:INFO:pinotdb.sqlalchemy:Updated pinot dialect args from {'dbapi': <module 'pinotdb' from '/Users/hugh/src/superset/venv/lib/python3.8/site-packages/pinotdb/__init__.py'>, 'debug': False, 'verify_ssl': True}: None and False
2022-04-19 10:21:16,807:INFO:pinotdb.sqlalchemy:Updated pinot dialect args from {'host': 'pinot', 'port': 8000, 'path': 'query', 'scheme': 'http', 'verify_ssl': True, 'username': None, 'password': None, 'debug': False}: <http://pinot:9000/> and False
2022-04-19 10:21:16,807:DEBUG:superset.stats_logger:[stats_logger] (incr) test_connection_attempt
2022-04-19 10:21:16,837:DEBUG:urllib3.connectionpool:Starting new HTTP connection (1): pinot:8000
2022-04-19 10:21:16,844:DEBUG:superset.stats_logger:[stats_logger] (incr) test_connection_error.DBAPIError
2022-04-19 10:21:16,846:DEBUG:superset.stats_logger:[stats_logger] (incr) DatabaseRestApi.test_connection.error
2022-04-19 10:21:16,847:WARNING:superset.views.base:[SupersetError(message='(builtins.NoneType) None\n(Background on this error at: <http://sqlalche.me/e/13/dbapi)>', error_type=<SupersetErrorType.GENERIC_DB_ENGINE_ERROR: 'GENERIC_DB_ENGINE_ERROR'>, level=<ErrorLevel.ERROR: 'error'>, extra={'engine_name': 'Apache Pinot', 'issue_codes': [{'code': 1002, 'message': 'Issue 1002 - The database returned an unexpected error.'}]})]
k
Can you get the log output from Pinot?
Oh hey, sorry my URL was wrong
pinot+<http://localhost:8000/query?server=http%3A%2F%2Flocalhost%3A9000%2F>
That's what I get from copy and pasting from my old code bases 😜
Oh still wrong...
Okay, it should be good now
URL decoded it looks like this: 
pinot+<http://localhost:8000/query?server=http://localhost:9000/>
Where did you get the format for your connection string?
h
from the docs https://docs.pinot.apache.org/integrations/superset this is what i’m using to test now:
Copy code
from sqlalchemy import *
    ...: from sqlalchemy.engine import create_engine
    ...: from sqlalchemy.schema import *
    ...:
    ...: engine = create_engine('pinot+<http://localhost:8000/query?server=http://localhost:9000/>')  
    ...:
    ...: places = Table('baseballStats', MetaData(bind=engine), autoload=True)
    ...: print(select([func.count('*')], from_obj=places).scalar())
c
@User just to confirm - database connection is fine - but query is timing out correct ?
k
Did that connection string work out?
h
for what i posted above this is the error i’m getting:
Copy code
ConnectionError: HTTPConnectionPool(host='localhost', port=8000): Max retries exceeded with url: /query/sql (Caused by NewConnectionError('<urllib3.connection.HTTPConnection object at 0x10730a790>: Failed to establish a new connection: [Errno 61] Connection refused'))
With pinotdb:
Copy code
In [26]: from pinotdb import connect
    ...:
    ...: conn = connect(host='localhost', port=9000, path='/query/sql', scheme='http')
    ...: curs = conn.cursor()
    ...: curs.execute("""
    ...:     select * from baseballStats
    ...: """)
    ...: for row in curs:
    ...:     print(row)
    ...:
Copy code
~/src/superset/venv/lib/python3.8/site-packages/pinotdb/db.py in check_sufficient_responded(self, query, queried, responded)
    261             needed = fraction
    262         if responded < 0 or responded < needed:
--> 263             raise exceptions.DatabaseError(
    264                 f"Query\n\n{query} timed out: Out of {queried}, only"
    265                 f" {responded} responded, while needed was {needed}"

DatabaseError: Query


    select * from baseballStats
 timed out: Out of -1, only -1 responded, while needed was -1
k
Can you 
curl <http://localhost:8000/query?server=http://localhost:9000/>
h
curl: (7) Failed to connect to localhost port 8000: Connection refused
k
Okay, so your broker is not available at 
localhost:8000
h
Screen Shot 2022-04-19 at 10.31.42 AM.png
for context
how do i figure out what port my broker is living at
k
One sec
h
is it 8000?
k
It should be
Try 
netstat -vanp tcp | grep '*.2123\|9000\|8000\|7000'
h
Copy code
✦ ➜ netstat -vanp tcp | grep '*.2123\|9000\|8000\|7000'
tcp6       0      0  ::1.9000               ::1.57711              FIN_WAIT_2  407508 146808    478      0 0x0112 0x0000000c
tcp6       0      0  ::1.57711              ::1.9000               CLOSE_WAIT  407577 146808  57242      0 0x0022 0x00000000
tcp46      0      0  *.9000                 *.*                    LISTEN      131072 131072    478      0 0x0100 0x00000006
tcp6       0      0  ::1.9000               ::1.63536              TIME_WAIT   403702 146808    478      0 0x2131 0x0000000c
tcp6       0      0  ::1.9000               ::1.63537              TIME_WAIT   400527 146808    478      0 0x2131 0x0000000c
tcp6       0      0  ::1.9000               ::1.63538              TIME_WAIT   404686 146808    478      0 0x2131 0x0000000c
k
Well thats odd 😜
Which version of Pinot are you using?
h
so i ran this to start up the cluster:
Copy code
docker run \
    -p 9000:9000 \
    apachepinot/pinot:0.9.3 QuickStart \
    -type batch
so 0.9.3
k
Ah, so you're running your test script inside the container of the controller
Which makes sense why you can't see the broker
h
got it
k
Let's see
h
what is the proper command
k
Oh, if it's QuickStart it should be all in one container
Try adding the broker and server ports to your docker command
h
Copy code
docker run \
    -p 9000:9000 \
    -p 8000:8000 \
    -p 7000:7000 \
    apachepinot/pinot:0.9.3 QuickStart \
    -type batch
right?
k
Yeah that looks good
That should solve the problem I think
h
working on it now
and i’ll report back thanks kenny
k
🎉
Sounds good Hugh. Let me know if I can help with anything
h
getting the same thing
Copy code
✦ ➜ docker ps
CONTAINER ID   IMAGE                      COMMAND                  CREATED          STATUS         PORTS                                                                                   NAMES
fe8b5fdf9dcb   apachepinot/pinot:latest   "./bin/pinot-admin.s…"   14 minutes ago   Up 2 minutes   0.0.0.0:7000->7000/tcp, 0.0.0.0:8000->8000/tcp, 0.0.0.0:9000->9000/tcp, 8096-8099/tcp   confident_curie
Copy code
✦ ➜ netstat -vanp tcp | grep '*.2123\|9000\|8000\|7000'
tcp46      0      0  *.8000                 *.*                    LISTEN      131072 131072    478      0 0x0100 0x00000006
tcp46      0      0  *.7000                 *.*                    LISTEN      131072 131072    478      0 0x0100 0x00000006
tcp46      0      0  *.9000                 *.*                    LISTEN      131072 131072    478      0 0x0100 0x00000006
tcp6       0      0  ::1.9000               ::1.57711              FIN_WAIT_2  407508 146808    478      0 0x0112 0x0000000c
tcp6       0      0  ::1.57711              ::1.9000               CLOSE_WAIT  407577 146808  57242      0 0x0022 0x00000000
k
Okay, that looks good. We might need to update that connection string. It is likely correct in the docs. I took mine from a much older version of Pinot.
h
waiitttt
⏸️ 1
we got it!
pinot+<http://localhost:8000/query?server=http://localhost:9000>
k
Woot 🙂
Sweet. If you folks need anything else, ping me. Looking forward to seeing the enhancements.
h
naw we are good i’m going to test this pr with the new grains then report there thanks kenny
💥 1
k
You're very welcome
h
Running Pinot w/ Docker
Copy code
docker run \
    -p 9000:9000 \
    -p 8000:8000 \
    -p 7000:7000 \
    apachepinot/pinot:0.9.3 QuickStart \
    -type batch
Working Connection String: 
pinot+<http://localhost:8000/query?server=http://localhost:9000>
Python Test Example:
Copy code
In [2]: from pinotdb import connect
   ...:
   ...: conn = connect(host='localhost', port=8000, path='/query/sql', scheme='http')
   ...: curs = conn.cursor()
   ...: curs.execute("""
   ...:     select * from baseballStats
   ...: """)
   ...: for row in curs:
   ...:     print(row)
   ...:
k
Okay I'll track back in the doc changes to see what happened
h
👍
a
i am able to connect superset with pinot like this
Copy code
pinot+<http://apache-pinot-broker-0.apache-pinot-broker-headless.pinot-cluster.svc.cluster.local:8099/query?controller=http%3A%2F%2Fapache-pinot-controller-0.apache-pinot-controller-headless.pinot-cluster.svc.cluster.local%3A9000%2F>
Open in Slack
PreviousNext
Powered by