本文为您介绍如何将MaxCompute分区表数据导入到Hologres分区表。

前提条件

背景信息

通过Hologres中的MaxCompute外表方式向Hologres导入数据是非常常见的数据导入模式。在日常工作中会经常需要进行数据导入，此时可以借助DataWorks的强大调度和作业编排能力，实现周期性调度，配置一个调度作业覆盖数据导入两个场景，详情请参见DataWorks作业案例。

考虑到作业较为复杂，所以可以利用DataWorks的迁移助手功能，将Data作业案例文件导入您的项目中，您即可获得Data作业案例，之后按照您的具体业务需求更改部分参数或脚本即可，详情请参见使用迁移工具导入DataWorks作业。

注意事项

  • 使用临时表的原因是为了保证原子性，只有在导入完成后才绑定至分区表，为了避免导入任务失败时还需要重新删除表等操作。
  • 对于更新子表分区数据场景，需要删除子表和重新绑定临时表放入一个事务过程中，保证该过程的事务性。
  • 使用迁移工具导入DataWorks作业时需满足以下条件：

详细操作步骤

  1. MaxCompute数据准备
    1. 登录MaxCompute控制台，在左上角选择区域，单击查询编辑，即可进入查询编辑器界面。
    2. 在选择数据源对话框，选择数据源类型为MaxCompute，工作空间为已创建好的项目空间。
      选择项目空间
      说明 如果您选择的工作空间模式为标准模式，在查询编辑器中提交作业实际是在开发项目（带dev标识）中提交。
    3. 单击确认，即可进入查询编辑器界面。
    4. 在SQL查询页面，输入如下SQL语句用于创建分区表，单击运行。
      DROP TABLE IF EXISTS odps_sale_detail;

--创建一张分区表sale_detail。
CREATE TABLE IF NOT EXISTS odps_sale_detail 
(
    shop_name STRING
    ,customer_id STRING
    ,total_price DOUBLE
)
PARTITIONED BY 
(
    sale_date STRING
)
;
    5. 在SQL查询页面，输入如下SQL语句用于向分区表中导入数据，单击运行。
      -- 向源表增加分区20210815
ALTER TABLE odps_sale_detail ADD IF NOT EXISTS PARTITION(sale_date='20210815')
;

-- 向分区写入数据
INSERT OVERWRITE TABLE odps_sale_detail PARTITION(sale_date='20210815') VALUES 
('s1','c1',100.1),
('s2','c2',100.2),
('s3','c3',100.3)
;

-- 向源表增加分区20210816
ALTER TABLE odps_sale_detail ADD IF NOT EXISTS PARTITION(sale_date='20210816')
;

-- 向分区写入数据
INSERT OVERWRITE TABLE odps_sale_detail PARTITION(sale_date='20210816') VALUES 
('s1','c1',100.1),
('s2','c2',100.2),
('s3','c3',100.3)
;

-- 向源表增加分区20210817
ALTER TABLE odps_sale_detail ADD IF NOT EXISTS PARTITION(sale_date='20210817')
;

-- 向分区写入数据
INSERT OVERWRITE TABLE odps_sale_detail PARTITION(sale_date='20210817') VALUES 
('s1','c1',100.1),
('s2','c2',100.2),
('s3','c3',100.3)
;

-- 向源表增加分区20210818
ALTER TABLE odps_sale_detail ADD IF NOT EXISTS PARTITION(sale_date='20210818')
;

-- 向分区写入数据
INSERT OVERWRITE TABLE odps_sale_detail PARTITION(sale_date='20210818') VALUES 
('s1','c1',100.1),
('s2','c2',100.2),
('s3','c3',100.3)
;
  2. Hologres中建表
    • 创建外部表
      1. 登录数据库
        1. 在HoloWeb控制台DB授权页面，单击元数据管理。
        2. 在元数据管理页面，双击左侧目录树中已创建成功的数据库名称，单击确认。登录数据库
      2. 创建外部表
        1. 在SQL编辑器页面，单击左上角的新建SQL窗口。
        2. 在新增的临时Query查询页面，选择已创建的实例名和数据库后，请您在SQL查询的编辑框输入如下语句，单击运行。
          DROP FOREIGN TABLE IF EXISTS odps_sale_detail;

-- 创建外部表
IMPORT FOREIGN SCHEMA maxcompute_project LIMIT to
(
    odps_sale_detail
) 
FROM SERVER odps_server INTO public 
OPTIONS(if_table_exist 'error',if_unsupported_type 'error');
    • 创建分区表（内部表）
      1. 登录数据库
        1. 在HoloWeb控制台DB授权页面，单击元数据管理。
        2. 在元数据管理页面，双击左侧目录树中已创建成功的数据库名称，单击确认。登录数据库
      2. 创建分区表
        1. 在SQL编辑器页面，单击左上角的新建SQL窗口。
        2. 在新增的临时Query查询页面，选择已创建的实例名和数据库后，请您在SQL查询的编辑框输入如下语句，单击运行。
          DROP TABLE IF EXISTS holo_sale_detail;

-- 创建Hologres分区表（内部表）
BEGIN ;
CREATE TABLE IF NOT EXISTS holo_sale_detail
(
    shop_name TEXT
    ,customer_id TEXT 
    ,total_price FLOAT8
    ,sale_date TEXT
)
PARTITION BY LIST(sale_date);
COMMIT;
  3. 分区数据导入Hologres临时表

    在临时Query查询页面，请您在SQL查询的编辑框输入如下语句，单击运行。

    此SQL语句将MaxCompute的hologres_test项目中的odps_sale_detail分区表的20210816分区导入Hologres中的holo_sale_detail分区表的20210816分区。

    -- 刷新外表的Schema

IMPORT FOREIGN SCHEMA hologres_test LIMIT to
(
    odps_sale_detail
) 
FROM SERVER odps_server INTO public 
OPTIONS(if_table_exist 'update',if_unsupported_type 'error');

-- 清理潜在的临时表
BEGIN ;

DROP TABLE IF EXISTS holo_sale_detail_tmp_20210816;

COMMIT ;

-- 创建临时表
SET hg_experimental_enable_create_table_like_properties=on;

BEGIN ;

CALL HG_CREATE_TABLE_LIKE ('holo_sale_detail_tmp_20210816', 'select * from holo_sale_detail'); 

COMMIT;

-- 向临时表插入数据
INSERT INTO holo_sale_detail_tmp_20210816
SELECT *
FROM public.odps_sale_detail
WHERE sale_date='20210816';
  4. 临时表绑定至Hologres分区表

    在临时Query查询页面，请您在SQL查询的编辑框输入如下语句，单击运行。

    • 存在旧的子表，则需要先删除旧子表，再将临时表绑定至Hologres分区表。
      此SQL语句用于删除子表holo_sale_detail_20210816并将临时表holo_sale_detail_tmp_20210816绑定至holo_sale_detail分区表的20210816分区。
      -- 已有子表时替换子表
BEGIN ;

-- 删除旧子表
DROP TABLE IF EXISTS holo_sale_detail_20210816;

-- 将临时表改名
ALTER TABLE holo_sale_detail_tmp_20210816 RENAME TO holo_sale_detail_20210816;

-- 将临时表绑定至指定分区表
ALTER TABLE holo_sale_detail ATTACH PARTITION holo_sale_detail_20210816
FOR VALUES IN ('20210816')
;

COMMIT ;
    • 不存在旧子表，直接将临时表绑定至Hologres分区表。
      此SQL语句用于将临时表holo_sale_detail_tmp_20210816绑定至holo_sale_detail分区表的20210816分区。
      BEGIN ;
-- 将临时表改名
ALTER TABLE holo_sale_detail_tmp_20210816 RENAME TO holo_sale_detail_20210816;
-- 将临时表绑定至指定分区表
ALTER TABLE holo_sale_detail ATTACH PARTITION holo_sale_detail_20210816
FOR VALUES IN ('20210816');
COMMIT ;
  5. ANALYZE Hologres分区表

    在临时Query查询页面，请您在SQL查询的编辑框输入如下语句，单击运行。

    此SQL语句用于ANALYZE holo_sale_detail分区表，验证分区表执行计划。ANALYZE分区表时，仅需ANALYZE父表。

    -- 大量数据导入后执行ANALYZE分区表父表操作
ANALYZE holo_sale_detail;
  6. 清理过期的分区子表（按需）
    生产环境中，数据具备生命周期，对于超期的分区需要清理。

    在临时Query查询页面，请您在SQL查询的编辑框输入如下语句，单击运行。

    此SQL语句清理20210631的分区。

    DROP TABLE IF EXISTS holo_sale_detail_20210631;

DataWorks作业案例

日常工作中往往需要周期性的调度以上的SQL，此时可以借助DataWorks的强大调度和作业编排能力，实现周期性调度，且使用一个调度作业覆盖以上两个场景。请仔细阅读以下内容，便于您使用迁移工具导入DataWorks作业时按照您的具体业务需求更改部分参数或脚本。业务流程总览如下。流程总览

业务流程模块详解

  • 基础参数

    基础参数用于管理整个业务流程中用到的所有参数，主要用到的参数如下。

    编号参数名类型取值描述
    1datepre31变量${yyyymmdd-31}用于控制清理过期分区的参数，此处含义为清理31天前的分区。
    2datetime1变量$bizdate用于控制创建分区的参数。
    3holo_table_name常量holo_sale_detailHologres分区表名。
    4odps_project常量hologres_testMaxCompute项目名。
    5odps_table_name常量odps_sale_detailMaxCompute分区表名。
    6partition_key常量sale_dateMaxCompute分区字段。
    系统配置图如下。基础参数
  • 写入分区数据至临时表

    该步骤是一个Hologres SQL模块，其中SQL代码如下。

    -- 刷新外表的Schema

IMPORT FOREIGN SCHEMA ${odps_project} LIMIT to
(
    ${odps_table_name}
) 
FROM SERVER odps_server INTO public 
OPTIONS(if_table_exist 'update',if_unsupported_type 'error');

-- 清理潜在的临时表
BEGIN ;

DROP TABLE IF EXISTS ${holo_table_name}_tmp_${datetime1};

COMMIT ;

-- 创建临时表
SET hg_experimental_enable_create_table_like_properties=on;

BEGIN ;

CALL HG_CREATE_TABLE_LIKE ('${holo_table_name}_tmp_${datetime1}', 'select * from ${holo_table_name}'); 

COMMIT;

-- 向临时表插入数据
INSERT INTO ${holo_table_name}_tmp_${datetime1}
SELECT *
FROM public.${odps_table_name}
WHERE ${partition_key}='${datetime1}';
    需要将基础参数绑定至该模块上游，用于控制其中的参数变量，系统配置如下：写入分区数据至临时表
  • 替换子表

    该步骤是一个Hologres SQL模块，用于替换已有子表。将替换子表相关过程放在一个事务中，保证执行的事务性，SQL代码如下。

    -- 已有子表时替换子表
BEGIN ;

-- 删除已经存在的子表
DROP TABLE IF EXISTS ${holo_table_name}_${datetime1};

-- 将临时表改名
ALTER TABLE ${holo_table_name}_tmp_${datetime1} RENAME TO ${holo_table_name}_${datetime1};

-- 将临时表绑定至指定分区表
ALTER TABLE ${holo_table_name} ATTACH PARTITION ${holo_table_name}_${datetime1}
FOR VALUES IN ('${datetime1}');

COMMIT ;
    需要将基础参数绑定至该模块上游，用于控制其中的参数变量，系统配置如下。替换子表
  • 收集分区表的统计信息

    该步骤是一个Hologres SQL模块，收集父表的统计信息，SQL代码如下。

    -- 大量数据导入后执行ANALYZE分区表父表操作
ANALYZE ${holo_table_name};
    需要将基础参数绑定至该模块上游，用于控制其中的参数变量，系统配置如下。收集分区表的统计信息
  • 清理过期子表

    生产环境中，数据具备生命周期，对于超期的分区需要清理。

    现以仅在Hologres中存储最近31天的分区为例，由于之前设置的参数为datepre31=${yyyymmdd-31}，所以清理过期子表的SQL代码如下。

    -- 清理过期子表

BEGIN ;

DROP TABLE IF EXISTS ${holo_table_name}_${datepre31};

COMMIT ;

    所以在作业运行时，如果bizdate=20200309，则datepre31=20200207，这样即可达到清理分区的目的。

    同时需要将基础参数绑定至该模块上游，用于控制其中的参数变量，系统配置如下。清理过期子表

使用迁移工具导入DataWorks作业

  • 考虑到作业较为复杂，所以可以利用DataWorks的迁移助手功能，将以下文件导入您的项目中，您即可获得以上说明的DataWorks的作业，之后按照您的具体业务需求更改部分参数或脚本即可。
  • DataWorks迁移助手的详细介绍，请参见DataWorks迁移助手介绍及实践。
  • 下载如下作业包：DataWorks作业包。
  1. 进入DataWorks迁移助手，详情请参见进入迁移助手。
  2. 在迁移助手的左侧导航栏，单击DataWorks迁移 > DataWorks导入。
  3. 在DataWorks导入页面，单击右上方的新建导入任务。
  4. 在新建导入任务对话框中，配置各项参数。
    新建导入任务
    参数描述
    导入名称自定义名称。导入名称仅支持大小写字母、中文、数字、下划线（_）和英文句号（.）。
    上传方式上传文件的方式。
    • 本地上传：上传导出包文件小于或等于30 MB时，您可使用本方式上传导出包文件到DataWorks工作空间中。
    • OSS链接：上传导出包文件大于30 MB时，请将导出包文件上传至OSS存储。在OSS存储控制台文件详情页面复制URL链接，将获取到的OSS链接上传至DataWorks工作空间中。OSS上传操作请参见上传文件，获取OSS下载链接请参见分享文件。下载链接
    备注对导入任务进行简单描述。
  5. 单击确认，进入导入任务设置页面，设置匹配关系。
    导入任务配置
  6. 单击右上方的开始导入，在请确认对话框中，单击确认。
    1. 导入成功后，在您的数据开发模块中则会出现以上提及的周期性作业。导入成功
    2. 同时在手工作业流程中会出现相关的DDL语句。手工作业流程