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

前提条件

  • 已购买并开通Hologres实例,开通方法请参见购买Hologres
  • 已开通MaxCompute并创建项目,详情请参见开通MaxCompute
  • 已开通DataWorks服务并创建DataWorks工作空间,详情请参见创建工作空间

背景信息

通过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作业时按照您的具体业务需求更改部分参数或脚本。业务流程总览如下。流程总览

业务流程模块详解

  • 基础参数

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

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

    该步骤是一个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语句。手工作业流程