Querying ApsaraDB for RDS with Data Lake Analytics

Create a Database

The following describes the syntax used to create an external table whose underlying layer is mapped to MySQL in DLA:

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

After creating a database, create the table person in your ApsaraDB for RDS instance for test:

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

DLA is not typically used to read data from an ApsaraDB for RDS instance for analysis because an ApsaraDB for RDS instance contains a limited amount of data and is not applicable for massive data analysis. Instead, DLA is often used to analyze massive data in OSS and Table Store instances and write back the result data to ApsaraDB for RDS instances for frontend business. This is easily implemented in DLA. The table person is used as an example in the following statement to convert 10 records of customer in oss_db and insert them into the table 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)

SQL Server and PostgreSQL

Usage of SQLServer and PostgreSQL is similar to that of MySQL. As database and schemain MySQL are equivalent, they are at the same layer. However, database and schema in SQLServer and PostgreSQL are at two layers. Therefore, the methods for creating a database and a table are different:

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'
);
  1. Set CATALOG to sqlserver.
  2. Specify the database name for LOCATION. Different from the database name for MySQL, the database name for SQLServer is specified using DatabaseName=dla_test. This is defined by the SQLServer JDBC URL rather than 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'
);

Create a Table

The difference for creating a table is in the table_mapping field. table_mapping of MySQLonly contains the table name (person):

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
Alibaba Cloud

Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com