网络营销型网站建设,十大高端网站建设,网站链接的常见形式,商城模板网站模板免费下载文章目录 一、概述二、数据装载#xff08;HDFS - Hive#xff09;2.1 创建Hive表2.1.1 业务全量表建表语句2.1.2 业务增量表建表语句2.1.3 流量增量表建表语句 2.2 数据装载2.2.1 初始化装载省份和地区表2.2.2 每日装载业务数据#xff08;1#xff09; 开发脚本… 文章目录 一、概述二、数据装载HDFS - Hive2.1 创建Hive表2.1.1 业务全量表建表语句2.1.2 业务增量表建表语句2.1.3 流量增量表建表语句 2.2 数据装载2.2.1 初始化装载省份和地区表2.2.2 每日装载业务数据1 开发脚本2 授予脚本执行权限3 定时调度 2.2.3 每日装载日志数据1 开发脚本2 授予脚本执行权限3 定时调度 一、概述 入仓时机业务数据、日志数据都同步到HDFS后。即Sqoop同步业务全量表完成后。 业务数据进入HDFS时机全量数据每天凌晨将昨天的数据同步到HDFS昨天目录。增量数据实时同步到HDFS到binlog对应的日期目录。 日志数据进入HDFS时机实时同步到日志行为发生的日期JSON串中的ts字段解析出来的日期。 二、数据装载HDFS - Hive
2.1 创建Hive表 共28张表16张业务全量表、11张业务增量表、1张流量增量表。 注意1Hive表默认是TextFile格式。 注意2省份表和地区表为非分区表。 2.1.1 业务全量表建表语句
DROP TABLE IF EXISTS ods_sku_info_full;
CREATE EXTERNAL TABLE ods_sku_info_full(id STRING COMMENT skuId,spu_id STRING COMMENT spuid,price DECIMAL(16,2) COMMENT 价格,sku_name STRING COMMENT 商品名称,sku_desc STRING COMMENT 商品描述,weight DECIMAL(16,2) COMMENT 重量,tm_id STRING COMMENT 品牌id,category3_id STRING COMMENT 品类id,is_sale STRING COMMENT 是否在售,create_time STRING COMMENT 创建时间
) COMMENT SKU商品表
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY \t
LOCATION /warehouse/dw_ods.db/ods_sku_info_full/;DROP TABLE IF EXISTS ods_base_category1_full;
CREATE EXTERNAL TABLE ods_base_category1_full(id STRING COMMENT id,name STRING COMMENT 名称
) COMMENT 商品一级分类表
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY \t
LOCATION /warehouse/dw_ods.db/ods_base_category1_full/;DROP TABLE IF EXISTS ods_base_category2_full;
CREATE EXTERNAL TABLE ods_base_category2_full(id STRING COMMENT id,name STRING COMMENT 名称,category1_id STRING COMMENT 一级品类id
) COMMENT 商品二级分类表
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY \t
LOCATION /warehouse/dw_ods.db/ods_base_category2_full/;DROP TABLE IF EXISTS ods_base_category3_full;
CREATE EXTERNAL TABLE ods_base_category3_full(id STRING COMMENT id,name STRING COMMENT 名称,category2_id STRING COMMENT 二级品类id
) COMMENT 商品三级分类表
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY \t
LOCATION /warehouse/dw_ods.db/ods_base_category3_full/;DROP TABLE IF EXISTS ods_base_province;
CREATE EXTERNAL TABLE ods_base_province (id STRING COMMENT 编号,name STRING COMMENT 省份名称,region_id STRING COMMENT 地区ID,area_code STRING COMMENT 地区编码,iso_code STRING COMMENT ISO-3166编码供可视化使用,iso_3166_2 STRING COMMENT IOS-3166-2编码供可视化使用
) COMMENT 省份表
ROW FORMAT DELIMITED FIELDS TERMINATED BY \t
LOCATION /warehouse/dw_ods.db/ods_base_province/;DROP TABLE IF EXISTS ods_base_trademark_full;
CREATE EXTERNAL TABLE ods_base_trademark_full (id STRING COMMENT 编号,tm_name STRING COMMENT 品牌名称
) COMMENT 品牌表
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY \t
LOCATION /warehouse/dw_ods.db/ods_base_trademark_full/;DROP TABLE IF EXISTS ods_spu_info_full;
CREATE EXTERNAL TABLE ods_spu_info_full(id STRING COMMENT spuid,spu_name STRING COMMENT spu名称,category3_id STRING COMMENT 品类id,tm_id STRING COMMENT 品牌id
) COMMENT SPU商品表
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY \t
LOCATION /warehouse/dw_ods.db/ods_spu_info_full/;DROP TABLE IF EXISTS ods_favor_info_full;
CREATE EXTERNAL TABLE ods_favor_info_full(id STRING COMMENT 编号,user_id STRING COMMENT 用户id,sku_id STRING COMMENT skuid,spu_id STRING COMMENT spuid,is_cancel STRING COMMENT 是否取消,create_time STRING COMMENT 收藏时间,cancel_time STRING COMMENT 取消时间
) COMMENT 商品收藏表
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY \t
LOCATION /warehouse/dw_ods.db/ods_favor_info_full/;DROP TABLE IF EXISTS ods_cart_info_full;
CREATE EXTERNAL TABLE ods_cart_info_full(id STRING COMMENT 编号,user_id STRING COMMENT 用户id,sku_id STRING COMMENT skuid,cart_price DECIMAL(16,2) COMMENT 放入购物车时价格,sku_num BIGINT COMMENT 数量,sku_name STRING COMMENT sku名称 (冗余),create_time STRING COMMENT 创建时间,operate_time STRING COMMENT 修改时间,is_ordered STRING COMMENT 是否已经下单,order_time STRING COMMENT 下单时间,source_type STRING COMMENT 来源类型,source_id STRING COMMENT 来源编号
) COMMENT 加购表
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY \t
LOCATION /warehouse/dw_ods.db/ods_cart_info_full/;DROP TABLE IF EXISTS ods_coupon_info_full;
CREATE EXTERNAL TABLE ods_coupon_info_full(id STRING COMMENT 购物券编号,coupon_name STRING COMMENT 购物券名称,coupon_type STRING COMMENT 购物券类型 1 现金券 2 折扣券 3 满减券 4 满件打折券,condition_amount DECIMAL(16,2) COMMENT 满额数,condition_num BIGINT COMMENT 满件数,activity_id STRING COMMENT 活动编号,benefit_amount DECIMAL(16,2) COMMENT 减金额,benefit_discount DECIMAL(16,2) COMMENT 折扣,create_time STRING COMMENT 创建时间,range_type STRING COMMENT 范围类型 1、商品 2、品类 3、品牌,limit_num BIGINT COMMENT 最多领用次数,taken_count BIGINT COMMENT 已领用次数,start_time STRING COMMENT 开始领取时间,end_time STRING COMMENT 结束领取时间,operate_time STRING COMMENT 修改时间,expire_time STRING COMMENT 过期时间
) COMMENT 优惠券表
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY \t
LOCATION /warehouse/dw_ods.db/ods_coupon_info_full/;DROP TABLE IF EXISTS ods_activity_info_full;
CREATE EXTERNAL TABLE ods_activity_info_full(id STRING COMMENT 编号,activity_name STRING COMMENT 活动名称,activity_type STRING COMMENT 活动类型,start_time STRING COMMENT 开始时间,end_time STRING COMMENT 结束时间,create_time STRING COMMENT 创建时间
) COMMENT 活动信息表
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY \t
LOCATION /warehouse/dw_ods.db/ods_activity_info_full/;DROP TABLE IF EXISTS ods_activity_rule_full;
CREATE EXTERNAL TABLE ods_activity_rule_full(id STRING COMMENT 编号,activity_id STRING COMMENT 活动ID,activity_type STRING COMMENT 活动类型,condition_amount DECIMAL(16,2) COMMENT 满减金额,condition_num BIGINT COMMENT 满减件数,benefit_amount DECIMAL(16,2) COMMENT 优惠金额,benefit_discount DECIMAL(16,2) COMMENT 优惠折扣,benefit_level STRING COMMENT 优惠级别
) COMMENT 活动规则表
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY \t
LOCATION /warehouse/dw_ods.db/ods_activity_rule_full/;DROP TABLE IF EXISTS ods_base_dic_full;
CREATE EXTERNAL TABLE ods_base_dic_full(dic_code STRING COMMENT 编号,dic_name STRING COMMENT 编码名称,parent_code STRING COMMENT 父编码,create_time STRING COMMENT 创建日期,operate_time STRING COMMENT 操作日期
) COMMENT 编码字典表
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY \t
LOCATION /warehouse/dw_ods.db/ods_base_dic_full/;DROP TABLE IF EXISTS ods_sku_attr_value_full;
CREATE EXTERNAL TABLE ods_sku_attr_value_full(id STRING COMMENT 编号,attr_id STRING COMMENT 平台属性ID,value_id STRING COMMENT 平台属性值ID,sku_id STRING COMMENT 商品ID,attr_name STRING COMMENT 平台属性名称,value_name STRING COMMENT 平台属性值名称
) COMMENT sku平台属性表
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY \t
LOCATION /warehouse/dw_ods.db/ods_sku_attr_value_full/;DROP TABLE IF EXISTS ods_sku_sale_attr_value_full;
CREATE EXTERNAL TABLE ods_sku_sale_attr_value_full(id STRING COMMENT 编号,sku_id STRING COMMENT sku_id,spu_id STRING COMMENT spu_id,sale_attr_value_id STRING COMMENT 销售属性值id,sale_attr_id STRING COMMENT 销售属性id,sale_attr_name STRING COMMENT 销售属性名称,sale_attr_value_name STRING COMMENT 销售属性值名称
) COMMENT sku销售属性名称
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY \t
LOCATION /warehouse/dw_ods.db/ods_sku_sale_attr_value_full/;DROP TABLE IF EXISTS ods_base_region;
CREATE EXTERNAL TABLE ods_base_region (id STRING COMMENT 编号,region_name STRING COMMENT 地区名称
) COMMENT 地区表
ROW FORMAT DELIMITED FIELDS TERMINATED BY \t
LOCATION /warehouse/dw_ods.db/ods_base_region/;2.1.2 业务增量表建表语句
DROP TABLE IF EXISTS ods_order_info_inc;
CREATE EXTERNAL TABLE ods_order_info_inc (id STRING COMMENT 订单号,final_amount DECIMAL(16,2) COMMENT 订单最终金额,order_status STRING COMMENT 订单状态,user_id STRING COMMENT 用户id,payment_way STRING COMMENT 支付方式,delivery_address STRING COMMENT 送货地址,out_trade_no STRING COMMENT 支付流水号,create_time STRING COMMENT 创建时间,operate_time STRING COMMENT 操作时间,expire_time STRING COMMENT 过期时间,tracking_no STRING COMMENT 物流单编号,province_id STRING COMMENT 省份ID,activity_reduce_amount DECIMAL(16,2) COMMENT 活动减免金额,coupon_reduce_amount DECIMAL(16,2) COMMENT 优惠券减免金额,original_amount DECIMAL(16,2) COMMENT 订单原价金额,feight_fee DECIMAL(16,2) COMMENT 运费,feight_fee_reduce DECIMAL(16,2) COMMENT 运费减免
) COMMENT 订单表
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY \t
LOCATION /warehouse/dw_ods.db/ods_order_info_inc/;DROP TABLE IF EXISTS ods_coupon_use_inc;
CREATE EXTERNAL TABLE ods_coupon_use_inc(id STRING COMMENT 编号,coupon_id STRING COMMENT 优惠券ID,user_id STRING COMMENT skuid,order_id STRING COMMENT spuid,coupon_status STRING COMMENT 优惠券状态,get_time STRING COMMENT 领取时间,using_time STRING COMMENT 使用时间(下单),used_time STRING COMMENT 使用时间(支付),expire_time STRING COMMENT 过期时间
) COMMENT 优惠券领用表
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY \t
LOCATION /warehouse/dw_ods.db/ods_coupon_use_inc/;DROP TABLE IF EXISTS ods_order_status_log_inc;
CREATE EXTERNAL TABLE ods_order_status_log_inc (id STRING COMMENT 编号,order_id STRING COMMENT 订单ID,order_status STRING COMMENT 订单状态,operate_time STRING COMMENT 修改时间
) COMMENT 订单状态表
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY \t
LOCATION /warehouse/dw_ods.db/ods_order_status_log_inc/;DROP TABLE IF EXISTS ods_user_info_inc;
CREATE EXTERNAL TABLE ods_user_info_inc(id STRING COMMENT 用户id,login_name STRING COMMENT 用户名称,nick_name STRING COMMENT 用户昵称,name STRING COMMENT 用户姓名,phone_num STRING COMMENT 手机号码,email STRING COMMENT 邮箱,user_level STRING COMMENT 用户等级,birthday STRING COMMENT 生日,gender STRING COMMENT 性别,create_time STRING COMMENT 创建时间,operate_time STRING COMMENT 操作时间
) COMMENT 用户表
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY \t
LOCATION /warehouse/dw_ods.db/ods_user_info_inc/;DROP TABLE IF EXISTS ods_order_detail_inc;
CREATE EXTERNAL TABLE ods_order_detail_inc(id STRING COMMENT 编号,order_id STRING COMMENT 订单号,sku_id STRING COMMENT 商品id,sku_name STRING COMMENT 商品名称,order_price DECIMAL(16,2) COMMENT 商品价格,sku_num BIGINT COMMENT 商品数量,create_time STRING COMMENT 创建时间,source_type STRING COMMENT 来源类型,source_id STRING COMMENT 来源编号,split_final_amount DECIMAL(16,2) COMMENT 分摊最终金额,split_activity_amount DECIMAL(16,2) COMMENT 分摊活动优惠,split_coupon_amount DECIMAL(16,2) COMMENT 分摊优惠券优惠
) COMMENT 订单详情表
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY \t
LOCATION /warehouse/dw_ods.db/ods_order_detail_inc/;DROP TABLE IF EXISTS ods_payment_info_inc;
CREATE EXTERNAL TABLE ods_payment_info_inc(id STRING COMMENT 编号,out_trade_no STRING COMMENT 对外业务编号,order_id STRING COMMENT 订单编号,user_id STRING COMMENT 用户编号,payment_type STRING COMMENT 支付类型,trade_no STRING COMMENT 交易编号,payment_amount DECIMAL(16,2) COMMENT 支付金额,subject STRING COMMENT 交易内容,payment_status STRING COMMENT 支付状态,create_time STRING COMMENT 创建时间,callback_time STRING COMMENT 回调时间
) COMMENT 支付流水表
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY \t
LOCATION /warehouse/dw_ods.db/ods_payment_info_inc/;DROP TABLE IF EXISTS ods_comment_info_inc;
CREATE EXTERNAL TABLE ods_comment_info_inc(id STRING COMMENT 编号,user_id STRING COMMENT 用户ID,sku_id STRING COMMENT 商品sku,spu_id STRING COMMENT 商品spu,order_id STRING COMMENT 订单ID,appraise STRING COMMENT 评价,create_time STRING COMMENT 评价时间
) COMMENT 商品评论表
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY \t
LOCATION /warehouse/dw_ods.db/ods_comment_info_inc/;DROP TABLE IF EXISTS ods_order_refund_info_inc;
CREATE EXTERNAL TABLE ods_order_refund_info_inc(id STRING COMMENT 编号,user_id STRING COMMENT 用户ID,order_id STRING COMMENT 订单ID,sku_id STRING COMMENT 商品ID,refund_type STRING COMMENT 退单类型,refund_num BIGINT COMMENT 退单件数,refund_amount DECIMAL(16,2) COMMENT 退单金额,refund_reason_type STRING COMMENT 退单原因类型,refund_status STRING COMMENT 退单状态,--退单状态应包含买家申请、卖家审核、卖家收货、退款完成等状态。此处未涉及到故该表按增量处理create_time STRING COMMENT 退单时间
) COMMENT 退单表
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY \t
LOCATION /warehouse/dw_ods.db/ods_order_refund_info_inc/;DROP TABLE IF EXISTS ods_order_detail_activity_inc;
CREATE EXTERNAL TABLE ods_order_detail_activity_inc(id STRING COMMENT 编号,order_id STRING COMMENT 订单号,order_detail_id STRING COMMENT 订单明细id,activity_id STRING COMMENT 活动id,activity_rule_id STRING COMMENT 活动规则id,sku_id BIGINT COMMENT 商品id,create_time STRING COMMENT 创建时间
) COMMENT 订单详情活动关联表
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY \t
LOCATION /warehouse/dw_ods.db/ods_order_detail_activity_inc/;DROP TABLE IF EXISTS ods_order_detail_coupon_inc;
CREATE EXTERNAL TABLE ods_order_detail_coupon_inc(id STRING COMMENT 编号,order_id STRING COMMENT 订单号,order_detail_id STRING COMMENT 订单明细id,coupon_id STRING COMMENT 优惠券id,coupon_use_id STRING COMMENT 优惠券领用记录id,sku_id STRING COMMENT 商品id,create_time STRING COMMENT 创建时间
) COMMENT 订单详情活动关联表
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY \t
LOCATION /warehouse/dw_ods.db/ods_order_detail_coupon_inc/;DROP TABLE IF EXISTS ods_refund_payment_inc;
CREATE EXTERNAL TABLE ods_refund_payment_inc(id STRING COMMENT 编号,out_trade_no STRING COMMENT 对外业务编号,order_id STRING COMMENT 订单编号,sku_id STRING COMMENT SKU编号,payment_type STRING COMMENT 支付类型,trade_no STRING COMMENT 交易编号,refund_amount DECIMAL(16,2) COMMENT 支付金额,subject STRING COMMENT 交易内容,refund_status STRING COMMENT 支付状态,create_time STRING COMMENT 创建时间,callback_time STRING COMMENT 回调时间
) COMMENT 支付流水表
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY \t
LOCATION /warehouse/dw_ods.db/ods_refund_payment_inc/;2.1.3 流量增量表建表语句
DROP TABLE IF EXISTS ods_log_inc;
CREATE EXTERNAL TABLE ods_log_inc (line string)
PARTITIONED BY (dt string)
LOCATION /warehouse/dw_ods.db/ods_log_inc/;2.2 数据装载 省份表和城市表只需首次导入一次其他业务表需要每天都装载。 2.2.1 初始化装载省份和地区表
[hadoophadoop102 ~]$ hive -e load data inpath /warehouse/db/gmall/base_province_full/2023-12-02 OVERWRITE into table dw_ods.ods_base_province;
[hadoophadoop102 ~]$ hive -e load data inpath /warehouse/db/gmall/base_region_full/2023-12-02 OVERWRITE into table dw_ods.ods_base_region;2.2.2 每日装载业务数据 注意1要在Sqoop数据同步成功之后运行。 注意2crontab无法直接处理任务依赖关系 TODO如何判定Sqoop同步成功 如何在其成功后运行 1 开发脚本
[hadoophadoop102 ~]$ cd /home/hadoop/bin/
[hadoophadoop102 bin]$ vim hdfs_to_ods_db.sh脚本内容
#!/bin/bashAPPdw_ods# 如果是输入的日期按照取输入日期如果没输入日期取当前时间的前一天
if [ -n $2 ] ;thendo_date$2
else do_datedate -d -1 day %F
fisku_info
load data inpath /warehouse/db/gmall/sku_info_full/$do_date OVERWRITE into table ${APP}.ods_sku_info_full partition(dt$do_date);base_category1
load data inpath /warehouse/db/gmall/base_category1_full/$do_date OVERWRITE into table ${APP}.ods_base_category1_full partition(dt$do_date);base_category2
load data inpath /warehouse/db/gmall/base_category2_full/$do_date OVERWRITE into table ${APP}.ods_base_category2_full partition(dt$do_date);base_category3
load data inpath /warehouse/db/gmall/base_category3_full/$do_date OVERWRITE into table ${APP}.ods_base_category3_full partition(dt$do_date);base_trademark
load data inpath /warehouse/db/gmall/base_trademark_full/$do_date OVERWRITE into table ${APP}.ods_base_trademark_full partition(dt$do_date);spu_info
load data inpath /warehouse/db/gmall/spu_info_full/$do_date OVERWRITE into table ${APP}.ods_spu_info_full partition(dt$do_date);favor_info
load data inpath /warehouse/db/gmall/favor_info_full/$do_date OVERWRITE into table ${APP}.ods_favor_info_full partition(dt$do_date); cart_info
load data inpath /warehouse/db/gmall/cart_info_full/$do_date OVERWRITE into table ${APP}.ods_cart_info_full partition(dt$do_date); coupon_info
load data inpath /warehouse/db/gmall/coupon_info_full/$do_date OVERWRITE into table ${APP}.ods_coupon_info_full partition(dt$do_date); activity_info
load data inpath /warehouse/db/gmall/activity_info_full/$do_date OVERWRITE into table ${APP}.activity_info_full partition(dt$do_date); activity_rule
load data inpath /warehouse/db/gmall/activity_rule_full/$do_date OVERWRITE into table ${APP}.ods_activity_rule_full partition(dt$do_date); base_dic
load data inpath /warehouse/db/gmall/base_dic_full/$do_date OVERWRITE into table ${APP}.ods_base_dic_full partition(dt$do_date); sku_attr_value
load data inpath /warehouse/db/gmall/sku_attr_value_full/$do_date OVERWRITE into table ${APP}.ods_sku_attr_value_full partition(dt$do_date); sku_sale_attr_value
load data inpath /warehouse/db/gmall/sku_sale_attr_value_full/$do_date OVERWRITE into table ${APP}.ods_sku_sale_attr_value_full partition(dt$do_date); order_info
load data inpath /warehouse/db/gmall/order_info_inc/$do_date OVERWRITE into table ${APP}.ods_order_info_inc partition(dt$do_date); coupon_use
load data inpath /warehouse/db/gmall/coupon_use_inc/$do_date OVERWRITE into table ${APP}.ods_coupon_use_inc partition(dt$do_date); order_status_log
load data inpath /warehouse/db/gmall/order_status_log_inc/$do_date OVERWRITE into table ${APP}.ods_order_status_log_inc partition(dt$do_date); user_info
load data inpath /warehouse/db/gmall/user_info_inc/$do_date OVERWRITE into table ${APP}.ods_user_info_inc partition(dt$do_date); order_detail
load data inpath /warehouse/db/gmall/order_detail_inc/$do_date OVERWRITE into table ${APP}.ods_order_detail_inc partition(dt$do_date); payment_info
load data inpath /warehouse/db/gmall/payment_info_inc/$do_date OVERWRITE into table ${APP}.ods_payment_info_inc partition(dt$do_date); comment_info
load data inpath /warehouse/db/gmall/comment_info_inc/$do_date OVERWRITE into table ${APP}.ods_comment_info_inc partition(dt$do_date); order_refund_info
load data inpath /warehouse/db/gmall/order_refund_info_inc/$do_date OVERWRITE into table ${APP}.ods_order_refund_info_inc partition(dt$do_date); order_detail_activity
load data inpath /warehouse/db/gmall/order_detail_activity_inc/$do_date OVERWRITE into table ${APP}.ods_order_detail_activity_inc partition(dt$do_date); order_detail_coupon
load data inpath /warehouse/db/gmall/order_detail_coupon_inc/$do_date OVERWRITE into table ${APP}.ods_order_detail_coupon_inc partition(dt$do_date);refund_payment
load data inpath /warehouse/db/gmall/refund_payment_inc/$do_date OVERWRITE into table ${APP}.ods_refund_payment_inc partition(dt$do_date);case $1 insku_info){hive -e $sku_info};;base_category1){hive -e $base_category1};;base_category2){hive -e $base_category2};;base_category3){hive -e $base_category3};;base_trademark){hive -e $base_trademark};;spu_info){hive -e $spu_info};;favor_info){hive -e $favor_info};;cart_info){hive -e $cart_info};;coupon_info){hive -e $coupon_info};;activity_info){hive -e $activity_info};;activity_rule){hive -e $activity_rule};;base_dic){hive -e $base_dic};;sku_attr_value){hive -e $sku_attr_value};;sku_sale_attr_value){hive -e $sku_sale_attr_value};;order_info){hive -e $order_info};;coupon_use){hive -e $coupon_use};;order_status_log){hive -e $order_status_log};;user_info){hive -e $user_info};;order_detail){hive -e $order_detail};;payment_info){hive -e $payment_info};;comment_info){hive -e $comment_info};;order_refund_info){hive -e $order_refund_info};;order_detail_activity){hive -e $order_detail_activity};;order_detail_coupon){hive -e $order_detail_coupon};;refund_payment){hive -e $refund_payment};;all){hive -e $sku_info$base_category1$base_category2$base_category3$base_trademark$spu_info$favor_info$cart_info$coupon_info$activity_info$activity_rule$base_dic$sku_attr_value$sku_sale_attr_value$order_info$coupon_use$order_status_log$user_info$order_detail$payment_info$comment_info$order_refund_info$order_detail_activity$order_detail_coupon$refund_payment};;
esac2 授予脚本执行权限
[hadoophadoop102 bin]$ chmod x hdfs_to_ods_db.sh3 定时调度 每天凌晨3点装载业务数据 [hadoophadoop102 ~]$ crontab -e新增内容
# 每天凌晨3点装载业务数据
0 3 * * * hdfs_to_ods_db.sh all2.2.3 每日装载日志数据
1 开发脚本
[hadoophadoop102 ~]$ cd /home/hadoop/bin/
[hadoophadoop102 bin]$ vim hdfs_to_ods_log.sh脚本内容
#!/bin/bash# 如果是输入的日期按照取输入日期如果没输入日期取当前时间的前一天
if [ -n $1 ] ;thendo_date$1
else do_datedate -d -1 day %F
fi echo 日志日期为 $do_date
sql
load data inpath /warehouse/applog/gmall/tracking_log/$do_date into table dw_ods.ods_log_inc partition(dt$do_date);
hive -e $sql2 授予脚本执行权限
[hadoophadoop102 bin]$ chmod x hdfs_to_ods_log.sh3 定时调度 凌晨3点执行要等昨天的日志数据同步完成。 关键是保证白天时数仓能查到昨天分区的数据。 [hadoophadoop102 ~]$ crontab -e新增内容
# 每天凌晨3点装载用户日志数据增量表
0 3 * * * hdfs_to_ods_log.sh
