memsql kafka集成
memsql 可以加载s3，文件系统，kafka、hdfs 系统的数据，测试使用kafka
环境使用 docker-compose 运行,新版本的需要申请license，参考链接：
https://www.cnblogs.com/rongfengliang/p/9922041.html
环境准备
- docker-compose 文件
version: "3.7"
services:
memsql:
image: memsql/cluster-in-a-box
init: true
ports:
- "8080:8080"
- "3306:3306"
environment:
- "LICENSE_KEY=youlicense"
zk:
image: wurstmeister/zookeeper:3.4.6
ports:
- "2181:2181"
kafka:
image: wurstmeister/kafka:1.1.0
ports:
- "9092:9092"
depends_on:
- zk
environment:
KAFKA_ADVERTISED_HOST_NAME: kafka
KAFKA_ADVERTISED_PORT: 9092
KAFKA_ZOOKEEPER_CONNECT: zk:2181
KAFKA_AUTO_CREATE_TOPICS_ENABLE: 'false'
KAFKA_DELETE_TOPIC_ENABLE: 'true'
KAFKA_BROKER_ID: 0
- 启动
docker-compose up -d
测试
- 链接kafka 创建topic
./kafka-topics.sh --topic test --zookeeper zk:2181 --create --partitions 8 --replication-factor 1
- 生产者发送消息
./kafka-console-producer.sh --topic test --broker-list 127.0.0.1:9092
- 创建memsql数据库&&表
CREATE DATABASE quickstart_kafka;
USE quickstart_kafka;
CREATE TABLE messages (id text);
- 创建pipeline kafka 数据加载
CREATE PIPELINE `quickstart_kafka` AS LOAD DATA KAFKA 'kafka/test' INTO TABLE `messages`;
- 测试pipeline
TEST PIPELINE quickstart_kafka LIMIT 1;
- 启动pipeline
START PIPELINE quickstart_kafka;
- 发送数据
上边的kafka product console
- 查询数据
SELECT * FROM quickstart_kafka.messages;
- 查询结果
SELECT * FROM quickstart_kafka.messages;
+--------+
| id |
+--------+
| |
| s |
| sdf |
| sd |
| sdf |
| sd |
| f |
| f |
| appo |
| sdf |
| f |
| demo |
| df |
| dalong |
| sd |
+--------+
- 查询pipeline 的信息
SELECT * FROM information_schema.PIPELINES_BATCHES_SUMMARY;
参考信息：
SELECT * FROM information_schema.PIPELINES_BATCHES_SUMMARY \G
*************************** 1. row ***************************
DATABASE_NAME: quickstart_kafka
PIPELINE_NAME: quickstart_kafka
BATCH_ID: 4
BATCH_STATE: Succeeded
START_TIME: 2018-12-24 01:27:19.000000
BATCH_TIME: 0.176132
ROWS_PER_SEC: 17.032680035427973
ROWS_STREAMED: 3
NUM_PARTITIONS: 3
NUM_PARTITIONS_FINISHED: 3
MB_STREAMED: 0.0000
MB_PER_SEC: 0.00009651852020075853
EXTRACTOR_WAIT_TIME: 0.0726
TRANSFORM_WAIT_TIME: 0
*************************** 2. row ***************************
DATABASE_NAME: quickstart_kafka
PIPELINE_NAME: quickstart_kafka
BATCH_ID: 2
BATCH_STATE: Succeeded
START_TIME: 2018-12-24 01:24:19.000000
BATCH_TIME: 0.665859
ROWS_PER_SEC: 1.5018194542688468
ROWS_STREAMED: 1
NUM_PARTITIONS: 1
NUM_PARTITIONS_FINISHED: 1
MB_STREAMED: 0.0000
- 可视化界面查看
open http://localhost:8080
一张pipeline 参考图
参考资料
https://docs.memsql.com/memsql-pipelines/v6.7/pipelines-overview/
https://docs.memsql.com/memsql-pipelines/v6.7/kafka-pipeline-quickstart/
https://github.com/rongfengliang/memsql-kafka-pipeline-docker-compose
