DUMP TO MAXCOMPUTE(2.0版)

本文介绍如何将AnalyticDB for MySQL 2.0的数据导出到MaxCompute中。

语法

/*+ 
    engine=MPP, 
    [return-dump-record-count=TRUE,]
    dump-header=[DUMP DATA [OVERWRITE] INTO 'odps://project_name/table_name']
*/ sql

参数

  • engine=MPP:计算引擎,必选参数。

  • return-dump-record-count=TRUE:显示导出的数据行数,可选参数。

  • dump-header:必选参数。

授权

进行数据导出操作时,您所使用的账号和分析型数据库MySQL版公共账号garuda_data@aliyun.comgaruda_build@aliyun.com需要拥有MaxCompute表的Describe,Select,Alter,Update和Drop权限。

以下SQL为garuda_data@aliyun.comgaruda_build@aliyun.com账号授权,可以参照以下SQL为用户账号授权。

说明

如果用户使用的账号是RAM用户,RAM用户格式为:ram$account_name:subaccount_name。更多账号授权信息,请参见为阿里云账号/RAM用户授权

USE project_name;
ADD USER ALIYUN$garuda_data@aliyun.com;
GRANT createInstance ON project project_name TO USER ALIYUN$garuda_data@aliyun.com;
GRANT Describe,Select,alter,update,drop ON TABLE table_name TO USER ALIYUN$garuda_data@aliyun.com;
USE project_name;
ADD USER ALIYUN$garuda_build@aliyun.com;
GRANT createInstance ON project project_name TO USER ALIYUN$garuda_build@aliyun.com;
GRANT Describe,Select,alter,update,drop ON TABLE table_name TO USER ALIYUN$garuda_build@aliyun.com;

示例

  • 将CUSTOMER表中年龄大于18岁的客户的姓名和手机号导出到MaxCompute的trade project下的CUSTOMER中。

      /*+ 
          engine=MPP, 
          return-dump-record-count=TRUE,
          dump-header=[DUMP DATA OVERWRITE INTO 'odps://trade/customer']
      */ 
          SELECT customer_id,customer_name,phone_num
          FROM customer
          WHERE age >18;
  • 将CUSTOMER表中年龄大于18的客户信息导入MaxCompute的trade project下的分区表CUSTOMER中,其中dtnation为MaxCompute目标表的分区字段。

      /*+ 
          engine=MPP, 
          return-dump-record-count=TRUE,
          dump-header=[DUMP DATA OVERWRITE INTO 'odps://trade/customer/dt=20090627/nation=china']
      */ 
          SELECT customer_id,customer_name,phone_num
          FROM customer
          WHERE age >18;