开发者学堂课程【新电商大数据平台2020最新课程:电商项目之广告投放数据表 SQL 实现(下)】学习笔记,与课程紧密联系,让用户快速学习知识。
课程地址:https://developer.aliyun.com/learning/course/640/detail/10538
电商项目之广告投放数据表 SQL 实现(下)
广告投放数据表下部分
select
from tbrelease a
join ods_ nshop.dim. pub. page P
on a.release_ product_ page-p.page_ code and p.page_ type- ' 4'
join ods_ nshop.dim pub_ product
既然要去分类页的产品,类型需要找产品页的,所以需要加 and 的条件。
此时用 UID 进行 join 不太合适,商品信息表中并没 UID,如果用页面布局进行 join
Time taken: 0.206 seconds, Fetched: 10 row(s)
hive> select p.category code from ods_ nshop.dim pub_ product p join ods_ nshop.dim pub_ page PP on pp page tar
get=p . product code timit 10;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using
a different execution engine (i.e. spark, tez) or using Hive 1.x releases .
Query ID = root 20200329101234 9df406ec -4ea3-43a7 bbd7 267485fece14
Total jobs = 1
Stage-1 is selected by condition resolver.
Launching Job 1 out of 1
Number of reduce tasks not specified. Estimated from input data size: 2
In order to change the average Load for a reducer (in
bytes):
set hive,exec . reducers. bytes . per,reduce rE<number>
In order to limit the maxinum number of reducers :
set hive . exec . reducers .max=<number>
In order to set a constant number of reducers:
set map reduce. j ob. reduc esscnumbe P
Cannot run job Locally: Input Size (= 338042241) is larger than hive . exec,mode. Local. auto . inputbytes,max (=134217728)
Starting Job = job 1585434648842 0001, Tracking URL = http://node1: 8088/proxy/ application 1585434648842 000
1/
Kill Command = /usr/local/hadoop-2.7.6/bin/hadoop job kill job_ 1585434648842 0001
页面布局中页面对应的实体编号(如产品、店铺)就是商品信息表的商品ID编号(分类编码+供应商编码+编号)join 会导致有一定的时间间隔,join 出现之后,如果能取到数据,就算完成了。
select
a.customer. id,(取值)
a.device_ _num ,
a.device_ type,
a.05 ,
a.os_ version ,
a. manuf acturer
a.area_ code,
a.release_ sid ,
a.release_ ip,
a.release_ session,
a.release_ sources ,
f.category_ code release. category,(投放产品分类)
b.page_ target release. product,(投放浏览产品)
a.release_ product. page ,
a.ct
from tbrelease a
join ods_ nshop.dim pub_ page p
on a.release prouuct page-p.page code and p.page_ type- 4