clickHouse 基本概念与用法

 

官方文档链接 : https://clickhouse.tech/docs/en/

https://clickhouse.tech/docs/zh/engines/table-engines/mergetree-family/versionedcollapsingmergetree/

1. clickhouse简介

1.1 概念

ClickHouse是一个用于联机分析(OLAP)的列式数据库管理系统(DBMS),是一个完全的列式数据库管理软件，支持线性扩展，简单方便，高可靠性，容错。

全称是Click Stream,Data WareHouse。ClickHouse非常适用于商业智能领域，除此之外，它也能够被广泛应用于广告流量、Web、App流量、电信、金融、电子商务、信息安全、网络游戏、物联网等众多其他领域。

相对于传统的关系型数据库mysql,Clickhouse 在处理大数据量的时候，在查询检索数据量上亿条时，其查询性能有非常强的优势，在数据量比较小的时候，优势不明显。

1.2 优点

  1. 灵活的MPP架构，支持线性扩展，简单方便，高可靠

  2. 多服务器分布式处理数据，完备的DBMS系统

  3. 底层数据列式存储，支持压缩，优化数据存储，优化索引数据

  4. 容错率高，跑分快。比 Vertica 快 5 倍，比Hive 快 279倍，比Mysql 快800 倍，其可处理的数据级别可达到 10 亿级别

  5. 功能多：支持数据统计分析各种场景，支持类SQL查询，异地复制部署，海量数据存储，分布式运算，快速闪电的性能，实时数据分析，出色的函数支持

1.3 缺点

  1. 不支持事务，不支持真正的删除/更新

  2. 单节点不支持高并发，一台机器官方建议qps为100 ，但可以通过修改配置文件增加连接数

  3. 不支持二级索引

  4. 虽然支持多表join查询，但不擅长多表join查询 。 因此在创建表结构的时候，优先考虑创建一张较大较宽的表，将所有的数据放在一张表中

  5. 元数据管理需要人为干预

  6. 尽量做到1000条以上的批量数据写入，避免逐行的insert 或者小批量的insert ,update ,delete 操作，因为写入一条记录和1000条记录所需要的资源和时间相差无几。

1.4. 应用场景

  1. 查询业务远远超过增删改的业务。

  2. 要求实时返回查询结果

  3. 数据需要以大批次（大于1000行）更新，而不是单行更新，或者根本没有更新操作

  4. 读取数据时，会从数据库中提取出大量的行，但只用到一小部分

  5. 表很宽，即表中含有大量的字段，列。每次查询中只会查询一个大宽表，除了一个大宽表，其余都是小表

  6. 列的值是比较小的数值和短字符串，而不是较长的文本

  7. 查询频率相对较低，通常每台服务器每秒查询数百次或更少

  8. 对于简单查询，允许大于50ms的延迟

  9. 在处理单个查询时需要提高吞吐量，每台服务器每秒高达数十亿行

  10. 不需要事务

  11. 数据一致性要求较低（ 原子性 持久性 一致性 隔离性 ）

  12. 查询结果显著小于数据源，即数据有过滤或者聚合，但查询返回的结果不超过单个服务器内存的大小

2. 核心概念

2.1 数据分片

数据分片 是将数据进行横向切分，这是一种在面对海量数据的场景下，解决存储和查询瓶颈的有效手段，是一种分治思想的体现，Clickhouse支持分片而分片则依赖集群，每个集群由1到多个分片组成，每个分片对应了Clickhouse的一个服务节点。分片的数量上限取决于节点数量，一个分片只能对应1个服务节点。Clickhouse并不向其他分布式系统那样，拥有高度自动化的分片功能。Clickhouse的提供了本地表和分布式表的概念，一张本地表等同于一份数据的分片，而分布式表本身并不存储任何数据，它是本地表的访问代理，其作用类似分库中间件。借助分布式表，能够代理访问多个数据分片，从而实现分布式查询，这种设计类似数据库的分库和分表，十分灵活。例如在业务系统上线的初期，数据体量并不高，此时数据并不需要多个分片，所以使用单个节点的本地表（单个数据分片）即可满足业务需求，待到业务增长，数据量增大的时候，再通过新增数据分片的方式分流数据，并通过分布式表实现分布式查询。

2.2 列式存储

列式存储是相对于传统关系型数据库的行式存储来说的。简单来说两者的区别就是如何组织表

img

从上图可以很清楚地看到，行式存储下一张表的数据都是放在一起的，但列式存储下都被分开保存了。将不同的行相同的列存储再一起，这些数据具有相同的结构和长度，重复率更高，可以压缩比例更高，降低IO次数，提高查询效率。

  1. 在实际的应用场景中，往往需要读大量行，但是少数几个列，在行存储模式下，数据按行连续存储，所有列的数据都存储再一个bloCK中，不参与计算的列在IO时也要全部读出，读取操作被严重放大，在列存储模式下，值需要读取参与计算的列即可，极大的降低的IO cast ,加快了查询效率。

  2. 更高的压缩比意味着更小的data size ,从磁盘中读取相应数据耗时更短。

  3. 自由的压缩算法选择，不同列的数据具有不同的数据类型，适用的压缩算法也就不尽相同，可以针对不同列类型，选择最合适的压缩算法

  4. 高压缩比，意味着同样大小的内存能够存放更多的数据，系统cache 效果更好

    官方显示，通过适用列式存储，在某些分析场景中，能后获得100倍甚至更高的加速效应。

2.3 分区

Clickhouse 支持 PARTITION BY 子句，在建表的时候可以指定按照任意合法表达式进行数据分区操作，比如通过toYYYYMM() 将数据按月进行分区，toMonday() 将数据按照星期进行分区，对Enum 类型的列直接每种取值作为一个分区等，数据以分区的形式统一管理和维护一批数据。

2.4 副本

数据存储副本，在集群模式下可实现高可用，在CK中通过复制集，实现了数据可靠性，也通过多副本的方式，增加了CK查询的并发能力。

一般有2种方式：（1）基于ZooKeeper的表复制方式，（2）基于Cluster的复制方式

由于推荐的数据写入方式为本地表写入，禁止分布式表写入，因此复制表主要考虑ZooKeeper的表复制方案。

3. 数据类型

3.1 数字类型

  1. IntX和UIntX

  • Int8 — [-128 : 127]

  • Int16 — [-32768 : 32767]

  • Int32 — [-2147483648 : 2147483647]

  • Int64 — [-9223372036854775808 : 9223372036854775807]

  • Int128 — [-170141183460469231731687303715884105728 : 170141183460469231731687303715884105727]

  • UInt8 — [0 : 255]

  • UInt16 — [0 : 65535]

  • UInt32 — [0 : 4294967295]

  • UInt64 — [0 : 18446744073709551615]

  1. FloatX

  • Float32 — float.

  • Float64 — double.

  1. Decimal

    Decimal32(S) - ( -1 * 10^(9 - S), 1 * 10^(9 - S) )

    Decimal64(S) - ( -1 * 10^(18 - S), 1 * 10^(18 - S) )

    Decimal128(S) - ( -1 * 10^(38 - S), 1 * 10^(38 - S) )

    Decimal256(S) - ( -1 * 10^(76 - S), 1 * 10^(76 - S) )

  2. bool

    0 或者 1

3.2 字符串类型

  1. String

  2. FixedString

  3. UUID

3.3 时间类型

  1. Date

  2. DateTime

  3. DateTime64

3.4 枚举

3.5 数组

3.6 Map

CREATE TABLE table_map (a Map(String, UInt64)) ENGINE=Memory;
INSERT INTO table_map VALUES ({'key1':1, 'key2':10}), ({'key1':2,'key2':20}), ({'key1':3,'key2':30});

4. Clickhouse基本操作

4.1 数据库操作

show databases;  // 展示当前有的数据库
create database if not exists test1 ;//创建数据库
use test1; // 选择test1数据库
select currentDatabase() ；查看当前使用的数据库
drop database test1; // 删除数据库
创建数据库语法：
CREATE database if not exists “数据库名称”；
CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster] [ENGINE = engine(...)]
​
示例：CREATE database if not exists test_for_szp ;

默认情况下，ClickHouse使用的是原生的数据库引擎Ordinary(在此数据库下可以使用任意类型的表引擎，在绝大多数情况下都只需使用默认的数据库引擎)。当然也可以使用Lazy引擎和MySQL引擎，比如使用MySQL引擎，可以直接在ClickHouse中操作MySQL对应数据库中的表。假设MySQL中存在一个名为clickhouse的数据库，可以使用下面的方式连接MySQL数据库。

-- --------------------------语法-----------------------------------
CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MySQL('host:port', ['database' | database], 'user', 'password')
-- --------------------------示例------------------------------------
CREATE DATABASE mysql_db ENGINE = MySQL('192.168.200.241:3306', 'clickhouse', 'root', '123qwe');
删除数据库
语法：DROP database “数据库名称”;
示例：DROP database test_for_szp;

4.2 数据表操作

ck创建数据表的时候，一定要指定引擎，否则会报错

create table tb_test1(
id int,
name String
)engine=Memory;
​
CREATE TABLE product_id
(
  factory_goods_id UInt32 COMMENT '工厂商品ID',
  goods_name String COMMENT '商品名称',
  shop_id UInt32 COMMENT '店铺ID',
  shop_name String COMMENT '店铺名称',
  create_time DateTime COMMENT '创建时间',
  update_time DateTime COMMENT '更新时间'
) ENGINE = MergeTree()
PRIMARY KEY factory_goods_id
ORDER BY factory_goods_id
​
CREATE TABLE wenl1.wenl_tmp_fact_general_report_windspeed (
    data_time Date,
    avg_of_wind_speed Float32
) ENGINE = MergeTree()
ORDER BY data_time;
删除表
DROP table if exists product_detail;
DROP table if exists product_id;
​
在集群上部署的Clickhouse ,删除表也应该带集群的名称  
DROP  TABLE wenl1.wenl_tmp_fact_general_report_substation on CLUSTER elune;

删除数据表的时候会遇到一个问题：

// 删除数据表
DROP   TABLE  if    exists  wenl1.wenl_tmp_fact_general_report_substation on CLUSTER elune;
// 重建数据表
CREATE TABLE if not exists wenl1.wenl_tmp_fact_general_report_production on cluster elune(
  date_time DATE,
  active_power_sum FLOAT(32),
  wtg_id String,
  site_id String
  )
ENGINE =ReplicatedReplacingMergeTree('/clickhouse/wenl1/tables/{layer}-{shard}/wenl_tmp_fact_general_report_production', '{replica}')
    order by (date_time)
    settings index_granularity = 8192;

会出现以下报错：

SQL 错误 [253]: ClickHouse exception, code: 253, host: 10.65.19.56, port: 8123; Code: 253, e.displayText() = DB::Exception: There was an error on [clickhouse0006.eniot.io:9000]: Code: 253, e.displayText() = DB::Exception: Replica /clickhouse/wenl1/tables/01-01/wenl_tmp_fact_general_report_production/replicas/elune-01-2 already exists. (version 21.4.3.21-edh-3.1.4) (version 21.4.3.21-edh-3.1.4)

在我们删除本地表和分布式表后，立即重建是没有问题的。唯一有问题的就是复制表，因为复制表需要在zookeeper上建立一个路径，存放相关数据。clickhouse默认的库引擎是原子数据库引擎，删除Atomic数据库中的表后，它不会立即删除，而是会在480秒后删除。由下面这个参数控制： image-20210818142930907

我们可以使用以下办法来解决这个问题：

  1. 使用普通数据库而不是原子数据库。 create database … Engine=Ordinary.

  2. 使用uniq ZK路径。{uuid}/clickhouse/tables/{layer}-{shard}-{uuid}/

  3. 减少database_atomic_delay_before_drop_table_sec = 0 ＆ drop table … sync

4.3 插入数据

INSERT INTO  [db.]table [(c1, c2, c3)]  VALUES (v11, v12, v13), (v21, v22, v23), ...
​
INSERT  into wenl_wtg_production_day 
(  wtg_id,  date_time   ,positive_energy_p1 ,positive_energy_point  ,positive_energy)
values
('wtg_id1',  '2021-01-01 00:00:00'  ,1.0    ,1.0    ,1.0 );
​
​
INSERT  into wenl_wtg_production_day 
(  wtg_id,  date_time   ,positive_energy_p1 ,positive_energy_point  ,positive_energy)
values
('04m1sTS5',  '2021-01-06'  ,1.0    ,1.0    ,1.0 ),
('04s4109D',  '2021-01-06'  ,1.0    ,1.0    ,1.0 ),
('0C04iY2k',  '2021-01-06'  ,1.0    ,1.0    ,1.0 ),
('0Mx32xyS',  '2021-01-06'  ,1.0    ,1.0    ,1.0 ),
('0QRgFwFQ',  '2021-01-06'  ,1.0    ,1.0    ,1.0 ),
('0ZbfFyQR',  '2021-01-06'  ,1.0    ,1.0    ,1.0 ),
('0bVTxmOo',  '2021-01-06'  ,1.0    ,1.0    ,1.0 ),
('0rP1oCJY',  '2021-01-06'  ,1.0    ,1.0    ,1.0 );
​
​
INSERT  into wenl_dm_fact_daily_report_10m_detail_tmp 
(org_id, site_id, wtg_id,data_time, update_time,wind_speed,read_wind_speed,tem_out, active_power,theory_power,production_loss )
values
('org_id4', 'site_id4' ,'wtg_id4',  '2021-01-04:00:00:00', '2021-01-04:00:00:00',40.0, 40.0, 40.0, 40.0, 40.0, 40.0 );
​
​
将查询的结果全部插入一张表  
INSERT  into wenl_tmp_fact_general_report_production 
select data_time,SUM(active_power) sum_of_Active_power
from wenl_dm_fact_daily_report_10m_detail_tmp 
group by data_time ;
​
插入之前数据类型转换，默认接受的是字符串类型，插入之前需要做一下数据类型转换
INSERT into wenl_meter_production_day values (5,'2021-01-01',toFloat64('1.0'),toFloat64('1.1'),toFloat64('1.2'),toFloat64('1.3'),toFloat64('1.4'),toFloat64('1.5'));
​

4.4 删除数据

按照分区删除数据
ALTER TABLE db_name.table_name  DROP PARTITIION "20200601";
按照条件删除
ALTER TABLE db_name.table_name  DELETE WHERE day='20210818'
​
清空数据表中的数据
TRUNCATE TABLE 表名 + ON CLUNE  集群名;
清空一张表的数据必须要加集群的名字，否则执行不成功
​

4.5 修改数据

ALTER TABLE <table_name> UPDATE col1 = expr1, ... WHERE <filter>
alter table table_name update name = '华为'，phone = '123456' where id = 1;
ALTER table wenl_dim_wtg_full update pc1_id = '0'  where wtg_id ='03glgjyZ';
ALTER table wenl_dim_wtg_full update production_scale = '000' ,production_slope = '0000' where wtg_id ='00Ood1S3';
ALTER TABLE wenl1.wenl_tmp_fact_general_report_date  DELETE WHERE data_time = '2010-01-04'；

4.6 查询数据

SELECT wtg_id ,production_scale ,production_slope  from wenl_dim_wtg_full limit 10 ;
​
SELECT  wtg_id ,wtg_name ,site_id ,site_name , pc1_id  from wenl_dim_wtg_full wdwf  limit 100;
​
// 分组求和 
SELECT  SUM(active_power)  as sum  from wenl_dm_fact_daily_report_10m_detail_tmp group by data_time ;
​
// 分组求平均数  
SELECT data_time ,AVG(wind_speed) as avg_of_wind_speed   from wenl_dm_fact_daily_report_10m_detail_tmp group by data_time ;
​
// 聚合分组查询
SELECT    site_id , data_time, SUM(active_power) 
from      wenl_dm_fact_daily_report_10m_detail_tmp wdfdrmdt 
group by  data_time ,site_id ;
注意：
    group by 后面可以接一个字段名，也可以接两个或者多个字段，但是注意，前面select 接的字段，只能出现 group by 后面跟着的字段，或者其他字段的聚合项，因为 group by 之后就已经把原来的数据集分组了，颗粒度变大了，就无法查询到原来下一级别颗粒度的数据。 
​
SELECT   site_id,  wtg_id , data_time, SUM(active_power) 
from     wenl_dm_fact_daily_report_10m_detail_tmp wdfdrmdt 
group by data_time ,site_id ,wtg_id ;
​
// 左连接查询 
SELECT  
a.wtg_id as wtg_id ,
b.site_id ,
a.data_time as data_time ,
b.sum_of_Active_power as sum_of_Active_power ,
c.avg_of_wind_speed as  avg_of_wind_speed
from wenl_tmp_fact_general_report_date a  
left join wenl_tmp_fact_general_report_production  b 
on ( a.wtg_id = b.wtg_id and a.data_time = b.data_time) 
LEFT join
wenl_tmp_fact_general_report_windspeed c
on (a.wtg_id  = c.wtg_id and a.data_time  = c.data_time) ;
​

4.7 数据类型转换

SELECT  toTypeName(toFloat64('1.0')) aa;

5. MergeTree 系列表引擎

不同的引擎决定了表数据的存储特点和表数的操作行为：

  1. 决定表存储再哪里以及以何种方式存储

  2. 支持哪些查询以及如何支持

  3. 并发数据访问,'不同的引擎决定是否支持并发操作

  4. 索引的使用

  5. 是否可以执行多线程请求

  6. 数据复制参数

    表引擎决定了数据在文件系统中的存储方式，常用的也是官方推荐的存储引擎是MergeTree系列，如果需要数据副本的话可以使用ReplicateMergeTree系列，相当于MergeTree的副本数据，读取集群数据需要使用分布式表引擎 Distribute

5.1 MergeTree

该系列的MergeTree引擎和其他引擎 ( *MergeTree) 是最强大的 ClickHouse 表引擎。

该MergeTree系列中的引擎旨在将大量数据插入表中。数据被快速逐个写入表格，然后应用规则在后台合并部分。这种方法比在插入期间不断重写存储中的数据要高效得多。

主要特点：

  • 存储按主键排序的数据。这允许您创建一个小的稀疏索引，帮助更快地查找数据。

  • 如果指定了分区键，则可以使用分区。

    ClickHouse 支持某些分区操作，这些操作比对具有相同结果的相同数据进行一般操作更有效。ClickHouse 还会自动切断查询中指定分区键的分区数据。

  • 数据复制支持。ReplicatedMergeTree表族提供数据复制。有关详细信息，请参阅数据复制。

  • 数据采样支持。如果需要，您可以在表中设置数据采样方法

5.2 ReplacingMergeTree

该引擎与MergeTree 的不同之处在于它删除具有相同排序键值（ORDER BYtable 部分，而不是PRIMARY KEY）的重复条目。

重复数据删除仅在合并期间发生。合并发生在未知时间的后台，因此您无法计划。某些数据可能尚未处理。尽管您可以使用OPTIMIZE查询运行计划外合并，但不要指望使用它，因为OPTIMIZE查询将读取和写入大量数据。因此，ReplacingMergeTree适合在后台清除重复数据以节省空间，但不保证不存在重复数据。

// 基于MergeTree引擎创建表
CREATE TABLE if not exists wenl1.wenl_tmp_fact_general_report_production on cluster elune(
  date_time DATE,
  active_power_sum FLOAT(32),
  wtg_id String,
  site_id String
  )
ENGINE =MergeTree()
    order by (date_time)
    settings index_granularity = 8192;
​
// 基于ReplicatedReplacingMergeTree引擎创建表
CREATE TABLE if not exists wenl1.wenl_tmp_fact_general_report_production on cluster elune(
  date_time DATE,
  active_power_sum FLOAT(32),
  wtg_id String,
  site_id String
  )
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/wenl1/tables/{layer}-{shard}/wenl_tmp_fact_general_report_production', '{replica}')
    order by (date_time)
    settings index_granularity = 8192;
    
// 执行插入语句：
INSERT  into wenl_tmp_fact_general_report_production
select
data_time ,SUM(active_power) as active_power_sum,wtg_id,MAX(site_id)  as site_id
from wenl_dm_fact_daily_report_10m_detail_tmp
group by data_time, wtg_id ;
​

基于MergeTree引擎创建表，插入之后的效果

image-20210818110336777

基于ReplicatedReplacingMergeTree引擎创建表，插入之后的效果

image-20210818110314143

ReplicatedReplacingMergeTree引擎会在一次插入执行的时候踢出掉一些重复的数据，进行筛选

原因是我们在建表的时候，默认的排序方式order by 指定的是一个字段： date_time, 而我们这里插入的5条记录date_time都是相同的，因此ReplicatedReplacingMergeTree引擎会判定为重复的数据，只保留一条

解决方案：

  1. 换用MergeTree引擎

  2. 或者在建表的时候，order by指定多个字段，中间用逗号隔开

 

6.java 连接Clickhouse 

import ru.yandex.clickhouse.ClickHouseDataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
​
public class ClickhouseUtil {
​
    private static  Connection connection;
​
    public static Connection getClickHouseConnection() throws SQLException {
        String url="jdbc:clickhouse://10.65.19.56:8123/wenl1";
        ClickHouseDataSource dataSou=new ClickHouseDataSource(url);//这是官方给的创建方式
        return dataSou.getConnection("wind", "Envisi0n4321!");
    }
​
    public static void createTable() throws SQLException, ClassNotFoundException {
        Class.forName("ru.yandex.clickhouse.ClickHouseDriver");
        connection = getClickHouseConnection();
        Statement statement = connection.createStatement();
        statement.executeQuery(
                "CREATE TABLE if not exists wenl1.wenl_dm_fact_daily_report_10m_detail_tmp4 on cluster elune(" +
                        "org_id String, " +
                        "site_id String, " +
                        "wtg_id String, " +
                        "data_time DateTime, " +
                        "update_time DateTime, " +
                        "wind_speed Float32, " +
                        "read_wind_speed Float32, " +
                        "tem_out Float32, " +
                        "active_power Float32, " +
                        "theory_power Float32, " +
                        "production_loss Float32" +
                        " )" +
                        "ENGINE = ReplicatedReplacingMergeTree('/clickhouse/wenl1/tables/{layer}-{shard}/ wenl_dm_fact_daily_report_10m_detail_tmp4', '{replica}')" +
                        "partition by toYYYYMM(data_time) " +
                        "order by (wtg_id,data_time) " +
                        "settings index_granularity = 8192; "
        );
    }
​
    public static  void write( ArrayList<HashMap<String,String>>  data2Mysql  ) throws SQLException{
        PreparedStatement pstmt = connection.prepareStatement(
                "insert into  wenl1.wenl_dm_fact_daily_report_10m_detail_tmp4 values(?,?,?,toDateTime(?),toDateTime(?),?,?,?,?,?,?)");
        for(HashMap<String,String> subMap : data2Mysql ){
            pstmt.setString(1,subMap.get("org_id"));
            pstmt.setString(2,subMap.get("site_id"));
            pstmt.setString(3,subMap.get("wtg_id"));
            pstmt.setString(4,subMap.get("data_time"));
            pstmt.setString(5, subMap.get("update_time"));
            pstmt.setDouble(6,Double.parseDouble(subMap.get("wind_speed"))); // "windSpeed"
            pstmt.setDouble(7,Double.parseDouble(subMap.get("read_wind_speed"))); // "readWindSpeed"
            pstmt.setDouble(8,Double.parseDouble(subMap.get("tem_out"))); // "tem_out"
            pstmt.setDouble(9,Double.parseDouble(subMap.get("active_power"))); // "active_power"
            pstmt.setDouble(10,Double.parseDouble(subMap.get("theory_power"))); // "theory_power"
            pstmt.setDouble(11,Double.parseDouble(subMap.get("production_loss"))); // "production_loss"
            pstmt.addBatch();
        }
        pstmt.executeBatch();
    }
}

 

 

 

 

posted @ 2021-11-20 11:09  ziperson  阅读(742)  评论(0)  编辑  收藏  举报
//雪花飘落效果