Use JDBC external tables to query external SQL databases - AnalyticDB

AnalyticDB for PostgreSQL lets you query data from Oracle, PostgreSQL, and MySQL databases directly, using Java Database Connectivity (JDBC) and external tables — without moving the data into AnalyticDB for PostgreSQL first.

Prerequisites

Before you begin, make sure that:

Your AnalyticDB for PostgreSQL instance is in elastic storage mode.
The instance and the external database are in the same VPC.
Your instance was created on or after September 6, 2020. Instances created before that date cannot connect to external databases over different network architectures. To use this feature on an older instance, contact Alibaba Cloud technical support to apply for a new instance and migrate your data.

How it works

External tables use the PXF (Platform Extension Framework) protocol to route queries from AnalyticDB for PostgreSQL to an external database at query time. The workflow is:

Submit a ticket to have Alibaba Cloud technical support configure the JDBC server connection on your behalf.
Enable the PXF extension in your AnalyticDB for PostgreSQL database.
Create an external table that maps to a table in the external database.
Query the external table with standard SQL — AnalyticDB for PostgreSQL fetches the data from the external source at query time.

Configure a JDBC server

JDBC server configuration is handled by Alibaba Cloud technical support. Submit a ticket and provide the following information:

External data source	Required information
PostgreSQL, MySQL, or Oracle	The JDBC connection URL, the database username, and the database password

After the ticket is processed, technical support will provide a SERVER name that you use when creating external tables.

Create an external table

Step 1: Enable the PXF extension

Run the following statement once in your AnalyticDB for PostgreSQL database:

CREATE EXTENSION pxf;

Step 2: Create an external table

Use CREATE EXTERNAL TABLE to define a table that maps to a table in the external database:

CREATE EXTERNAL TABLE <table_name>
        ( <column_name> <data_type> [, ...] | LIKE <other_table> )
LOCATION('pxf://<path-to-data>? PROFILE=Jdbc[&<custom-option>=<value>[...]] &[SERVER=<server_name>]')
FORMAT '[TEXT|CSV|CUSTOM]' (<formatting-properties>);

For the full CREATE EXTERNAL TABLE syntax, see CREATE EXTERNAL TABLE.

LOCATION parameters

Parameter	Description
`path-to-data`	The schema and table name in the external database. Example: `public.test_a`
`PROFILE`	The method used to query external data. For JDBC connections, set this to `Jdbc`
`SERVER`	The location of the configuration file of the server, provided by Alibaba Cloud technical support after you submit the ticket

FORMAT options

When FORMAT is set to CUSTOM, set formatting-properties to one of the following values:

formatter='pxfwritable_import' — for reading data
formatter='pxfwritable_export' — for writing data

When FORMAT is set to TEXT or CSV, set formatting-properties to a delimiter:

delimiter=E'\t' — tab delimiter (E escapes special characters)
delimiter=':' — colon delimiter

Example: Query data from a PostgreSQL database

The following example creates an external table that maps to the t table in the public schema of an external PostgreSQL database, then queries it.

All statements use PROFILE=Jdbc and reference the postgresql server configuration provided by technical support. The SERVER=postgresql value tells PXF to use the configuration file in the PXF_SERVER/postgresql/ directory.

postgres=# CREATE EXTERNAL TABLE pxf_ext_pg(a int, b int)
  LOCATION ('pxf://public.t? PROFILE=Jdbc&SERVER=postgresql')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import')
ENCODING 'UTF8';

postgres=# SELECT * FROM pxf_ext_pg;
   a   |   b
-------+-------
     1 |     2
     2 |     4
     3 |     6
     4 |     8
     5 |    10
     6 |    12
     7 |    14
--more--

To map a different table — for example, public.test_a:

CREATE EXTERNAL TABLE pxf_ext_test_a(id int, name varchar)
  LOCATION ('pxf://public.test_a? PROFILE=Jdbc&server=postgresql')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import')
ENCODING 'UTF8';

Supported data types

The following AnalyticDB for PostgreSQL data types are supported for external table columns:

Category	Supported types
Integer	`INTEGER`, `BIGINT`, `SMALLINT`
Floating point	`REAL`, `FLOAT8`
Fixed precision	`NUMERIC`
Boolean	`BOOLEAN`
Character	`VARCHAR`, `BPCHAR`, `TEXT`
Date and time	`DATE`, `TIMESTAMP`
Binary	`BYTEA`