DataWorks:Process data

Edit the node content.

On the workflow orchestration page, hover over the EMR Hive node dwd_log_info_di_emr and click Open Node that appears above the node. In the dialog that appears, select Save and Open to go to the EMR Hive node editing page. Enter the following sample SQL statements.

--Create ODS layer table
CREATE TABLE IF NOT EXISTS dwd_log_info_di_emr (
  ip STRING COMMENT 'IP address',
  uid STRING COMMENT 'User ID',
  `time` STRING COMMENT 'Time yyyymmddhh:mi:ss',
  status STRING COMMENT 'Server response status code',
  bytes STRING COMMENT 'Bytes returned to client',
  region STRING COMMENT 'Region, derived from IP',
  method STRING COMMENT 'HTTP request type',
  url STRING COMMENT 'URL',
  protocol STRING COMMENT 'HTTP protocol version',
  referer STRING COMMENT 'Referrer URL',
  device STRING COMMENT 'Device type ',
  identity STRING COMMENT 'Access type: crawler feed user unknown'
)
PARTITIONED BY (
  dt STRING
);

ALTER TABLE dwd_log_info_di_emr ADD IF NOT EXISTS PARTITION (dt='${bizdate}');

set hive.vectorized.execution.enabled = false;
INSERT OVERWRITE TABLE dwd_log_info_di_emr PARTITION (dt='${bizdate}')
SELECT ip
  , uid
  , tm
  , status
  , bytes 
  , getregion(ip) AS region --Use custom UDF to derive region from IP. 
  , regexp_extract(request, '(^[^ ]+) .*') AS method --Split request into three fields using regex.
  , regexp_extract(request, '^[^ ]+ (.*) [^ ]+$') AS url
  , regexp_extract(request, '.* ([^ ]+$)') AS protocol 
  , regexp_extract(referer, '^[^/]+://([^/]+){1}') AS referer  --Clean referer using regex to get a more precise URL.
  , CASE
    WHEN lower(agent) RLIKE 'android' THEN 'android' --Derive device type and access pattern from agent.
    WHEN lower(agent) RLIKE 'iphone' THEN 'iphone'
    WHEN lower(agent) RLIKE 'ipad' THEN 'ipad'
    WHEN lower(agent) RLIKE 'macintosh' THEN 'macintosh'
    WHEN lower(agent) RLIKE 'windows phone' THEN 'windows_phone'
    WHEN lower(agent) RLIKE 'windows' THEN 'windows_pc'
    ELSE 'unknown'
  END AS device
  , CASE
    WHEN lower(agent) RLIKE '(bot|spider|crawler|slurp)' THEN 'crawler'
    WHEN lower(agent) RLIKE 'feed'
    OR regexp_extract(request, '^[^ ]+ (.*) [^ ]+$') RLIKE 'feed' THEN 'feed'
    WHEN lower(agent) NOT RLIKE '(bot|spider|crawler|feed|slurp)'
    AND agent RLIKE '^[Mozilla|Opera]'
    AND regexp_extract(request, '^[^ ]+ (.*) [^ ]+$') NOT RLIKE 'feed' THEN 'user'
    ELSE 'unknown'
  END AS identity
  FROM (
    SELECT SPLIT(col, '##@@')[0] AS ip
    , SPLIT(col, '##@@')[1] AS uid
    , SPLIT(col, '##@@')[2] AS tm
    , SPLIT(col, '##@@')[3] AS request
    , SPLIT(col, '##@@')[4] AS status
    , SPLIT(col, '##@@')[5] AS bytes
    , SPLIT(col, '##@@')[6] AS referer
    , SPLIT(col, '##@@')[7] AS agent
    FROM ods_raw_log_d_emr
  WHERE dt = '${bizdate}'
) a;

Configure the debug parameters.

On the right side of the EMR Hive node editing page, click Run Configuration and configure the following parameters. These parameters are used for test runs in Step 4 by using the Run Configuration settings.

After the configuration is complete, click the icon in the toolbar to save the task node.

Edit the node content.

On the workflow orchestration page, hover over the EMR Hive node dws_user_info_all_di_emr and click Open Node that appears above the node. In the dialog that appears, select Save and Open to go to the EMR Hive node editing page. Enter the following sample SQL statements.

--Create DW layer table
CREATE TABLE IF NOT EXISTS dws_user_info_all_di_emr (
  uid STRING COMMENT 'User ID',
  gender STRING COMMENT 'Gender',
  age_range STRING COMMENT 'Age range',
  zodiac STRING COMMENT 'Zodiac sign',
  region STRING COMMENT 'Region, derived from IP',
  device STRING COMMENT 'Device type ',
  identity STRING COMMENT 'Access type: crawler feed user unknown',
  method STRING COMMENT 'HTTP request type',
  url STRING COMMENT 'URL',
  referer STRING COMMENT 'Referrer URL',
  `time` STRING COMMENT 'Time yyyymmddhh:mi:ss'
)
PARTITIONED BY (
  dt STRING
);

ALTER TABLE dws_user_info_all_di_emr ADD IF NOT EXISTS PARTITION (dt='${bizdate}');

INSERT OVERWRITE TABLE dws_user_info_all_di_emr PARTITION (dt='${bizdate}')
SELECT COALESCE(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`
FROM (
  SELECT *
  FROM dwd_log_info_di_emr
  WHERE dt = '${bizdate}'
) a
LEFT OUTER JOIN (
  SELECT *
  FROM ods_user_info_d_emr
  WHERE dt = '${bizdate}'
) b
ON a.uid = b.uid;

Configure the debug parameters.

On the right side of the EMR Hive node editing page, click Run Configuration and configure the following parameters. These parameters are used for test runs in Step 4 by using the Run Configuration settings.

After the configuration is complete, click the icon in the toolbar to save the task node.

Edit the node content.

On the workflow orchestration page, hover over the EMR Hive node ads_user_info_1d_emr and click Open Node that appears above the node. In the dialog that appears, select Save and Open to go to the EMR Hive node editing page. Enter the following sample SQL statements.

--Create RPT layer table
CREATE TABLE IF NOT EXISTS ads_user_info_1d_emr (
  uid STRING COMMENT 'User ID',
  region STRING COMMENT 'Region, derived from IP',
  device STRING COMMENT 'Device type ',
  pv BIGINT COMMENT 'PV',
  gender STRING COMMENT 'Gender',
  age_range STRING COMMENT 'Age range',
  zodiac STRING COMMENT 'Zodiac sign'
)
PARTITIONED BY (
  dt STRING
);

ALTER TABLE ads_user_info_1d_emr ADD IF NOT EXISTS PARTITION (dt='${bizdate}');

INSERT OVERWRITE TABLE ads_user_info_1d_emr PARTITION (dt='${bizdate}')
SELECT uid
  , MAX(region)
  , MAX(device)
  , COUNT(0) AS pv
  , MAX(gender)
  , MAX(age_range)
  , MAX(zodiac)
FROM dws_user_info_all_di_emr
WHERE dt = '${bizdate}'
GROUP BY uid;

Configure the debug parameters.

On the right side of the EMR Hive node editing page, click Run Configuration and configure the following parameters. These parameters are used for test runs in Step 4 by using the Run Configuration settings.

After the configuration is complete, click the icon in the toolbar to save the task node.