|NO.Z.00017|——————————|BigDataEnd|——|Hadoop&PB级数仓.V01|——|PB数仓.v01|会员活跃度分析|数据处理&ODS建表&加载数据|

一、ODS建表和数据加载

### --- ODS建表和数据加载

~~~     ODS层的数据与源数据的格式基本相同。

二、创建ODS层表：

### --- 创建ODS层表

[root@hadoop02 ~]# hive

~~~     # 建表语句

use ODS;
drop table if exists ods.ods_start_log;
create external table ods.ods_start_log(`str` string)
comment '用户启动日志信息'
partitioned by (`dt` string)
location '/user/data/logs/start';

~~~     # 创建表

hive (default)> use ODS;
hive (ODS)> drop table if exists ods.ods_start_log;
hive (ODS)> create external table ods.ods_start_log(`str` string)
          > comment '用户启动日志信息'
          > partitioned by (`dt` string)
          > location '/user/data/logs/start';

~~~     # 查看创建的表

hive (ODS)> show tables;
tab_name
ods_start_log

### --- 语句：-- 加载数据的功能(测试时使用)

alter table ods.ods_start_log add partition(dt='2021-09-28');
alter table ods.ods_start_log drop partition (dt='2021-09-28');

~~~     # 加载数据
hive (ODS)> alter table ods.ods_start_log add partition(dt='2021-09-28');

~~~     # 删除数据
hive (ODS)> alter table ods.ods_start_log drop partition (dt='2021-09-28');

~~~     # 查看数据
hive (ODS)> select count(*) from ods_start_log limit 10;

### --- 编写linux脚本创建ODS层表
~~~     # 加载启动日志数据：
~~~     # 可以传参数确定日志，如果没有传参使用昨天日期

[root@hadoop02 ~]# vim /data/yanqidw/script/member_active/ods_load_startlog.sh
#!/bin/bash

APP=ODS
hive=/opt/yanqi/servers/hive-2.3.7/bin/hive

# 可以输入日期；如果未输入日期取昨天的时间
if [ -n "$1" ]
then
    do_date=$1
else
    do_date=`date -d "-1 day" +%F`
fi

# 定义要执行的SQL
sql="alter table "$APP".ods_start_log add partition(dt='$do_date');"

$hive -e "$sql"

~~~     # 为脚本授予可执行权限

[root@hadoop02 ~]# chmod +x /data/yanqidw/script/member_active/ods_load_startlog.sh

### --- 通过linux脚本创建ODS层表

~~~     # 删除表中的数据
hive (ODS)>  alter table ods.ods_start_log drop partition (dt='2021-09-28');

~~~     # 查看日志分区是没有的
hive (ODS)> show partitions ods_start_log;
partition

~~~     # 执行脚本
[root@hadoop02 ~]# /data/yanqidw/script/member_active/ods_load_startlog.sh

### --- 查看数据是否从hdfs加载到ODS下

~~~     # 可以查看到分区表
hive (ods)> show partitions ods.ods_start_log;
partition
dt=2021-09-28

~~~     # 查看数据是否从hdfs下加载到ODS下

hive (ods)> select * from ods_start_log limit 10;
OK
ods_start_log.str   ods_start_log.dt
2021-09-16 16:55:01.203 [main] INFO  com.yanqi.ecommerce.AppStart - {"app_active":{"name":"app_active","json":{"entry":"1","action":"0","error_code":"0"},"time":1595260800000},"attr":{"area":"连云港","uid":"2F10092A1","app_v":"1.1.8","event_type":"common","device_id":"1FB872-9A1001","os_type":"0.43","channel":"PN","language":"chinese","brand":"iphone-7"}}  2021-09-28
2021-09-16 16:55:01.205 [main] INFO  com.yanqi.ecommerce.AppStart - {"app_active":{"name":"app_active","json":{"entry":"1","action":"1","error_code":"0"},"time":1595260800000},"attr":{"area":"金昌","uid":"2F10092A2","app_v":"1.1.5","event_type":"common","device_id":"1FB872-9A1002","os_type":"5.8.7","channel":"OF","language":"chinese","brand":"xiaomi-0"}}  2021-09-28

---

 

 

 

 

 

 

 

 

 

---

Walter Savage Landor:strove with none,for none was worth my strife.Nature I loved and, next to Nature, Art:I warm'd both hands before the fire of life.It sinks, and I am ready to depart

                                                                                                                                                   ——W.S.Landor

---