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

前提条件

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

注意事项

  • 只有华北2(北京)、华东2(上海)、华北3(张家口)和华东1(杭州)区域支持通过服务映射或专有网络连接方案连接MaxCompute和云数据库RDS,仅这四个区域可以创建RDS数据源外部表,其他区域暂不支持。
  • 暂不支持PrivateZone域名。
  • 支持RDS MySQL5.x版本,暂不支持8.0版本。

语法说明

--开启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数据源表字段的映射关系。其中: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;