С сервера с которого пытаюсь создать таблицу 1) data-05 2) no node 3) no node. С сервера на котором есть CH_CV_FACT_ONDATE_KKD 1) data-01 2)  путь есть 3) в списке нод отсутствует data-05. Т.е. задача в том, что есть пару нод с replicated таблицами пытаюсь поднять еще одну (просто выполнив из system.tables.create_table_query существующей ноды)

2) no node 3) no node
2)  путь есть 3) в списке нод отсутствует data-05

как это возможно? У зукипера одна и таже база. Отовсюду будет видно одно и тоже
какая фигня с зукипером у вас

/clickhouse/tables/CH_CV_FACT_ONDATE_KKD'

путь без шарда, вы один шард хотите сделать ? правильно я понимаю?

drop table if exists testreplicatedmt ON CLUSTER 'mycluster';

create table testreplicatedmt ON CLUSTER 'mycluster' (A Int64, S String) 
Engine=ReplicatedMergeTree('/clickhouse/mycluster/default/testreplicatedmt', '{replica}') 
order by A;

create table testreplicatedmt_dist ON CLUSTER 'mycluster' (A Int64, S String) 
ENGINE = Distributed('mycluster', 'default', 'testreplicatedmt', rand());

insert into testreplicatedmt select number, toString(cityHash64(number)) from numbers(100000000);
select hostName() as hstname, count(A) from clusterAllReplicas('mycluster', default.testreplicatedmt) group by hostName();
optimize table testreplicatedmt final;
select count(*) from system.parts where table='testreplicatedmt' and active;
-- 1

set prefer_localhost_replica=1;
select top 20 A, S from testreplicatedmt order by S Format Null;
--0 rows in set. Elapsed: 0.418 sec. Processed 100.00 million rows, 3.64 GB (239.20 million rows/s., 8.71 GB/s.)
select top 20 A, S from testreplicatedmt_dist order by S Format Null; 
--0 rows in set. Elapsed: 4.294 sec. Processed 100.00 million rows, 3.64 GB (23.29 million rows/s., 847.62 MB/s.)

на 20.3
куда смотреть?

да, проблема с зукипером, спасибо за потраченное время

drop table if exists testreplicatedmt ON CLUSTER 'mycluster';

create table testreplicatedmt ON CLUSTER 'mycluster' (A Int64, S String) 
Engine=ReplicatedMergeTree('/clickhouse/mycluster/default/testreplicatedmt', '{replica}') 
order by A;

create table testreplicatedmt_dist ON CLUSTER 'mycluster' (A Int64, S String) 
ENGINE = Distributed('mycluster', 'default', 'testreplicatedmt', rand());

insert into testreplicatedmt select number, toString(cityHash64(number)) from numbers(100000000);
select hostName() as hstname, count(A) from clusterAllReplicas('mycluster', default.testreplicatedmt) group by hostName();
optimize table testreplicatedmt final;
select count(*) from system.parts where table='testreplicatedmt' and active;
-- 1

set prefer_localhost_replica=1;
select top 20 A, S from testreplicatedmt order by S Format Null;
--0 rows in set. Elapsed: 0.418 sec. Processed 100.00 million rows, 3.64 GB (239.20 million rows/s., 8.71 GB/s.)
select top 20 A, S from testreplicatedmt_dist order by S Format Null; 
--0 rows in set. Elapsed: 4.294 sec. Processed 100.00 million rows, 3.64 GB (23.29 million rows/s., 847.62 MB/s.)

а сколько серверов в кластере?

SET send_logs_level = 'debug'

4, 2 заполнены, 2 не долго едут в другой стране

в смысле запрос выполняется и на репликах которые далеко?

drop table if exists testreplicatedmt ON CLUSTER 'mycluster';

create table testreplicatedmt ON CLUSTER 'mycluster' (A Int64, S String) 
Engine=ReplicatedMergeTree('/clickhouse/mycluster/default/testreplicatedmt', '{replica}') 
order by A;

create table testreplicatedmt_dist ON CLUSTER 'mycluster' (A Int64, S String) 
ENGINE = Distributed('mycluster', 'default', 'testreplicatedmt', rand());

insert into testreplicatedmt select number, toString(cityHash64(number)) from numbers(100000000);
select hostName() as hstname, count(A) from clusterAllReplicas('mycluster', default.testreplicatedmt) group by hostName();
optimize table testreplicatedmt final;
select count(*) from system.parts where table='testreplicatedmt' and active;
-- 1

set prefer_localhost_replica=1;
select top 20 A, S from testreplicatedmt order by S Format Null;
--0 rows in set. Elapsed: 0.418 sec. Processed 100.00 million rows, 3.64 GB (239.20 million rows/s., 8.71 GB/s.)
select top 20 A, S from testreplicatedmt_dist order by S Format Null; 
--0 rows in set. Elapsed: 4.294 sec. Processed 100.00 million rows, 3.64 GB (23.29 million rows/s., 847.62 MB/s.)

сталкивался на 20.3 с тем, что запросы на шардах выполнялись по очереди

нет, локалхост реплика в преферред

шардов нет

специально пробую без шардов

таблица напрямую:

[hostname] 2020.09.29 21:05:35.041301 [ 6549 ] {8c8d6e07-3812-4741-afa8-7d41aeb9b396} <Debug> executeQuery: (from [::1]:39486) SELECT A, S FROM default.testreplicatedmt ORDER BY S ASC LIMIT 20 FORMAT Null
→ Progress: 0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.) [hostname] 2020.09.29 21:05:35.041630 [ 6549 ] {8c8d6e07-3812-4741-afa8-7d41aeb9b396} <Trace> AccessRightsContext (default): Access granted: SELECT(A, S) ON default.testreplicatedmt
[hostname] 2020.09.29 21:05:35.041763 [ 6549 ] {8c8d6e07-3812-4741-afa8-7d41aeb9b396} <Debug> default.testreplicatedmt (SelectExecutor): Key condition: unknown
[hostname] 2020.09.29 21:05:35.041804 [ 6549 ] {8c8d6e07-3812-4741-afa8-7d41aeb9b396} <Debug> default.testreplicatedmt (SelectExecutor): Selected 1 parts by date, 1 parts by key, 12208 marks to read from 1 ranges
[hostname] 2020.09.29 21:05:35.041888 [ 6549 ] {8c8d6e07-3812-4741-afa8-7d41aeb9b396} <Trace> default.testreplicatedmt (SelectExecutor): Reading approx. 100007936 rows with 12 streams
[hostname] 2020.09.29 21:05:35.041993 [ 6549 ] {8c8d6e07-3812-4741-afa8-7d41aeb9b396} <Trace> InterpreterSelectQuery: FetchColumns -> Complete
[hostname] 2020.09.29 21:05:36.460782 [ 6549 ] {8c8d6e07-3812-4741-afa8-7d41aeb9b396} <Information> executeQuery: Read 100000000 rows, 3.39 GiB in 1.419 sec., 70453437 rows/sec., 2.39 GiB/sec.
[hostname] 2020.09.29 21:05:36.460876 [ 6549 ] {8c8d6e07-3812-4741-afa8-7d41aeb9b396} <Debug> MemoryTracker: Peak memory usage (for query): 50.81 MiB.
Ok.

0 rows in set. Elapsed: 1.430 sec. Processed 100.00 million rows, 3.64 GB (69.95 million rows/s., 2.55 GB/s.)

и как описан mycluster?

distributed:

[hostname] 2020.09.29 21:05:43.685627 [ 6549 ] {aa5dd02c-08ff-424b-a2e3-a45de1051251} <Debug> executeQuery: (from [::1]:39486) SELECT A, S FROM default.testreplicatedmt_dist ORDER BY S ASC LIMIT 20 FORMAT Null
↗️ Progress: 0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.) [hostname] 2020.09.29 21:05:43.685994 [ 6549 ] {aa5dd02c-08ff-424b-a2e3-a45de1051251} <Trace> AccessRightsContext (default): Access granted: SELECT(A, S) ON default.testreplicatedmt_dist
[hostname] 2020.09.29 21:05:43.686150 [ 6549 ] {aa5dd02c-08ff-424b-a2e3-a45de1051251} <Trace> AccessRightsContext (default): Access granted: SELECT(A, S) ON default.testreplicatedmt_dist
[hostname] 2020.09.29 21:05:43.686278 [ 6549 ] {aa5dd02c-08ff-424b-a2e3-a45de1051251} <Trace> AccessRightsContext (default): Access granted: SELECT(A, S) ON default.testreplicatedmt
[hostname] 2020.09.29 21:05:43.686368 [ 6549 ] {aa5dd02c-08ff-424b-a2e3-a45de1051251} <Debug> default.testreplicatedmt (SelectExecutor): Key condition: unknown
[hostname] 2020.09.29 21:05:43.686403 [ 6549 ] {aa5dd02c-08ff-424b-a2e3-a45de1051251} <Debug> default.testreplicatedmt (SelectExecutor): Selected 1 parts by date, 1 parts by key, 12208 marks to read from 1 ranges
[hostname] 2020.09.29 21:05:43.686473 [ 6549 ] {aa5dd02c-08ff-424b-a2e3-a45de1051251} <Trace> default.testreplicatedmt (SelectExecutor): Reading approx. 100007936 rows with 12 streams
[hostname] 2020.09.29 21:05:43.686561 [ 6549 ] {aa5dd02c-08ff-424b-a2e3-a45de1051251} <Trace> InterpreterSelectQuery: FetchColumns -> Complete
[hostname] 2020.09.29 21:05:43.686830 [ 6549 ] {aa5dd02c-08ff-424b-a2e3-a45de1051251} <Trace> InterpreterSelectQuery: Complete -> Complete
[hostname] 2020.09.29 21:05:54.653891 [ 6549 ] {aa5dd02c-08ff-424b-a2e3-a45de1051251} <Information> executeQuery: Read 100000000 rows, 3.39 GiB in 10.968 sec., 9117321 rows/sec., 316.48 MiB/sec.
[hostname] 2020.09.29 21:05:54.653994 [ 6549 ] {aa5dd02c-08ff-424b-a2e3-a45de1051251} <Debug> MemoryTracker: Peak memory usage (for query): 8.24 MiB.
Ok.

0 rows in set. Elapsed: 10.970 sec. Processed 100.00 million rows, 3.64 GB (9.12 million rows/s., 331.80 MB/s.)

    <mycluster>
      <shard>
         <replica>
                <host>host-01-ro.domain.net</host>
                <port>9000</port>
        </replica>
        <replica>
                <host>host-02-ro.domain.net</host>
                <port>9000</port>
        </replica>
                <replica>
                <host>host-03-tr.domain.net</host>
                <port>9000</port>
        </replica>
        <replica>
                <host>host-04-tr.domain.net</host>
                <port>9000</port>
        </replica>
          </shard>
    </mycluster>

может ли он "не мочь" определить локалхост из за того что в конфиге FQDN, а hostName() берет из host?

    <mycluster>
      <shard>
         <replica>
                <host>host-01-ro.domain.net</host>
                <port>9000</port>
        </replica>
        <replica>
                <host>host-02-ro.domain.net</host>
                <port>9000</port>
        </replica>
                <replica>
                <host>host-03-tr.domain.net</host>
                <port>9000</port>
        </replica>
        <replica>
                <host>host-04-tr.domain.net</host>
                <port>9000</port>
        </replica>
          </shard>
    </mycluster>

а если проверить это в системных таблицах?
SELECT * FROM system.replicas;

там теперь не видно

is_local будет 1 у всех реплик