数据库创建外表只要解决从DWS访问DLI下的表数据

2022-06-19 21:20:47

HIVE创建外表

drop table if EXISTS akcdw.cici_seller;
--将as的表结构存储在表中，不存储数据
create EXTERNAL table akcdw.cici_seller
--数据存储格式
STORED AS ORC
--数据存储地址，需要在指定'obs://akc-bigdata/akcdw目录下创建，obs是云服务地址相当于云盘符
location 'obs://akc-bigdata/akcdw/cici_seller'
--将下面的查询结果复制到cici_seller表中，数据存到location中
as
------------------------------------------------------------------------------------------------------------------
select a.dt
,a.user_id
,b.user_level
,sum(forward_cnt)live_forward_cnt
,sum(if(c.live_name like '%超级品牌日%' or c.live_name like '%超级爆品%',forward_cnt,0)) super_live_forward_cnt
,sum(if(c.live_name like '%超级品牌日%',forward_cnt,0)) super_brand_live_forward_cnt
,sum(if(c.live_name like '%超级爆品%',forward_cnt,0)) super_boom_live_forward_cnt
from
(
select dt,user_id,forward_activity_id,sum(forward_cnt)forward_cnt
from akcdw.rpt_user_forward_behavior_d
where dt<='20210331' and dt>='20210325'
and forward_content in ('活动','商品')
and user_role='店主'
group by user_id,forward_activity_id,dt
)a
join akdc.dim_live_info c
on a.forward_activity_id=c.live_id
left join akdc.dim_user_info_akucun_app b
on a.user_id=b.user_id
group by b.user_level,a.user_id,dt;

postgresql创建外表

要在指定的空间下创建，akc_dw的dws的10节点的akc_dws下执行创建sql

--删除外表如果存在
drop FOREIGN TABLE if exists cici_seller;
--建立schema
SET search_path = public;
--建立外表
--– 后面options里面参数的说明
--– filename后面是文件名和绝对路径
--– format是格式,csv是逗号分隔,text表示是tab分隔的方式
--– delimiter是分隔符
--– header表示第一行数据是否需要
--– null表示空数据的转化处理,例子中字段1将转化为null
CREATE FOREIGN TABLE cici_seller (
dt character varying,
user_id character varying,
user_level bigint,
live_forward_cnt bigint,
super_live_forward_cnt bigint,
super_brand_live_forward_cnt bigint,
super_boom_live_forward_cnt bigint
)

--key：server value：obs_server,就相当于一个云服务的地址，去云服务的地址拿数据
SERVER obs_server
OPTIONS (
encoding 'utf8',
foldername '/akc-bigdata/akcdw.db/cici_seller/',--这个是DLI里面的文件地址
format 'orc'
)
DISTRIBUTE BY ROUNDROBIN;

如果DLI的文件更新数据了，那么DWS需要创建调度更新下数据

使用DLI定时刷新数据（可选）

此语句就是将建表语句进行了更改，create部分改成了overwrite，保存成脚本，脚本名称与表名一致(方便后续维护)，此步骤可选，如果不需要定时更新，可不做此步骤

只要DLI定时写入数据到文件里，那么DWS每次也都是从这个文件里读取新数据

INSERT OVERWRITE table akcdw.cici_seller
-------------------------------------------------------------------
select a.dt
,a.user_id
,b.user_level
,sum(forward_cnt)live_forward_cnt
,sum(if(c.live_name like '%超级品牌日%' or c.live_name like '%超级爆品%',forward_cnt,0)) super_live_forward_cnt
,sum(if(c.live_name like '%超级品牌日%',forward_cnt,0)) super_brand_live_forward_cnt
,sum(if(c.live_name like '%超级爆品%',forward_cnt,0)) super_boom_live_forward_cnt
from
(
select dt,user_id,forward_activity_id,sum(forward_cnt)forward_cnt
from akcdw.rpt_user_forward_behavior_d
where dt<='20210331' and dt>='20210325'
and forward_content in ('活动','商品')
and user_role='店主'
group by user_id,forward_activity_id,dt
)a
join akdc.dim_live_info c
on a.forward_activity_id=c.live_id
left join akdc.dim_user_info_akucun_app b
on a.user_id=b.user_id
group by b.user_level,a.user_id,dt;