将mysql表中数据导入到hive分区事务桶表

3.1.1 逻辑描述

1.删除hv_orders_user_buckets表中对应分区的数据

2.按指定日期从mysql中的数据库查询数据orders0-9和orders_user0-9表的数据导入到hive中的hv_orders_user表指定的分区中。Hv_orders_user是一个分区表&＃xff0c;不具有事务

3.将hv_orders_user表中的数据按分区导入到hv_orders_user_buckets表对应的分区中。hv_orders_user_buckets表具有分区和事务。

4.删除指定表hv_orders_use中分区的数据

为何要先把数据加载到hv_orders_user表&＃xff08;只具有分区&＃xff09;&＃xff0c;再合并到hv_orders_user_buckets&＃xff08;分区事务表&＃xff09;&＃xff1f;

因为直接将其加载到hv_orders_user_buckets&＃xff08;具有分区事务&＃xff09;的表中&＃xff0c;报错&＃xff0c;不支持&＃xff0c;只能将数据先存储到hv_orders_user表后&＃xff0c;经过中转&＃xff0c;合并到hv_orders_user_buckets表中

19/07/10 16:05:05 INFO hive.HiveImport: FAILED: SemanticException Unable to load data to destination table. Error: The file that you are trying to load does not match the file format of the destination table.

19/07/10 16:05:05 ERROR tool.ImportTool: Import failed: java.io.IOException: Hive exited with status 64

at org.apache.sqoop.hive.HiveImport.executeExternalHiveScript(HiveImport.java:384)

at org.apache.sqoop.hive.HiveImport.executeScript(HiveImport.java:337)

at org.apache.sqoop.hive.HiveImport.importTable(HiveImport.java:241)

at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:537)

at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:628)

at org.apache.sqoop.Sqoop.run(Sqoop.java:147)

at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)

at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)

at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)

at org.apache.sqoop.Sqoop.runTool(Sqoop.java:243)

at org.apache.sqoop.Sqoop.main(Sqoop.java:252)

3.1.2 新建表

#订单用户集成表&＃xff08;临时的中转表没有事务&＃xff09;

create table hv_orders_user(

user_id bigint,

order_no  string,

total_amount decimal(10,2),

order_amount  decimal(10,2),

mi_amount     decimal(10,2),

unlimited_card_amount   decimal(10,2),

mi_card_amount  decimal(10,2),

give_mi_amount  decimal(10,2),

coupon_amount  decimal(10,2),

activity_amount  decimal(10,2),

pay_status int,

pay_method int,

end_time string)

PARTITIONED BY (ymd string)

ROW FORMAT DELIMITED  FIELDS TERMINATED BY &＃39;\t&＃39;;

#订单用户集成表&＃xff08;具有事务&＃xff0c;分区&＃xff0c;桶表&＃xff09;

create table  if not exists  hv_orders_user_buckets(

user_id bigint,

order_no  string,

total_amount decimal(10,2),

order_amount  decimal(10,2),

mi_amount     decimal(10,2),

unlimited_card_amount   decimal(10,2),

mi_card_amount  decimal(10,2),

give_mi_amount  decimal(10,2),

coupon_amount  decimal(10,2),

activity_amount  decimal(10,2),

pay_status int,

pay_method int,

end_time string)

PARTITIONED BY (ymd string)

clustered by (user_id) into 10 buckets

ROW FORMAT DELIMITED  FIELDS TERMINATED BY &＃39;\t&＃39;

stored as orc

TBLPROPERTIES(&＃39;transactional&＃39;&＃61;&＃39;true&＃39;);

3.1.3 脚本文件import-ordersdata.sh

#!/bin/bash
#要遍历的表序号
table_name&＃61;(0 1 2 3 4 5 6 7 8 9)
analysis_date&＃61;$1#开始执行方法
function start(){echo "...........第一步&＃xff1a;开始删除hv_orders_user_buckets表中的ymd&＃61;${analysis_date}的分区数据.............................................................................."/opt/hive-2.3.5/bin/hive -e "use hv_user_profile;ALTER TABLE hv_orders_user_buckets DROP partition(ymd&＃61;&＃39;$analysis_date&＃39;);" for str in ${table_name[&＃64;]}doecho "...........第二步&＃xff1a;str:orders_${str},将mysql中orders_${str}表导入到hive的hv_orders_user表中&＃xff0c;分区为:ymd&＃61;$analysis_date................................................................................................"sqoop import --connect jdbc:mysql://10.1.11.110:3310/meboth-userprofile?characterEncoding&＃61;UTF-8 --username baojia_xm --password &＃39;DgisNKhg&＃39; --query "select a.user_id,a.order_no ,a.total_amount,a.order_amount,a.mi_amount,a.unlimited_card_amount,a.mi_card_amount,a.give_mi_amount,b.coupon_amount,b.activity_amount,a.pay_status,a.pay_method,b.end_time from orders_${str} as a ,orders_user_${str} as b where a.order_no&＃61;b.order_no and b.end_time like &＃39;${analysis_date}%&＃39; AND \$CONDITIONS " \--target-dir &＃39;/user/hive/warehouse/hv_user_profile.db/hv_orders_user_temp&＃39; --delete-target-dir --split-by a.user_id --hive-import --hive-database hv_user_profile --hive-table hv_orders_user --hive-drop-import-delims --hive-partition-key ymd --hive-partition-value ${analysis_date} --fields-terminated-by &＃39;\t&＃39; --m 5 --lines-terminated-by "\n";echo ".......... 第三步&＃xff1a;str:orders_${str}, 将hv_orders_user表分区ymd&＃61;$analysis_date的数据执行合并到hv_orders_user_buckets表分区ymd&＃61;$analysis_date中........................................................."/opt/hive-2.3.5/bin/hive -e "use hv_user_profile;insert into table hv_orders_user_buckets partition(ymd&＃61;&＃39;$analysis_date&＃39;) select user_id,order_no,total_amount,order_amount,mi_amount,unlimited_card_amount,mi_card_amount,give_mi_amount,coupon_amount,activity_amount,pay_status,pay_method,end_time from hv_orders_user where ymd&＃61;&＃39;$analysis_date&＃39;;"echo "...........第四步&＃xff1a;str:orders_${str},删除表hv_orders_user表中&＃xff0c;分区为:ymd&＃61;$analysis_date 中的数据........................................................."/opt/hive-2.3.5/bin/hive -e "use hv_user_profile;ALTER TABLE hv_orders_user DROP partition(ymd&＃61;&＃39;$analysis_date&＃39;);"echo ".............................本次循环执行表为:orders_${str}&＃xff0c;分区为:ymd&＃61;$analysis_date中的数据成功successfully!!!!!!!........................................................."done
echo "程序执行完成!!!!$1"
}#程序的入口
start

3.1.4 执行脚本

[www&＃64;1-11-100 opt]$ sh import-ordersdata.sh 2019-06

3.1.5 查看结果