DataX离线同步异构数据
简介
DataX是阿里巴巴集团内被广泛使用的离线数据同步工具平台，支持离线同步各种异构数据源
DataX本身作为数据同步框架，将不同数据源的同步抽象为从源头数据源读取数据的Reader插件，以及向目标端写入数据的Writer插件，理论上DataX框架可以支持任意数据源类型的数据同步工作。同时DataX插件体系作为一套生态系统, 每接入一套新数据源该新加入的数据源即可实现和现有的数据源互通。
建目标表
从容器进入TDengine库
也可直接使用数据库连接工具，比如DBeavar或者Datagrip连接tdengine库，图形化操作建表。
然后准备job.josn，从mysql迁移到tdengine：
{
"job": {
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"username": "root",
"password": "fXBS3HN4J6EW9Xmw)@3z&)RD~cDnph",
"connection": [
{
"querySql": [
"SELECT time, ten_speed, ten_direction, ten_direction_angle, thirty_speed, thirty_direction,thirty_direction_angle,fifty_speed, fifty_direction,fifty_direction_angle, seventy_speed,seventy_direction,seventy_direction_angle, hub_speed, hub_direction, hub_direction_angle, surface_temperature, surface_humidity, surface_pressure, create_by, create_time, update_by,update_time from wpp_weather_forecast_data ;"
],
"jdbcUrl": [
"jdbc:mysql://YOUR_SOURCE_DATABASE_IP:3306/wpp_server?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true"
]
}
]
}
},
"writer": {
"name": "tdengine30writer",
"parameter": {
"username": "root",
"password": "123456",
"column": [
"time",
"ten_speed",
"ten_direction",
"ten_direction_angle",
"thirty_speed",
"thirty_direction",
"thirty_direction_angle",
"fifty_speed",
"fifty_direction",
"fifty_direction_angle",
"seventy_speed",
"seventy_direction",
"seventy_direction_angle",
"hub_speed",
"hub_direction",
"hub_direction_angle",
"surface_temperature",
"surface_humidity",
"surface_pressure",
"create_by",
"create_time",
"update_by",
"update_time"
],
"connection": [
{
"table": [
"wpp_weather_forecast_data"
],
"jdbcUrl": "jdbc:TAOS-RS://YOUR_TARGET_DATABASE_IP:6041/wpp_server"
}
],
"batchSize": 100,
"ignoreTagsUnmatched": true
}
}
}
],
"setting": {
"speed": {
"channel": 1
}
}
}
}
我这里有一个问题就是没有tdengine30writer，因为使用的tdengine版本是3.2.3.0版本，语法上有很大的区别，所以后面又更新了新的迁移插件，而我下载的dataX里面只有名为tdenginewriter的插件（只能处理2.x版本），所以重新下载
https://gitcode.com/taosdata/DataX/blob/master/tdengine30writer/doc/tdengine30writer-CN.md
将tdengine30writer编译打包，放入dataX的plugin/writer插件位置,并配置plugin.json文件！
Python datax.py YOUR_JOB.json
如果是在系统安装tdengine
启动服务,使用原生连接,连接url：jdbc:TAOS://YOUR_IP:6030/wpp_server
systemctl start taosd
这个是提供REST连接需要启动的服务，默认端口6041，连接url：jdbc:TAOS-RS://YOUR_IP:6041/wpp_server
systemctl start taosadapter
同步迁移数据表的代码
package com.alibaba.datax.plugin.writer.tdengine30writer.save;
import org.junit.Before;
import org.junit.Test;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Random;
public class WPPWindTurbinesData2TDengine {
public static void main(String[] args) throws Exception {
System.out.println("遍历迁移");
final String url = "jdbc:mysql://" + host1 + ":3306/wpp_server?useSSL=false&useUnicode=true&charset=UTF-8&generateSimpleParameterMetadata=true";
Connection conn = DriverManager.getConnection(url, "root", "root");
stmt = conn.createStatement();
String deviceListSql = "SELECT device_id from wpp_wind_turbines_data GROUP by device_id ORDER BY device_id";
ResultSet deviceSet = stmt.executeQuery(deviceListSql);
ArrayList<Long> deviceList = new ArrayList<>();
while (deviceSet.next()){
System.out.println(deviceSet.getString(1));
deviceList.add(Long.parseLong(deviceSet.getString(1)));
}
WPPWindTurbinesData2TDengine WPPWindTurbinesData2TDengine = new WPPWindTurbinesData2TDengine();
for (Long l : deviceList) {
deviceId = String.valueOf(l);
ResultSet noSet = stmt.executeQuery("SELECT no from wpp_wind_turbines_data where device_id = " + deviceId);
if (noSet.next()){
no = noSet.getString(1);
}
WPPWindTurbinesData2TDengine.before();
}
stmt.close();
}
private static String no = "";
private static Statement stmt;
private static String deviceId = "";
private static final String host1 = "///////";
private static final String host2 = "///////";
private static final Random random = new Random(System.currentTimeMillis());
private String sql = "create table stb1(id int primary key AUTO_INCREMENT, ";
private static String sql2 = "SELECT id, device_id, time, speed, direction, direction_angle, ideal_power, available_power, actual_power_output, capacity_power, create_by, create_time, update_by, update_time, no from wpp_wind_turbines_data t where device_id = ";
@Test
public void test2() throws Throwable {
String[] params = {"-mode", "standalone", "-jobid", "-1", "-job", "src/test/resources/m2t-3.json"};
/* String property = System.getProperty("datax.home");
System.out.println(property);
System.setProperty("datax.home", "C:\\Users\\tianjiawei\\Downloads\\datax (1)\\datax\\tdenginewriter.json");
Engine.entry(params);*/
}
@Before
public void before() throws Exception {
// given
long ts_start = new Date(System.currentTimeMillis()).getTime();
final int columnSize = 10;
final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
String sqlBody = "";
StringBuffer sb = new StringBuffer();
final String url = "jdbc:mysql://" + host1 + ":3306/wpp_server?useSSL=false&useUnicode=true&charset=UTF-8&generateSimpleParameterMetadata=true";
try {
// Statement stmt = conn.createStatement();
sql2 = "SELECT id, device_id, time, speed, direction, direction_angle, ideal_power, available_power, actual_power_output, capacity_power, create_by, create_time, update_by, update_time, no from wpp_wind_turbines_data t where device_id = ";
sql2 = sql2 + deviceId;
System.out.println(sql2);
ResultSet resultSet = stmt.executeQuery(sql2);
System.out.println("mysql data select done");
while (resultSet.next()) {
sb.append("(");
joinSql(sb, resultSet.getString(3), Date.class);
joinSql(sb, resultSet.getString(4), Long.class);
joinSql(sb, resultSet.getString(5), Float.class);
joinSql(sb, resultSet.getString(6), String.class);
joinSql(sb, resultSet.getString(7), Float.class);
joinSql(sb, resultSet.getString(8), Float.class);
joinSql(sb, resultSet.getString(9), Float.class);
joinSql(sb, resultSet.getString(10), Float.class);
if (sb.charAt(sb.length() - 1) == ',') {
sb.deleteCharAt(sb.length() - 1);
}
sb.append(") ");
}
System.out.println("sql concat done");
// stmt.close();
}catch (RuntimeException e){
}
if (sb.charAt(sb.length() - 1) == ',') {
sb.deleteCharAt(sb.length() - 1);
}
final String url2 = "jdbc:TAOS-RS://" + host2 + ":6041/wpp_server";
try (Connection conn = DriverManager.getConnection(url2, "root", "taosdata")) {
Statement stmt = conn.createStatement();
System.out.println("get conn");
String sqlHead = "INSERT INTO wpp_wind_turbines_data_" + deviceId + " using wpp_wind_turbines_data TAGS(" + deviceId + ", \"admin\", null, \"admin\", null, " + "\"j56fUdq8\") values ";
// 拼接sql
sqlBody = String.valueOf(sb);
String tdengineSql = sqlHead + sqlBody;
System.out.println(tdengineSql);
// INSERT INTO d1001 VALUES (NOW, 10.2, 219, 0.32);
// INSERT INTO d1001 VALUES ('2021-07-13 14:06:32.272', 10.2, 219, 0.32) (1626164208000, 10.15, 217, 0.33);
// 插入数据 2024-04-19 23:45:00.09.57东北276.789.81东北343.1311.79东北
stmt.executeUpdate(tdengineSql);
System.out.println("taos data create done");
stmt.close();
}
}
public static StringBuffer joinSql(StringBuffer sb, String str, Class<?> clazz) throws Exception {
Object o1 = checkAndConvertType(str, clazz);
sb.append(o1);
sb.append(",");
return sb;
}
public static Object checkAndConvertType(String str, Class<?> clazz) {
java.util.Date date = new java.util.Date();
Date sqlDate = new Date(234);
try {
if (str == null || str.isEmpty()) {
return "null";
} else if (clazz.isInstance(new String())) {
return "\"" + str + "\"";
} else if (clazz.isInstance(new Integer(1))) {
return Integer.parseInt(str);
} else if (clazz.isInstance(new Double(1.0))) {
return Double.parseDouble(str);
} else if (clazz.isInstance(new Float(1.0f))) {
return Float.parseFloat(str);
} else if (clazz.isInstance(Long.class)) {
return Long.parseLong(str);
} else if (clazz.isInstance(sqlDate)) {
return "'" + str + "'";
} else {
return str;
}
} catch (NumberFormatException e) {
System.out.println("出错啦：");
System.out.println("str:" + str + " clazz:" + clazz);
e.printStackTrace();
return null;
} finally {
}
}
}
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· Manus的开源复刻OpenManus初探
· AI 智能体引爆开源社区「GitHub 热点速览」
· 从HTTP原因短语缺失研究HTTP/2和HTTP/3的设计差异
· 三行代码完成国际化适配，妙~啊~