clickHouse常⽤命令（⼀）
⼀、导⼊数据
1、同步mysql库中表
CREATE TABLE tab1 ENGINE = MergeTree ORDER BY id AS SELECT * FROM mysql('hostip:3306', 'db', 'table', 'user', 'passwd') ；注：id mysql中的主键
2、csv⽂件导⼊clickhouse
2.1、创建表
CREATE TABLE tab1(
eventDate Date,
impid UInt64,
uid String,
idfa String,
imei String
) ENGINE=MergeTree(eventDate, (impid, eventDate), 8192)
⼀般情况下, 都建议使⽤ MergeTree 引擎. 这个引擎必须要有⼀个 Date 的列来作为索引, 即上⾯的 eventDate.
2.2、导⼊CSV数据
cat test.csv | clickhouse-client -u user --password password --query="INSERT INTO db.tab1 FORMAT CSV";
指定分隔符
cat test.csv | clickhouse-client -u user --password password --format_csv_delimiter="|" --query="INSERT INTO db.tab1 FORMAT CSV"；导⼊数据时忽略错误
clickhouse-client --input_format_allow_errors_num=100000 --input_format_allow_errors_ratio=0.2
--input_format_allow_errors_num : 是允许的错误数
-
-input_format_allow_errors_ratio : 是允许的错误率, 范围是 [0-1]
3、采⽤remote函数
insert into db.tab1 select * from remote('⽬标IP',db.table,'user','passwd')
4、clickhouse-copier ⼯具
5、hdfs导⼊数据
ClickHouse与Hadoop的兼容性不好，数据交互还是依靠将数据导出为固定格式的⽂件，然后将⽂件导⼊到ClickHouse中。
准备
创建student.csv⽂件，
添加内容如下：
1,lis
2,wangw
3,zhaos
上传到HDFS
[hadoop01@localhost webapps]# hadoop fs -put student.csv /
5.1、从HDFS读取数据
从HDFS上读取数据类似于将HDFS作为外部存储，然后去拉取HDFS上的数据。
需要⽤到⼀个新的引擎HDFS：
CREATE TABLE hdfs_student_csv(
id Int8,
name String
)
Engine=HDFS('hdfs://hadoop01:9000/student.csv','csv');
但是数据实际上还是在HDFS上，如果关掉HDFS，他就会报错。
5.2、从HDFS导⼊数据
我们想要将读取到的数据保存到本地，只需要将读取数据的表导⼊其他的本地表。
创建⼀张表结构和hdfs_student_csv完全⼀致，但是表引擎⽆所谓。
CREATE TABLE student_local(
id Int8,
name String
)
Engine=TinyLog;
导⼊数据：
insert into student_local select * from hdfs_student_csv;
6、INSERT INTO插⼊数据
主要⽤于向表中添加数据，基本格式如下：
INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), ...
还可以使⽤select来写⼊数据：
INSERT INTO [db.]table [(c1, c2, c3)] SELECT ...
insert into t2 select * from t3
不严格插⼊数据，没有出现的列⾃动填充为默认值
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)
ClickHouse不⽀持的修改数据的查询：UPDATE, DELETE, REPLACE, MERGE, UPSERT, INSERT UPDATE。
⼆、导出数据
1、导出 CSV 数据
clickhouse-client --query="select uid, idfa, imei from (select impid, uid from tab1 where impid >= 15289903030261609347 and impid <= 15289904230261609347) any inner join (select impid, idfa, imei from tab1 where impid >= 15289903030261609347 and impid <= 15289904230261609347) using(impid) format CSV" > 9c9dc608-269b-4f02-b122-ef5dffb2669d.log
三、删除库、表、数据、分区
1、删除库
2、删除表
drop table tabl;
删除集多个节点同⼀张表
drop table tabl on cluster clickhouse_cluster;
3、删除数据
删除表的数据，对主键⽀持的可以，⾮主键有时数据删除有问题
删除指定数据
ALTER TABLE <table> DELETE WHERE <filter expression>
全部删除
您始终必须指定过滤器表达式。如果要通过Mutation删除所有数据，请指定始终为true的内容，例如：
ALTER TABLE <table> DELETE WHERE 1=1
truncate table tabl;
清理集表数据
truncate table lmmbase.user_label_uid on cluster crm_4shards_1replicas;
4、删除分区
按时间分区：
drop table if exists usertoYYYYMM(EventDate)：按⽉分区
toMonday(EventDate)：按周分区
toDate(EventDate)：按天分区
按指定列分区：
PARTITION BY cloumn_name
对分区的操作：
alter table tab1 DROP PARTITION [partition] #删除分区
alter table tab1 DETACH PARTITION [partition]#下线分区
alter table tab1 ATTACH PARTITION [partition]#恢复分区
MergeTree 引擎中删除分区
注意, 默认情况下 mergeTree 引擎是按⽉分区的, 删除分区的格式为 201808
如果想修改为按⽇分区, 则在建表时加上:
ENGINE = MergeTree PARTITION BY eventDate ORDER BY imp_id SETTINGS index_granularity = 8192;
然后就可以:
alter table xxx drop partition '2018-08-08';
默认情况下, Clickhouse 不允许删除分区或表的⼤⼩⼤于 50GB 的分区或表. 可以通过修改server的配置⽂件来永久配置. 也可以临时设置⼀下来删除⽽不⽤重启服务.
永久配置
sudo vim /etc/l
然后注释掉下⾯两⾏
<!-- <max_table_size_to_drop>0</max_table_size_to_drop> -->
<!-- <max_partition_size_to_drop>0</max_partition_size_to_drop> -->
0表⽰不限制. 或者你可以设置为你想限制的最⼤的⼤⼩.
临时设置
创建个标志⽂件:
sudo touch '/home/username/clickhouse/flags/force_drop_table' && sudo chmod 666 '/home/username/clickhouse/flags/force_drop_table'创建好之后, 就可以执⾏上⾯的删除分区或表的命
令了.
四、更新
ALTER只⽀持MergeTree系列，Merge和Distributed引擎的表，基本语法：
ALTER TABLE [db].name [ON CLUSTER cluster] ADD|DROP|MODIFY COLUMN ...
参数解析：
ADD COLUMN – 向表中添加新列
DROP COLUMN – 在表中删除列
MODIFY COLUMN – 更改列的类型
1、更新数据
也可以⽤类似的⽅法进⾏变异（UPDATE）
ALTER TABLE <table> UPDATE column1 = expr1 [, ...] WHERE <filter expression>
2、变更表名
rename table tabl1 to tabl2;
3、添加新列
alter table tab1 add column age UInt32 default 0;
4、更改列的类型
alter table tab1 modify column age UInt16
5、删除age列
alter table tab1 drop column age
6、查看表结构
desc tabl;
五、表创建
1、创建本地表
drop TABLE if exists idc.web_element_detail_dist on CLUSTER idc_cluster ;
drop TABLE if exists idc.web_element_detail on CLUSTER idc_cluster ;
CREATE TABLE if not exists idc.web_element_detail on CLUSTER idc_cluster (
`task_id` UInt64 COMMENT '拨测任务id',
`target` String COMMENT '域名/⽹址',
`target_name` String COMMENT '⽹址名称',
`element` String COMMENT '元素名称',
`report_time` DateTime COMMENT '上报时间',
`net_type` String COMMENT '⽹络接⼊⽅式',
`probe_id` String COMMENT '探针id',
`opt_type` String COMMENT '运营商类型',
`opt_name` String COMMENT '运营商名称',
`province_id` UInt32 COMMENT '省份编码',
`province_name` String COMMENT '省份名称',
`city_id` UInt32 COMMENT '地市编码',
`city_name` String COMMENT '地市名称',
`area_id` UInt32 COMMENT '区县编码',
`area_name` String COMMENT '区县名称',
`busi_type` String COMMENT '业务类型',
`element_num` String COMMENT '元素个数',
`idc_ip` String COMMENT '⽬标ip地址',
`idc_delay` Float32 COMMENT 'idc延迟',
`idc_size` Float32 COMMENT 'idc⼤⼩' ,
`ip_opt_type` String COMMENT '⽬标运营商类型',
`ip_opt_name` String COMMENT '⽬标运营商名称',
`ip_province_id` UInt32 COMMENT '⽬标IP省份编码',
`ip_province_name` String COMMENT '⽬标IP省份名称',
`ip_city_id` UInt32 COMMENT '⽬标IP地市编码',
`ip_city_name` String COMMENT '⽬标IP地市名称',
`ip_area_id` UInt32 COMMENT '⽬标IP区县编码',
`ip_area_name` String COMMENT '⽬标IP区县名称',
`five_min` UInt32,
`ten_min` UInt32,
`half_hour` UInt32,
`one_hour` UInt32,
`four_hour` UInt32,
`half_day` UInt32 ) ENGINE = MergeTree() PARTITION BY (task_id, toYYYYMMDD(report_time)) ORDER BY (target, report_time) SETTINGS index_granularity = 8192;
2、创建分布式表
CREATE TABLE idc.web_element_detail_dist on CLUSTER idc_cluster AS idc.web_element_detail ENGINE = Distributed(idc_cluster, idc, web_element_detail, rand());
六、检查表数据损坏
CHECK TABLE
检查表中的数据是否损坏，他会返回两种结果：
0 – 数据已损坏
1 – 数据完整
该命令只⽀持Log，TinyLog和StripeLog引擎。
七、join 表性能
join 表性能
切记, 要⽤⼤表 join ⼩表. (不知道具体为什么, 从经验上看, ⽤⼤表作为驱动表, 性能远远快于⽤⼩表作为驱动表). (MySQL ⾥的话, 则是⼩表驱动⼤表).
优化 distinct count
之前
select yob, count(), count(distinct uid, idfa, imei) from nginx_bid_log where eventDate='2018-9-1' group by yob;
之后
select yob, count(), count(distinct(sipHash64(concat(uid, idfa, imei)))) from nginx_bid_log where eventDate='2018-9-1' group by yob;
查看数据分布
select histogram(100)(upstream_resp_time) from (select upstream_resp_time from nginx_bid_log where eventDate = '2018-12-13') format CSV;
histogram(100) 表⽰组距100 (即分成100等份的的分布) , 后⾯的 upstream_resp_time 是你的列名, 即按这个列的数据来进⾏统计.
select upstream_resp_time, bar(列名, 最⼩值, 最⼤, step) from tableXX;
显⽰简单的图形.
hex ⼗六进制转换为⼗进制
SELECT reinterpretAsInt64(reverse(unhex('123')));
md5 分区
# ⼀
SELECT reinterpretAsInt64(reverse(unhex(substring(md5_field, 1, 1))));
# ⼆, md5 => hex => ⼗进制 => 取模
SELECT modulo(reinterpretAsInt64(reverse(unhex(substring(md5_field, 1, 1)))), 5);
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论