Datax开发实例-hive2mysql
Datax部署
下载地址:http://datax-opensource.oss-cn-hangzhou.aliyuncs.com/datax.tar.gz
下载完成以后 放置服务器,解压可直接使用
tar -zxvf datax.tar.gz
创建hive表制造数据
--创建测试hive表
create table if not exists adm_test (
bizdate string comment '业务日期'
,name string comment '姓名'
) partitioned by (dt string);
--由于是测试直接插入一条数据
insert into adm_test partition (dt =20200629)
select '20200629' bizdate
,'张三' name;
创建mysql表
--这里要注意下mysql和hive的字段类型不一样
create table if not exists adm_test (
bizdate varchar(32)
,name varchar(32)
) ;
Datax与mysql关系对比
DataX 内部类型 | Mysql 数据类型 |
---|---|
Long | int, tinyint, smallint, mediumint, int, bigint, year |
Double | float, double, decimal |
String | varchar, char, tinytext, text, mediumtext, longtext |
Date | date, datetime, timestamp, time |
Boolean | bit, bool |
Bytes | tinyblob, mediumblob, blob, longblob, varbinary |
编写Datax配置文件
文件位置:
vim /alidata1/admin/datax/conf/adm_test.json
##这里提供两种配置文件,hadoop ha和hadoop单节点
{
"job": {
"setting": {
"speed": {
"channel": 1
}
},
"content": [{
"reader": {
"name": "hdfsreader",
"parameter": {
"path": "/user/hive/warehouse/test.db/adm_test/dt=${bizdate} 文件存放位置有分区一定要指定到分区里面",
"defaultFS": "hdfs://端口号或者域名/",
"hadoopConfig": {
"dfs.nameservices": "随便给个名字:nameser",
"dfs.ha.namenodes.nameser": "cdh0,cdh2",
"dfs.namenode.rpc-address.nameser.cdh0": "cdh0:8020",
"dfs.namenode.rpc-address.nameser.cdh2": "cdh2:8020",
"dfs.client.failover.proxy.provider.nameser": "org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider"
},
"partition": [
"dt='${bizdate}'"
],
"column": [{
"index": 0,
"type": "string"
}, {
"index": 1,
"type": "string"
}
],
"fileType": "text",
"encoding": "UTF-8",
"fieldDelimiter": "\u0001"
}
},
"writer": {
"name": "mysqlwriter",
"parameter": {
"column": [
"bizdate",
"name"
],
"preSql": [
"delete from adm_test where bizdate = '${bizdate}' ; "
],
"writeMode": "update",
"username": "****",
"password": "****",
"connection": [{
"jdbcUrl": "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8",
"table": [
"adm_test"
]
}]
}
}
}]
}
}
##单节点
{
"job": {
"content": [{
"writer": {
"parameter": {
"username": "****",
"writeMode": "insert",
"column": [
"bizdate_date",
"name"
],
"connection": [{
"table": [
"adm_test"
],
"jdbcUrl": "jdbc:localhost:3306/test?useUnicode=true&characterEncoding=utf8"
}],
"preSql": [
"delete from adm_test where bizdate_date = '${bizdate}' ; "
],
"password": "****"
},
"name": "mysqlwriter"
},
"reader": {
"parameter": {
"encoding": "UTF-8",
"column": [{
"index": 0,
"type": "string"
},
{
"index": 1,
"type": "string"
}
],
"fieldDelimiter": "\u0001",
"fileType": "text",
"partition": [
"dt='${bizdate}'"
],
"path": "/user/hive/warehouse/test.db/adm_test/dt=${bizdate}",
"defaultFS": "hdfs://cdh0:8020"
},
"name": "hdfsreader"
}
}],
"setting": {
"speed": {
"channel": 1
}
}
}
}
执行命令
/alidata1/admin/datax/bin/datax.py -p “-Dbizdate=20200629” /alidata1/admin/datax/conf/adm_test.json