DataWorks百问百答36：如何在DataWorks中使用组件（SQL存储过程）实现代码复用？

2020-07-01 3403

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

大数据开发治理平台DataWorks，Serverless资源组抵扣包300CU*H

简介： 组件-可视化SQL存储过程应用场景案例

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钉钉交流群】

DataWorks百问百答36：如何在DataWorks中使用组件（SQL存储过程）实现代码复用？

概论

实践

附录

大数据开发治理DataWorks

热门文章

最新文章

相关产品

相关课程

相关电子书

相关实验场景