Original post
I want to try out the Apache Arrow Flight SQL Driver for a large OLAP query on a PostgreSQL database. When I run the following example:
import adbc_driver_flightsql.dbapi
import adbc_driver_manager
conn = adbc_driver_flightsql.dbapi.connect('flightsql://username:[email protected]:5432/database')
with conn.cursor() as cur:
cur.execute("SELECT 1")
assert cur.fetchone() == (1,)
conn.close()
I keep getting this error:
adbc_driver_manager.OperationalError: IO: [FlightSQL] connection
error: desc = “transport: authentication handshake failed: EOF”
(Unavailable; Prepare)
However, I can reach the DB using the normal adbc PostgreSQL Driver without issues:
conn = adbc_driver_postgresql.dbapi.connect('postgresql://username:[email protected]:5432/database')
I followed these instructions to install Flight SQL (I run Debian 12) and then added shared_preload_libraries = 'arrow_flight_sql' in postgresql.conf per this, plus restart. Any idea what I’m missing?
Update
Using @Zeroshade’s connection request, I get a connection refused answer:
[FlightSQL] connection error: desc = “transport: Error while dialing: dial tcp 172.20.30.40:15432: connect: connection refused” (Unavailable; AuthenticateBasicToken)
Trying netcat (nc -vz 172.20.30.40 15432) on that port also yielded:
nc: connect to 172.20.30.40 port 15432 (tcp) failed: Connection refused
So I checked netstat (netstat -an | grep "LISTEN ") to see if the server listens on that port and lo and behold: for some reason it’s only listening on IPv6 and localhost…
tcp6 0 0 127.0.0.1:15432 :::* LISTEN
Which brings me to my new question: Does anybody know in what config file I can adjust what IP the arrow_flight_sql module listens on?
3
Looking at the link you provided, most likely the issue is the port.
In your post you said that you used postgresql://username:[email protected]:5432/database as the URI to connect, note the port being 5432. In the link to the config documentation, the default port is 15432 and uses a different scheme than flightsql.
Can you try using grpc://172.20.30.40:15432 as your URI and setting the username and password using options?
It also looks like you need to set an extra header option to set the database.
Like so:
import adbc_driver_flightsql.dbapi
import adbc_driver_manager
conn = adbc_driver_flightsql.dbapi.connect('grpc://172.20.30.40:15432',
db_kwargs={
adbc_driver_manager.DatabaseOptions.USERNAME.value: "username",
adbc_driver_manager.DatabaseOptions.PASSWORD.value: "password",
},
conn_kwargs={
"adbc.flight.sql.rpc.call_header.x-flight-sql-database": "database",
})
with conn.cursor() as cur:
cur.execute("SELECT 1")
assert cur.fetchone() == (1,)
conn.close()
3