Q：使用DataWorks業務流程搭建100張表結構一緻，僅表名不同的ads結果表的節點，要把sql複制100遍嗎？

A：使用元件，實作代碼複用。

概論

元件：DataWorks上可視化的SQL存儲過程，類似于函數，可将"string類型"、"table類型"的資料作為輸入或輸出參數放到一個SQL語句中。

元件的組成：

過程體：即SQL
參數：輸入/輸出參數，類型兩種"string類型"和"table類型"，在過程體中參數的引用格式為：@@{參數名}

注：系統參數bizdate、cyctime都可使用，與普通odpssql節點使用方法一緻，建立元件時無需配置。

優勢：僅需維護一段sql，實作代碼複用，減少重複開發備援。

實踐

場景：從

通路記錄寬表

中通過sql擷取

**北京**上月活躍使用者表

、

**上海**上月活躍使用者表

**廣州**上月活躍使用者表

三張結果表，這三張結果表的特點是：表結構（字段數，資料類型等）一緻，where篩選時某些條件值不同，表名不同。

DataWorks百問百答36：如何在DataWorks中使用元件（SQL存儲過程）實作代碼複用？概論實踐附錄

業務流程圖概覽：（下圖紅框部分即上圖實作過程，元件應用過程）

将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存儲過程）實作代碼複用？概論實踐附錄

概論

實踐

附錄

繼續閱讀

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述