大数据之clickhouse案例分析

一、clickhouse权限配置

1、增加用户

在user.xml中添加admin用户配置：

<admin>
    <password>admin</password>
    <networks incl="networks" replace="replace">
             <ip>::/0</ip>
    </networks>
    <quota>default</quota>
    <allow_databases>
        <database>doit23</database>
     </allow_databases>
</admin>

allow_databases： 可以访问数据库

2、密码加密

明文密码： password
暗文密码：sha256或者double_sha1

[root@ck1 ~]#  echo -n hangge | openssl dgst -sha256       
(stdin)= f493c8a7a3c37088731336766459cc37e4b094e95b918038726660cc42013fcd

将

<password>admin</password>

替换为

<password_sha256_hex>f493c8a7a3c37088731336766459cc37e4b094e95b918038726660cc42013fcd</password_sha256_hex>

3、JDBC连接

导入依赖

<dependency>
    <groupId>ru.yandex.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.2.4</version>
</dependency>

一般连接方式：

 public static void main(String[] args) throws Exception {
 
	 //初始化驱动
	 Class.forName("ru.yandex.clickhouse.ClickHouseDriver");
	 //url
	 String url = "jdbc:clickhouse://doit01:8123/default";
	 //用户名密码
	 String username = "default";
	 String password = "";
	 //登录
	 Connection con = DriverManager.getConnection(url, username, password);
	 //获取执行sql对象
	 Statement stmt = con.createStatement();
	 //执行sql语句
	 ResultSet resultSet = stmt.executeQuery("select * from s_2_p");
	        while (resultSet.next()) {
	            int id = resultSet.getInt("id");
	            String name = resultSet.getString("name");
	            Date ct = resultSet.getDate("ct");
	            System.out.println(id + ":" + name + ":" + ct);
	        }
	
	   con.close();
	   stmt.close();
	   resultSet.close();
  }

高可用连接方式：

public static void main(String[] args) throws Exception {
        //初始化驱动
        Class.forName("ru.yandex.clickhouse.ClickHouseDriver");
        //url
        String url = "jdbc:clickhouse://doit01:8123,doit02:8123,doit03:8123/default";
        //设置JDBC参数
        ClickHouseProperties clickHouseProperties = new ClickHouseProperties();
        clickHouseProperties.setUser("default");

        //声明数据源
        BalancedClickhouseDataSource balanced = new BalancedClickhouseDataSource(url, clickHouseProperties);
        //对每个host进行ping操作, 排除不可用的dead连接
        balanced.actualize();
        //获得JDBC连接
        Connection con = balanced.getConnection();
        Statement stmt = con.createStatement();
        ResultSet resultSet = stmt.executeQuery("select * from s_2_p");
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            Date ct1 = resultSet.getDate("ct");
            Date ct = ct1;
            System.out.println(id + ":" + name + ":" + ct);
        }

        con.close();
        stmt.close();
        resultSet.close();

}

二、案例分析

1、用户行为分析

windowFunnel(window)(time, cond1, cond2, ...)  判断是否存在满足根据时间排序且依次条件1,条件2,...条件n都成立的链路. 并且设置链路的窗口时间.   一般用于计算漏斗模型

建表：

使用windowFunnel链路函数

2、用户行为路径分析

sequenceMatch(pattern)(time, cond1, cond2, ...)  判断是否存在满足根据时间排序且依次条件1,条件2,...条件n都成立的链路.若满足 返回 1,否则为 0 .

clickhouse对json字符串的处理并不是很友好 , 为此我们可以对数据进行预先处理一下：

根据数据结构建ck表：

create table tb_event(
	gid String ,
	account String,
	deviceId String,
	ip IPv4,
	eventId String,
	properties String,
	ts Int32
) engine=MergeTree
Partition by toYYYYMMDD(from_unixtime(ts))
order by (gid,ts) ;

clickhouse-client -q ‘insert into doit20. tb_event  format tsv’  <  /data/000000_0

数据格式：

使用函数：

SELECT
  gid, 
  sequenceMatch('(?1)(?t<=3600)(?2).*(?3)(?4)')(
   FROM_UNIXTIME(ts) ,  
   eventId='adClick' ,
   eventId='search' ,
   eventId='pageView' ,
   eventId='submitOrder' 
  ) 
FROM tb_event
GROUP BY gid

(?N)：表示匹配对应事件
(?1)(?t<=3600)(?2) ： 即表示事件1和2发生的时间间隔在3600秒以内。
(?2).*(?3) ：表示事件二和事件三直接可以间隔任何事件

3、用户留存分析

准备数据

1,2020-11-01
2,2020-11-01
3,2020-11-01
4,2020-11-01
4,2020-11-02
5,2020-11-02
6,2020-11-02
9,2020-11-02
6,2020-11-03
7,2020-11-03
8,2020-11-03
9,2020-11-03
5,2020-11-01

建表导入数据

retention函数可以方便地计算留存情况。该函数接受多个条件，以第一个条件的结果为基准，
观察后面的各个条件是否也满足，若满足则置1，不满足则置0，最终返回0和1的数组。
通过统计1的数量，即可计算出留存率。

SELECT
	  sum(ret[1]) AS original,                 --第一天人数
	  sum(ret[2]) AS next_day_ret,             --第二天人数
	  round(next_day_ret / original * 100, 3) AS next_day_ratio,   --第二天留存率
	  sum(ret[3]) AS seven_day_ret,            --第七天
	  round(seven_day_ret / original * 100, 3) AS seven_day_ratio  --第七天留存率
FROM (
  WITH toDate('2020-06-24') AS first_date
  SELECT
    user_id,
    retention(
	    ts_date = first_date,
	    ts_date = first_date + INTERVAL 1 DAY,
	    ts_date = first_date + INTERVAL 7 DAY
    ) AS ret
  FROM ods.ms_order_done_all
  WHERE ts_date >= first_date AND ts_date <= first_date + INTERVAL 7 DAY
  GROUP BY user_id
)

4、用户一天在一个页面的session数

准备数据：

1,'2020-12-11 12:00:01'
1,'2020-12-11 12:00:02'
1,'2020-12-11 12:00:03'
1,'2020-12-11 12:00:11'
1,'2020-12-12 12:00:02'
1,'2020-12-12 12:00:03'
1,'2020-12-12 12:00:10'
1,'2020-12-12 12:41:00'
1,'2020-12-11 12:30:00'
1,'2020-12-11 12:40:00'
1,'2020-12-11 12:50:00'
1,'2020-12-11 15:00:00'

建表导入数据：

计算指标

结果：