clickhouse-copier: Syntax error (storage definition): failed at position 1 ('rand'): rand().

[KillMyEllie]

Version:
ClickHouse server version 23.6.2.18 (official build).

Command:
clickhouse-copier --config keeper.xml --task-path /etc/clickhouse-server/copier --base-dir /var/log/clickhouse-server

Error:
Logging trace to /var/log/clickhouse-server/clickhouse-copier_20230802114341_444907/log.log
Logging errors to /var/log/clickhouse-server/clickhouse-copier_20230802114341_444907/log.err.log
2023.08.02 11:43:41.090784 [ 444907 ] {} SentryWriter: Sending crash reports is disabled
2023.08.02 11:43:41.091107 [ 444907 ] {} Pipe: Pipe capacity is 1.00 MiB
2023.08.02 11:43:41.184486 [ 444907 ] {} Application: Starting ClickHouse 23.6.2.18 (revision: 54475, git hash: 89f39a7, build id: D0E83BD1974B9B4B1FE300F9D23222CFF56067E2), PID 444907
2023.08.02 11:43:41.184806 [ 444907 ] {} Application: Starting clickhouse-copier (id 20230802114341_444907, host_id ch%2D01#20230802114341_444907, path /var/log/clickhouse-server/clickhouse-copier_20230802114341_444907, revision 54475)
2023.08.02 11:43:41.203990 [ 444907 ] {} ZooKeeperClient: Connected to ZooKeeper at 10.3.1.201:2181 with session_id 189
2023.08.02 11:43:41.205865 [ 444907 ] {} ZooKeeperClient: Keeper feature flag FILTERED_LIST: enabled
2023.08.02 11:43:41.205951 [ 444907 ] {} ZooKeeperClient: Keeper feature flag MULTI_READ: enabled
2023.08.02 11:43:41.205969 [ 444907 ] {} ZooKeeperClient: Keeper feature flag CHECK_NOT_EXISTS: disabled
2023.08.02 11:43:41.205995 [ 444907 ] {} ZooKeeper: Initialized, hosts: 10.3.1.201:2181
2023.08.02 11:43:41.206908 [ 444907 ] {} Application: Loading task description
2023.08.02 11:43:41.208161 [ 444907 ] {} MemoryTracker: Peak memory usage (for query): 0.00 B.
2023.08.02 11:43:41.208357 [ 444907 ] {} ZooKeeperClient: Finalizing session 189. finalization_started: false, queue_finished: false, reason: 'Destructor called'
2023.08.02 11:43:41.211586 [ 444907 ] {} : virtual int DB::ClusterCopierApp::main(const std::vectorstd::string &): Code: 62. DB::Exception: Syntax error (storage definition): failed at position 1 ('rand'): rand(). Expected one of: storage definition, ENGINE, PARTITION BY, PRIMARY KEY, ORDER BY, SAMPLE BY, TTL. (SYNTAX_ERROR), Stack trace (when copying this message, always include the lines below):

0. DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0x000000000e22b5f5 in /usr/bin/clickhouse
1. ? @ 0x0000000008c1890d in /usr/bin/clickhouse
2. DB::parseQueryAndMovePosition(DB::IParser&, char const*&, char const*, String const&, bool, unsigned long, unsigned long) @ 0x000000001539a07c in /usr/bin/clickhouse
3. DB::parseQuery(DB::IParser&, String const&, unsigned long, unsigned long) @ 0x000000001539a257 in /usr/bin/clickhouse
4. DB::TaskTable::TaskTable(DB::TaskCluster&, Poco::Util::AbstractConfiguration const&, String const&, String const&) @ 0x000000000e418aca in /usr/bin/clickhouse
5. DB::TaskCluster::loadTasks(Poco::Util::AbstractConfiguration const&, String const&) @ 0x000000000e40f9d4 in /usr/bin/clickhouse
6. DB::ClusterCopier::init() @ 0x000000000e3b83c7 in /usr/bin/clickhouse
7. DB::ClusterCopierApp::mainImpl() @ 0x000000000e3b5eed in /usr/bin/clickhouse
8. DB::ClusterCopierApp::main(std::vector<String, std::allocator> const&) @ 0x000000000e3b6a3d in /usr/bin/clickhouse
9. Poco::Util::Application::run() @ 0x0000000017c0e186 in /usr/bin/clickhouse
10. Poco::Util::ServerApplication::run(int, char**) @ 0x0000000017c218b9 in /usr/bin/clickhouse
11. mainEntryClickHouseClusterCopier(int, char**) @ 0x000000000e3b6d14 in /usr/bin/clickhouse
12. main @ 0x000000000887cecf in /usr/bin/clickhouse
13. __libc_start_main @ 0x00007f0b45b480b3 in ?
14. _start @ 0x0000000007f8e76e in /usr/bin/clickhouse
    (version 23.6.2.18 (official build))
    2023.08.02 11:43:41.211835 [ 444907 ] {} Application: Uninitializing subsystem: Logging Subsystem
    2023.08.02 11:43:41.212068 [ 444908 ] {} BaseDaemon: Received signal -2
    2023.08.02 11:43:41.212278 [ 444908 ] {} BaseDaemon: Stop SignalListener thread

keeper.xml

<clickhouse>
    <logger>
        <level>trace</level>
        <size>100M</size>
        <count>3</count>
    </logger>
    <zookeeper>
         <node index="1">
             <host>10.3.1.201</host>
             <port>2181</port>
         </node>
     </zookeeper>
</clickhouse>

task.xml

<clickhouse>
    <remote_servers>
        <src>
            <shard>
                    <replica>
                        <host>10.3.1.201</host>
                        <port>9000</port>
                        <user>default</user>
                        <password>somePassword</password>
                    </replica>
            </shard>
            <shard>
                    <replica>
                        <host>10.3.1.202</host>
                        <port>9000</port>
                        <user>default</user>
                        <password>somePassword</password>
                    </replica>
            </shard>
        </src>

        <dst>
            <shard>
                    <replica>
                        <host>10.3.1.203</host>
                        <port>9000</port>
                        <user>default</user>
                        <password>somePassword</password>
                    </replica>
                    <replica>
                        <host>10.3.1.204</host>
                        <port>9000</port>
                        <user>default</user>
                        <password>somePassword</password>
                    </replica>
            </shard>
        </dst>
    </remote_servers>

    <max_workers>2</max_workers>

    <settings_pull>
        <readonly>1</readonly>
    </settings_pull>

    <settings_push>
        <readonly>0</readonly>
    </settings_push>

    <settings>
        <connect_timeout>3</connect_timeout>
        <insert_distributed_sync>1</insert_distributed_sync>
    </settings>

    <tables>
        <table_hits>
            <cluster_pull>src</cluster_pull>
            <database_pull>telegraf</database_pull>
            <table_pull>metrics</table_pull>

            <cluster_push>dst</cluster_push>
            <database_push>telegraf</database_push>
            <table_push>metrics</table_push>

            <sharding_key>rand()</sharding_key>       
       </table_hits>
    </tables>
</clickhouse>

[thgruiz]

got the same error here on 23.3.13.7.altinitystable.. when running clickhouse-copier on a ReplicatedMergeTree
tried to point it to the Distributed, too.. did not work either

[SaltTan]

clickhouse-copier only works with MergeTree tables.
You have to provide the exact engine parameters in the task config file because clickhouse-copier creates intermediate MergeTree tables as part of its process.