RDS（Relational Database Service）是阿里云用户主要使用的服务，需要通过内网域名访问。如果您需要通过MaxCompute将数据加载至RDS的表中，可参考该文档进行操作。本文为您介绍如何在外网或VPC网络环境下基于RDS数据源创建外部表并写入数据。

前提条件

已开通MaxCompute和云数据库RDS间的网络连接，详情请参见网络开通流程。

使用限制

  • 只有华北2（北京）、华东2（上海）、华北3（张家口）、华东1（杭州）和华南1（深圳）地域支持通过服务映射或专有网络连接方案连接MaxCompute和云数据库RDS，仅这五个地域可以创建RDS数据源外部表，其他地域暂不支持。
  • 暂不支持PrivateZone域名。
  • 支持RDS MySQL5.x版本，暂不支持8.0版本。
  • 当您需要向RDS外部表写入大量数据时，采用并行多进程写入方式，会小概率出现某个写入进程数据重写情况，导致数据重复。
  • MaxCompute里建的RDS外部表，其中DECIMAL数据类型默认小数18位，不能修改，只能建成decimal(38，18)。如果小数位数比较少，可以在MaxCompute中建外部表时数据类型选择String，使用的时候再使用cast函数强制转换。

语法说明

建表时，表名和列名大小写不敏感。在查询表或列名时，无需区分大小写，且不支持强制转换大小写。
--开启Hive兼容模式。
set odps.sql.hive.compatible = true;
--打开外部表VPC支持。
set odps.sql.external.net.vpc=true;
CREATE EXTERNAL TABLE <mcrdsexternal_table_name>(
  <col_name1> <data_type>,
  <col_name2> <data_type>,
  ......
)
STORED BY 'com.aliyun.odps.jdbc.JdbcStorageHandler'  --处理JDBC连接类数据源的Handler。
 WITH SERDEPROPERTIES (
 'odps.external.net.vpc'='true',
 'odps.vpc.id'='<VPC ID>',
 'odps.vpc.access.ips'='<realm_name:port>',
 'odps.vpc.region'='<RegionID>'
 )
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>'
)
;
  • mcrdsexternal_table_name：必填。待创建外部表的名称。
  • col_name：必填。外部表的列名称。
  • data_type：必填。列的数据类型。
  • WITH SERDEPROPERTIES：
    • 'odps.external.net.vpc'='true'：必填。表明外部表的数据源处于VPC网络。
    • 'odps.vpc.id'='<VPC ID>'：必填。VPC网络ID号。详情请参见获取RegionID及VPC ID。
    • 'odps.vpc.access.ips'='<realm_name:port>'：必填。RDS数据源外部表所属VPC域名（内网地址）及端口。查看内网地址及端口操作，请参见查看或修改内外网地址和端口。
    • 'odps.vpc.region'='<RegionID>'：必填。VPC网络所在的地域ID。详情请参见获取RegionID及VPC ID。
  • jdbc:mysql://realm_name:port/rds_database_name?useSSL=false&user=user_name&password=password_value@&table=rds_table_name：必填。连接RDS数据源表的连接字符串。
    • rds_database_name：RDS数据库名称。
    • user_name：RDS数据库的账号。
    • password_value：RDS数据库的密码。
    • rds_table_name：RDS源表名称。
  • TBLPROPERTIES：
    • 'odps.federation.jdbc.colmapping'='<col_name1:rdstable_colname1|select_alias1>[,<col_name2:rdstable_colname2|select_alias2>,...]'：可选。MaxCompute外部表与RDS数据源表列的映射关系，此处的映射列数，需要与MaxCompute外部表定义的列数保持一致。其中：rdstable_colname为RDS源表的列名（全部列映射），select_alias为给查询结果定义的列别名（指定列映射）。
    • 'mcfed.mapreduce.jdbc.input.query'='<select_sentence>'：必填。读取RDS数据源表数据。新建外部表的列名与RDS数据源表的列名及数据类型保持一致。select_sentence格式为select xx from rds_database_name.rds_table_name。

使用RDS数据源创建MaxCompute的外部表并加载数据

使用RDS数据源创建MaxCompute外部表的步骤如下：

  1. 登录RDS数据库，执行建表语句并插入数据。操作详情请参见通过DMS登录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,"张三");
INSERT INTO `rds_mc_external`(`id` ,`name` ) VALUES(1,"zhangsan");
  2. 登录MaxCompute客户端，设置MaxCompute需要访问的外部表所属RegionID和VPC ID。
    命令语法如下：
    --注册RDS数据源的VPC属性。
setproject odps.security.outbound.destination=<RegionID>_<VPC ID>[*];
    • RegionID：必填。VPC网络所在的地域ID。详情请参见获取RegionID及VPC ID。
    • VPC ID：必填。VPC网络ID号。详情请参见获取RegionID及VPC ID。
    • [*]：可选。通配符，表示VPC网络下的所有IP和端口都加入MaxCompute白名单。
    命令示例如下：
    setproject odps.security.outbound.destination=cn-beijing_vpc-2ze7cqx2bqodp9ri1****[*];
  3. 在MaxCompute客户端创建映射RDS数据源的外部表。您可以通过如下两种方式进行操作：
    • 创建MaxCompute外部表的列名和RDS中表的列名完全对应。
      1. 在MaxCompute客户端创建外部表，表列名与RDS中表的列名完全对应。命令示例如下：
        set odps.sql.hive.compatible = true;
set odps.sql.external.net.vpc=true;
CREATE EXTERNAL TABLE mc_vpc_rds_external (
id int,
name string
)
STORED BY 'com.aliyun.odps.jdbc.JdbcStorageHandler'
WITH SERDEPROPERTIES (
'odps.external.net.vpc'='true',
'odps.vpc.id'='vpc-2zeeopx9atnrkay8n****',
'odps.vpc.access.ips'='rm-2ze01y92y1tzp****.mysql.rds.aliyuncs.com:3306',
'odps.vpc.region'='cn-beijing'
)
location 'jdbc:mysql://rm-2ze01y92y1tzp****.mysql.rds.aliyuncs.com:3306/rds_test?useSSL=false&user=****&password=****&table=rds_mc_external'
TBLPROPERTIES(
'mcfed.mapreduce.jdbc.input.query'='select * from rds_test.rds_mc_external'
);
      2. 向新建的MaxCompute表中插入数据。
        命令示例如下：
        --插入数据。
INSERT INTO table mc_vpc_rds_external values(2,"zhagnsan");
      3. 查询数据插入结果。
        命令示例如下：
        --查询数据插入结果。
SELECT * FROM mc_vpc_rds_external;
    • 创建MaxCompute外部表的列名和RDS表指定的列名进行映射。
      1. 在MaxCompute客户端创建外部表，表列名与RDS表中指定的列名进行映射。命令示例如下：
        set odps.sql.hive.compatible = true;
set odps.sql.external.net.vpc=true;
CREATE EXTERNAL TABLE mc_vpc_rds_external_mapping (
  key int,
  value string
)
STORED BY 'com.aliyun.odps.jdbc.JdbcStorageHandler'
 WITH SERDEPROPERTIES (
 'odps.external.net.vpc'='true',
 'odps.vpc.id'='vpc-2zeeopx9atnrkay8n****',
 'odps.vpc.access.ips'='rm-2ze01y92y1tzp****.mysql.rds.aliyuncs.com:3306',
 'odps.vpc.region'='cn-beijing'
 )
location 'jdbc:mysql://rm-2ze01y92y1tzp****.mysql.rds.aliyuncs.com:3306/rds_test?useSSL=false&user=****&password=****&table=rds_mc_external'
TBLPROPERTIES(
 'odps.federation.jdbc.colmapping'='key:id2,value:id3',
 'mcfed.mapreduce.jdbc.input.query'='select id*100 as id2,count(*) as id3 from test_mc_rdset.rds_mc_external group by id'
)
;
      2. 向新建的MaxCompute表中插入数据。
        命令示例如下：
        --插入数据。
INSERT INTO TABLE mc_vpc_rds_external_mapping VALUES(4,"lisi");
      3. 查询数据插入结果。
        命令示例如下：
        --查询数据插入结果。
SELECT * FROM mc_vpc_rds_external_mapping;