使用者Portrait analysis案例加工資料-基於新版資料開發和StarRocks計算資源 - DataWorks

本文為您介紹如何將同步至StarRocks的使用者資訊表ods_user_info_d_starrocks及訪問日誌資料ods_raw_log_d_starrocks，通過DataWorks的StarRocks節點加工得到目標使用者畫像資料，閱讀本文後，您可以瞭解如何通過DataWorks+StarRocks產品組合來計算和分析已同步的資料，完成數倉簡單資料加工情境。

前提條件

開始本案例前，請先完成同步資料中的操作。

一、設計資料加工流程

在同步資料階段，已經成功將資料同步至StarRocks，接下來的流程的目標是對資料進行進一步加工，以輸出基本使用者畫像資料。

在Data Studio左側導覽列單擊，然後在專案目錄地區找到已建立好的工作流程，單擊進入工作流程看板，從左側拖拽StarRocks節點至畫布中，分別設定節點名稱。

本教程節點名稱樣本及作用如下：

節點類型	節點名稱	節點作用
StarRocks	`dwd_log_info_di_starrocks`	使用內建函數，自訂函數等完成原始日誌`ods_raw_log_d_starrocks`資料拆分寫入`dwd_log_info_di_starrocks`表多個欄位。
StarRocks	`dws_user_info_all_di_starrocks`	將使用者基本資料（`ods_user_info_d_starrocks`）和初步加工後的日誌資料（`dwd_log_info_di_starrocks`）進行匯總，將資料寫入`dws_user_info_all_di_starrocks`表中。
StarRocks	`ads_user_info_1d_starrocks`	對`dws_user_info_all_di_starrocks`表中資料進一步加工，將資料寫入`ads_user_info_1d_starrocks`表，產出基本使用者畫像。

手動拖拽連線，配置各節點的上遊節點。最終效果如下：
說明
工作流程中支援通過手動連線方式設定各節點的上下遊依賴關係，也支援在子節點中，使用代碼解析自動識別節點上下遊依賴關係。本教程採用手動連線方式，代碼解析的更多資訊，請參見自動解析機制。

二、註冊函數

根據同步的實驗日誌資料結構，我們需要通過函數等方式將其拆解成表格。

重要

本教程已為您提供用於將IP解析為地區的函數所需資源，您需將其下載至本地後，並上傳至OSSObject Storage Service，並按以下照操作步驟，將下載的資源在DataWorks上註冊為函數。
該函數僅為本教程使用（IP資源範例），若需在正式業務中實現IP到地理位置的映射功能，需前往專業IP網站擷取相關IP轉換服務。

上傳資源（ip2region-starrocks.jar）

下載ip2region-starrocks.jar。
說明
ip2region-starrocks.jar資源範例僅為教程使用。
上傳資源至OSS。
1. 登入Object Storage Service控制台，進入準備環境階段建立的OSS Bucket路徑下建立dataworks_starrocks目錄。
2. 上傳ip2region-starrocks.jar資源至dataworks_starrocks目錄下。
  本案例的資源儲存完整路徑為：https://test.oss-cn-shanghai-internal.aliyuncs.com/dataworks_starrocks/ip2region-starrocks.jar，您可參考該路徑格式獲得您上傳的OSS資源儲存路徑。
  說明
  - 本案例Bucket為test。
  - 自訂函數所在的Bucket網路地址使用ECS的傳統網路訪問（內網）地址。
  - 使用內網地址時需要OSS Bucket地區和DataWorks工作空間所在地區相同，本案例均在華東2（上海）地區。

註冊函數（getregion）

建立註冊函數節點。
在Data Studio左側導覽列單擊，然後在專案目錄地區單擊按鈕，選擇建立節點 > 資料庫 > StarRocks建立一個StarRocks節點。

編輯並註冊函數。

註冊函數。

CREATE FUNCTION getregion(string)
RETURNS string
PROPERTIES ( 
    "symbol" = "com.starrocks.udf.sample.Ip2Region", 
    "type" = "StarrocksJar",
    "file" = "此處請替換檔案儲存體在oss的完整路徑，檔案路徑擷取請參見上傳資源"
);

確認函數是否註冊成功。
```
SELECT getregion('您本機ip');
```

重要

函數在生產環境和開發環境僅能進行一次註冊，註冊函數的StarRocks節點需發布至生產環境在生產環境註冊函數。

在頂部工具列單擊儲存，然後單擊發布，根據發布面板提示，將函數註冊任務發布至開發環境和生產環境對應的StarRocks中，並對該任務進行補資料，在生產環境註冊函數。函數註冊完成後，需在營運中心手動凍結生產環境中的註冊函數任務，避免重複註冊導致任務執行失敗。

三、配置StarRocks節點

資料加工需要將每層加工邏輯通過StarRocks調度實現，本案例已提供完整的資料加工SQL範例程式碼，您需要依次為dwd_log_info_di_starrocks、dws_user_info_all_di_starrocks和ads_user_info_1d_starrocks節點配置。

配置dwd_log_info_di_starrocks節點

在本節點的範例程式碼中，利用建立的函數處理上遊表ods_raw_log_d_starrocks欄位的SQL代碼，並將其寫入dwd_log_info_di_starrocks表中。

在工作流程編排頁面，滑鼠移至上方至dwd_log_info_di_starrocks節點上，單擊開啟節點。
在節點編輯頁面的選擇資料來源處選擇準備環境階段準備的StarRocks計算資源。

將如下代碼粘貼至SQL編輯頁面。

說明

dwd_log_info_di_starrocks節點的範例程式碼中，利用建立的函數處理上遊表ods_raw_log_d_starrocks欄位的SQL代碼，並將其寫入dwd_log_info_di_starrocks表中。

dwd_log_info_di_starrocks節點程式碼範例

CREATE TABLE IF NOT EXISTS dwd_log_info_di_starrocks (
    uid STRING COMMENT '使用者ID',
    ip STRING COMMENT 'ip地址',
    TIME STRING COMMENT '時間yyyymmddhh:mi:ss',
    status STRING COMMENT '伺服器返回狀態代碼',
    bytes STRING COMMENT '返回給用戶端的位元組數',
    region STRING COMMENT '地區，根據ip得到',
    method STRING COMMENT 'http請求類型',
    url STRING COMMENT 'url',
    protocol STRING COMMENT 'http協議版本號碼',
    referer STRING COMMENT '來源url',
    device STRING COMMENT '終端類型 ',
    identity STRING COMMENT '訪問類型 crawler feed user unknown',
    dt DATE NOT NULL COMMENT '時間'
) DUPLICATE KEY(uid) 
COMMENT '使用者行為分析案例-網站訪問日誌明細表' 
PARTITION BY(dt) 
PROPERTIES ("replication_num" = "1");

-- 本樣本按欄位dt動態分區，為避免節點重跑資料重複寫入，通過以下命令實現每次加工前刪除已有目標資料分割。
ALTER TABLE dwd_log_info_di_starrocks DROP PARTITION IF EXISTS p${var} FORCE;

--情境：以下SQL使用函數getregion對原始日誌資料中的ip進行解析，並通過正則等方式，將未經處理資料拆解為可分析欄位寫入並寫入dwd_log_info_di_starrocks表。
--補充：
--     1. 在DataWorks節點中使用自訂函數前，您需要先註冊函數。
--     2. DataWorks提供調度參數，可實現調度情境下，將每日增量資料寫入目標表對應業務分區。
--        在實際開發情境下，您可通過${變數名}格式定義代碼變數，並在調度配置頁面通過為變數賦值調度參數的方式，實現調度情境下代碼動態入參。
INSERT INTO dwd_log_info_di_starrocks 
SELECT 
    uid
    , ip  
    , time
    , status
    , bytes 
    , getregion(ip) AS region--使用自訂UDF通過IP得到地區
    ,REGEXP_EXTRACT(request, '([^ ]+)', 1) AS method
    ,REGEXP_EXTRACT(request, '^[^ ]+ (.*) [^ ]+$', 1) AS url
    ,REGEXP_EXTRACT(request, '([^ ]+)$', 1) AS protocol
    ,REGEXP_EXTRACT(referer, '^[^/]+://([^/]+)', 1) AS referer
  , CASE
    WHEN LOWER(agent) REGEXP 'android' THEN 'android'
    WHEN LOWER(agent) REGEXP 'iphone' THEN 'iphone'
    WHEN LOWER(agent) REGEXP 'ipad' THEN 'ipad'
    WHEN LOWER(agent) REGEXP 'macintosh' THEN 'macintosh'
    WHEN LOWER(agent) REGEXP 'windows phone' THEN 'windows_phone'
    WHEN LOWER(agent) REGEXP 'windows' THEN 'windows_pc'
    ELSE 'unknown'
END AS device
  , CASE
    WHEN LOWER(agent) REGEXP '(bot|spider|crawler|slurp)' THEN 'crawler'
    WHEN LOWER(agent) REGEXP 'feed' OR REGEXP_EXTRACT(request, '^[^ ]+ (.*) [^ ]+$', 0) REGEXP 'feed' THEN 'feed'
    WHEN NOT (LOWER(agent) REGEXP '(bot|spider|crawler|feed|slurp)') 
         AND agent REGEXP '^(Mozilla|Opera)' 
         AND NOT (REGEXP_EXTRACT(request, '^[^ ]+ (.*) [^ ]+$', 0) REGEXP 'feed') THEN 'user'
    ELSE 'unknown'
END AS identity,
 cast('${var}' AS DATE )AS dt
  FROM (
    SELECT
      SPLIT_PART(CAST(col AS VARCHAR(65533)), '##@@', 1)  AS ip
    , SPLIT_PART(CAST(col AS VARCHAR(65533)), '##@@', 2)  AS uid
    , SPLIT_PART(CAST(col AS VARCHAR(65533)), '##@@', 3)  AS time
    , SPLIT_PART(CAST(col AS VARCHAR(65533)), '##@@', 4)  AS request
    , SPLIT_PART(CAST(col AS VARCHAR(65533)), '##@@', 5)  AS status
    , SPLIT_PART(CAST(col AS VARCHAR(65533)), '##@@', 6)  AS bytes
    , SPLIT_PART(CAST(col AS VARCHAR(65533)), '##@@', 7)  AS referer
    , SPLIT_PART(CAST(col AS VARCHAR(65533)), '##@@', 8)  AS agent
FROM
    ods_raw_log_d_starrocks
WHERE
    dt = '${var}'
) a;

配置調試參數。

在StarRocks編輯頁面右側單擊調試配置，配置以下參數，用於在步驟四調試運行中使用調試配置的相關參數測試回合。

配置項	配置說明
計算資源	選擇準備環境階段綁定的StarRocks計算資源。
資源群組	選擇準備環境階段購買的Serverless資源群組。
指令碼參數	單擊添加參數，配置為`var=yyyymmdd格式的具體常量`（例如`var=20250223`）。在調試時，Data Studio將會使用此常量替換任務中的定義的變數。

（可選）配置調度屬性。
本教程調度配置相關參數保持預設即可，您可以在節點編輯頁面右側單擊調度配置。調度配置中參數的詳細說明，詳情可參見節點調度。
- 調度參數：本教程已在工作流程調度參數中統一配置，工作流程內部節點無需配置，在任務或代碼中可直接使用。
- 調度策略：您可以在延時執行時間參數中指定子節點在工作流程執行後，延遲多久再執行，本教程不設定。
在頂部工具列單擊儲存，儲存當前節點。

配置dws_user_info_all_di_starrocks節點

本節點對使用者基本資料表（ods_user_info_d_starrocks）和初步加工後的日誌資料表（dwd_log_info_di_starrocks）進行匯總，將資料寫入dws_user_info_all_di_starrocks表中。

在工作流程編排頁面，滑鼠移至上方至dws_user_info_all_di_starrocks節點上，單擊開啟節點。
在節點編輯頁面的選擇資料來源處選擇準備環境階段準備的StarRocks計算資源。

將如下代碼粘貼至SQL編輯頁面。

說明

dws_user_info_all_di_starrocks節點的編輯頁面，編寫合并上遊表dwd_log_info_di_starrocks和ods_user_info_d_starrocks的SQL代碼，寫入dws_user_info_all_di_starrocks表中。

dws_user_info_all_di_starrocks節點程式碼範例

CREATE TABLE IF NOT EXISTS dws_user_info_all_di_starrocks (
    uid STRING COMMENT '使用者ID',
    gender STRING COMMENT '性別',
    age_range STRING COMMENT '年齡段',
    zodiac STRING COMMENT '星座',
    region STRING COMMENT '地區，根據ip得到',
    device STRING COMMENT '終端類型 ',
    identity STRING COMMENT '訪問類型 crawler feed user unknown',
    method STRING COMMENT 'http請求類型',
    url STRING COMMENT 'url',
    referer STRING COMMENT '來源url',
    TIME STRING COMMENT '時間yyyymmddhh:mi:ss',
    dt DATE NOT NULL COMMENT '時間'
) DUPLICATE KEY(uid) 
COMMENT '使用者行為分析案例-使用者網站訪問資訊寬表' 
PARTITION BY(dt) 
PROPERTIES ("replication_num" = "1");

-- 本樣本按欄位dt動態分區，為避免節點重跑資料重複寫入，通過以下命令實現每次加工前刪除已有目標資料分割。
ALTER TABLE dws_user_info_all_di_starrocks DROP PARTITION IF EXISTS p${var} FORCE;


-- 情境：將加工後的日誌資料dwd_log_info_di_starrocks 與使用者基本資料資料ods_user_info_d_starrocks匯總寫入dws_user_info_all_di_starrocks表。
-- 補充：DataWorks提供調度參數，可實現調度情境下，將每日增量資料寫入目標表對應業務分區。
--      在實際開發情境下，您可通過${變數名}格式定義代碼變數，並在調度配置頁面通過為變數賦值調度參數的方式，實現調度情境下代碼動態入參。
INSERT INTO dws_user_info_all_di_starrocks 
SELECT 
    IFNULL(a.uid, b.uid) AS uid,
    b.gender,
    b.age_range,
    b.zodiac,
    a.region,
    a.device,
    a.identity,
    a.method,
    a.url,
    a.referer,
    a.time,
    a.dt
FROM dwd_log_info_di_starrocks a
LEFT JOIN ods_user_info_d_starrocks b
ON a.uid = b.uid
WHERE a.dt = '${var}';

配置調試參數。

在StarRocks編輯頁面右側單擊調試配置，配置以下參數，用於在步驟四調試運行中使用調試配置的相關參數測試回合。

配置項	配置說明
計算資源	選擇準備環境階段綁定的StarRocks計算資源。
資源群組	選擇準備環境階段購買的Serverless資源群組。
指令碼參數	單擊添加參數，配置為`var=yyyymmdd格式的具體常量`（例如`var=20250223`）。在調試時，Data Studio將會使用此常量替換任務中的定義的變數。

（可選）配置調度屬性。
本教程調度配置相關參數保持預設即可，您可以在節點編輯頁面右側單擊調度配置。調度配置中參數的詳細說明，詳情可參見節點調度。
- 調度參數：本教程已在工作流程調度參數中統一配置，工作流程內部節點無需配置，在任務或代碼中可直接使用。
- 調度策略：您可以在延時執行時間參數中指定子節點在工作流程執行後，延遲多久再執行，本教程不設定。
在頂部工具列單擊儲存，儲存當前節點。

配置ads_user_info_1d_starrocks節點

本節點對dws_user_info_all_di_starrocks表中資料進一步加工，將資料寫入ads_user_info_1d_starrocks表，產出基本使用者畫像。

在工作流程編排頁面，滑鼠移至上方至ads_user_info_1d_starrocks節點上，單擊開啟節點。
在節點編輯頁面的選擇資料來源處選擇準備環境階段準備的StarRocks計算資源。

將如下代碼粘貼至SQL編輯頁面。

ads_user_info_1d_starrocks節點程式碼範例

CREATE TABLE IF NOT EXISTS ads_user_info_1d_starrocks (
uid STRING COMMENT '使用者ID',
region STRING COMMENT '地區，根據ip得到',
device STRING COMMENT '終端類型 ',
pv BIGINT COMMENT 'pv',
gender STRING COMMENT '性別',
age_range STRING COMMENT '年齡段',
zodiac STRING COMMENT '星座',
dt DATE NOT NULL COMMENT '時間'
) DUPLICATE KEY(uid) 
COMMENT '使用者行為分析案例-使用者畫像資料' 
PARTITION BY(dt) 
PROPERTIES ("replication_num" = "1");

-- 本樣本按欄位dt動態分區，為避免節點重跑資料重複寫入，通過以下命令實現每次加工前刪除已有目標資料分割。
ALTER TABLE ads_user_info_1d_starrocks DROP PARTITION IF EXISTS p${var} FORCE;

-- 情境：以下SQL用於對使用者訪問資訊寬表dws_user_info_d_all_di_starrocks 進一步加工產出基本的使用者畫像資料寫入ads_user_info_1d_starrocks 表。
-- 補充：DataWorks提供調度參數，可實現調度情境下，將每日增量資料寫入目標表對應業務分區。
-- 在實際開發情境下，您可通過${變數名}格式定義代碼變數，並在調度配置頁面通過為變數賦值調度參數的方式，實現調度情境下代碼動態入參。
INSERT INTO ads_user_info_1d_starrocks 
SELECT 
uid,
MAX(region) AS region,
MAX(device) AS device,
COUNT(*) AS pv,
MAX(gender) AS gender,
MAX(age_range) AS age_range,
MAX(zodiac) AS zodiac,
dt
FROM dws_user_info_all_di_starrocks
WHERE dt = '${var}'
GROUP BY uid, dt;

SELECT * FROM dws_user_info_all_di_starrocks
WHERE dt = '${var}';

配置調試參數。

在StarRocks編輯頁面右側單擊調試配置，配置以下參數，用於在步驟四調試運行中使用調試配置的相關參數測試回合。

配置項	配置說明
計算資源	選擇準備環境階段綁定的StarRocks計算資源。
資源群組	選擇準備環境階段購買的Serverless資源群組。
指令碼參數	單擊添加參數，配置為`var=yyyymmdd格式的具體常量`（例如`var=20250223`）。在調試時，Data Studio將會使用此常量替換任務中的定義的變數。

（可選）配置調度屬性。
本教程調度配置相關參數保持預設即可，您可以在節點編輯頁面右側單擊調度配置。調度配置中參數的詳細說明，詳情可參見節點調度。
- 調度參數：本教程已在工作流程調度參數中統一配置，工作流程內部節點無需配置，在任務或代碼中可直接使用。
- 調度策略：您可以在延時執行時間參數中指定子節點在工作流程執行後，延遲多久再執行，本教程不設定。
在頂部工具列單擊儲存，儲存當前節點。

四、加工資料

加工資料。
在工作流程工具列中，單擊運行，設定各節點定義的參數變數在本次運行中的取值（本教程使用20250223，您可以按需修改），單擊確定後，等待運行完成。

查詢結果。

進入SQL查詢頁面。
登入DataWorks控制台，切換至目標地區後，單擊左側導覽列的資料分析與服務 > 資料分析，單擊進入資料分析頁面，單擊左側導覽列的SQL查詢進入SQL查詢頁面。

配置SQL查詢檔案。

單擊我的檔案後的按鈕建立檔案，自訂SQL查詢檔案名稱。
單擊已建立的檔案，進入檔案編輯頁面。

在檔案編輯頁面單擊右上方的按鈕，配置需進行SQL查詢的工作空間等資訊，配置詳情如下：

配置項	說明
工作空間	選擇`user_profile_analysis_starrocks`工作流程所在的工作空間。
資料來源類型	下拉選擇`StarRocks`。
資料來源名稱	選擇在準備環境時綁定的Starrocks開發環境。

單擊確認按鈕，完成查詢資料來源的配置。

編輯查詢SQL。

在確保該章節內的所有節點運行成功的情況下，編寫以下SQL查詢以檢查StarRocks節點建立的外部表格是否正常產出。

--查詢語句中的分區列需要更新為業務日期。例如，任務啟動並執行日期為20250223，則業務日期為20250222，即任務運行日期的前一天。
SELECT * FROM ads_user_info_1d_starrocks  WHERE dt=業務日期;

五、發布工作流程

任務需要發布至生產環境後才可自動調度運行，您可以參考如下步驟，將工作流程發布至生產環境。

說明

本教程已在工作流程調度配置中統一配置了調度參數，發布前無需再為每個節點單獨配置調度參數。

在Data Studio左側導覽列單擊，進入資料開發頁面，然後在專案目錄地區找到已建立好的工作流程，單擊進入工作流程編排頁面。
單擊節點工具列中的發布，開啟發布面板。
單擊開始發布生產，根據發布流程引導，完成發布。

六、在生產環境運行任務

任務發布後，在次日才會產生執行個體運行，您可以通過補資料來對發行流程進行補資料操作，以便查看任務在生產環境是否可以運行，詳情可參見執行補資料並查看補資料執行個體（新版）。

任務發布成功後，單擊右上方的營運中心。
您也可以單擊左上方的表徵圖，選擇全部產品 > 資料開發與營運 > 營運中心（工作流程）。
單擊左側導覽列中的周期任務營運 > 周期任務，進入周期任務頁面，單擊workshop_start_starrocks虛擬節點。
在右側的DAG圖中，按右鍵workshop_start_starrocks節點，選擇補資料 > 當前節點及下遊節點。
勾選需要補資料的任務，設定業務日期，單擊提交並跳轉。
在補資料頁面單擊重新整理，直至SQL任務全部運行成功即可。

說明

案例完成後，為了避免後續持續產生費用，您可以選擇設定節點調度有效期間或者凍結虛擬節點workshop_start_starrocks。

後續步驟

資料視覺效果展現：使用者Portrait analysis完成後，使用資料分析模組，將加工後的資料以圖表形式直觀展示，便於您快速提取關鍵資訊，洞察資料背後的業務趨勢。
監控資料品質：為資料加工產生的表配置資料品質監控，提前識別髒資料並進行攔截，避免髒資料影響擴大。
管理資料：使用者Portrait analysis任務流程完成後，在StarRocks內將建立對應資料表。產生的資料表可在資料地圖模組進行查看，可通過血緣查看產生表之間的關係。
API資料服務：擷取最終加工後的資料後，使用資料服務模組，通過標準化的資料服務介面，實現資料的共用與應用，為其他使用API接收資料的業務模組提供資料。