全部产品

联合查询多个MySQL实例

更新时间:2020-09-03 15:25:02

本文档将以两个云数据库RDS MySQL 版(简称MySQL)为例,介绍如何通过DLA联合查询多个MySQL实例数据。

背景信息

Data Lake Analytics(简称DLA) 作为云上数据处理的枢纽,支持通过标准JDBC对单个实例RDSTable StoreOSSMongoDB中的数据进行查询和分析。随着业务的扩展或者数据的划分,您可能会创建多个RDS、Table Store、OSS等实例存储数据。在这种多数据源场景下,仍然可以通过DLA实现多个相同类型数据源的联合查询。

前提条件

通过DLA联合查询两个MySQL实例数据前,您需要通过以下操作在两个MySQL实例中准备好测试数据。

  1. 创建两个MySQL实例

    DLA和两个MySQL所属地域必须相同,否则无法进行本文档操作。

    由于DLA将通过MySQL的VPC连接MySQL数据库,建议您创建MySQL实例时,网络类型选择VPC。同时,MySQL支持通过切换为专有网络将经典网络切换为VPC。

  2. 设置白名单

  3. 连接实例

  4. 写入测试数据

    • MySQL实例1中创建orders_db数据库和order_item表,写入测试数据:
    	create table orders_db.order_item (
    		id bigint not null primary key auto_increment,
    		prod_id bigint comment '商品ID',
    		prod_cnt int comment '商品数量'
    	);
    	insert into orders_db.order_item values 
    		(1, 1, 2),
    		(2, 2, 3),
    		(3, 3, 4),
    		(4, 2, 5),
    		(5, 1, 6);
    
    • MySQL实例2中创建prod_db数据库和prod表,写入测试数据:
    	create table prod_db.prod (
    		id bigint not null primary key auto_increment,
    		prod_name varchar(31) comment '商品名称'
    	);
    	insert into prod_db.prod values 
    		(1, '键盘'),
    		(2, '鼠标'),
    		(3, '显示器');
    

实施步骤

注意事项

在DLA中创建MySQL数据库连接前,需要将IP地址段100.104.0.0/16分别加入到MySQL白名单列表中。

由于您的MySQL实例位于VPC内,默认情况下DLA无法访问该VPC中的资源。为了让DLA访问MySQL,需要利用VPC反向访问技术,即在MySQL白名单中添加100.104.0.0/16IP地址段。

权限声明:当您在MySQL白名单中添加了100.104.0.0/16IP地址段,即视为您同意我们利用VPC反向访问技术读取MySQL数据库数据。

步骤一:在DLA中创建MySQL数据库连接

登录DLA控制台登录DMS,分别在DLA中创建两个底层映射到MySQL的数据库连接。

CREATE SCHEMA dla_orders_db WITH DBPROPERTIES (
  CATALOG = 'mysql', 
  LOCATION = 'jdbc:mysql://rm-******.mysql.rds.aliyuncs.com:3306/orders_db',
  USER = 'mysql_db_user_name',
  PASSWORD = 'mysql_db_password',
  VPC_ID = 'mysql_vpc_id',
  INSTANCE_ID = 'mysql_instance_id'
);
CREATE SCHEMA dla_prod_db WITH DBPROPERTIES (
  CATALOG = 'mysql', 
  LOCATION = 'jdbc:mysql://rm-******.mysql.rds.aliyuncs.com:3306/prod_db',
  USER = 'mysql_db_user_name',
  PASSWORD = 'mysql_db_password',
  VPC_ID = 'mysql_vpc_id',
  INSTANCE_ID = 'mysql_instance_id'
);

参数说明

  • LOCATION:由jdbc:mysql://MySQL内网连接地址:端口号/MySQL数据库名构成。

    LOCATION

  • USER:连接MySQL数据库所使用的用户名。

  • PASSWORD:连接MySQL数据库所使用的用户名对应的密码。

  • VPC_ID:MySQL实例所属VPC ID。

  • INSTANCE_ID:MySQL实例ID。

    VPC_ID

步骤二:在DLA中创建MySQL外表

针对MySQL中order_item表,本示例在DMS for Data Lake Analytics中创建同名外表order_item:

create external table order_item (
    id bigint,
    prod_id bigint,
    prod_cnt int,
);

针对MySQL中prod表,本示例在DMS for Data Lake Analytics中创建同名外表prod:

create external table prod (
    id bigint,
    prod_name varchar(31)
);

步骤三:通过DLA联合查询多个MySQL实例数据

MySQL数据库连接和外表创建成功后,接下来您可以通过MySQL客户端或者MySQL命令行工具连接DLA,使用标准SQL语句操作MySQL数据库数据。

也可以直接在DMS for Data Lake Analytics中操作MySQL数据库数据。

以下示例通过MySQL命令行工具连接DLA,联合查询order_item表和prod表数据:

mysql> select * from dla_orders_db.order_item;
+------+---------+----------+
| id   | prod_id | prod_cnt |
+------+---------+----------+
|    1 |       1 |        2 |
|    2 |       2 |        3 |
|    3 |       3 |        4 |
|    4 |       2 |        5 |
|    5 |       1 |        6 |
+------+---------+----------+
5 rows in set (0.22 sec)

mysql> select * from dla_prod_db.prod;
+------+-----------+
| id   | prod_name |
+------+-----------+
|    1 | 键盘      |
|    2 | 鼠标      |
|    3 | 显示器    |
+------+-----------+
3 rows in set (0.18 sec)

select p.prod_name, sum(o.prod_cnt) cnt
   from dla_prod_db.prod p inner join dla_orders_db.order_item o
    on p.id = o.prod_id
    group by p.prod_name
    order by cnt desc;
+------+-----------+
| prod_name | cnt  |
+-----------+------+
| 键盘      |    8 |
| 鼠标      |    8 |
| 显示器    |    4 |
+-----------+------+
3 rows in set (0.55 sec)

后续操作

我们通过DLA联合查询多个MySQL实例数据后,可以通过DLA将查询到的数据存入阿里云对象存储服务(Object Storage Service,简称 OSS)。OSS是阿里云提供的海量、安全、低成本、高可靠的云存储服务。后续需要这部分数据时,DLA可以在不移动OSS文件的情况下,直接读取数据。

通过以下步骤,将联合查询到的多个MySQL实例数据写入OSS。

  1. 开通OSS服务

    DLA和OSS所属地域必须相同,否则无法进行本文档操作。

  2. 创建存储空间

  3. 在DLA中创建OSS连接。

    详细操作,请参见海量、低成本日志分析。

    	CREATE SCHEMA dla_oss_db with DBPROPERTIES(
     catalog='oss',
    location = 'oss://dlaossfile1/'
    );
    
  4. 在DLA中创建AnalyticDB外表

    详细操作,请参见海量、低成本日志分析。

    	CREATE EXTERNAL TABLE IF NOT EXISTS top_prod (
     prod_name string NOT NULL COMMENT '',
     cnt bigint NOT NULL COMMENT ''
    	) 
    	ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' 
    	STORED AS TEXTFILE 
    	LOCATION 'oss://dlaossfile1/';
    
  5. 在DLA中异步执行INSERT FROM SELECT将数据写入OSS

    详细操作,请参见海量、低成本日志分析。

    	insert into dla_oss_db.top_prod
    		select p.prod_name as prod_name, sum(o.prod_cnt) as cnt
    		from dla_prod_db.prod p inner join dla_orders_db.order_item o
    		on p.id = o.prod_id
    		group by p.prod_name
    		order by cnt desc;
    
  6. 在DLA中查看OSS数据

    	select * from top_prod;
    	+-----------+------+
    	| prod_name | cnt  |
    	+-----------+------+
    	| 键盘      |    8 |
    	| 显示器    |    4 |
    	| 鼠标      |    8 |