DataWorks:Proses data

1. Edit kode

Klik ganda node dwd_log_info_di_emr untuk membuka tab konfigurasinya. Di editor kode, masukkan pernyataan SQL berikut.

-- Create an ODS-layer table.
CREATE TABLE IF NOT EXISTS dwd_log_info_di_emr (
  ip STRING COMMENT 'The IP address.',
  uid STRING COMMENT 'The user ID.',
  `time` STRING COMMENT 'The time in the yyyymmddhh:mi:ss format.',
  status STRING COMMENT 'The status code returned by the server.',
  bytes STRING COMMENT 'The number of bytes returned to the client.',
  region STRING COMMENT 'The region, which is obtained based on the IP address.',
  method STRING COMMENT 'The HTTP request type.',
  url STRING COMMENT 'The URL.',
  protocol STRING COMMENT 'The HTTP protocol version.',
  referer STRING COMMENT 'The source URL.',
  device STRING COMMENT 'The client type.',
  identity STRING COMMENT 'The access type. Valid values: crawler, feed, user, and 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 the UDF to obtain the region from the IP address. 
  , regexp_extract(request, '(^[^ ]+) .*') AS method -- Use a regular expression to split the request into three fields.
  , regexp_extract(request, '^[^ ]+ (.*) [^ ]+$') AS url
  , regexp_extract(request, '.* ([^ ]+$)') AS protocol 
  , regexp_extract(referer, '^[^/]+://([^/]+){1}') AS referer  -- Use a regular expression to clean the referer to obtain a more accurate URL.
  , CASE
    WHEN lower(agent) RLIKE 'android' THEN 'android' -- Obtain client and access type information from the 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;

2. Konfigurasikan properti penjadwalan

Konfigurasi ini menerapkan skenario penjadwalan harian. Pada pukul 00.30, setelah node hulu ods_raw_log_d_emr selesai menyinkronkan data dari file user_log.txt di OSS ke tabel ods_raw_log_d_emr di EMR, node tersebut memicu node dwd_log_info_di_emr. Node ini memproses data dari tabel ods_raw_log_d_emr dan menulis hasilnya ke partisi yang sesuai di tabel dwd_log_info_di_emr.

3. Simpan konfigurasi

Konfigurasikan parameter lain yang diperlukan sesuai kebutuhan. Setelah menyelesaikan konfigurasi, klik ikon di bilah alat untuk menyimpan konfigurasi.

1. Edit kode

Klik ganda node dws_user_info_all_di_emr untuk membuka tab konfigurasinya. Di editor kode, masukkan pernyataan SQL berikut.

-- Create a DW-layer table.
CREATE TABLE IF NOT EXISTS dws_user_info_all_di_emr (
  uid STRING COMMENT 'The user ID.',
  gender STRING COMMENT 'The gender.',
  age_range STRING COMMENT 'The age range.',
  zodiac STRING COMMENT 'The zodiac sign.',
  region STRING COMMENT 'The region, which is obtained based on the IP address.',
  device STRING COMMENT 'The client type.',
  identity STRING COMMENT 'The access type. Valid values: crawler, feed, user, and unknown.',
  method STRING COMMENT 'The HTTP request type.',
  url STRING COMMENT 'The URL.',
  referer STRING COMMENT 'The source URL.',
  `time` STRING COMMENT 'The time in the yyyymmddhh:mi:ss format.'
)
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;

2. Konfigurasikan properti penjadwalan

Konfigurasi ini menerapkan skenario penjadwalan harian. Pada pukul 00.30, setelah tugas hulu ods_user_info_d_emr dan dwd_log_info_di_emr selesai, node dws_user_info_all_di_emr dipicu. Node ini menggabungkan data dari tabel ods_user_info_d_emr dan dwd_log_info_di_emr lalu menulis hasilnya ke tabel dws_user_info_all_di_emr.

3. Simpan konfigurasi

Konfigurasikan parameter lain yang diperlukan sesuai kebutuhan. Setelah menyelesaikan konfigurasi, klik ikon di bilah alat untuk menyimpan konfigurasi.

1. Edit kode

Klik ganda node ads_user_info_1d_emr untuk membuka tab konfigurasinya. Di editor kode, masukkan pernyataan SQL berikut.

-- Create an RPT-layer table.
CREATE TABLE IF NOT EXISTS ads_user_info_1d_emr (
  uid STRING COMMENT 'The user ID.',
  region STRING COMMENT 'The region, which is obtained based on the IP address.',
  device STRING COMMENT 'The client type.',
  pv BIGINT COMMENT 'The number of page views.',
  gender STRING COMMENT 'The gender.',
  age_range STRING COMMENT 'The age range.',
  zodiac STRING COMMENT 'The 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;

2. Konfigurasikan properti penjadwalan

Setelah node hulu dws_user_info_all_di_emr selesai menggabungkan tabel ods_user_info_d_emr dan dwd_log_info_di_emr, node tersebut memicu node ads_user_info_1d_emr untuk memproses data lebih lanjut dan menghasilkan dataset akhir.

3. Simpan konfigurasi

Konfigurasikan parameter lain yang diperlukan sesuai kebutuhan. Setelah menyelesaikan konfigurasi, klik ikon di bilah alat untuk menyimpan konfigurasi.