Comments (4)
So my full setup is as follows:
dremio.conf
services: {
coordinator.enabled: true,
coordinator.master.enabled: true,
executor.enabled: true,
flight.enabled: true,
flight.auth.mode: "arrow.flight.auth2"
flight.use_session_service: true,
flight.ssl.enabled: true
}
Dockerfile
FROM dremio/dremio-oss
COPY dremio.conf /opt/dremio/conf/dremio.conf
docker command:
docker build -t dremio-flight . && docker run --rm -p 9047:9047 -p 31010:31010 -p 32010:32010 -p 45678:45678 dremio-flight
python
Python 3.8.10 (default, Mar 13 2023, 10:26:41)
[GCC 9.4.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlalchemy as sa
>>> engine = sa.create_engine('dremio+flight://dremio:dremio123@localhost:32010/dremio?UseEncryption=true&disableCertificateVerification=true', echo=True)
<stdin>:1: SADeprecationWarning: The dbapi() classmethod on dialect classes has been renamed to import_dbapi(). Implement an import_dbapi() classmethod directly on class <class 'sqlalchemy_dremio.flight.DremioDialect_flight'> to remove this warning; the old .dbapi() classmethod may be maintained for backwards compatibility.
>>> con = engine.connect()
>>> con.execute(sa.text('SELECT * FROM "lakefs.staging.main".table1'))
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/home/tobias/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1414, in execute
return meth(
File "/home/tobias/.local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 486, in _execute_on_connection
return connection._execute_clauseelement(
File "/home/tobias/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1630, in _execute_clauseelement
compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
File "/home/tobias/.local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 631, in _compile_w_cache
if compiled_cache is not None and dialect._supports_statement_cache:
File "/home/tobias/.local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 1138, in __get__
obj.__dict__[self.__name__] = result = self.fget(obj)
File "/home/tobias/.local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 424, in _supports_statement_cache
"warning." % (self.name, self.driver),
AttributeError: 'DremioDialect_flight' object has no attribute 'driver'
>>>
The SELECT * FROM "lakefs.staging.main".table1
query works from within Dremio as well as from DBeaver using the JDBC driver so to me this points to a problem with the sqlalchemy_dremio
driver.
$ pip freeze
...
SQLAlchemy==2.0.8
sqlalchemy-dremio==3.0.3
...
from sqlalchemy_dremio.
After sleeping on it, I managed to make some progress on this.
It occurred to me that the people reporting this issue sometimes use port 31010 and sometimes 32020. I'm able to connect to my Dremio instance from DBeaver using the JDBC driver on port 31010. The Superset Dremio page mentions that the default port for Arrow Flight is 32010 (but this is quite subtle and I missed it at first) but this port isn't exposed in the Dremio DockerHub page which only lists port 31010. I also exposed port 32010 by changing the docker command to
docker run -p 9047:9047 -p 31010:31010 - p 32010:32010 -p 45678:45678 dremio/dremio-oss
and now I'm able to connect and authenticate from Pyhon:
>>> import sqlalchemy as sa
>>> engine = sa.create_engine('dremio+flight://dremio:dremio123@localhost:32010/dremio?UseEncryption=false&disableCertificateVerification=true', echo=True)
>>> con = engine.connect()
>>>
The connection from Superset is still not working. I'm guessing it might need a different combination of encryption settings. I will experiment more with this and in particular try the suggestion from #23.
from sqlalchemy_dremio.
Using the settings from #23 I now get an SSL certificate error:
dremio.conf:
services: {
coordinator.enabled: true,
coordinator.master.enabled: true,
executor.enabled: true,
flight.enabled: true,
flight.use_session_service: true,
flight.ssl.enabled: true,
flight.ssl.auto-certificate.enabled: true
}
Python 3.8.10 (default, Mar 13 2023, 10:26:41)
[GCC 9.4.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlalchemy as sa
>>> engine = sa.create_engine('dremio+flight://dremio:dremio123@localhost:32010/dremio?UseEncryption=true&disableCertificateVerification=false', echo=True)
<stdin>:1: SADeprecationWarning: The dbapi() classmethod on dialect classes has been renamed to import_dbapi(). Implement an import_dbapi() classmethod directly on class <class 'sqlalchemy_dremio.flight.DremioDialect_flight'> to remove this warning; the old .dbapi() classmethod may be maintained for backwards compatibility.
>>> con = engine.connect()
E0405 09:35:25.276792401 4334 ssl_transport_security.cc:1501] Handshake failed with fatal error SSL_ERROR_SSL: error:0A000086:SSL routines::certificate verify failed.
E0405 09:35:25.286732554 4334 ssl_transport_security.cc:1501] Handshake failed with fatal error SSL_ERROR_SSL: error:0A000086:SSL routines::certificate verify failed.
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/home/tobias/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3251, in connect
return self._connection_cls(self)
File "/home/tobias/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 145, in __init__
self._dbapi_connection = engine.raw_connection()
File "/home/tobias/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3275, in raw_connection
return self.pool.connect()
File "/home/tobias/.local/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 420, in connect
return _ConnectionFairy._checkout(self, self._fairy)
File "/home/tobias/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 1271, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "/home/tobias/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 719, in checkout
rec = pool._do_get()
File "/home/tobias/.local/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 402, in _do_get
c = self._create_connection()
File "/home/tobias/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 396, in _create_connection
return _ConnectionRecord(self)
File "/home/tobias/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 681, in __init__
self.__connect()
File "/home/tobias/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 906, in __connect
pool.logger.debug("Error on connect(): %s", e)
File "/home/tobias/.local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 147, in __exit__
raise exc_value.with_traceback(exc_tb)
File "/home/tobias/.local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 901, in __connect
self.dbapi_connection = connection = pool._invoke_creator(self)
File "/home/tobias/.local/lib/python3.8/site-packages/sqlalchemy/engine/create.py", line 636, in connect
return dialect.connect(*cargs, **cparams)
File "/home/tobias/.local/lib/python3.8/site-packages/sqlalchemy_dremio/flight.py", line 196, in connect
return self.dbapi.connect(*cargs, **cparams)
File "/home/tobias/.local/lib/python3.8/site-packages/sqlalchemy_dremio/db.py", line 20, in connect
return Connection(c)
File "/home/tobias/.local/lib/python3.8/site-packages/sqlalchemy_dremio/db.py", line 86, in __init__
bearer_token = client.authenticate_basic_token(properties['UID'], properties['PWD'])
File "pyarrow/_flight.pyx", line 1398, in pyarrow._flight.FlightClient.authenticate_basic_token
File "pyarrow/_flight.pyx", line 71, in pyarrow._flight.check_flight_status
pyarrow._flight.FlightUnavailableError: Flight returned unavailable error, with message: failed to connect to all addresses; last error: UNKNOWN: ipv4:127.0.0.1:32010: Ssl handshake failed: SSL_ERROR_SSL: error:0A000086:SSL routines::certificate verify failed
>>> engine = sa.create_engine('dremio+flight://dremio:dremio123@localhost:32010/dremio?UseEncryption=true&disableCertificateVerification=false', echo=True)E0405 09:35:35.108920946 4346 ssl_transport_security.cc:1501] Handshake failed with fatal error SSL_ERROR_SSL: error:0A000086:SSL routines::certificate verify failed.
E0405 09:35:45.108883819 4346 ssl_transport_security.cc:1501] Handshake failed with fatal error SSL_ERROR_SSL: error:0A000086:SSL routines::certificate verify failed.
I'm serving this off localhost for the moment so perhaps it's no surprise that certificates don't work. I did try it with SSL enabled and without the certificate and that worked from Python but still didn't work from Superset. What do I need to do on the Superset side to get this working?
from sqlalchemy_dremio.
Refer to #37 (comment)
from sqlalchemy_dremio.
Related Issues (20)
- Parametrized statements - Issue with quoted strings HOT 1
- Connection pool - The default implementation is not recommended for production use
- Upgrading Arrow Flight to latest for Dremio on ARM
- Flight support for Passwords with = in them HOT 1
- Extend _type_map with Array HOT 1
- NotImplementedError for dremio+flight dialect HOT 6
- Driver files unavailable HOT 7
- sqlalchemy_dremio arrow flight ssl certificates HOT 2
- pytest fails sqlalchemy.exc.StatementError: (builtins.KeyError) 'object' for test query HOT 1
- Problem installing version 2.0.0 with pip HOT 2
- SSL=0 still try to verify SSL cert HOT 3
- Token auth support in Flight for Dremio Cloud HOT 2
- Superset - NaTType does not support utcoffset HOT 3
- Superset Dremio Connectivity HOT 3
- AttributeError: 'DremioDialect_flight' object has no attribute 'driver' HOT 6
- All queries sent from client query INFORMATION_SCHEMA."TABLES"
- SQL Alchemy driver returns nan for null values HOT 1
- AttributeError: 'DremioDialect_flight' object has no attribute 'driver' #37
- Add 'datetime64[ms]': types.DATETIME to _type_map in query.py HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from sqlalchemy_dremio.