Data Lake Analytics - Deprecated：聯集查詢多個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服務（Object Storage Service，簡稱 OSS）。OSS是阿里雲提供的海量、安全、低成本、高可靠的雲端儲存體服務。後續需要這部分資料時，DLA可以在不移動OSS檔案的情況下，直接讀取資料。