Q：使用DataWorks业务流程搭建100张表结构一致，仅表名不同的ads结果表的节点，要把sql复制100遍吗？
A：使用组件，实现代码复用。
概论
组件：DataWorks上可视化的SQL存储过程，类似于函数，可将"string类型"、"table类型"的数据作为输入或输出参数放到一个SQL语句中。
组件的组成：
- 过程体：即SQL
- 参数：输入/输出参数，类型两种"string类型"和"table类型"，在过程体中参数的引用格式为：@@{参数名}
注：系统参数bizdate、cyctime都可使用，与普通odpssql节点使用方法一致，创建组件时无需配置。
优势：仅需维护一段sql，实现代码复用，减少重复开发冗余。
实践
场景：从访问记录宽表中通过sql获取**北京**上月活跃用户表、**上海**上月活跃用户表、**广州**上月活跃用户表三张结果表，这三张结果表的特点是：表结构（字段数，数据类型等）一致，where筛选时某些条件值不同，表名不同。
业务流程图概览：（下图红框部分即上图实现过程，组件应用过程）
将dwd层的三张表用户表、产品表、用户行为表过滤、连接、聚合成一张dws_log_info宽表，从这张宽表中过滤出三张表结构一致的ads结果表，通过离线同步任务导出到MySQL数据库，为下一步数据分析做准备。
注：组件主要应用在红框部分，也是本文的主要内容，如需测试，红框以上数据准备部分请先执行一下文末附录。
创建组件：
get_city_active_user该组件的作用：创建结果表，将XX城市上个月前N位活跃用户数据插入结果表，降序。（活跃用户即点击次数多的用户）。
step1：编辑组件过程体（忽略这些红色波浪线，代码无错）
step2：配置参数
step3：保存 提交 公开
--过程体
--结果表模板 四月XX地区最活跃的用户(点击数)以及点击自营商品数
DROP TABLE IF EXISTS ads_@@{cityname_pinyin}_month_active_user;
CREATE TABLE IF NOT EXISTS ads_@@{cityname_pinyin}_month_active_user
(
`user_id` STRING COMMENT '用户id'
,`user_name` STRING COMMENT '用户名'
,`click_count` STRING COMMENT '点击次数'
,`self_model_count` STRING COMMENT '点击自营商品数'
)
COMMENT '城市上月活跃用户结果表'
PARTITIONED BY
(
pt STRING
)
;
INSERT OVERWRITE TABLE ads_@@{cityname_pinyin}_month_active_user PARTITION(pt='${bizdate}')
SELECT tb_all_model.user_id
,tb_all_model.user_name
,tb_all_model.action_count action_count
,tb_part_model.model_count model_count
FROM (
SELECT user_id
,user_name
,COUNT(action) action_count
,COUNT(model) model_all_count
FROM dws_log_info
WHERE pt = '${bizdate}'
AND REPLACE(SPLIT_PART(action_time,'-',1,2),'-','') = '${prebizmonth}'
AND city = CAST('@@{cityname_zhongwen}' AS STRING)
AND action = '点击'
GROUP BY user_id
,user_name
) tb_all_model
LEFT JOIN (
SELECT user_id
,user_name
,COUNT(action) action_count
,COUNT(model) model_count
FROM dws_log_info
WHERE pt = '${bizdate}'
AND REPLACE(SPLIT_PART(action_time,'-',1,2),'-','') = '${prebizmonth}'
AND city = '@@{cityname_zhongwen}'
AND action = '点击'
AND model = '自营'
GROUP BY user_id
,user_name
) tb_part_model
ON tb_all_model.user_id = tb_part_model.user_id
ORDER BY action_count DESC
LIMIT @@{topn}
;
--SELECT * FROM ads_@@{cityname_pinyin}_month_active_user WHERE pt='${bizdate}';
使用组件
如下图创建SQL组件节点，建三个，北京、上海、广州的节点，选择之前配置好的组件，配置好参数。
ads_beijing_month_action_user节点
ads_guangzhou_month_action_user节点
ads_shanghai_month_action_user节点
**
**
查看结果：北京上个月活跃用户点击次数以及他们购买自营商品的次数，按点击次数降序。
附录
dws_log_info表数据准备部分
--组件应用场景案例，实现“一对多”结果表
--源表1用户表
DROP TABLE IF EXISTS `dwd_user_info_dim` ;
CREATE TABLE IF NOT EXISTS `dwd_user_info_dim`
(
user_id STRING COMMENT '用户id'
,user_name STRING COMMENT '用户名'
,age STRING COMMENT '年龄'
,gender STRING COMMENT '性别'
,city STRING COMMENT '城市'
)
COMMENT '用户信息表'
;
--源表2商品表
DROP TABLE IF EXISTS `dwd_product_info_dim` ;
CREATE TABLE IF NOT EXISTS `dwd_product_info_dim`
(
product_id STRING COMMENT '商品id'
,product_name STRING COMMENT '商品名'
,model STRING COMMENT '模式'
)
COMMENT '商品信息表';
--源表3行为表
DROP TABLE IF EXISTS `dwd_user_action` ;
CREATE TABLE IF NOT EXISTS `dwd_user_action`
(
action_id STRING COMMENT '动作访问记录id'
,user_id STRING COMMENT '用户id'
,product_id STRING COMMENT '商品id'
,action STRING COMMENT '点击|下单|支付'
,action_time STRING COMMENT '动作发生时刻'
)
COMMENT '用户行为表'
PARTITIONED BY
(
pt STRING COMMENT '分区'
)
;
-----------------------------
--源表中插入测试数据
INSERT OVERWRITE TABLE `dwd_user_info_dim` VALUES
('001','张三','18','女','北京')
,('002','李四','22','男','北京')
,('003','王五','36','女','北京')
,('004','赵六','36','女','上海')
,('005','孙七','36','男','上海')
,('006','周八','36','男','广州')
;
INSERT OVERWRITE TABLE `dwd_product_info_dim` VALUES
('A1280','华为手机','自营')
,('B1280','海尔冰箱','三方')
,('C1280','松下洗衣机','自营')
,('D1280','格力空调','三方');
INSERT OVERWRITE TABLE `dwd_user_action` PARTITION (pt='${bizdate}') VALUES
('1122334455000','002','C1280','点击','2020-03-19 11:00:04')
,('1122334455001','004','A1280','点击','2020-04-01 00:00:05')
,('1122334455002','001','C1280','点击','2020-04-02 00:00:04')
,('1122334455003','003','D1280','点击','2020-04-03 00:00:04')
,('1122334455004','005','B1280','点击','2020-04-03 00:00:05')
,('1122334455005','006','D1280','点击','2020-04-04 00:00:05')
,('1122334455006','001','B1280','点击','2020-04-05 00:00:04')
,('1122334455007','001','B1280','下单','2020-04-05 00:00:04')
,('1122334455008','004','C1280','点击','2020-04-05 00:00:05')
,('1122334455009','003','A1280','点击','2020-04-06 00:00:04')
,('1122334455010','001','D1280','点击','2020-04-07 00:00:04')
,('1122334455011','002','C1280','点击','2020-04-08 00:00:04')
,('1122334455012','005','B1280','点击','2020-04-08 00:00:05')
,('1122334455013','002','D1280','点击','2020-04-09 00:00:04')
,('1122334455014','006','B1280','点击','2020-04-09 00:00:05')
,('1122334455015','003','D1280','点击','2020-04-09 00:00:05')
,('1122334455016','002','C1280','点击','2020-04-10 00:00:04')
,('1122334455017','003','A1280','支付','2020-04-11 00:00:04')
,('1122334455018','002','D1280','下单','2020-04-12 00:00:04')
,('1122334455019','003','A1280','下单','2020-04-13 00:00:04')
,('1122334455020','002','C1280','下单','2020-04-14 00:00:04')
,('1122334455021','004','A1280','点击','2020-04-14 00:00:05')
,('1122334455022','005','C1280','点击','2020-04-14 00:00:05')
,('1122334455023','001','A1280','点击','2020-04-15 00:00:04')
,('1122334455024','002','A1280','点击','2020-04-16 00:00:04')
,('1122334455025','003','D1280','点击','2020-04-16 00:00:05')
,('1122334455025','002','C1280','点击','2020-04-17 00:00:04')
,('1122334455027','001','C1280','下单','2020-04-18 00:00:04')
,('1122334455028','003','D1280','点击','2020-04-18 00:00:05')
,('1122334455029','003','D1280','点击','2020-04-19 00:00:05')
,('1122334455030','002','A1280','点击','2020-04-19 00:00:04')
,('1122334455031','002','C1280','支付','2020-04-20 00:00:04')
,('1122334455032','003','D1280','点击','2020-04-02 00:00:05')
,('1122334455033','003','D1280','点击','2020-04-02 00:00:05')
,('1122334455034','002','C1280','点击','2020-05-19 11:00:04');
--------------------------------
--建表
DROP TABLE IF EXISTS `dws_log_info` ;
CREATE TABLE IF NOT EXISTS `dws_log_info`
(
`action_id` STRING COMMENT '动作访问记录id'
,`user_id` STRING COMMENT '用户id'
,`user_name` STRING COMMENT '用户名'
,`city` STRING COMMENT '城市'
,`product_id` STRING COMMENT '商品id'
,`product_name` STRING COMMENT '商品名'
,`model` STRING COMMENT '模式'
,`action` STRING COMMENT '点击|下单|支付'
,`action_time` STRING COMMENT '动作发生时刻'
)
COMMENT '简化版访问记录宽表'
PARTITIONED BY
(
pt STRING
)
;
INSERT OVERWRITE TABLE dws_log_info PARTITION(pt='${bizdate}')
SELECT a.action_id
,b.user_id
,b.user_name
,b.city
,c.product_id
,c.product_name
,c.model
,a.action
,a.action_time
FROM (
SELECT action_id
,user_id
,product_id
,action
,action_time
FROM dwd_user_action
WHERE pt = '${bizdate}'
) a
JOIN (
SELECT user_id
,user_name
,city
FROM dwd_user_info_dim
) b
JOIN (
SELECT product_id
,product_name
,model
FROM dwd_product_info_dim
) c
ON a.user_id = b.user_id
AND a.product_id = c.product_id
;
SELECT * FROM dwd_user_info_dim;
SELECT * FROM dwd_product_info_dim;
SELECT * FROM dwd_user_action WHERE pt='${bizdate}';
SELECT * FROM dws_log_info WHERE pt='${bizdate}';
--SELECT * FROM beijing_month_action_user WHERE pt='${bizdate}';dws_log_info数据预览
组件官方文档链接
DataWorks百问百答历史记录 请点击这里查看>>
更多DataWorks技术和产品信息，欢迎加入【DataWorks钉钉交流群】
