MaxCompute:ApsaraDB for MySQL foreign tables

Introduction

ApsaraDB Relational Database Service (RDS) is a relational database service from Alibaba Cloud that is typically accessed using an internal endpoint. You can use MaxCompute to load data into ApsaraDB RDS tables and perform read and write operations.

Scope

• Region restrictions: This feature is available only in the following regions: China (Beijing), China (Shanghai), China (Zhangjiakou), China (Ulanqab), China (Hangzhou), China (Shenzhen), China (Hong Kong), China (Shanghai) Finance Cloud (Zone F), Japan (Tokyo), Singapore, Malaysia (Kuala Lumpur), Indonesia (Jakarta), Germany (Frankfurt), US (Silicon Valley), and US (Virginia).

• Engine restrictions: This feature supports only ApsaraDB for MySQL 5.x and 8.0. Other ApsaraDB RDS engines are not supported.

• PrivateZone domain names are not supported.

• ApsaraDB RDS foreign tables do not support the cluster property.

• When you write a large amount of data to an ApsaraDB RDS foreign table, the system uses parallel multi-process writing. This may cause a write process to rewrite data and result in data duplication.

• Decimal place restrictions: For ApsaraDB RDS foreign tables that are created in MaxCompute, the DECIMAL data type defaults to 18 decimal places and cannot be changed. You can create the data type only as DECIMAL(38,18). If you require fewer decimal places, select the STRING data type when you create the foreign table in MaxCompute. Then, you can use the CAST function to convert the data type when you use the data.

Notes

If the schema of the ApsaraDB for MySQL source table does not match the schema of the foreign table, take note of the following items:

• Mismatched column count: If the number of columns in the ApsaraDB for MySQL source table is less than the number of columns in the Data Definition Language (DDL) statement of the foreign table, the system reports an error when reading data from ApsaraDB RDS. An example error message is Unknown column 'xxx' in 'field list'. If the number of columns in the ApsaraDB for MySQL source table is greater than the number of columns in the DDL statement of the foreign table, the system discards the extra columns.

• Mismatched column types: MaxCompute does not support mapping a STRING column from an ApsaraDB RDS source table to an INT column in the foreign table. Mapping an INT source column to a STRING foreign table column is supported but not recommended.

Create a foreign table

-- Enable Hive-compatible mode.
SET odps.sql.hive.compatible = true;
CREATE EXTERNAL TABLE <table_name>(
  <col_name1> <data_type>,
  <col_name2> <data_type>,
  ......
)
STORED BY 'com.aliyun.odps.jdbc.JdbcStorageHandler'  -- The handler for JDBC data sources.
location '<jdbc:mysql://<realm_name:port>/<rds_database_name>?useSSL=false&user=<user_name>&password=<password_value>&table=<rds_table_name>>' 
TBLPROPERTIES(
   ['odps.federation.jdbc.colmapping'='<col_name1:rdstable_colname1|select_alias1>,[<col_name2:rdstable_colname2|select_alias2>,...]',]
   'mcfed.mapreduce.jdbc.input.query'='<select_sentence>',
   'networklink'='<networklink_name>');

Create a MaxCompute foreign table and load data from an ApsaraDB RDS data source

To create a MaxCompute foreign table from an ApsaraDB RDS data source, perform the following steps:

1. Establish network connectivity between MaxCompute and ApsaraDB RDS. For more information, see Access the public network.

   After the network is connected, MaxCompute can connect only to the network of the specified VPC. To access other regions or other VPCs in the same region, you must establish a network connection between the VPC that is specified for the leased line connection and the other VPCs based on your cloud VPC connection solution.

2. Log on to the ApsaraDB RDS database, execute a CREATE TABLE statement, and insert data into the table. For more information, see Log on to an ApsaraDB for MySQL instance using DMS.

   1. Log on to the RDS console.

   2. In the navigation pane on the left, click Instances. Then, select a region in the upper-left corner.

   3. If you do not have an instance, click Create Instance on the Instances page. If you have an instance, click the Instance ID/Name of the target instance on the Instances page to go to the instance details page.

      When you create an instance, you must set the ApsaraDB RDS engine to ApsaraDB for MySQL 5.x or 8.0. Other ApsaraDB RDS engines are not supported.

   4. In the left navigation pane, click Databases.

   5. Click Create Database. Configure the following parameters:

      Parameter	Required	Description	Example
      Database Name	Required	The name must be 2 to 64 characters in length. The name must start with a letter and end with a letter or a digit. The name can contain lowercase letters, digits, underscores (_), and hyphens (-). The database name must be unique within the instance. If the database name contains -, the - in the database folder name is changed to @002d.	rds_mc_test
      Supported Character Set	Required	Select a character set as needed.	utf8
      Authorized Account	Optional	Select the account that needs to access this database. You can leave this parameter empty and then attach an account after the database is created. Only standard accounts are displayed here. Privileged accounts have all permissions on all databases and do not require authorization.	Default
      Description	Optional	Enter remarks about the database for easier database management. The remarks can be up to 256 characters in length.	ApsaraDB RDS foreign table test database

   6. Click Log On to Database. In the left navigation pane, select Database Instances. Double-click the database that you created. On the SQLConsole page, execute the following statements to create a test table and write test data.

      If the instance exists but the target database is not displayed after you expand the instance, the reason may be one of the following:

      • The logon account does not have access to the target database: You can go to the Accounts page of the RDS instance to modify the account permissions or change the logon database account.

      • The metadata is not synchronized, which prevents the directory from being displayed: Hover the mouse pointer over the instance that contains the target database. Click the button to the right of the instance name to refresh the database list.

   7. The following sample code provides an example of a CREATE TABLE statement:

      CREATE TABLE `rds_mc_external` (
        `id` int(11) DEFAULT NULL,
        `name` varchar(32) DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      INSERT INTO `rds_mc_external`(`id` ,`name` ) VALUES(1,"Alice");
      INSERT INTO `rds_mc_external`(`id` ,`name` ) VALUES(1,"Bob");

3. Create a foreign table in the MaxCompute client to map to the ApsaraDB RDS data source.

   Method 1: Map all columns

   1. Create a foreign table in the MaxCompute client. The column names in the table must be the same as the column names in the ApsaraDB RDS table. The following sample code provides an example:

      SET odps.sql.hive.compatible = true;
      
      CREATE EXTERNAL TABLE mc_vpc_rds_external (
      id INT,
      name STRING)
      STORED BY 'com.aliyun.odps.jdbc.JdbcStorageHandler'
      location 'jdbc:mysql://rm-2ze01y92y1tzp****.mysql.rds.aliyuncs.com:3306/rds_mc_test?useSSL=false&user=<>&password=<>&table=rds_mc_external'
      TBLPROPERTIES(
        'odps.federation.jdbc.colmapping'='key:id,value:name',
        'mcfed.mapreduce.jdbc.input.query'='select * from rds_mc_test.rds_mc_external',
        'networklink'='<your network name>');

   2. Insert data into the new MaxCompute table.

      INSERT INTO TABLE mc_vpc_rds_external VALUES(2,"Zoey");

   3. Query the result.

      -- Query the data insertion result.
      SELECT * FROM mc_vpc_rds_external;
      
      -- The following result is returned:
      +------------+------------+
      | id         | name       | 
      +------------+------------+
      | 1          | Alice      | 
      | 1          | Bob        | 
      | 2          | Zoey       |  
      +------------+------------+

   Method 2: Map specific columns

   1. Create a foreign table in the MaxCompute client. The column names in the table map to the specified column names in the ApsaraDB RDS table. The following sample code provides an example:

      SET odps.sql.hive.compatible = true;
      
      CREATE EXTERNAL TABLE mc_vpc_rds_external_mapping (
        id INT,
        name STRING
      )
      STORED BY 'com.aliyun.odps.jdbc.JdbcStorageHandler'
      location 'jdbc:mysql://rm-2ze01y92y1tzp****.mysql.rds.aliyuncs.com:3306/rds_mc_test?useSSL=false&user=****&password=****&table=rds_mc_external'
      TBLPROPERTIES(
        'mcfed.mapreduce.jdbc.input.query'='select * from rds_mc_test.rds_mc_external',
        'networklink'='<your network name>');

   2. Insert data into the new MaxCompute table.

      INSERT INTO TABLE mc_vpc_rds_external_mapping VALUES(4,"Lisa");

   3. Query the data insertion result.

      SELECT * FROM mc_vpc_rds_external_mapping;
      
      -- The following result is returned:
      +------------+------------+
      | id         | name       | 
      +------------+------------+
      | 1          | Alice      | 
      | 1          | Bob        | 
      | 4          | Lisa       | 
      +------------+------------+