使用Data Lake Analytics讀/寫RDS資料

阿里云云棲社群發表於2019-04-12
原文網址 : https://juejin.im/post/5cb0503c5188251af26d25df

Data Lake Analytics 作為雲上資料處理的樞紐，最近加入了對於RDS(目前支援 MySQL , SQLServer ,Postgres 引擎)的支援, 這篇教程帶你玩轉 DLA 的 RDS 支援。我們文章中會以 MySQL 的介紹為主，最後會簡要介紹下 SQLServer 與 Postgres 的一些不同點、需要注意的地方。

建立資料庫

在 DLA 裡面建立一個底層對映到 MySQL 的外表的語法如下:

CREATE SCHEMA hello_mysql_vpc_rds WITH DBPROPERTIES (
  CATALOG = 'mysql', 
  LOCATION = 'jdbc:mysql://rm-2zer0vg58mfofake.mysql.rds.aliyuncs.com:3306/dla_test',
  USER = 'dla_test',
  PASSWORD = 'the-fake-password',
  VPC_ID = 'vpc-2zeij924vxd303kwifake',
  INSTANCE_ID = 'rm-2zer0vg58mfo5fake'
);
複製程式碼

跟普通的建表不同的是這裡多了兩個屬性: VPC_ID 和 INSTANCE_ID 。VPC_ID是你的RDS所在VPC的ID, 如下圖所示:

使用Data Lake Analytics讀/寫RDS資料

而 INSTANCE_ID 則是你的RDS例項ID, 在RDS的詳情頁面可以找到:

使用Data Lake Analytics讀/寫RDS資料

建表需要這兩個額外資訊是因為現在使用者的資料庫都是處於使用者自己的VPC內部，預設情況下 DLA 是訪問不了使用者 VPC 裡面的資源的，為了讓DLA能夠訪問到使用者RDS裡面的資料，我們需要利用阿里雲的VPC反向訪問技術。

許可權宣告: 當您通過上述方式建庫，就視為您同意我們利用VPC反向訪問的技術去讀寫您的RDS。

另外您還需要把 100.104.0.0/16 IP地址段加入到你的RDS的白名單列表，這是我們VPC反向訪問的IP地段，如下圖:

使用Data Lake Analytics讀/寫RDS資料

建立表

資料庫建完之後，我們可以建表了，我們先在你的 RDS 裡面建立如下的 person 表用來做測試:

create table person (
       id int,
       name varchar(1023),
       age int
);
複製程式碼

並且向裡面插入一下測試資料:

insert into person 
  values (1, 'james', 10), 
         (2, 'bond', 20), 
         (3, 'jack', 30), 
         (4, 'lucy', 40);
複製程式碼

然後就可以在 DLA 的資料庫裡面建立相應的對映表了:

create external table person (
       id int,
       name varchar(1023),
       age int
) tblproperties (
  table_mapping = "person"
);
複製程式碼

這樣我們通過MySQL客戶端連線到 DLA 資料庫上面，就可以對 MySQL 資料庫裡面的資料進行查詢了:

mysql> select * from person;
+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | james |   10 |
|    2 | bond  |   20 |
|    3 | jack  |   30 |
|    4 | lucy  |   40 |
+------+-------+------+
4 rows in set (0.35 sec)
複製程式碼

ETL: 把資料從OSS裡面清洗出來寫入RDS

其實 DLA 裡面使用 RDS 的典型場景不是把RDS的資料讀出來進行分析，因為RDS本身能承載的資料量有限，不適合大資料分析，更多的場景是在我們對儲存在OSS/OTS上的大資料進行分析，分析完成之後把結果資料回寫到 RDS 裡面供前臺業務使用。這種場景在DLA裡面非常容易實現，還是舉前面 person 表例子，下面的語句把 oss_db 裡面 customer 的十條記錄進行了一些轉換然後插入了我們的 hello_mysql_vps_rds.person 表:

mysql> insert into hello_mysql_vpc_rds.person
    -> select c_custkey, c_name, c_custkey + 20 from oss_db.customer limit 10;

+------+
| rows |
+------+
|   10 |
+------+
1 row in set (4.57 sec)

mysql> select * from person;
+------+--------------------+------+
| id   | name               | age  |
+------+--------------------+------+
|    1 | james              |   10 |
|    2 | bond               |   20 |
|    3 | jack               |   30 |
|    4 | lucy               |   40 |
|    1 | Customer#000000001 |   21 |
|    3 | Customer#000000003 |   23 |
|    5 | Customer#000000005 |   25 |
|    2 | Customer#000000002 |   22 |
|    4 | Customer#000000004 |   24 |
|    7 | Customer#000000007 |   27 |
|    6 | Customer#000000006 |   26 |
|    9 | Customer#000000009 |   29 |
|    8 | Customer#000000008 |   28 |
|   10 | Customer#000000010 |   30 |
+------+--------------------+------+
14 rows in set (0.26 sec)

複製程式碼

SQLServer 和 PostgreSQL

SQLServer 和 PostgreSQL 總體用法上跟 MySQL 類似，但是因為 MySQL 裡面 database 跟 schema 是等價的，只有一層結構，而 SQLServer 和 PostgreSQL面則有 database 和 schema 的兩層結構，因此在建庫和建表的時候都有點稍微不一樣:

建庫

SQLServer

CREATE SCHEMA `hello_sqlserver_vpc_rds` WITH DBPROPERTIES 
( 
  CATALOG = 'sqlserver', 
  LOCATION = 'jdbc:sqlserver://rm-bp15g1r5jf90hfake.sqlserver.rds.aliyuncs.com:3433;DatabaseName=dla_test',
  USER='dla_test1',
  PASSWORD='this-is-not-a-real-password',
  INSTANCE_ID = 'rm-bp15g1r5jf90fake',
  VPC_ID = 'vpc-bp1adypqlcn535yrdfake'
);
複製程式碼
  • 首先 CATALOG 要指定 sqlserver 。
  • LOCATION 要指定資料庫的名字，指定的方式跟 MySQL 不一樣, 是通過 DatabaseName=dla_test 的方式來指定。這是 SQLServer JDBC URL 定義的，不是DLA定義的。

PostgreSQL

CREATE SCHEMA `hello_postgresql_vpc_rds` WITH DBPROPERTIES 
( 
  CATALOG = 'postgresql', 
  LOCATION = 'jdbc:postgresql://rm-bp1oo49r6j3hvfake.pg.rds.aliyuncs.com:3433/dla_test',
  USER='dla_test',
  PASSWORD='this-is-not-a-real-password',
  INSTANCE_ID = 'rm-bp1oo49r6j3hfake',
  VPC_ID = 'vpc-bp1adypqlcn535yrfake'
);
複製程式碼

這裡跟 MySQL 幾乎一樣，除了 CATALOG 要指定成 postgresql 。

建表

建表這塊的差異主要在 table_mapping 這個欄位裡面, MySQL 的 table_mapping裡面只有表名(person ):

create external table person1 (
       id int,
       name varchar(1023),
       age int
) tblproperties(
  table_mapping = 'person'
);
複製程式碼

而 SQLServer 和 PostgreSQL 的 table_mapping 裡面則要有schema的名字和表名:

create external table person (
       id int,
       name varchar(1023),
       age int
) tblproperties(
  table_mapping = 'public.person'
);
複製程式碼

總結

Happy DLAing!


本文作者：xumingmingv

原文連結

本文為雲棲社群原創內容，未經允許不得轉載。


相關文章