Problem with the distributed_table materialization #179

zli06160 · 2023-08-11T15:19:57Z

Hello everyone, I am not 100% sure if this is a bug or because I did not correctly use the connector.

No same issue with table and view materializations.

Describe the bug

Here my table definition event.sql: it takes a parquet file from s3, then creates the table with the configurations.

{{ config(
    cluster="default",
    schema="db_test_dbt",
    materialized="distributed_table",
    sharding_key="rand()",
    unique_key="(event_id)",
    full_refresh=true,
    engine="ReplicatedReplacingMergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}') ",
    settings={"allow_nullable_key": 1},
    order_by="(event_id)"
    )
}}

SELECT 
    * 
FROM 
    s3('path/to/parquet/file/on/s3/event/*', 'Parquet')

😊The first dbt run works well, the table is correctly created on my clickhouse cluster.

😭When I do dbt run again, it does not work any more:

Database Error in model event (models/event.sql)
    Code: 62.
    DB::Exception: Empty query. Stack trace:

The file target/run/dbt_project_name/models/event.sql is empty;
The file target/compiled/dbt_project_name/models/event.sql contains the select * from s3 query;

Steps to reproduce

dbt run --select models/event.sql: Completed successfully.😊
dbt run --select models/event.sql again: ERROR!😭
dbt run --select models/event.sql again: SAME ERROR!😭
dbt run --select models/event.sql again: SAME ERROR!😭
drop the tables manually, and retry dbt run --select models/event.sql: Completed successfully. 😊

Configuration

Environment

dbt version: 1.4.6.
dbt-clickhouse version: 1.4.7 and same problem for previous versions.
clickhouse-driver version (if using native)
clickhouse-connect version (if using http): 1.4.7

ClickHouse server

ClickHouse Server version: 23.3.1
multiple shards * 2 replicates

Is it a bug?
Or something under development?
Or something wrongly set in my config clause?

Thanks in advance.

The text was updated successfully, but these errors were encountered:

genzgd · 2023-08-13T22:35:44Z

This is a new, experimental feature so I suspect it's a bug. Any thoughts @gladkikhtutu?

gfunc · 2023-08-16T10:09:21Z

can you please provide dbt.log? The error was returned by ClickHouse, guess we need to know what exactly the query looks like.

zli06160 · 2023-08-16T14:23:28Z

Hello @gfunc, thanks for your attention, here the log extracts you required:

first dbt run, everything is OK, the table is created, I can select from the table.
second dbt run: K.O., here are some extracts:

(2nd dbt run)
�[0m13:26:13.933866 [debug] [ThreadPool]: dbt_clickhouse adapter: On list_schemas: /* {"app": "dbt", "dbt_version": "1.4.6", "profile_name": "stage", "target_name": "clickhouse_stage", "connection_name": "list_schemas"} */

    select name from system.databases
  ...
�[0m13:26:13.957913 [debug] [ThreadPool]: dbt_clickhouse adapter: SQL status: OK in 0.02 seconds
�[0m13:26:13.962853 [debug] [ThreadPool]: Acquiring new clickhouse connection 'list_None_default_db_test_dbt'
�[0m13:26:13.963439 [debug] [ThreadPool]: Re-using an available connection from the pool (formerly list_None_default_db_test_dbt)
�[0m13:26:13.973177 [debug] [ThreadPool]: dbt_clickhouse adapter: On list_None_default_db_test_dbt: /* {"app": "dbt", "dbt_version": "1.4.6", "profile_name": "stage", "target_name": "clickhouse_stage", "connection_name": "list_None_default_db_test_dbt"} */
select
      t.name as name,
      t.database as schema,
      if(engine not in ('MaterializedView', 'View'), 'table', 'view') as type,
      db.engine as db_engine
    from system.tables as t JOIN system.databases as db on t.database = db.name
    where schema = 'default_db_test_dbt'
  ...
�[0m13:26:14.004950 [debug] [ThreadPool]: dbt_clickhouse adapter: SQL status: OK in 0.03 seconds
.
.
.
�[0m13:26:14.022817 [debug] [Thread-1  ]: Began executing node model.dbt_prototype_version.event
�[0m13:26:14.047107 [debug] [Thread-1  ]: dbt_clickhouse adapter: On model.dbt_prototype_version.event: /* {"app": "dbt", "dbt_version": "1.4.6", "profile_name": "stage", "target_name": "clickhouse_stage", "node_id": "model.dbt_prototype_version.event"} */

    SELECT value FROM system.settings WHERE name = 'insert_distributed_sync'
  ...
�[0m13:26:14.077945 [debug] [Thread-1  ]: dbt_clickhouse adapter: SQL status: OK in 0.03 seconds
�[0m13:26:14.095161 [debug] [Thread-1  ]: dbt_clickhouse adapter: On model.dbt_prototype_version.event: /* {"app": "dbt", "dbt_version": "1.4.6", "profile_name": "stage", "target_name": "clickhouse_stage", "node_id": "model.dbt_prototype_version.event"} */
drop table if exists default_db_test_dbt.event__dbt_tmp 
    ON CLUSTER "default" ...
�[0m13:26:14.253025 [debug] [Thread-1  ]: dbt_clickhouse adapter: SQL status: OK in 0.16 seconds
.
.
.
�[0m13:26:14.270410 [debug] [Thread-1  ]: dbt_clickhouse adapter: On model.dbt_prototype_version.event: /* {"app": "dbt", "dbt_version": "1.4.6", "profile_name": "stage", "target_name": "clickhouse_stage", "node_id": "model.dbt_prototype_version.event"} */
  create view default_db_test_dbt.event__dbt_tmp 

    ON CLUSTER "default" 
  as (
    
SELECT
    *
FROM
    s3('https://path-to-s3/events/*.parquet',
     'Parquet')
  )
  ...
�[0m13:26:14.954330 [debug] [Thread-1  ]: dbt_clickhouse adapter: SQL status: OK in 0.68 seconds
�[0m13:26:14.975285 [debug] [Thread-1  ]: dbt_clickhouse adapter: On model.dbt_prototype_version.event: /* {"app": "dbt", "dbt_version": "1.4.6", "profile_name": "stage", "target_name": "clickhouse_stage", "node_id": "model.dbt_prototype_version.event"} */

    select name, type from system.columns where table = 'event__dbt_tmp'
    
      and database = 'default_db_test_dbt'
    
    order by position
  ...
�[0m13:26:15.007987 [debug] [Thread-1  ]: dbt_clickhouse adapter: SQL status: OK in 0.03 seconds
�[0m13:26:15.028474 [debug] [Thread-1  ]: dbt_clickhouse adapter: On model.dbt_prototype_version.event: /* {"app": "dbt", "dbt_version": "1.4.6", "profile_name": "stage", "target_name": "clickhouse_stage", "node_id": "model.dbt_prototype_version.event"} */

  create table default_db_test_dbt.eventlocal__dbt_backup

    ON CLUSTER "default"  (
      code Nullable(String), event_id Nullable(String),........ 
  )

  engine = ReplicatedReplacingMergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}') 
  order by ((event_id))
  
  SETTINGS  allow_nullable_key=1
.
.
.
�[0m13:26:15.271484 [debug] [Thread-1  ]: Timing info for model.dbt_prototype_version.event (execute): 2023-08-16 13:26:14.022976 => 2023-08-16 13:26:15.271337
�[0m13:26:15.307930 [debug] [Thread-1  ]: Database Error in model event (models/event.sql)
  Code: 62.
  DB::Exception: Empty query. Stack trace:
  
  0. DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0xe18d895 in /opt/bitnami/clickhouse/bin/clickhouse
  1. ? @ 0x8d3f6cd in /opt/bitnami/clickhouse/bin/clickhouse
  2. DB::parseQueryAndMovePosition(DB::IParser&, char const*&, char const*, String const&, bool, unsigned long, unsigned long) @ 0x14fdc6bf in /opt/bitnami/clickhouse/bin/clickhouse
  3. ? @ 0x13b1697b in /opt/bitnami/clickhouse/bin/clickhouse
  4. DB::executeQuery(String const&, std::shared_ptr<DB::Context>, bool, DB::QueryProcessingStage::Enum) @ 0x13b1634d in /opt/bitnami/clickhouse/bin/clickhouse
  5. DB::TCPHandler::runImpl() @ 0x148e80ac in /opt/bitnami/clickhouse/bin/clickhouse
  6. DB::TCPHandler::run() @ 0x148fd859 in /opt/bitnami/clickhouse/bin/clickhouse
  7. Poco::Net::TCPServerConnection::start() @ 0x17838054 in /opt/bitnami/clickhouse/bin/clickhouse
  8. Poco::Net::TCPServerDispatcher::run() @ 0x1783927b in /opt/bitnami/clickhouse/bin/clickhouse
  9. Poco::PooledThread::run() @ 0x179c0b07 in /opt/bitnami/clickhouse/bin/clickhouse
  10. Poco::ThreadImpl::runnableEntry(void*) @ 0x179be53d in /opt/bitnami/clickhouse/bin/clickhouse
  11. start_thread @ 0x7ea7 in /lib/x86_64-linux-gnu/libpthread-2.31.so
  12. clone @ 0xfca2f in /lib/x86_64-linux-gnu/libc-2.31.so
  compiled Code at target/run/dbt_prototype_version/models/event.sql

The file target/run/dbt_prototype_version/models/event.sql is empty.

Same error occurs when I retry the dbt run again.

(You can reproduce my scenario by replacing the S3 file by any other data source.)

gfunc · 2023-08-17T02:06:44Z

I will try to reproduce this with the NYC taxi data example. Looks like sth is broken before the actual SQL run.

zli06160 · 2023-08-17T12:59:45Z

@gfunc Exactly, the target/run/... file is complied to empty, maybe there is a wrong when/if condition in a macro file.

BTW, You can reproduce it with just an existing clickhouse table as well.

gfunc · 2023-08-17T19:15:22Z

raised a merge request for this specific case. In the meanwhile creating test cases for distributed materializations.

genzgd · 2023-08-17T20:23:01Z

@zli06160 Can you possibly check to see if changing the macro as in the PR #180 fixes your situation, since we don't have tests? Some additional validation would be nice before we merge and release the fix.

gfunc · 2023-08-18T07:25:34Z

Hi @genzgd , before I start to get distributed materialization tested, I started off trying to run unit tests with cluster setting in the profile.
I found one thing a bit confusing: running tests from test_basic.py, tables created with an "on cluster" clause, but the insert into will only affect one node. IMO maybe it is best that if the cluster setting is specified dbt adds a Replicated keyword before the engine clause, or we could simply raise an exception.

Please let me know your thoughts on this :)

zli06160 · 2023-08-18T12:57:04Z

@gfunc @genzgd I made some comments in the PR.

When I disable the following bloc in dbt/include/clickhouse/macros/adapters.sql

  {%- if adapter.get_clickhouse_cluster_name() is not none and obj_types == 'TABLES' %}
    {% do run_query("SYSTEM SYNC REPLICA " + on_cluster_clause() + target_relation.schema + '.' + target_relation.identifier) %}
  {%- endif %}

~~or the tests of distributed tables, everything seems ok~~
~~(multiple runs, with full_refresh=false or full_refresh=true).~~

gfunc · 2023-08-18T15:31:26Z

@zli06160 Yes, I've noticed this also when trying pytest with cluster setting. thus I changed this block to

  {%- if adapter.get_clickhouse_cluster_name() is not none and obj_types == 'TABLES' and 'Replicated' in engine_clause() %}
    {% do run_query("SYSTEM SYNC REPLICA " + on_cluster_clause() + target_relation.schema + '.' + target_relation.identifier) %}
  {%- endif %}

It seems like the assumption for the purpose of using a ClickHouse cluster within this repo is to replicate data (Replicated table engines).

zli06160 · 2023-08-18T16:22:21Z

@gfunc There is another problem: the table default_db_test_dbt.event becomes non-distributed table after the 2nd dbt run.

(edit: I deactivated completely the bloc SYSTEM SYNC REPLICA ).

zli06160 · 2023-08-18T16:34:00Z

Here the log, always with the initial sql file.

(drop the database)
.
.
.
(1st dbt run => ok)
.
.
.
(2nd dbt run)
�[0m16:28:25.191052 [debug] [Thread-1  ]: Writing runtime sql for node "model.dbt_prototype_version.event"
�[0m16:28:25.191701 [debug] [Thread-1  ]: dbt_clickhouse adapter: On model.dbt_prototype_version.event: /* {"app": "dbt", "dbt_version": "1.4.6", "profile_name": "stage", "target_name": "clickhouse_stage", "node_id": "model.dbt_prototype_version.event"} */

  create table default_db_test_dbt.eventlocal__dbt_backup

    ON CLUSTER "default"  (
      code Nullable(String), event_id Nullable(String), .....................
  )
  engine = ReplicatedReplacingMergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}') 
  order by ((event_id))
  
  '''
  SETTINGS  allow_nullable_key=1
...
�[0m16:28:25.387290 [debug] [Thread-1  ]: dbt_clickhouse adapter: SQL status: OK in 0.20 seconds
�[0m16:28:25.396881 [debug] [Thread-1  ]: dbt_clickhouse adapter: On model.dbt_prototype_version.event: /* {"app": "dbt", "dbt_version": "1.4.6", "profile_name": "stage", "target_name": "clickhouse_stage", "node_id": "model.dbt_prototype_version.event"} */
EXCHANGE TABLES default_db_test_dbt.eventlocal__dbt_backup AND default_db_test_dbt.event 
  
    ON CLUSTER "default" 
  ...
�[0m16:28:25.537292 [debug] [Thread-1  ]: dbt_clickhouse adapter: SQL status: OK in 0.14 seconds
.
.
.
(3rd run => same logs, same problem)

zli06160 · 2023-08-21T08:06:54Z

FYI, I added my comments in the PR #180.

genzgd · 2023-08-22T22:40:13Z

PR merged into 1.4.8 release.

gfunc · 2023-08-24T12:16:49Z

@zli06160 @genzgd please see #186. I found several more bugs concerning distributed_table and distributed_incremental, and completed tests for cluster setting and distributed materializations.

zli06160 added the bug Something isn't working label Aug 11, 2023

zli06160 mentioned this issue Aug 18, 2023

bug fix #180

Closed

zli06160 mentioned this issue Aug 22, 2023

fix: distributed_table materialization issue #184

Merged

2 tasks

genzgd closed this as completed Aug 22, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Problem with the distributed_table materialization #179

Problem with the distributed_table materialization #179

zli06160 commented Aug 11, 2023 •

edited

Loading

genzgd commented Aug 13, 2023

gfunc commented Aug 16, 2023

zli06160 commented Aug 16, 2023 •

edited

Loading

gfunc commented Aug 17, 2023

zli06160 commented Aug 17, 2023

gfunc commented Aug 17, 2023

genzgd commented Aug 17, 2023

gfunc commented Aug 18, 2023 •

edited

Loading

zli06160 commented Aug 18, 2023 •

edited

Loading

gfunc commented Aug 18, 2023

zli06160 commented Aug 18, 2023 •

edited

Loading

zli06160 commented Aug 18, 2023 •

edited

Loading

zli06160 commented Aug 21, 2023

genzgd commented Aug 22, 2023

gfunc commented Aug 24, 2023

Problem with the distributed_table materialization #179

Problem with the distributed_table materialization #179

Comments

zli06160 commented Aug 11, 2023 • edited Loading

Describe the bug

Steps to reproduce

Configuration

Environment

ClickHouse server

genzgd commented Aug 13, 2023

gfunc commented Aug 16, 2023

zli06160 commented Aug 16, 2023 • edited Loading

gfunc commented Aug 17, 2023

zli06160 commented Aug 17, 2023

gfunc commented Aug 17, 2023

genzgd commented Aug 17, 2023

gfunc commented Aug 18, 2023 • edited Loading

zli06160 commented Aug 18, 2023 • edited Loading

gfunc commented Aug 18, 2023

zli06160 commented Aug 18, 2023 • edited Loading

zli06160 commented Aug 18, 2023 • edited Loading

zli06160 commented Aug 21, 2023

genzgd commented Aug 22, 2023

gfunc commented Aug 24, 2023

zli06160 commented Aug 11, 2023 •

edited

Loading

zli06160 commented Aug 16, 2023 •

edited

Loading

gfunc commented Aug 18, 2023 •

edited

Loading

zli06160 commented Aug 18, 2023 •

edited

Loading

zli06160 commented Aug 18, 2023 •

edited

Loading

zli06160 commented Aug 18, 2023 •

edited

Loading