Table and Column Comments set up misbehaviour #295
Description
Describe the bug
Testing v1.7.7 Community PRs
exacly: allows to add a comment in table's or view's metadata by @dstsimokha in #228
We've found, that generated ALTER TABLE SQL "ignores" ON CLUSTER clause and all changes done only on connection node.
Clickhouse and DBT does not return any errors.
Steps to reproduce
- Create a model: models\test_comment_model.sql
{{
config
(
materialized = 'table',
schema = 'dbt__core',
order_by=('some_id'),
engine='ReplicatedMergeTree',
persist_docs={"relation": true, "columns": true}
)
}}
Select
1 AS some_id,
'----' AS some_column_name,
'++++' AS some_other_column_name
- Create a description: models\models.yml
version: 2
models:
- name: test_comment_model
description: |
test_comment_model description
columns:
- name: some_id
description: some_id description
- name: some_column_name
description: some_column_name description
- name: some_other_column_name
description: some_other_column_name description
- Build model dbt build --select test_comment_model.sql
- Checking SQL in dbt_logs
alter table `dbt__core`.`test_comment_model`
ON CLUSTER "clust"
modify comment $dbt_comment_literal_block$test_comment_model description$dbt_comment_literal_block$,
comment column some_id $dbt_comment_literal_block$some_id description$dbt_comment_literal_block$,
comment column some_column_name $dbt_comment_literal_block$some_column_name description$dbt_comment_literal_block$,
comment column some_other_column_name $dbt_comment_literal_block$some_other_column_name description$dbt_comment_literal_block$
- Check it up in Clickhouse
5.1. columns comments
SELECT hostName() as hostName,`database`, name, `comment`, *
FROM clusterAllReplicas('{cluster}', system.columns)
where database = 'dbt__core'
and `table` = 'test_comment_model'
order by database, name, hostName
hostName |database |name |comment
clickhouse-01|dbt__core|some_column_name |some_column_name description
clickhouse-02|dbt__core|some_column_name |
clickhouse-01|dbt__core|some_id |some_id description
clickhouse-02|dbt__core|some_id |
clickhouse-01|dbt__core|some_other_column_name |some_other_column_name description
clickhouse-02|dbt__core|some_other_column_name |
5.2. tables comments
SELECT hostName() as hostName,`database`, name, `comment`, *
FROM clusterAllReplicas('{cluster}', system.tables)
where database = 'dbt__core'
and name = 'test_comment_model'
order by database, name, hostName
hostName |database |name |comment
-----------------+---------+------------------+-------------------------------+
clickhouse-01|dbt__core|test_comment_model |test_comment_model description
clickhouse-02|dbt__core|test_comment_model |
Expected behaviour
Comments should be on all nodes
Fix proposal
Split single command to two
- table comment
alter table `dbt__core`.`test_comment_model`
ON CLUSTER "clust" modify comment
$dbt_comment_literal_block$test_comment_model description$dbt_comment_literal_block$
- columns comments
alter table `dbt__core`.`test_comment_model`
ON CLUSTER "clust"
comment column some_id $dbt_comment_literal_block$some_id description$dbt_comment_literal_block$,
comment column some_column_name $dbt_comment_literal_block$some_column_name description$dbt_comment_literal_block$,
comment column some_other_column_name $dbt_comment_literal_block$some_other_column_namedescription$dbt_comment_literal_block$
that makes clickhouse behave as expected
dbt and/or ClickHouse server logs
Configuration
Environment
- dbt version:1.7.16
- dbt-clickhouse version:1.7.7
- Python version: 3.9.13
- Operating system: Windows-10-10.0.22621-SP0
ClickHouse server
- ClickHouse Server version: ClickHouse 24.2.1.2248
- ClickHouse Server non-default settings, if any:
** <allow_experimental_live_view>1</allow_experimental_live_view>
** <allow_experimental_nlp_functions>1</allow_experimental_nlp_functions>
** <distributed_ddl_task_timeout>180</distributed_ddl_task_timeout>
** <allow_nondeterministic_mutations>1</allow_nondeterministic_mutations>