clickhouse学习笔记(三)SummingMergeTree 数据导入方式数组类型导出数据 array join groupArray arrayEnumerate limit 分区表

最新推荐文章于 2023-11-07 16:43:13 发布

z小丑八怪r

最新推荐文章于 2023-11-07 16:43:13 发布

阅读量1.2k

点赞数 1

分类专栏： clickhouse

本文链接：https://blog.csdn.net/weixin_46959672/article/details/110843687

版权

clickhouse 专栏收录该内容

5 篇文章 1 订阅

订阅专栏

1 SummingMergeTree

将相同主键的所有的数据累加

--建表，插入数据
create table sum_mergetree(
id Int8 ,
name String ,
cDate Date ,
cost Float64 
)
engine=SummingMergeTree(cost)
order by id 
partition by name ;
insert into sum_mergetree values(1,'zss','2020-12-06',70);
insert into sum_mergetree values(1,'zss','2020-12-06',34.8);
insert into sum_mergetree values(1,'zss','2020-12-06',94.5);
insert into sum_mergetree values(1,'zss','2020-12-06',50.9);
┌─id─┬─name─┬──────cDate─┬─cost─┐
│  1 │ zss  │ 2020-12-06 │   70 │
└────┴──────┴────────────┴──────┘
┌─id─┬─name─┬──────cDate─┬─cost─┐
│  1 │ zss  │ 2020-12-06 │ 94.5 │
└────┴──────┴────────────┴──────┘
┌─id─┬─name─┬──────cDate─┬─cost─┐
│  1 │ zss  │ 2020-12-06 │ 50.9 │
└────┴──────┴────────────┴──────┘
┌─id─┬─name─┬──────cDate─┬─cost─┐
│  1 │ zss  │ 2020-12-06 │ 34.8 │
└────┴──────┴────────────┴──────┘
optimize table sum_mergetree;
┌─id─┬─name─┬──────cDate─┬───────────────cost─┐
│  1 │ zss  │ 2020-12-06 │ 250.20000000000002 │
└────┴──────┴────────────┴────────────────────┘
精度丢失，造成这种结果，可以使用Decimal数据类型解决

create  table  tb_decimal(
id  Int8 ,
money Decimal64(3)
)engine= Log ;
insert  into  tb_decimal  values(1,9) ;  
--查询数据
┌─id─┬─money─┐
│  1 │ 9.000 │
└────┴───────┘

2 数据导入方式

2.1 local方式

在ClickHouse-local中，文件引擎除了还接受文件路径Format。默认输入/输出流可使用数字或人类可读的名称等来指定0或stdin，1或stdout。

将本地文件导入到CH表中，只是生成了一个临时文件，并未在clickhouse终端生成表
clickhouse -local -q

[root@linux01 ~]# echo -e "1,2\n3,4" | clickhouse-local -q "CREATE TABLE tb_local1 (a Int64, b Int64) ENGINE = File(CSV, stdin);select * from tb_local1;"  
1       2
3       4
本地文件 data.csv
1,zss
2,lss
3,www
[root@linux01 ~]# cat data.csv | clickhouse-local -q 'create table local_file(id Int8,name String) engine=File(CSV,0);select * from local_file'
1       zss
2       lss
3       www

2.2 client方式

--在CH中建表
create table tb_client(id UInt16 , name String) engine=TinyLog ;

--将文件数据导入到CH表中
cat data.csv | clickhouse-client -q "INSERT INTO tb_client FORMAT CSV";
cat data.csv | clickHouse-client --query="INSERT INTO db_doit19.tb_client FORMAT CSV";
select * from tb_client;
┌─id─┬─name─┐
│  1 │ zss  │
│  2 │ lss  │
│  3 │ www  │
└────┴──────┘

2.3 导入HDFS中的文件数据到表中

ENGINE = HDFS(URI, format)

2.3.1

HDFS中有文件user.tsv
1       zss     北京    24
2       lss     北京    21
3       wb      上海    27

--建表导入文件数据
create table tb_user(
id Int8,
name String,
city String,
age UInt8
)
engine=HDFS('hdfs://linux01:8020/ch/data/user.tsv',TSV);
--查询数据
┌─id─┬─name─┬─city─┬─age─┐
│  1 │ zss  │ 北京 │  24 │
│  2 │ lss  │ 北京 │  21 │
│  3 │ www  │ 上海 │  27 │
└────┴──────┴──────┴─────┘

2.3.3 导入ORC格式的文件数据到表中

先创建一个ORC格式的文件

在hive的default数据库中 有一个tb_teacher表 
慧慧    处女座  B
老娜    射手座  A
奔奔    处女座  B
gangge  白羊座  A
taoge   射手座  A

--hive中创建一个表，指定存储格式为ORC
create table teacher2(
name String,
xz String,
xx String
)stored as ORCFILE;
--把tb_teacher表中的数据导入teacher2表中
insert into teacher2 select * from tb_teacher;
select * from teacher2;
┌─name───┬─xz─────┬─xx─┐
│ 慧慧   │ 处女座 │ B  │
│ 老娜   │ 射手座 │ A  │
│ 奔奔   │ 处女座 │ B  │
│ gangge │ 白羊座 │ A  │
│ taoge  │ 射手座 │ A  │
└────────┴────────┴────┘
--表teacher2在HDFS中的位置
/user/hive/warehouse/teacher2/000000_0	
此时，ORC格式的数据已创建好

将hive表中的数据直接加载到CH中

create table ch_teacher2(
name String,
xz String,
xx String  
)
engine=HDFS('hdfs://linux01:8020/user/hive/warehouse/teacher2/000000_0',ORC);
查询数据
┌─name───┬─xz─────┬─xx─┐
│ 慧慧   │ 处女座 │ B  │
│ 老娜   │ 射手座 │ A  │
│ 奔奔   │ 处女座 │ B  │
│ gangge │ 白羊座 │ A  │
│ taoge  │ 射手座 │ A  │
└────────┴────────┴────┘
数据正确

总结：

-- 在hive建表  指定表数据存储的格式 
ORCFILE
Parquet
TextFile
---对应的在clickhouse中支持 的输入的文件格式有 
ORC 
Parquet
CSV/TSV
-- 将hive表中的数据直接加载到CH中

数组
CH中数组类型插入方式

create  table  tb_arr(
id Int8 ,
name String ,
hobby  Array(String)
) engine = Log ;

insert  into tb_arr values(1,'wangben',['抽烟','喝酒','相亲']) ;
insert  into tb_arr values(2,'xiaokang',array('抽烟','喝酒','撩汉子')) ;

注意数据类型的匹配
hive中有这样一个表

create  table  arr(
id string ,
names  array<String>
)stored as ORC;

将hdfs中的表导入clickhouse

--clickhouse：
create table  arr(
id String ,
names  Array(String)
)engine=HDFS("hdfs://linux01:8020/user/hive/warehouse/arr/000000_0",ORC) ;

此时，会报异常： The type “list” of an input column “names” is not supported for conversion from a ORC data format: While executing HDFS.
因为hive和clickhouse的数组类型互不兼容

2.4 from

![在这里插入图片描述](https://img-blog.csdnimg.cn/20201207215157753.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Njk1OTY3Mg==,size_16,color_FFFFFF,t_70

2.4.1 file

文件必须在指定的路径下 /var/lib/clickhouse/user_files

此路径下有一个文件user.csv

[root@linux01 user_files]# cat user.csv
1,zs,18,F
2,ls,20,M
--CH：
SELECT *
FROM file('user.csv', CSV, 'id Int8 , name String , age Int8 , gender String')
┌─id─┬─name─┬─age─┬─gender─┐
│  1 │ zs   │  18 │ F      │
│  2 │ ls   │  20 │ M      │
└────┴──────┴─────┴────────┘

clickhosue的配置文件的位置默认在/etc/clickouse-server下

[root@linux01 clickhouse-server]# pwd
/etc/clickhouse-server
-rw-r--r--. 1 root root 33738 Oct  5 18:05 config.xml -- 
-rw-r--r--. 1 root root  5587 Oct  5 18:05 users.xml

config.xml中可以修改文件路径的位置

2.4.2 hdfs

hdfs(URI, format, structure)

select  * from  hdfs("hdfs://linux01:8020/user/hive/warehouse/teacher2/000000_0" ,ORC , 'name String , xz String , xx String' );--字段要和源数据一致
-- 注意特殊的列式存储的数据格式 , ORC格式存储时字段是内置在数据中 , 定义structure 注意字段名和数据类型
(一定要解析有的字段)
┌─name───┬─xz─────┬─xx─┐
│ 慧慧   │ 处女座 │ B  │
│ 老娜   │ 射手座 │ A  │
│ 奔奔   │ 处女座 │ B  │
│ gangge │ 白羊座 │ A  │
│ taoge  │ 射手座 │ A  │
└────────┴────────┴────┘

2.4.3 mysql

 select * from mysql('linux01:3306','default','tb_teacher','root','root');

3 导出数据

clickhouse-client -q "SELECT * FROM {some_table} FORMAT Parquet" > {some_file.pq}

4 array join用法

create  table  arr_join(
id Int8 ,
arr Array(String)
)engine=Memory ;
insert  into arr_join values(1,['a1','a2']),(2,array('b1','b2','b3')) ;
┌─id─┬─arr──────────────┐
│  1 │ ['a1','a2']      │
│  2 │ ['b1','b2','b3'] │
└────┴──────────────────┘

1）
select
id ,
arr
from
arr_join 
array join 
arr  ;  -- 相当于hive中的 explode  +  lateral view
┌─id─┬─arr─┐
│  1 │ a1  │
│  1 │ a2  │
│  2 │ b1  │
│  2 │ b2  │
│  2 │ b3  │
└────┴─────┘

2）
select 
id , arr ,x
from 
arr_join 
array join       --array join 就是把后面的数组炸开，分别和字段拼接
[1,2,3,4,5] as x；
┌─id─┬─arr──────────────┬─x─┐
│  1 │ ['a1','a2']      │ 1 │
│  1 │ ['a1','a2']      │ 2 │
│  1 │ ['a1','a2']      │ 3 │
│  1 │ ['a1','a2']      │ 4 │
│  1 │ ['a1','a2']      │ 5 │
│  2 │ ['b1','b2','b3'] │ 1 │
│  2 │ ['b1','b2','b3'] │ 2 │
│  2 │ ['b1','b2','b3'] │ 3 │
│  2 │ ['b1','b2','b3'] │ 4 │
│  2 │ ['b1','b2','b3'] │ 5 │
└────┴──────────────────┴───┘

5 groupArray

create  table test_arr(
id  Int8 ,
name String
)engine = Log ;
insert  into test_arr values(1,'a1'),(1,'a2'),(1,'a3'),(2,'b1'),(2,'b2'),(2,'b3') ;
┌─id─┬─name─┐
│  1 │ a1   │
│  1 │ a2   │
│  1 │ a3   │
│  2 │ b1   │
│  2 │ b2   │
│  2 │ b3   │
└────┴──────┘

select
id ,
groupArray(name)  arr 
from
test_arr
group by  id;
┌─id─┬─groupArray(name)─┐
│  1 │ ['a1','a2','a3'] │
│  2 │ ['b1','b2','b3'] │
└────┴──────────────────┘

6 arrayEnumerate

接上表
select
id ,
groupArray(name)  arr  ,
arrayEnumerate(arr) arr_index
from
test_arr
group by  id;

┌─id─┬─arr──────────────┬─arrayEnumerate(groupArray(name))─┐
│  1 │ ['a1','a2','a3'] │ [1,2,3]                          │
│  2 │ ['b1','b2','b3'] │ [1,2,3]                          │
└────┴──────────────────┴──────────────────────────────────┘
arrayEnumerate 相当于给数组里的元素建一个索引

--与array join合用
select
id ,e  ,i
from
		(
		select
		id ,
		groupArray(name)  arr   ,
		arrayEnumerate(arr)  arr2
		from
		test_arr
		group by  id
		)
array join 
arr  as  e  ,
arr2  as i ;

┌─id─┬─e──┬─i─┐
│  1 │ a1 │ 1 │
│  1 │ a2 │ 2 │
│  1 │ a3 │ 3 │
│  2 │ b1 │ 1 │
│  2 │ b2 │ 2 │
│  2 │ b3 │ 3 │
└────┴────┴───┘

7 limit

create  table  tb_limit(
id Int8 ,
name String ,
score  Float64
)engine=Log ;

insert  into  tb_limit values(1,'zss',77),(1,'zss',79),(1,'zss',99),(1,'zss',89) ;
insert  into  tb_limit values(2,'lss',66),(2,'lss',69),(2,'lss',61),(2,'lss',69) ;
┌─id─┬─name─┬─score─┐
│  2 │ lss  │    66 │
│  2 │ lss  │    69 │
│  2 │ lss  │    61 │
│  2 │ lss  │    69 │
└────┴──────┴───────┘
┌─id─┬─name─┬─score─┐
│  1 │ zss  │    77 │
│  1 │ zss  │    79 │
│  1 │ zss  │    99 │
│  1 │ zss  │    89 │
└────┴──────┴───────┘

select * from tb_limit order by  score desc  limit 2  by name;

┌─id─┬─name─┬─score─┐
│  1 │ zss  │    99 │
│  1 │ zss  │    89 │
└────┴──────┴───────┘
┌─id─┬─name─┬─score─┐
│  2 │ lss  │    69 │
│  2 │ lss  │    69 │
└────┴──────┴───────┘

建表的其他方式

创建一个试视图
create  view  v_limit as   select * from tb_limit ;
create  table  t_limit engine=Log  as  select * from tb_limit ;

8 创建分区表

create table  p(
oid String ,
money  Float64 ,
cDate  Date
)  engine = MergeTree 
order by oid 
partition by cDate ;

insert into  p values ('002',99,'2020-12-01') ,('001',98,'2020-12-01') ,('003',199,'2020-12-02');

┌─oid─┬─money─┬──────cDate─┐
│ 003 │   199 │ 2020-12-02 │
└─────┴───────┴────────────┘
┌─oid─┬─money─┬──────cDate─┐
│ 001 │    98 │ 2020-12-01 │
│ 002 │    99 │ 2020-12-01 │

按月进行分区

create table  p2(
oid String ,
money  Float64 ,
cDate  Date
)  engine = MergeTree 
order by oid 
partition by toMonth(cDate) ;-- 月进行分区 

insert into  p2 values ('002',99,'2020-12-01') ,('001',98,'2020-12-01') ,('003',199,'2020-12-02'),('004',299,'2020-11-02');
┌─oid─┬─money─┬──────cDate─┐
│ 001 │    98 │ 2020-12-01 │
│ 002 │    99 │ 2020-12-01 │
│ 003 │   199 │ 2020-12-02 │
└─────┴───────┴────────────┘
┌─oid─┬─money─┬──────cDate─┐
│ 004 │   299 │ 2020-11-02 │
└─────┴───────┴────────────┘

按年月分区

create table  p3(
oid String ,
money  Float64 ,
cDate  Date
)  engine = MergeTree 
order by oid 
partition by (toYear(cDate) , toMonth(cDate)) ;

insert into  p3 values ('002',99,'2020-12-01') ,('001',98,'2020-12-01') ,('003',199,'2020-12-02'),('004',299,'2020-11-02'),('005',299,'2019-11-02')
┌─oid─┬─money─┬──────cDate─┐
│ 005 │   299 │ 2019-11-02 │
└─────┴───────┴────────────┘
┌─oid─┬─money─┬──────cDate─┐
│ 001 │    98 │ 2020-12-01 │
│ 002 │    99 │ 2020-12-01 │
│ 003 │   199 │ 2020-12-02 │
└─────┴───────┴────────────┘
┌─oid─┬─money─┬──────cDate─┐
│ 004 │   299 │ 2020-11-02 │
└─────┴───────┴────────────┘
数据存储格式
drwxr-x---. 2 clickhouse clickhouse 228 Dec  6 04:33 2019-11_3_3_0
drwxr-x---. 2 clickhouse clickhouse 228 Dec  6 04:33 2020-11_2_2_0
drwxr-x---. 2 clickhouse clickhouse 228 Dec  6 04:33 2020-12_1_1_0

z小丑八怪r

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
2
评论
clickhouse学习笔记(三)SummingMergeTree 数据导入方式数组类型导出数据 array join groupArray arrayEnumerate limit 分区表

1 SummingMergeTree将相同主键的所有的数据累加--建表，插入数据create table sum_mergetree(id Int8 ,name String ,cDate Date ,cost Float64 )engine=SummingMergeTree(cost)order by id partition by name ;insert into sum_mergetree values(1,'zss','2020-12-06',70);insert int
复制链接

扫一扫