Linux安装clickhouse

### 一、安装

官网：https://clickhouse.tech/

下载地址：http://repo.red-soft.biz/repos/clickhouse/stable/el7/

https://clickhouse.com/docs/zh/getting-started/install/

1、 vim /etc/selinux/config

修改：SELINUX=disabled

2、 yum install -y libtool

3、在 hadoop102 的/opt/software 下创建 clickhouse 目录

mkdir clickhouse

安装包

4、rpm -ivh *.rpm

5、vim /etc/clickhouse-server/config.xml

把 <listen_host>::</listen_host> 的注释打开

在这个文件中，有 ClickHouse 的一些默认路径配置，比较重要的

数据文件路径：<path>/var/lib/clickhouse/</path>

日志文件路径：<log>/var/log/clickhouse-server/clickhouse-server.log</log>

6、启动

systemctl start clickhouse-server

7、关闭开机自启

systemctl disable clickhouse-server

8、客户端登录

clickhouse-client -m

-m :可以在命令窗口输入多行命令

IDEA 连接clickhouse

 

 

 

 

 

### 二、数据类型

1整数类型:
Int8/Int16/Int32/Int64
分别对应mysql类型tinyint/smallint/int/bigint
取值范围:是-2的(Int后数字-1)的平方到2的(Int后数字的平方-1)-1,Int8对应-128~127
UInt8/UInt16/UInt32/UInt64
前面加U 例如 UInt8 代表全部是正数,
取值范围0到2的Int后面数字次方-1,如UInt8对应0-255
2 浮点类型:
Float32/Float64
分别对应mysql的float与double
Decimal(n,p)对应于mysql decimal(n,p)

a指定指定小数点左边和右边可以存储的十进制数字的最大个数，最大精度38。
b指定小数点右边可以存储的十进制数字的最大个数。小数位数必须是从 0 到 a之间的值。默认小数位数是 0。

但是有简写形式Decimal32(p),Decimal64(p),Decimal128(p),分别对应
Decimal(1~9,p),Decimal(10~18,p)``Decimal(19~38,p)

如：123.1234567

Decimal32(5)保留5位：123.12345 不会四舍五入

https://www.cnblogs.com/zsmynl/p/6927659.html

3 字符串类型
String/FixedString/UUID
String不像mysql一样有大小限制,它可以表示成msyql varchar/text/clob/blob的合集
FixedString(n) 定长字符串,相当于char
4 日期类型
Date yyyy-MM-dd格式
DateTime yyyy-MM-dd HH:mm:ss格式

### 三、表引擎

表引擎是 ClickHouse 的一大特色。可以说， 表引擎决定了如何存储表的数据。包括：

➢ 数据的存储方式和位置，写到哪里以及从哪里读取数据。

➢ 支持哪些查询以及如何支持。

➢ 并发数据访问。

➢ 索引的使用（如果存在）。

➢ 是否可以执行多线程请求。

➢ 数据复制参数。

表引擎的使用方式就是必须显式在创建表时定义该表使用的引擎，以及引擎使用的相关

参数

 

TinyLog：

以列文件的形式保存在磁盘上，不支持索引，没有并发控制。一般保存少量数据的小表，

生产环境上作用有限。可以用于平时练习测试用。

```sql
create table t_tinylog ( id String, name String) engine=TinyLog;
```

Memory:

内存引擎，数据以未压缩的原始形式直接保存在内存当中，服务器重启数据就会消失。

读写操作不会相互阻塞，不支持索引。简单查询下有非常非常高的性能表现（超过 10G/s）。

一般用到它的地方不多，除了用来测试，就是在需要非常高的性能，同时数据量又不太

大（上限大概 1 亿行）的场景。

 

MergeTree：

ClickHouse 中最强大的表引擎当属 MergeTree（合并树）引擎及该系列（*MergeTree）

中的其他引擎，支持索引和分区，地位可以相当于 innodb 之于 Mysql。

```sql
CREATE TABLE insert_select_testtable
(
`a` Int8,
`b` String,
`c` Int8
)
ENGINE = MergeTree()
ORDER BY a

INSERT INTO insert_select_testtable (*) VALUES (1, 'a', 1) ;
```

例子2：

```sql
create table t_order_mt(
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time Datetime
) engine =MergeTree
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id);

insert into t_order_mt values
(101,'sku_001',1000.00,'2020-06-01 12:00:00') ,
(102,'sku_002',2000.00,'2020-06-01 11:00:00'),
(102,'sku_004',2500.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 13:00:00'),
(102,'sku_002',12000.00,'2020-06-01 13:00:00'),
(102,'sku_002',600.00,'2020-06-02 12:00:00');

--orderby必选
rder by 设定了分区内的数据按照哪些字段顺序进行有序保存。
order by 是 MergeTree 中唯一一个必填项，甚至比 primary key 还重要，因为当用户不
设置主键的情况，很多处理会依照 order by 的字段进行处理

--ClickHouse 中的主键，和其他数据库不太一样，它只提供了数据的一级索引，但是却不是唯一约束。这就意味着是可以存在相同 primary key 的数据的

TTL 即 Time To Live，MergeTree 提供了可以管理数据表或者列的生命周期的功能。
下面的这条语句是数据会在 create_time 之后 10 秒丢失
alter table t_order_mt3 MODIFY TTL create_time + INTERVAL 10 SECOND;

create table t_order_mt3(
id UInt32,
sku_id String,
total_amount Decimal(16,2) TTL create_time+interval 10 SECOND,
create_time Datetime
) engine =MergeTree
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id, sku_id);

insert into t_order_mt3 values
(106,'sku_001',1000.00,'2020-06-12 22:52:30'),
(107,'sku_002',2000.00,'2020-06-12 22:52:30'),
(110,'sku_003',600.00,'2020-06-13 12:00:00');

```

 

 

### 四、sql

https://clickhouse.com/docs/en/sql-reference/statements/insert-into/

##### 1、Insert

基本与标准 SQL（MySQL）基本一致

（

1）标准

insert into [table_name] values(…),(….)

（

2）从表到表的插入

insert into [table_name] select a,b,c from [table_name_2]

 

##### 2、update和delete

Alter 的一种。

虽然可以实现修改和删除，但是和一般的 OLTP 数据库不一样，**Mutation** **语句是一种很**

**“重”的操作，而且不支持事务。**

“重”的原因主要是每次修改或者删除都会导致放弃目标数据的原有分区，重建新分区。

所以尽量做批量的变更，不要进行频繁小数据的操作。

（

1）删除操作

alter table t_order_smt delete where sku_id ='sku_001';

（

2）修改操作

alter table t_order_smt update total_amount=toDecimal32(2000.00,2) where id

=102;

由于操作比较“重”，所以 Mutation 语句分两步执行，同步执行的部分其实只是进行

新增数据新增分区和并把旧分区打上逻辑上的失效标记。直到触发分区合并的时候，才会删

除旧数据释放磁盘空间，一般不会开放这样的功能给用户，由管理员完成。

 

##### 3、查询操作

ClickHouse 基本上与标准 SQL 差别不大

➢ 支持子查询

➢ 支持 CTE(Common Table Expression 公用表表达式 with 子句)

➢ 支持各种 JOIN，

但是 JOIN 操作无法使用缓存，所以即使是两次相同的 JOIN 语句，

ClickHouse 也会视为两条新 SQL

➢ 窗口函数

➢ 不支持自定义函数

➢ GROUP BY 操作增加了 with rollup\with cube\with total 用来计算小计和总计。

（

1）插入数据

hadoop102 :) alter table t_order_mt delete where 1=1;

insert into t_order_mt values

(101,'sku_001',1000.00,'2020-06-01 12:00:00'),

(101,'sku_002',2000.00,'2020-06-01 12:00:00'),

(103,'sku_004',2500.00,'2020-06-01 12:00:00'),

(104,'sku_002',2000.00,'2020-06-01 12:00:00'),

(105,'sku_003',600.00,'2020-06-02 12:00:00'),

(106,'sku_001',1000.00,'2020-06-04 12:00:00'),

(107,'sku_002',2000.00,'2020-06-04 12:00:00'),

(108,'sku_004',2500.00,'2020-06-04 12:00:00'),

(109,'sku_002',2000.00,'2020-06-04 12:00:00'),

(110,'sku_003',600.00,'2020-06-01 12:00:00');

2）with rollup：从右至左去掉维度进行小计

hadoop102 :) select id , sku_id,sum(total_amount) from t_order_mt group by

id,sku_id with rollup;

3）with cube : 从右至左去掉维度进行小计，再从左至右去掉维度进行小计

hadoop102 :) select id , sku_id,sum(total_amount) from t_order_mt group by

id,sku_id with cube;

4）with totals: 只计算合计

hadoop102 :) select id , sku_id,sum(total_amount) from t_order_mt group by

id,sku_id with totals;

 

```sql

WITH
(
SELECT sum(bytes)
FROM system.parts
WHERE active
) AS total_disk_usage
SELECT
(sum(bytes) / total_disk_usage) * 100 AS table_disk_usage,
table
FROM system.parts
GROUP BY table
ORDER BY table_disk_usage DESC
LIMIT 10

┌──────table_disk_usage─┬─table──────┐
│ 66.13563015490257 │ hits_v1 │
│ 29.31875240635302 │ visits_v1 │
│ 5.843957690326289 │ t_dict │
│ 0.3967229532123266 │ metric_log │
│ 0.0012069656728751568 │ trace_log │
│ 0.000137742753168703 │ part_log │
└───────────────────────┴────────────┘

--https://www.saoniuhuo.com/article/detail-82.html
--https://www.codeleading.com/article/12084056248/

WITH (
SELECT avg(total_amount) AS avga
FROM t_order_mt
) AS avga
SELECT *
FROM t_order_mt
WHERE total_amount > toDecimal64(avga, 2)
```

 

 

##### 4、alter

同 MySQL 的修改字段基本一致

1）新增字段

alter table **tableName** add column **newcolname** String after col1;

2）修改字段类型

alter table tableName modify column newcolname String;

3）删除字段

alter table tableName drop column newcolname;

##### 5、导出数据

clickhouse-client --query "select * from t_order_mt where

create_time='2020-06-01 12:00:00'" --format CSVWithNames>

/opt/module/data/rs1.csv

 

 

hive with

```sql
--1
with cte_order as
(
select store_id,channel,paid_date,order_id,gmv
from testdb.order_line
where channel in ('SNG','JDDJ')
)
--with as 语句不能加分号 ;

select store_id,order_id,paid_date,order_id,gmv
from cte_order
where channel='SNG'
union all
select store_id,order_id,paid_date,order_id,gmv
from cte_order
where channel='JDDJ'
;

```

 

 

表引擎：

sql: 数据类型、查询语句 、分组聚合、join、函数。

### 五、jdbc