search

ClickHouse / dbt-clickhouse

The Clickhouse plugin for dbt (data build tool)
Apache License 2.0
253 stars 113 forks source link

Cannot execute DBT on a CH cluster #354

Closed LPauzies closed 2 months ago

LPauzies commented 2 months ago

Describe the bug

I'm building a ClickHouse cluster and for testing purpose, we will need to have the same number of topologies in a dev environment to mimic the production.

When I try running DBT in my remote dev server I got this message in my clickhouse logs : 

(NO_REMOTE_SHARD_AVAILABLE) (version 23.8.4.69 (official build)) (from 127.0.0.1:57994) (in query: /* {"app": "dbt", "dbt_version": "1.7.17", "profile_name": "profiles", "target_name": "local", "connection_name": "list__replay"} */
 select t.name as name, t.database as schema, multiIf( engine in ('MaterializedView', 'View'), 'view', engine = 'Dictionary', 'dictionary', 'table' ) as type, db.engine as db_engine,count(distinct _shard_num) > 1 as is_on_cluster from clusterAllReplicas("{cluster}", system.tables) as t join system.databases as db on t.database = db.name where schema = 'replay' group by name, schema, type, db_engine ), Stack trace (when copying this message, always include the lines below):

0. DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0x000000000c64d1d7 in /usr/bin/clickhouse
1. DB::NetException::NetException<String&>(int, FormatStringHelperImpl<std::type_identity<String&>::type>, String&) @ 0x000000001296bf6d in /usr/bin/clickhouse
2. DB::getStructureOfRemoteTable(DB::Cluster const&, DB::StorageID const&, std::shared_ptr<DB::Context const>, std::shared_ptr<DB::IAST> const&) @ 0x000000001296bd11 in /usr/bin/clickhouse
3. DB::TableFunctionRemote::getActualTableStructure(std::shared_ptr<DB::Context const>, bool) const @ 0x00000000109ab562 in /usr/bin/clickhouse
4. DB::TableFunctionRemote::executeImpl(std::shared_ptr<DB::IAST> const&, std::shared_ptr<DB::Context const>, String const&, DB::ColumnsDescription, bool) const @ 0x00000000109aa727 in /usr/bin/clickhouse
5. DB::ITableFunction::execute(std::shared_ptr<DB::IAST> const&, std::shared_ptr<DB::Context const>, String const&, DB::ColumnsDescription, bool, bool) const @ 0x0000000010c40043 in /usr/bin/clickhouse
6. DB::Context::executeTableFunction(std::shared_ptr<DB::IAST> const&, DB::ASTSelectQuery const*) @ 0x0000000011664957 in /usr/bin/clickhouse
7. DB::JoinedTables::getLeftTableStorage() @ 0x0000000011fe3096 in /usr/bin/clickhouse
8. DB::InterpreterSelectQuery::InterpreterSelectQuery(std::shared_ptr<DB::IAST> const&, std::shared_ptr<DB::Context> const&, std::optional<DB::Pipe>, std::shared_ptr<DB::IStorage> const&, DB::SelectQueryOptions const&, std::vector<String, std::allocator<String>> const&, std::shared_ptr<DB::StorageInMemoryMetadata const> const&, std::shared_ptr<DB::PreparedSets>) @ 0x0000000011eedf03 in /usr/bin/clickhouse
9. DB::InterpreterSelectWithUnionQuery::InterpreterSelectWithUnionQuery(std::shared_ptr<DB::IAST> const&, std::shared_ptr<DB::Context>, DB::SelectQueryOptions const&, std::vector<String, std::allocator<String>> const&) @ 0x0000000011fa0f28 in /usr/bin/clickhouse
10. DB::InterpreterFactory::get(std::shared_ptr<DB::IAST>&, std::shared_ptr<DB::Context>, DB::SelectQueryOptions const&) @ 0x0000000011ea7f3e in /usr/bin/clickhouse
11. DB::executeQueryImpl(char const*, char const*, std::shared_ptr<DB::Context>, bool, DB::QueryProcessingStage::Enum, DB::ReadBuffer*) @ 0x00000000122ea20a in /usr/bin/clickhouse
12. DB::executeQuery(String const&, std::shared_ptr<DB::Context>, bool, DB::QueryProcessingStage::Enum) @ 0x00000000122e61f5 in /usr/bin/clickhouse
13. DB::TCPHandler::runImpl() @ 0x000000001315add9 in /usr/bin/clickhouse
14. DB::TCPHandler::run() @ 0x000000001316d059 in /usr/bin/clickhouse
15. Poco::Net::TCPServerConnection::start() @ 0x0000000015b635d4 in /usr/bin/clickhouse
16. Poco::Net::TCPServerDispatcher::run() @ 0x0000000015b647d1 in /usr/bin/clickhouse
17. Poco::PooledThread::run() @ 0x0000000015c9b007 in /usr/bin/clickhouse
18. Poco::ThreadImpl::runnableEntry(void*) @ 0x0000000015c992dc in /usr/bin/clickhouse
19. start_thread @ 0x0000000000007fa3 in /usr/lib/x86_64-linux-gnu/libpthread-2.28.so
20. ? @ 0x00000000000f906f in /usr/lib/x86_64-linux-gnu/libc-2.28.so

2024.09.05 17:48:39.108627 [ 31400 ] {d2128d2d-cc57-47b9-b9a8-6f7ad0aa5d4a} <Error> TCPHandler: Code: 519. DB::NetException: All attempts to get table structure failed. Log: 

Code: 102. DB::NetException: Unexpected packet from server hostname-dev-01:9440 (expected Hello or Exception, got Unknown packet). (UNEXPECTED_PACKET_FROM_SERVER) (version 23.8.4.69 (official build))

Here is the message I got when I'm running it on my local against remote server : 

dbt run --target dev-profile --project-dir ./transform_dbt --profiles-dir ./transform_dbt
17:54:46  Running with dbt=1.7.17
17:54:46  Registered adapter: clickhouse=1.7.7
17:54:46  Unable to do partial parsing because config vars, config profile, or config target have changed
17:54:46  Unable to do partial parsing because profile has changed
17:54:49  Found 47 models, 176 tests, 74 sources, 0 exposures, 0 metrics, 937 macros, 0 groups, 0 semantic models
17:54:49  
17:54:52  
17:54:52  Finished running  in 0 hours 0 minutes and 3.04 seconds (3.04s).
17:54:52  Encountered an error:
Database Error
  Code: 519.
  DB::NetException. DB::NetException: All attempts to get table structure failed. Log: 

  Code: 102. DB::NetException: Unexpected packet from server hostname-dev-01:9440 (expected Hello or Exception, got Unknown packet). (UNEXPECTED_PACKET_FROM_SERVER) (version 23.8.4.69 (official build))

  . Stack trace:

  0. DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0x000000000c64d1d7 in /usr/bin/clickhouse
  1. DB::NetException::NetException<String&>(int, FormatStringHelperImpl<std::type_identity<String&>::type>, String&) @ 0x000000001296bf6d in /usr/bin/clickhouse
  2. DB::getStructureOfRemoteTable(DB::Cluster const&, DB::StorageID const&, std::shared_ptr<DB::Context const>, std::shared_ptr<DB::IAST> const&) @ 0x000000001296bd11 in /usr/bin/clickhouse
  3. DB::TableFunctionRemote::getActualTableStructure(std::shared_ptr<DB::Context const>, bool) const @ 0x00000000109ab562 in /usr/bin/clickhouse
  4. DB::TableFunctionRemote::executeImpl(std::shared_ptr<DB::IAST> const&, std::shared_ptr<DB::Context const>, String const&, DB::ColumnsDescription, bool) const @ 0x00000000109aa727 in /usr/bin/clickhouse
  5. DB::ITableFunction::execute(std::shared_ptr<DB::IAST> const&, std::shared_ptr<DB::Context const>, String const&, DB::ColumnsDescription, bool, bool) const @ 0x0000000010c40043 in /usr/bin/clickhouse
  6. DB::Context::executeTableFunction(std::shared_ptr<DB::IAST> const&, DB::ASTSelectQuery const*) @ 0x0000000011664957 in /usr/bin/clickhouse
  7. DB::JoinedTables::getLeftTableStorage() @ 0x0000000011fe3096 in /usr/bin/clickhouse
  8. DB::InterpreterSelectQuery::InterpreterSelectQuery(std::shared_ptr<DB::IAST> const&, std::shared_ptr<DB::Context> const&, std::optional<DB::Pipe>, std::shared_ptr<DB::IStorage> const&, DB::SelectQueryOptions const&, std::vector<String, std::allocator<String>> const&, std::shared_ptr<DB::StorageInMemoryMetadata const> const&, std::shared_ptr<DB::PreparedSets>) @ 0x0000000011eedf03 in /usr/bin/clickhouse
  9. DB::InterpreterSelectWithUnionQuery::InterpreterSelectWithUnionQuery(std::shared_ptr<DB::IAST> const&, std::shared_ptr<DB::Context>, DB::SelectQueryOptions const&, std::vector<String, std::allocator<String>> const&) @ 0x0000000011fa0f28 in /usr/bin/clickhouse
  10. DB::InterpreterFactory::get(std::shared_ptr<DB::IAST>&, std::shared_ptr<DB::Context>, DB::SelectQueryOptions const&) @ 0x0000000011ea7f3e in /usr/bin/clickhouse
  11. DB::executeQueryImpl(char const*, char const*, std::shared_ptr<DB::Context>, bool, DB::QueryProcessingStage::Enum, DB::ReadBuffer*) @ 0x00000000122ea20a in /usr/bin/clickhouse
  12. DB::executeQuery(String const&, std::shared_ptr<DB::Context>, bool, DB::QueryProcessingStage::Enum) @ 0x00000000122e61f5 in /usr/bin/clickhouse
  13. DB::TCPHandler::runImpl() @ 0x000000001315add9 in /usr/bin/clickhouse
  14. DB::TCPHandler::run() @ 0x000000001316d059 in /usr/bin/clickhouse
  15. Poco::Net::TCPServerConnection::start() @ 0x0000000015b635d4 in /usr/bin/clickhouse
  16. Poco::Net::TCPServerDispatcher::run() @ 0x0000000015b647d1 in /usr/bin/clickhouse
  17. Poco::PooledThread::run() @ 0x0000000015c9b007 in /usr/bin/clickhouse
  18. Poco::ThreadImpl::runnableEntry(void*) @ 0x0000000015c992dc in /usr/bin/clickhouse
  19. start_thread @ 0x0000000000007fa3 in /usr/lib/x86_64-linux-gnu/libpthread-2.28.so
  20. ? @ 0x00000000000f906f in /usr/lib/x86_64-linux-gnu/libc-2.28.so

Here is the cluster.xml

<clickhouse>
    <default_replica_path>/clickhouse/tables/{cluster}/{uuid}/{shard}</default_replica_path>

    <keeper_server>
        <tcp_port>2181</tcp_port>
        <server_id>1</server_id>
        <log_storage_path>/var/lib/clickhouse/coordination/log</log_storage_path>
        <snapshot_storage_path>/var/lib/clickhouse/coordination/snapshots</snapshot_storage_path>
        <listen_host>localhost</listen_host>

        <coordination_settings>
            <operation_timeout_ms>10000</operation_timeout_ms>
            <session_timeout_ms>30000</session_timeout_ms>
            <raft_logs_level>trace</raft_logs_level>
        </coordination_settings>

        <raft_configuration>
            <server>
                <id>1</id>
                <hostname>hostname-dev</hostname>
                <port>9444</port>
            </server>
        </raft_configuration>
    </keeper_server>

    <zookeeper>
        <node>
            <host>localhost</host>
            <port>2181</port>
        </node>
        <session_timeout_ms>30000</session_timeout_ms>
        <operation_timeout_ms>10000</operation_timeout_ms>
    </zookeeper>

    <distributed_ddl>
        <path>/clickhouse/task_queue/ddl</path>
    </distributed_ddl>

    <macros>
        <cluster>unsharded</cluster>
        <shard>1</shard>
        <replica>hostname-dev</replica>
        <sharded_cluster>sharded</sharded_cluster>
        <sharded_replica>hostname-dev</sharded_replica>
        <sharded_shard>1</sharded_shard>
    </macros>

    <!-- remember the default port (9000) since is used by nginx -->
    <remote_servers replace="1">
        <sharded>
            <shard>
                <replica>
                    <host>hostname-dev</host>
                    <port>9440</port>
                </replica>
            </shard>
        </sharded>
        <unsharded>
            <shard>
                <replica>
                    <host>hostname-dev</host>
                    <port>9440</port>
                </replica>
            </shard>
        </unsharded>
    </remote_servers>
</clickhouse>

Here is the profile.yml I would use : 

flags:
  partial_parse: true

profiles:
  outputs:
    dev-profile:
      type: clickhouse
      driver: native
      secure: true
      verify: false
      schema: dbt
      retries: 3
      cluster: '{cluster}'
      connect_timeout: 100
      send_receive_timeout: 3000
      sync_request_timeout: 15
      threads: 4
      host: localhost
      ### dev
      port: 9433  # ssh port mapping
      user: user 
      password: pwd

Here is the dbt_project.yml : 

name: project
version: 0.1.0
config-version: 2
profile: profiles

model-paths:
  - models

target-path: target  # directory which will store compiled SQL files
clean-targets:  # directories to be removed by `dbt clean`
  - target
  - dbt_packages

models:
  +engine: ReplicatedMergeTree # the default engine
  project:  # project_name
    staging:
      +schema: staging
      +tags:
        - staging

My CH cluster is on a remote server, from which i'm ssh tunneling it. The native port is forwarded on 9433. The connection is going well when I go to the server and log into it using my credentials.

Expected behavior

Running dbt run --target dev-profile --project-dir ./transform_dbt --profiles-dir ./transform_dbt should work the same way I did in local with a docker compose setup.

Here is my dbt-debug : 

dbt debug --target dev-profile --project-dir ./transform_dbt --profiles-dir ./transform_dbt
17:46:04  Running with dbt=1.7.17
17:46:04  dbt version: 1.7.17
17:46:04  python version: 3.11.5
17:46:04  python path: /home/lpauzies/.pyenv/versions/3.11.5/envs/3.11-knowledge/bin/python
17:46:04  os info: Linux-6.8.0-40-generic-x86_64-with-glibc2.35
17:46:04  Using profiles dir at ./transform_dbt
17:46:04  Using profiles.yml file at ./transform_dbt/profiles.yml
17:46:04  Using dbt_project.yml file at transform_dbt/dbt_project.yml
17:46:04  adapter type: clickhouse
17:46:04  adapter version: 1.7.7
17:46:05  Configuration:
17:46:05    profiles.yml file [OK found and valid]
17:46:05    dbt_project.yml file [OK found and valid]
17:46:05  Required dependencies:
17:46:05   - git [OK found]

17:46:05  Connection:
17:46:05    driver: native
17:46:05    host: localhost
17:46:05    port: 9433
17:46:05    user: user
17:46:05    schema: dbt
17:46:05    retries: 3
17:46:05    database_engine: None
17:46:05    cluster_mode: False
17:46:05    secure: True
17:46:05    verify: False
17:46:05    connect_timeout: 100
17:46:05    send_receive_timeout: 3000
17:46:05    sync_request_timeout: 15
17:46:05    compress_block_size: 1048576
17:46:05    compression: 
17:46:05    check_exchange: True
17:46:05    use_lw_deletes: False
17:46:05    allow_automatic_deduplication: False
17:46:05  Registered adapter: clickhouse=1.7.7
17:46:06    Connection test: [OK connection ok]

17:46:06  All checks passed!

Configuration

Environment

ClickHouse server

What do you think about this ? Do I open the same issue into ClickHouse repository ?

Thanks for reading me and for your time :pray:

genzgd commented 2 months ago

The 9440 port you listed in your <remote_servers> is the native "secure" port. You either need to use <secure>1</secure> in the <replica> configuration or use the "non-secure" port that is configured on your ClickHouse server (since you apparently moved it from 9000 to something else due to an nginx conflict?). See https://clickhouse.com/docs/en/engines/table-engines/special/distributed.

In any case, this is not a specific DBT problem -- you should be able to test your access to your cluster just using the ClickHouse Client.