ClickHouse / ClickHouse/dbt-clickhouse
Table and Column Comments set up misbehaviour
- Dominant language
- Python
- Stars
- 362
- Forks
- 177
- Avg merge
- 2d 10h
- Merged PRs (30d)
- 8
Description
### Describe the bug
Testing [v1.7.7 Community PRs](https://github.com/ClickHouse/dbt-clickhouse/releases/tag/v1.7.7)
exacly: allows to add a comment in table's or view's metadata by @dstsimokha in https://github.com/ClickHouse/dbt-clickhouse/pull/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
1. 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
```
2. 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
```
3. Build model dbt build --select test_comment_model.sql
4. 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$
```
5. 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:
** 1
** 1
** 180
** 1
Contributor guide
Research direction
Reproduce with models\test_comment_model.sql and models\models.yml, run dbt build --select test_comment_model.sql, and inspect the generated ALTER TABLE SQL in dbt_logs. Confirm that table and column comments reach every ClickHouse replica; the issue proposes separate commands for table and column comments.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- clickhouse, python
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100