通过外表将MaxCompute的数据导入至数仓版_云原生数据仓库AnalyticDB(AnalyticDB)-阿里云帮助中心

前提条件

MaxCompute项目与AnalyticDB for MySQL集群位于同一地域。具体操作，请参见创建集群。
已添加AnalyticDB for MySQL的VPC网段到MaxCompute项目的白名单中。
登录云原生数据仓库AnalyticDB MySQL控制台，在集群信息页面查询VPC ID。然后登录专有网络控制台，在专有网络页面根据VPC ID查询网段。设置MaxCompute白名单的操作，请参见管理IP白名单。

使用Storage API方式访问并导入MaxCompute数据时，还需满足以下条件：
- 已提交Storage API功能的试用申请，并获得试用资格。详情请参见试用申请。
- 集群内版本需为3.1.10.2及以上版本。
- 集群所在地域为华东1（杭州）、华东2（上海）、华南1（深圳）或华北2（北京）。

功能介绍

AnalyticDB for MySQL集群默认使用Tunnel方式访问MaxCompute数据，3.1.10.2及以上内核版本集群支持使用Storage API方式访问MaxCompute数据。相较于Tunnel方式，Storage API方式可以缩短生成查询计划的耗时和将MaxCompute外表数据导入AnalyticDB for MySQL内表的耗时，提升OLAP查询的查询性能和数据导入性能。

Storage API方式与Tunnel方式的区别如下：

访问方式	集群版本	地域	费用	数据访问效率

访问方式	集群版本	地域	费用	数据访问效率
Tunnel方式	无限制	无限制	免费	使用公共数据传输服务资源组，该资源会被该地域所有项目共享使用，数据访问和导入速度慢。
Storage API方式	3.1.10.2及以上版本	华东1（杭州）华东2（上海）华南1（深圳）华北2（北京）	需购买独享数据传输服务资源组，会产生费用。独享数据传输服务资源组的计费规则请参见独享数据传输服务资源组定价。	使用独享数据传输服务资源组，为项目配置后，该资源仅供当前项目使用，减少数据访问和导入时间，提供更高的数据传输速度。

示例数据

本文示例中的MaxCompute项目为odps_project1，示例表odps_nopart_import_test。示例如下：

CREATE TABLE IF NOT EXISTS odps_nopart_import_test (
    id int,
    name string,
    age int)
partitioned by (dt string);

在odps_nopart_import_test表中添加分区，示例如下：

ALTER TABLE odps_nopart_import_test 
ADD 
PARTITION (dt='202207');

向分区中添加数据，示例如下：

INSERT INTO odps_project1.odps_nopart_import_test 
PARTITION (dt='202207') 
VALUES (1,'james',10),(2,'bond',20),(3,'jack',30),(4,'lucy',40);

Tunnel方式访问MaxCompute

连接目标AnalyticDB for MySQL集群。详细操作步骤，请参见连接集群。
创建目标数据库。
```
CREATE database test_adb;
```

创建MaxCompute外表。本文以odps_nopart_import_test_external_table为例。

CREATE TABLE IF NOT EXISTS odps_nopart_import_test_external_table
(
    id int,
    name string,
    age int,
    dt string
)
 ENGINE='ODPS'
 TABLE_PROPERTIES='{
 "endpoint":"http://service.cn.maxcompute.aliyun-inc.com/api",
 "accessid":"L*******FsE",
 "accesskey":"CcwF********iWjv",
 "partition_column":"dt",
 "project_name":"odps_project1",
 "table_name":"odps_nopart_import_test"
 }';

参数	说明

参数	说明
`ENGINE=’ODPS’`	外表的存储引擎。读写MaxCompute数据时，取值为ODPS。
`endpoint`	MaxCompute的EndPoint（域名节点）。说明目前仅支持AnalyticDB for MySQL通过MaxCompute的VPC网络Endpoint访问MaxCompute。查询各地域VPC网络的Endpoint，请参见各地域Endpoint对照表（阿里云VPC网络连接方式）。
`accessid`	阿里云账号或者具备MaxCompute访问权限的RAM用户的AccessKey ID。如何获取AccessKey ID和AccessKey Secret，请参见账号与权限。
`accesskey`	阿里云账号或者具备MaxCompute访问权限的RAM用户的AccessKey Secret。如何获取AccessKey ID和AccessKey Secret，请参见账号与权限。
`partition_column`	本文使用的示例是创建分区表的示例，所以需要配置`partition_column`。如果MaxCompute的表是非分区表，那么AnalyticDB for MySQL中也需要创建非分区表，此时无需配置`partition_column`。
`project_name`	MaxCompute中的工作空间名称。
`table_name`	MaxCompute中的数据源表名。

在test_adb数据库中创建表adb_nopart_import_test，用于存储从MaxCompute中导入的数据。

CREATE TABLE IF NOT EXISTS adb_nopart_import_test
(   id int,
    name string,
    age int,
    dt string,
    PRIMARY KEY(id,dt)
)
DISTRIBUTED BY HASH(id)
PARTITION BY VALUE('dt') LIFECYCLE 30;

导入数据。

方式一：执行INSERT INTO导入数据，当主键重复时会自动忽略当前写入数据，不做更新，作用等同于INSERT IGNORE INTO，详情请参见INSERT INTO。示例如下：

INSERT INTO adb_nopart_import_test
SELECT * FROM odps_nopart_import_test_external_table;

通过SELECT查询写入表中的数据，示例如下：

SELECT * FROM adb_nopart_import_test;

返回结果如下：

+------+-------+------+---------+
| id   | name  | age  |   dt    |
+------+-------+------+---------+
|    1 | james |   10 |  202207 |
|    2 | bond  |   20 |  202207 |
|    3 | jack  |   30 |  202207 |
|    4 | lucy  |   40 |  202207 |
+------+-------+------+---------+

如果需要将特定分区的数据导入adb_nopart_import_test，可以执行：

INSERT INTO adb_nopart_import_test
SELECT * FROM odps_nopart_import_test_external_table
WHERE dt = '202207';

方式二：执行INSERT OVERWRITE导入数据，会覆盖表中原有的数据。示例如下：

INSERT OVERWRITE adb_nopart_import_test
SELECT * FROM odps_nopart_import_test_external_table;

方式三：异步执行INSERT OVERWRITE导入数据。通常使用SUBMIT JOB提交异步任务，由后台调度，可以在写入任务前增加Hint加速写入任务。详情请参见异步写入。示例如下：

SUBMIT JOB 
INSERT OVERWRITE adb_nopart_import_test 
SELECT * FROM odps_nopart_import_test_external_table;

返回结果如下：

+---------------------------------------+
| job_id                                |
+---------------------------------------+
| 2020112122202917203100908203303****** |

关于异步提交任务详情请参见异步提交导入任务。

Storage API方式访问MaxCompute

使用Storage API方式访问MaxCompute数据时，您需确保：

已提交Storage API功能的试用申请，并获得试用资格。详情请参见试用申请。
集群内版本需为3.1.10.2及以上版本。
集群所在地域为华东1（杭州）、华东2（上海）、华南1（深圳）或华北2（北京）。

费用说明

使用Storage API方式访问MaxCompute外表数据需购买独享数据传输服务资源组，会产生费用。独享数据传输服务资源组的计费规则请参见独享数据传输服务资源组定价。

购买并配置独享数据传输服务资源组

登录MaxCompute控制台购买独享数据传输服务资源组。具体操作，请参见购买独享数据服务资源组。
在项目管理页面，选择目标MaxCompute项目并单击操作列的管理。
单击基础属性区域的编辑，在数据传输服务下拉列表中选择您购买的独享资源组。

访问MaxCompute数据

连接目标AnalyticDB for MySQL集群。详细操作步骤，请参见连接集群。
创建目标数据库。
```
CREATE database adb_demo;
```

创建MaxCompute外表。本文以person为例。

CREATE TABLE IF NOT EXISTS person
(
    id int,
    name string,
    age int,
    dt string
)
 ENGINE='ODPS'
 TABLE_PROPERTIES='{
 "endpoint":"http://service.cn.maxcompute.aliyun-inc.com/api",
 "accessid":"L*******FsE",
 "accesskey":"CcwF********iWjv",
  "partition_column":"dt",
 "project_name":"odps_project1",
 "table_name":"odps_nopart_import_test",
 "odps_quota_name":ot_42854300324284****,
 "odps_compression_code":ZSTD
 }';

更多参数，请参见参数说明。

查询数据。

/*+ storage_api_enabled=true,odps_quota_name=ot_42854300324284****,odps_compression_code=ZSTD*/
SELECT * FROM person;

Hint参数说明：

参数	是否必填	说明

参数	是否必填	说明
storage_api_enabled	是	是否使用Storage API访问MaxCompute数据。取值： true：是。 false（默认值）：否，使用Tunnel方式访问MaxCompute数据。本示例需配置为true。说明您也可以使用SET ADB_CONFIG命令配置该参数。详情请参见Config和Hint配置参数。
odps_quota_name	是	购买独享数据传输服务资源组的名称。本文示例为ot_42854300324284****。独享数据传输服务资源组的名称查看方法，请参见查看Quota名称。说明您也可以使用SET ADB_CONFIG命令或在TABLE_PROPERTIES中配置该参数。详情请参见Config和Hint配置参数。
odps_compression_code	否	压缩MaxCompute数据的方式。默认值为`""`，即不压缩MaxCompute数据。取值： ZSTD LZ4_FRAME 说明您也可以使用SET ADB_CONFIG命令或在TABLE_PROPERTIES中配置该参数。详情请参见Config和Hint配置参数。

返回结果如下：

+------+-------+------+---------+
| id   | name  | age  |   dt    |
+------+-------+------+---------+
|    1 | james |   10 |  202207 |
|    2 | bond  |   20 |  202207 |
|    3 | jack  |   30 |  202207 |
|    4 | lucy  |   40 |  202207 |
+------+-------+------+---------+

在adb_demo数据库中创建表test用于存储从MaxCompute中导入的数据。

CREATE TABLE IF NOT EXISTS test
(   id int,
    name string,
    age int,
    dt string,
    PRIMARY KEY(id,dt)
)
DISTRIBUTED BY HASH(id)
PARTITION BY VALUE('dt') LIFECYCLE 30;

导入数据。

方式一：执行INSERT INTO导入数据，当主键重复时会自动忽略当前写入数据，不做更新，作用等同于INSERT IGNORE INTO，详情请参见INSERT INTO。示例如下：

/*+ storage_api_enabled=true,odps_quota_name=ot_42854300324284****,odps_compression_code=ZSTD*/
INSERT INTO test SELECT * FROM person;

通过SELECT查询写入表中的数据，示例如下：

SELECT * FROM test;

返回结果如下：

+------+-------+------+---------+
| id   | name  | age  |   dt    |
+------+-------+------+---------+
|    1 | james |   10 |  202207 |
|    2 | bond  |   20 |  202207 |
|    3 | jack  |   30 |  202207 |
|    4 | lucy  |   40 |  202207 |
+------+-------+------+---------+

如果需要将特定分区的数据导入test，可以执行：

/*+ storage_api_enabled=true,odps_quota_name=ot_42854300324284****,odps_compression_code=ZSTD*/
INSERT INTO test SELECT * FROM person WHERE dt = '202207';

方式二：执行INSERT OVERWRITE导入数据，会覆盖表中原有的数据。示例如下：

/*+ storage_api_enabled=true,odps_quota_name=ot_42854300324284****,odps_compression_code=ZSTD*/
INSERT OVERWRITE test SELECT * FROM person;

方式三：异步执行INSERT OVERWRITE导入数据。通常使用SUBMIT JOB提交异步任务，由后台调度，可以在写入任务前增加Hint加速写入任务。详情请参见异步写入。示例如下：

/*+ storage_api_enabled=true,odps_quota_name=ot_42854300324284****,odps_compression_code=ZSTD*/
SUBMIT JOB INSERT OVERWRITE test SELECT * FROM person;

返回结果如下：

+---------------------------------------+
| job_id                                |
+---------------------------------------+
| 2020112122202917203100908203303****** |

关于异步提交任务，详情请参见异步提交导入任务。

Hint参数说明，请参见Hint参数说明。

（可选）查看是否已使用Storage API方式访问MaxCompute数据。具体操作，请参见资源观测。
在表访问热度（并发数）和访问来源IP（B/S）指标区域，将使用方式选择为Storage API读或Storage API写，若指标区域有数据，则说明使用了Storage API方式访问MaxCompute数据；若无，则说明未使用Storage API方式，而默认使用了Tunnel方式访问MaxCompute数据。