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

|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_nameods_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/bashAPP=ODShive=/opt/yanqi/servers/hive-2.3.7/bin/hive# 可以输入日期；如果未输入日期取昨天的时间if [ -n "$1" ]then do_date=$1else do_date=`date -d "-1 day" +%F`fi# 定义要执行的SQLsql="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;partitiondt=2021-09-28

~~~ # 查看数据是否从hdfs下加载到ODS下hive (ods)> select * from ods_start_log limit 10;OKods_start_log.str ods_start_log.dt2021-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-282021-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

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。