sqoop从mysql导数到hive动态分区

1.创建有dt的普通表homs.oms_order_tmp
CREATE TABLE homs.oms_order_tmp(id int,order_id bigint,order_name string,custom string,created_time string,created_by date,updated_time string,updated_by string,status tinyint,day string) COMMENT ‘订单临时表’ STORED As textfile;
问题：有可能创建parquet表，导数成功后查询会报错，说格式错误，CREATE TABLE homs.oms_order_tmp(id int,order_id bigint,order_name string,custom string,created_time string,created_by date,updated_time string,updated_by string,status tinyint,day string) COMMENT ‘订单临时表’ STORED As parquet TBLPROPERTIES (“parquet.compress”=“SNAPPY”);
2.创建dt的分区表oms_order
CREATE TABLE homs.oms_order(id int,order_id bigint,order_name string,custom string,created_time date,created_by date,updated_time date,updated_by string,status tinyint) partitioned by(day string) COMMENT ‘Welcome to order!’ STORED AS parquet TBLPROPERTIES (“parquet.compress”=“SNAPPY”);
3.sqoop导入数据到oms_order_tmp，查询的时候多一列dt
sqoop import --hive-import
–connect “jdbc:mysql://192.168.42.1:3306/oms2?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&dontTrackOpenResources=true&defaultFetchSize=50000&useCursorFetch=true”
–driver com.mysql.jdbc.Driver
–username archive
–password 123
–query "select *,left(created_time,10) day from oms_order where $CONDITIONS "
–hive-table homs.oms_order_tmp
–target-dir /user/hive/warehouse/homs.db/oms_order_tmp
–delete-target-dir
–fetch-size 50000
–null-string ‘\N’ --null-non-string ‘\N’
–hive-drop-import-delims
-m 6 --split-by ‘id’;

备注：特别要注意日期截取为字符串的过程，艰难又困苦

当导数有问题时，先sqoop查询数据是什么样子的
sqoop eval --connect “jdbc:mysql://192.168.42.1:3306/oms2?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&dontTrackOpenResources=true&defaultFetchSize=50000&useCursorFetch=true”
–driver com.mysql.jdbc.Driver
–username archive
–password 123
–query “select *,left(created_time,10) day from oms_order” ;

4.插入到分区表
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
SET hive.exec.max.dynamic.partitions.pernode=1000;
insert overwrite table homs.oms_order partition(day) select * from homs.oms_order_tmp;

FQA
1.执行导数时报错，java heap space、物理内存或虚拟内存不足时，需要正确配置mapreduce和yarn资源的相关参数，详见：https://blog.csdn.net/zhanglh046/article/details/78567070