Clickhouse导入数据和导出数据的方法,文件引擎,集成引擎的详解,以及multiIf函数,Array函数的使用

1.SummingMergeTree引擎:

SummingMergeTree(参数:字段):这个引擎会将指定的字段,在合并数据片段的时候对指定的字段,主键相同的进行一个累加;

2.Memory引擎:

数据不会持久化到磁盘 临时数据,当我们重启clickhouse服务器的时候会将内存中的数据释放掉,而不会写入到磁盘中; 

3.File引擎:

File表引擎以特殊的文件格式（TabSeparated，Native等）将数据保存在文件中。 

 3.1file引擎的用法:

数据从ClickHouse导出到文件。将数据从一种格式转换为另一种格式。通过编辑磁盘上的文件来更新ClickHouse中的数据。

实例:
create table tb_file_demo1(uid UInt16 , name String) engine=File(TabSeparated) ;
在指定的目录下创建数据文件
/var/lib/ClickHouse/data/db_demo1/tb_file_demo1
[root@linux01 tb_file_demo1]# vi data.TabSeparated   注意这个文件的名字不能变化
1001    TaoGe
1002    XingGe
1003    HANGGE

create table tb_file_demo2(uid UInt16 , name String) engine=File(CSV) ;
插入数据:在指定的目录下插入数据:vi  data.CSV  注意后缀是大写
101,TaoGe
102,XingGe
103,HANGGE

3.2将本地文件数据导入到clickhouse的表中

导入数据:
先创建一个表
create table tb_client (id UInt16 , name String) engine=TinyLog ;

指定本地文件的路径:
cat /data/user.txt | ClickHouse-client -q="INSERT INTO tb_client FORMAT CSV"; 

默认csv文件的分隔符为逗号,如果遇到有其他特殊符号,我们可以设置,比如分割符为"|"

set format_csv_delimiter="|"

4.集成引擎:

常用的集成引擎主要有:

• Kafka
• MySQL
• ODBC
• JDBC
• HDFS
• 我们主要使用的是mysql引擎和hdfs引擎:

 4.1 HDFS集成引擎:

create table tb_hdfs(
uid String,
name String
)engine=HDFS('hdfs://linux01:8020/user.txt',CSV);
-- 设置数据的分隔符 
set format_csv_delimiter='|' ;

4.2 MySQL 集成引擎:

MySQL引擎用于将远程的MySQL服务器中的表映射到ClickHouse中，并允许您对表进行INSERT和SELECT查询，以方便您在ClickHouse与MySQL之间进行数据交换。

MySQL数据库引擎会将对其的查询转换为MySQL语法并发送到MySQL服务器中，因此您可以执行诸如SHOW TABLES或SHOW CREATE TABLE之类的操作。

 实例:
创建一个数据库,在clickhouse中的数据库映射到mysql中:

实例:
创建一个数据库,在clickhouse中的数据库映射到mysql中:
create database db_mysql
engine=MySQL('linux01:3306','db_demo','root','root');
插入数据到mysql数据库中的表;
insert into tb_demo1 values(1,'zsdf',18);
使用mysql引擎是支持我们在clickhouse中查询和插入数据的操作

插入数据到mysql数据库中的表;

insert into tb_demo1 values(1,'zsdf',18);

使用mysql引擎是支持我们在clickhouse中查询和插入数据的操作

4.3 集成引擎中的case when案例

集成引擎中的case when案例
1.先创建表
2.指定csv文件的分割符:

set format_csv_delimiter='|';
drop table tb_case;
create table tb_case(
name String,
xx String,
gender String
)
engine=HDFS('hdfs://linux01:8020/case.txt',CSV);

执行case when的需求

select
xx,
sum(if(gender='男' ,1,0 )) m,
sum(if(gender='女' ,1,0 )) n
from
tb_case
group by
xx;

结果展示

┌─xx─┬─m─┬─n─┐
│ B      │ 1      │ 2    │
│ A      │ 2       │ 1    │
└────┴───┴───┘

 5. multiIf函数的用法

multiIf函数可以类比为java语言中的if  ...eles if ....eles if........else 语法

SELECT
    left,
    right,
    multiIf(left < right, 'left is smaller', left > right, 'left is greater', left = right, 'Both equal', 'Null value') AS result
FROM LEFT_RIGHT

┌─left─┬─right─┬─result──────┐
│ ᴺᵁᴸᴸ    │     4     │ Null value          │
│    1     │     3     │ left is smaller     │
│    2     │     2     │ Both equal         │
│    3     │     1     │ left is greater     │
│    4     │  ᴺᵁᴸᴸ     │ Null value          │
└────┴─────┴──────────┘

 6. from后面的表函数

6.1 直接读本地文件的方法(不用创建表):

select 
*
from
file("a.csv",'CSV','id String , name String , job String') ; 

文件必须在指定的目录下:/var/lib/clickhouse/user_files/

将本地文件根据自己定义的字段,映射成表; 

6.2  直接读hdfs中的文件(不用创建表直接读取hdfs中的数据);

set format_csv_delimiter=','; --这里需要设置CSV文件的分隔符,默认为逗号,其他符合需要手动设置;

select 
*
from
hdfs( 'hdfs://linux01:8020/a.csv' , 'CSV' ,'uid Int32,name String,gender String') ;

--第一个参数:hdfs://linux01:8020/a.csv  hdfs中的文件路径
--第二个参数:'CSV'  文件的格式
--第三个参数:'uid Int32,name String,gender String'   你要将文件映射为的表结构

 

6.3 直接读mysql中的表:

select 
*
from
mysql('linux01:3306','db_demo','tb_demo1','root','root') ;
--第一个参数:'linux01:3306',指的是要读取mysql中主机名和端口号;
--第二个参数:'db_demo'  , 要读取mysql中的那个数据库名;
--第三个参数: 'tb_demo1' ,对应数据库的中那张表;
--第四,五个参数: 'root','root'   要链接masql的用户名和密码;

 

7. 数据的导出:

 指定导出数据的格式,在shell端执行的命令,导出数据命令,将查询出来的结果导出到本地文件

clickhouse-client -d 'db_demo2'  -q  'select * from tb_a FORMAT TSV'  > /a.tsv

需要指定数据库名称和要查询的表的sql语句;如:db_demo2 值得是数据库的名称,select * from tb_a FORMAT TSV 要查询表的sql语句和指定 输出文件的格式为TSV; 

8. 数据的导入: 

1 使用集成引擎 
    HDFS  ****在创建表的时候指定表引擎为mysql或者是hdfs  重点掌握
    File 了解即可file引擎;详见3
    MySQL    **** 在创建表的时候指定表引擎为mysql或者是hdfs  重点掌握
    KAFKA
2 from  表函数  file  mysql ****  hdfs**** ....  表函数,不用创建表直接从hdfs中的文件读取,或者是从mysql中读取
3 insert into values  普通的插入数据的方法;
4 cat 本地文件 | clickhouse-client -q 'insert into tb_name  format +' 文件格式' ***  将本地文件映射为clickhouse中的表
5 insert into tb_name select  字段 from  tb_name2 ;
6 create table tmp_a engine=Log  as select * from tb_a ;******  从一个表中查询数据然后导入到另一个创建的新表

9.limit函数:

limit为分页函数,指定你要展示数据的前几行,但是在clickhouse中limit和之前用法有不同,不同之处在于,
limit n by 字段,就是可以按照字段分组,然后取组内的前几条数据; 

 select * from tmp_a engine order by uid,name desc limit 2 by uid;
原数据查询:
┌─uid─┬─name┐
│   1      │ 98      │
│   1      │ 93      │
│   2     │ 99       │
│   1      │ 96      │
│   2      │66       │
│   2      │ 23      │
└─────┴───┘
结果:
┌─uid─┬─name ┐
│   1 │ 98   │
│   1 │ 96   │
│   2 │ 99   │
│   2 │ 66   │
└─────┴── ┘

 10. with 函数:(在clickhouse中要区别hive中的 with的用法hive中的用法(with   x as (子查询)给子查询命名为x)

 

在clickhouse中with是用来定义一个常量的如: 

with "hello" as h
SELECT 
    concat(h,',',name)  --将这个常量和指定字段拼接
FROM a; 

 

11 array 数组函数:

array数组中在给array数组中插入数据的方式,1.array(1,2,3)也可以是2. [1,2,3]

 11.1 array  join函数:类比列转行

数据准备:

CREATE TABLE arrays_test
(
    s String,
    arr Array(UInt8)
) ENGINE = Memory;

INSERT INTO arrays_test
VALUES ('Hello', [1,2]), ('World', [3,4,5]), ('Goodbye', []);

┌─s───────────┬─arr─────┐
│ Hello       │ [1,2]   │
│ World       │ [3,4,5] │
│ Goodbye     │ []      │
└─────────────┴─────────┘

SELECT s, arr
FROM arrays_test
ARRAY JOIN arr;

结果:┌─s─────┬─arr─┐
│ Hello │   1 │
│ Hello │   2 │
│ World │   3 │
│ World │   4 │
│ World │   5 │
└───────┴─────┘

 11.2 arrayEnumerate :

取数组的角标的方法

11.3 groupArray(字段) :相当于行转列

按照某个字段分组,将组内的例如姓名收集到一个数组中,展示出来,相当于hive中的collect_list();