Superset Clickhouse driver

[suedschwede]

At the moment there are two superset drivers "clickhouse-sqlalchemy","sqlalchemy-clickhouse"

There are problems with both drivers and that leads to a lot of questions

sqlalchemy-clickhouse

• Table with 128 Columns - Datasets sync of column does not work correctly - it seems there is a maximum columns you can use
• If the default user has a password you must use "infi.clickhouse_orm==1.0.4"
• using a dictionary you get "Python int too large to convert to C long"

clickhouse-sqlalchemy

• Sync of columns you get wrong data types e.g.: "NULLABLE(STRING)" instead of "STRING"

For me the only stable driver is "clickhouse-sqlalchemy" with the native interface

The documentation should recommend "clickhouse-sqlalchemy" instead of "sqlalchemy-clickhouse"
https://superset.apache.org/docs/databases/installing-database-drivers

It would be nice if the columns sync works correctly with "clickhouse-sqlalchemy"

[junlincc]

@srinify ^ 🙏

[hodgesrm]

Hello @suedschwede, I'm testing this case and need some advice on the problem you are seeing. To check datatypes I made a small table and added a row to enable queries.

CREATE TABLE superset_test (
    `basic_string` String,
    `nullable_string` Nullable(String),
    `lowcard_string` LowCardinality(String),
    `basic_datetime` DateTime,
    `nullable_datetime` Nullable(DateTime)
) ENGINE = TinyLog;

INSERT INTO superset_test VALUES('basic', 'nullable', 'lowcard', now(), now());

I can see the effect of syncing columns in the Edit Dataset view. The data types that return are STRING, NULLABLE(STRING), LOWCARDINALITY(STRING), DATETIME, NULLABLE(DATETIME). It looks as if the SQLAlchemy driver is getting these out of system.columns. A couple of questions:

1. Is the expected behavior to see just STRING and DATETIME? Or would you expect SQL types like VARCHAR?
2. Are there cases where these vendor-specific datatypes cause failures?
3. Are there other places where these types appear and cause problems?

It looks as Sqllite returns standard SQL types by comparison.

Thanks! I appended a copy of the Edit Dataset view mentioned above.

Cheers, Robert

[hodgesrm]

Adding to the original request by @suedschwede, I strongly recommend switching to clickhouse-sqlalchemy as the standard driver for ClickHouse. The reasons for this switch are as follows.

1. clickhouse-sqlalchemy is actively maintained (last commit 18 Feb 2021, under active development).
2. Releases are pushed regularly to pypi.org (last push 14 Dec 2020).
3. It has what appears to be a fairly complete SQLAlchemy implementation.
4. It supports both HTTP and native TCP wire protocols (vs. HTTP only)
5. It has a good security including support for TLS, SNI, and configurable certificate verification (vs. no encryption support)
6. It has a good suite of tests that check SQLAlchemy interfaces.

To the extent that there are compatibility issues like the one discussed here, we (Altinity) can get them fixed fairly quickly. We currently recommend clickhouse-sqlalchemy and clickhouse-driver to our users and have made fixes to them in the past, most recently in December.

What are the next steps to making this switch?

[suedschwede]

If you try to make a date/time filter to NULLABLE(DATE) you get an error in superset

The expected behavior is to see just STRING and DATETIME

NULLABLE(STRING) -> STRING
NULLABLE(DATE) -> DATE
NULLABLE(DATETIME) -> DATETIME

[villebro]

We're currently working on improving type inference (#13294). We'll make it top priority to enable this for Clickhouse as soon as possible.

[HeinzMayer]

I have created a temporary fix on my environment
clickhouse.py

It seems that the master branch does not work with clickhouse-sqlalchemy==0.1.6
Discussion

[betodealmeida]

Superset now uses clickhouse-sqlalchemy for Clickhouse.

[royxact]

Hi @villebro,

I see the issue is still happening in the current superset version, is there a fix planned, or HeinzMayer temporary fix the current solution?

Thanks!
Roy