商品订单数仓ODS层

和之前的用户行为数仓一样，ODS和DWD这两层和业务需求没啥关系，所以可以先搞起来。

ODS层初始化脚本。

> cd /home/work/warehouse_goods_order
> vi ods_shopmall_init.sh

#!/bin/bash
# ods层数据库和表初始化，只需要执行一次

hive -e "
create database if not exists ods_shopmall;

create external table if not exists ods_shopmall.ods_user (
    id              bigint,
    username        string,
    gender          tinyint,
    birthday        string,
    email           string,
    mobile          string,
    createtime      string,
    disabled        tinyint
) partitioned by(dt string)
  row format delimited
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/ods/user/';

create external table if not exists ods_shopmall.ods_user_addr (
    id              bigint,
    userid          bigint,
    addrname        string,
    isdefault       tinyint,
    username        string,
    mobile          string
) partitioned by(dt string)
  row format delimited
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/ods/user_addr/';

create external table if not exists ods_shopmall.ods_category (
    id              int,
    parentids       string,
    name            string,
    level           int,
    createtime      string
) partitioned by(dt string)
  row format delimited
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/ods/category/';

create external table if not exists ods_shopmall.ods_goods_info (
    id              bigint,
    name            string,
    desc            string,
    price           double,
    categoryid      int,
    createtime      string
) partitioned by(dt string)
  row format delimited
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/ods/goods_info/';

create external table if not exists ods_shopmall.ods_payment (
    id              bigint,
    orderid         bigint,
    tradeno         string,
    money           double,
    type            int,
    createtime      string
) partitioned by(dt string)
  row format delimited
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/ods/payment/';

create external table if not exists ods_shopmall.ods_order (
    id              bigint,
    userid          bigint,
    money           double,
    type            int,
    status          int,
    payid           bigint,
    createtime      string
    update_time     string
) partitioned by(dt string)
  row format delimited
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/ods/order/';

create external table if not exists ods_shopmall.ods_orderitem (
    orderid         bigint,
    goodsid         bigint,
    amount          int,
    price           double,
    createtime      string
) partitioned by(dt string)
  row format delimited
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/ods/orderitem/';
"

然后通过脚本给这些表按天来添加分区。

> cd /home/work/warehouse_goods_order
> vi ods_shopmall_add_partition.sh

#!/bin/bash
# 给ods层的表添加分区，每天凌晨执行一次
if [ "d$1" = "d" ]
then
dt=`date +%Y%m%d --date="1 days ago"`
else
dt=$1
fi

sh ../common_add_partition.sh ods_shopmall.ods_user ${dt} ${dt}
sh ../common_add_partition.sh ods_shopmall.ods_user_addr ${dt} ${dt}
sh ../common_add_partition.sh ods_shopmall.ods_category ${dt} ${dt}
sh ../common_add_partition.sh ods_shopmall.ods_goods_info ${dt} ${dt}
sh ../common_add_partition.sh ods_shopmall.ods_payment ${dt} ${dt}
sh ../common_add_partition.sh ods_shopmall.ods_order ${dt} ${dt}
sh ../common_add_partition.sh ods_shopmall.ods_orderitem ${dt} ${dt}

可以通过定时任务来执行它。

> crontab -e

# 每天凌晨0点1分执行
1 0 * * * /home/work/warehouse_goods_order/ods_shopmall_add_partition.sh

---

感谢支持

更多内容，请移步《超级个体》。