教程:使用Data Lake Analytics读/写RDS数据

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介:

Data Lake Analytics 作为云上数据处理的枢纽,最近加入了对于RDS(目前支持 MySQL , SQLServer , Postgres 引擎)的支持, 这篇教程带你玩转 DLA 的 RDS 支持。我们文章中会以 MySQL 的介绍为主,最后会简要介绍下 SQLServerPostgres 的一些不同点、需要注意的地方。

创建数据库

在 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_IDINSTANCE_IDVPC_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

SQLServerPostgreSQL 总体用法上跟 MySQL 类似,但是因为 MySQL 里面 databaseschema 是等价的,只有一层结构,而 SQLServerPostgreSQL 面则有 databaseschema 的两层结构,因此在建库和建表的时候都有点稍微不一样:

建库

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 这个字段里面,  MySQLtable_mapping 里面只有表名( person ):

create external table person1 (
       id int,
       name varchar(1023),
       age int
) tblproperties(
  table_mapping = 'person'
);

SQLServerPostgreSQLtable_mapping 里面则要有schema的名字和表名:

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

总结

Happy DLAing!

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
12天前
|
关系型数据库 MySQL 数据库
rds迁移数据迁移工具选择
rds迁移数据迁移工具选择
27 3
|
21天前
|
SQL 关系型数据库 MySQL
【MySQL进阶之路丨第十四篇】一文带你精通MySQL重复数据及SQL注入
【MySQL进阶之路丨第十四篇】一文带你精通MySQL重复数据及SQL注入
30 0
|
24天前
|
SQL 关系型数据库 MySQL
【MySQL】学习如何通过DML更新数据库的数据
【MySQL】学习如何通过DML更新数据库的数据
73 0
|
21天前
|
SQL 关系型数据库 MySQL
【MySQL进阶之路丨第十五篇】一文带你精通MySQL数据的导入与导出
【MySQL进阶之路丨第十五篇】一文带你精通MySQL数据的导入与导出
27 0
【MySQL进阶之路丨第十五篇】一文带你精通MySQL数据的导入与导出
|
20小时前
|
关系型数据库 MySQL Java
mysql批量添加数据
mysql批量添加数据
|
18天前
|
缓存 NoSQL 关系型数据库
Redis与MySQL的数据情感:延迟双删的秘密揭示
Redis与MySQL的数据情感:延迟双删的秘密揭示
27 0
|
19天前
|
SQL 关系型数据库 MySQL
mysql 中sql 语句查询今天、昨天、近7天、近30天、一个月内、上一月数据
mysql 中sql 语句查询今天、昨天、近7天、近30天、一个月内、上一月数据
|
19天前
|
SQL 分布式计算 关系型数据库
Dataphin实现MaxCompute外表数据快速批量同步至ADB MySQL
当前大数据时代背景下,企业对数据的处理、分析和实时应用的需求日益增强。阿里云MaxCompute广泛应用于海量数据的ETL、数据分析等场景,但在将处理后的数据进一步同步至在线数据库系统,如ADB MySQL 3.0(阿里云自研的新一代云原生关系型数据库MySQL版)以支持实时查询、业务决策等需求时,可能会遇到数据迁移速度缓慢的问题。 DataphinV3.14版本支持外表导入SQL的带参调度,实现通过MaxCompute外表的方式将数据批量同步至ADB MySQL 3.0中,显著提升数据迁移的速度和效率。
|
22天前
|
存储 缓存 关系型数据库
Mysql专栏 - 缓冲池补充、数据页、表空间简述
Mysql专栏 - 缓冲池补充、数据页、表空间简述
13 0
|
24天前
|
SQL 关系型数据库 MySQL
【MySQL】学习如何通过DQL进行数据库数据的条件查询
【MySQL】学习如何通过DQL进行数据库数据的条件查询
35 1

热门文章

最新文章