Search code examples
clickhousematerialized-viewsdbt

dbt + clickhouse incremental materialization - error 62

i am struggling a bit with the incremental materialization via dbt-clickhouse (+ dbt 1.3). My model looks like this (i simplified as much as i could):

{{
    config(
        materialized='incremental',
        engine='MergeTree()',
        order_by=['ts'],
        unique_key=['impid']
    )
}}
select ts, impid from gam.data
{% if is_incremental() %}
where ts >= now() - toIntervalHour(1)
{% endif %}

the error I am receiving is this when running dbt run:

10:16:18    :HTTPDriver for http://localhost:8123 returned response code 400)
10:16:18     Code: 62. DB::Exception: Syntax error: failed at position 312 ('empty') (line 15, col 9): empty
10:16:18        as (
10:16:18
10:16:18    select ts, impid from gam.data
10:16:18
10:16:18        )
10:16:18            . Expected one of: token, Arrow, DoubleColon, MOD, DIV, NOT, BETWEEN, LIKE, ILIK
10:16:18
10:16:18  Done. PASS=6 WARN=0 ERROR=1 SKIP=0 TOTAL=7

the funny part is that the exact same model works with the basic view materialization but not with the incremental one. I tried every single combination I could come up with but so far no luck. Did you encounter something similar?

Solution

  • Ok, problem solved. I reached out on dbt's slack and it seems like the dbt-clickhouse adapter requires a more recent version of Clickhouse. It is a bug as the adapter should work with as versions as low as 22.7.1 but it does not. That being said, after upgrading the server, everything works fine.