DataX之Hive写入MySQL的NULL值处理

docsz

已于 2022-04-22 07:14:33 修改

阅读量3.9k

点赞数 3

CC 4.0 BY-SA版权

分类专栏： datax hive MySQL 文章标签： hive mysql hadoop

于 2022-03-09 15:07:04 首次发布

本文链接：https://blog.youkuaiyun.com/docsz/article/details/123378101

hive 同时被 3 个专栏收录

28 篇文章

订阅专栏

datax

19 篇文章

订阅专栏

MySQL

10 篇文章

订阅专栏

1、Hive建表

CREATE TABLE `rcdn_ads.ads_rcdn_zc_xq`(
  `qybm` varchar(25) COMMENT '区域编码', 
  `csbm` varchar(25) COMMENT '城市编码', 
  `shengbm` varchar(25) COMMENT '省份编码', 
  `cybm` varchar(25) COMMENT '产业编码', 
  `zclxbm` varchar(25) COMMENT '政策类型编码', 
  `zcztbm` varchar(25) COMMENT '政策状态编码', 
  `zcjbbm` varchar(25) COMMENT '政策级别编码', 
  `zcszbm` varchar(25) COMMENT '政策受众编码', 
  `qylxbm` varchar(25) COMMENT '企业类型编码', 
  `zcbt` varchar(255) COMMENT '政策标题', 
  `zcnr` string COMMENT '政策内容', 
  `zclj` varchar(255) COMMENT '政策链接', 
  `fwbmmc` varchar(100) COMMENT '发文部门名称', 
  `tjrq` date COMMENT '统计日期', 
  `sjly` varchar(255) COMMENT '数据来源')
COMMENT '政策详情表'
row format delimited fields terminated by '|' NULL DEFINED AS '' stored as textfile;

2、DataX脚本

{
	"job": {
		"setting": {
			"speed": {
				"channel": 3
			}
		},
		"content": [
			{
				"reader": {
					"name": "hdfsreader",
					"parameter": {
						"column": [
							{
								"index": 0,
								"type": "STRING"
							},
							{
								"index": 1,
								"type": "STRING"
							},
							{
								"index": 2,
								"type": "STRING"
							},
							{
								"index": 3,
								"type": "STRING"
							},
							{
								"index": 4,
								"type": "STRING"
							},
							{
								"index": 5,
								"type": "STRING"
							},
							{
								"index": 6,
								"type": "STRING"
							},
							{
								"index": 7,
								"type": "STRING"
							},
							{
								"index": 8,
								"type": "STRING"
							},
							{
								"index": 9,
								"type": "STRING"
							},
							{
								"index": 10,
								"type": "STRING"
							},
							{
								"index": 11,
								"type": "STRING"
							},
							{
								"index": 12,
								"type": "STRING"
							},
							{
								"index": 13,
								"type": "DATE"
							},
							{
								"index": 14,
								"type": "STRING"
							}
						],
						"defaultFS": "hdfs://hadoop01:8020",
						"encoding": "UTF-8",
						"fieldDelimiter": "|",
						"fileType": "text",
						"nullFormat": "",
						"path": "/user/hive/warehouse/rcdn_ads.db/ads_rcdn_zc_xq/*"
					}
				},
				"writer": {
					"name": "mysqlwriter",
					"parameter": {
						"column": [
							"qybm",
							"csbm",
							"shengbm",
							"cybm",
							"zclxbm",
							"zcztbm",
							"zcjbbm",
							"zcszbm",
							"qylxbm",
							"zcbt",
							"zcnr",
							"zclj",
							"fwbmmc",
							"tjrq",
							"sjly"
						],
						"connection": [
							{
								"jdbcUrl": "jdbc:mysql://node01:13306/talent_brain?useUnicode=true&characterEncoding=UTF-8&useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=GMT%2b8",
								"table": [
									"t_zc_xq"
								]
							}
						],
						"password": "123456",
						"preSql": [
							"truncate table talent_brain.t_zc_xq"
						],
						"username": "root",
						"writeMode": "insert"
					}
				}
			}
		]
	}
}