联合查询多个MySQL实例

背景信息

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

前提条件

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

1. 创建两个MySQL实例

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地址段。

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

登录DLA控制台，单击左侧导航栏的SQL访问点，单击登录到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数据库名构成。

  

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

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

• VPC_ID：MySQL实例所属VPC ID。

• INSTANCE_ID：MySQL实例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文件的情况下，直接读取数据。