AnalyticDB:Use external tables for federated analytics of external SQL databases

Configure a JDBC server

JDBC server configurations vary based on user requirements. To use JDBC to query data from external data sources for federated analytics, you must submit a ticket to request technical support to configure the specified JDBC server. The following table describes the information that is required to query data from external data sources.

Create an external table for federated analytics of an external SQL database

Create an extension

CREATE extension pxf; 

Create an external table

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

For more information about the syntax that is used to create an external table, see CREATE EXTERNAL TABLE.

Field	Description
path-to-data	The path to the data to be queried. Example: public.test_a.
PROFILE [&<custom-option>=<value>[...]]	The method to query external data. To use JDBC to query data from external databases, set PROFILE to Jdbc.
FORMAT '[TEXT|CSV|CUSTOM]'	The format of the file to be queried.
formatting-properties	The formatting option of the specified data format. Set this field to formatter or delimiter. Valid values when you set FORMAT to CUSTOM: formatter='pxfwritable_import' formatter='pxfwritable_export' Valid values when you set FORMAT to TEXT or CSV: delimiter=E'\t' delimiter ':' Note E is used to escape possible special characters.
SERVER	The location of the configuration file of the server, which is provided by the technical support of AnalyticDB for PostgreSQL.

Example: Query data from an external PostgreSQL database

After the JDBC server is configured, you can execute the following SQL statements in AnalyticDB for PostgreSQL to create an external table and query data from the specified external PostgreSQL database:

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

The following list describes the fields in the LOCATION parameter:

• pxf://: the PXF protocol, which cannot be modified.
• public.t: the t table in the public database to be queried.
• PROFILE=Jdbc: the method that is used to query external data. In this example, JDBC is used to query the external data source.
• SERVER=postgresql: the location of the configuration file of the server, which is provided by the technical support of AnalyticDB for PostgreSQL after you submit the ticket. In this example, SERVER=postgresql indicates that the file located in the PXF_SERVER/postgresql/ directory is used to configure the server.
• FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import'): the formatting option of the external data source. In this example, FORMAT is set to CUSTOM and FORMATTER is set to pxfwritable_import.

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

• INTEGER, BIGINT, SMALLINT
• REAL, FLOAT8
• NUMERIC
• BOOLEAN
• VARCHAR, BPCHAR, TEXT
• DATE
• TIMESTAMP
• BYTEA