加工数据

1. 配置代码

双击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;

2. 配置调度

单击dwd_log_info_di_starrocks节点编辑页右侧调度配置。配置详情如下：

3. 保存配置

本案例其他必填配置项，您可按需自行配置，配置完成后，在节点代码编辑页面，单击工具栏中的按钮，保存当前配置。

编辑代码

双击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}';

配置调度

单击dws_user_info_all_di_starrocks节点编辑页面右侧的调度配置。配置详情如下：

3. 保存配置

本案例其他必填配置项，您可按需自行配置，配置完成后，在节点代码编辑页面，单击工具栏中的按钮，保存当前配置。

1. 编辑代码

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}';

2. 配置调度

单击ads_user_info_1d_starrocks节点编辑页右侧的调度配置。配置详情如下：

3. 保存配置

本案例其他必填配置项，您可按需自行配置，配置完成后，在节点代码编辑页面，单击工具栏中的按钮，保存当前配置。