1.clickhouse中创建表
CREATE TABLE user_behavior_test (
user_id String,
item_id String,
category_id String,
behavior_type String,
time_stamp String
)ENGINE = MergeTree ORDER BY (user_id, item_id, time_stamp);
2.插入一下测试数据
准备数据如下:
1,2268318,2520377,pv,1511544070
1,2333346,2520771,pv,1511561733
1,2576651,149192,pv,1511572885
1,3830808,4181361,pv,1511593493
1,4365585,2520377,pv,1511596146
1,4606018,2735466,pv,1511616481
1,230380,411153,pv,1511644942
1,3827899,2920476,pv,1511713473
1,3745169,2891509,pv,1511725471
1,1531036,2920476,pv,1511733732
导入到user_behavior_test表
# clickhouse-client --query "INSERT INTO datasets.user_behavior_test FORMAT CSV" < /home/datasets/ub-10.csv --user XXXXXXX --password XXXXXXX
查看导入结果。
master :) select * from user_behavior_test;
SELECT *
FROM user_behavior_test
┌─user_id─┬─item_id─┬─category_id─┬─behavior_type─┬─time_stamp─┐
│ 1 │ 1531036 │ 2920476 │ pv │ 1511733732 │
│ 1 │ 2268318 │ 2520377 │ pv │ 1511544070 │
│ 1 │ 230380 │ 411153 │ pv │ 1511644942 │
│ 1 │ 2333346 │ 2520771 │ pv │ 1511561733 │
│ 1 │ 2576651 │ 149192 │ pv │ 1511572885 │
│ 1 │ 3745169 │ 2891509 │ pv │ 1511725471 │
│ 1 │ 3827899 │ 2920476 │ pv │ 1511713473 │
│ 1 │ 3830808 │ 4181361 │ pv │ 1511593493 │
│ 1 │ 4365585 │ 2520377 │ pv │ 1511596146 │
│ 1 │ 4606018 │ 2735466 │ pv │ 1511616481 │
└─────────┴─────────┴─────────────┴───────────────┴────────────┘
10 rows in set. Elapsed: 0.010 sec.
3.mysql中建表
CREATE TABLE `user_behavior_mysql` (
`user_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`item_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`category_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`behavior_type` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`time_stamp` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
PRIMARY KEY (`user_id`, `item_id`, `time_stamp`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
4.在clickhouse中建立mysql的物化表
master :) CREATE TABLE user_behavior_mysql (
:-] user_id String,
:-] item_id String,
:-] category_id String,
:-] behavior_type String,
:-] time_stamp String
:-] )engine=MySQL('10.16.60.62:3306','datasets','user_behavior_mysql','root','123456');
CREATE TABLE user_behavior_mysql
(
`user_id` String,
`item_id` String,
`category_id` String,
`behavior_type` String,
`time_stamp` String
)
ENGINE = MySQL('10.16.60.62:3306', 'datasets', 'user_behavior_mysql', 'root', 'XXXXXXXXXX')
Ok.
0 rows in set. Elapsed: 0.005 sec.
5.在clickhouse中将原始表数据插入物化表
master :) insert into user_behavior_mysql select * from user_behavior_test;
INSERT INTO user_behavior_mysql SELECT *
FROM user_behavior_test
Ok.
0 rows in set. Elapsed: 0.006 sec.
6.在mysql中查看导入结果。
mysql> select * from user_behavior_mysql;
+---------+---------+-------------+---------------+------------+
| user_id | item_id | category_id | behavior_type | time_stamp |
+---------+---------+-------------+---------------+------------+
| 1 | 1531036 | 2920476 | pv | 1511733732 |
| 1 | 2268318 | 2520377 | pv | 1511544070 |
| 1 | 230380 | 411153 | pv | 1511644942 |
| 1 | 2333346 | 2520771 | pv | 1511561733 |
| 1 | 2576651 | 149192 | pv | 1511572885 |
| 1 | 3745169 | 2891509 | pv | 1511725471 |
| 1 | 3827899 | 2920476 | pv | 1511713473 |
| 1 | 3830808 | 4181361 | pv | 1511593493 |
| 1 | 4365585 | 2520377 | pv | 1511596146 |
| 1 | 4606018 | 2735466 | pv | 1511616481 |
+---------+---------+-------------+---------------+------------+
10 rows in set (0.00 sec)