drop table if exists ods_order_info;
create external table ods_order_info (
    `id` string COMMENT \'订单编号\',
    `total_amount` decimal(10,2) COMMENT \'订单金额\',
    `order_status` string COMMENT \'订单状态\',
    `user_id` string COMMENT \'用户id\',
    `payment_way` string COMMENT \'支付方式\',
    `out_trade_no` string COMMENT \'支付流水号\',
    `create_time` string COMMENT \'创建时间\',
    `operate_time` string COMMENT \'操作时间\'
) COMMENT \'订单表\'
PARTITIONED BY (`dt` string)
row format delimited fields terminated by \'\t\'
location \'/warehouse/gmall/ods/ods_order_info/\'
;

drop table if exists ods_order_detail;
create external table ods_order_detail( 
    `id` string COMMENT \'订单编号\',
    `order_id` string  COMMENT \'订单号\', 
    `user_id` string COMMENT \'用户id\',
    `sku_id` string COMMENT \'商品id\',
    `sku_name` string COMMENT \'商品名称\',
    `order_price` string COMMENT \'商品价格\',
    `sku_num` string COMMENT \'商品数量\',
    `create_time` string COMMENT \'创建时间\'
) COMMENT \'订单明细表\'
PARTITIONED BY (`dt` string)
row format delimited fields terminated by \'\t\' 
location \'/warehouse/gmall/ods/ods_order_detail/\'
;

drop table if exists ods_sku_info;
create external table ods_sku_info( 
    `id` string COMMENT \'skuId\',
    `spu_id` string   COMMENT \'spuid\', 
    `price` decimal(10,2) COMMENT \'价格\',
    `sku_name` string COMMENT \'商品名称\',
    `sku_desc` string COMMENT \'商品描述\',
    `weight` string COMMENT \'重量\',
    `tm_id` string COMMENT \'品牌id\',
    `category3_id` string COMMENT \'品类id\',
    `create_time` string COMMENT \'创建时间\'
) COMMENT \'商品表\'
PARTITIONED BY (`dt` string)
row format delimited fields terminated by \'\t\'
location \'/warehouse/gmall/ods/ods_sku_info/\'
;

drop table if exists ods_user_info;
create external table ods_user_info( 
    `id` string COMMENT \'用户id\',
    `name`  string COMMENT \'姓名\',
    `birthday` string COMMENT \'生日\',
    `gender` string COMMENT \'性别\',
    `email` string COMMENT \'邮箱\',
    `user_level` string COMMENT \'用户等级\',
    `create_time` string COMMENT \'创建时间\'
) COMMENT \'用户信息\'
PARTITIONED BY (`dt` string)
row format delimited fields terminated by \'\t\'
location \'/warehouse/gmall/ods/ods_user_info/\'
;

drop table if exists ods_base_category1;
create external table ods_base_category1( 
    `id` string COMMENT \'id\',
    `name`  string COMMENT \'名称\'
) COMMENT \'商品一级分类\'
PARTITIONED BY (`dt` string)
row format delimited fields terminated by \'\t\'
location \'/warehouse/gmall/ods/ods_base_category1/\'
;

drop table if exists ods_base_category2;
create external table ods_base_category2( 
    `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/gmall/ods/ods_base_category2/\'
;

drop table if exists ods_base_category3;
create external table ods_base_category3(
    `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/gmall/ods/ods_base_category3/\'
;

drop table if exists ods_payment_info;
create external table ods_payment_info(
    `id`   bigint COMMENT \'编号\',
    `out_trade_no`    string COMMENT \'对外业务编号\',
    `order_id`        string COMMENT \'订单编号\',
    `user_id`         string COMMENT \'用户编号\',
    `alipay_trade_no` string COMMENT \'支付宝交易流水编号\',
    `total_amount`    decimal(16,2) COMMENT \'支付金额\',
    `subject`         string COMMENT \'交易内容\',
    `payment_type`    string COMMENT \'支付类型\',
    `payment_time`    string COMMENT \'支付时间\'
   )  COMMENT \'支付流水表\'
PARTITIONED BY (`dt` string)
row format delimited fields terminated by \'\t\'
location \'/warehouse/gmall/ods/ods_payment_info/\'
;

#!/bin/bash

   APP=gmall
   hive=/opt/module/hive/bin/hive

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$1" ] ;then
    do_date=$1
else 
    do_date=`date -d "-1 day" +%F`
fi

sql=" 
load data inpath \'/origin_data/$APP/db/order_info/$do_date\' OVERWRITE into table "$APP".ods_order_info partition(dt=\'$do_date\');

load data inpath \'/origin_data/$APP/db/order_detail/$do_date\' OVERWRITE into table "$APP".ods_order_detail partition(dt=\'$do_date\');

load data inpath \'/origin_data/$APP/db/sku_info/$do_date\' OVERWRITE into table "$APP".ods_sku_info partition(dt=\'$do_date\');

load data inpath \'/origin_data/$APP/db/user_info/$do_date\' OVERWRITE into table "$APP".ods_user_info partition(dt=\'$do_date\');

load data inpath \'/origin_data/$APP/db/payment_info/$do_date\' OVERWRITE into table "$APP".ods_payment_info partition(dt=\'$do_date\');

load data inpath \'/origin_data/$APP/db/base_category1/$do_date\' OVERWRITE into table "$APP".ods_base_category1 partition(dt=\'$do_date\');

load data inpath \'/origin_data/$APP/db/base_category2/$do_date\' OVERWRITE into table "$APP".ods_base_category2 partition(dt=\'$do_date\');

load data inpath \'/origin_data/$APP/db/base_category3/$do_date\' OVERWRITE into table "$APP".ods_base_category3 partition(dt=\'$do_date\'); 
"
$hive -e "$sql"