Small-scale Real-time Data Warehouse: How ApsaraDB RDS for PostgreSQL Supports mysql_fdw

This article explains creating a small-scale real-time data warehouse with ApsaraDB RDS for PostgreSQL and describes how it supports mysql_fdw to enable read and write access to MySQL.

By digoal

Background

ApsaraDB RDS for PostgreSQL 10 supports the mysql_fdw plug-in, which grants read and write permissions to other MySQL instances or user-defined MySQL instances in a PostgreSQL instance as long as the network is connected. For example, the PostgreSQL instance and the target library MySQL are in the same virtual private cloud (VPC).

ApsaraDB RDS for PostgreSQL 11 will support oracle_fdw, mysql_fdw, and mongo_fdw. So, PostgreSQL can perform read and write operations on more data sources.

PostgreSQL has supported parallel computing since PostgreSQL 9.6, and the performance has dramatically improved in PostgreSQL 11. For example, you can process 1 billion JOINs within seconds. Many users use PostgreSQL as a small data warehouse to achieve high-concurrency access. PostgreSQL 13 will also support columnar storage engines to offer improved analysis capabilities.

Example: mysql_fdw

For example, if you have an ApsaraDB RDS for MySQL instance, you need to create an ApsaraDB RDS for PostgreSQL instance for real-time analysis.

1) Apply for ApsaraDB RDS for PostgreSQL 10 (PostgreSQL 11 will be supported soon). Ensure that the selected VPC is consistent with the object library.

To access other instances on the internet, you can use an elastic compute service (ECS) in the same VPC for port redirection.

2) Create an extension.

postgres=> create extension mysql_fdw;  
CREATE EXTENSION

3) Configure the connection of the ApsaraDB RDS for MySQL instance.

postgres=> CREATE SERVER mysql_server  
postgres->      FOREIGN DATA WRAPPER mysql_fdw  
postgres->      OPTIONS (host 'rm-xxx.mysql.rds.aliyuncs.com', port '3306');  
CREATE SERVER

4) Map the MySQL link to a local PostgreSQL account for future access (from the local PostgreSQL). Assume that the local PostgreSQL user is dtstest and the MySQL user is test.

postgres=> CREATE USER MAPPING FOR dtstest   
SERVER mysql_server  
OPTIONS (username 'test', password 'Test123!');  
CREATE USER MAPPING

5) Use dtstest to create an external table of the MySQL server under the assumption that test123 in MySQL has a table named test that contains two fields.

postgres=> CREATE FOREIGN TABLE ft_test (id int, info text) server mysql_server options (dbname 'test123', table_name 'test');  
CREATE FOREIGN TABLE  
postgres=> select * from ft_test ;  
 id | info   
----+------  
  1 | abc  
(1 row)

6) Write data directly from PostgreSQL to the MySQL database. The MySQL table must have a primary key.

postgres=> insert into ft_test select generate_series(1,100),'abc';  
ERROR:  first column of remote table must be unique for INSERT/UPDATE/DELETE operation

7) Add the primary key to the MySQL table to write data into PostgreSQL.

postgres=> insert into ft_test values (2,'abc');  
INSERT 0 1  
  
  
postgres=> insert into ft_test select generate_series(3,100),'abc';  
INSERT 0 98  
postgres=> select count(*) from ft_test ;  
 count   
-------  
    99  
(1 row)

8) Check the execution. How is PostgreSQL's request to query MySQL table executed in MySQL?

postgres=> explain verbose select count(*) from ft_test ;  
                                  QUERY PLAN                                     
-------------------------------------------------------------------------------  
 Aggregate  (cost=1027.50..1027.51 rows=1 width=8)  
   Output: count(*)  
   ->  Foreign Scan on public.ft_test  (cost=25.00..1025.00 rows=1000 width=0)  
         Output: id, info  
         Remote server startup cost: 25  
         Remote query: SELECT NULL FROM `test123`.`test`  
(6 rows)  
  
postgres=> explain verbose select id from ft_test where id=2;  
                               QUERY PLAN                                  
-------------------------------------------------------------------------  
 Foreign Scan on public.ft_test  (cost=25.00..1025.00 rows=1000 width=4)  
   Output: id  
   Remote server startup cost: 25  
   Remote query: SELECT `id` FROM `test123`.`test` WHERE ((`id` = 2))  
(4 rows)

Community

Small-scale Real-time Data Warehouse: How ApsaraDB RDS for PostgreSQL Supports mysql_fdw

Background

Example: mysql_fdw

References

Read previous post:

Read next post:

digoal

You may also like

Comments

digoal

Related Products

Database Overview

PolarDB for PostgreSQL

Hologres

PolarDB for MySQL