:External data source access

Add an HDFS data source

Prerequisites

• A self-managed Hadoop cluster or an E-MapReduce (EMR) cluster that supports Hadoop Common, HDFS, and YARN is created in the same virtual private cloud (VPC) as an AnalyticDB for PostgreSQL instance.

• The IP address of the vSwitch that is connected to the AnalyticDB for PostgreSQL instance is added to a security group of the Hadoop cluster. All default port numbers of the Hadoop services that may be used by the external data source access feature are added to the security group. The following table describes the Hadoop services. For more information, see Add a security group rule.

  Service	Port number
  Namenode IPC	Hadoop 2.x: 8020 Hadoop 3.x: 9820 EMR: 9000 For more information about the port numbers, see the dfs.namenode.rpc-address parameter in the hdfs-site.xml file.
  DataNode IPC	Hadoop 2.x: 50020 Hadoop 3.x: 9867 For more information about the port numbers, see the dfs.datanode.ipc.address parameter in the hdfs-site.xml file.
  DataNode	Hadoop 2.x: 50010 Hadoop 3.x: 9866 For more information about the port numbers, see the dfs.datanode.address parameter in the hdfs-site.xml file.
  Hive metastore	9083 For more information about the port numbers, see the dfs.datanode.address parameter in the hive-site.xml file.
  Hive HiveServer	10000 For more information about the port numbers, see the hive.server2.thrift.port parameter in the hive-site.xml file.
  Zookeeper	2181 For more information about the port numbers, see the clientPort parameter in the zoo.cfg file.
  Kerberos Server	88 (transmission encryption and authentication) and 749 (kadmin) For more information about the port numbers, see the kdc_ports and kadmind_port parameters in the kdc.conf file.
  JDBC (MySQL or PostgreSQL)	3306, 1521, and 5432
  Internet Control Message Protocol (ICMP)	For network connectivity purposes, a security group rule is added to allow inbound and outbound ICMP traffic.

  Note

  To obtain the IP address of the vSwitch that is connected to the AnalyticDB for PostgreSQL instance, go to the External Data Source Management page.

Prepare a test dataset

Prepare a test dataset and run the hadoop fs -put <File name> <Hadoop cluster path> command to store the dataset in the Hadoop cluster path.

For example, to upload the /home/user/file.txt file to the /tmp directory of HDFS, run the hadoop fs -put /home/user/file.txt /tmp/file.tx command. Sample dataset:

1,test1,1,100
2,test2,2,90

Procedure

1. On the External Data Service Access tab, select Hadoop Data Service from the Add Data Source drop-down list.

2. In the Add Hadoop Data Source panel, configure the parameters in the Network & Security Group, Configuration Files, and Initialization steps.

   1. Configure the parameters in the Network & Security Group step. The following table describes the parameters.

      Parameter	Description
      Data Source Name	The name can contain letters, digits, hyphens (-), and underscores (_). The name must start and end with a letter or digit. The name can be up to 50 characters in length.
      Data Source Description	The description of the data source.
      Data Source Type	If the data source is an EMR cluster, select E-MapReduce (EMR). If the data source is a self-managed Hadoop cluster, select Self-managed Hadoop Cluster.
      Database Engine	The database engine. Select HDFS.
      Data Source	The ID of the EMR cluster. Note This parameter is not supported for self-managed Hadoop clusters.
      Network Check	Click Network Check. A message indicating that the network is connected appears.

   2. Click Next and configure the parameters in the Configuration Files step. The following table describes the parameters.

      Parameter	Description	Method to obtain
      Parameter Configuration	None	If the data source is an EMR cluster, click Obtain Automatic Configuration. If the data source is a self-managed Hadoop cluster, you must paste the configuration file information.
      hdfs-site.xml	The HDFS configuration parameters, such as the replication factor and block size.	Obtain the .xml files of self-managed Hadoop clusters from one of the following directories: conf directory: suitable for earlier versions of Hadoop. etc/hadoop directory: suitable for later versions of Hadoop. This directory is located in the main installation directory of Hadoop.
      core-site.xml	The Hadoop configuration files, such as the I/O settings and file system configuration.
      yarn-site.xml	The YARN configuration parameters that are used for cluster resource management and job scheduling.
      mapred-site.xml	The EMR job parameters, such as the job executor and optimization parameters.
      /etc/host	Resolves the hostnames of all Hadoop cluster nodes to IP addresses.	Use one of the following methods to obtain the file content: Method 1: Log on to each host of the Hadoop cluster to obtain the mapping between the IP address and the hostname of the current node from the /etc/hosts file. Method 2: Log on to the host where a Hadoop NameNode is located and run the #!/bin/bash command. Sample result: echo $(hostname -i) $(hostname -f) hdfs dfsadmin -report | awk -F':' '/^Name:/ {printf $2} /^Hostname:/ {print $2}'

   3. Click Save and Finish.

      Wait 3 minutes to 5 minutes. When the status of the data source changes to Running, the HDFS data source is available.

3. Find the data source and click Check in the Actions column.

4. In the Check Service Configuration dialog box, enter the Hadoop cluster path of the dataset, such as /tmp/file.txt, and click Check.

   If the check is successful, you can read and write HDFS external tables.

Add a Hive data source

Prerequisites

• A self-managed Hive cluster or an EMR cluster that supports Hadoop Common, HDFS, Hive, and YARN is created in the same VPC as an AnalyticDB for PostgreSQL instance.

• The IP address of the vSwitch that is connected to the AnalyticDB for PostgreSQL instance is added to a security group of the Hive cluster. For more information, see Add a security group rule.

  Note

  To obtain the IP address of the vSwitch that is connected to the AnalyticDB for PostgreSQL instance, go to the External Data Source Management page.

Prepare a test dataset

Prepare a test dataset and run the hadoop fs -put <File name> <Hadoop cluster path> command to store the dataset in the Hadoop cluster path.

For example, to upload the /home/user/file.txt file to the /tmp directory of Hive, run the hadoop fs -put /home/user/file.txt /tmp/file.tx command. Sample dataset:

1,test1,1,100
2,test2,2,90

Procedure

1. On the External Data Service Access tab, select Hadoop Data Service from the Add Data Source drop-down list.

2. In the Add Hadoop Data Source panel, configure the parameters in the Network & Security Group, Configuration Files, and Initialization steps.

   1. Configure the parameters in the Network & Security Group step. The following table describes the parameters.

      Parameter	Description
      Data Source Name	The name can contain letters, digits, hyphens (-), and underscores (_). The name must start and end with a letter or digit. The name can be up to 50 characters in length.
      Data Source Description	The description of the data source.
      Data Source Type	If the data source is an EMR cluster, select E-MapReduce (EMR). If the data source is a self-managed Hive cluster, select Self-managed Hadoop Cluster.
      Database Engine	The database engine. Select HIVE.
      Data Source	The ID of the EMR cluster. Note This parameter is not supported for self-managed Hive clusters.
      Network Check	Click Network Check. A message indicating that the network is connected appears.

   2. Click Next and configure the parameters in the Configuration Files step. The following table describes the parameters.

      Parameter	Description	Method to obtain
      Parameter Configuration	None	If the data source is an EMR cluster, click Obtain Automatic Configuration. If the data source is a self-managed Hive cluster, you must paste the configuration file information.
      hdfs-site.xml	The HDFS configuration parameters, such as the replication factor and block size.	Obtain the .xml files of self-managed Hive clusters from one of the following directories: conf directory: suitable for the standard installation. etc/hadoop directory: suitable for the installation that integrates Hadoop and Hive.
      core-site.xml	The Hadoop configuration files, such as the I/O settings and file system configuration.
      yarn-site.xml	The YARN configuration parameters that are used for cluster resource management and job scheduling.
      mapred-site.xml	The EMR job parameters, such as the job executor and optimization parameters.
      hive-site.xml	The Hive configuration parameters, such as the metadata connection information, Hive memory, and Hive performance.
      /etc/host	Resolves the hostnames of all Hive cluster nodes to IP addresses.	Use one of the following methods to obtain the file content: Method 1: Log on to each host of the Hive cluster to obtain the mapping between the IP address and the hostname of the current node from the /etc/hosts file. Method 2: Log on to the host where a Hive NameNode is located and run the #!/bin/bash command. Sample result: echo $(hostname -i) $(hostname -f) hive dfsadmin -report | awk -F':' '/^Name:/ {printf $2} /^Hostname:/ {print $2}'

   3. Click Save and Finish.

      Wait 3 minutes to 5 minutes. When the status of the data source changes to Running, the Hive data source is available.

3. Find the data source and click Check in the Actions column.

4. In the Check Service Configuration dialog box, enter the Hadoop cluster path of the dataset, such as /tmp/file.txt, and click Check.

   If the check is successful, you can read and write Hive external tables.

Add a JDBC data source

A JDBC data source is a data source that is compatible with MySQL and PostgreSQL, such as ApsaraDB RDS for MySQL, ApsaraDB RDS for PostgreSQL, PolarDB for MySQL, and PolarDB for PostgreSQL.

In this example, an ApsaraDB RDS for MySQL data source is used.

Prerequisites

• An ApsaraDB RDS for MySQL instance is created in the same VPC as an AnalyticDB for PostgreSQL instance.

• Databases and accounts are created in the ApsaraDB RDS for MySQL instance. For more information, see Create accounts and databases.

• The IP address of the vSwitch that is connected to the AnalyticDB for PostgreSQL instance is added to a whitelist of the ApsaraDB RDS for MySQL instance. For more information, see Configure an IP address whitelist.

  Note

  To obtain the IP address of the vSwitch that is connected to the AnalyticDB for PostgreSQL instance, go to the External Data Source Management page.

Procedure

1. On the External Data Service Access tab, select JDBC Data Service from the Add Data Source drop-down list.

2. In the Add JDBC Data Source panel, configure the parameters that are described in the following table.

   Parameter	Description
   Data Source Name	The name can contain letters, digits, hyphens (-), and underscores (_). The name must start and end with a letter or digit. The name can be up to 50 characters in length.
   Database Engine	The database engine. Select MySQL.
   Data Source Description	The description of the data source.
   JDBC Connection String	The JDBC connection string that is used to connect to the ApsaraDB RDS for MySQL instance. Format: jdbc:mysql://<servername>:<port>/<dbName> <servername>:<port>: the endpoint and port number of the ApsaraDB RDS for MySQL instance. If you use an internal connection, enter the internal endpoint and internal port number of the ApsaraDB RDS for MySQL instance. If you use a public connection, enter the public endpoint and public port number of the ApsaraDB RDS for MySQL instance. To obtain the endpoint and port number, go to the Database Connection page of the ApsaraDB RDS for MySQL instance. For more information, see View and change the endpoints and port numbers. <dbName>: the name of the database in the ApsaraDB RDS for MySQL instance.
   Username	The database account of the ApsaraDB RDS for MySQL instance.
   Logon Password	The password of the database account.

3. Click Test Connection. If the network connection is successful, click OK.

   If the network connection fails, check whether the JDBC connection string is correct.

   Wait 3 minutes to 5 minutes. When the status of the data source changes to Running, the JDBC data source is available. You can read and write JDBC external tables.

HDFS external tables

Read and write text data

Syntax

CREATE [WRITABLE | READABLE] EXTERNAL TABLE <table_name>
    ( <column_name> <data_type> [, ...] | LIKE <other_table> )
LOCATION ('pxf://<path-to-hdfs-dir>
    ?PROFILE=hdfs:text[&SERVER=<server_name>][&<custom-option>=<value>[...]]')
FORMAT '[TEXT|CSV]' (delimiter[=|<space>][E]'<delim_value>');
[DISTRIBUTED BY (<column_name> [, ... ] ) | DISTRIBUTED RANDOMLY];

Parameters

Examples

• Example 1: Create a writable external table. In the CREATE TABLE statement, specify the HDFS protocol, data format, and path. Insert data into the external table.

  CREATE WRITABLE EXTERNAL TABLE pxf_hdfs_writable_table
  ( 
  a int,
  b int,
  c int
  )
  LOCATION ('pxf://data/demo/pxf_hdfs_demo_table?PROFILE=hdfs:text&SERVER=test2')
  FORMAT 'TEXT' (delimiter='|');
  
  INSERT INTO pxf_hdfs_writable_table 
  SELECT generate_series(1, 9000), generate_series(1, 9000), generate_series(1, 9000);

• Example 2: Create a readable external table. In the CREATE TABLE statement, specify the HDFS protocol, data format, and path. Query data of the external table.

  CREATE READABLE EXTERNAL TABLE pxf_hdfs_readable_table
  ( 
  a int,
  b int,
  c int
  )
  LOCATION ('pxf://data/demo/pxf_hdfs_demo_table?PROFILE=hdfs:text&SERVER=test2')
  FORMAT 'TEXT' (delimiter='|');
  
  SELECT * FROM pxf_hdfs_readable_table order by 1;

Read and write Parquet data

Syntax

CREATE [WRITABLE |READABLE] EXTERNAL TABLE <table_name>
    ( <column_name> <data_type> [, ...] | LIKE <other_table> )
LOCATION ('pxf://<hdfs_dbName>.<hdfs_tableName>
    ?PROFILE=hdfs:parquet[&SERVER=<server_name>][&<custom-option>=<value>[...]]')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import'|'pxfwritable_export');
[DISTRIBUTED BY (<column_name> [, ... ] ) | DISTRIBUTED RANDOMLY];

Parameters

The hdfs:parquet profile supports encoding- and compression-related write options. You can specify the write options in the CREATE WRITABLE EXTERNAL TABLE LOCATION clause. The following table describes the custom write options.

Examples

• Example 1: Create a writable external table. In the CREATE TABLE statement, specify the HDFS protocol, Parquet data format, and path. Insert data into the external table.

  CREATE WRITABLE EXTERNAL TABLE pxf_hdfs_writable_parquet_table
  ( 
  a int,
  b int,
  c int
  )
  LOCATION ('pxf://data/demo/pxf_hdfs_writable_parquet_table?PROFILE=hdfs:parquet&SERVER=test2')
  FORMAT 'CUSTOM' (FORMATTER='pxfwritable_export');
  
  INSERT INTO pxf_hdfs_writable_parquet_table 
  SELECT generate_series(1, 9000), generate_series(1, 9000), generate_series(1, 9000);

• Example 2: Create a readable external table. In the CREATE TABLE statement, specify the HDFS protocol, Parquet data format, and path. Query data of the external table.

  CREATE READABLE EXTERNAL TABLE pxf_hdfs_readable_parquet_table
  ( 
  a int,
  b int,
  c int
  )
  LOCATION ('pxf://data/demo/pxf_hdfs_writable_parquet_table?PROFILE=hdfs:parquet&SERVER=test2')
  FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');
  
  SELECT * FROM pxf_hdfs_readable_parquet_table;

Hive external tables

To display Hive data in AnalyticDB for PostgreSQL, you must map the Hive data values to AnalyticDB for PostgreSQL table columns of the same type. For information about the mappings, see the "Appendix 4: Mappings between Hive data types and AnalyticDB for PostgreSQL data types" section of this topic.

Syntax

Writable and readable Hive external tables support different features. Select the appropriate SQL syntax based on your business requirements.

• Writable external tables

  The underlying logic of the Hive data write interface significantly changes as the version iterates. Errors may occur when you create or write Hive external tables of later versions. We recommend that you create and write external tables in HDFS mode. For more information about the syntax, see the "HDFS external tables" section of this topic.

• Readable external tables

  CREATE [READABLE] EXTERNAL TABLE <table_name>
      ( <column_name> <data_type> [, ...] | LIKE <other_table> )
  LOCATION ('pxf://<hive-db-name>.<hive-table-name>
      ?PROFILE=Hive|HiveText|HiveRC|HiveORC|HiveVectorizedORC[&SERVER=<server_name>]'])
  FORMAT 'CUSTOM|TEXT' (FORMATTER='pxfwritable_import' | delimiter='<delim>')

Parameters

Examples

1. Connect to the Hive cluster and create a Hive table.

   hive> CREATE EXTERNAL TABLE IF NOT EXISTS pxf_hive_demo_table
   (
   a int,
   b int,
   c int
   )
   row format delimited fields terminated by '|'
   stored as textfile
   location '/data/demo/pxf_hdfs_demo_table';

2. Connect to the AnalyticDB for PostgreSQL database, create a writable Hive external table, and then insert data into the external table.

   postgres> CREATE WRITABLE EXTERNAL TABLE pxf_hive_writable_table
   ( 
   a int,
   b int,
   c int
   )
   LOCATION ('pxf://default.pxf_hive_demo_table?PROFILE=Hive&SERVER=test2')
   FORMAT 'TEXT' (delimiter='|');
   
   INSERT INTO pxf_hive_writable_table 
   SELECT generate_series(1, 9000), generate_series(1, 9000), generate_series(1, 9000);

3. Create a readable Hive external table and query data of the external table.

   postgres> CREATE EXTERNAL TABLE pxf_hive_readable_table
   ( 
   a int,
   b int,
   c int
   )
   LOCATION ('pxf://default.pxf_hive_demo_table?PROFILE=Hive&SERVER=test2')
   FORMAT 'TEXT' (delimiter='|');
   
   SELECT * FROM pxf_hive_readable_table;

JDBC external tables

Supported data types

The JDBC connector supports the following data types:

• INTEGER, BIGINT, and SMALLINT

• REAL and FLOAT8

• NUMERIC

• BOOLEAN

• VARCHAR, BPCHAR, and TEXT

• DATE

• TIMESTAMP

• BYTEA

Syntax

You can use the following syntax to create an external table and use the JDBC connector to read or write data. To access data in a remote SQL database, you can create a readable or writable

AnalyticDB for PostgreSQL

external table that references the remote database table.

The AnalyticDB for PostgreSQL

external table and the remote database table or query result tuple must have the same column names and types.

CREATE [READABLE | WRITABLE] EXTERNAL TABLE <table_name>
    ( <column_name> <data_type> [, ...] | LIKE <other_table> )
LOCATION ('pxf://<external_tableName>?PROFILE=Jdbc[&SERVER=<server_name>][&<custom-option>=<value>[...]]')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import'|'pxfwritable_export');

Parameters

JDBC custom options

You can include JDBC connector custom options in the LOCATION URI and add an ampersand (&) before each option. The following table describes the supported JDBC custom options.

&PARTITION_BY=id:int&RANGE=1:100&INTERVAL=5
&PARTITION_BY=year:int&RANGE=2011:2013&INTERVAL=1
&PARTITION_BY=createdate:date&RANGE=2013-01-01:2016-01-01&INTERVAL=1:month
&PARTITION_BY=color:enum&RANGE=red:yellow:blue

Examples

1. Create a table in a MySQL or PostgreSQL database and insert data into the table.

CREATE TABLE test(id int, name VARCHAR(255));
INSERT INTO test(id,name) VALUES(1,'qingzhi');
INSERT INTO test(id,name) VALUES(2,'jianli');

2. Connect to an AnalyticDB for PostgreSQL database, create a JDBC external table, and then query data of the external table.

CREATE EXTERNAL TABLE pxf_jdbc_textsimple(id int, name varchar)
LOCATION ('pxf://test?PROFILE=Jdbc&SERVER=test01')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import')
ENCODING 'UTF8';
SELECT * FROM pxf_jdbc_textsimple;