从Hologres V0.10版本开始，Hologres与MaxCompute集成性进一步提升，支持在Hologres中执行MaxCompute的SQL语句，方便您直接快速操作MaxCompute。本文将为您介绍，在Hologres中如何执行MaxCompute SQL语句。

前提条件

  • 已开通Hologres并连接开发工具，本文使用HoloWeb，详情请参见连接HoloWeb。
  • 已开通MaxCompute，详情请参见开通MaxCompute和DataWorks。
  • 在Hologres中执行MaxCompute SQL需要当前用户具有在MaxCompute中执行SQL的权限。关于MaxCompute的权限，详情请参见用户规划与管理。

使用限制

  • 仅Hologres V0.10 及以上版本支持在Hologres中执行MaxCompute SQL语句。请前往Hologres管控台的实例详情页查看当前实例版本，如果您的实例是V0.10以下版本，请您提交工单或加入在线支持钉钉群申请升级实例。
  • 目前建议您在Hologres中仅执行MaxCompute的DDL语句，如果需要执行DML语句，请前往MaxCompute进行操作。
  • 每次调用只能执行一条SQL语句。

命令语法

  • 语法示例
    select exec_external_sql(
  'server', 
  'database', 
  'sql' , 
  timeout_ms, 
  'options'
);
    如上参数必须要按照顺序填写，若是需要省略某些参数，需要显示指定参数名，如下所示：
    exec_external_sql(
            server:='odps_server' , 
            database:='odps_project_name' , 
            sql:='sql' , 
            timeout_ms:=timeout_ms , 
            options:='options'
)
  • 参数说明
    参数名 参数含义 说明 示例
    server 外部服务器名称。您可以直接调用Hologres底层已创建的名为odps_server的外部表服务器。此函数目前仅支持odps server，外部server详细原理请参见Postgres FDW。 如果为空字符串，会取odps_server作为外部server。 'odps_server'
    database MaxCompute的project名。 无 'seahawks'
    sql 需要执行的MaxCompute SQL（建议只执行DDL语句）：
    • create table
    • alter table
    • desc table
    • drop table
    		 SQL语句需要符合MaxCompute的语法，建议只执行DDL语句。如果SQL中有单引号，需要在SQL语句前后添加双美元符号（$$SQL$$）实现单引号转义。
    说明 在HoloWeb和HoloStudio的临时查询页面暂不支持转义字符。
    		'CREATE TABLE IF EXISTS MC_TBL ;'
    timeout_ms 执行超时时间，单位ms。 缺省或者小于0的情况下会将该参数值设置为60000ms。超过超时时间将会退出，同时向MaxCompute发送一个取消指令。 50000
    options 使用DataWorks或者MaxCompute客户端提交SQL时，通常需要设置的SQL Flag。 具体的配置项请参见SET操作，若是有多个flag需要设置，需要将字段类型改成JSON格式。 {"odps.sql.type.system.odps2":"true"}或者{ "odps.sql.type.system.odps2":"true", "odps.sql.decimal.odps2=":"true"}

使用示例

您可以在Hologres中执行MaxCompute SQL，如果执行建表语句则会在MaxCompute对应的project中创建一张表。当前版本建议仅执行DDL语句。

  • 示例一：创建MaxCompute非分区表
    select exec_external_sql(
            'odps_server',
            'mc_project' , --project名
            'create table par_mc_table（id int,name string);' ,--在MaxCompute中创建一张表
            5000  --超时时间为5000ms
            );
  • 示例二：创建MaxCompute分区表并指定分区
    --创建分区表
select exec_external_sql(
          server:='odps_server',
          database:='mc_project', --maxcompute的project名
          sql:='create table par_mc_table（id int,name string) partitioned by (pt string);', --创建分区表
          timeout_ms:=10000--超时时间为10000ms
                  );
--指定分区表的分区
select exec_external_sql(
           'odps_server',
           'mc_project', --maxcompute的project名
           $$ALTER TABLE par_mc_table ADD IF NOT EXISTS partition(pt='202102');$$--指定分区
                  );
  • 示例三：跨Region创建MaxCompute表
    select exec_external_sql(
            'hangzhou_odps_server' ,
            'hologres_test' ,
            'create table mc_test(id int,create_time datetime,decimal_column decimal(38, 10));' ,
            50000,    --超时时间
            '{
            "odps.sql.type.system.odps2":"true",
            "odps.sql.decimal.odps2":"true"
  }'                  --MaxCompute的flag设置，分别代表开启新数据类型和开启decimal类型
);
  • 示例四：删除MaxCompute中的表
    select exec_external_sql(
            'odps_server',
            'mc_project',  --MaxCompute的project名
            'drop table if exists mc_table;' ,--删除MaxCompute的表
            50000
            );
  • 示例五：配合“通过SQL方式导出MaxCompute”功能使用

    从Hologres V0.9版本开始，Hologres支持通过SQL导出数据至MaxCompute，但是该方法需要在MaxCompute中提前创建接收数据的表，操作比较麻烦。从Hologres V0.10版本开始，通过在Hologres中执行MaxCompute SQL，即可创建表，再将数据导出，支持一站式开发。

    示例操作将Hologres中非分区数据导入至MaxCompute非分区表。具体如下：

    1. 在Hologres准备一张Hologres内部表（例如：holo_table），用于导出数据至MaxCompute，示例DDL和数据如下：
      create table "public"."holo_table" (
 "id" int4,
 "name" text
);

insert into "public"."holo_table" values 
(1,'a'),
(2,'b'),
(3,'c');
    2. 创建一张MaxCompute表，用于在MaxCompute中接收数据。
      select exec_external_sql(
            'odps_server',
            'mc_project' , --project名
            'create table mc_sink_table（id int,name string);' ,--在MaxCompute中创建一张接收数据表
            5000--超时时间为5000ms
            );
    3. 在Hologres新建一张外部表，用于映射MaxCompute表。
      begin;
create foreign table "public"."mc_mapping_foreign_table" (
 "id" int4,
 "name" text
)
server odps_server_bj
options (project_name 'default_project_2361b62', table_name 'mc_sink_table');
commit;
    4. 在Hologres通过SQL语句导出数据至MaxCompute。
      • 全部字段数据导出
        set hg_experimental_enable_write_maxcompute = on;-- 由于是beta功能，需要打开GUC参数
insert into mc_mapping_foreign_table
select * from holo_table;
      • 部分字段数据导出
        set hg_experimental_enable_write_maxcompute = on;-- 由于是beta功能，需要打开GUC参数
insert into mc_mapping_foreign_table (name)
select name from holo_table;
      更多关于数据导出的操作说明请参见通过SQL导出数据至MaxCompute。