使用Data Lake Analytics從OSS清洗資料到AnalyticDB

阿里云云棲社群發表於2019-04-12

前提

  • 必須是同一阿里雲region的Data Lake Analytics（DLA）到AnalyticDB的才能進行清洗操作；
  • 開通並初始化了該region的DLA服務；
  • 開通併購買了AnalyticDB的例項，例項規模和資料清洗速度強相關，與AnalyticDB的例項資源規模基本成線性比例關係。

整體執行流程示意圖：

使用Data Lake Analytics從OSS清洗資料到AnalyticDB

步驟 1：在AnalyticDB中為DLA開通一個VPC訪問點

使用Data Lake Analytics從OSS清洗資料到AnalyticDB

DLA在上海region的VPC引數資訊：

  • 可用區：cn-shanghai-d
  • VPC id: vpc-uf6wxkgst74es59wqareb
  • VSwitch id: vsw-uf6m7k4fcq3pgd0yjfdnm
DLA Region可用區VPC idVSwitch id
華東1（杭州）cn-hangzhou-gvpc-bp1g66t4f0onrvbht2et5vsw-bp1nh5ri8di2q7tkof474
華東2（上海）cn-shanghai-dvpc-uf6wxkgst74es59wqarebvsw-uf6m7k4fcq3pgd0yjfdnm
華北2（北京）cn-beijing-gvpc-2zeawsrpzbelyjko7i0irvsw-2zea8ct4hy4hwsrcpd52d
華南1（深圳）cn-shenzhen-avpc-wz9622zx341dy24ozifn3vsw-wz91ov6gj2i4u2kenpe42
華北3（張家口）cn-zhangjiakou-avpc-8vbpi1t7c0devxwfe19snvsw-8vbjl32xkft0ewggef6g9
新加坡ap-southeast-avpc-t4n3sczhu5efvwo1gsupfvsw-t4npcrmzzk64r13e3nhhm
英國（倫敦）eu-west-1avpc-d7ovzdful8490upm8b413vsw-d7opmgixr2h34r1975s8a

在AnalyticDB中為DLA建立VPC的專有網路，注意，要使用MySQL命令列連線AnalyticDB的經典網路連結，執行：

alter database txk_cldsj set zone_id='xxx' vpc_id='xxx' vswitch_id='xxx';
複製程式碼

其中，“zone_id”、“vpc_id”和“vswitch_id”分別填同region的DLA對應的VPC id和VSwitch id，見上表。

命令執行成功後，重新整理DMS for AnalyticDB控制檯頁面，應該能看到一個VPC的URL。

步驟 2：在AnalyticDB中建立好目標的實時表

使用Data Lake Analytics從OSS清洗資料到AnalyticDB

具體AnalyticDB的建表文件請參考：help.aliyun.com/document_de…

-- 例如：

-- 目標表為實時維度表：
CREATE DIMENSION TABLE etl_ads_db.etl_ads_dimension_table (
  col1 INT, 
  col2 STRING, 
  col3 INT, 
  col4 STRING,
  primary key (col1)
)
options (updateType='realtime');

-- 目標表為實時分割槽表：
CREATE TABLE etl_ads_db.etl_ads_partition_table (
  col1 INT, 
  col2 INT, 
  col3 INT, 
  col4 INT, 
  col5 DOUBLE, 
  col6 DOUBLE, 
  col7 DOUBLE
  primary key (col1, col2, col3, col4)
)
PARTITION BY HASH KEY(col1)
PARTITION NUM 32
TABLEGROUP xxx_group
options (updateType='realtime');

複製程式碼

步驟 3：在DLA中建立好與AnalyticDB目標表對映的表

使用Data Lake Analytics從OSS清洗資料到AnalyticDB

DLA中的表名、列名與AnalyticDB目標表對應同名

這種情況下，建表語句會比較簡單。
其中，如下引數需要指明：

-- 目標AnalyticDB
LOCATION = 'jdbc:mysql://etl_ads_db-e85fbfe8-vpc.cn-shanghai-1.ads.aliyuncs.com:10001/etl_ads_db'

-- 目標AnalyticDB的訪問使用者名稱
USER='xxx'

-- 目標AnalyticDB的訪問密碼
PASSWORD='xxx'
複製程式碼
CREATE SCHEMA `etl_dla_schema` WITH DBPROPERTIES 
( 
  CATALOG = 'ads', 
  LOCATION = 'jdbc:mysql://etl_ads_db-e85fbfe8-vpc.cn-shanghai-1.ads.aliyuncs.com:10001/etl_ads_db',
  USER='xxx',
  PASSWORD='xxx'
);

USE etl_dla_schema;

CREATE EXTERNAL TABLE etl_ads_dimension_table (
  col1 INT, 
  col2 VARCHAR(200), 
  col3 INT, 
  col4 VARCHAR(200),
  primary key (col1)
);

CREATE EXTERNAL TABLE etl_ads_partition_table (
  col1 INT, 
  col2 INT, 
  col3 INT, 
  col4 INT, 
  col5 DOUBLE, 
  col6 DOUBLE, 
  col7 DOUBLE
  primary key (col1, col2, col3, col4)
)
複製程式碼

步驟 4：在DLA中建立表指向源OSS資料

使用Data Lake Analytics從OSS清洗資料到AnalyticDB
CREATE SCHEMA oss_data_schema with DBPROPERTIES(
  LOCATION = 'oss://my_bucket/',
  catalog='oss'
);

CREATE EXTERNAL TABLE IF NOT EXISTS dla_table_1 (
    col_1 INT, 
    col_2 VARCHAR(200), 
    col_3 INT, 
    col_4 VARCHAR(200)
) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' 
STORED AS TEXTFILE 
LOCATION 'oss://my_bucket/oss_table_1';

CREATE EXTERNAL TABLE IF NOT EXISTS dla_table_2 (
  col_1 INT, 
  col_2 INT, 
  col_3 INT, 
  col_4 INT, 
  col_5 DOUBLE, 
  col_6 DOUBLE, 
  col_7 DOUBLE
) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' 
STORED AS TEXTFILE 
LOCATION 'oss://my_bucket/oss_table_2';

複製程式碼

步驟 5：在DLA中執行INSERT FROM SELECT語句

使用Data Lake Analytics從OSS清洗資料到AnalyticDB

INSERT FROM SELECT通常為長時執行任務，建議通過非同步執行方式：
注意：用MySQL命令列執行時，連線時，需要在命令列指定-c引數，用來識別MySQL語句前的hint：

mysql -hxxx -Pxxx -uxxx -pxxx db_name -c
複製程式碼

示例：

-- 執行OSS到AnalyticDB的全量資料插入
/*+run-async=true*/
INSERT INTO etl_dla_schema.etl_dla_dimension_table 
SELECT * FROM oss_data_schema.dla_table_1;

-- 執行OSS到AnalyticDB的資料插入，包含對OSS資料的篩選邏輯
/*+run-async=true*/
INSERT INTO etl_dla_schema.etl_dla_partition_table (col_1, col_2, col_3, col_7)
SELECT col_1, col_2, col_3, col_7 
FROM oss_data_schema.dla_table_2 
WHERE col_1 > 1000 
LIMIT 10000;
複製程式碼

注意：

  • 如果在INSERT INTO子句和SELECT子句中沒有指定列資訊，請確保源表和目標表的列定義順序一致，且型別對應匹配；
  • 如果在INSERT INTO子句和SELECT子句中指定了列的資訊，請確保兩者中的列的順序符合業務需要的匹配順序，且型別對應匹配。

如果在DMS for Data Lake Analytics控制檯(datalakeanalytics.console.aliyun.com/))執行，請選擇“非同步執行”。

使用Data Lake Analytics從OSS清洗資料到AnalyticDB

然後可以從“執行歷史” 中，點選“重新整理”，檢視任務的執行狀態。
非同步執行INSERT FROM SELECT語句，會返回一個task id，通過這個task id，可以輪詢任務執行情況，如果status為“SUCCESS”，則任務完成：

SHOW query_task WHERE id = '26c6b18b_1532588796832'
複製程式碼

注意事項

  • AnalyticDB為主鍵覆蓋邏輯，整個INSERT FROM SELECT的ETL任務失敗，使用者需要整體重試；
  • AnalyticDB消費資料有一定延時，在AnalyticDB端查詢寫入資料時，會有一定的延遲可見，具體延遲時間取決於AnalyticDB的資源規格；
  • 建議將ETL任務儘量切成小的單位批次執行，比如，OSS資料200GB，在業務允許的情況下，200GB的資料切成100個資料夾，每個資料夾2GB資料，對應DLA中建100張表，100張表分別做ETL，單個ETL任務失敗，可以只重試單個ETL任務；
  • ETL任務結束後，視情況刪除DLA中的表，包括對映AnalyticDB中的表、以及指向OSS資料的表。


本文作者：julian.zhou

原文連結

本文為雲棲社群原創內容，未經允許不得轉載。


相關文章