DataWorks：Hologres資料來源

配置非分區表

• 配置從Hologres非分區表讀取資料至記憶體，如下所示。

  {
    "type":"job",
    "version":"2.0",//版本號碼。
    "steps":[
      {
        "stepType":"holo",//外掛程式名。
        "parameter":{
          "endpoint": "instance-id-region-endpoint.hologres.aliyuncs.com:port",
          "accessId": "***************", //訪問Hologres的accessId。
          "accessKey": "*******************", //訪問Hologres的accessKey。
          "database": "postgres",
          "table": "holo_reader_****",
          "column" : [ //欄位。
            "tag",
            "id",
            "title"
          ]
        },
        "name":"Reader",
        "category":"reader"
      },
      {
        "stepType":"stream",
        "parameter":{},
        "name":"Writer",
        "category":"writer"
      }
    ],
    "setting":{
      "errorLimit":{
        "record":"0"//錯誤記錄數。
      },
      "speed":{
        "throttle":true,//當throttle值為false時，mbps參數不生效，表示不限流；當throttle值為true時,表示限流。
        "concurrent":1,//作業並發數。 
                    "mbps":"12"//限流，此處1mbps = 1MB/s。
      }
    },
    "order":{
      "hops":[
        {
          "from":"Reader",
          "to":"Writer"
        }
      ]
    }
  }

• Hologres表的DDL語句，如下所示。

  begin;
  drop table if exists holo_reader_basic_src;
  create table holo_reader_basic_src(
    tag text not null, 
    id int not null, 
    title text not null, 
    body text, 
    primary key (tag, id));
    call set_table_property('holo_reader_basic_src', 'orientation', 'column');
    call set_table_property('holo_reader_basic_src', 'shard_count', '3');
  commit;

配置分區表

• 配置從記憶體產生的資料同步至Hologres分區表的子表。

  說明

  請注意partition的配置。

  {
    "type":"job",
    "version":"2.0",//版本號碼。
    "steps":[
      {
        "stepType":"holo",//外掛程式名。
        "parameter":{
          "endpoint": "instance-id-region-endpoint.hologres.aliyuncs.com:port",
          "accessId": "***************", //訪問Hologres的accessId。
          "accessKey": "*******************", //訪問Hologres的accessKey。
          "database": "postgres",
          "table": "holo_reader_basic_****",
          "partition": "tag=foo",
          "column" : [
            "*"
          ],
          "fetchSize": "100"
        },
        "name":"Reader",
        "category":"reader"
      },
      {
        "stepType":"stream",
        "parameter":{},
        "name":"Writer",
        "category":"writer"
      }
    ],
    "setting":{
      "errorLimit":{
        "record":"0"//錯誤記錄數。
      },
      "speed":{
        "throttle":true,//當throttle值為false時，mbps參數不生效，表示不限流；當throttle值為true時,表示限流。
        "concurrent":1,//作業並發數。
                    "mbps":"12"//限流，此處1mbps = 1MB/s。
      }
    },
    "order":{
      "hops":[
        {
          "from":"Reader",
          "to":"Writer"
        }
      ]
    }
  }

• Hologres表的DDL語句，如下所示。

  begin;
  drop table if exists holo_reader_basic_part_src;
  create table holo_reader_basic_part_src(
    tag text not null, 
    id int not null, 
    title text not null, 
    body text, 
    primary key (tag, id))
    partition by list( tag );
    call set_table_property('holo_reader_basic_part_src', 'orientation', 'column');
    call set_table_property('holo_reader_basic_part_src', 'shard_count', '3');
  commit;
  
  create table holo_reader_basic_part_src_1583161774228 partition of holo_reader_basic_part_src for values in ('foo');
  
  # 確保分區表子表已經建立且匯入資料。
  postgres=# \d+ holo_reader_basic_part_src
                           Table "public.holo_reader_basic_part_src"
   Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
  --------+---------+-----------+----------+---------+----------+--------------+-------------
   tag    | text    |           | not null |         | extended |              | 
   id     | integer |           | not null |         | plain    |              | 
   title  | text    |           | not null |         | extended |              | 
   body   | text    |           |          |         | extended |              | 
  Partition key: LIST (tag)
  Indexes:
      "holo_reader_basic_part_src_pkey" PRIMARY KEY, btree (tag, id)
  Partitions: holo_reader_basic_part_src_1583161774228 FOR VALUES IN ('foo')

配置非分區表

• 配置從記憶體產生的資料匯入至Hologres普通表，樣本為通過JDBC模式匯入的配置。

  {
      "type": "job",
      "version": "2.0",
      "steps": [
          {
              "stepType": "mysql",
              "parameter": {
                  "envType": 0,
                      "datasource": "<mysql_source_name>",
                  "column": [
                      "<column1>",
                      "<column2>",
                      ......,
                      "<columnN>"
                  ],
                  "connection": [
                      {
                          "datasource": "<mysql_source_name>",//mysql資料來源名
                          "table": [
                              "<mysql_table_name>"
                          ]
                      }
                  ],
                  "where": "",
                  "splitPk": "",
                  "encoding": "UTF-8"
              },
              "name": "Reader",
              "category": "reader"
          },
          {
              "stepType": "holo",
              "parameter": {
                  "maxConnectionCount": 9,
                  "datasource": "<holo_sink_name>",//Hologres資料來源名稱
                  "truncate":true,//清理規則。
                  "conflictMode": "ignore",
                  "envType": 0,
                  "column": [
                      "<column1>",
                      "<column2>",
                      ......,
                      "<columnN>"
                  ],
                  "table": "<holo_table_name>"
              },
              "name": "Writer",
              "category": "writer"
          }
      ],
      "setting": {
          "executeMode": null,
          "errorLimit": {
              "record": ""
          },
          "speed": {
              "concurrent": 2,//作業並發數
              "throttle": false//限流
          }
      },
      "order": {
          "hops": [
              {
                  "from": "Reader",
                  "to": "Writer"
              }
          ]
      }
  }

• Hologres表的DDL語句，如下所示。

  begin;
  drop table if exists mysql_to_holo_test;
  create table mysql_to_holo_test(
    tag text not null,
    id int not null,
    body text not null,
    brrth date,
    primary key (tag, id));
    call set_table_property('mysql_to_holo_test', 'orientation', 'column');
    call set_table_property('mysql_to_holo_test', 'distribution_key', 'id');
    call set_table_property('mysql_to_holo_test', 'clustering_key', 'birth');
  commit;

配置分區表

• 配置從記憶體產生的資料同步至Hologres分區表的子表。

  {
    "type": "job",
    "version": "2.0",
    "steps": [
      {
        "stepType": "mysql",
        "parameter": {
          "envType": 0,
          "datasource": "<mysql_source_name>",
          "column": [
            "<column1>",
            "<column2>",
              ......,
            "<columnN>"
          ],
          "connection": [
            {
              "datasource": "<mysql_source_name>",
              "table": [
                "<mysql_table_name>"
              ]
            }
          ],
          "where": "",
          "splitPk": "<mysql_pk>",//mysql的pk欄位
          "encoding": "UTF-8"
        },
        "name": "Reader",
        "category": "reader"
      },
      {
        "stepType": "holo",
        "parameter": {
          "maxConnectionCount": 9,
          "partition": "<partition_key>",//Hologres分區鍵
          "datasource": "<holo_sink_name>",//Hologres資料來源名
          "conflictMode": "ignore",
          "envType": 0,
          "column": [
            "<column1>",
            "<column2>",
              ......,
            "<columnN>"
          ],
          "table": "<holo_table_name>"
        },
        "name": "Writer",
        "category": "writer"
      }
    ],
    "setting": {
      "executeMode": null,
      "errorLimit": {
        "record": ""
      },
      "speed": {
        "concurrent": 2,//作業並發數
        "throttle": false//限流
      }
    },
    "order": {
      "hops": [
        {
          "from": "Reader",
          "to": "Writer"
        }
      ]
    }
  }

• Hologres表的DDL語句，如下所示。

  BEGIN;
  CREATE TABLE public.hologres_parent_table(
    a text ,
    b int,
    c timestamp,
    d text,
    ds text,
    primary key(ds,b)
    )
    PARTITION BY LIST(ds);
  CALL set_table_property('public.hologres_parent_table', 'orientation', 'column');
  CREATE TABLE public.holo_child_1 PARTITION OF public.hologres_parent_table FOR VALUES IN('20201215');
  CREATE TABLE public.holo_child_2 PARTITION OF public.hologres_parent_table FOR VALUES IN('20201216');
  CREATE TABLE public.holo_child_3 PARTITION OF public.hologres_parent_table FOR VALUES IN('20201217');
  COMMIT;