全部产品
Search
文档中心

实时数仓Hologres:MaxCompute分区表数据导入

更新时间:May 26, 2023

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

前提条件

背景信息

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

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

注意事项

  • 使用临时表的原因是为了保证原子性,只有在导入完成后才绑定至分区表,为了避免导入任务失败时还需要重新删除表等操作。

  • 对于更新子表分区数据场景,需要删除子表和重新绑定临时表放入一个事务过程中,保证该过程的事务性。

  • 使用迁移工具导入DataWorks作业时需满足以下条件:

详细操作步骤

  1. MaxCompute数据准备

    1. 登录MaxCompute控制台

    2. 单击左侧导航栏数据分析 > SQL查询sql查询
    3. 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
      )
      ;
    4. 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分区。

    -- 清理潜在的临时表
    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代码如下。

    -- 清理潜在的临时表
    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作业包

  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语句。手工作业流程