数据写入RDS

本文将以在DLA中读取OSS数据，并将符合条件的数据写入RDS for MySQL为例，介绍如何通过DLA向RDS中写入数据。

操作步骤

步骤一：创建OSS Schema

CREATE DATABASE tpch_50x_text
WITH DBPROPERTIES (
catalog = 'oss',
location = 'oss://${您的bucket}/datasets/tpch/50x/text_date/'
)

步骤二：创建OSS表customer表

CREATE EXTERNAL TABLE tpch_50x_text.customer (
c_custkey int,
c_name string
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
STORED AS TEXTFILE
LOCATION 'oss://${您的bucket}/datasets/tpch/50x/text_date/customer_text'

查询customer表数据：

mysql> select * from customer;
+------+--------------------+
| c_custkey  | c_name
+------+--------------------+
|    1 | Customer#000000001 |
|    3 | Customer#000000003 |
|    5 | Customer#000000005 |
|    2 | Customer#000000002 |
|    4 | Customer#000000004 |
|    7 | Customer#000000007 |
|    6 | Customer#000000006 |
|    9 | Customer#000000009 |
|    8 | Customer#000000008 |
|   10 | Customer#000000010 |
+------+--------------------+
10 rows in set (0.26 sec)

步骤三：创建RDS Schema

 CREATE SCHEMA hello_mysql_vpc_rds WITH DBPROPERTIES (
   CATALOG = 'mysql', 
   LOCATION = 'jdbc:mysql://rm-****.mysql.rds.aliyuncs.com:3306/rds_mysql_dbname',
   USER = 'rds_mysqldb_username',
   PASSWORD = 'rds_mysqldb_password',
   INSTANCE_ID = 'rds_mysql_instance_id',
   VPC_ID = 'rds_mysqldb_vpcid'
 );

步骤四：创建RDS表person表

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

向person表中插入数据：

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

步骤五：将OSS中的数据导入RDS

通过以下SQL，将oss_db中customer的十条记录进行了一些转换，然后插入hello_mysql_vpc_rds.person表。

mysql> insert into hello_mysql_vpc_rds.person
    select c_custkey, c_name, c_custkey + 20 from tpch_50x_text.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)