在测试 ClickHouse 分布式表时，创建分布式表成功，但是查询数据时报错，如下：

Received exception from server (version 22.2.2):
Code: 516. DB::Exception: Received from 192.168.38.101:9000. DB::Exception: Received from 192.168.38.103:9000. DB::Exception: default: Authentication failed: pass
word is incorrect or there is no user with such name. (AUTHENTICATION_FAILED)

排查后发现，是集群开始安装时设置了密码，而配置分布式表时，没有添加各服务器的用户名和密码，所以访问不到别的服务器的数据，在我们的/etc/clickhouse-server/config.d/metrika.xml中，加入用户名和密码即可正常查询数据，如下： 

   
   

     
     

      
      

     
     
     
     

      
      

       
       <clickhouse_remote_servers>
      
      
     
     

     
     

      
      

     
     
     
     

      
      

       
       <!--market_ck_cluster:for market business and Marketing platform-->
      
      
     
     

     
     

      
      

     
     
     
     

      
      

       
       <market_ck_cluster>
      
      
     
     

     
     

      
      

     
     
     
     

      
      
    
       
       <shard>
      
      
     
     

     
     

      
      

     
     
     
     

      
      
        
       
       <internal_replication>true
       
       </internal_replication>
      
      
     
     

     
     

      
      

     
     
     
     

      
      
        
       
       <replica>
      
      
     
     

     
     

      
      

     
     
     
     

      
      
            
       
       <host>192.168.38.101
       
       </host>
      
      
     
     

     
     

      
      

     
     
     
     

      
      
            
       
       <port>9000
       
       </port>
      
      
     
     

     
     

      
      

     
     
     
     

      
      
            
       
       <user>default
       
       </user>
      
      
     
     

     
     

      
      

     
     
     
     

      
      
            
       
       <password>123456
       
       </password>
      
      
     
     

     
     

      
      

     
     
     
     

      
      
 
      
      
     
     

     
     

      
      

     
     
     
     

      
      
         
       
       <password_sha256_hex>8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92
       
       </password_sha256_hex>
      
      
     
     

     
     

      
      

     
     
     
     

      
      
        
       
       </replica>
      
      
     
     

     
     

      
      

     
     
     
     

      
      
        
       
       <replica>
      
      
     
     

     
     

      
      

     
     
     
     

      
      
            
       
       <host>192.168.38.102
       
       </host>
      
      
     
     

     
     

      
      

     
     
     
     

      
      
            
       
       <port>9000
       
       </port>
      
      
     
     

     
     

      
      

     
     
     
     

      
      
            
       
       <user>default
       
       </user>
      
      
     
     

     
     

      
      

     
     
     
     

      
      

       
       <password>123456
       
       </password>          
       
       <password_sha256_hex>8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92
       
       </password_sha256_hex> 
      
      
     
     

     
     

      
      

     
     
     
     

      
      
       
       
       </replica>
      
      
     
     

     
     

      
      

     
     
     
     

      
      
    
       
       </shard>
      
      
     
     

     
     

      
      

     
     
     
     

      
      
    
       
       <shard>
      
      
     
     

     
     

      
      

     
     
     
     

      
      
        
       
       <internal_replication>true
       
       </internal_replication>
      
      
     
     

     
     

      
      

     
     
     
     

      
      
        
       
       <replica>
      
      
     
     

     
     

      
      

     
     
     
     

      
      
            
       
       <host>192.168.38.103
       
       </host>
      
      
     
     

     
     

      
      

     
     
     
     

      
      
            
       
       <port>9000
       
       </port>
      
      
     
     

     
     

      
      

     
     
     
     

      
      
            
       
       <user>default
       
       </user>
      
      
     
     

     
     

      
      

     
     
     
     

      
      

       
       <password>123456
       
       </password>
      
      
     
     

     
     

      
      

     
     
     
     

      
      
 
      
      
     
     

     
     

      
      

     
     
     
     

      
      
            
       
       <password_sha256_hex>8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92
       
       </password_sha256_hex>
      
      
     
     

     
     

      
      

     
     
     
     

      
      

       
       </replica>
      
      
     
     

     
     

      
      

     
     
     
     

      
      
    
       
       </shard>
      
      
     
     

     
     

      
      

     
     
     
     

      
      

       
       </market_ck_cluster>
      
      
     
     

     
     

      
      

     
     
     
     

      
      

       
       </clickhouse_remote_servers>
      
      
     
     
   
   

    
    
   
   
   
   

就是这两行：

   
   

     
     

      
      

     
     
     
     

      
      
 
       
       <user>default
       
       </user>
      
      
     
     

     
     

      
      

     
     
     
     

      
      
 
       
       <password>123456
       
       </password>
      
      
     
     
   
   

 修改完成后，查询分布式成功：

   
   

     
     

      
      

     
     
     
     

      
      

       
       node102 :) 
       
       select dt ,
       
       count(id) 
       
       from dbapi_db.dwd_middle_homework_students_correct_detail_1_all  
       
       group 
       
       by dt 
       
       order 
       
       by dt;
      
      
     
     

     
     

      
      

     
     
     
     

      
      
 
      
      
     
     

     
     

      
      

     
     
     
     

      
      

       
       SELECT
      
      
     
     

     
     

      
      

     
     
     
     

      
      

       
           dt,
      
      
     
     

     
     

      
      

     
     
     
     

      
      
    
       
       count(id)
      
      
     
     

     
     

      
      

     
     
     
     

      
      

       
       FROM dbapi_db.dwd_middle_homework_students_correct_detail_1_all
      
      
     
     

     
     

      
      

     
     
     
     

      
      

       
       GROUP 
       
       BY dt
      
      
     
     

     
     

      
      

     
     
     
     

      
      

       
       ORDER 
       
       BY dt 
       
       ASC
      
      
     
     

     
     

      
      

     
     
     
     

      
      
 
      
      
     
     

     
     

      
      

     
     
     
     

      
      

       
       Query id: 
       
       1409ea4e
       
       -0f74
       
       -417d
       
       -9026
       
       -8409a2e4e887
      
      
     
     

     
     

      
      

     
     
     
     

      
      
 
      
      
     
     

     
     

      
      

     
     
     
     

      
      

       
       ┌─dt─────────┬─
       
       count(id)─┐
      
      
     
     

     
     

      
      

     
     
     
     

      
      

       
       │ 
       
       2022
       
       -06
       
       -09 │    
       
       522346 │
      
      
     
     

     
     

      
      

     
     
     
     

      
      

       
       │ 
       
       2022
       
       -06
       
       -10 │    
       
       460020 │
      
      
     
     

     
     

      
      

     
     
     
     

      
      

       
       └────────────┴───────────┘
      
      
     
     

     
     

      
      

     
     
     
     

      
      
 
      
      
     
     

     
     

      
      

     
     
     
     

      
      

       
       2 
       
       rows 
       
       in set. Elapsed: 
       
       0.052 sec. Processed 
       
       982.37 thousand 
       
       rows, 
       
       51.08 MB (
       
       19.05 million 
       
       rows
       
       /s., 
       
       990.39 MB
       
       /s.)
      
      
     
     
   
   

    
    
   
   
   
   

问题解决，开始开心的学习.....