教程:使用DataLakeAnalytics讀/寫RDS資料
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, 如下圖所示:
而 INSTANCE_ID
則是你的RDS例項ID, 在RDS的詳情頁面可以找到:
建表需要這兩個額外資訊是因為現在使用者的資料庫都是處於使用者自己的VPC內部,預設情況下 DLA 是訪問不了使用者 VPC 裡面的資源的,為了讓DLA能夠訪問到使用者RDS裡面的資料,我們需要利用阿里雲的VPC反向訪問技術。
許可權宣告: 當您通過上述方式建庫,就視為您同意我們利用VPC反向訪問的技術去讀寫您的RDS。
另外您還需要把 100.104.0.0/16
IP地址段加入到你的RDS的白名單列表,這是我們VPC反向訪問的IP地段,如下圖:
建立表
資料庫建完之後,我們可以建表了,我們先在你的 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!
相關文章
- 使用Data Lake Analytics讀/寫RDS資料
- RDS讀寫分離,海量資料一鍵搞定
- 教程:使用DataLakeAnalytics+OSS分析CSV格式的TPC-H資料集
- 教程:如何使用DataLakeAnalytics建立分割槽表
- 使用DataLakeAnalytics從OSS清洗資料到AnalyticDB
- Pandas資料讀寫
- 資料讀寫流程
- 基於 DataLakeAnalytics 的資料湖實踐
- 基於DataLakeAnalytics的資料湖實踐
- 資料庫–如何連線RDS例項,使用雲資料庫?資料庫
- .NET 5/.NET Core使用EF Core 5連線MySQL資料庫寫入/讀取資料示例教程MySql資料庫
- 資料讀寫壓力大,讀寫分離
- 基於DataLakeAnalytics做跨地域的資料分析
- TensorFlow讀寫資料
- Pandas讀寫資料庫資料庫
- 雲資料庫RDS是什麼?雲資料庫RDS有什麼優勢?資料庫
- AWS RDS Oracle資料遷移Oracle
- Django 直接使用資料庫連線和遊標讀寫資料庫Django資料庫
- HBase資料的讀寫流程
- vtk資料的讀寫
- 資料庫讀寫分離資料庫
- Flink 實踐教程-入門(6):讀取 PG 資料寫入 ClickHouse
- HDFS讀寫兩步教程
- Java mysql blob 資料讀寫操作JavaMySql
- JuiceFS 資料讀寫流程詳解UI
- io流對資料的讀寫
- iOS 簡單資料的讀寫iOS
- 資料庫的讀寫分離資料庫
- Redis資料儲存和讀寫Redis
- 說說如何在 Spring Boot 中使用 JdbcTemplate 讀寫資料Spring BootJDBC
- 使用Spring AOP切面解決資料庫讀寫分離Spring資料庫
- Flink 實踐教程 - 入門(4):讀取 MySQL 資料寫入到 ESMySql
- PG 資料庫 從阿里雲pg rds 同步資料。資料庫阿里
- JavaScript 讀寫二進位制資料JavaScript
- 讀寫分離的的資料同步?
- 天翼雲RDS資料庫如何修改資料庫引數資料庫
- E-Mapreduce如何處理RDS的資料
- 從RDS(相容MySQL)匯出資料案例分析MySql