DataWorks:EMR Hive ノードを使用したデータ処理

このノードは、3つの操作を使用して未加工のログデータをクレンジングします：getregion UDF は各 IP アドレスから地理的リージョンを導出し、正規表現はリクエストフィールドから HTTP メソッド、URL、プロトコルを抽出し、ユーザーエージェント文字列に対する CASE-WHEN 式はデバイスタイプとアクセス ID を分類します。ソースレコードは ods_raw_log_d_emr から ##@@ で区切られた文字列として到着し、処理前に個々のフィールドに分割されます。

1. ワークフローキャンバスで、dwd_log_info_di_emr ノードにカーソルを合わせ、[ノードを開く] をクリックします。[リマインダー] ダイアログボックスで、[保存して開く] をクリックします。

2. コードエディタに、次の 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  -- 正規表現を使用して HTTP リファラーをクレンジングし、より正確な URL を取得します。
     , CASE
       WHEN lower(agent) RLIKE 'android' THEN 'android' -- 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;

3. 右側のナビゲーションウィンドウで [デバッグ設定] をクリックし、次のパラメーターを設定します：

   パラメーター	値
   計算リソース	ご利用のワークスペースに関連付けられた EMR 計算リソースを選択します。詳細については、「環境の準備」をご参照ください。
   リソースグループ	環境準備時に作成したサーバーレスリソースグループを選択します。
   スクリプトパラメーター	空白のままにします。ワークフローをステップ 4 で実行すると、${bizdate} 変数は定数値に置き換えられます。

4. 上部のツールバーにある アイコンをクリックしてノードを保存します。

このノードは、クレンジングされたログデータをユーザープロファイルの属性でエンリッチ化します。dwd_log_info_di_emr (ログデータ) と ods_user_info_d_emr (基本ユーザーデータ) の間でユーザー ID (uid) をキーに LEFT OUTER JOIN を実行し、性別、年齢層、星座などのデモグラフィックフィールドを各ログレコードに追加します。ログテーブルに一致するユーザーエントリがないレコードも保持されます。

1. ワークフローキャンバスで、dws_user_info_all_di_emr ノードにカーソルを合わせ、[ノードを開く] をクリックします。[リマインダー] ダイアログボックスで、[保存して開く] をクリックします。

2. コードエディタに、次の 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;

3. 右側のナビゲーションウィンドウで [デバッグ設定] をクリックし、前のノードと同じパラメーターを設定します：

   パラメーター	値
   コンピューティングリソース	ご利用のワークスペースに関連付けられた EMR コンピューティングリソースを選択します。 詳細については、「環境の準備」をご参照ください。
   リソースグループ	環境の準備中に作成されたサーバーレスリソースグループを選択します。
   スクリプトパラメーター	空のままにします。

4. アイコンをクリックしてノードを保存します。

このノードは、ユーザーごとのすべてのログアクティビティを集計して、最終的なユーザープロファイルを生成します。行を uid でグループ化し、MAX を適用してリージョン、デバイス、性別、年齢層、星座を統合し (すべてのセッションで最も優勢な値を取得)、COUNT を使用してユーザーごとの合計ページビュー (PV) 数を計算します。

1. ワークフローキャンバスで、ads_user_info_1d_emr ノードにカーソルを合わせ、[ノードを開く] をクリックします。[リマインダー] ダイアログボックスで、[保存して開く] をクリックします。

2. コードエディタに、次の SQL を入力します：

   -- ADS レイヤーにテーブルを作成します。
   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;

3. 右側のナビゲーションウィンドウで [デバッグ設定] をクリックし、前のノードと同じパラメーターを設定します：

   パラメーター	値
   コンピューティング リソース	ワークスペースに関連付けられている EMR 計算リソースを選択します。「環境の準備」をご参照ください。
   リソースグループ	環境準備時に作成したサーバーレスリソースグループを選択します。
   スクリプトパラメータ	空白のままにします。

4. アイコンをクリックしてノードを保存します。