DataWorks:データ処理

dwd_log_info_di_odps テーブル

dwd_log_info_di_odps テーブルをダブルクリックします。表示された編集ページで、DDL パターン をクリックし、次のステートメントを入力します。

CREATE TABLE IF NOT EXISTS dwd_log_info_di_odps (
 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 'アクセス種別：crawler、feed、user、または unknown'
)
PARTITIONED BY (
 dt STRING
)
LIFECYCLE 14;

dws_user_info_all_1d_odps テーブル

dws_user_info_all_di_odps テーブルをダブルクリックします。 表示された編集ページで、DDL パターン をクリックし、次のステートメントを入力します。

CREATE TABLE IF NOT EXISTS dws_user_info_all_1d_odps (
 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 形式の時刻'
)
PARTITIONED BY (
 dt STRING
)
LIFECYCLE 14;

ads_user_info_1d_odps テーブル

ads_user_info_1d_odps テーブルをダブルクリックします。表示された編集ページで、DDL パターン をクリックし、次のステートメントを入力します。

CREATE TABLE IF NOT EXISTS ads_user_info_1d_odps (
 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
)
LIFECYCLE 14;    

dwd_log_info_di_odps ノード

ワークフローキャンバスで、dwd_log_info_di_odps ノードをダブルクリックします。作成した関数を使用して、祖先テーブル ods_raw_log_d_odps のフィールドを処理し、出力を dwd_log_info_di_odps テーブルに書き込む SQL コードを記述します。詳細については、「付録: データ処理の例」をご参照ください。

1. コードを編集します。

   -- この SQL ステートメントは、getregion 関数を使用して生ログデータ内の IP アドレスを解析します。また、正規表現やその他の手法を用いて生データを複数のフィールドに分割し、dwd_log_info_di_odps テーブルに書き込みます。
   --      このチュートリアルでは、IP アドレスをリージョンに変換する getregion 関数を提供します。
   -- 注意事項：
   --     1. DataWorks ノードで関数を使用する前に、必要なリソースを DataWorks にアップロードし、UI 上でそれらのリソースを使用して関数を登録する必要があります。
   --        このチュートリアルで getregion 関数を登録するために使用するリソースは ip2region.jar です。
   --     2. DataWorks は、スケジューリングシナリオでターゲットテーブルの対応するビジネスパーティションに日次増分データを書き込むためのスケジューリングパラメーターを提供します。
   --        実際の開発シナリオでは、${variable_name} 形式でコード変数を定義できます。スケジューリング設定ページで、これらの変数にスケジューリングパラメーターを割り当てて、スケジュール実行時に動的なパラメーター渡しを有効にできます。
   INSERT OVERWRITE TABLE dwd_log_info_di_odps PARTITION (dt='${bizdate}')
   SELECT ip 
     , uid
     , time
     , status
     , bytes 
     , getregion(ip) AS region -- カスタム UDF を使用して IP アドレスからリージョンを取得します。
     , regexp_substr(request, '(^[^ ]+ )') AS method -- 正規表現を使用してリクエストを 3 つのフィールドに分割します。
     , regexp_extract(request, '^[^ ]+ (.*) [^ ]+$') AS url
     , regexp_substr(request, '([^ ]+$)') AS protocol 
     , regexp_extract(referer, '^[^/]+://([^/]+){1}') AS referer -- 正規表現を使用してリファラーを解析し、より正確な URL を取得します。
     , CASE
       WHEN TOLOWER(agent) RLIKE 'android' THEN 'android' -- エージェントからデバイス情報とアクセス種別を取得します。
       WHEN TOLOWER(agent) RLIKE 'iphone' THEN 'iphone'
       WHEN TOLOWER(agent) RLIKE 'ipad' THEN 'ipad'
       WHEN TOLOWER(agent) RLIKE 'macintosh' THEN 'macintosh'
       WHEN TOLOWER(agent) RLIKE 'windows phone' THEN 'windows_phone'
       WHEN TOLOWER(agent) RLIKE 'windows' THEN 'windows_pc'
       ELSE 'unknown'
     END AS device
     , CASE
       WHEN TOLOWER(agent) RLIKE '(bot|spider|crawler|slurp)' THEN 'crawler'
       WHEN TOLOWER(agent) RLIKE 'feed'
       OR regexp_extract(request, '^[^ ]+ (.*) [^ ]+$') RLIKE 'feed' THEN 'feed'
       WHEN TOLOWER(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 time
       , 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_odps  
     WHERE dt ='${bizdate}'
   ) a;

2. スケジューリングプロパティを設定します。

   設定ページで、右側の [スケジューリングプロパティ] をクリックすると スケジューリング設定 パネルが開き、スケジューリングとノード情報を設定できます。 詳細については、「ノードのスケジューリングプロパティ」をご参照ください。 次のパラメーターを設定できます:

   パラメーター	設定	図解
   [スケジューリングパラメータ]	スケジューリングパラメータ セクションで、以下のパラメーターを設定します。 [パラメーター名]: bizdate [パラメーター値]： $[yyyymmdd-1]	スケジューリングパラメーターペインには、bizdate パラメーターの値が $[yyyymmdd-1] で、ソースが [コードから解析] であることが表示されます。下部には [スケジューリングパラメーターのプレビュー] ボタンがあります。
   [時間プロパティ]	スケジューリング周期： Day。 スケジュール時刻： 00:30。 再実行プロパティ：前回の成功/失敗に関係なく、タスクの再実行を許可します。 その他のパラメータはデフォルト値を使用します。 説明 ノードは毎日 00:30 以降にのみ実行されます。	「時間プロパティ」パネルでは、インスタンス生成方法は [T+1 (翌日生成)]、スケジューリングタイプは [通常スケジューリング]、スケジューリング周期は [日次]、スケジュール時刻は [00:30]、再実行プロパティは [成功時または失敗時に再実行可能]、有効日は [永続的に有効] です。
   [リソースプロパティ]	環境の準備ステップで作成したサーバーレスリソースグループを選択します。	リソースプロパティペインでは、スケジューリングリソースグループは [User_portraits] に設定されています (お使いのサーバーレスリソースグループの実際の名前を使用してください)。
   [スケジュール依存]	[コード自動解析] 機能は、ods_raw_log_d_odps テーブルを生成する ods_raw_log_d_odps ノードを、現在のノード dwd_log_info_di_odps の上流の依存関係として自動的に設定します。dwd_log_info_di_odps テーブルをノードの出力として設定すると、このテーブルをクエリする下流のジョブが現在のノードに自動的に依存できるようになります。	「依存関係」ペインで、[コミット前に依存関係を自動解析] が有効になっています。祖先ノードは、[コードから解析] を使用して [ods_raw_log_d_odps] として自動的に設定されます。現在のノードの出力は、システムのデフォルト、手動追加、またはコード解析から取得できます。

   説明

   上位ノードの依存関係: [コードから解析] をクリックします。システムは、コードからテーブル名を解析し、対応する上位ノードの出力名を Production_MaxCompute_Project_Name.TableName 形式で先祖ノードの出力名として使用します。

   現在のノード出力: [コードから解析] をクリックします。 システムはコードからテーブル名を解析し、現在のノードの出力名を Production_MaxCompute_Project_Name.TableName という形式で生成します。 この出力は、ダウンストリームノードで利用可能になります。

3. その他の必要な設定を行い、ノードエディターのツールバーにある [Save] アイコンをクリックします。

dws_user_info_all_di_odps ノードの設定

ワークフローキャンバスで、dws_user_info_all_di_odps ノードをダブルクリックします。 祖先テーブル dwd_log_info_di_odps と ods_user_info_d_odps をマージし、その結果を dws_user_info_all_di_odps テーブルに書き込む SQL コードを記述します。

1. コードを編集します。

   -- この SQL ステートメントは、dwd_log_info_di_odps からの処理済みログデータと ods_user_info_d_odps からの基本ユーザー情報を集約し、その結果を dws_user_info_all_1d_odps テーブルに書き込みます。
   -- 注意：DataWorks は、スケジューリングシナリオでターゲットテーブルの対応するビジネスパーティションに日次増分データを書き込むためのスケジューリングパラメーターを提供します。
   --      実際の開発シナリオでは、${variable_name} 形式でコード変数を定義できます。スケジューリング設定ページで、これらの変数にスケジューリングパラメーターを割り当てて、スケジュール実行時に動的なパラメーター渡しを有効にできます。
   INSERT OVERWRITE TABLE dws_user_info_all_1d_odps  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_odps  
     WHERE dt = '${bizdate}'
   ) a
   LEFT OUTER JOIN (
     SELECT *
     FROM ods_user_info_d_odps 
     WHERE dt = '${bizdate}'
   ) b
   ON a.uid = b.uid;

2. スケジューリングプロパティを設定します。

   設定ページで、右側の[スケジューリングプロパティ]をクリックしてスケジューリング設定パネルを開き、スケジューリングとノード情報を設定します。 詳細については、「ノードスケジューリングプロパティ」をご参照ください。 設定項目は次のとおりです。

   パラメーター	設定	図解
   [スケジューリングパラメータ]	スケジューリングパラメータ セクションで、次のパラメーターを設定します。 [パラメーター名]: bizdate [パラメーター値]： $[yyyymmdd-1]	スケジューリングパラメーターペインには、[bizdate] パラメーターの値は [$[yyyymmdd-1]]、ソースは [コードから解析] と表示されます。下部には [スケジューリングパラメーターのプレビュー] ボタンがあります。
   [時間プロパティ]	スケジューリング周期： Day。 スケジュール時刻： 00:30。 再実行プロパティ：前回の成功/失敗に関係なく、タスクの再実行を許可します。 その他のパラメータはデフォルト値を使用します。 説明 ノードは毎日 00:30 以降にのみ実行されます。	時間プロパティ ペインで、[インスタンス生成] は [翌日 (T+1) に生成] に設定され、スケジューリングタイプは [通常]、スケジューリング周期は [日]、スケジュール時刻は [00:30] に設定され、再実行ポリシーは [成功または失敗時に再実行]、有効日は無期限です。
   [リソースプロパティ]	環境の準備ステップで作成したサーバーレスリソースグループを選択します。	「リソースプロパティ」ペインでは、スケジューリングリソースグループは User_portraits に設定されています (実際のサーバーレスリソースグループ名を使用します)。
   [スケジュール依存]	システムはコードを自動的に解析し、dwd_log_info_di_odps と ods_user_info_d_odps テーブルを出力するノード dwd_log_info_di_odps と ods_user_info_d_odps を、現在のノード dws_user_info_all_di_odps の上流依存関係として設定します。また、システムはノードの出力テーブル dws_user_info_all_di_odps をノード出力として登録します。これにより、このテーブルに対する下流クエリで現在のノードへの依存関係が自動的に確立されます。	設定は dwd_log_info_di_odps ノードと同じです。祖先ノードは、コードの解析により ods_user_info_d_odps および dwd_log_info_di_odps として自動的に設定されます。現在のノードの出力には dws_user_info_all_di_odps が含まれます。

   説明

   上位ノードの依存関係: [コードから解析] をクリックします。システムは、コードからテーブル名を解析し、対応する上位ノードの出力名を Production_MaxCompute_Project_Name.TableName 形式で先祖ノードの出力名として使用します。

   現在のノード出力: [コードから解析] をクリックします。 システムはコードからテーブル名を解析し、現在のノードの出力名を Production_MaxCompute_Project_Name.TableName という形式で生成します。 この出力は、ダウンストリームノードで利用可能になります。

3. その他の必要な設定を行い、ノードエディターのツールバーにある [Save] アイコンをクリックします。

ads_user_info_1d_odps ノードの設定

ワークフローキャンバスで、ads_user_info_1d_odps ノードをダブルクリックします。ソーステーブル dws_user_info_all_di_odps を処理し、結果を ads_user_info_1d_odps テーブルに書き込む SQL コードを記述します。

1. コードを編集します。

   -- この SQL ステートメントは、ユーザーアクセス情報の dws_user_info_all_1d_odps ワイドテーブルをさらに処理して基本的なユーザープロファイルデータを生成し、その結果を ads_user_info_1d_odps テーブルに書き込みます。
   -- 注意：DataWorks は、スケジューリングシナリオでターゲットテーブルの対応するビジネスパーティションに日次増分データを書き込むためのスケジューリングパラメーターを提供します。
   --      実際の開発シナリオでは、${variable_name} 形式でコード変数を定義できます。スケジューリング設定ページで、これらの変数にスケジューリングパラメーターを割り当てて、スケジュール実行時に動的なパラメーター渡しを有効にできます。
   INSERT OVERWRITE TABLE ads_user_info_1d_odps  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_1d_odps 
   WHERE dt = '${bizdate}'
   GROUP BY uid; 

2. スケジューリングプロパティを設定します。

   設定ページで、右側の[スケジューリングプロパティ]をクリックするとスケジューリング設定パネルが開き、スケジューリングとノード情報を設定できます。 詳細については、「ノードのスケジューリングプロパティ」をご参照ください。 設定は次のとおりです。

   パラメーター	設定	図解
   [スケジューリングパラメータ]	スケジューリングパラメータ セクションで、次のパラメーターを設定します。 [パラメーター名]：bizdate [パラメーター値]: $[yyyymmdd-1]	スケジューリングパラメーターペインには、bizdate パラメーターの値が $[yyyymmdd-1]、ソースが [コードから解析] と表示されます。下部には [スケジューリングパラメーターのプレビュー] ボタンがあります。
   [時間プロパティ]	スケジューリング周期： Day。 スケジュール時刻： 00:30。 再実行プロパティ：前回の成功/失敗に関係なく、タスクの再実行を許可します。 その他のパラメータはデフォルト値を使用します。 説明 ノードは毎日 00:30 以降にのみ実行されます。	「時間プロパティ」ペインでは、[インスタンス生成] は [翌日 (T+1) に生成]、スケジューリングタイプは [通常]、スケジューリング周期は [日]、スケジュール時刻は [00:30]、再実行ポリシーは [成功または失敗時に再実行]、有効日は「無期限」です。
   [リソースプロパティ]	環境の準備ステップで作成したサーバーレスリソースグループを選択します。	「リソースプロパティ」ペインで、スケジューリングリソースグループは [User_portraits] に設定されています (お使いのサーバーレスリソースグループの実際の名前を使用してください)。
   [スケジュール依存]	[コードの自動解析] 機能は、データリネージに基づいてノードの依存関係を自動的に設定します。dws_user_info_all_1d_odps テーブルを生成する dws_user_info_all_1d_odps ノードが、現在の ads_user_info_1d_odps ノードの上流の依存関係として設定されます。ノードの出力は ads_user_info_1d テーブルに設定され、このテーブルへの下流のクエリが現在のノードへの依存関係を自動的に作成するようになります。	設定は以前のノードと同じです。 祖先ノードは、コードの解析により dws_user_info_all_di_odps として自動的に設定されます。 現在のノードの出力には ads_user_info_1d_odps が含まれます。

   説明

   上位ノードの依存関係: [コードから解析] をクリックします。システムは、コードからテーブル名を解析し、対応する上位ノードの出力名を Production_MaxCompute_Project_Name.TableName 形式で先祖ノードの出力名として使用します。

   現在のノード出力: [コードから解析] をクリックします。 システムはコードからテーブル名を解析し、現在のノードの出力名を Production_MaxCompute_Project_Name.TableName という形式で生成します。 この出力は、ダウンストリームノードで利用可能になります。

3. その他の必要な設定を行い、ノードエディターのツールバーにある [Save] アイコンをクリックします。