一、clickhouse权限配置
1、增加用户
在user.xml中添加admin用户配置：
admin ::/0 default doit23
allow_databases： 可以访问数据库
2、密码加密
明文密码： password
暗文密码：sha256或者double_sha1
[root@ck1 ~]# echo -n hangge | openssl dgst -sha256 (stdin)= f493c8a7a3c37088731336766459cc37e4b094e95b918038726660cc42013fcd
将
admin
替换为
f493c8a7a3c37088731336766459cc37e4b094e95b918038726660cc42013fcd
3、JDBC连接
导入依赖
ru.yandex.clickhouse clickhouse-jdbc 0.2.4
一般连接方式：
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'
建表导入数据：
计算指标
结果：
