：DataWorks OpenLake一站式智能化湖倉一體資料開發

建立Notebook

1. 進入Data Studio > 資料開發。

2. 在個人目錄中，按右鍵目標檔案夾，選擇建立Notebook。

3. 輸入Notebook名稱，單擊斷行符號鍵 或 頁面空白位置，使Notebook名稱生效。

4. 在個人目錄中單擊Notebook名稱，即可開啟並進入Notebook編輯頁面。

Notebook使用

• Notebook多引擎開發

  EMR Spark SQL

  1. 在DataWorks Notebook中單擊按鈕，建立SQL Cell。

  2. 在SQL Cell中，輸入以下語句，完成dim_ec_mbr_user_info 表的查詢。

     dim_ec_mbr_user_info

     -- 說明：基於「會員資訊源表」和「地區源表」，查詢某電商平台的會員基礎資訊。
     USE openlake_win.default;
     SELECT  user.user_id AS user_id
             ,user.nick AS nick
             ,user.gmt_create AS gmt_modified
             ,user.gmt_modified AS gmt_create
             ,user.reg_fullname AS reg_fullname
             ,user.reg_mobile_phone AS reg_mobile_phone
             ,user.reg_email AS reg_email
             ,user.reg_gender AS reg_gender
             ,user.reg_gender_name AS reg_gender_name
             ,user.reg_birthdate AS reg_birthdate
             ,user.reg_address AS reg_address
             ,user.reg_nation_id AS reg_nation_id
             ,user.reg_nation_name AS reg_nation_name
             ,user.reg_prov_id AS reg_prov_id
             ,area_prov.name AS reg_prov_name
             ,user.reg_city_id AS reg_city_id
             ,area_city.name AS reg_city_name
             ,user.user_regip AS user_regip
             ,user.id_card_type AS id_card_type
             ,user.id_card_type_name AS id_card_type_name
             ,user.id_card_number AS id_card_number
             ,null as id_gender
             ,null as id_bday
             ,(2024 - CAST(SUBSTR(user.id_card_number,7,4) AS INT)) AS id_age
             ,user.user_regdate AS user_regdate
             ,user.user_active_type AS user_active_type
             ,user.user_active_name AS user_active_name
             ,user.user_active_time AS user_active_time
             ,user.vip_level AS vip_level
             ,user.vip_level_name AS vip_level_name
             ,user.is_delete AS is_delete
     FROM    (
                 SELECT  id    -- 主鍵
                         ,gmt_create    -- 建立時間
                         ,gmt_modified    -- 修改時間
                         ,user_id    -- 會員數字ID
                         ,nick    -- 會員NICK。會員暱稱
                         ,reg_fullname    -- 個人認證表示真實姓名，企業認證表示企業名稱
                         ,reg_mobile_phone    -- 註冊時綁定手機號碼
                         ,reg_email    -- 註冊填寫EMAIL（使用者可以修改）
                         ,reg_gender    -- 註冊填寫性別（F女，M男，不是這兩個就是未知的，說明性別保密）
                         ,CASE    WHEN reg_gender='F' THEN '女'
                                  WHEN reg_gender='M' THEN '男' 
                                  ELSE '未知' 
                          END AS reg_gender_name    -- 註冊填寫性別（F女，M男，不是這兩個就是未知的，說明性別保密）
                         ,reg_birthdate    -- 註冊填寫生日（使用者可以修改）
                         ,reg_address    -- 註冊填寫地址（使用者可以修改）
                         ,reg_nation_id    -- 註冊填寫國家ID（暫時為空白）
                         ,CASE    WHEN reg_nation_id='cn' THEN '中國' 
                                  ELSE '海外' 
                          END AS reg_nation_name
                         ,reg_prov_id    -- 註冊填寫省ID
                         ,reg_city_id    -- 註冊填寫城市ID
                         ,user_regip    -- 註冊IP
                         ,id_card_type    -- 會員認證證件類型 0：未知 1：身份證 2：企業營業執照號
                         ,CASE    WHEN id_card_type=0 THEN '未知'
                                  WHEN id_card_type=1 THEN '身份證'
                                  WHEN id_card_type=2 THEN '企業營業執照號' 
                                  ELSE '異常' 
                          END AS id_card_type_name
                         ,id_card_number    -- 個人認證表示社會安全號碼，企業認證表示企業的營業執照號，沒有認證不保證準確性
                         ,user_regdate    -- 註冊時間
                         ,user_active_type    -- 使用者啟用方式
                         ,CASE    WHEN user_active_type='email' THEN '郵箱'
                                  WHEN user_active_type='mobile_phone' THEN '手機' 
                                  ELSE '異常' 
                          END AS user_active_name    -- 使用者啟用方式
                         ,user_active_time    -- 啟用時間
                         ,cast(vip_level AS BIGINT) AS vip_level    -- VIP等級
                         ,CASE    WHEN vip_level>0 AND vip_level<=3 THEN '初級'
                                  WHEN vip_level>3 AND vip_level<=6 THEN '中級'
                                  WHEN vip_level>6 AND vip_level<=10 THEN '進階' 
                                  WHEN vip_level>10  THEN '特級' 
                          ELSE '異常'
                          END  AS vip_level_name
                         ,is_delete    -- 是否刪除
                 FROM    ods_mbr_user_info
             ) AS USER
     LEFT JOIN (
                   SELECT  id,pid,name,shortname,longitude,latitude,level,sort
                   FROM    ods_t_area
               ) AS area_prov
     ON      user.reg_prov_id = area_prov.id 
     LEFT JOIN    (
                 SELECT  id,pid,name,shortname,longitude,latitude,level,sort
                 FROM    ods_t_area
             ) AS area_city
     ON      user.reg_city_id = area_city.id
     ;

  3. 在SQL Cell右下角，選擇SQL Cell類型為EMR Spark SQL，選擇計算資源為openlake_serverless_spark。

     

  4. 單擊運行按鈕，等待運行完成，查看資料結果。

  StarRocks SQL

  1. 在DataWorks Notebook中單擊按鈕，建立SQL Cell，如下圖：

  2. 在SQL Cell中，輸入以下語句，完成dws_ec_trd_cate_commodity_gmv_kpi_fy 表的查詢。

     dws_ec_trd_cate_commodity_gmv_kpi_fy

     -- 說明：基於「交易下單事實明細表」和「商品基礎資訊維度資料表」查詢"財年_訂單支付成功金額、財年_成交金額完成度"等資料指標
     USE `openlake_win`.`default`;
     select   t1.cate_id, t1.cate_name, t1.commodity_id, t1.commodity_name, round(10*sum(t1.total_fee),4) as pay_ord_amt_fy, round((10*sum(t1.total_fee)/30000000),4) as kpi_gmv_rate_fy
     from    (
                 select  DATE_FORMAT(a.gmt_create,'yyyymmdd') as stat_date
                         ,a.sub_order_id, a.buyer_id, a.item_id, a.biz_type, a.pay_status, a.total_fee/100 as total_fee, b.cate_id, b.cate_name, b.commodity_id, b.commodity_name 
                 from    `openlake_win`.`default`.dwd_ec_trd_create_ord_di a
                 left outer join (
                                     select  distinct item_id, cate_id, cate_name, commodity_id, commodity_name, shop_id, shop_nick
                                     from    `openlake_win`.`default`.dim_ec_itm_item_info
                                 ) b
                 on      a.item_id = b.item_id
                 and     a.shop_id = b.shop_id
             ) t1
     where   t1.pay_status in ('2')
     and     t1.biz_type in ('2','3','4')
     group by   t1.cate_id, t1.cate_name, t1.commodity_id, t1.commodity_name
     ;

  3. 在SQL Cell右下角，選擇SQL Cell類型為StarRocks SQL，選擇計算資源為openlake_starrocks。

     

  4. 單擊運行按鈕，等待運行完成，查看資料結果。

  Hologres SQL

  1. 在DataWorks Notebook中單擊按鈕，建立SQL Cell。

  2. 在SQL Cell中，輸入以下語句，完成dws_ec_mbr_cnt_std 表的查詢。

     dws_ec_mbr_cnt_std

     -- 說明：「會員基礎資訊維度資料表」的資料進行計算轉換得到“存量會員數”等資料指標，獲得歷史截至當日_存量會員數_cube統計情況
     SELECT    IF(grouping(reg_prov_id) = 0, reg_prov_id, '-9999') as reg_prov_id
             , IF(grouping(reg_prov_name) = 0, reg_prov_name, '全部') as reg_prov_name
             , IF(grouping(reg_gender) = 0, reg_gender, '-9999') as reg_gender
             , IF(grouping(reg_gender_name) = 0, reg_gender_name, '全部') as reg_gender_name
             , IF(grouping(age_tag) = 0, age_tag, '-9999') as age_tag
             , IF(grouping(user_active_type) = 0, user_active_type, '-9999') as user_active_type
             , IF(grouping(user_active_name) = 0, user_active_name, '全部') as user_active_name
             , IF(grouping(vip_level) = 0, vip_level, '-9999') as vip_level
             , IF(grouping(vip_level_name) = 0, vip_level_name, '全部') as vip_level_name 
             , count(distinct user_id) as mbr_cnt
     from (
         select    reg_prov_id
                 , reg_prov_name
                 , reg_gender
                 , reg_gender_name
                 , case when cast(substr(reg_birthdate,1,4) as int)>=2010 and cast(substr(reg_birthdate,1,4) as int)<2020 then '10後' 
                         when cast(substr(reg_birthdate,1,4) as int)>=2000 and cast(substr(reg_birthdate,1,4) as int)<2010 then '00後' 
                         when cast(substr(reg_birthdate,1,4) as int)>=1990 and cast(substr(reg_birthdate,1,4) as int)<2000 then '90後' 
                         when cast(substr(reg_birthdate,1,4) as int)>=1980 and cast(substr(reg_birthdate,1,4) as int)<1990 then '80後' 
                         when cast(substr(reg_birthdate,1,4) as int)>=1970 and cast(substr(reg_birthdate,1,4) as int)<1980 then '70後' 
                         else '其他' 
                   end as age_tag
                 , user_active_type
                 , user_active_name
                 , vip_level
                 , vip_level_name 
                 , user_id
         from    openlake_win.default.dim_ec_mbr_user_info
     ) _main       
     group by 
     grouping sets(
         (reg_prov_id, reg_prov_name)
        ,(reg_gender, reg_gender_name)
        ,(age_tag)
        ,(user_active_type, user_active_name)
        ,(vip_level, vip_level_name)
        ,()
     );

  3. 在SQL Cell右下角，選擇SQL Cell類型為Hologres SQL，選擇計算資源為openlake_hologres。

     

  4. 單擊運行按鈕，等待運行完成，查看資料結果。

  MaxCompute SQL

  1. 在DataWorks Notebook中單擊按鈕，建立SQL Cell。

  2. 在SQL Cell中，輸入以下語句，完成dws_ec_mbr_cnt_std 表的查詢。

     dws_ec_mbr_cnt_std

     -- 說明：查詢輕度匯總層「歷史截至當日_存量會員數_cube統計表」
     set odps.task.major.version=flighting;
     set odps.namespace.schema=true;
     set odps.sql.allow.namespace.schema=true;
     set odps.service.mode=only;
     set odps.sql.unstructured.data.split.size=1;
     
     SELECT * 
     FROM openlake_win.default.dws_ec_mbr_cnt_std 
     LIMIT 200;

  3. 在SQL Cell右下角，選擇SQL Cell類型為MaxCompute SQL，選擇計算資源為openlake_maxcompute。

     

  4. 單擊運行按鈕，等待運行完成，查看資料結果。

• Notebook互動式資料

  1. 在DataWorks Notebook中單擊按鈕，建立Python Cell。

  2. 在Python Cell右上方，單擊按鈕，呼出DataWorks Copilot智能編程助手。

  3. 在DataWorks Copilot輸入框中，輸入以下需求，用於產生一個查詢會員年齡的ipywidgets互動組件。

     說明

     需求描述：使用Python，產生一個會員年齡的滑動條組件，取值範圍從1到100，預設值為20，即時監測組件取值的變化，並將值儲存到全域變數query_age中。

  4. 查看DataWorks Copilot產生的Python代碼，單擊接受按鈕。

     

  5. 單擊Python Cell的運行按鈕，等待運行完成，查看互動組件的產生（運行Copilot產生的程式碼，或預設代碼）；同時，能夠在互動組件中滑動選擇目標年齡。

     產生ipywidgets互動組件範例程式碼

     import ipywidgets as widgets
     
     # 建立滑動條組件
     slider = widgets.IntSlider(
         min = 1,
         max = 100,
         value = 20,
         description = '年齡：',
     )
     
     # 定義全域變數query_age
     query_age = None
     
     
     # 定義函數來處理滑動條值的變化
     def on_slider_change(change):
         global query_age
         query_age = change.new
     
     # 將函數綁定到滑動條的值變化事件上
     slider.observe(on_slider_change,names='value')
     
     # 顯示滑動條
     display(slider)

     

  6. 在DataWorks Notebook中單擊按鈕，建立SQL Cell。

  7. 在SQL Cell中，輸入以下查詢語句，包含Python中定義的會員年齡變數${query_age}。

     SELECT * FROM openlake_win.default.dim_ec_mbr_user_info
     WHERE CAST(id_age AS INT) >= ${query_age};

  8. 在SQL Cell右下角，選擇SQL Cell類型為Hologres SQL，選擇計算資源為openlake_hologres。

     

  9. 單擊運行按鈕，等待運行完成，查看資料結果。

  10. 在運行結果中，單擊按鈕，產生可視化圖表。

• Notebook模型開發與訓練

  1. 在DataWorks Notebook中單擊按鈕，建立SQL Cell。

  2. 在SQL Cell中，輸入以下語句，完成ods_trade_order表的查詢。

     SELECT * FROM openlake_win.default.ods_trade_order;

  3. 將SQL查詢結果寫入DataFrame變數中，單擊df位置，自訂DataFrame變數名稱（例如：df_ml）。

     

  4. 單擊SQL Cell的運行按鈕，等待運行完成，查看資料結果。

  5. 在DataWorks Notebook中單擊按鈕，建立Python Cell。

  6. 在Python Cell中，輸入以下語句，使用Pandas完成資料清洗和處理，並存入DataFrame的新變數df_ml_clean中。

     import pandas as pd
     
     def clean_data(df_ml):
         # 產生新的一列：預估訂單總額 = 商品單價 * 商品數量
         df_ml['predict_total_fee'] = df_ml['item_price'].astype(float).values * df_ml['buy_amount'].astype(float).values
         # 將列 'total_fee' 重新命名為 'actual_total_fee'
         df_ml = df_ml.rename(columns={'total_fee': 'actual_total_fee'})
         return df_ml
     
     df_ml_clean = clean_data(df_ml.copy())
     df_ml_clean.head()

  7. 單擊Python Cell的運行按鈕，等待運行完成，查看資料清理結果。

  8. 在DataWorks Notebook中單擊按鈕，再次建立Python Cell。

  9. 在Python Cell中，輸入以下語句，構建一個線性迴歸的機器學習模型，並進行訓練和測試。

     import pandas as pd  
     from sklearn.model_selection import train_test_split  
     from sklearn.linear_model import LinearRegression  
     from sklearn.metrics import mean_squared_error  
       
     # 擷取商品價格及總費用
     X = df_ml_clean[['predict_total_fee']].values  
     y = df_ml_clean['actual_total_fee'].astype(float).values  
     
     # 準備資料  
     X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.05, random_state=42)  
     
     # 建立並訓練模型  
     model = LinearRegression()  
     model.fit(X_train, y_train)  
       
     # 預測和評估  
     y_pred = model.predict(X_test)  
     for index, (x_t, y_pre, y_t) in enumerate(zip(X_test, y_pred, y_test)):
         print("[{:>2}] input: {:<10} prediction:{:<10} gt: {:<10}".format(str(index+1), f"{x_t[0]:.3f}", f"{y_pre:.3f}", f"{y_t:.3f}"))
     
     # 計算均方誤差MSE
     mse = mean_squared_error(y_test, y_pred)  
     print("均方誤差（MSE）:", mse)

  10. 單擊運行按鈕，等待運行完成，查看模型訓練的測試結果。