Springboot Flink sql Mysql同步到ElasticSearch

 

 

1. 通过Flink Sql 将mysql 的数据同步到ElasticSearch 中

套路

 

在这里插入图片描述

官网示例：
官网地址：
https://ci.apache.org/projects/flink/flink-docs-release-1.12/dev/table/connectors/elasticsearch.html#document-type

CREATE TABLE myUserTable (
  user_id STRING,
  user_name STRING
  uv BIGINT,
  pv BIGINT,
  PRIMARY KEY (user_id) NOT ENFORCED
) WITH (
  'connector' = 'elasticsearch-7',
  'hosts' = 'http://localhost:9200',
  'index' = 'users'
);

连接的参数：

 

Flink -es -common.png

 

Flink-sql es-comon.png

mysqlk 同步到Mysql 中 总结为：
准备环境 ----> 准备源表 -----> 准备目标表 ----> (查询原表插入目标表)

2. 加依赖

目前两个版本

<dependency>
  <groupId>org.apache.flink</groupId>
  <artifactId>flink-connector-elasticsearch7_2.11</artifactId>
  <version>1.12.3</version>
</dependency>

<dependency>
  <groupId>org.apache.flink</groupId>
  <artifactId>flink-connector-elasticsearch6_2.11</artifactId>
  <version>1.12.3</version>
</dependency>

3. 自己实现

3.1 es 建映射

6.1创建索引

#  创建索引
PUT /wudl_dbes

6.2 创建mapper 映射

#创建mapping
PUT /wudl_dbes/_mapping
{ 
  "properties": {
    "name": {
      "type": "text",
      "index": true
    },
    "sex": {
      "type": "text",
      "index": false
    },
    "age": {
      "type": "long",
      "index": false
    }
  }
}

6.3 插入数据

PUT /wudl_dbes/_doc/1
{
"name":"HFDS",
    "sex":"男",
    "age":18
}

PUT /wudl_dbes/_doc/2
{
"name":"HIVE",
    "sex":"女",
    "age":20
}
PUT /wudl_dbes/_doc/3
{
"name":"Flink",
    "sex":"女",
    "age":18
}

6.4 查询

GET /wudl_dbes/_doc/1
**************************************************

{
  "_index" : "wudl_dbes",
  "_type" : "_doc",
  "_id" : "1",
  "_version" : 1,
  "_seq_no" : 0,
  "_primary_term" : 1,
  "found" : true,
  "_source" : {
    "name" : "HFDS",
    "sex" : "男",
    "age" : 18
  }
}

GET /wudl_dbes/_search

********************************************

{
  "took" : 1,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 3,
      "relation" : "eq"
    },
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "wudl_dbes",
        "_type" : "_doc",
        "_id" : "1",
        "_score" : 1.0,
        "_source" : {
          "name" : "HFDS",
          "sex" : "男",
          "age" : 18
        }
      },
      {
        "_index" : "wudl_dbes",
        "_type" : "_doc",
        "_id" : "2",
        "_score" : 1.0,
        "_source" : {
          "name" : "HIVE",
          "sex" : "女",
          "age" : 20
        }
      },
      {
        "_index" : "wudl_dbes",
        "_type" : "_doc",
        "_id" : "3",
        "_score" : 1.0,
        "_source" : {
          "name" : "Flink",
          "sex" : "女",
          "age" : 18
        }
      }
    ]
  }
}

3.2 mysql 数据结构

CREATE TABLE `myEs` (
  `id` int(64) DEFAULT NULL,
  `name` varchar(64) DEFAULT NULL,
  `sex` varchar(64) DEFAULT NULL,
  `age` int(64) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

代码

package com.wudl.flink.examples;

import org.apache.flink.api.java.tuple.Tuple2;
import org.apache.flink.streaming.api.datastream.DataStream;
import org.apache.flink.streaming.api.environment.StreamExecutionEnvironment;
import org.apache.flink.table.api.EnvironmentSettings;
import org.apache.flink.table.api.SqlDialect;
import org.apache.flink.table.api.Table;
import org.apache.flink.table.api.TableResult;
import org.apache.flink.table.api.bridge.java.StreamTableEnvironment;
import org.apache.flink.types.Row;

/**
 * @ClassName : FlinkSqlMysqlToMySql
 * @Description : Flink sql-mysql
 * @Author :wudl
 * @Date: 2021-08-24 23:28
 */

public class FlinkSqlMysqlToElasticsearch {
    public static void main(String[] args) {

        String driverClass = "com.mysql.jdbc.Driver";

        String dbUrl = "jdbc:mysql://192.168.1.180:3306/MyFlink";
        String userNmae = "root";
        String passWord = "123456";
        StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
        env.setParallelism(1);
        EnvironmentSettings settings = EnvironmentSettings.newInstance().inStreamingMode().useBlinkPlanner().build();
        StreamTableEnvironment tableEvn = StreamTableEnvironment .create(env,settings);
        //1. 指定方言
        tableEvn.getConfig().setSqlDialect(SqlDialect.DEFAULT);
        String flink_sink_table = "myEs";

        TableResult inputTable = tableEvn.executeSql("CREATE TABLE  esTable (" +
                "id int ," +
                "name STRING ," +
                "sex STRING ," +
                "age int" +
                ") " +
                "WITH (" +
                "'connector' = 'elasticsearch-7'," +
                "'hosts' = 'http://node02.com:9200'," +
                "'index' = 'wudl_dbes'"+
                " )");
      TableResult outPutTable = tableEvn.executeSql("CREATE TABLE  sourceMySqlTable (" +
              "id int ," +
              "name STRING ," +
              "sex STRING ," +
              "age int " +
              ") " +
                "WITH (" +
                "'connector' = 'jdbc'," +
                "'url' = '" + dbUrl + "'," +
                "'table-name' = '"+flink_sink_table+"'," +
                " 'username' = '" + userNmae + "'," +
                " 'password' = '" + passWord + "'" +
                " )");

        String sql = " select id,name,sex, age  from sourceMySqlTable";
        Table ResultTable = tableEvn.sqlQuery(sql);

        tableEvn.executeSql("insert into esTable select id,name,sex,age  from "+ResultTable);



    }
}