方案一:
最初的方式是考虑用ELK全家桶的logstash实现,本人用的elasticsearch版本是7.6.2 具体实现方式如下
1.sync.conf
input {
jdbc {
#设置timezone
jdbc_default_timezone => "Asia/Shanghai"
# mysql 数据库链接,dianpingdb为数据库名
jdbc_connection_string => "jdbc:mysql://mysql_ip:3306/mydatabase"
# 用户名和密码
jdbc_user => "user"
jdbc_password => "pass"
# 驱动
jdbc_driver_library => "/home/deployer/zhanggf/test/mysql/mysql-connector-java-5.1.47.jar"
# 驱动类名
jdbc_driver_class => "com.mysql.jdbc.Driver"
jdbc_paging_enabled => "true"
jdbc_page_size => "50000"
#last_run_metadata_path => "/Users/hzllb/Desktop/devtool/logstash-7.3.0/bin/mysql/last_value_meta"
# 执行的sql 文件路径+名称;
statement_filepath => "/home/deployer/zhanggf/test/mysql/sync.sql"
# 设置监听间隔 各字段含义(由左至右)分、时、天、月、年,全部为*默认含义为每分钟都更新
schedule => "* * * * *"
}
}
output {
elasticsearch {
# ES的IP地址及端口
hosts => ["es1:9200","es2:9200","es3:9200"]
# 索引名称
index => "sys_log_202005"
document_type => "_doc"
# 自增ID 需要关联的数据库中有有一个id字段,对应索引的id号
document_id => "%{id}"
}
stdout {
# JSON格式输出
codec => json_lines
}
}
2. 业务逻辑SQL sync.sql
select id,face_id,staff_num,snap_shot_id,snap_shot_time,mac,similar_percent,created_time,updated_time,open_flag,temperature,masks_status,temperature_status,device_name,access_type,check_type,device_num,domain
from sys_identify_log ;
3. 指定conf文件启动
bin\logstash.bat -f sync.conf
mysql 和 es 集群网络不通,出现问题。尝试通过程序mysql到es
方案二 java程序读mysql数据,之后写入到ES
代码如下
1.定义DBHelper类
public class DBHelper {
public static final String url = "jdbc:mysql://ip:3600/mydatabase?useSSL=false";
public static final String name = "com.mysql.jdbc.Driver";
public static final String user = "root";
public static final String password = "123456";
public static Connection conn = null;
public static Connection getConn() {
try {
Class.forName(name);
conn = DriverManager.getConnection(url, user, password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
}
2.
public class BulkProcessImpl {
private static final Logger logger = LogManager.getLogger(BulkProcessImpl.class);
public static void main(String[] args) {
try {
// test();
long startTime = System.currentTimeMillis();
String tableName = "sys_identify_log_back";
createIndex(tableName);
BulkProcessImpl bulk = new BulkProcessImpl();
bulk.writeMysqlDataToES(tableName);
logger.info(" use time: " + (System.currentTimeMillis() - startTime) / 1000 + "s");
} catch (Exception e) {
e.printStackTrace();
}
}
// 创建索引
public static void createIndex(String indexName) throws IOException {
RestHighLevelClient client = new RestHighLevelClient(RestClient.builder(new HttpHost("es1", 9200, "http")));// ��ʼ��
// ES 索引默认需要小写,将其转为小写
CreateIndexRequest requestIndex = new CreateIndexRequest(indexName.toLowerCase());// ��������
// 注: 设置副本数为0,索引刷新时间为-1对大批量索引数据效率的提升有不小的帮助
requestIndex.settings(Settings.builder().put("index.number_of_shards", 3)
.put("index.number_of_replicas", 2)
.put("index.refresh_interval", "-1"));
CreateIndexResponse createIndexResponse = client.indices().create(requestIndex, RequestOptions.DEFAULT);
logger.info("isAcknowledged:" + createIndexResponse.isAcknowledged());
logger.info("isShardsAcknowledged:" + createIndexResponse.isShardsAcknowledged());
client.close();
}
/**
*
* @param
* // 将mysql 数据查出组装成es需要的map格式,通过批量写入es中
*/
public void writeMysqlDataToES(String tableName1) {
String tableNameSql="sys_identify_log_back"; // helloworld
String tableName="sys_identify_log_back"; // helloworld
RestHighLevelClient client = new RestHighLevelClient(RestClient.builder(
new HttpHost("es1", 9200, "http"),
new HttpHost("es2", 9200, "http"),
new HttpHost("es3", 9200, "http")
));
BulkProcessor bulkProcessor = getBulkProcessor(client);
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = DBHelper.getConn();
logger.info("Start handle data :" + tableNameSql);
String sql = "SELECT * from " + tableNameSql+" where 1=1";
ps = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
ps.setFetchSize(Integer.MIN_VALUE);
rs = ps.executeQuery();
ResultSetMetaData colData = rs.getMetaData();
ArrayList<HashMap<String, String>> dataList = new ArrayList<HashMap<String, String>>();
// bulkProcessor 添加的数据支持的方式并不多,查看其api发现其支持map键值对的方式,故笔者在此将查出来的数据转换成hashMap方式
HashMap<String, String> map = null;
HashMap<String, Integer> mapInteger = null;
int count = 0;
String c = null;
String v = null;
int vint=0;
while (rs.next()) {
count++;
map = new HashMap<String, String>(100);
for (int i = 1; i <= colData.getColumnCount(); i++) {
c = colData.getColumnName(i);
v = rs.getString(c);
map.put(c, v);
}
dataList.add(map);
// 每10万条写一次,不足的批次的最后再一并提交
if (count % 50000 == 0) {
logger.info("Mysql handle data number : " + count);
// 将数据添加到 bulkProcessor 中
for (HashMap<String, String> hashMap2 : dataList) {
bulkProcessor.add(new IndexRequest(tableName.toLowerCase(), "_doc", hashMap2.get("id")).source(hashMap2));
}
// 每提交一次便将map与list清空
map.clear();
dataList.clear();
}
}
// count % 100000 处理未提交的数据
for (HashMap<String, String> hashMap2 : dataList) {
bulkProcessor.add(new IndexRequest(tableName.toLowerCase(), "_doc", hashMap2.get("id")).source(hashMap2));
}
logger.info("-------------------------- Finally insert number total : " + count);
// 将数据刷新到es, 注意这一步执行后并不会立即生效,取决于bulkProcessor设置的刷新时间
bulkProcessor.flush();
} catch (Exception e) {
logger.error(e.getMessage());
} finally {
try {
rs.close();
ps.close();
conn.close();
boolean terminatedFlag = bulkProcessor.awaitClose(150L, TimeUnit.SECONDS);
client.close();
logger.info(terminatedFlag);
} catch (Exception e) {
logger.error(e.getMessage());
}
}
}
// 创建bulkProcessor并初始化
private static BulkProcessor getBulkProcessor(RestHighLevelClient client) {
BulkProcessor bulkProcessor = null;
try {
BulkProcessor.Listener listener = new BulkProcessor.Listener() {
@Override
public void beforeBulk(long executionId, BulkRequest request) {
logger.info("Try to insert data number : " + request.numberOfActions());
}
@Override
public void afterBulk(long executionId, BulkRequest request, BulkResponse response) {
logger.info("************** Success insert data number : " + request.numberOfActions() + " , id: "
+ executionId);
}
@Override
public void afterBulk(long executionId, BulkRequest request, Throwable failure) {
logger.error("Bulk is unsuccess : " + failure + ", executionId: " + executionId);
}
};
BiConsumer<BulkRequest, ActionListener<BulkResponse>> bulkConsumer = (request, bulkListener) -> client
.bulkAsync(request, RequestOptions.DEFAULT, bulkListener);
BulkProcessor.Builder builder = BulkProcessor.builder(bulkConsumer, listener);
builder.setBulkActions(10000);
builder.setBulkSize(new ByteSizeValue(300L, ByteSizeUnit.MB));
builder.setConcurrentRequests(10);
builder.setFlushInterval(TimeValue.timeValueSeconds(100L));
builder.setBackoffPolicy(BackoffPolicy.constantBackoff(TimeValue.timeValueSeconds(1L), 3));
// 注意点:在这里感觉有点坑,官网样例并没有这一步,而笔者因一时粗心也没注意,在调试时注意看才发现,上面对builder设置的属性没有生效
bulkProcessor = builder.build();
} catch (Exception e) {
e.printStackTrace();
try {
bulkProcessor.awaitClose(100L, TimeUnit.SECONDS);
client.close();
} catch (Exception e1) {
logger.error(e1.getMessage());
}
}
return bulkProcessor;
}
}