HoloStudio与DataWorks无缝连通,您可以通过HoloStudio将MaxCompute数据导入Hologres,并基于DataWorks的底层能力,前往DataWorks进行定时调度,实现周期性导入数据至Hologres。本文为您介绍如何将MaxCompute源表数据导入Hologres进行周期性调度。

背景信息

  • MaxCompute与Hologres的分区无强映射关系,MaxCompute的分区字段映射为Hologres的普通字段。因此,您可以将MaxCompute的分区表或非分区表导入Hologres的分区或非分区表,可以根据实际业务情况选择是否需要分区。
  • 前往DataWorks调度会产生一定的调度费用,详细收费情况,请参见DataWorks资源组概述
  • 如果需要实现写入更新,您需要使用INSERT ON CONFLICT语法。
  • 如果MaxCompute数据会定期更新,建议您在Hologres导入数据时,使用IMPORT FOREIGN SCHEMA语句来更新外部表,以便于及时获取MaxCompute的元数据。
  • 外表名和内表名必须不一样,否则会报错。如使用import foreign语法创建外表,则外表名和MaxCompute表名必须保持一致。

操作步骤

  1. 准备MaxCompute表数据。
    准备一张MaxCompute数据源表,您可以参考创建表进行建表并导入数据。示例选用MaxCompute公共数据集public_data的分区表dwd_ product_movie_basic_info表,您可以参照使用公开数据集描述,登录并查询数据集。其建表DDL语句如下:
    --MaxCompute分区表DDL
    CREATE TABLE IF NOT EXISTS public_data.dwd_product_movie_basic_info(
      movie_name STRING COMMENT '电影名称',
      dirctor STRING COMMENT '导演',
      scriptwriter STRING COMMENT '编剧',
      area STRING COMMENT '制片地区/国家',
      actors STRING COMMENT '主演',
      `type` STRING COMMENT '类型',
      movie_length STRING COMMENT '电影长度',
      movie_date STRING COMMENT '上映日期',
      movie_language STRING COMMENT '语言',
      imdb_url STRING COMMENT 'imdb号'
    ) 
    PARTITIONED BY (ds STRING) STORED AS ALIORC;
    
    --查看分区表的某个分区数据
    SELECT * FROM public_data.dwd_product_movie_basic_info WHERE ds = '20170112';
    查询数据显示如下图所示。movie_basic
  2. 新建外部表。
    进入HoloStudio,在SQL Console中新建一张外部表,用于映射MaxCompute源表数据。外部表的字段顺序和字段类型需要和MaxCompute一一对应。示例新建外部表的SQL语句如下:
    BEGIN;
    CREATE FOREIGN TABLE public.dwd_product_movie_basic_info (
        "movie_name" text,
        "dirctor" text,
        "scriptwriter" text,
        "area" text,
        "actors" text,
        "type" text,
        "movie_length" text,
        "movie_date" text,
        "movie_language" text,
        "imdb_url" text,
        "ds" text
    )
    SERVER odps_server
    OPTIONS (project_name 'public_data', table_name 'dwd_product_movie_basic_info');
    comment on column public.dwd_product_movie_basic_info."movie_name" is '电影名称';
    comment on column public.dwd_product_movie_basic_info."dirctor" is '导演';
    comment on column public.dwd_product_movie_basic_info."scriptwriter" is '编剧';
    comment on column public.dwd_product_movie_basic_info."area" is '制片地区/国家';
    comment on column public.dwd_product_movie_basic_info."actors" is '主演';
    comment on column public.dwd_product_movie_basic_info."type" is '类型';
    comment on column public.dwd_product_movie_basic_info."movie_length" is '电影长度';
    comment on column public.dwd_product_movie_basic_info."movie_date" is '上映日期';
    comment on column public.dwd_product_movie_basic_info."movie_language" is '语言';
    comment on column public.dwd_product_movie_basic_info."imdb_url" is 'imdb号';
    COMMIT;
           
    OPTIONS的连接参数说明如下表所示。
    参数 描述
    project_name MaxCompute的项目名称。
    table_name MaxCompute的表名称。
  3. 新建存储表。
    在HoloStudio中新建一张用于接收并存储数据的真实存储表。由于本次示例是将MaxCompute分区表导入Hologres分区表,因此需要在Hologres中创建一张分区表。
    该示例建表DDL仅是简单示例,实际建表DDL请根据实际业务需要创建,并给表设置合理的索引,以达到更优的查询性能,DDL示例如下:
    BEGIN;
    CREATE TABLE "public"."holo_dwd_product_movie_basic_info" (
     "movie_name" text,
     "dirctor" text,
     "scriptwriter" text,
     "area" text,
     "actors" text,
     "type" text,
     "movie_length" text,
     "movie_date" text,
     "movie_language" text,
     "imdb_url" text,
     "ds" text
    )
    PARTITION BY LIST (ds);
    CALL SET_TABLE_PROPERTY('"public"."holo_dwd_product_movie_basic_info"', 'orientation', 'column');
    CALL SET_TABLE_PROPERTY('"public"."holo_dwd_product_movie_basic_info"', 'bitmap_columns', '"movie_name","dirctor","scriptwriter","area","actors","type","movie_length","movie_date","movie_language","imdb_url","ds"');
    CALL SET_TABLE_PROPERTY('"public"."holo_dwd_product_movie_basic_info"', 'dictionary_encoding_columns', '"movie_name:auto","dirctor:auto","scriptwriter:auto","area:auto","actors:auto","type:auto","movie_length:auto","movie_date:auto","movie_language:auto","imdb_url:auto","ds:auto"');
    CALL SET_TABLE_PROPERTY('"public"."holo_dwd_product_movie_basic_info"', 'time_to_live_in_seconds', '3153600000');
    comment on column "public"."holo_dwd_product_movie_basic_info"."movie_name" is '电影名称';
    comment on column "public"."holo_dwd_product_movie_basic_info"."dirctor" is '导演';
    comment on column "public"."holo_dwd_product_movie_basic_info"."scriptwriter" is '编剧';
    comment on column "public"."holo_dwd_product_movie_basic_info"."area" is '制片地区/国家';
    comment on column "public"."holo_dwd_product_movie_basic_info"."actors" is '主演';
    comment on column "public"."holo_dwd_product_movie_basic_info"."type" is '类型';
    comment on column "public"."holo_dwd_product_movie_basic_info"."movie_length" is '电影长度';
    comment on column "public"."holo_dwd_product_movie_basic_info"."movie_date" is '上映日期';
    comment on column "public"."holo_dwd_product_movie_basic_info"."movie_language" is '语言';
    comment on column "public"."holo_dwd_product_movie_basic_info"."imdb_url" is 'imdb号';
    COMMIT;
  4. 新建分区子表的数据开发。
    由于本示例是将MaxCompute分区表数据导入Hologres分区表,因此需要在Hologres中创建对应的分区键值的分区子表。然后将分区数据导入对应的分区子表。这里通过{bizdate}参数控制分区键值,在调度系统中自动赋值完成周期性调度。关于参数的配置,请参见DataWorks文档DataWorks调度配置。其中,insert的分区数据必须和分区键(本文示例为ds)的值保持一致,否则会报错。
    1. 在HoloStudio中,单击左侧导航栏的数据开发图标,进入数据开发页面。
    2. 鼠标悬停至新建图标,单击Hologres开发
    3. 配置新建节点对话框的节点名称目标文件夹数据库参数。新建节点
    4. 单击提交,成功创建Hologres开发节点。
    5. 在新建的Hologres开发节点的编辑界面,输入如下创建分区表的数据开发语句。
      --创建分区子表
      DROP TABLE IF EXISTS "public".holo_dwd_product_movie_basic_info_${bizdate};
      
      CREATE TABLE IF NOT EXISTS "public".holo_dwd_product_movie_basic_info_${bizdate} PARTITION OF "public".holo_dwd_product_movie_basic_info FOR VALUES IN ('${bizdate}');
      
      --更新外表数据
      import foreign schema public_data limit to (dwd_product_movie_basic_info) from server odps_server into hmads options(if_table_exist 'update');
      
      --等待30s再导入Hologres,以防Hologres meta信息更新缓存慢导致的数据不一致而同步不成功
      select pg_sleep(30); 
      
      --导入指定分区数据
      INSERT INTO "public".holo_dwd_product_movie_basic_info_${bizdate}
      SELECT 
          "movie_name",
          "dirctor",
          "scriptwriter",
          "area",
          "actors",
          "type",
          "movie_length",
          "movie_date",
          "movie_language",
          "imdb_url",
          "ds"
      FROM "public".foreign_dwd_product_movie_basic_info
      WHERE ds='${bizdate}';
    6. 单击顶部菜单栏左侧的保存,保存Hologres开发。
    7. 单击顶部菜单栏右侧的前往DataWorks调度,进入调度页面。前往调度
  5. 新建分区表的调度作业。
    1. 在DataWorks中新建Hologres节点。
      单击前往DataWorks调度后,页面自动跳转至DataWorks的新建节点。您需要配置新建节点对话框的节点类型节点名称目标文件夹,创建Hologres节点。节点类型选择Hologres开发新建节点
      如果是重新执行已有作业,则可以不需要重新创建节点。
    2. 在新建的节点页面,单击更新节点版本,将分区表的信息同步至该节点。同步数据
  6. 配置调度信息。
    在新建的节点页面,单击右侧导航栏的调度配置,配置调度参数。详情请参见配置调度参数。本次示例选择的是日调度,因此将会每天自动生成一张分区子表,并导入对应的数据,以此来实现增量数据周期性调度。更多的调度逻辑(包括调度周期、调度参数、调度上下游等)请参见DataWorks调度配置
    具体配置如下所示:
    1. 配置基础属性。
      基础属性区域,将参数赋值为时间节点。基础属性
    2. 配置时间属性。
      时间属性区域,配置时间属性正常调度。其余参数请根据业务实际情况配置,详情请参见时间属性配置说明时间属性
    3. 配置调度依赖。
      调度依赖区域,您需要配置调度依赖为root节点,操作如下:
      1. 自动解析配置为
      2. 单击使用工作空间根节点,自动解析出root节点。
      3. 再将自动解析配置为,详情请参见配置同周期调度依赖
      调度依赖
      您也可以根据业务逻辑选择已有的父节点。
  7. 发布调度。
    1. 配置完调度参数,在新建节点页面的顶部菜单栏左侧,单击保存图标,保存调度配置。
    2. 在新建节点页面的顶部菜单栏左侧,单击提交图标,提交调度信息。
    3. 发布包提交成功后,单击任务发布页面顶部菜单栏右侧的运维中心,进入运维中心。
    4. 在左侧导航栏,单击周期任务运维 > 周期任务,勾选并单击目标任务名称。
    5. 在右侧编辑界面,鼠标右键单击目标任务节点,选择补数据 > 当前节点补数据
    6. 根据业务实际情况,配置补数据对话框的各项参数。完成调度任务的发布。
  8. 任务执行成功后,您可以通过如下方式查询数据。
    • 您可以返回Hologres中,通过标准SQL查看父表或分区表的数据,示例如下。
      --查看分区子表数据
      select * from holo_dwd_product_movie_basic_info_20170112;
      
      --查看分区父表总数据
      select count (*) from holo_dwd_product_movie_basic_info;
    • 返回HoloStudio。单击新建节点的刷新图标,刷新当前页面。您可以在PG管理模块找到目标分区表,查看调度成功的分区表数据。双击目标表名称,在表编辑页面单击数据预览,即可看到已成功导入的数据。