数据源管理| OLAP查询引擎，ClickHouse集群管理

本文的源代码: GitHub·单击此处|| GitEE·单击此处

ClickHouse是Yandex公司于2016年开源的列式存储（DBMS）. 它主要用于OLAP分析和处理查询. 它可以使用SQL查询实时生成分析数据报告.

列存储

线性存储和列存储，磁盘上数据的组织方式根本不同. 在数据分析和计算中，行存储区需要遍历整个表，而列式存储区仅需要遍历单个列olap数据源，因此，柱状库更适合于制作一个大而宽的表以进行数据分析和计算.

A句: 请注意，此处比较的场景是用于数据分析和计算的场​​景.

默认已安装ClickHouse单一服务

vim /etc/security/limits.conf
vim /etc/security/limits.d/90-nproc.conf
文件末尾追加
* soft nofile 65536 
* hard nofile 65536 
* soft nproc 131072 
* hard nproc 131072

修改SELINUX =在/ etc / selinux / config中已禁用并重新启动

添加服务的集群配置: vim /etc/metrika.xml

<yandex>
<clickhouse_remote_servers>
    <clickhouse_cluster>
        <shard>
            <internal_replication>true</internal_replication>
            <replica>
                <host>192.168.72.133</host>
                <port>9000</port>
            </replica>
        </shard>
        <shard>
            <replica>
                <internal_replication>true</internal_replication>
                <host>192.168.72.136</host>
                <port>9000</port>
            </replica>
        </shard>
        <shard>
            <internal_replication>true</internal_replication>
            <replica>
                <host>192.168.72.137</host>
                <port>9000</port>
            </replica>
        </shard>
    </clickhouse_cluster>
</clickhouse_remote_servers>
<zookeeper-servers>
  <node index="1">
    <host>192.168.72.133</host>
    <port>2181</port>
  </node>
  <node index="2">
    <host>192.168.72.136</host>
    <port>2181</port>
  </node>
  <node index="3">
    <host>192.168.72.137</host>
    <port>2181</port>
  </node>
</zookeeper-servers>
<macros>
    <replica>192.168.72.133</replica>
</macros>
<networks>
   <ip>::/0</ip>
</networks>
<clickhouse_compression>
<case>
  <min_part_size>10000000000</min_part_size>
  <min_part_size_ratio>0.01</min_part_size_ratio>
  <method>lz4</method>
</case>
</clickhouse_compression>
</yandex>

在这里注意

<macros>
    <replica>192.168.72.133</replica>
</macros>

配置每个服务的IP地址.

分别启动三个服务

service clickhouse-server start

只需在此处登录任何服务

clickhouse-client
en-master :) select * from system.clusters

这是群集名称: clickhouse_cluster，将在以后使用.

在三个服务上同时创建表结构.

CREATE TABLE ontime_local (FlightDate Date,Year UInt16) ENGINE = MergeTree(FlightDate, (Year, FlightDate), 8192);

133环境下创建分配表

CREATE TABLE ontime_all AS ontime_local ENGINE = Distributed(clickhouse_cluster, default, ontime_local, rand());

随便写任何服务数据

insert into ontime_local (FlightDate,Year) values ('2020-03-12',2020);

查询摘要表

select * from ontime_all;

写入主表，数据将分发到每个单个表

insert into ontime_all (FlightDate,Year)values('2001-10-12',2001);
insert into ontime_all (FlightDate,Year)values('2002-10-12',2002);
insert into ontime_all (FlightDate,Year)values('2003-10-12',2003);

任意关闭任何服务，集群查询直接挂起

url: 配置所有服务列表，主要用于管理表结构和批处理；

cluster: 群集连接服务，可以基于Nginx代理服务进行配置；

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    click:
      driverClassName: ru.yandex.clickhouse.ClickHouseDriver
      url: jdbc:clickhouse://127.0.0.1:8123/default,jdbc:clickhouse://127.0.0.1:8123/default,jdbc:clickhouse://127.0.0.1:8123/default
      cluster: jdbc:clickhouse://127.0.0.1:8123/default
      initialSize: 10
      maxActive: 100
      minIdle: 10
      maxWait: 6000

创建表并将数据分别写入每个单节点服务:

data_shard（单节点数据）

data_all（分布式数据）

@RestController
public class DataShardWeb {
    @Resource
    private JdbcFactory jdbcFactory ;
    /**
     * 基础表结构创建
     */
    @GetMapping("/createTable")
    public String createTable (){
        List<JdbcTemplate> jdbcTemplateList = jdbcFactory.getJdbcList();
        for (JdbcTemplate jdbcTemplate:jdbcTemplateList){
            jdbcTemplate.execute("CREATE TABLE data_shard (FlightDate Date,Year UInt16) ENGINE = MergeTree(FlightDate, (Year, FlightDate), 8192)");
            jdbcTemplate.execute("CREATE TABLE data_all AS data_shard ENGINE = Distributed(clickhouse_cluster, default, data_shard, rand())");
        }
        return "success" ;
    }
    /**
     * 节点表写入数据
     */
    @GetMapping("/insertData")
    public String insertData (){
        List<JdbcTemplate> jdbcTemplateList = jdbcFactory.getJdbcList();
        for (JdbcTemplate jdbcTemplate:jdbcTemplateList){
            jdbcTemplate.execute("insert into data_shard (FlightDate,Year) values ('2020-04-12',2020)");
        }
        return "success" ;
    }
}

完成上述步骤后olap数据源，您可以连接集群服务以查询分发表和单个表的数据.

基于Druid的连接

@Configuration
public class DruidConfig {
    @Resource
    private JdbcParamConfig jdbcParamConfig ;
    @Bean
    public DataSource dataSource() {
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(jdbcParamConfig.getCluster());
        datasource.setDriverClassName(jdbcParamConfig.getDriverClassName());
        datasource.setInitialSize(jdbcParamConfig.getInitialSize());
        datasource.setMinIdle(jdbcParamConfig.getMinIdle());
        datasource.setMaxActive(jdbcParamConfig.getMaxActive());
        datasource.setMaxWait(jdbcParamConfig.getMaxWait());
        return datasource;
    }
}

基于映射器的查询

<mapper namespace="com.ckhouse.cluster.mapper.DataAllMapper">
    <resultMap id="BaseResultMap" type="com.ckhouse.cluster.entity.DataAllEntity">
        <result column="FlightDate" jdbcType="VARCHAR" property="flightDate" />
        <result column="Year" jdbcType="INTEGER" property="year" />
    </resultMap>
    <select id="getList" resultMap="BaseResultMap" >
        select * from data_all where Year=2020
    </select>
</mapper>

GitHub·地址
https://github.com/cicadasmile/data-manage-parent
GitEE·地址
https://gitee.com/cicadasmile/data-manage-parent

本文来自电脑杂谈，转载请注明本文网址：
http://www.pc-fly.com/a/jisuanjixue/article-263665-1.html