全部产品
云市场

通过DLA联合查询多个MySQL实例数据

更新时间:2019-10-18 10:29:35

背景信息

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

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

前提条件

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

  1. 创建两个MySQL实例

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

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

  2. 设置白名单

  3. 连接实例

  4. 写入测试数据

    • MySQL实例1中创建orders_db数据库和order_item表,写入测试数据:

      1. create table orders_db.order_item (
      2. id bigint not null primary key auto_increment,
      3. prod_id bigint comment '商品ID',
      4. prod_cnt int comment '商品数量'
      5. );
      6. insert into orders_db.order_item values
      7. (1, 1, 2),
      8. (2, 2, 3),
      9. (3, 3, 4),
      10. (4, 2, 5),
      11. (5, 1, 6);
    • MySQL实例2中创建prod_db数据库和prod表,写入测试数据:

      1. create table prod_db.prod (
      2. id bigint not null primary key auto_increment,
      3. prod_name varchar(31) comment '商品名称'
      4. );
      5. insert into prod_db.prod values
      6. (1, '键盘'),
      7. (2, '鼠标'),
      8. (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的数据库连接。

  1. CREATE SCHEMA dla_orders_db WITH DBPROPERTIES (
  2. CATALOG = 'mysql',
  3. LOCATION = 'jdbc:mysql://rm-******.mysql.rds.aliyuncs.com:3306/orders_db',
  4. USER = 'mysql_db_user_name',
  5. PASSWORD = 'mysql_db_password',
  6. VPC_ID = 'mysql_vpc_id',
  7. INSTANCE_ID = 'mysql_instance_id'
  8. );
  9. CREATE SCHEMA dla_prod_db WITH DBPROPERTIES (
  10. CATALOG = 'mysql',
  11. LOCATION = 'jdbc:mysql://rm-******.mysql.rds.aliyuncs.com:3306/prod_db',
  12. USER = 'mysql_db_user_name',
  13. PASSWORD = 'mysql_db_password',
  14. VPC_ID = 'mysql_vpc_id',
  15. INSTANCE_ID = 'mysql_instance_id'
  16. );

参数说明

  • 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:

  1. create external table order_item (
  2. id bigint,
  3. prod_id bigint,
  4. prod_cnt int,
  5. );

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

  1. create external table prod (
  2. id bigint,
  3. prod_name varchar(31)
  4. );

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

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

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

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

  1. mysql> select * from dla_orders_db.order_item;
  2. +------+---------+----------+
  3. | id | prod_id | prod_cnt |
  4. +------+---------+----------+
  5. | 1 | 1 | 2 |
  6. | 2 | 2 | 3 |
  7. | 3 | 3 | 4 |
  8. | 4 | 2 | 5 |
  9. | 5 | 1 | 6 |
  10. +------+---------+----------+
  11. 5 rows in set (0.22 sec)
  12. mysql> select * from dla_prod_db.prod;
  13. +------+-----------+
  14. | id | prod_name |
  15. +------+-----------+
  16. | 1 | 键盘 |
  17. | 2 | 鼠标 |
  18. | 3 | 显示器 |
  19. +------+-----------+
  20. 3 rows in set (0.18 sec)
  21. select p.prod_name, sum(o.prod_cnt) cnt
  22. from dla_prod_db.prod p inner join dla_orders_db.order_item o
  23. on p.id = o.prod_id
  24. group by p.prod_name
  25. order by cnt desc;
  26. +------+-----------+
  27. | prod_name | cnt |
  28. +-----------+------+
  29. | 键盘 | 8 |
  30. | 鼠标 | 8 |
  31. | 显示器 | 4 |
  32. +-----------+------+
  33. 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连接。

    详细操作,请参见通过DLA将OSS数据迁移至AnalyticDB中的步骤三:在DLA中创建OSS连接

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

    详细操作,请参见通过DLA将OSS数据迁移至AnalyticDB中的步骤四:在DLA中创建指向OSS文件的外表

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

    详细操作,请参见通过DLA将OSS数据迁移至AnalyticDB中的步骤五:在DLA中执行INSERT FROM SELECT将OSS中的数据迁移至AnalyticDB

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

    1. select * from top_prod;
    2. +-----------+------+
    3. | prod_name | cnt |
    4. +-----------+------+
    5. | 键盘 | 8 |
    6. | 显示器 | 4 |
    7. | 鼠标 | 8 |