电商数仓（四）

最新推荐文章于 2021-12-05 22:01:00 发布
最新推荐文章于 2021-12-05 22:01:00 发布
阅读量615 收藏
点赞数
分类专栏： 大数据
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43362002/article/details/109381388
版权
本文详细介绍了大数据领域的业务术语，如用户、活跃用户和留存用户等，并展示了Hive的collect_set函数和日期处理函数的用法。接着，通过DWS层加载数据的脚本，演示了如何处理和整合数据，以及ADS层的数据加载。此外，还阐述了Sqoop的安装、使用和定时导入脚本的编写，以及ODS和DWD层的数据导入过程。整个流程覆盖了数据处理的各个环节，展示了大数据分析系统的实际操作流程。
摘要由CSDN通过智能技术生成

文章目录

业务知识准备

业务术语

  1. 用户
    用户以设备为判断标准，在移动统计中，每个独立设备认为是一个独立用户。Android系统根据IMEI号，IOS系统根据OpenUDID来标识一个独立用户，每部手机一个用户。
  2. 新增用户
    首次联网使用应用的用户。如果一个用户首次打开某APP，那这个用户定义为新增用户；卸载再安装的设备，不会被算作一次新增。新增用户包括日新增用户、周新增用户、月新增用户。
  3. 活跃用户
    打开应用的用户即为活跃用户，不考虑用户的使用情况。每天一台设备打开多次会被计为一个活跃用户。
  4. 周（月）活跃用户
    某个自然周（月）内启动过应用的用户，该周（月）内的多次启动只记一个活跃用户。
  5. 月活跃率
    月活跃用户与截止到该月累计的用户总和之间的比例。
  6. 沉默用户
    用户仅在安装当天（次日）启动一次，后续时间无再启动行为。该指标可以反映新增用户质量和用户与APP的匹配程度。
  7. 版本分布
    不同版本的周内各天新增用户数，活跃用户数和启动次数。利于判断APP各个版本之间的优劣和用户行为习惯。
  8. 本周回流用户
    上周未启动过应用，本周启动了应用的用户。
  9. 连续n周活跃用户
    连续n周，每周至少启动一次。
  10. 忠诚用户
    连续活跃5周以上的用户
  11. 连续活跃用户
    连续2周及以上活跃的用户
  12. 近期流失用户
    连续n(2<= n <= 4)周没有启动应用的用户。（第n+1周没有启动过）
  13. 留存用户
    某段时间内的新增用户，经过一段时间后，仍然使用应用的被认作是留存用户；这部分用户占当时新增用户的比例即是留存率。
    例如，5月份新增用户200，这200人在6月份启动过应用的有100人，7月份启动过应用的有80人，8月份启动过应用的有50人；则5月份新增用户一个月后的留存率是50%，二个月后的留存率是40%，三个月后的留存率是25%。
  14. 用户新鲜度
    每天启动应用的新老用户比例，即新增用户数占活跃用户数的比例。
  15. 单次使用时长
    每次启动使用的时间长度。
  16. 日使用时长
    累计一天内的使用时间长度。
  17. 启动次数计算标准
    IOS平台应用退到后台就算一次独立的启动；Android平台我们规定，两次启动之间的间隔小于30秒，被计算一次启动。用户在使用过程中，若因收发短信或接电话等退出应用30秒又再次返回应用中，那这两次行为应该是延续而非独立的，所以可以被算作一次使用行为，即一次启动。业内大多使用30秒这个标准，但用户还是可以自定义此时间间隔。

系统函数

collect_set函数

1）创建原数据表

hive (gmall)>
drop table if exists stud;
create table stud (name string, area string, course string, score int);

2）向原数据表中插入数据

hive (gmall)>
insert into table stud values('zhang3','bj','math',88);
insert into table stud values('li4','bj','math',99);
insert into table stud values('wang5','sh','chinese',92);
insert into table stud values('zhao6','sh','chinese',54);
insert into table stud values('tian7','bj','chinese',91);

3）查询表中数据

hive (gmall)> select * from stud;
stud.name       stud.area       stud.course     stud.score
zhang3 bj      math    88
li4     bj      math    99
wang5   sh      chinese 92
zhao6   sh      chinese 54
tian7   bj      chinese 91

4）把同一分组的不同行的数据聚合成一个集合

hive (gmall)> select course, collect_set(area), avg(score) from stud group by course;
chinese ["sh","bj"]     79.0
math    ["bj"]  93.5

5） 用下标可以取某一个

hive (gmall)> select course, collect_set(area)[0], avg(score) from stud group by course;
chinese sh      79.0
math    bj      93.5

日期处理函数

1）date_format函数（根据格式整理日期）

hive (gmall)> select date_format('2019-02-10','yyyy-MM');
2019-02

2）date_add函数（加减日期）

hive (gmall)> select date_add('2019-02-10',-1);
2019-02-09

hive (gmall)> select date_add('2019-02-10',1);
2019-02-11

3）next_day函数
（1）取当前天的下一个周一

hive (gmall)> select next_day('2019-02-12','MO')
2019-02-18

说明：星期一到星期日的英文（Monday，Tuesday、Wednesday、Thursday、Friday、Saturday、Sunday）
（2）取当前周的周一

hive (gmall)> select date_add(next_day('2019-02-12','MO'),-7);
2019-02-11

4）last_day函数（求当月最后一天日期）

hive (gmall)> select last_day('2019-02-10');
2019-02-28

DWS层加载数据脚本

1）在hadoop102的/home/atguigu/bin目录下创建脚本

[atguigu@hadoop102 bin]$ vim dws_uv_log.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="
  set hive.exec.dynamic.partition.mode=nonstrict;

  insert overwrite table "$APP".dws_uv_detail_day partition(dt='$do_date')
  select  
    mid_id,
    concat_ws('|', collect_set(user_id)) user_id,
    concat_ws('|', collect_set(version_code)) version_code,
    concat_ws('|', collect_set(version_name)) version_name,
    concat_ws('|', collect_set(lang)) lang,
    concat_ws('|', collect_set(source)) source,
    concat_ws('|', collect_set(os)) os,
    concat_ws('|', collect_set(area)) area, 
    concat_ws('|', collect_set(model)) model,
    concat_ws('|', collect_set(brand)) brand,
    concat_ws('|', collect_set(sdk_version)) sdk_version,
    concat_ws('|', collect_set(gmail)) gmail,
    concat_ws('|', collect_set(height_width)) height_width,
    concat_ws('|', collect_set(app_time)) app_time,
    concat_ws('|', collect_set(network)) network,
    concat_ws('|', collect_set(lng)) lng,
    concat_ws('|', collect_set(lat)) lat
  from "$APP".dwd_start_log
  where dt='$do_date'  
  group by mid_id;


  insert overwrite table "$APP".dws_uv_detail_wk partition(wk_dt)
  select  
    mid_id,
    concat_ws('|', collect_set(user_id)) user_id,
    concat_ws('|', collect_set(version_code)) version_code,
    concat_ws('|', collect_set(version_name)) version_name,
    concat_ws('|', collect_set(lang)) lang,
    concat_ws('|', collect_set(source)) source,
    concat_ws('|', collect_set(os)) os,
    concat_ws('|', collect_set(area)) area, 
    concat_ws('|', collect_set(model)) model,
    concat_ws('|', collect_set(brand)) brand,
    concat_ws('|', collect_set(sdk_version)) sdk_version,
    concat_ws('|', collect_set(gmail)) gmail,
    concat_ws('|', collect_set(height_width)) height_width,
    concat_ws('|', collect_set(app_time)) app_time,
    concat_ws('|', collect_set(network)) network,
    concat_ws('|', collect_set(lng)) lng,
    concat_ws('|', collect_set(lat)) lat,
    date_add(next_day('$do_date','MO'),-7),
    date_add(next_day('$do_date','MO'),-1),
    concat(date_add( next_day('$do_date','MO'),-7), '_' , date_add(next_day('$do_date','MO'),-1) 
  )
  from "$APP".dws_uv_detail_day
  where dt>=date_add(next_day('$do_date','MO'),-7) and dt<=date_add(next_day('$do_date','MO'),-1) 
  group by mid_id; 


  insert overwrite table "$APP".dws_uv_detail_mn partition(mn)
  select
    mid_id,
    concat_ws('|', collect_set(user_id)) user_id,
    concat_ws('|', collect_set(version_code)) version_code,
    concat_ws('|', collect_set(version_name)) version_name,
    concat_ws('|', collect_set(lang))lang,
    concat_ws('|', collect_set(source)) source,
    concat_ws('|', collect_set(os)) os,
    concat_ws('|', collect_set(area)) area, 
    concat_ws('|', collect_set(model)) model,
    concat_ws('|', collect_set(brand)) brand,
    concat_ws('|', collect_set(sdk_version)) sdk_version,
    concat_ws('|', collect_set(gmail)) gmail,
    concat_ws('|', collect_set(height_width)) height_width,
    concat_ws('|', collect_set(app_time)) app_time,
    concat_ws('|', collect_set(network)) network,
    concat_ws('|', collect_set(lng)) lng,
    concat_ws('|', collect_set(lat)) lat,
    date_format('$do_date','yyyy-MM')
  from "$APP".dws_uv_detail_day
  where date_format(dt,'yyyy-MM') = date_format('$do_date','yyyy-MM')   
  group by mid_id;
"

$hive -e "$sql"

2）增加脚本执行权限

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

3）脚本使用

[atguigu@hadoop102 module]$ dws_uv_log.sh 2019-02-11

4）查询结果

hive (gmall)> select count(*) from dws_uv_detail_day where dt='2019-02-11';
hive (gmall)> select count(*) from dws_uv_detail_wk;
hive (gmall)> select count(*) from dws_uv_detail_mn ;

5）脚本执行时间
企业开发中一般在每日凌晨30分~1点

ADS层加载数据脚本

1）在hadoop102的/home/atguigu/bin目录下创建脚本

[atguigu@hadoop102 bin]$ vi ads_uv_log.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="
  set hive.exec.dynamic.partition.mode=nonstrict;

insert into table "$APP".ads_uv_count 
select  
  '$do_date' dt,
   daycount.ct,
   wkcount.ct,
   mncount.ct,
   if(date_add(next_day('$do_date','MO'),-1)='$do_date','Y','N') ,
   if(last_day('$do_date')='$do_date','Y','N') 
from 
(
   select  
      '$do_date' dt,
       count(*) ct
   from "$APP".dws_uv_detail_day
   where dt='$do_date'  
)daycount   join 
( 
   select  
     '$do_date' dt,
     count (*) ct
   from "$APP".dws_uv_detail_wk
   where wk_dt=concat(date_add(next_day('$do_date','MO'),-7),'_' ,date_add(next_day('$do_date','MO'),-1) )
)  wkcount  on daycount.dt=wkcount.dt
join 
( 
   select  
     '$do_date' dt,
     count (*) ct
   from "$APP".dws_uv_detail_mn
   where mn=date_format('$do_date','yyyy-MM')  
)mncount on daycount.dt=mncount.dt;
"

$hive -e "$sql"

2）增加脚本执行权限

chmod 777 ads_uv_log.sh

3）脚本使用

[atguigu@hadoop102 module]$ ads_uv_log.sh 2019-02-11

4）脚本执行时间
企业开发中一般在每日凌晨30分~1点

新数据准备

  1. 先关闭当前集群
cluster.sh stop
  1. 修改时间
dt.sh 2019-02-12
  1. 启动集群
cluster.sh stop
  1. 生成日志数据
lg.sh
  1. 将HDFS数据导入到ODS层
ods_log.sh 2019-02-12
  1. 将ODS数据导入到DWD层
[atguigu@hadoop102 ~]$ dwd_start_log.sh 2019-02-12
[atguigu@hadoop102 ~]$ dwd_base_log.sh 2019-02-12
[atguigu@hadoop102 ~]$ dwd_event_log.sh 2019-02-12
  1. 将DWD数据导入到DWS层
[atguigu@hadoop102 ~]$ dws_uv_log.sh 2019-02-12
  1. 验证
hive (gmall)> select * from dws_uv_detail_day where dt='2019-02-12' limit 2;

sqoop的安装

  1. 下载地址：http://mirrors.hust.edu.cn/apache/sqoop/1.4.6/
  2. 解压sqoop安装包到指定目录，如：
tar -zxvf sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz -C /opt/module/
  1. 修改sqoop的名称
mv sqoop-1.4.6.bin__hadoop-2.0.4-alpha/ sqoop
  1. 进入sqoop的conf目录，修改env文件名
cd conf/
mv sqoop-env-template.sh sqoop-env.sh
  1. 配置sqoop-env.sh文件
vi sqoop-env.sh

添加如下配置

export HADOOP_COMMON_HOME=/opt/module/hadoop-2.7.2
export HADOOP_MAPRED_HOME=/opt/module/hadoop-2.7.2
export HIVE_HOME=/opt/module/hive
export ZOOKEEPER_HOME=/opt/module/zookeeper-3.4.10
export ZOOCFGDIR=/opt/module/zookeeper-3.4.10
export HBASE_HOME=/opt/module/hbase
  1. 进入以下目录目录
cd /opt/software/mysql-libs/mysql-connector-java-5.1.27
  1. 拷贝jdbc驱动到sqoop的lib目录下，如：
sudo cp mysql-connector-java-5.1.27-bin.jar /opt/module/sqoop/lib/
  1. 进入sqoop的lib目录
 cd /opt/module/sqoop/lib/
  1. 修改jar包权限
 sudo chown atguigu:atguigu mysql-connector-java-5.1.27-bin.jar
  1. 进入sqoop目录,执行以下命令，验证sqoop
 bin/sqoop help
  1. 测试Sqoop是否能够成功连接数据库
bin/sqoop list-databases --connect jdbc:mysql://hadoop102:3306/ --username root --password root

Sqoop定时导入脚本（注意mysql修改密码）

1）在/home/atguigu/bin目录下创建脚本sqoop_import.sh

vi sqoop_import.sh

在脚本中填写如下内容

#!/bin/bash

db_date=$2
echo $db_date
db_name=gmall

import_data() {
/opt/module/sqoop/bin/sqoop import \
--connect jdbc:mysql://hadoop102:3306/$db_name \
--username root \
--password root \
--target-dir /origin_data/$db_name/db/$1/$db_date \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by "\t" \
--query "$2"' and $CONDITIONS;'
}

import_sku_info(){
  import_data "sku_info" "select 
id, spu_id, price, sku_name, sku_desc, weight, tm_id,
category3_id, create_time
  from sku_info where 1=1"
}

import_user_info(){
  import_data "user_info" "select 
id, name, birthday, gender, email, user_level, 
create_time 
from user_info where 1=1"
}

import_base_category1(){
  import_data "base_category1" "select 
id, name from base_category1 where 1=1"
}

import_base_category2(){
  import_data "base_category2" "select 
id, name, category1_id from base_category2 where 1=1"
}

import_base_category3(){
  import_data "base_category3" "select id, name, category2_id from base_category3 where 1=1"
}

import_order_detail(){
  import_data   "order_detail"   "select 
    od.id, 
    order_id, 
    user_id, 
    sku_id, 
    sku_name, 
    order_price, 
    sku_num, 
    o.create_time  
  from order_info o, order_detail od
  where o.id=od.order_id
  and DATE_FORMAT(create_time,'%Y-%m-%d')='$db_date'"
}

import_payment_info(){
  import_data "payment_info"   "select 
    id,  
    out_trade_no, 
    order_id, 
    user_id, 
    alipay_trade_no, 
    total_amount,  
    subject, 
    payment_type, 
    payment_time 
  from payment_info 
  where DATE_FORMAT(payment_time,'%Y-%m-%d')='$db_date'"
}

import_order_info(){
  import_data   "order_info"   "select 
    id, 
    total_amount, 
    order_status, 
    user_id, 
    payment_way, 
    out_trade_no, 
    create_time, 
    operate_time  
  from order_info 
  where (DATE_FORMAT(create_time,'%Y-%m-%d')='$db_date' or DATE_FORMAT(operate_time,'%Y-%m-%d')='$db_date')"
}

case $1 in
  "base_category1")
     import_base_category1
;;
  "base_category2")
     import_base_category2
;;
  "base_category3")
     import_base_category3
;;
  "order_info")
     import_order_info
;;
  "order_detail")
     import_order_detail
;;
  "sku_info")
     import_sku_info
;;
  "user_info")
     import_user_info
;;
  "payment_info")
     import_payment_info
;;
   "all")
   import_base_category1
   import_base_category2
   import_base_category3
   import_order_info
   import_order_detail
   import_sku_info
   import_user_info
   import_payment_info
;;
esac

2）增加脚本执行权限

chmod 777 sqoop_import.sh

3）执行脚本导入数据(先重启集群)

[atguigu@hadoop102 bin]$ sqoop_import.sh all 2019-02-10

4）在SQLyog中生成2019年2月11日数据

CALL init_data('2019-02-11',1000,200,300,TRUE);

5）执行脚本导入数据

[atguigu@hadoop102 bin]$ sqoop_import.sh all 2019-02-11

ODS层数据导入脚本

1）在/home/atguigu/bin目录下创建脚本ods_db.sh

vi ods_db.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=" 
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"

2）增加脚本执行权限

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

3）执行脚本导入数据

[atguigu@hadoop102 bin]$ ods_db.sh 2019-02-10
[atguigu@hadoop102 bin]$ ods_db.sh 2019-02-11

4）查询导入数据

hive (gmall)> 
select * from ods_order_info where dt='2019-02-10' limit 1;
select * from ods_order_info where dt='2019-02-11' limit 1;

DWD层数据导入脚本

1）在/home/atguigu/bin目录下创建脚本dwd_db.sh

vi dwd_db.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="

set hive.exec.dynamic.partition.mode=nonstrict;

insert overwrite table "$APP".dwd_order_info partition(dt)
select * from "$APP".ods_order_info 
where dt='$do_date' and id is not null;
 
insert overwrite table "$APP".dwd_order_detail partition(dt)
select * from "$APP".ods_order_detail 
where dt='$do_date'   and id is not null;

insert overwrite table "$APP".dwd_user_info partition(dt)
select * from "$APP".ods_user_info
where dt='$do_date' and id is not null;
 
insert overwrite table "$APP".dwd_payment_info partition(dt)
select * from "$APP".ods_payment_info
where dt='$do_date' and id is not null;

insert overwrite table "$APP".dwd_sku_info partition(dt)
select  
    sku.id,
    sku.spu_id,
    sku.price,
    sku.sku_name,
    sku.sku_desc,
    sku.weight,
    sku.tm_id,
    sku.category3_id,
    c2.id category2_id,
    c1.id category1_id,
    c3.name category3_name,
    c2.name category2_name,
    c1.name category1_name,
    sku.create_time,
    sku.dt
from
    "$APP".ods_sku_info sku
join "$APP".ods_base_category3 c3 on sku.category3_id=c3.id 
    join "$APP".ods_base_category2 c2 on c3.category2_id=c2.id 
    join "$APP".ods_base_category1 c1 on c2.category1_id=c1.id 
where sku.dt='$do_date'  and c2.dt='$do_date'
and c3.dt='$do_date' and c1.dt='$do_date'
and sku.id is not null;
"

$hive -e "$sql"

2）增加脚本执行权限

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

3）执行脚本导入数据

[atguigu@hadoop102 bin]$ dwd_db.sh 2019-02-10
[atguigu@hadoop102 bin]$ dwd_db.sh 2019-02-11

4）查看导入数据

hive (gmall)>
select * from dwd_sku_info where dt='2019-02-10' limit 2;
select * from dwd_sku_info where dt='2019-02-11' limit 2;

用户行为数据宽表导入脚本

1）在/home/atguigu/bin目录下创建脚本dws_db_wide.sh

vi dws_db_wide.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="

with  
tmp_order as
(
    select 
        user_id, 
        sum(oi.total_amount) order_amount, 
        count(*)  order_count
    from "$APP".dwd_order_info  oi
    where date_format(oi.create_time,'yyyy-MM-dd')='$do_date'
    group by user_id
)  ,
tmp_payment as
(
    select 
        user_id, 
        sum(pi.total_amount) payment_amount, 
        count(*) payment_count 
    from "$APP".dwd_payment_info pi 
    where date_format(pi.payment_time,'yyyy-MM-dd')='$do_date'
    group by user_id
),
tmp_comment as
(  
    select  
        user_id, 
        count(*) comment_count
    from "$APP".dwd_comment_log c
    where date_format(c.dt,'yyyy-MM-dd')='$do_date'
    group by user_id 
)

Insert overwrite table "$APP".dws_user_action partition(dt='$do_date')
select 
    user_actions.user_id, 
    sum(user_actions.order_count), 
    sum(user_actions.order_amount),
    sum(user_actions.payment_count), 
    sum(user_actions.payment_amount),
    sum(user_actions.comment_count) 
from
(
    select
        user_id,
        order_count,
        order_amount,
        0 payment_count,
        0 payment_amount,
        0 comment_count
    from tmp_order

    union all
    select
        user_id,
        0,
        0,
        payment_count,
        payment_amount,
        0
    from tmp_payment

    union all
    select
        user_id,
        0,
        0,
        0,
        0,
        comment_count 
    from tmp_comment
 ) user_actions
group by user_id;
"

$hive -e "$sql"

2）增加脚本执行权限

chmod 777 dws_db_wide.sh

3）执行脚本导入数据

dws_db_wide.sh 2019-02-11

4）查看导入数据

hive (gmall)> 
select * from dws_user_action where dt='2019-02-11' limit 2;

ADS层数据导入脚本

1）在/home/atguigu/bin目录下创建脚本ads_db_gmv.sh

vi 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 "$APP".ads_gmv_sum_day 
select 
    '$do_date' dt,
    sum(order_count)  gmv_count,
    sum(order_amount) gmv_amount,
    sum(payment_amount) payment_amount 
from "$APP".dws_user_action 
where dt ='$do_date'
group by dt;
"

$hive -e "$sql"

2）增加脚本执行权限

chmod 777 ads_db_gmv.sh

3）执行脚本导入数据

ads_db_gmv.sh 2019-02-11

4）查看导入数据

hive (gmall)> 
select * from ads_gmv_sum_day where dt='2019-02-11' limit 2;

DWS层数据导入脚本

1）在/home/atguigu/bin目录下创建脚本dws_sale.sh

vi dws_sale.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="

set hive.exec.dynamic.partition.mode=nonstrict;

with
tmp_detail as
(
    select 
        user_id,
        sku_id, 
        sum(sku_num) sku_num,   
        count(*) order_count, 
        sum(od.order_price*sku_num)  order_amount
    from "$APP".dwd_order_detail od
    where od.dt='$do_date'
    group by user_id, sku_id
)  
insert overwrite table "$APP".dws_sale_detail_daycount partition(dt='$do_date')
select 
    tmp_detail.user_id,
    tmp_detail.sku_id,
    u.gender,
    months_between('$do_date', u.birthday)/12  age, 
    u.user_level,
    price,
    sku_name,
    tm_id,
    category3_id,
    category2_id,
    category1_id,
    category3_name,
    category2_name,
    category1_name,
    spu_id,
    tmp_detail.sku_num,
    tmp_detail.order_count,
    tmp_detail.order_amount 
from tmp_detail 
left join "$APP".dwd_user_info u 
on tmp_detail.user_id=u.id and u.dt='$do_date'
left join "$APP".dwd_sku_info s on tmp_detail.sku_id =s.id  and s.dt='$do_date';

"
$hive -e "$sql"

2）增加脚本执行权限

chmod 777 dws_sale.sh

3）执行脚本导入数据

dws_sale.sh 2019-02-11

4）查看导入数据

hive (gmall)>
select * from dws_sale_detail_daycount where dt='2019-02-11' limit 2;

ADS层数据导入脚本

1）在/home/atguigu/bin目录下创建脚本ads_sale.sh

vi ads_sale.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="

set hive.exec.dynamic.partition.mode=nonstrict;

insert into table "$APP".ads_sale_tm_category1_stat_mn
select   
    mn.sku_tm_id,
    mn.sku_category1_id,
    mn.sku_category1_name,
    sum(if(mn.order_count>=1,1,0)) buycount,
    sum(if(mn.order_count>=2,1,0)) buyTwiceLast,
    sum(if(mn.order_count>=2,1,0))/sum( if(mn.order_count>=1,1,0)) buyTwiceLastRatio,
    sum(if(mn.order_count>=3,1,0)) buy3timeLast,
    sum(if(mn.order_count>=3,1,0))/sum( if(mn.order_count>=1,1,0)) buy3timeLastRatio ,
    date_format('$do_date' ,'yyyy-MM') stat_mn,
    '$do_date' stat_date
from 
(     
select 
        user_id, 
od.sku_tm_id, 
        od.sku_category1_id,
        od.sku_category1_name,  
        sum(order_count) order_count
    from "$APP".dws_sale_detail_daycount  od 
    where date_format(dt,'yyyy-MM')=date_format('$do_date' ,'yyyy-MM')
    group by user_id, od.sku_tm_id, od.sku_category1_id, od.sku_category1_name
) mn
group by mn.sku_tm_id, mn.sku_category1_id, mn.sku_category1_name;

"
$hive -e "$sql"

2）增加脚本执行权限

 chmod 777 ads_sale.sh

3）执行脚本导入数据

ads_sale.sh 2019-02-11

4）查看导入数据

hive (gmall)>
select * from ads_sale_tm_category1_stat_mn limit 2;

Sqoop导出脚本（注意密码修改）

1）编写Sqoop导出脚本
在/home/atguigu/bin目录下创建脚本sqoop_export.sh

vi sqoop_export.sh

在脚本中填写如下内容

#!/bin/bash

db_name=gmall

export_data() {
/opt/module/sqoop/bin/sqoop export \
--connect "jdbc:mysql://hadoop102:3306/${db_name}?useUnicode=true&characterEncoding=utf-8"  \
--username root \
--password root \
--table $1 \
--num-mappers 1 \
--export-dir /warehouse/$db_name/ads/$1 \
--input-fields-terminated-by "\t" \
--update-mode allowinsert \
--update-key "tm_id,category1_id,stat_mn,stat_date" \
--input-null-string '\\N'    \
--input-null-non-string '\\N'
}

case $1 in
  "ads_uv_count")
     export_data "ads_uv_count"
;;
  "ads_user_action_convert_day")
     export_data "ads_user_action_convert_day"
;;
  "ads_gmv_sum_day")
     export_data "ads_gmv_sum_day"
;;
   "all")
	 export_data "ads_uv_count"
	 export_data "ads_user_action_convert_day"
     export_data "ads_gmv_sum_day"
;;
esac

关于导出update还是insert的问题

  • –update-mode：
    updateonly 只更新，无法插入新数据
    allowinsert 允许新增

  • –update-key：允许更新的情况下，指定哪些字段匹配视为同一条数据，进行更新而不增加。多个字段用逗号分隔。

  • –input-null-string和–input-null-non-string：
    分别表示，将字符串列和非字符串列的空串和“null”转换成’\N’。
    官网地址：http://sqoop.apache.org/docs/1.4.6/SqoopUserGuide.html

  • Hive中的Null在底层是以“\N”来存储，而MySQL中的Null在底层就是Null，为了保证数据两端的一致性。在导出数据时采用–input-null-string和–input-null-non-string两个参数。导入数据时采用–null-string和–null-non-string。
    3）执行Sqoop导出脚本

[atguigu@hadoop102 bin]$ chmod 777 sqoop_export.sh
[atguigu@hadoop102 bin]$ sqoop_export.sh all

4）在MySQL中查看结果

SELECT * FROM ads_uv_count;
SELECT * FROM ads_user_retention_day_rate;
SELECT * FROM ads_user_action_convert_day;
SELECT * FROM ads_gmv_sum_day;
SELECT * FROM ads_gmv_sum_province;

结果展示

localhost:8080/active

结果展示

Sparky*
关注
专栏目录
写个数仓吧（12）生成模拟业务数据
KujyouRuri的博客
05-07 430
业务数据的生成 建表语句 CREATE TABLE `order_info` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '编号', `consignee` VARCHAR(100) DEFAULT NULL COMMENT '收货人', `consignee_tel` VARCHAR(20) DEFAULT NULL COMMENT '收件人电话', `total_amount` DECIMAL(10,2) DEFAULT NULL
电商数仓描述_电商数仓架构介绍
weixin_39972768的博客
12-19 520
1.数仓分层概述数据仓库分为三层，自下而上为：数据引入层(ODS，Operation Data Store)数据公共层(CDM，Common Data Model)数据应用层(ADS，Application Data Service)数据引入层(ODS，Operation Data Store)：将原始数据几乎无处理的存放在数据仓库系统，结构上与源系统基本保持一致，是数据仓库的数据准备区。数据公共...
Linux内核内存corruption检查机制KASAN实现原理
勤学如春起之苗，不见其增，日有所长！ 辍学如磨刀之石，不见其损，日有所亏！
05-13 6117
KASAN实现原理 转载自：http://www.wowotech.net/memory_management/424.html \1. 前言 KASAN是一个动态检测内存错误的工具。KASAN可以检测全局变量、栈、堆分配的内存发生越界访问等问题。功能比SLUB DEBUG齐全并且支持实时检测。越界访问的严重性和危害性通过我之前的文章（SLUB DEBUG技术）应该有所了解。正是由于SLUB DEBUG缺陷，因此我们需要一种更加强大的检测工具。难道你不想吗？KASAN就是其中一种。KASAN的使用真的很简单
【离线数仓】Day03-系统业务数据仓库：数仓表概念、搭建、数据导入、数据可视化、Azkaban全调度、拉链表的使用...
USTSD的博客
12-05 270
一、电商业务与数据结构简介 1、业务流程 2、常识：SKU/SPU SKU=Stock Keeping Unit(库存量基本单位)。现在已经被引申为产品统一编号的简称，每种产品均对应有唯一的SKU号。SPU(Standard Product Unit)：是商品信息聚合的最小单位，是一组可复用、易检索的标准化信息集合。 3、电商表结构 二、数仓理论 1、表的分类 实体表 维度表：对...
系统业务数据仓库
cts618
11-14 1152
第1章 电商业务与数据结构简介 1.1 电商业务流程 1.2 电商常识（SKU、SPU） SKU=Stock Keeping Unit（库存量基本单位）。现在已经被引申为产品统一编号的简称，每种产品均对应有唯一的SKU号。 SPU(Standard Product Unit)：是商品信息聚合的最小单位，是一组可复用、易检索的标准化信息集合。 比如，咱们购买一台iPhoneX手机，iPhoneX手机...
大数据之CDH数仓(9) | 数仓之业务数仓搭建
Knight
10-10 828
业务数据生成 建表语句 1）通过SQLyog创建数据库gmall 2）设置数据库编码 3）导入建表语句（1建表脚本） 选择->1建表脚本.sql 4）重复步骤3的导入方式，依次导入：2商品分类数据插入脚本、3函数脚本、4存储过程脚本。 生成业务数据 1）生成业务数据函数说明 init_data ( do_date_string VARCHAR(20) , order_incr_num INT, user_incr_num INT , sku_num INT , if_truncate BOOLEAN
大数据项目之电商数仓（4即席查询数据仓库）.docx
11-18
大数据项目之电商数仓（4即席查询数据仓库） 本文档是关于大数据项目之电商数仓的详细介绍，特别是关于Presto即席查询数据仓库的设计和实现。下面是从标题、描述、标签和部分内容中提炼出的知识点： Presto 概念 ...
尚硅谷大数据项目之电商数仓用户行为采集平台1
08-04
【尚硅谷大数据项目之电商数仓用户行为采集平台1】是一个综合性的教程，旨在教授如何构建一个电商领域的数据仓库，特别关注用户行为数据的采集。本项目涵盖了从数据仓库的基本概念到具体实施的各个阶段，包括项目...
大数据项目之电商数仓（2业务数据采集平台）V4.1.docx
10-25
大数据项目之电商数仓（业务数据采集平台）V4.1 本资源主要介绍了电商数仓的业务数据采集平台，涵盖了电商业务流程、电商常识、电商系统表结构等方面的知识点。 电商业务流程 电商业务流程可以分为以下几个阶段：...
大数据项目之电商数仓（4可视化报表Superset）V4.0.doc
09-07
【大数据项目之电商数仓（4可视化报表Superset）V4.0】 Apache Superset 是一个强大而开源的商业智能（BI）工具，专为数据分析和可视化设计。它提供了丰富的图表选项，支持自定义仪表板，并能与多种数据源集成，如...
大数据项目之电商数仓-代码
08-31
在本大数据项目中，我们关注的是电商数仓的构建，这是一个关键领域，它涉及大量数据的存储、管理和分析，以支持企业的决策制定和业务优化。数据仓库是将来自多个源的交易数据整合到一个中央存储库的过程，为商业智能...
数仓1.4 |业务数仓搭建| 拉链表| Presto
weixin_30312563的博客
03-24 400
电商业务及数据结构 SKU库存量单位，剩余多少；现在已经被引申为产品统一编号的简称，每种产品均对应有唯一的SKU号；每个商品都是一个SKU，每个SKU都有自己独立的库存数。也就是说每一个商品详情展示都是一个SKU（比如iPhone手机的SKU--银色、128G内存、支持联通网以这个为单位记录库存数）。SPU商品聚集的最小单位 ，这类商品的抽象，提取公共的内容；比如一个商品关联了其他好...
电商数仓（一）
sparky的博客
10-25 648
文章目录jdk配置命令hadoop配置编写集群分发脚本 xsync配置hadoop集群 jdk配置命令 解压JDK到/opt/module目录下 tar -zxvf jdk-8u144-linux-x64.tar.gz -C /opt/module/ 进入/opt/module/jdk1.8.0_144目录 pwd # 查看jdk的路径 打开/etc/profile文件 sudo vi /etc/profile 在profile文件末尾添加JDK路径 #JAVA_HOME expor
（从电商项目认识数仓体系九）业务数仓搭建
z774884795的博客
07-06 556
1 业务数据 业务数据一般存入mysql中，过程较为单一简单，不再赘述 2 业务数据导入数仓 2.1 表的同步策略 序号 表名 解释 同步策略 1 order_info 订单表 新增及变化 2 order_detail 订单详情 增量 3 sku_info 商品表 全量 4 user_info 用户表 全量 5 base_category1 商品一级分类表 全量 6
OpenStack平台超详细搭建步骤（先电版--附镜像文件）
热门推荐
sparky的博客
11-21 1万+
centos版本：CentOS-7-x86_64-DVD-2003.iso XianDian版本：XianDian-IaaS-v2.2.iso
全国大学生大数据技能竞赛
sparky的博客
07-05 1万+
文章目录1.ip地址配置 1.ip地址配置 查看ip信息 ip addr 修改配置文件 vi /etc/sysconfig/network-scropts/ifcfg-ens33 3.修改完成后 4. 重启网络服务 service network restart 5.检测网络连接
ubuntu16.04安装sbt
sparky的博客
11-23 1704
文章目录 下载sbt安装包 https://www.scala-sbt.org/download.html 用传输工具将这个压缩包传到ubuntu 解压到/usr/local目录下 tar -zxvf sbt-1.4.3.tgz /usr/local 建立启动sbt的脚本文件 cd /usr/local/sbt/ #进入sbt目录 vi sbt #编写sbt脚本 # 在sbt文本文件中添加如下信息： BT_OPTS="-Xms512M -Xmx1536M -Xss1M -XX:+CMSCla
大数据集群启动顺序及命令
sparky的博客
10-18 1644
1.启动zookeeper 进入目录 cd /opt/module/zookeeper-3.4.10/ 启动zookeeper bin/zkServer.sh start 查看状态 bin/zkServer.sh status 2.启动hadoop 进入hadoop目录 cd /opt/module/hadoop-2.7.3/ 启动所有服务 sbin/start-dfs.sh 在slave1上启动yarn (先进入hadoop目录) sbin/start-yarn
MapReduce知识总结
sparky的博客
07-24 1583
文章目录1.MapReduce概述2.Map和Reduce函数3.MapReduce的核心思想3.1MapTask3.2ReduceTask4. MapReduce优缺点4.1优点4.2缺点5.MapReduce工作流程6. Shuffle详解6.1Shuffle过程简介6.2. Map端的Shuffle过程6.3. Reduce端的Shuffle过程7.MapReduce编程7.1Mapper阶段7.2Reduce阶段7.3Driver阶段 1.MapReduce概述 Map Reduce是一个分布式运
基于Presto的大数据电商数仓建设详解
大数据项目之电商数仓（4即席查询数据仓库） 本文档主要介绍了大数据项目中的电商数仓，特别是第四代即席查询数据仓库。该项目使用Presto作为主要的查询引擎，以下是相关知识点的总结： **Presto 概念** Presto是...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Sparky*

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值