All Products
Search

This Product

    Data Lake Analytics - Deprecated:Insert data into ApsaraDB for RDS

    Document Center

    Data Lake Analytics - Deprecated:Insert data into ApsaraDB for RDS

    Last Updated:May 09, 2019

    This topic describes how to read specified data from OSS and insert the data into ApsaraDB for RDS (RDS) by using DLA. The following code uses ApsaraDB RDS for MySQL (MySQL) as an example.

    Procedure

    Step 1: Create an OSS schema

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

    Step 2: Create a table named customer in OSS

    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://${your bucket}/datasets/tpch/50x/text_date/customer_text'

    Query data in the customer table.

    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)

    Step 3: Create an 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.  );

    Step 4: Create a table named person in RDS

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

    Insert data into the person table.

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

    Step 5: Import data from OSS to RDS

    Execute the following SQL statements to convert the first 10 records from the customer table in oss_db, and then insert them into the hello_mysql_vpc_rds.person table:

    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)