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

 

一张pipeline 参考图

memsql kafka集成-LMLPHP

参考资料

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

04-25 18:48