FINAL modifier

[jmerchantmetrics]

Is your feature request related to a problem? Please describe. When using replacingMergeTree and some other tables in clickhouse, you need to use the FINAL modifier to dedupe the results that hasn't been garbage collected yet. (ex. SELECT * FROM tbl FINAL WHERE id = 1) is there any way to do this without creating a custom dataset or view?

[genzgd]

I assume you're talking about within Superset? The main client query methods in clickhouse-connect accept whatever SQL query (including a FINAL modifier) you specify. Note that we don't recommend using the minimal clickhouse-connect SQLAlchemy dialect outside of Superset.

Within Superset I could see setting a flag somehow to always specify a FINAL modifier for ReplacingMergeTree queries. We'll look into that as time and resources permit. And of course, community contributions to either the SQLAlchemy dialect in clickhouse-connect or the Superset ClickHouse Engine Spec are more than welcome.

[jmerchantmetrics]

I assume you're talking about within Superset? The main client query methods in clickhouse-connect accept whatever SQL query (including a FINAL modifier) you specify. Note that we don't recommend using the minimal clickhouse-connect SQLAlchemy dialect outside of Superset.

Within Superset I could see setting a flag somehow to always specify a FINAL modifier for ReplacingMergeTree queries. We'll look into that as time and resources permit. And of course, community contributions to either the SQLAlchemy dialect in clickhouse-connect or the Superset ClickHouse Engine Spec are more than welcome.

Do you know how i could manually code in the FINAL modifier in to superset, i am using clickhouse-connect in superset and cant seem to figure out how to do it.

[genzgd]

Unfortunately I'm not aware of any way to pass such a modifier with the current versions of Superset and clickhouse-connect (or with clickhouse-driver and clickhouse-sqlalchemy, for that matter). Others more familiar with Superset might have an idea.

As I mentioned above, we'll look into the necessary code changes to support this in the future, although I can't give any time estimate.

[franz101]

Is your feature request related to a problem? Please describe. When using replacingMergeTree and some other tables in clickhouse, you need to use the FINAL modifier to dedupe the results that hasn't been garbage collected yet. (ex. SELECT * FROM tbl FINAL WHERE id = 1) is there any way to do this without creating a custom dataset or view?

SETTINGS final=1 for sqlalchemy or execution_options(settings=dict(final=1))

[joe-clickhouse]

I know this is old but I was able to use the Select.with_hint() method to accomplish this. It has been merged, but not yet released.

Closed by https://github.com/ClickHouse/clickhouse-connect/pull/584