最全的clickhouse的增删改查以及导入导出操作

左手的ㄟ右手 2023-03-13 02:14 13阅读 0赞

**下面的相关代码，全是基于生产环境部署实践的，**

**---客户端链接工具**  
clickhouse-client -m -u root -p root

**查询正在进行执行的sql操作**  
SHOW PROCESSLIST

**建表语句**

create TABLE test.test( id Int32,create\_date Date ,c2 Nullable(String) )   
engine=MergeTree(create\_date,id,(c3,c2),8192);

**表变更预计**

ALTER查询只支持MergeTree表，。该查询有几个变体。  
ALTER TABLE \[db\].name \[ON CLUSTER cluster\] ADD|DROP|MODIFY COLUMN  
\--更改列的类型

alter TABLE test.ontime\_wpp\_t MODIFY COLUMN TailNum Nullable(String);

alter TABLE test.ontime\_wpp\_t ADD COLUMN TailNum2 Nullable(String)  after   Div5TailNum ;

alter TABLE test.ontime\_wpp\_t drop COLUMN TailNum2;

**表变更数据系统监控**  
select \* from system.mutations where is\_done = 0 order by create\_time desc limit 1;

**删除表**  
drop table cdm\_dwd.dwd\_ord\_car\_sharing\_df on cluster crm\_4shards\_1replicas;

**删除数据**

truncate table cdm\_dwd.dwd\_ord\_car\_sharing\_df on cluster crm\_4shards\_1replicas;

**变更表名**  
RENAME TABLE test.ontime\_wpp\_t to test.ontime\_wpp\_t2;

**集群操作**  
RENAME TABLE cdm\_dwd.dwd\_ord\_carsh\_base\_df2 to cdm\_dwd.dwd\_ord\_carsh\_base\_df on cluster crm\_4shards\_1replicas;

**1，数据导出**  
在相关节点执行：  
echo 'select \* from test.ads\_user\_portrait\_vertical\_df\_cls' | curl localhost:8123?database=test -uroot:root -d @- > table\_name.sql

**2、导入数据，以tab作为分割符：**

导入数据库的本机执行：

cat table\_name.sql | clickhouse-client --query="INSERT INTO database.table\_name FORMAT TabSeparated"  
       
  
cat /root/user\_lable\_local\_mid\_cluster.tgz | clickhouse-client --user hadoop --password hadoop --query="INSERT INTO lmmbase.user\_lable\_local\_mid\_cluster FORMAT CSV";  
cat /home/hadoop/work\_wpp/user\_label\_uid\_cluster | clickhouse-client --user hadoop --password hadoop --query="INSERT INTO lmmbase.user\_label\_uid\_cluster FORMAT CSV";

cat /tmp/test\_user2| clickhouse-client --user hadoop --password hadoop --query="INSERT INTO lmmbase.test\_user2 FORMAT CSV";

**插入语句**  
不严格插入数据，没有出现的列自动填充为默认值  
INSERT INTO \[db.\]table \[(c1, c2, c3)\] VALUES (v11, v12, v13), (v21, v22)

严格插入数据，每一列都必须出现在上面  
INSERT INTO \[db.\]table \[(c1, c2, c3)\] FORMAT Values (v11, v12, v13), (v21, v22, v23)

cat /tmp/user\_point\_info | clickhouse-client --query="INSERT INTO test.user\_point\_info FORMAT CSV";

clickhouse-client -m --user hadoop --password hadoop --query="truncate table lmmbase.user\_label\_uid on cluster crm\_4shards\_1replicas";

ssh hadoop@dn1 "/bin/bash /home/hadoop/app/otherApp/truncate\_user\_label\_uid\_data.sh"

clickhouse-client --query=" alter table  test.ads\_user\_portrait\_vertical\_df delete where create\_time ='2019-10-17' ";

**                              相关压测，同时执行相关sql，看下机器负载**  
\*/2 \* \* \* \* clickhouse-client -m --query="select t\_mac,t\_type,count(\*) cnt from carendpoint\_porlog\_cls group by t\_mac,t\_type order by cnt desc limit 100;"  
\*/2 \* \* \* \* clickhouse-client  -m --query="select t\_mac,count(\*) cnt from carendpoint\_porlog\_cls group by t\_mac order by cnt desc limit 100;"  
\*/2 \* \* \* \* clickhouse-client  -m --query="select t\_type,count(\*) cnt from carendpoint\_porlog\_cls group by t\_type order by cnt desc limit 100;"

\*/1 \* \* \* \* clickhouse-client  -m --query="select t\_ip,t\_type,count(\*) cnt from carendpoint\_porlog\_cls group by t\_ip,t\_type order by cnt desc limit 100;" >> /root/wpp1.log  
\*/1 \* \* \* \* clickhouse-client  -m --query="select t\_ip,count(\*) cnt from carendpoint\_porlog\_cls group by t\_ip order by cnt desc limit 100;" >> /root/wpp2.log  
\*/1 \* \* \* \* clickhouse-client  -m --query="select event,count(\*) cnt from carendpoint\_porlog\_cls group by event order by cnt desc limit 100;" >> /root/wpp2.log

有兴趣的可以加本人微信号:  wangpengpeng0525

码字不易，有闲钱可以打赏杯奶茶喝

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dwcHdwcDE_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dwcHdwcDE_size_16_color_FFFFFF_t_70]: /images/20230312/bc34ba736ccd4ff3aa44e595948967eb.png