最具影响力的数字化技术在线社区

168大数据

 找回密码
 立即注册

QQ登录

只需一步,快速开始

1 2 3 4 5
查看: 86|回复: 0
收起左侧

[ClickHouse] clickhouse 常用命令

[复制链接]

参加活动: 1

组织活动: 62

发表于 2021-2-8 21:21:34 | 显示全部楼层 |阅读模式

马上注册,结交更多数据大咖,获取更多知识干货,轻松玩转大数据

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
一、导入数据
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、删除分区
按时间分区:
toYYYYMM(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/clickhouse-server/config.xml
然后注释掉下面两行
<!-- <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; 
五、表创建
  
[AppleScript] 纯文本查看 复制代码
1、创建本地表[/size][/font][/color][/align]    [font=新宋体][size=2]
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);

#查看所有分区
SELECT
  database,
  table,
  partition,
  name,
  active
FROM system.parts
WHERE table = 'table_name'
Clickhouse删除分区命令: 分区name
alter table sip.ngfw_access_tuple_all_20y DROP PARTITION '2020-05-01';
Clickhouse统计当日数据:
SELECT count() FROM log.netflow WHERE toDate(record_time) = '{}';

#查看库表容量,压缩率等
select
  sum(rows) as row,--总行数
  formatReadableSize(sum(data_uncompressed_bytes)) as ysq,--原始大小
  formatReadableSize(sum(data_compressed_bytes)) as ysh,--压缩大小
  round(sum(data_compressed_bytes) / sum(data_uncompressed_bytes) * 100, 0) ys_rate--压缩率
from system.parts
#查看各库表指标(字节显示):大小,行数,日期,落盘数据大小,压缩前,压缩后大小
select database,
  table,
  sum(bytes) as size,
  sum(rows) as rows,
  min(min_date) as min_date,
  max(max_date) as max_date,
  sum(bytes_on_disk) as bytes_on_disk,
  sum(data_uncompressed_bytes) as data_uncompressed_bytes,
  sum(data_compressed_bytes) as data_compressed_bytes,
  (data_compressed_bytes / data_uncompressed_bytes) * 100 as compress_rate,
  max_date - min_date as days,
  size / (max_date - min_date) as avgDaySize
from system.parts
where active
  and database = 'db_name'
  and table = 'table_name'
  group by database, table

#查看各库表指标(GB显示):大小,行数,日期,落盘数据大小,压缩前,压缩后大小
select
  database,
  table,
  formatReadableSize(size) as size,
  formatReadableSize(bytes_on_disk) as bytes_on_disk,
  formatReadableSize(data_uncompressed_bytes) as data_uncompressed_bytes,
  formatReadableSize(data_compressed_bytes) as data_compressed_bytes,
  compress_rate,
  rows,
  days,
  formatReadableSize(avgDaySize) as avgDaySize
from
 (
   select
      database,
      table,
      sum(bytes) as size,
      sum(rows) as rows,
      min(min_date) as min_date,
      max(max_date) as max_date,
      sum(bytes_on_disk) as bytes_on_disk,
      sum(data_uncompressed_bytes) as data_uncompressed_bytes,
      sum(data_compressed_bytes) as data_compressed_bytes,
      (data_compressed_bytes / data_uncompressed_bytes) * 100 as compress_rate,
      max_date - min_date as days,
      size / (max_date - min_date) as avgDaySize
    from system.parts
    where active
      and database = 'db_name'
      and table = 'tb_name'
    group by
      database,
      table
)

#查看表中数据大小:
SELECT column,
  any(type),
  sum(column_data_compressed_bytes) AS compressed,
  sum(column_data_uncompressed_bytes) AS uncompressed,
  sum(rows)
FROM system.parts_columns
WHERE database = 'db_name'
  and table = 'table_name'
  AND active
GROUP BY column
ORDER BY column ASC

#python 模块地址
/usr/lib64/python2.7/site-packages/clickhouse
#删除表
DROP table db.tb
#全流量元数据建表
"CREATE TABLE IF NOT EXISTS slb.netflow_25E_io_test (src_ip IPv6, src_port UInt16, dst_ip IPv6, dst_port UInt16, app_crc UInt32, request_flow Int64, response_flow Int64, record_time DateTime) ENGINE = MergeTree() PARTITION BY toDate(record_time) ORDER BY record_time SETTINGS index_granularity = 8192"
#批处理 SQL 语句执行,文件插入
#cat 读取文件流,作为 INSERT 数据输入
cat /data/test_fetch.tsv | clickhouse-client --query "INSERT INTO test_fetch FORMAT TSV"
#重定向输出
clickhouse-client --query="SELECT * FROM test_fetch" > /data/test_fetch.tsv"
#多条SQL语句,分号间隔,依次输出
clickhouse-client -h 127.0.0.1 --multiquery --query="SELECT 1;SELECT 2;SELECT 3;"
--host -h 地址
--port 端口
--user -u
--password
--database -d
--query
--multiquery -n
--time -t 打印每条sql执行时间
#建库
CREATE DATABASE IF NOT EXISTS db_name [ENGINE = engine]
#数据库支持的五种引擎
Ordinary 默认
Dictionary 字典引擎
Memory 内存引擎,存放临时数据,此库下的数据表只停留在内存中,不涉及磁盘操作,重启数据消失
Lazy 日志引擎,该数据库下只能使用Log 系列的表引擎
MySQL mysql引擎,该数据库会自动拉取远端MySQL中的数据,并为他们创建MySQL的表引擎的数据表
CREATE DATABASE DB_TEST;
默认数据库实质是磁盘的一个文件目录,建库语句执行后 ck 会在安装路径下创建 DB_TEST 数据库的文件目录
#pwd
/chbase/data
#ls
DB_TEST default system
#删库
DROP DATABASE [IF EXISTS] db_name;
#建表
CREATE TABLE [IF NOT EXISTS] [db_name.]table_name (
name1 [type] [DEFAULT | MATERIALIZED | ALIAS expr],
name2....
.....
) ENGINE = engine;
#复制其他表结构
CREATE TABLE [IF NOT EXISTS] [db_name.]new_tb AS [db_name2.]old_tb [ENGINE = engine]
#eg:
create table if not exists new_tb as default.hits_v1 engine = TinyLog;
#SELECT 语句复制表,并 copy 数据
CREATE TABLE IF NOT EXISTS [db_name.]new_tb ENGINE = engine AS SELECT .....
#eg:
create table if not exists new_tb engine=Memory as select * from default.hits_v1
#删除表
DROP TABLE [IF EXISTS] [db_name.]tb_name;
#按照分区表查询,提高查询速度
SELECT * FROM partition_name WHERE record_time = '2020-06-17';
#删除字段
ALTER TABLE tb_name DROP COLUMN [IF EXISTS] name
alter table test_v1 drop column URL;
#移动表/重命名表 - 类 Linux mv 命令
RENAME TABLE [db_name11.]tb_name11 TO [db_name12.]tb_name12, [db_name21.]tb_name21 TO [db_name22.]tb_name22,.....
#eg:
rename table default.test_v1 to db_test.test_v2;
#清空数据表
TRUNCATE TABLE [IF EXISTS] [db_name.]tb_name
#eg:
truncate table db_test.test_v2
#查询分区信息
SELECT partition_id,name,table,database FROM system.parts where table = 'partition_name';
#删除分区
ALTER TABLE tb_name DROP PARTITION partition_expr
#卸载分区 DETACH 语句
ALTER TABLE tb_name DETACH PARTITION partition_expr;
#eg: 如下语句将卸载整个2020年6月的数据
alter table tb_nama detach partition '202006';
#被卸载的数据移动到
#pwd
/chbase/data/data/default/partition_v2/detached 目录下
分区一旦移动到 detached 子目录,代表它脱离了 Clickhouse 的管理,clickhouse 并不会主动清理这些文件,只能自己删除,除非重新装载它们
#重新装载分区
ALTER TABLE partition_v2 ATTACH PARTITION '202006';
#分布式DDL执行 只需要加上 ON CLUSTER cluster_name 即可:
一条普通DDL语句转换分布式执行,如下语句将会对 ch_cluster 集群内的所有节点广播这条 DDL 语句:
CREATE TABLE partition_v3 ON CLUSTER ch_cluster(
  ID String,
  URL String,
  EventTime Data
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventTime)
ORDER BY ID
#数据写入 INSERT 语句,三种方式
1.常规,多行数据后面逗号依次展开
INSERT INTO [db.]table [(c1,c2,c3...)] values (v11,v12,v13...),(v21,v22,v23...),.....
同时支持表达式及函数
insert into partition_v2 values ('a0014',toString(1+2),now());
2.使用指定格式的语法
INSERT INTO [db.]table [(c1,c2,c3...)] FORMAT format_name data_set;
#eg CSV 格式为例:
INSERT INTO partition_v2 FORMAT CSV \
'A0017','url1','2020-06-01'\
'A0018','url2','2020-06-03'\
3.使用 SELECT 子句
INSERT INTO [db.]table [(c1,c2,c3...)] SELECT * FROM partition_v1

楼主热帖
168大数据(www.bi168.cn)是国内首家系统性关注大数据科学与人工智能的社区媒体!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

关闭

站长推荐上一条 /1 下一条

关于我们|小黑屋|Archiver|168大数据 ( 京ICP备14035423号|申请友情链接

GMT+8, 2021-3-9 09:50 , Processed in 0.065002 second(s), 17 queries , Xcache On.

Powered by BI168大数据社区

© 2012-2014 168大数据

快速回复 返回顶部 返回列表