DataWorks百问百答36:如何在DataWorks中使用组件(SQL存储过程)实现代码复用?

Q:使用DataWorks业务流程搭建100张表结构一致,仅表名不同的ads结果表的节点,要把sql复制100遍吗?
A:使用组件,实现代码复用。

概论

组件:DataWorks上可视化的SQL存储过程,类似于函数,可将"string类型"、"table类型"的数据作为输入或输出参数放到一个SQL语句中。
组件的组成:

  1. 过程体:即SQL
  2. 参数:输入/输出参数,类型两种"string类型"和"table类型",在过程体中参数的引用格式为:@@{参数名}

注:系统参数bizdate、cyctime都可使用,与普通odpssql节点使用方法一致,创建组件时无需配置。
优势:仅需维护一段sql,实现代码复用,减少重复开发冗余。

实践

场景:访问记录宽表中通过sql获取**北京**上月活跃用户表**上海**上月活跃用户表**广州**上月活跃用户表三张结果表,这三张结果表的特点是:表结构(字段数,数据类型等)一致,where筛选时某些条件值不同,表名不同。
DataWorks百问百答36:如何在DataWorks中使用组件(SQL存储过程)实现代码复用?


业务流程图概览:(下图红框部分即上图实现过程,组件应用过程)
将dwd层的三张表用户表产品表用户行为表过滤、连接、聚合成一张dws_log_info宽表,从这张宽表中过滤出三张表结构一致的ads结果表,通过离线同步任务导出到MySQL数据库,为下一步数据分析做准备。
DataWorks百问百答36:如何在DataWorks中使用组件(SQL存储过程)实现代码复用?


注:组件主要应用在红框部分,也是本文的主要内容,如需测试,红框以上数据准备部分请先执行一下文末附录。


创建组件:
get_city_active_user该组件的作用:创建结果表,将XX城市上个月前N位活跃用户数据插入结果表,降序。(活跃用户即点击次数多的用户)。
step1:编辑组件过程体(忽略这些红色波浪线,代码无错)
step2:配置参数
step3:保存 提交 公开
DataWorks百问百答36:如何在DataWorks中使用组件(SQL存储过程)实现代码复用?

--过程体
--结果表模板 四月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节点
**DataWorks百问百答36:如何在DataWorks中使用组件(SQL存储过程)实现代码复用?
**
查看结果:北京上个月活跃用户点击次数以及他们购买自营商品的次数,按点击次数降序。

DataWorks百问百答36:如何在DataWorks中使用组件(SQL存储过程)实现代码复用?



附录

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百问百答36:如何在DataWorks中使用组件(SQL存储过程)实现代码复用?



组件官方文档链接

DataWorks百问百答历史记录 请点击这里查看>>

更多DataWorks技术和产品信息,欢迎加入【DataWorks钉钉交流群】

上一篇:DataWorks百问百答38:有哪些阿里云vpc环境数据源与数据集成独享资源组网络打通案例?


下一篇:DataWorks百问百答39:VPC网络下adb pg实例引擎如何绑定dataworks工作空间执行任务?