Channels
#troubleshooting
Title
# troubleshooting
s
Srini Kadamati
04/19/2022, 5:13 PMhey 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
Hugh Miles
04/19/2022, 5:14 PMI’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 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 -1let me know if anyone can help me debug so i can verify my changes work as intended
k
Kenny Bastani
04/19/2022, 5:17 PMTry
pinot+<http://pinot:8000/query?server=http%3A%2F%2Fpinot%3A9000%2F>h
Hugh Miles
04/19/2022, 5:20 PMsame 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
Kenny Bastani
04/19/2022, 5:22 PMCan 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
Hugh Miles
04/19/2022, 5:28 PMfrom 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
Chinmay Soman
04/19/2022, 5:28 PM@User just to confirm - database connection is fine - but query is timing out correct ?
k
Kenny Bastani
04/19/2022, 5:29 PMDid that connection string work out?
h
Hugh Miles
04/19/2022, 5:29 PMfor 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 -1k
Kenny Bastani
04/19/2022, 5:30 PMCan you
curl <http://localhost:8000/query?server=http://localhost:9000/>h
Hugh Miles
04/19/2022, 5:31 PMcurl: (7) Failed to connect to localhost port 8000: Connection refused
k
Kenny Bastani
04/19/2022, 5:31 PMOkay, so your broker is not available at
localhost:8000h
Hugh Miles
04/19/2022, 5:31 PMmessage has been deleted
for context
how do i figure out what port my broker is living at
k
Kenny Bastani
04/19/2022, 5:32 PMOne sec
h
Hugh Miles
04/19/2022, 5:32 PMis it 8000?
k
Kenny Bastani
04/19/2022, 5:32 PMIt should be
Try
netstat -vanp tcp | grep '*.2123\|9000\|8000\|7000'h
Hugh Miles
04/19/2022, 5:33 PM 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 0x0000000ck
Kenny Bastani
04/19/2022, 5:33 PMWell thats odd 😜
Which version of Pinot are you using?
h
Hugh Miles
04/19/2022, 5:34 PMso i ran this to start up the cluster:
Copy code
docker run \
-p 9000:9000 \
apachepinot/pinot:0.9.3 QuickStart \
-type batchso 0.9.3
k
Kenny Bastani
04/19/2022, 5:34 PMAh, so you're running your test script inside the container of the controller
Which makes sense why you can't see the broker
h
Hugh Miles
04/19/2022, 5:34 PMgot it
k
Kenny Bastani
04/19/2022, 5:34 PMLet's see
h
Hugh Miles
04/19/2022, 5:34 PMwhat is the proper command
k
Kenny Bastani
04/19/2022, 5:35 PMOh, if it's QuickStart it should be all in one container
Try adding the broker and server ports to your docker command
h
Hugh Miles
04/19/2022, 5:37 PM Copy code
docker run \
-p 9000:9000 \
-p 8000:8000 \
-p 7000:7000 \
apachepinot/pinot:0.9.3 QuickStart \
-type batchright?
k
Kenny Bastani
04/19/2022, 5:37 PMYeah that looks good
That should solve the problem I think
h
Hugh Miles
04/19/2022, 5:41 PMworking on it now
and i’ll report back thanks kenny
k
Kenny Bastani
04/19/2022, 5:41 PM🎉
Sounds good Hugh. Let me know if I can help with anything
h
Hugh Miles
04/19/2022, 5:56 PMgetting 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 0x00000000k
Kenny Bastani
04/19/2022, 5:57 PMOkay, 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
Hugh Miles
04/19/2022, 5:57 PMwaiitttt
⏸️ 1
we got it!
pinot+<http://localhost:8000/query?server=http://localhost:9000>k
Kenny Bastani
04/19/2022, 5:58 PMWoot 🙂
Sweet. If you folks need anything else, ping me. Looking forward to seeing the enhancements.
h
Hugh Miles
04/19/2022, 6:00 PMnaw we are good i’m going to test this pr with the new grains then report there thanks kenny
💥 1
k
Kenny Bastani
04/19/2022, 6:00 PMYou're very welcome
h
Hugh Miles
04/19/2022, 6:00 PMRunning Pinot w/ Docker
Copy code
docker run \
-p 9000:9000 \
-p 8000:8000 \
-p 7000:7000 \
apachepinot/pinot:0.9.3 QuickStart \
-type batchpinot+<http://localhost:8000/query?server=http://localhost:9000> 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
Kenny Bastani
04/19/2022, 6:00 PMOkay I'll track back in the doc changes to see what happened
h
Hugh Miles
04/19/2022, 6:01 PM👍
a
ahsen m
04/19/2022, 9:26 PMi 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>