數(shù)倉(cāng)實(shí)戰(zhàn)03：數(shù)倉(cāng)搭建-ODS 層

1.創(chuàng)建數(shù)據(jù)庫(kù)

1）啟動(dòng)
hive
[atguigu@hadoop102 hive]$ nohup bin/hive --service metastore &
[atguigu@hadoop102 hive]$ nohup bin/hive --service hiveserver2 &
[atguigu@hadoop102 hive]$ bin/hive
2）顯示數(shù)據(jù)庫(kù)
hive (default)> show databases;
3）創(chuàng)建數(shù)據(jù)庫(kù)
hive (default)> create database gmall;
4）使用數(shù)據(jù)庫(kù)
hive (default)> use gmall;

2.ODS 層（用戶行為數(shù)據(jù)）

2.1 創(chuàng)建啟動(dòng)日志表 ods_start_log

1）創(chuàng)建輸入數(shù)據(jù)是 lzo 輸出是 text啰脚，支持 json 解析的分區(qū)表

hive (gmall)> drop table if exists ods_start_log;
 CREATE EXTERNAL TABLE ods_start_log (`line` string) 
PARTITIONED BY (`dt` string) 
STORED AS 
INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' 
OUTPUTFORMAT 
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' 
LOCATION '/warehouse/gmall/ods/ods_start_log';

說(shuō)明 Hive 的 LZO 壓縮：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+LZO
2）加載數(shù)據(jù)
hive (gmall)> load data inpath '/origin_data/gmall/log/topic_start/2020-03-10' into table gmall.ods_start_log partition(dt='2020-03-10');
3）查看是否加載成功
hive (gmall)> select * from ods_start_log where dt='2020-03-10' limit 2;
4）為 lzo 壓縮文件創(chuàng)建索引

[atguigu@hadoop102 hadoop-2.7.2]$ hadoop jar /opt/module/hadoop-
2.7.2/share/hadoop/common/hadoop-lzo-0.4.20. jar 
com.hadoop.compression.lzo.DistributedLzoIndexer 
/warehouse/gmall/ods/ods_start_log/dt=2020-03-10

2.2 創(chuàng)建事件日志表 ods_event_log

1）創(chuàng)建輸入數(shù)據(jù)是 lzo 輸出是 text枫攀，支持 json 解析的分區(qū)表

hive (gmall)> drop table if exists ods_event_log;
CREATE EXTERNAL TABLE ods_event_log(`line` string) 
PARTITIONED BY (`dt` string) 
STORED AS 
INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' 
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' 
LOCATION '/warehouse/gmall/ods/ods_event_log';

2）加載數(shù)據(jù)

hive (gmall)> load data inpath '/origin_data/gmall/log/topic_event/2020-03-10' into table gmall.ods_event_log partition(dt='2020-03-10');

注意：時(shí)間格式都配置成 YYYY-MM-DD 格式，這是 Hive 默認(rèn)支持的時(shí)間格式
3）查看是否加載成功
hive (gmall)> select * from ods_event_log where dt="2020-03-10" limit 2;
4）為 lzo 壓縮文件創(chuàng)建索引

[atguigu@hadoop102 hadoop-2.7.2]$ hadoop jar /opt/module/hadoop-
2.7.2/share/hadoop/common/hadoop-lzo-0.4.20. jar 
com.hadoop.compression.lzo.DistributedLzoIndexer 
/warehouse/gmall/ods/ods_event_log/dt=2020-03-10

2.3 Shell 中單引號(hào)和雙引號(hào)區(qū)別

1）在/home/atguigu/bin 創(chuàng)建一個(gè) test.sh 文件
[atguigu@hadoop102 bin]$ vim test.sh
在文件中添加如下內(nèi)容

#!/bin/bash 
do_date=$1 
echo '$do_date' 
echo "$do_date" 
echo "'$do_date'" 
echo '"$do_date"' 
echo `date`

2）查看執(zhí)行結(jié)果

[atguigu@hadoop102 bin]$ test.sh 
2020-03-10 
$do_date 
2020-03-10 
'2020-03-10' 
"$do_date" 
2020 年 05 月 02 日 星期四 21:02:08 CST

3）總結(jié)：
（1）單引號(hào)不取變量值
（2）雙引號(hào)取變量值
（3）反引號(hào)`棋嘲，執(zhí)行引號(hào)中命令
（4）雙引號(hào)內(nèi)部嵌套單引號(hào)鸭限，取出變量值
（5）單引號(hào)內(nèi)部嵌套雙引號(hào)，不取出變量值

2.4ODS 層加載數(shù)據(jù)腳本

1）在 hadoop102 的/home/atguigu/bin 目錄下創(chuàng)建腳本
[atguigu@hadoop102 bin]$ vim hdfs_to_ods_log.sh
在腳本中編寫(xiě)如下內(nèi)容

#!/bin/bash 
# 定義變量方便修改 
APP=gmall 
hive=/opt/module/hive/bin/hive 
# 如果是輸入的日期按照取輸入日期；如果沒(méi)輸入日期取當(dāng)前時(shí)間的前一天 
if [ -n "$1" ] ;then 
     do_date=$1 
else
     do_date=`date -d "-1 day" +%F` 
fi
echo "===日志日期為 $do_date===" 
sql=" 
load data inpath '/origin_data/gmall/log/topic_start/$do_date' overwrite into table ${APP}.ods_start_log partition(dt='$do_date'); 
load data inpath '/origin_data/gmall/log/topic_event/$do_date' overwrite into table ${APP}.ods_event_log partition(dt='$do_date'); "
$hive -e "$sql" 
hadoop jar /opt/module/hadoop-2.7.2/share/hadoop/common/hadoop-lzo-0.4.20.jar 
com.hadoop.compression.lzo.DistributedLzoIndexer 
/warehouse/gmall/ods/ods_start_log/dt=$do_date
hadoop jar /opt/module/hadoop-2.7.2/share/hadoop/common/hadoop-lzo-0.4.20.jar 
com.hadoop.compression.lzo.DistributedLzoIndexer 
/warehouse/gmall/ods/ods_event_log/dt=$do_date

說(shuō)明 1：
[ -n 變量值 ] 判斷變量的值铜异，是否為空
-- 變量的值，非空秸架，返回 true -- 變量的值揍庄，為空，返回 false
說(shuō)明 2：
查看 date 命令的使用东抹，[atguigu@hadoop102 ~]$ date --help
2）增加腳本執(zhí)行權(quán)限
[atguigu@hadoop102 bin]$ chmod 777 hdfs_to_ods_log.sh
3）腳本使用
[atguigu@hadoop102 module]$ hdfs_to_ods_log.sh 2020-03-11
4）查看導(dǎo)入數(shù)據(jù)
hive (gmall)> select * from ods_start_log where dt='2020-03-11' limit 2;
select * from ods_event_log where dt='2020-03-11' limit 2;
5）腳本執(zhí)行時(shí)間
企業(yè)開(kāi)發(fā)中一般在每日凌晨 30 分~1 點(diǎn)

3.ODS 層（業(yè)務(wù)數(shù)據(jù)）

3.1 訂單表（增量及更新）

hive (gmall)> 
drop table if exists ods_order_info;
create external table ods_order_info ( `id` string COMMENT '訂單號(hào)',
 `final_total_amount` decimal(10,2) COMMENT '訂單金額', 
`order_status` string COMMENT '訂單狀態(tài)', 
`user_id` string COMMENT '用戶 id', 
`out_trade_no` string COMMENT '支付流水號(hào)', 
`create_time` string COMMENT '創(chuàng)建時(shí)間', 
`operate_time` string COMMENT '操作時(shí)間', 
`province_id` string COMMENT '省份 ID', 
`benefit_reduce_amount` decimal(10,2) COMMENT '優(yōu)惠金額', 
`original_total_amount` decimal(10,2) COMMENT '原價(jià)金額', 
`feight_fee` decimal(10,2) COMMENT '運(yùn)費(fèi)' ) 
COMMENT '訂單表' 
PARTITIONED BY (`dt` string) 
row format delimited fields terminated by '\t' 
STORED AS 
INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' 
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' 
location '/warehouse/gmall/ods/ods_order_info/';

3.2訂單詳情表（增量）

hive (gmall) > DROP TABLE
IF EXISTS ods_order_detail;

CREATE external TABLE ods_order_detail (
    `id` string COMMENT '訂單編號(hào)',
    `order_id` string COMMENT '訂單號(hào)',
    `user_id` string COMMENT '用戶 id',
    `sku_id` string COMMENT '商品 id',
    `sku_name` string COMMENT '商品名稱',
    `order_price` DECIMAL (10, 2) COMMENT '商品價(jià)格',
    `sku_num` BIGINT COMMENT '商品數(shù)量',
    `create_time` string COMMENT '創(chuàng)建時(shí)間'
) COMMENT '訂單詳情表' PARTITIONED BY (`dt` string) ROW format delimited FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' location '/warehouse/gmall/ods/ods_order_detail/';

3.3SKU 商品表（全量）

hive (gmall) > 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 '價(jià)格',
    `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 '創(chuàng)建時(shí)間'
) COMMENT 'SKU 商品表' PARTITIONED BY (`dt` string) 
ROW format delimited FIELDS TERMINATED BY '\t' 
STORED AS 
INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' 
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' 
location '/warehouse/gmall/ods/ods_sku_info/';

3.4用戶表（增量及更新）

hive (gmall) > 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 '用戶等級(jí)',
    `create_time` string COMMENT '創(chuàng)建時(shí)間',
    `operate_time` string COMMENT '操作時(shí)間'
) COMMENT '用戶表' PARTITIONED BY (`dt` string) 
ROW format delimited FIELDS TERMINATED BY '\t' 
STORED AS 
INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' 
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' 
location '/warehouse/gmall/ods/ods_user_info/';

3.5商品一級(jí)分類表（全量）

hive (gmall) > DROP TABLE
IF EXISTS ods_base_category1;

CREATE external TABLE ods_base_category1 (
    `id` string COMMENT 'id',
    `name` string COMMENT '名稱'
) COMMENT '商品一級(jí)分類表' 
PARTITIONED BY (`dt` string) 
ROW format delimited FIELDS TERMINATED BY '\t' 
STORED AS 
INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' 
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' 
location '/warehouse/gmall/ods/ods_base_category1/';

3.6商品二級(jí)分類表（全量）

hive (gmall) > DROP TABLE
IF EXISTS ods_base_category2;

CREATE external TABLE ods_base_category2 (
    `id` string COMMENT ' id',
    `name` string COMMENT '名稱',
    category1_id string COMMENT '一級(jí)品類 id'
) COMMENT '商品二級(jí)分類表' 
PARTITIONED BY (`dt` string) 
ROW format delimited FIELDS TERMINATED BY '\t' 
STORED AS 
INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' 
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' 
location '/warehouse/gmall/ods/ods_base_category2/';

3.7商品三級(jí)分類表（全量）

hive (gmall) > DROP TABLE
IF EXISTS ods_base_category3;

CREATE external TABLE ods_base_category3 (
    `id` string COMMENT ' id',
    `name` string COMMENT '名稱',
    category2_id string COMMENT '二級(jí)品類 id'
) COMMENT '商品三級(jí)分類表' 
PARTITIONED BY (`dt` string) 
ROW format delimited FIELDS TERMINATED BY '\t' 
STORED AS 
INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' 
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' 
location '/warehouse/gmall/ods/ods_base_category3/';

3.8支付流水表（增量）

hive (gmall) > DROP TABLE
IF EXISTS ods_payment_info;

CREATE external TABLE ods_payment_info (
    `id` BIGINT COMMENT '編號(hào)',
    `out_trade_no` string COMMENT '對(duì)外業(yè)務(wù)編號(hào)',
    `order_id` string COMMENT '訂單編號(hào)',
    `user_id` string COMMENT '用戶編號(hào)',
    `alipay_trade_no` string COMMENT '支付寶交易流水編號(hào)',
    `total_amount` DECIMAL (16, 2) COMMENT '支付金額',
    `subject` string COMMENT '交易內(nèi)容',
    `payment_type` string COMMENT '支付類型',
    `payment_time` string COMMENT '支付時(shí)間'
) COMMENT '支付流水表' 
PARTITIONED BY (`dt` string) 
ROW format delimited FIELDS TERMINATED BY '\t' 
STORED AS 
INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' 
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' 
location '/warehouse/gmall/ods/ods_payment_info/';

3.9 省份表（特殊）

hive (gmall) > DROP TABLE
IF EXISTS ods_base_province;

CREATE external TABLE ods_base_province (
    `id` BIGINT COMMENT '編號(hào)',
    `name` string COMMENT '省份名稱',
    `region_id` string COMMENT '地區(qū) ID',
    `area_code` string COMMENT '地區(qū)編碼',
    `iso_code` string COMMENT 'iso 編碼'
) COMMENT '省份表' 
ROW format delimited FIELDS TERMINATED BY '\t' 
STORED AS 
INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' 
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' 
location '/warehouse/gmall/ods/ods_base_province/';

3.10 地區(qū)表（特殊）

hive (gmall) > DROP TABLE
IF EXISTS ods_base_region;

CREATE external TABLE ods_base_region (
    `id` BIGINT COMMENT '編號(hào)',
    `region_name` string COMMENT '地區(qū)名稱'
) COMMENT '地區(qū)表' 
ROW format delimited FIELDS TERMINATED BY '\t' 
STORED AS 
INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' 
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' 
location '/warehouse/gmall/ods/ods_base_region/';

3.11 品牌表（全量）

hive (gmall) > DROP TABLE
IF EXISTS ods_base_trademark;

CREATE external TABLE ods_base_trademark (
    `tm_id` BIGINT COMMENT '編號(hào)',
    `tm_name` string COMMENT '品牌名稱'
) COMMENT '品牌表' 
PARTITIONED BY (`dt` string) 
ROW format delimited FIELDS TERMINATED BY '\t' 
STORED AS 
INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' 
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' 
location '/warehouse/gmall/ods/ods_base_trademark/';

3.12 訂單狀態(tài)表（增量）

hive (gmall) > DROP TABLE
IF EXISTS ods_order_status_log;

CREATE external TABLE ods_order_status_log (
    `id` BIGINT COMMENT '編號(hào)',
    `order_id` string COMMENT '訂單 ID',
    `order_status` string COMMENT '訂單狀態(tài)',
    `operate_time` string COMMENT '修改時(shí)間'
) COMMENT '訂單狀態(tài)表' 
PARTITIONED BY (`dt` string) 
ROW format delimited FIELDS TERMINATED BY '\t' 
STORED AS 
INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' 
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' 
location '/warehouse/gmall/ods/ods_order_status_log/';

3.13 SPU 商品表（全量）

hive (gmall) > DROP TABLE
IF EXISTS ods_spu_info;

CREATE external TABLE ods_spu_info (
    `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' 
STORED AS 
INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' 
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' 
location '/warehouse/gmall/ods/ods_spu_info/';

3.14 商品評(píng)論表（增量）

hive (gmall) > DROP TABLE
IF EXISTS ods_comment_info;

CREATE external TABLE ods_comment_info (
    `id` string COMMENT '編號(hào)',
    `user_id` string COMMENT '用戶 ID',
    `sku_id` string COMMENT '商品 sku',
    `spu_id` string COMMENT '商品 spu',
    `order_id` string COMMENT '訂單 ID',
    `appraise` string COMMENT '評(píng)價(jià)',
    `create_time` string COMMENT '評(píng)價(jià)時(shí)間'
) COMMENT '商品評(píng)論表' 
PARTITIONED BY (`dt` string) 
ROW format delimited FIELDS TERMINATED BY '\t' 
STORED AS 
INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' 
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' 
location '/warehouse/gmall/ods/ods_comment_info/';

3.15 退單表（增量）

hive (gmall) > DROP TABLE
IF EXISTS ods_order_refund_info;

CREATE external TABLE ods_order_refund_info (
    `id` string COMMENT '編號(hào)',
    `user_id` string COMMENT '用戶 ID',
    `order_id` string COMMENT '訂單 ID',
    `sku_id` string COMMENT '商品 ID',
    `refund_type` string COMMENT '退款類型',
    `refund_num` BIGINT COMMENT '退款件數(shù)',
    `refund_amount` DECIMAL (16, 2) COMMENT '退款金額',
    `refund_reason_type` string COMMENT '退款原因類型',
    `create_time` string COMMENT '退款時(shí)間'
) COMMENT '退單表' 
PARTITIONED BY (`dt` string) 
ROW format delimited FIELDS TERMINATED BY '\t' 
STORED AS 
INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' 
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' 
location '/warehouse/gmall/ods/ods_order_refund_info/';

3.16 加購(gòu)表（全量）

hive (gmall) > DROP TABLE
IF EXISTS ods_cart_info;

CREATE external TABLE ods_cart_info (
    `id` string COMMENT '編號(hào)',
    `user_id` string COMMENT '用戶 id',
    `sku_id` string COMMENT 'skuid',
    `cart_price` string COMMENT '放入購(gòu)物車(chē)時(shí)價(jià)格',
    `sku_num` string COMMENT '數(shù)量',
    `sku_name` string COMMENT 'sku 名稱 (冗余)',
    `create_time` string COMMENT '創(chuàng)建時(shí)間',
    `operate_time` string COMMENT '修改時(shí)間',
    `is_ordered` string COMMENT '是否已經(jīng)下單',
    `order_time` string COMMENT '下單時(shí)間'
) COMMENT '加購(gòu)表' 
PARTITIONED BY (`dt` string) 
ROW format delimited FIELDS TERMINATED BY '\t' 
STORED AS 
INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' 
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' 
location '/warehouse/gmall/ods/ods_cart_info/';

3.17 商品收藏表（全量）

hive (gmall) > DROP TABLE
IF EXISTS ods_favor_info;

CREATE external TABLE ods_favor_info (
    `id` string COMMENT '編號(hào)',
    `user_id` string COMMENT '用戶 id',
    `sku_id` string COMMENT 'skuid',
    `spu_id` string COMMENT 'spuid',
    `is_cancel` string COMMENT '是否取消',
    `create_time` string COMMENT '收藏時(shí)間',
    `cancel_time` string COMMENT '取消時(shí)間'
) COMMENT '商品收藏表' 
PARTITIONED BY (`dt` string) 
ROW format delimited FIELDS TERMINATED BY '\t' 
STORED AS 
INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' 
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' 
location '/warehouse/gmall/ods/ods_favor_info/';

3.18 優(yōu)惠券領(lǐng)用表（新增及變化）

hive (gmall) > DROP TABLE
IF EXISTS ods_coupon_use;

CREATE external TABLE ods_coupon_use (
    `id` string COMMENT '編號(hào)',
    `coupon_id` string COMMENT '優(yōu)惠券 ID',
    `user_id` string COMMENT 'skuid',
    `order_id` string COMMENT 'spuid',
    `coupon_status` string COMMENT '優(yōu)惠券狀態(tài)',
    `get_time` string COMMENT '領(lǐng)取時(shí)間',
    `using_time` string COMMENT '使用時(shí)間(下單)',
    `used_time` string COMMENT '使用時(shí)間(支付)'
) COMMENT '優(yōu)惠券領(lǐng)用表' 
PARTITIONED BY (`dt` string) 
ROW format delimited FIELDS TERMINATED BY '\t' 
STORED AS 
INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' 
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' 
location '/warehouse/gmall/ods/ods_coupon_use/';

3.19 優(yōu)惠券表（全量）

hive (gmall) > DROP TABLE
IF EXISTS ods_coupon_info;

CREATE external TABLE ods_coupon_info (
    `id` string COMMENT '購(gòu)物券編號(hào)',
    `coupon_name` string COMMENT '購(gòu)物券名稱',
    `coupon_type` string COMMENT '購(gòu)物券類型 1 現(xiàn)金券 2 折扣券 3 滿減券 4 滿件打折券',
    `condition_amount` string COMMENT '滿額數(shù)',
    `condition_num` string COMMENT '滿件數(shù)',
    `activity_id` string COMMENT '活動(dòng)編號(hào)',
    `benefit_amount` string COMMENT '減金額',
    `benefit_discount` string COMMENT '折扣',
    `create_time` string COMMENT '創(chuàng)建時(shí)間',
    `range_type` string COMMENT '范圍類型 1蚂子、商品 2、品類 3缭黔、品牌',
    `spu_id` string COMMENT '商品 id',
    `tm_id` string COMMENT '品牌 id',
    `category3_id` string COMMENT '品類 id',
    `limit_num` string COMMENT '最多領(lǐng)用次數(shù)',
    `operate_time` string COMMENT '修改時(shí)間',
    `expire_time` string COMMENT '過(guò)期時(shí)間'
) COMMENT '優(yōu)惠券表' 
PARTITIONED BY (`dt` string) 
ROW format delimited FIELDS TERMINATED BY '\t' 
STORED AS 
INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' 
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' 
location '/warehouse/gmall/ods/ods_coupon_info/';

3.20 活動(dòng)表（全量）

hive (gmall) > DROP TABLE
IF EXISTS ods_activity_info;

CREATE external TABLE ods_activity_info (
    `id` string COMMENT '編號(hào)',
    `activity_name` string COMMENT '活動(dòng)名稱',
    `activity_type` string COMMENT '活動(dòng)類型',
    `start_time` string COMMENT '開(kāi)始時(shí)間',
    `end_time` string COMMENT '結(jié)束時(shí)間',
    `create_time` string COMMENT '創(chuàng)建時(shí)間'
) COMMENT '活動(dòng)表' 
PARTITIONED BY (`dt` string) 
ROW format delimited FIELDS TERMINATED BY '\t' 
STORED AS 
INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' 
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' 
location '/warehouse/gmall/ods/ods_activity_info/';

3.21 活動(dòng)訂單關(guān)聯(lián)表（增量）

hive (gmall) > DROP TABLE
IF EXISTS ods_activity_order;

CREATE external TABLE ods_activity_order (
    `id` string COMMENT '編號(hào)',
    `activity_id` string COMMENT '優(yōu)惠券 ID',
    `order_id` string COMMENT 'skuid',
    `create_time` string COMMENT '領(lǐng)取時(shí)間'
) COMMENT '活動(dòng)訂單關(guān)聯(lián)表' 
PARTITIONED BY (`dt` string) 
ROW format delimited FIELDS TERMINATED BY '\t' 
STORED AS 
INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' 
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' 
location '/warehouse/gmall/ods/ods_activity_order/';

3.22 優(yōu)惠規(guī)則表（全量）

hive (gmall) > DROP TABLE
IF EXISTS ods_activity_rule;

CREATE external TABLE ods_activity_rule (
    `id` string COMMENT '編號(hào)',
    `activity_id` string COMMENT '活動(dòng) ID',
    `condition_amount` string COMMENT '滿減金額',
    `condition_num` string COMMENT '滿減件數(shù)',
    `benefit_amount` string COMMENT '優(yōu)惠金額',
    `benefit_discount` string COMMENT '優(yōu)惠折扣',
    `benefit_level` string COMMENT '優(yōu)惠級(jí)別'
) COMMENT '優(yōu)惠規(guī)則表' 
PARTITIONED BY (`dt` string) 
ROW format delimited FIELDS TERMINATED BY '\t' 
STORED AS 
INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' 
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' 
location '/warehouse/gmall/ods/ods_activity_rule/';

3.23 編碼字典表（全量）

hive (gmall) > DROP TABLE
IF EXISTS ods_base_dic;

CREATE external TABLE ods_base_dic (
    `dic_code` string COMMENT '編號(hào)',
    `dic_name` string COMMENT '編碼名稱',
    `parent_code` string COMMENT '父編碼',
    `create_time` string COMMENT '創(chuàng)建日期',
    `operate_time` string COMMENT '操作日期'
) COMMENT '編碼字典表' 
PARTITIONED BY (`dt` string) 
ROW format delimited FIELDS TERMINATED BY '\t' 
STORED AS 
INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat' 
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' 
location '/warehouse/gmall/ods/ods_base_dic/';

3.24 ODS 層加載數(shù)據(jù)腳本
1）在/home/atguigu/bin 目錄下創(chuàng)建腳本 hdfs_to_ods_db.sh
[atguigu@hadoop102 bin]$ vim hdfs_to_ods_db.sh
在腳本中填寫(xiě)如下內(nèi)容

#!/bin/bash 
APP=gmall 
hive=/opt/module/hive/bin/hive 
# 如果是輸入的日期按照取輸入日期食茎；如果沒(méi)輸入日期取當(dāng)前時(shí)間的前一天 
if [ -n "$2" ] ;
  then 
do_date=$2 
elsedo_date=`date -d "-1 day" +%F` 
fisql1=" 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'); 
load data inpath '/origin_data/$APP/db/base_trademark/$do_date' OVERWRITE into table ${APP}.ods_base_trademark partition(dt='$do_date'); 
load data inpath '/origin_data/$APP/db/activity_info/$do_date' OVERWRITE into table ${APP}.ods_activity_info partition(dt='$do_date');
load data inpath '/origin_data/$APP/db/activity_order/$do_date' OVERWRITE into table ${APP}.ods_activity_order partition(dt='$do_date'); 
load data inpath '/origin_data/$APP/db/cart_info/$do_date' OVERWRITE into table ${APP}.ods_cart_info partition(dt='$do_date'); 
load data inpath '/origin_data/$APP/db/comment_info/$do_date' OVERWRITE into table ${APP}.ods_comment_info partition(dt='$do_date'); 
load data inpath '/origin_data/$APP/db/coupon_info/$do_date' OVERWRITE into table ${APP}.ods_coupon_info partition(dt='$do_date'); 
load data inpath '/origin_data/$APP/db/coupon_use/$do_date' OVERWRITE into table ${APP}.ods_coupon_use partition(dt='$do_date'); 
load data inpath '/origin_data/$APP/db/favor_info/$do_date' OVERWRITE into table ${APP}.ods_favor_info partition(dt='$do_date'); 
load data inpath '/origin_data/$APP/db/order_refund_info/$do_date' OVERWRITE into table ${APP}.ods_order_refund_info partition(dt='$do_date'); 
load data inpath '/origin_data/$APP/db/order_status_log/$do_date' OVERWRITE into table ${APP}.ods_order_status_log partition(dt='$do_date'); 
load data inpath '/origin_data/$APP/db/spu_info/$do_date' OVERWRITE into table ${APP}.ods_spu_info partition(dt='$do_date'); 
load data inpath '/origin_data/$APP/db/activity_rule/$do_date' OVERWRITE into table ${APP}.ods_activity_rule partition(dt='$do_date'); 
load data inpath '/origin_data/$APP/db/base_dic/$do_date' OVERWRITE into table ${APP}.ods_base_dic partition(dt='$do_date'); "
sql2=" load data inpath '/origin_data/$APP/db/base_province/$do_date' OVERWRITE into table ${APP}.ods_base_province; 
load data inpath '/origin_data/$APP/db/base_region/$do_date' OVERWRITE into table ${APP}.ods_base_region; 
"case $1 in 
"first")
{ $hive -e "$sql1" 
$hive -e "$sql2" 
};; 
"all"){ 
    $hive -e "$sql1" 
};; 
esac

2）修改權(quán)限

[atguigu@hadoop102 bin]$ chmod 777 hdfs_to_ods_db.sh

3）初次導(dǎo)入

[atguigu@hadoop102 bin]$ hdfs_to_ods_db.sh first 2020-03-10

4）每日導(dǎo)入

[atguigu@hadoop102 bin]$ hdfs_to_ods_db.sh all 2020-03-11

5）測(cè)試數(shù)據(jù)是否導(dǎo)入成功

hive (gmall)> select * from ods_order_detail where dt='2020-03-11';

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者

人面猴
序言：七十年代末，一起剝皮案震驚了整個(gè)濱河市馏谨，隨后出現(xiàn)的幾起案子别渔，更是在濱河造成了極大的恐慌，老刑警劉巖惧互，帶你破解...
沈念sama閱讀 217,657評(píng)論 6贊 505
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件哎媚，死亡現(xiàn)場(chǎng)離奇詭異，居然都是意外死亡喊儡，警方通過(guò)查閱死者的電腦和手機(jī)拨与，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 92,889評(píng)論 3贊 394
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門(mén)，熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái)艾猜，“玉大人买喧，你說(shuō)我怎么就攤上這事〈以撸” “怎么了岗喉？”我有些...
開(kāi)封第一講書(shū)人閱讀 164,057評(píng)論 0贊 354
道士緝兇錄：失蹤的賣(mài)姜人
文/不壞的土叔我叫張陵，是天一觀的道長(zhǎng)炸庞。經(jīng)常有香客問(wèn)我钱床，道長(zhǎng)，這世上最難降的妖魔是什么埠居？我笑而不...
開(kāi)封第一講書(shū)人閱讀 58,509評(píng)論 1贊 293
?港島之戀（遺憾婚禮）
正文為了忘掉前任查牌，我火速辦了婚禮，結(jié)果婚禮上滥壕，老公的妹妹穿的比我還像新娘纸颜。我一直安慰自己，他們只是感情好绎橘，可當(dāng)我...
茶點(diǎn)故事閱讀 67,562評(píng)論 6贊 392
惡毒庶女頂嫁案：這布局不是一般人想出來(lái)的
文/花漫我一把揭開(kāi)白布胁孙。她就那樣靜靜地躺著唠倦，像睡著了一般。火紅的嫁衣襯著肌膚如雪涮较。梳的紋絲不亂的頭發(fā)上稠鼻，一...
開(kāi)封第一講書(shū)人閱讀 51,443評(píng)論 1贊 302
城市分裂傳說(shuō)
那天，我揣著相機(jī)與錄音狂票，去河邊找鬼候齿。笑死，一個(gè)胖子當(dāng)著我的面吹牛闺属，可吹牛的內(nèi)容都是我干的慌盯。我是一名探鬼主播，決...
沈念sama閱讀 40,251評(píng)論 3贊 418
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開(kāi)眼掂器，長(zhǎng)吁一口氣：“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼亚皂！你這毒婦竟也來(lái)了？” 一聲冷哼從身側(cè)響起国瓮，我...
開(kāi)封第一講書(shū)人閱讀 39,129評(píng)論 0贊 276
萬(wàn)榮殺人案實(shí)錄
序言：老撾萬(wàn)榮一對(duì)情侶失蹤灭必，失蹤者是張志新（化名）和其女友劉穎，沒(méi)想到半個(gè)月后巍膘，有當(dāng)?shù)厝嗽跇?shù)林里發(fā)現(xiàn)了一具尸體，經(jīng)...
沈念sama閱讀 45,561評(píng)論 1贊 314
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡芋簿，尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 37,779評(píng)論 3贊 335
?白月光啟示錄
正文我和宋清朗相戀三年峡懈，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了。大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片与斤。...
茶點(diǎn)故事閱讀 39,902評(píng)論 1贊 348
活死人
序言：一個(gè)原本活蹦亂跳的男人離奇死亡肪康，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出撩穿，到底是詐尸還是另有隱情磷支，我是刑警寧澤，帶...
沈念sama閱讀 35,621評(píng)論 5贊 345
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布食寡，位于F島的核電站雾狈，受9級(jí)特大地震影響，放射性物質(zhì)發(fā)生泄漏抵皱。R本人自食惡果不足惜善榛，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 41,220評(píng)論 3贊 328
男人毒藥：我在死后第九天來(lái)索命
文/蒙蒙一、第九天我趴在偏房一處隱蔽的房頂上張望呻畸。院中可真熱鬧移盆，春花似錦、人聲如沸伤为。這莊子的主人今日做“春日...
開(kāi)封第一講書(shū)人閱讀 31,838評(píng)論 0贊 22
一樁弒父案，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽(yáng)。三九已至叙甸，卻和暖如春颖医，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背蚁署。一陣腳步聲響...
開(kāi)封第一講書(shū)人閱讀 32,971評(píng)論 1贊 269
情欲美人皮
我被黑心中介騙來(lái)泰國(guó)打工便脊，沒(méi)想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留，地道東北人光戈。一個(gè)月前我還...
沈念sama閱讀 48,025評(píng)論 2贊 370
代替公主和親
正文我出身青樓哪痰，卻偏偏與公主長(zhǎng)得像，于是被迫代替她去往敵國(guó)和親久妆。傳聞我的和親對(duì)象是個(gè)殘疾皇子晌杰，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 44,843評(píng)論 2贊 354