RDS資料來源建立外部表格並寫入資料說明 - MaxCompute

本文介紹如何在外網或VPC網路環境下基於RDS資料來源建立外部表格並寫入資料。

功能介紹

RDS（Relational Database Service）是阿里雲的關係型資料庫服務，通常使用內網網域名稱訪問。通過MaxCompute可以將資料載入至RDS表，並執行資料讀寫操作。

適用範圍

地區限制：目前支援地區包括華北2（北京）、華東2（上海）、華北3（張家口）、華北6（烏蘭察布）、華東1（杭州）、華南1（深圳）、中國香港、華東 2 金融雲（可用性區域F）、日本（東京）、新加坡、馬來西亞（吉隆坡）、印尼（雅加達）、德國（法蘭克福）、美國（矽谷）、美國（維吉尼亞）。
引擎節流：支援RDS MySQL 5.x和8.0版本，暫不支援其他RDS引擎。
暫不支援PrivateZone網域名稱。
RDS外部表格不支援cluster屬性。
向RDS外部表格寫入大量資料時，採用並行多進程寫入方式，會小機率出現某個寫入進程資料重寫情況，導致資料重複。
小數位元限制：MaxCompute中建立的RDS外部表格，DECIMAL資料類型預設小數位為18位，不能修改，只能建立為DECIMAL(38,18)。如果小數位元比較少，可以在MaxCompute中建立外部表格時，資料類型選擇String，使用該資料時再使用CAST函數強制轉換。

注意事項

當RDS MySQL源表中的Schema與外表Schema不一致時：

列數不一致：如果RDS MySQL源表中的列數小於外表DDL的列數，則讀取RDS資料時，系統會報錯。例如Unknown column 'xxx' in 'field list'。當RDS MySQL源表中的列數大於外表DDL的列數時，系統會丟棄超出的列資料。
列類型不一致：MaxCompute不支援使用INT類型接收RDS源表中的STRING類型資料，支援使用STRING類型接收INT類型資料（不推薦）。

建立外部表格

文法結構

建表時，表名和列名大小寫不敏感。在查詢表或列名時，無需區分大小寫，且不支援強制轉換大小寫。

-- 開啟Hive相容模式。
SET odps.sql.hive.compatible = true;
CREATE EXTERNAL TABLE <table_name>(
  <col_name1> <data_type>,
  <col_name2> <data_type>,
  ......
)
STORED BY 'com.aliyun.odps.jdbc.JdbcStorageHandler'  -- 處理JDBC串連類資料來源的Handler。
location '<jdbc:mysql://<realm_name:port>/<rds_database_name>?useSSL=false&user=<user_name>&password=<password_value>&table=<rds_table_name>>' 
TBLPROPERTIES(
   ['odps.federation.jdbc.colmapping'='<col_name1:rdstable_colname1|select_alias1>,[<col_name2:rdstable_colname2|select_alias2>,...]',]
   'mcfed.mapreduce.jdbc.input.query'='<select_sentence>',
   'networklink'='<networklink_name>');

參數說明

table_name：必填。待建立外部表格的名稱。
col_name：必填。外部表格的列名稱。
data_type：必填。列的資料類型。
jdbc:mysql://realm_name:port/rds_database_name?useSSL=false&user=user_name&password=password_value&table=rds_table_name：必填。串連RDS資料來源表的連接字串。
連接字串中如果包含特殊字元需要轉換為URL編碼，轉換URL編碼請參見URL_ENCODE。
- realm_name:port：RDS資料連線內網地址及內網連接埠。
  1. 登入RDS 控制台。
  2. 在左側導覽列，選擇執行個體列表，在左上方選擇地區。
  3. 在執行個體列表頁面，單擊目標執行個體ID/名稱，進入執行個體詳情頁。
  4. 在左側導覽列，單擊資料庫連接。
  5. 查看資料庫內網地址、外網地址及內網連接埠。
- rds_database_name：RDS資料庫名稱。
- user_name：RDS資料庫的帳號。
- password_value：RDS資料庫的密碼。
- rds_table_name：RDS源表名稱。
TBLPROPERTIES：
- odps.federation.jdbc.colmapping：可選。
  MaxCompute外部表格與RDS資料來源表列的映射關係，此處的映射列數需要與MaxCompute外部表格定義的列數保持一致。
  其中：rdstable_colname為RDS源表的列名（全部列映射），select_alias為給查詢結果定義的列別名（指定列映射）。
  - 若未配置該參數，則會使用MaxCompute外部表格中定義的列名映射RDS同名列訪問。
  - 若配置該參數，但是只指定MaxCompute外部表格部分列的映射關係，則按照源表欄位名映射至RDS外部表格的對應列。其他未指定的列，如果列名或類型不符會報錯。
- mcfed.mapreduce.jdbc.input.query：可選。
  讀取RDS資料來源表資料。外部表格的列、列名與直接查詢的RDS資料來源表的列、列名及資料類型保持一致。如果使用了別名，則與別名保持一致。select_sentence格式為SELECT xxx FROM <rds_database_name>.<rds_table_name>。
- networklink：必填。RDS執行個體所在VPC的MaxCompute網路連接名稱。
  - 登入MaxCompute控制台，在左上方選擇地區。
  - 在左側導覽列，選擇管理配置 > 网络连接 。
  - 在网络连接頁面擷取RDS所在VPC對應的網路連接名稱。
    RDS的網路連接查看位置在登入RDS 控制台並選擇執行個體後，單擊左側導覽列的資料庫連接，查看該資料庫所在VPC網路。

使用RDS資料來源建立MaxCompute的外部表格並載入資料

使用RDS資料來源建立MaxCompute外部表格的步驟如下：

已開通MaxCompute和雲資料庫RDS間的網路連接，詳情請參見訪問公網方案。
網路打通時，MaxCompute側僅打通到所填VPC ID的網路連接，如需跨Region訪問或者訪問該Region的其他VPC，請根據云上VPC現有打通方案，打通專線直連方案所填寫的VPC和其他VPC之間的網路。

登入RDS資料庫，執行建表語句並插入資料。操作詳情請參見通過DMS登入RDS資料庫。

登入RDS 控制台。
在左側導覽列，選擇執行個體列表，在左上方選擇地區。
若沒有執行個體，則在執行個體列表頁面，單擊建立執行個體；若已有執行個體，則在執行個體列表頁面，單擊目標執行個體ID/名稱，進入執行個體詳情頁。
建立執行個體時，RDS引擎選擇RDS MySQL 5.x和8.0版本，暫不支援其他RDS引擎。
在左側導覽列，單擊資料庫管理。

單擊建立資料庫。配置如下參數：

參數	是否必填	說明	樣本
資料庫（DB）名稱	必填	長度為2~64個字元。以字母開頭，以字母或數字結尾。由小寫字母、數字、底線或中劃線組成。資料庫名稱在執行個體內必須是唯一的。資料庫名稱中如果包含`-`，建立出的資料庫的檔案夾的名字中的`-`會變成`@002d`。	`rds_mc_test`
支援字元集	必填	請按需選擇字元集。	`utf8`
授權帳號	選填	選中需要訪問本資料庫的帳號。本參數可以留空，建立資料庫後再綁定帳號。此處僅會顯示普通帳號。高許可權帳號擁有所有資料庫的所有許可權，無需授權。	`預設`
備忘說明	選填	用於備忘該資料庫的相關資訊，便於後續資料庫管理，最多支援256個字元。	`RDS外部表格測試資料庫`

單擊登入資料庫，在左側導覽列選擇資料庫執行個體，雙擊選中已建立的資料庫，在右側SQLConsole頁面執行下列語句，建立測試表並寫入測試資料。
如果執行個體存在，但執行個體展開後未找到目標資料庫，可能是：
- 登入帳號無目標資料庫的存取權限：可前往RDS執行個體詳情頁的帳號管理頁面手動修改帳號許可權或更換登入的資料庫帳號
- 中繼資料未同步導致目錄無法顯示：請將滑鼠懸浮在目標資料庫所屬執行個體上，單擊執行個體名右側的按鈕，即可重新整理資料庫列表，顯示目標資料庫。

建表示例如下：

CREATE TABLE `rds_mc_external` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `rds_mc_external`(`id` ,`name` ) VALUES(1,"Alice");
INSERT INTO `rds_mc_external`(`id` ,`name` ) VALUES(1,"Bob");

在MaxCompute用戶端建立映射RDS資料來源的外部表格

方式一：全部列映射

在MaxCompute用戶端建立外部表格，表列名與RDS中表的列名完全對應。命令樣本如下：

SET odps.sql.hive.compatible = true;

CREATE EXTERNAL TABLE mc_vpc_rds_external (
id INT,
name STRING)
STORED BY 'com.aliyun.odps.jdbc.JdbcStorageHandler'
location 'jdbc:mysql://rm-2ze01y92y1tzp****.mysql.rds.aliyuncs.com:3306/rds_mc_test?useSSL=false&user=<>&password=<>&table=rds_mc_external'
TBLPROPERTIES(
  'odps.federation.jdbc.colmapping'='key:id,value:name',
  'mcfed.mapreduce.jdbc.input.query'='select * from rds_mc_test.rds_mc_external',
  'networklink'='<your network name>');

向建立的MaxCompute表中插入資料

INSERT INTO TABLE mc_vpc_rds_external VALUES(2,"Zoey");

查詢結果

-- 查詢資料插入結果。
SELECT * FROM mc_vpc_rds_external;

-- 返回結果：
+------------+------------+
| id         | name       | 
+------------+------------+
| 1          | Alice      | 
| 1          | Bob        | 
| 2          | Zoey       |  
+------------+------------+

方式二：指定列映射

在MaxCompute用戶端建立外部表格，表列名與RDS表中指定的列名映射。命令樣本如下：

SET odps.sql.hive.compatible = true;

CREATE EXTERNAL TABLE mc_vpc_rds_external_mapping (
  id INT,
  name STRING
)
STORED BY 'com.aliyun.odps.jdbc.JdbcStorageHandler'
location 'jdbc:mysql://rm-2ze01y92y1tzp****.mysql.rds.aliyuncs.com:3306/rds_mc_test?useSSL=false&user=****&password=****&table=rds_mc_external'
TBLPROPERTIES(
  'mcfed.mapreduce.jdbc.input.query'='select * from rds_mc_test.rds_mc_external',
  'networklink'='<your network name>');

向建立的MaxCompute表中插入資料

INSERT INTO TABLE mc_vpc_rds_external_mapping VALUES(4,"Lisa");

查詢資料插入結果

SELECT * FROM mc_vpc_rds_external_mapping;

-- 返回結果
+------------+------------+
| id         | name       | 
+------------+------------+
| 1          | Alice      | 
| 1          | Bob        | 
| 4          | Lisa       | 
+------------+------------+