datax把mysql数据导入hive，以及hive导数据到mysql

1.首先在集群安装datax应用，只需要在一台服务器安装就可以

 

2.编辑json脚本：我选择在notepad++进行编辑，保存的时候可以直接选择json格式

 3.json脚本

{"job":{
        "setting":{
            "speed":{
                "channel":1
            }
        },
        "content":[
            {
                "reader":{
                    "name":"mysqlreader",
                    "parameter":{
                        "username":"数据库",
                        "password":"密码",
                        "column": [  
				"ID",    //字段
				"NAME",
				"professional",
                "type"
                        ],
                        "connection": [
                            {
                                "table": [
                                    "表名"
                                ],
                                "jdbcUrl":[
                                    "jdbc:mysql://IP地址:端口号/数据库名"  //这个数据库名是表所在的库名
                                ]
                            }
                        ]
                    }
                },
                "writer":{
                    "name":"hdfswriter",
                    "parameter":{
                        "defaultFS":"hdfs://IP地址:9000", //如果记不得可以在haddoop里面的core-site.xml这个配置查找到
                        "fileType":"text",
                        "path":"/user/hive/warehouse/tangf.db/test_task/202109",//hive保存路径，我的是202109是分区名
                        "fileName":"202109",
                        "column":[
                            {
                                "name": "ID",
                                "type": "INT"  //如果mysql是varchar类型，就不能是INT类型
                            },
                            {
                                "name": "NAME",
                                "type": "STRING"
                            },
                            {
                                "name": "professional",
                                "type": "STRING"
                            },
                            {
                                "name": "type",
                                "type": "STRING"
                            }
                        ],
                        "writeMode":"append",
                        "fieldDelimiter":"," //逗号还是\t，取决于建立hive表格决定
                        
                    }
                }
            }
        ]
    }
}

4.将json脚本上传到datax目录下面

5.创建mysql表格

CREATE TABLE test (
  ID varchar(32) NOT NULL,
  NAME varchar(64) DEFAULT NULL,
  professional varchar(64) DEFAULT NULL,
  type varchar(64) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

 创建hive表格

CREATE TABLE test_task(
ID INT,
NAME  STRING,
professional STRING,
type STRING
)
PARTITIONED BY (DAY STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';  //决定json文件"fieldDelimiter":","

5.在datax 的bin目录下执行脚本

./datax.py ./mysql_to_hive.json

 

hive到数据入mysql：

配置json文件，其他操作步骤与上面一样：

{
    "job": {
        "setting": {
            "speed": {
                "channel": 3
            }
        },
        "content": [
            {
                "reader": {
                    "name": "hdfsreader",
                    "parameter": {
                        "path": "hive文件路径",
                        "defaultFS": "hdfs://IP:端口号",
                    
                       "column":[
                            {
                                "index": 0,
                                "type": "Long"  //如果原表名字是int，需要改为long
                               },
                               {
                                "index": 1,
                                "type": "String"
                               },
                               {
                                "index": 2,
                                "type": "String"
                               },
                               {
                                "index": 3,
                                "type": "String"
                               }
                     ],
						
                        "fileType": "text",
                        "encoding": "UTF-8",
                        "fieldDelimiter": ","
                
                    }

                },
                "writer": {
                    "name": "mysqlwriter",
                    "parameter": {
                        "writeMode": "insert",
                        "username": "数据库",
                        "password": "密码",
                        "column": [
                            "ID",
                            "NAME",
                            "professional",
                            "type"   
                   
                        ],
                     
                        "connection": [
                            {
                                "jdbcUrl":"jdbc:mysql://IP:端口号/数据库名",
                                "table": [
                                    "表名"
                                ]
                            }
                        ]
                    }
            }
        }
        ]
    }
}

 如果有什么不对的麻烦指正，谢谢