全部产品
云市场

数据写入RDS

更新时间:2019-04-12 10:26:21

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

操作步骤

步骤一:创建OSS Schema

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

步骤二:创建OSS表customer表

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

查询customer表数据:

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

步骤三:创建RDS Schema

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

步骤四:创建RDS表person表

  1. create external table person (
  2. id int,
  3. name varchar(1023),
  4. age int
  5. ) tblproperties (
  6. table_mapping = "person"
  7. );

向person表中插入数据:

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

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

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

  1. mysql> insert into hello_mysql_vpc_rds.person
  2. select c_custkey, c_name, c_custkey + 20 from tpch_50x_text.customer limit 10;
  3. +------+
  4. | rows |
  5. +------+
  6. | 10 |
  7. +------+
  8. 1 row in set (4.57 sec)
  9. mysql> select * from person;
  10. +------+--------------------+------+
  11. | id | name | age |
  12. +------+--------------------+------+
  13. | 1 | james | 10 |
  14. | 2 | bond | 20 |
  15. | 3 | jack | 30 |
  16. | 4 | lucy | 40 |
  17. | 1 | Customer#000000001 | 21 |
  18. | 3 | Customer#000000003 | 23 |
  19. | 5 | Customer#000000005 | 25 |
  20. | 2 | Customer#000000002 | 22 |
  21. | 4 | Customer#000000004 | 24 |
  22. | 7 | Customer#000000007 | 27 |
  23. | 6 | Customer#000000006 | 26 |
  24. | 9 | Customer#000000009 | 29 |
  25. | 8 | Customer#000000008 | 28 |
  26. | 10 | Customer#000000010 | 30 |
  27. +------+--------------------+------+
  28. 14 rows in set (0.26 sec)