Superset is not able to load the result from hydrolix

[Dhruvin4530]

Superset is unable to load the dashboard even though there is a timestamp mentioned in the query. Here is an example of that.

SELECT
  toDateTime(intDiv(toUInt32(toDateTime("timestamp")), 300) * 300) AS "timestamp",
  countIf(location = 'session_setup' AND message = 'Session Created') AS "Session Created",
  countIf(solved = 1 AND verified = 1 AND location = 'game_verify') AS "Session Verified"
FROM "events_development"."events"
WHERE
  "timestamp" >= toDateTime('2026-06-23 14:02:34')
  AND "timestamp" < toDateTime('2026-06-24 14:02:34')
GROUP BY
  "timestamp"
ORDER BY
  "Session Created" DESC
LIMIT 10000

Error

Error: Received ClickHouse exception, code: 1001, server response: { "error": "std::exception. Code: 1001, type: h::db::HdxStorageError, e.what() = <HdxStorageError hdx_query_timerange_required is set to true. Your query needs a time range filter in a WHERE clause> (version 24.8.6.1)", "query": "SELECT\n toDateTime(intDiv(toUInt32(toDateTime(\"timestamp\")), 300) * 300) AS \"timestamp\",\n countIf(location = 'session_setup' AND message = 'Session Created') AS \"Session Created\",\n countIf(solved = 1 AND verified = 1 AND location = 'game_verify') AS \"Session Verified\"\nFROM \"events_development\".\"events\"\nWHERE\n \"timestamp\" >= toDateTime('2026-06-23 18:04:38')\n AND \"timestamp\" < toDateTime('2026-06-24 18:04:38')\nGROUP BY\n \"timestamp\"\nORDER BY\n \"Session Created\" DESC\nLIMIT 10000\nSETTINGS hdx_query_pool_name = 'query-peer', hdx_query_max_memory_usage = 40802189312, hdx_query_timerange_required = TRUE, hdx_query_max_timerange_sec = 5356800, hdx_query_max_execution_time = 120, hdx_query_admin_comment = 'User: Dhruvin Soni'\n FORMAT Native" } (for url https://hydrolix-eks.darkoselabs.com:8088)

@dosu Please take a look and help here.

[dosubot[bot]]

This is a Hydrolix-side issue, not a Superset bug. The error hdx_query_timerange_required is set to true. Your query needs a time range filter in a WHERE clause is coming from Hydrolix's query validator, which is failing to recognize Superset's time filter as a valid time range — even though the WHERE clause clearly contains timestamp boundaries.

Looking at the query in the error, the filter is:

WHERE "timestamp" >= toDateTime('2026-06-23 18:04:38')
  AND "timestamp" < toDateTime('2026-06-24 18:04:38')

The likely cause is that Hydrolix requires the time range filter to reference the raw primary timestamp column directly, without the toDateTime() wrapping or with a specific format it can parse. Superset's ClickHouse engine always wraps datetime literals with toDateTime() [1], which Hydrolix's filter validator may not recognize.

Suggested workarounds:

1. Check Hydrolix's time filter requirements — Hydrolix may require the filter to use specific comparison syntax (e.g., bare string literals like '2026-06-23 14:02:34' instead of toDateTime('...')). Consult Hydrolix documentation on what constitutes a recognized time range filter when hdx_query_timerange_required = TRUE.

2. Disable hdx_query_timerange_required — If you control the Hydrolix settings (the SETTINGS appended to the query), setting hdx_query_timerange_required = FALSE would bypass the validator. This appears to be injected by Hydrolix's query pool configuration rather than by Superset.

3. Use a custom DB engine spec — You could create a custom Superset database engine spec that extends ClickHouse but overrides convert_dttm() to output datetime literals in whatever format Hydrolix's validator expects. The current implementation is in superset/db_engine_specs/clickhouse.py [2].

4. Add a Jinja template or SQL override — If your dataset supports custom SQL, you might be able to use a virtual dataset with a raw SQL query that formats the time filter in a way Hydrolix accepts.

This is fundamentally a compatibility gap between Superset's ClickHouse connector and Hydrolix's extended validation layer. You may want to raise this with the Hydrolix team to understand exactly what filter format their validator parses.

To reply, just mention @dosu.

---

Docs are dead. Just use Dosu.

[dosubot[bot]]

If you set hdx_query_timerange_required = False, Hydrolix will no longer reject queries that lack a time range filter. This means:

• Queries without a WHERE timestamp >= ... AND timestamp < ... clause will be allowed to execute
• Those queries could potentially scan your entire table across all time partitions, leading to very expensive/slow queries, high memory usage, and potentially hitting hdx_query_max_execution_time or hdx_query_max_memory_usage limits instead

The hdx_query_timerange_required setting exists as a safety guardrail to prevent unbounded full-table scans on Hydrolix's columnar storage.

A better middle-ground approach would be to keep the guardrail but make it conditional in your mutator. For example, you could check whether the query already contains a time filter before deciding:

def get_settings(user_name, security_manager, query_settings=None, query=None):
    ...
    # Only enforce time range requirement if query doesn't already have a time filter
    has_time_filter = query and re.search(
        r"""["']?timestamp["']?\s*>=\s*""", query, re.IGNORECASE
    )
    
    settings = {
        ...
        "hdx_query_timerange_required": not has_time_filter,
        ...
    }

Or alternatively, since the real issue is that Hydrolix's validator doesn't recognize toDateTime('...') as a valid time range — you could raise this with Hydrolix support as a bug in their validator. The setting should work with your queries since they clearly contain time boundaries; the validator just isn't parsing them correctly in the newer server version (24.8.6.1).

You also still have hdx_query_max_timerange_sec: 62 days as a secondary guardrail, so even without the timerange_required check, queries are still bounded by the max time range setting if Hydrolix enforces that separately.

To reply, just mention @dosu.

---

Docs are dead. Just use Dosu.

[Dhruvin4530]

@dosu I want the best solution, what would you recommand?

[dosubot[bot]]

My recommended approach, in order of preference:

1. Best long-term fix: Report this to Hydrolix as a bug

Your queries clearly contain valid time range filters ("timestamp" >= toDateTime('...') AND "timestamp" < toDateTime('...')). Hydrolix's validator (v24.8.6.1) should recognize this format. The fact that it doesn't is a defect in their time range parser. File a ticket with Hydrolix support showing that their validator rejects toDateTime('...') comparisons — this is the correct fix at the source.

2. Best immediate fix: Override convert_dttm() to drop the toDateTime() wrapper

Create a custom engine spec that outputs bare datetime string literals, which Hydrolix's validator is more likely to recognize:

# In your superset_config.py or a custom engine spec module

from superset.db_engine_specs.clickhouse import ClickHouseConnectEngineSpec
from datetime import datetime
from typing import Optional


class HydrolixEngineSpec(ClickHouseConnectEngineSpec):
    engine_name = "Hydrolix"
    # Match your existing database connection engine
    engine = "clickhousedb"
    # Override to use bare string literals instead of toDateTime()
    
    @classmethod
    def convert_dttm(
        cls, target_type: str, dttm: datetime, db_extra: Optional[dict] = None
    ) -> Optional[str]:
        sqla_type = cls.get_sqla_column_type(target_type)
        if isinstance(sqla_type, types.Date):
            return f"'{dttm.strftime('%Y-%m-%d')}'"
        if isinstance(sqla_type, (types.DateTime, types.TIMESTAMP)):
            return f"'{dttm.strftime('%Y-%m-%d %H:%M:%S')}'"
        return None

This makes Superset generate "timestamp" >= '2026-06-23 14:02:34' instead of "timestamp" >= toDateTime('2026-06-23 14:02:34'). ClickHouse implicitly casts string literals to DateTime in comparisons, so the query still works correctly — and Hydrolix's validator should recognize it.

3. Acceptable short-term workaround: Disable the check conditionally

Modify your SQL_QUERY_MUTATOR to set hdx_query_timerange_required = False only when a time filter is already present:

def SQL_QUERY_MUTATOR(sql, **kwargs):
    ...
    query = str(sql)
    
    # Detect if query already has a time range filter
    has_time_filter = bool(re.search(
        r'["\']?timestamp["\']?\s*>=', query, re.IGNORECASE
    ))
    
    query, query_settings = extract_settings(query)
    
    # Override: don't require time range if one is already present
    if has_time_filter:
        query_settings.setdefault("hdx_query_timerange_required", False)
    
    new_settings = get_settings(
        user_name=user_name,
        security_manager=security_manager,
        query_settings=query_settings,
    )
    ...

This keeps the safety guardrail for queries without time filters (like ad-hoc SQL Lab queries) while bypassing the broken validator for chart/dashboard queries that already include one.

---

My recommendation: Go with option 3 as your immediate fix (deploy today, unblocks your dashboards) and option 1 in parallel (report to Hydrolix so they fix their validator). Option 2 is the cleanest long-term solution if Hydrolix won't fix the validator, but requires more testing to ensure nothing else breaks.

To reply, just mention @dosu.

---

Share context across your team and agents. Try Dosu.

[Dhruvin4530]

@dosu
We've upgraded the clickhouse connect driver and that's where the issue came. The minimum required version of clickhouse-connect has been raised to >=0.13.0. I want to know what has been changed that cause hydrolix to not understand the timestamp even though the timestamp is present in the query.

[dosubot[bot]]

Based on my research, the key change that's likely causing Hydrolix to reject your queries is how the newer clickhouse-connect driver sends the query over HTTP — specifically the FORMAT Native clause appended to the end of the query.

Looking at the error you posted earlier, the query Hydrolix receives ends with:

...LIMIT 10000
SETTINGS hdx_query_timerange_required = TRUE, ...
 FORMAT Native

Here's what's happening:

The clickhouse-connect driver (not Superset) appends FORMAT Native to your query before sending it over the HTTP interface [1]. Superset itself does not add this — it's an internal behavior of the driver to request results in ClickHouse's binary Native format.

What likely changed between the old and new driver versions:

1. FORMAT clause positioning — In older versions (0.5.x/0.6.x), the driver may have sent the format specification differently (e.g., via HTTP parameters, or using a different format like JSON/TabSeparated). In 0.13.0+, it appears to append FORMAT Native directly to the SQL text.

2. SETTINGS + FORMAT interaction — Your SQL_QUERY_MUTATOR appends SETTINGS ... to the query, and then the driver appends FORMAT Native after that. The combined query structure may be confusing Hydrolix's time-range parser. Hydrolix's validator likely parses the raw SQL text to find time range filters, and the FORMAT Native suffix (or the way SETTINGS are positioned relative to it) may be breaking that parsing in the newer driver.

3. The version bump was for a different reason — PR fix(clickhouse): remove _mutate_label workaround and bump clickhouse-connect to >=0.13.0 #38280 bumped the version to remove a column-alias hashing workaround (_mutate_label), not because of any query formatting changes [2]. The FORMAT Native behavior change is a side effect.

To confirm this theory, you can:

• Enable debug logging on the clickhouse-connect driver to see the exact raw HTTP request being sent
• Or use a network proxy to capture the actual query text sent to Hydrolix's HTTP endpoint

The root cause is that Hydrolix's time-range validator cannot parse the time filter when the query ends with SETTINGS ... FORMAT Native. This is ultimately a Hydrolix compatibility issue with how newer clickhouse-connect versions structure HTTP requests — they need to update their SQL parser to handle this format.

To reply, just mention @dosu.

---

Share context across your team and agents. Try Dosu.