Querying ApsaraDB for RDS with Data Lake Analytics

Create a Database

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'
);

Create a Table

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);
create external table person (
id int,
name varchar(1023),
age int
) tblproperties (
table_mapping = "person"
);
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: Clean Data from OSS and Write it to ApsaraDB for RDS

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)

SQL Server and PostgreSQL

Create a Database

SQL Server

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'
);

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'
);

Create a Table

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

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store