ClickHouse数据导入Mysql的方法

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)