clickhouse数据导入导出

 

1.mysql数据导入到clickhouse

create table app_message_80 engine = MergeTree ORDER BY user_id AS SELECT * FROM mysql('192.168.1.14:3306', 'db_admin', 'app_message_80', 'hxl', 'mysql');

这里必须指定order by或是分区

 

2.已经存在的表导入

 

truncate table app_message_80;
全量导入
insert into app_message_80 SELECT * FROM mysql('192.168.1.14:3306', 'db_admin', 'app_message_80', 'hxl', 'mysql');

 

按照条件导入
insert into app_message_80 SELECT * FROM mysql('192.168.1.14:3306', 'db_admin', 'app_message_80', 'hxl', 'mysql') where massive_type = '1003'

 

3.导出

 

SELECT *
FROM metric where app = 'user-web' and resource = '/user/getUserInfo' and month='2024-01-16'
INTO OUTFILE '/home/hxl/tmpfile/metric.csv'
FORMAT CSVWithNames