# Problem connecting with Apache Superset

**URL:** <https://community.cratedb.com/t/problem-connecting-with-apache-superset/1594>\
**Category:** SQL\
**Created:** [September 19, 2023, 12:07pm UTC](https://community.cratedb.com/t/problem-connecting-with-apache-superset/1594 "2023-09-19T12:07:18Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Alin\_Mihut](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/alin_mihut/32/695_2.png) [@Alin\_Mihut](https://community.cratedb.com/u/Alin_Mihut)\
**Post date:** [September 19, 2023, 12:07pm UTC](https://community.cratedb.com/t/problem-connecting-with-apache-superset/1594/1 "2023-09-19T12:07:18Z")

</div>

Hello,

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

```auto
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

```auto
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](https://us1.discourse-cdn.com/flex020/uploads/crate/original/2X/8/8c7a35d47176b3c39eeae96b29ce7f710196b86d.png)

---

<div class="post-metadata">

**Author:** ![amotl](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/amotl/32/617_2.png) [@amotl](https://community.cratedb.com/u/amotl)\
**Post date:** [September 19, 2023, 12:13pm UTC](https://community.cratedb.com/t/problem-connecting-with-apache-superset/1594/2 "2023-09-19T12:13:55Z")

</div>

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:

```auto
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.

---

<div class="post-metadata">

**Author:** ![Alin\_Mihut](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/alin_mihut/32/695_2.png) [@Alin\_Mihut](https://community.cratedb.com/u/Alin_Mihut)\
**Post date:** [September 19, 2023, 12:33pm UTC](https://community.cratedb.com/t/problem-connecting-with-apache-superset/1594/3 "2023-09-19T12:33:50Z")

</div>

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

```auto
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

```

---

<div class="post-metadata">

**Author:** ![amotl](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/amotl/32/617_2.png) [@amotl](https://community.cratedb.com/u/amotl)\
**Post date:** [September 19, 2023, 12:40pm UTC](https://community.cratedb.com/t/problem-connecting-with-apache-superset/1594/4 "2023-09-19T12:40:02Z")

</div>

Hi Alin,

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

> [@amotl](#):
>
> `?ssl=true`

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](https://crate.io/docs/python/en/latest/by-example/sqlalchemy/getting-started.html#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](https://community.cratedb.com/t/workaround-for-connecting-cratedb-and-apache-superset-with-https/969).

With kind regards,  
Andreas.

---

<div class="post-metadata">

**Author:** ![Alin\_Mihut](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/alin_mihut/32/695_2.png) [@Alin\_Mihut](https://community.cratedb.com/u/Alin_Mihut)\
**Post date:** [September 19, 2023, 1:49pm UTC](https://community.cratedb.com/t/problem-connecting-with-apache-superset/1594/5 "2023-09-19T13:49:51Z")

</div>

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