使用DataX和sqoop将数据从MySQL导入Hive
一、DataX简述
DataX 是阿里云 DataWorks数据集成 的开源版本，在阿里巴巴集团内被广泛使用的离线数据同步工具/平台。DataX 实现了包括 MySQL、Oracle、OceanBase、SqlServer、Postgre、HDFS、Hive、ADS、HBase、TableStore(OTS)、MaxCompute(ODPS)、Hologres、DRDS 等各种异构数据源之间高效的数据同步功能。
源码地址：https://github.com/alibaba/DataX
二、sqoop简述
Apache Sqoop（SQL-to-Hadoop）项目旨在协助RDBMS与Hadoop之间进行高效的大数据交流。用户可以在 Sqoop 的帮助下，轻松地把关系型数据库的数据导入到 Hadoop 与其相关的系统 (如HBase和Hive)中；同时也可以把数据从 Hadoop 系统里抽取并导出到关系型数据库里。
Sqoop是一个在结构化数据和Hadoop之间进行批量数据迁移的工具，结构化数据可以是MySQL、Oracle等RDBMS。Sqoop底层用MapReduce程序实现抽取、转换、加载，MapReduce天生的特性保证了并行化和高容错率，而且相比Kettle等传统ETL工具，任务跑在Hadoop集群上，减少了ETL服务器资源的使用情况。在特定场景下，抽取过程会有很大的性能提升。
三、需求背景
MySQL中的库ecommerce有以下表，需要将以下表导入Hive中的ecommerce库中作为ODS层以后进一步处理。
- 用户信息表（t_member）
- 用户信息表（t_member）
- 用户地址表（t_member_addr）
- 商品信息表（t_commodity）
- 商品类别信息表（t_commodity_cate）
- 订单表（t_order）
- 订单商品表（t_order_commodity）
- 优惠券表（t_coupon）
- 用户优惠券表（t_coupon_member）
- 订单优惠券表（t_coupon_order）
- 快递表（t_delivery）
- 反馈表（t_feedback）
- 商店表（t_shop）
- 商家订单表（t_shop_order，订单对于卖家也有记录）
- 后台用户表（t_user，员工表）
四、实现方式
可以通过sqoop和DataX两种方式来实现数据从MySQL导入Hive功能。
3.1 使用DataX将数据从MySQL导入Hive
在Hive里建库 ecommerce
create database ecommerce;
使用Python写入DataX配置文件批量生成脚本gen_import_config.py
# coding=utf-8
import json
import getopt
import os
import sys
import MySQLdb
#MySQL相关配置，需根据实际情况作出修改
mysql_host = "hadoop102"
mysql_port = "3306"
mysql_user = "root"
mysql_passwd = "123456"
#HDFS NameNode相关配置，需根据实际情况作出修改
hdfs_nn_host = "hadoop102"
hdfs_nn_port = "8020"
#生成配置文件的目标路径，可根据实际情况作出修改
output_path = "/opt/module/datax/job/import"
#获取mysql连接
def get_connection():
return MySQLdb.connect(host=mysql_host, port=int(mysql_port), user=mysql_user, passwd=mysql_passwd)
#获取表格的元数据 包含列名和数据类型
def get_mysql_meta(database, table):
connection = get_connection()
cursor = connection.cursor()
sql = "SELECT COLUMN_NAME,DATA_TYPE from information_schema.COLUMNS WHERE TABLE_SCHEMA=%s AND TABLE_NAME=%s ORDER BY ORDINAL_POSITION"
cursor.execute(sql, [database, table])
fetchall = cursor.fetchall()
cursor.close()
connection.close()
return fetchall
#获取mysql表的列名
def get_mysql_columns(database, table):
return map(lambda x: x[0], get_mysql_meta(database, table))
#将获取的元数据中mysql的数据类型转换为hive的数据类型 写入到hdfswriter中
def get_hive_columns(database, table):
def type_mapping(mysql_type):
mappings = {
"bigint": "bigint",
"int": "bigint",
"smallint": "bigint",
"tinyint": "bigint",
"decimal": "string",
"double": "double",
"float": "float",
"binary": "string",
"char": "string",
"varchar": "string",
"datetime": "string",
"time": "string",
"timestamp": "string",
"date": "string",
"text": "string",
"tinytext": "string"
}
return mappings[mysql_type]
meta = get_mysql_meta(database, table)
return map(lambda x: {"name": x[0], "type": type_mapping(x[1].lower())}, meta)
#生成json文件
def generate_json(source_database, source_table):
job = {
"job": {
"setting": {
"speed": {
"channel": 3
},
"errorLimit": {
"record": 0,
"percentage": 0.02
}
},
"content": [{
"reader": {
"name": "mysqlreader",
"parameter": {
"username": mysql_user,
"password": mysql_passwd,
"column": get_mysql_columns(source_database, source_table),
"splitPk": "",
"connection": [{
"table": [source_table],
"jdbcUrl": ["jdbc:mysql://" + mysql_host + ":" + mysql_port + "/" + source_database]
}]
}
},
"writer": {
"name": "hdfswriter",
"parameter": {
"defaultFS": "hdfs://" + hdfs_nn_host + ":" + hdfs_nn_port,
"fileType": "text",
"path": "${targetdir}",
"fileName": source_table,
"column": get_hive_columns(source_database, source_table),
"writeMode": "append",
"fieldDelimiter": "\t",
"compress": "gzip"
}
}
}]
}
}
if not os.path.exists(output_path):
os.makedirs(output_path)
with open(os.path.join(output_path, ".".join([source_database, source_table, "json"])), "w") as f:
json.dump(job, f)
def main(args):
source_database = ""
source_table = ""
options, arguments = getopt.getopt(args, '-d:-t:', ['sourcedb=', 'sourcetbl='])
for opt_name, opt_value in options:
if opt_name in ('-d', '--sourcedb'):
source_database = opt_value
if opt_name in ('-t', '--sourcetbl'):
source_table = opt_value
generate_json(source_database, source_table)
if __name__ == '__main__':
main(sys.argv[1:])
由于需要使用Python访问Mysql数据库，故需安装驱动，命令如下：
yum install -y MySQL-python
调用上述的配置文件脚本
#!/bin/bash
python /root/bin/gen_import_config.py -d ecommerce -t t_commodity
python /root/bin/gen_import_config.py -d ecommerce -t t_commodity_cate
python /root/bin/gen_import_config.py -d ecommerce -t t_coupon
python /root/bin/gen_import_config.py -d ecommerce -t t_coupon_member
python /root/bin/gen_import_config.py -d ecommerce -t t_coupon_order
python /root/bin/gen_import_config.py -d ecommerce -t t_delivery
python /root/bin/gen_import_config.py -d ecommerce -t t_feedback
python /root/bin/gen_import_config.py -d ecommerce -t t_member
python /root/bin/gen_import_config.py -d ecommerce -t t_member_addr
python /root/bin/gen_import_config.py -d ecommerce -t t_order
python /root/bin/gen_import_config.py -d ecommerce -t t_order_commodity
python /root/bin/gen_import_config.py -d ecommerce -t t_shop
python /root/bin/gen_import_config.py -d ecommerce -t t_shop_order
python /root/bin/gen_import_config.py -d ecommerce -t t_user
在/opt/module/datax/job/import可以看到json文件
编写脚本将mysql数据传入HDFS中
#!/bin/bash
DATAX_HOME=/opt/module/datax
#处理目标路径，此处的处理逻辑是，如果目标路径不存在，则创建；若存在，则清空，目的是保证同步任务可重复执行
handle_targetdir() {
hadoop fs -test -e $1
if [[ $? -eq 1 ]]; then
echo "路径$1不存在，正在创建......"
hadoop fs -mkdir -p $1
else
echo "路径$1已经存在"
fs_count=$(hadoop fs -count $1)
content_size=$(echo $fs_count | awk '{print $3}')
if [[ $content_size -eq 0 ]]; then
echo "路径$1为空"
else
echo "路径$1不为空，正在清空......"
hadoop fs -rm -r -f $1/*
fi
fi
}
#数据同步
import_data() {
datax_config=$1
target_dir=$2
handle_targetdir $target_dir
python $DATAX_HOME/bin/datax.py -p"-Dtargetdir=$target_dir" $datax_config
}
case $1 in
"t_commodity")
import_data /opt/module/datax/job/import/ecommerce.t_commodity.json /ecommerce/t_commodity
;;
"t_commodity_cate")
import_data /opt/module/datax/job/import/ecommerce.t_commodity_cate.json /ecommerce/t_commodity_cate
;;
"t_coupon")
import_data /opt/module/datax/job/import/ecommerce.t_coupon.json /ecommerce/t_coupon
;;
"t_coupon_member")
import_data /opt/module/datax/job/import/ecommerce.t_coupon_member.json /ecommerce/t_coupon_member
;;
"t_coupon_order")
import_data /opt/module/datax/job/import/ecommerce.t_coupon_order.json /ecommerce/t_coupon_order
;;
"t_delivery")
import_data /opt/module/datax/job/import/ecommerce.t_delivery.json /ecommerce/t_delivery
;;
"t_feack")
import_data /opt/module/datax/job/import/ecommerce.t_feack.json /ecommerce/t_feack
;;
"t_member")
import_data /opt/module/datax/job/import/ecommerce.t_member.json /ecommerce/t_member
;;
"t_member_addr")
import_data /opt/module/datax/job/import/ecommerce.t_member_addr.json /ecommerce/t_member_addr
;;
"t_order")
import_data /opt/module/datax/job/import/ecommerce.t_order.json /ecommerce/t_order
;;
"t_order_commodity")
import_data /opt/module/datax/job/import/ecommerce.t_order_commodity.json /ecommerce/t_order_commodity
;;
"t_shop")
import_data /opt/module/datax/job/import/ecommerce.t_shop.json /ecommerce/t_shop
;;
"t_shop_order")
import_data /opt/module/datax/job/import/ecommerce.t_shop_order.json /ecommerce/t_shop_order
;;
"t_user")
import_data /opt/module/datax/job/import/ecommerce.t_user.json /ecommerce/t_user
;;
"all")
import_data /opt/module/datax/job/import/ecommerce.t_commodity.json /ecommerce/t_commodity
import_data /opt/module/datax/job/import/ecommerce.t_commodity_cate.json /ecommerce/t_commodity_cate
import_data /opt/module/datax/job/import/ecommerce.t_coupon.json /ecommerce/t_coupon
import_data /opt/module/datax/job/import/ecommerce.t_coupon_member.json /ecommerce/t_coupon_member
import_data /opt/module/datax/job/import/ecommerce.t_coupon_order.json /ecommerce/t_coupon_order
import_data /opt/module/datax/job/import/ecommerce.t_delivery.json /ecommerce/t_delivery
import_data /opt/module/datax/job/import/ecommerce.t_feedback.json /ecommerce/t_feedback
import_data /opt/module/datax/job/import/ecommerce.t_member.json /ecommerce/t_member
import_data /opt/module/datax/job/import/ecommerce.t_member_addr.json /ecommerce/t_member_addr
import_data /opt/module/datax/job/import/ecommerce.t_order.json /ecommerce/t_order
import_data /opt/module/datax/job/import/ecommerce.t_order_commodity.json /ecommerce/t_order_commodity
import_data /opt/module/datax/job/import/ecommerce.t_shop.json /ecommerce/t_shop
import_data /opt/module/datax/job/import/ecommerce.t_shop_order.json /ecommerce/t_shop_order
import_data /opt/module/datax/job/import/ecommerce.t_user.json /ecommerce/t_user
;;
esac
调用上述函数可以在HDFS上看到数据库文件
启动hiveserver2在Hive中建表，与MySQL字段映射关系如下
写入SQL建表及加载HDFS上的数据
DROP TABLE IF EXISTS `t_commodity`;
CREATE TABLE `t_commodity` (
`id` BIGINT,
`commodity_name` STRING,
`commodity_price` DECIMAL(10,2),
`commodity_cate_one` BIGINT,
`commodity_cate_two` BIGINT,
`create_user_id` BIGINT,
`status` BIGINT,
`create_time` STRING,
`update_time` STRING
)COMMENT '商品信息表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
NULL DEFINED AS ''
LOCATION '/warehouse/ecommerce/t_commodity/';
load data inpath 'hdfs:/ecommerce/t_commodity' into table `t_commodity`;
DROP TABLE IF EXISTS `t_commodity_cate`;
CREATE TABLE `t_commodity_cate` (
`id` BIGINT,
`cate_name` STRING,
`cate_parent_id` BIGINT,
`create_user_id` BIGINT,
`status` BIGINT,
`create_time` STRING,
`update_time` STRING
)COMMENT '商品类别信息表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
NULL DEFINED AS ''
LOCATION '/warehouse/ecommerce/t_commodity_cate/';
load data inpath 'hdfs:/ecommerce/t_commodity_cate' into table `t_commodity_cate`;
DROP TABLE IF EXISTS `t_coupon`;
CREATE TABLE `t_coupon` (
`id` BIGINT,
`coupon_name` STRING,
`coupon_price` decimal(10,2),
`create_user_id` BIGINT,
`create_time` STRING,
`update_time` STRING
)COMMENT '优惠券表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
NULL DEFINED AS ''
LOCATION '/warehouse/ecommerce/t_coupon/';
load data inpath 'hdfs:/ecommerce/t_coupon' into table `t_coupon`;
DROP TABLE IF EXISTS `t_coupon_member`;
CREATE TABLE `t_coupon_member` (
`id` BIGINT,
`coupon_id` BIGINT,
`member_id` BIGINT,
`coupon_channel` BIGINT COMMENT '1 用户购买 2 公司发放',
`create_time` STRING,
`update_time` STRING
)COMMENT '用户优惠券表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
NULL DEFINED AS ''
LOCATION '/warehouse/ecommerce/t_coupon_member/';
load data inpath 'hdfs:/ecommerce/t_coupon_member' into table `t_coupon_member`;
DROP TABLE IF EXISTS `t_member`;
CREATE TABLE `t_member` (
`id` BIGINT,
`name` STRING,
`password` STRING,
`sex` STRING,
`phone` STRING,
`address_default_id` BIGINT,
`member_channel` BIGINT COMMENT '1 IOS 2 android 3 微信小程序 4 微信公众号 5 h5',
`mp_open_id` STRING COMMENT '微信公众号openId',
`status` BIGINT,
`create_time` STRING,
`update_time` STRING
)COMMENT '用户信息表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
NULL DEFINED AS ''
LOCATION '/warehouse/ecommerce/t_member/';
load data inpath 'hdfs:/ecommerce/t_member' into table `t_member`;
DROP TABLE IF EXISTS `t_member_addr`;
CREATE TABLE `t_member_addr` (
`id` BIGINT,
`member_id` BIGINT,
`contact_person` STRING,
`contact_phone` STRING,
`address` STRING,
`create_time` STRING,
`update_time` STRING
)COMMENT '用户地址表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
NULL DEFINED AS ''
LOCATION '/warehouse/ecommerce/t_member_addr/';
load data inpath 'hdfs:/ecommerce/t_member_addr' into table `t_member_addr`;
DROP TABLE IF EXISTS `t_delivery`;
CREATE TABLE `t_delivery` (
`id` BIGINT,
`delivery_no` STRING,
`order_id` BIGINT,
`shop_id` BIGINT,
`postman` BIGINT,
`pick_time` STRING,
`arrive_time` STRING,
`member_id` BIGINT,
`member_addr_id` BIGINT
)COMMENT '快递表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
NULL DEFINED AS ''
LOCATION '/warehouse/ecommerce/t_delivery/';
load data inpath 'hdfs:/ecommerce/t_delivery' into table `t_delivery`;
DROP TABLE IF EXISTS `t_feedback`;
CREATE TABLE `t_feedback` (
`id` BIGINT,
`member_id` BIGINT,
`create_user_id` BIGINT,
`feedback_content` STRING,
`feedback_type` BIGINT COMMENT '1 破损 2 缺货 3 错货 4 投诉'
)COMMENT '反馈表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
NULL DEFINED AS ''
LOCATION '/warehouse/ecommerce/t_feedback/';
load data inpath 'hdfs:/ecommerce/t_feedback' into table `t_feedback`;
DROP TABLE IF EXISTS `t_shop`;
CREATE TABLE `t_shop` (
`id` BIGINT,
`shop_name` STRING,
`city_id` BIGINT,
`city_name` STRING,
`area_id` BIGINT,
`area_name` STRING,
`charge_user` STRING,
`create_time` STRING,
`update_time` STRING
)COMMENT '商店表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
NULL DEFINED AS ''
LOCATION '/warehouse/ecommerce/t_shop/';
load data inpath 'hdfs:/ecommerce/t_shop' into table `t_shop`;
DROP TABLE IF EXISTS `t_shop_order`;
CREATE TABLE `t_shop_order` (
`id` BIGINT,
`shop_id` BIGINT,
`order_id` BIGINT,
`start_time` STRING,
`done_time` STRING
)COMMENT '商家订单表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
NULL DEFINED AS ''
LOCATION '/warehouse/ecommerce/t_shop_order/';
load data inpath 'hdfs:/ecommerce/t_shop_order' into table `t_shop_order`;
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
`id` BIGINT,
`user_name` STRING,
`user_password` STRING,
`user_phone` STRING,
`create_time` STRING,
`update_time` STRING
)COMMENT '后台用户表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
NULL DEFINED AS ''
LOCATION '/warehouse/ecommerce/t_user/';
load data inpath 'hdfs:/ecommerce/t_user' into table `t_user`;
DROP TABLE IF EXISTS `t_order`;
CREATE TABLE `t_order` (
`order_id` BIGINT,
`member_id` BIGINT,
`origin_price` decimal(10,2),
`pay_price` decimal(10,2),
`shop_id` BIGINT,
`shop_name` STRING,
`order_status` STRING COMMENT '1,进行中 2 已完成 3 已取消',
`create_time` STRING,
`update_time` STRING
)COMMENT '订单表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
NULL DEFINED AS ''
LOCATION '/warehouse/ecommerce/t_order/';
load data inpath 'hdfs:/ecommerce/t_order' into table `t_order`;
DROP TABLE IF EXISTS `t_order_commodity`;
CREATE TABLE `t_order_commodity` (
`id` BIGINT,
`order_id` BIGINT,
`commodity_id` BIGINT,
`commodity_name` STRING,
`commodity_num` BIGINT,
`commodity_price` decimal(10,2),
`create_time` STRING,
`update_time` STRING
)COMMENT '订单商品表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
NULL DEFINED AS ''
LOCATION '/warehouse/ecommerce/t_order_commodity/';
load data inpath 'hdfs:/ecommerce/t_order_commodity' into table `t_order_commodity`;
在Hive中可以看到如下表格
数据也都进入了Hive表中
3.2 通过sqoop将数据从MySQL导入Hive
在Hive里建库 ecommerce
create database ecommerce;
运行下面的函数可以将数据从MySQL导入Hive
#!/bin/bash
# 定义一个函数，执行sqoop命令，所以执行脚本应该在sqoop/bin下面
sq()
{
./sqoop import \
--connect jdbc:mysql://hadoop102:3306/ecommerce \
--username root \
--password 000000 \
--table $1 \
--num-mappers 1 \
--hive-import \
--fields-terminated-by "\t" \
--hive-overwrite \
--hive-database ecommerce \
--hive-table $1
}
sq t_commodity
sq t_commodity_cate
sq t_coupon
sq t_coupon_member
sq t_coupon_order
sq t_delivery
sq t_feedback
sq t_member
sq t_member_addr
sq t_order
sq t_order_commodity
sq t_shop
sq t_shop_order
sq t_user
四、总结
4.1 Datax主要特点
1、异构数据库 和 文件系统 之间的数据交换；
2、采用 Framework + plugin 架构构建，
Framework 处理了缓冲，流控，并发，上下文加载
等高速数据交换的大部分技术问题，提供了简单的接口与插件交互，
插件仅需实现对数据处理系统的访问；
3、数据传输过程在单进程内完成，全内存操作，不读写磁盘，也没有 IPC；
4、开放式的框架，开发者可以在极短的时间开发一个新插件
以快速支持新的数据库/文件系统。
4.2 Sqoop主要特点
1、可以将关系型数据库中的数据导入 hdfs、hive 或者 hbase 等 hadoop 组件中，
也可将 hadoop 组件中的数据导入到关系型数据库中；
2、sqoop 在导入导出数据时，充分采用了 map-reduce 计算框架，
根据输入条件生成一个 map-reduce 作业，在 hadoop 集群中运行。
采用 map-reduce 框架同时在多个节点进行 import 或者 export 操作，
速度比单节点运行多个并行导入导出效率高，同时提供了良好的并发性和容错性；
3、支持 insert、update 模式，可以选择参数，若内容存在就更新，若不存在就插入；
4、对国外的主流关系型数据库支持性更好。
4.3 Sqoop 和 Datax的区别
1、sqoop 采用 map-reduce 计算框架进行导入导出，而 datax 仅仅在运行 datax 的单台机器上进行数据的抽取和加载，速度比 sqoop 慢了许多；
2、sqoop 只可以在关系型数据库和 hadoop 组件之间进行数据迁移，
而在 hadoop 相关组件之间，比如 hive 和 hbase 之间就无法使用 sqoop 互相导入导出数据，同时在关系型数据库之间，比如 mysql 和 oracle 之间也无法通过 sqoop 导入导出数据。
datax 能够分别实现关系型数据库 hadoop 组件之间、关系型数据库之间、hadoop 组件之间的数据迁移；
3、sqoop 是专门为 hadoop 而生，对 hadoop 支持度好，而 datax 可能会出现不支持高版本 hadoop 的现象；
4、sqoop 只支持官方提供的指定几种关系型数据库和 hadoop 组件之间的数据交换，而在 datax 中，用户只需根据自身需求修改文件，生成相应 rpm 包，自行安装之后就可以使用自己定制的插件；
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
