行業背景

行業現狀：
- 廣告仍然是網際網路公司的主要變現手段，2019年，中國廣告市場總體規模達到8674.28億元，較2018年增長了8.54%，據統計全球網際網路市值前十的公司廣告收入占比高達40%，可見其重要性。AI、大資料、智能投放等創新技術的普及應用，不僅創生了一批獨角獸營銷平台，而且大幅拉低了廣告投放門檻，拓寬了廣告市場空間。
大資料在其行業中的作用：
- 大資料技術的應用在改變我們生活及工作的同時，為我們尋找資料背後的客觀規律提供了一種有效途徑。對潛在消費群體進行深入分析，并進行定制營銷基礎上的現代廣告營銷，對資料的規模及精準度有着極高的要求，而大資料的出現無疑為其落地提供了強有力的支撐。

業務場景

類似媒體，新聞類等APP，上面有各種廣告位提供給廣告主。廣告主投放廣告，使用者點選廣告将實時的産生記錄檔資料，對這些日志資料進行實時分析，通過每個廣告位上不同廣告的投放地區、廣告ID、裝置唯一編碼等資訊，可以統計點選次數、投放次數等名額，可用于制定更高效的廣告投放政策，降低投放成本，提高廣告收益。

技術架構

架構解析：

資料采集：該場景中，APP、Web、Server等服務上會産生大量的廣告投放、使用者廣告點選等記錄檔資料，這些日志資料被實時采集至日志服務系統（SLS），作為Flink的資料源。

實時數倉架構：該場景中，整個實時數倉建構，全部通過 Flink完成。Flink讀取SLS中的原始日志資料，經過資料清洗、資料處理等操作寫出到DataHub，Flink進一步讀取DataHub的資料進行實時統計分析，最終輸出對應的名額結果到RDS，供業務系統使用。

業務名額

實時資料中間層，對原始日志進行實時資料清洗
- 擷取投放主題及次元打寬
- 擷取點選主題及次元打寬
統計投放名額
- 某個廣告在某個省的當天投放量
- 某個廣告在某個市的當天投放量
- 某個廣告在某個投放終端的當天投放量
統計點選名額
- 某個廣告在某個省的當天點選量
- 某個廣告在某個市的當天點選量
- 某個廣告在某個投放終端的當天點選量
熱門廣告排行榜

業務代碼

場景一：對原始日志進行實時資料清洗

投放主題

根據業務主題分成投放主題和點選主題，當release_status=1時為投放主題。

輸入表

create table ods_release(
  `sid` varchar,           --投放請求ID
  exts varchar,                       --擴充資訊
  device_type varchar,     --1 android| 2 ios | 9 其他
  release_status varchar,  --投放狀态 1 or 2
  device_num varchar,      --裝置唯一編碼
  release_session varchar, --投放會話ID
  `date` date              --建立時間
) with (
  type ='sls',
...
);

輸出表

create table dw_release_exposure(
  release_session varchar, -- comment '投放會話id'
  release_status varchar,  -- comment '投放狀态'
  device_num varchar,      -- comment '裝置唯一編碼'
  device_type varchar,     -- comment '1 android| 2 ios | 9 其他'
  area_code varchar,       -- comment '地區'
  aid varchar,             -- comment '廣告id'
  ct date                  -- comment '建立時間'
)with(
type='datahub',
...
);

insert into dw_release_exposure
select
  release_session,
  release_status,
  device_num,
  device_type,
  json_value(exts,'$.area_code'),
  json_value(exts,'$.aid'),
  `date` as ct
from
ods_release
where release_status='1'
;

投放主題關聯次元表

投放主題與地區次元表、裝置次元表進行聚合，得出寬表

create table dw_release_exposure(
  release_session varchar, -- comment '投放會話id'
  release_status varchar,  -- comment '投放狀态'
  device_num varchar,      -- comment '裝置唯一編碼'
  device_type varchar,     -- comment '1 android| 2 ios | 9 其他'
  area_code varchar,       -- comment '地區'
  aid varchar,             -- comment '廣告id'
  ct date                  -- comment '建立時間'
)with(
type='datahub',
...
);

--dim次元表
--（地區，省市，唯一地區編碼,編碼和city_id是一一對應的）
create table dim_province(
  area_code varchar,
  province_id bigint,
  province_name varchar,
  city_id bigint,
  city_name varchar,
  region_id bigint,
  region_name varchar,
 PRIMARY KEY (area_code),
 PERIOD FOR SYSTEM_TIME--定義維表的變化周期。
 )with(
    type= 'rds',
...
);

--(使用者裝置次元表）
create table dim_device(
  device_type varchar comment '1 android| 2 ios | 9 其他',
  device_name varchar comment '裝置名字',
 PRIMARY KEY (device_type),
 PERIOD FOR SYSTEM_TIME--定義維表的變化周期。
)with(
type= 'rds',
...
);

create table dm_release_exposure(
  aid varchar,
  aid_count bigint,
  device_name varchar,
  area_code varchar,
  province_id bigint,
  province_name varchar,
  city_id bigint,
  city_name varchar,
  ct date
)with(
type='datahub',
...
);

insert into dm_release_exposure
select
  a.aid,
  count(a.aid) aid_count,
  c.device_name,
  a.area_code,
  b.province_id,
  b.province_name,
  b.city_id,
  b.city_name,
  a.ct
from
dw_release_exposure a
join
dim_province  FOR SYSTEM_TIME AS OF PROCTIME() as b on a.area_code=b.area_code
join
dim_device  FOR SYSTEM_TIME AS OF PROCTIME() as c on a.device_type=c.device_type
group by
a.aid,
a.area_code,
a.ct
;

點選主題

根據業務主題分成投放主題和點選主題，當release_status=2時為點選主題。

create table ods_release(
  `sid` varchar,           --投放請求ID
  exts varchar,                       --擴充資訊
  device_type varchar,     --1 android| 2 ios | 9 其他
  release_status varchar,  --投放狀态 1 or 2
  device_num varchar,      --裝置唯一編碼
  release_session varchar, --投放會話ID
  `date` date              --建立時間
  ) with (
  type ='sls',
...
);

create table dw_release_click(
  release_session varchar,  -- comment '投放會話id'
  release_status varchar,   -- comment '投放狀态'
  device_num varchar,       -- comment '裝置唯一編碼' 
  device_type varchar,      -- comment '1 android| 2 ios | 9 其他'
  `user_id` varchar,          -- comment '使用者id'
  area_code varchar,        -- comment '地區'
  aid varchar,              -- comment '廣告id'
  ct date                   -- comment '建立時間'
)with(
type='datahub',
...
);

insert into dw_release_click
select
  release_session,
  release_status,
  device_num,
  device_type,
  json_value(exts,'$.user_id') as `user_id`,
  json_value(exts,'$.area_code') as area_code,
  json_value(exts,'$.aid') as aid,
  `date` as ct
from
ods_release
where release_status='2'
;

點選主題關聯次元表

點選主題與地區次元表進行聚合，得出寬表

create table dw_release_click(
  release_session varchar,  -- comment '投放會話id'
  release_status varchar,   -- comment '投放狀态'
  device_num varchar,       -- comment '裝置唯一編碼' 
  device_type varchar,      -- comment '1 android| 2 ios | 9 其他'
  area_code varchar,        -- comment '地區'
  aid varchar,              -- comment '廣告id'
  user_id varchar,          -- comment '使用者id'
  ct date                   -- comment '建立時間'
)with(
type='datahub',
...
);

--dim次元表
--（地區，省市，唯一地區編碼,編碼和city_id是一一對應的）
create table dim_province(
  area_code varchar,
  province_id bigint,
  province_name varchar,
  city_id bigint,
  city_name varchar,
  region_id bigint,
  region_name varchar,
 PRIMARY KEY (area_code),
 PERIOD FOR SYSTEM_TIME--定義維表的變化周期。
 )with(
    type= 'rds',
...
);

--(使用者裝置次元表）
create table dim_device(
device_type varchar comment '1 android| 2 ios | 9 其他',
device_name varchar comment '裝置名字',
 PRIMARY KEY (device_type),
 PERIOD FOR SYSTEM_TIME--定義維表的變化周期。
)with(
type= 'rds',
...
);

create table dm_release_click(
  aid varchar,
  aid_count bigint,
  device_name varchar,
  area_code varchar,
  province_id bigint,
  province_name varchar,
  city_id bigint,
  city_name varchar,
  ct date
)with(
type='datahub',
...
);

insert into dm_release_click
select
  a.aid,
  count(a.aid) aid_count,
  c.device_name,
  a.area_code,
  b.province_id,
  b.province_name,
  b.city_id,
  b.city_name,
  a.ct
from
dw_release_click a
join
dim_province  FOR SYSTEM_TIME AS OF PROCTIME() as b
on a.area_code=b.area_code
join
dim_device  FOR SYSTEM_TIME AS OF PROCTIME() as c on
a.device_type=c.device_type
group by
a.aid,
a.area_code,
a.ct
;

場景二：統計投放名額

以aid和province_name分組，統計某個廣告在某個省的當天投放量

create table dm_release_exposure(
  aid varchar,
  aid_count bigint,
  device_name varchar,
  area_code varchar,
  province_id bigint,
  province_name varchar,
  city_id bigint,
  city_name varchar,
  ct date
)with(
type='datahub',
...
);

--某個廣告在某個省的當天投放量
CREATE TABLE ads_release_exposure_pro (
    aid                       VARCHAR,
    aid_count                 BIGINT,
    province_name             VARCHAR,
  ct                        DATE,
    primary key(aid,province_name,ct)
) WITH (
    type= 'rds',
...
);

insert into ads_release_exposure_pro
select 
  aid,
  sum(aid_count) as aid_count,
  province_name,
  ct
from
dm_release_exposure
group by
aid,
province_name,
ct
;

以aid和city_name分組，統計某個廣告在某個市的當天投放量

create table dm_release_exposure(
  aid varchar,
  aid_count bigint,
  device_name varchar,
  area_code varchar,
  province_id bigint,
  province_name varchar,
  city_id bigint,
  city_name varchar,
  ct date
)with(
type='datahub',
...
);

CREATE TABLE ads_release_exposure_city (
    aid                   VARCHAR,
    aid_count             BIGINT,
    city_name             VARCHAR,
  ct                    DATE,
    primary key(aid,city_name,ct)
) WITH (
    type= 'rds',
...
);

insert into ads_release_exposure_city
select 
  aid,
  sum(aid_count) as aid_count,
  city_name,
  ct
from
dm_release_exposure
group by
aid,
city_name,
ct
;

以aid和device_name分組，統計某個廣告在某個使用者用戶端上的當天投放量

create table dm_release_exposure(
  aid varchar,
  aid_count bigint,
  device_name varchar,
  area_code varchar,
  province_id bigint,
  province_name varchar,
  city_id bigint,
  city_name varchar,
  ct date
)with(
type='datahub',
...
);

CREATE TABLE ads_release_exposure_device (
    aid                     VARCHAR,
    aid_count               BIGINT,
    device_name             VARCHAR,
  ct                      DATE,
    primary key(aid,device_name,ct)
) WITH (
    type= 'rds',
...
);

insert into ads_release_exposure_device
select
  aid,
  sum(aid_count),
  device_name,
  ct
from
dm_release_exposure
group by 
aid,
device_name,
ct
;

場景三：統計點選名額

以ct和aid、provice_name分組，統計某個廣告在某個省的當天點選量

create table dm_release_click(
  aid varchar,
  aid_count bigint,
  device_name varchar,
  area_code varchar,
  province_id bigint,
  province_name varchar,
  city_id bigint,
  city_name varchar,
  ct date
)with(
type='datahub',
...
);

CREATE TABLE ads_release_click_pro (
  aid                  VARCHAR,
  aid_count            BIGINT,
  province_name        VARCHAR,
  ct                   DATE,
  primary key(aid,province_name,ct)
) WITH (
  type= 'rds',
...
);

insert into ads_release_click_pro
select
  aid,
  count(aid) as aid_count,
  province_name,
  ct
from
dm_release_click
group by
aid,
province_name,
ct
;

以ct和aid、city_name分組，統計某個廣告在某個市的當天點選量

create table dm_release_click(
aid varchar,
aid_count bigint,
device_name varchar,
area_code varchar,
province_id bigint,
province_name varchar,
city_id bigint,
city_name varchar,
ct date
)with(
type='datahub',
...
);

CREATE TABLE ads_release_click_city (
  aid                  VARCHAR,
  aid_count            BIGINT,
  city_name            VARCHAR,
  ct                   DATE,
  primary key(aid,city_name,ct)
) WITH (
  type= 'rds',
...
);

insert into ads_release_click_city
select
aid,
count(aid) as aid_count,
city_name,
ct
from
dm_release_click
group by
aid,
city_name,
ct
;

create table dm_release_click(
  aid varchar,
  aid_count bigint,
  device_name varchar,
  area_code varchar,
  province_id bigint,
  province_name varchar,
  city_id bigint,
  city_name varchar,
  ct date
)with(
type='datahub',
...
);

CREATE TABLE ads_release_click_device (
  aid                     VARCHAR,
  aid_count               BIGINT,
  device_name             VARCHAR,
  ct                      DATE,
    primary key(aid,device_name,ct)
) WITH (
  type= 'rds',
...
);

insert into ads_release_click_device
select
  aid,
  sum(aid_count),
  device_name,
  ct
from
dm_release_exposure
group by
aid,
device_name,
ct
;

場景四：熱門廣告排行榜

以ct和aid分組，計算當天每個廣告的總點選量，對廣告ID進行topn排序，得到點選次數最多的三個廣告作為最熱門廣告。根據按天次元的時間字段（ct）和廣告ID（aid）分組，計算每天每個廣告的總點選量，根據廣告ID對點選量進行topn排序，統計得到每天點選次數最多的三個廣告，用于資料大屏中的熱門廣告排行榜。

create table dm_release_click(
aid varchar,
aid_count bigint,
area_code varchar,
province_id bigint,
province_name varchar,
city_id bigint,
city_name varchar,
ct date
)with(
type='datahub',
...
);

CREATE TABLE ads_release_click_dtclick (
  Ranking              BIGINT,
    aid                  VARCHAR,
    ct                   DATE,
  aid_count            BIGINT,
  primary key(aid,ct)
) WITH (
    type= 'rds',
...
);

INSERT INTO ads_release_click_dtclick
SELECT 
Ranking,
aid,
ct,
aid_count
FROM (
  SELECT *,
     ROW_NUMBER() OVER (PARTITION BY `ct` ORDER BY aid_count desc) AS Ranking
  FROM (
        SELECT 
       `ct` AS `ct`,
        COUNT(aid) AS aid_count,
        aid
        FROM  dm_release_click
        GROUP BY `ct`,aid
    )a
) 
WHERE Ranking <= 3

實時計算 Flink 版産品交流群

阿裡雲實時計算Flink - 解決方案： https://developer.aliyun.com/article/765097 阿裡雲實時計算Flink - 場景案例： https://ververica.cn/corporate-practice 阿裡雲實時計算Flink - 産品詳情頁： https://www.aliyun.com/product/bigdata/product/sc

【最佳實踐】實時計算Flink在廣告行業的實時數倉建設實踐行業背景業務場景技術架構業務名額業務代碼

行業背景

業務場景

技術架構

業務名額

業務代碼

場景一：對原始日志進行實時資料清洗

投放主題

輸入表

輸出表

投放主題關聯次元表

點選主題

點選主題關聯次元表

場景二：統計投放名額

場景三：統計點選名額

場景四：熱門廣告排行榜

繼續閱讀

尚矽谷—韓順平—圖解 Java設計模式（結構型）（55～）

Storm編譯打包過程中遇到的一些問題及解決方法

新聞 | Mapbox 牽手阿裡，飛豬旅行上線六大城市地圖功能

EGORefreshTableHeaderView 解讀代碼解讀 ELTableViewController 的使用寫在最後

今日頭條iOS用戶端啟動速度優化技術調研實測資料

MapReduce的幾個企業級經典面試案例MapReduce的幾個企業級經典面試案例

[HTML5]自定義屬性 data-* 和 jQuery.data 詳解

9.spark Core 進階2--Cashe

2021-2025年中國運動療法（KT）帶行業市場供需與戰略研究報告

2021年危險化學品經營機關安全管理人員考試題庫及危險化學品經營機關安全管理人員考試技巧

淺談企業活動中進行資料分析的重要性

Android電視機（機頂盒）初次開發的一些經驗分享

無人機--飛控科普

Ambari介紹和架構原理

NOSQL安全攻擊

win10本地scala和spark安裝安裝scala安裝spark