DataWorks:データの処理

ノードの内容を編集します。

ワークフローオーケストレーションページで、EMR Hive ノード dwd_log_info_di_emr にカーソルを合わせ、ノードの上に表示される Open Node をクリックします。表示されたダイアログボックスで Save and Open を選択し、EMR Hive ノード編集ページに移動します。次のサンプル SQL ステートメントを入力します。

-- ODS レイヤーテーブルの作成
CREATE TABLE IF NOT EXISTS dwd_log_info_di_emr (
  ip STRING COMMENT 'IP アドレス',
  uid STRING COMMENT 'ユーザー ID',
  `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 'アクセスタイプ：クローラー フィード ユーザー 不明'
)
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 -- カスタム UDF を使用して IP からリージョンを派生させます。
  , regexp_extract(request, '(^[^ ]+) .*') AS method -- 正規表現を使用してリクエストを 3 つのフィールドに分割します。
  , regexp_extract(request, '^[^ ]+ (.*) [^ ]+$') AS url
  , regexp_extract(request, '.* ([^ ]+$)') AS protocol 
  , regexp_extract(referer, '^[^/]+://([^/]+){1}') AS referer  -- 正規表現を使用してリファラーをクレンジングし、より正確な URL を取得します。
  , CASE
    WHEN lower(agent) RLIKE 'android' THEN 'android' -- エージェントからデバイスタイプとアクセスパターンを派生させます。
    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;

デバッグパラメーターを設定します。

EMR Hive ノード編集ページの右側で、Run Configuration をクリックし、次のパラメーターを設定します。これらのパラメーターは、ステップ 4 で Run Configuration を使用したテスト実行に使用されます。

設定が完了したら、ツールバーの アイコンをクリックしてタスクノードを保存します。

ノードの内容を編集します。

ワークフローオーケストレーションページで、EMR Hive ノード dws_user_info_all_di_emr にカーソルを合わせ、ノードの上に表示される Open Node をクリックします。表示されたダイアログボックスで Save and Open を選択し、EMR Hive ノード編集ページに移動します。次のサンプル SQL ステートメントを入力します。

-- DW レイヤーテーブルの作成
CREATE TABLE IF NOT EXISTS dws_user_info_all_di_emr (
  uid STRING COMMENT 'ユーザー ID',
  gender STRING COMMENT '性別',
  age_range STRING COMMENT '年齢層',
  zodiac STRING COMMENT '星座',
  region STRING COMMENT 'リージョン、IP から派生',
  device STRING COMMENT 'デバイスタイプ',
  identity STRING COMMENT 'アクセスタイプ：クローラー フィード ユーザー 不明',
  method STRING COMMENT 'HTTP リクエストタイプ',
  url STRING COMMENT 'URL',
  referer STRING COMMENT 'リファラー URL',
  `time` STRING COMMENT '時間 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;

デバッグパラメーターを設定します。

EMR Hive ノード編集ページの右側で、Run Configuration をクリックし、次のパラメーターを設定します。これらのパラメーターは、ステップ 4 で Run Configuration を使用したテスト実行に使用されます。

設定が完了したら、ツールバーの アイコンをクリックしてタスクノードを保存します。

ノードの内容を編集します。

ワークフローオーケストレーションページで、EMR Hive ノード ads_user_info_1d_emr にカーソルを合わせ、ノードの上に表示される Open Node をクリックします。表示されたダイアログボックスで Save and Open を選択し、EMR Hive ノード編集ページに移動します。次のサンプル SQL ステートメントを入力します。

-- RPT レイヤーテーブルの作成
CREATE TABLE IF NOT EXISTS ads_user_info_1d_emr (
  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 '星座'
)
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;

デバッグパラメーターを設定します。

EMR Hive ノード編集ページの右側で、Run Configuration をクリックし、次のパラメーターを設定します。これらのパラメーターは、ステップ 4 で Run Configuration を使用したテスト実行に使用されます。

設定が完了したら、ツールバーの アイコンをクリックしてタスクノードを保存します。