第4章需求一:GMV成交总额
4.1 ADS层
4.1.2建表语句
hive (gmall)>
drop table if exists ads_gmv_sum_day;
create external table ads_gmv_sum_day(
dt
string COMMENT ‘统计日期’,
gmv_count
bigint COMMENT ‘当日gmv订单个数’,
gmv_amount
decimal(16,2) COMMENT ‘当日gmv订单总金额’,
gmv_payment
decimal(16,2) COMMENT ‘当日支付金额’
) COMMENT ‘GMV’
row format delimited fields terminated by ‘\t’
location ‘/warehouse/gmall/ads/ads_gmv_sum_day/’
;
4.1.3数据导入
1)数据导入
hive (gmall)>
insert into table ads_gmv_sum_day
select
‘2019-02-10’ dt,
sum(order_count)gmv_count,
sum(order_amount) gmv_amount,
sum(payment_amount) payment_amount
from dws_user_action
where dt =‘2019-02-10’
group by dt
;
2)查询导入数据
hive (gmall)> select * from ads_gmv_sum_day;
4.1.4数据导入脚本
1)在/home/atguigu/bin目录下创建脚本ads_db_gmv.sh
[atguigu@hadoop102 bin]$ vim ads_db_gmv.sh
在脚本中填写如下内容
#!/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="
insert into table " A P P " . a d s g m v s u m d a y s e