Problem connecting with Apache Superset

CrateDB SQL
1

Hello,

When trying to connect to CrateDB from Apache Superset on pq protocol I get an io.crate.exception.

superset             | superset.databases.commands.exceptions.DatabaseTestConnectionFailedError: [SupersetError(message="(psycopg2.errors.InternalError_) line 1:1: mismatched input 'ROLLBACK' expecting {'SELECT', 'DEALLOCATE', 'FETCH', 'END', 'WITH', 'CREATE', 'ALTER', 'KILL', 'CLOSE', 'BEGIN', 'START', 'COMMIT', 'ANALYZE', 'DISCARD', 'EXPLAIN', 'SHOW', 'OPTIMIZE', 'REFRESH', 'RESTORE', 'DROP', 'INSERT', 'VALUES', 'DELETE', 'UPDATE', 'SET', 'RESET', 'COPY', 'GRANT', 'DENY', 'REVOKE', 'DECLARE'}\nCONTEXT:  io.crate.exceptions.SQLExceptions.esToCrateException(SQLExceptions.java:163)\nio.crate.exceptions.SQLExceptions.prepareForClientTransmission(SQLExceptions.java:152)\nio.crate.protocols.postgres.Messages.sendErrorResponse(Messages.java:190)\nio.crate.protocols.postgres.PostgresWireProtocol.handleSimpleQuery(PostgresWireProtocol.java:781)\nio.crate.protocols.postgres.PostgresWireProtocol$MessageHandler.dispatchMessage(PostgresWireProtocol.java:339)\nio.crate.protocols.postgres.PostgresWireProtocol$MessageHandler.dispatchState(PostgresWireProtocol.java:329)\nio.crate.protocols.postgres.PostgresWireProtocol$MessageHandler.channelRead0(PostgresWireProtocol.java:297)\nio.crate.protocols.postgres.PostgresWireProtocol$MessageHandler.channelRead0(PostgresWireProtocol.java:281)\nio.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:99)\nio.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444)\nio.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)\nio.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412)\nio.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:346)\nio.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:318)\nio.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444)\nio.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)\nio.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412)\nio.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1410)\nio.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:440)\nio.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)\n\n\n(Background on this error at: https://sqlalche.me/e/14/2j85)", error_type=<SupersetErrorType.GENERIC_DB_ENGINE_ERROR: 'GENERIC_DB_ENGINE_ERROR'>, level=<ErrorLevel.ERROR: 'error'>, extra={'engine_name': 'PostgreSQL', 'issue_codes': [{'code': 1002, 'message': 'Issue 1002 - The database returned an unexpected error.'}]})]

Has anyone encounter a similar case? I’m tying to figure it out if this is related with how CrateDB cluster is configured, or it’s caused by the Crate connector.

I’m running Apache Superset inside docker containers. I’m using the latest version of Crate connector and SQLAlchemy 1.4.32

RUN pip install sqlalchemy==1.4.32
RUN pip install crate

The same connection parameters work just fine from Grafana. Any help is appreciated.

Screenshot from 2023-09-19 15-06-32
Screenshot from 2023-09-19 15-06-321918Ă—921 68.2 KB

2

Dear Alin,

thank you for writing in. Apache Superset 3.0 was released yesterday, and we did not validate it with CrateDB yet. Are you using it already?

In general, you should use the CrateDB SQLAlchemy dialect to connect from Apache Superset, which reflects in using such a database connection string in URI format:

crate://foo:bar@cratedb.example.org:4200/?ssl=true

We confirmed this connection URI style worked on Apache Superset 2.x.

With kind regards,
Andreas.

3

Hi Andreas,
thank you for the reply.

I’m using apache/superset:2.1.0

When trying to make the connection using SQLAlchemy dialect I’m getting a SSL connection Error

superset             | 2023-09-19 12:26:44,619:WARNING:urllib3.connectionpool:Retrying (Retry(total=8, connect=None, read=0, redirect=None, status=None)) after connection broken by 'SSLError(SSLCertVerificationError(1, '[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: self signed certificate in certificate chain (_ssl.c:1131)'))': /_sql?types=true
4

Hi Alin,

if you are connecting to a non TLS/SSL peer, you will probably need to omit the ssl=true parameter.

If you need to turn off certificate verification, in order to connect to a host using self-signed certificates, you may need to use the verify_ssl_cert connection option within custom connection arguments, like outlined at SQLAlchemy » Getting Started » TLS options.

Those custom connection arguments can be defined by navigating to the connection settings’ “Advanced Settings” dialog, choose “Other”, and set "connect_args" in “Engine Parameters”, similarly like described at Workaround for connecting CrateDB and Apache Superset with HTTPS.

With kind regards,
Andreas.

1 Like
5

Thank you Andreas, this should solve the connection using a self signed cert, after I’ll match the client hostname :smile:

1 Like