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。

     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;

  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代码，单击接受按钮。

     import ipywidgets as widgets
     # 创建滑动条组件
     slider = widgets.IntSlider(
         min=1,
         max=100,
         value=20,
     )

  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. 单击运行按钮，等待运行完成，查看模型训练的测试结果。