八 Clickhouse 应用案例
1 用户和权限
在user.xml中添加用户配置
<?xml version="1.0"?>? ? ?10000000000 ?0 ?random ?? 1 ?? ? ? ? ?::/0 ?default ?default ?f493c8a7a3c37088731336766459cc37e4b094e95b918038726660cc42013fcd ?? ?::/0 ?default ?default ?? ? ? ?3600 ?0 ?0 ?0 ?0 ?0 ?
明文密码
里面没有配置说明是没有密码
SHA256加密：
在使用SHA256加密算法的时候，需要通过password_sha256_hex标签定义密码
[root@ck1 ~]# echo -n hangge | openssl dgst -sha256 ? (stdin)= f493c8a7a3c37088731336766459cc37e4b094e95b918038726660cc42013fcd
f493c8a7a3c37088731336766459cc37e4b094e95b918038726660cc42013fcd ::/0 default default
double_sha1加密:
在使用double_sha1加密算法的时候，则需要通过 password_double_sha1_hex标签定义密码，
23ae809ddacaf96af0fd78ed04b6a265e05aa257 ? \# echo -n 123 | openssl dgst -sha1 -binary | openssl dgst -sha1 ? (stdin)= 23ae809ddacaf96af0fd78ed04b6a265e05aa257
用户权限控制'
? ? ? ## 3 JDBC和客户端工具 ? ### 3.1 JDBC ? ?```xml 60cd41aedc4e47e8883682b416109e7b7e345e15decc63c2c98ecdab5e8e053a readonly default default public class Demo1 { ? public static void main(String[] args) throws Exception { Class.forName("ru.yandex.clickhouse.ClickHouseDriver"); String url = "jdbc:clickhouse://linux01:8123/default"; String username = "default"; String password = ""; Connection con = DriverManager.getConnection(url, username, password); Statement stmt = con.createStatement(); ResultSet resultSet = stmt.executeQuery("select * from tb_demo1"); while (resultSet.next()) { int id = resultSet.getInt("id"); String name = resultSet.getString("name"); System.out.println(id + ":" + name); } con.close(); stmt.close(); resultSet.close(); } } ru.yandex.clickhouse clickhouse-jdbc 0.2.4
高可用模式允许设置多个host地址，每次会从可用的地址中随机选择一个进行连接,在高可用模式下，需要通过BalancedClickhouseDataSource对象获取连接
public static void main(String[] args) throws Exception {
// 初始化驱动
Class.forName("ru.yandex.clickhouse.ClickHouseDriver");
// url
String url = "jdbc:clickhouse://linux01:8123,linux02:8123,linux03: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 demo3_all");
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
?
System.out.println(id + ":" + name);
}
?
con.close();
stmt.close();
resultSet.close();
}
3.2 客户端工具DBeaver
4.1 windowFunnel函数
(参数一) 时间的单位 窗口的大小 时间的单位 (时间 , 事件链条)
uid1 event1 1551398404
uid1 event2 1551398406
uid1 event3 1551398408
uid2 event2 1551398412
uid2 event3 1551398415
uid3 event3 1551398410
uid3 event4 1551398413
————————————————
-- 建表
drop table if exists test_funnel ;
CREATE TABLE test_funnel(
uid String,
eventid String,
eventTime UInt64)
ENGINE = MergeTree
ORDER BY (uid, eventTime) ;
-- 导入数据
insert into test_funnel values
('uid1','event1',1551398404),
('uid1','event2',1551398406),
('uid1','event3',1551398408),
('uid2','event2',1551398412),
('uid2','event3',1551398415),
('uid3','event3',1551398410),
('uid3','event4',1551398413);
-- 查看数据
┌─uid──┬─eventid─┬──eventTime─┐
│ uid1 │ event1 │ 1551398404 │
│ uid1 │ event2 │ 1551398406 │
│ uid1 │ event3 │ 1551398408 │
│ uid2 │ event2 │ 1551398412 │
│ uid2 │ event3 │ 1551398415 │
│ uid3 │ event3 │ 1551398410 │
│ uid3 │ event4 │ 1551398413 │
└──────┴─────────┴────────────┘
?
select
uid ,
windowFunnel(4)(
toDateTime(eventTime),
eventid='event1' ,
eventid='event2' ,
eventid='event3'
) as funnel
from
test_funnel
group by uid ;
┌─uid──┬─funnel─┐
│ uid3 │ 0 │
│ uid1 │ 3 │
│ uid2 │ 0 │
└──────┴────────┘
select
uid ,
windowFunnel(4)(
toDateTime(eventTime),
eventid='event2' ,
eventid='event3'
) as funnel
from
test_funnel
group by uid ;
┌─uid──┬─funnel─┐
│ uid3 │ 0 │
│ uid1 │ 2 │
│ uid2 │ 2 │
└──────┴────────┘
案例
建表 导入数据
clickhouse-client -q 'insert into test1.ods_log format JSONAsString' < event.log ? drop table if exists test_log ; create table test_log engine=MergeTree() order by (id,ts) as with visitParamExtractUInt(line,'timeStamp') as ts , visitParamExtractString(line ,'account')as account, visitParamExtractString(line ,'deviceId')as deviceId, visitParamExtractString(line ,'sessionId')as sessionId, visitParamExtractString(line ,'ip')as ip, visitParamExtractString(line ,'eventId')as eventId, visitParamExtractRaw(line ,'properties')as properties select if(account='' , deviceId , account) id , account , deviceId, sessionId, ip, eventId, properties, ts from tb_ods_log ;
select
id ,
windowFunnel(100000)(
toDateTime(ts),
eventId='productView' ,
eventId='adClick' ,
eventId='productView' ,
eventId='collect'
) as funnel
from
test_log
group by id ;
4.2 sequenceCount
sequenceCount满足要求的次数
SELECT id,
sequenceCount('(?1)')
(
FROM_UNIXTIME(ts) ,
eventId='adShow' ,
eventId='productView' ,
eventId='collect' ,
eventId='addCart' )AS cnt
FROM test_log
GROUP BY id having id='0T7136zA3BZI';
4.3 sequenceMatch
这个函数都需要指定模式串、时间列和期望的事件序列（最多可指定32个事件）。模式串的语法有以下三种： (?N)：表示时间序列中的第N个事件，从1开始。例如上述SQL中，(?2)即表示event_type = 'shtKkclick' AND column_type = 'homePage'。 (?t op secs)：插入两个事件之间，表示它们发生时需要满足的时间条件（单位为秒）。例如上述SQL中，(?1)(?t<=15)(?2)即表示事件1和2发生的时间间隔在15秒以内。 .*：表示任意的非指定事件。
SELECT
id,
sequenceMatch('(?1)(?t<=10)(?2)(?3).*(?4)')(
FROM_UNIXTIME(ts) ,
eventId='adClick' ,
eventId='productView' ,
eventId='collect' ,
eventId='addCart'
) AS is_match
FROM log
GROUP BY id
having id='dGHDHV7WOrpJ';
?
?
SELECT
id,
sequenceCount('(?1)(?t<=10)(?2)(?3).*(?4)')(
FROM_UNIXTIME(ts) ,
eventId='adClick' ,
eventId='productView' ,
eventId='collect' ,
eventId='addCart'
) AS cnt
FROM test_log
GROUP BY id ;
?
?
having id='dGHDHV7WOrpJ 0T7136zA3BZI';
?
?
select id ,eventId from test_log
where id = 'dGHDHV7WOrpJ'