Hologres使用说明

Hologres使用说明

Hologres作为源表（代替kafka，有开窗需求时，无法替代kafka）

CREATE TEMPORARY TABLE test_sanhongbo_1 (
  id bigint NOT NULL,
  `a` varchar NOT NULL
) 
with (
  'connector' = 'hologres',
  'dbname' = 'bigdata01',
  'tablename' = 'goodme_dmp.test_sanhongbo_1',
  'username' = '*********',
  'password' = '*******************',
  'endpoint' = 'hgprecn-cn-tl32b9p7800i-cn-hangzhou-vpc.hologres.aliyuncs.com:80',
  'binlog' = 'true', -- 开启binlog
  --'cdcMode' = 'true',
  'binlogMaxRetryTimes' = '3',
  'binlogRetryIntervalMs' = '5000',
  'binlogBatchReadSize' = '100', 
  --'upsertSource' = 'true',
  'binlogStartupMode' = 'timestamp',
  'startTime' = '2023-02-08 00:00:00'
);

Hologres作为结果表，实现部分字段更新

1.创建结果表的实体表

CREATE TABLE goodme_dmp.test_sanhongbo_3 (
    id bigint NOT NULL,
    a text,
    b text
    ,PRIMARY KEY (id)
);

CALL set_table_property('goodme_dmp.test_sanhongbo_3', 'bitmap_columns', 'a,b');
CALL set_table_property('goodme_dmp.test_sanhongbo_3', 'dictionary_encoding_columns', 'a:auto,b:auto');
CALL set_table_property('goodme_dmp.test_sanhongbo_3', 'time_to_live_in_seconds', '3153600000');
CALL set_table_property('goodme_dmp.test_sanhongbo_3', 'storage_format', 'orc');
CALL set_table_property('goodme_dmp.test_sanhongbo_3', 'table_group', 'bigdata01_tg_default');
CALL set_table_property('goodme_dmp.test_sanhongbo_3', 'distribution_key', 'id');
CALL set_table_property('goodme_dmp.test_sanhongbo_3', 'orientation', 'column');

COMMENT ON TABLE goodme_dmp.test_sanhongbo_3 IS '测试-伞宏博';
ALTER TABLE goodme_dmp.test_sanhongbo_3 OWNER TO p4_209220275674912540;


END;

2.创建结果表的映射表（flink sql）

-- 结果表
-- 映射表1
CREATE TEMPORARY TABLE if not exists test_sanhongbo_3_1 (
  id bigint NOT NULL,
  `a` varchar,
  PRIMARY KEY(id) NOT ENFORCED
) 
WITH (
  'connector' = 'hologres',
  'dbname' = 'bigdata01',
  'tablename' = 'goodme_dmp.test_sanhongbo_3',
  'username' = '*********',
  'password' = '*********',
  'endpoint' = 'hgprecn-cn-tl32b9p7800i-cn-hangzhou-vpc.hologres.aliyuncs.com:80',
  'mutatetype' = 'insertOrUpdate',
  'ignoredelete' = 'true',
  'useRpcMode' = 'true'
);

-- 映射表2
CREATE TEMPORARY TABLE if not exists test_sanhongbo_3_2 (
  id bigint NOT NULL,
  `b` varchar,
  PRIMARY KEY(id) NOT ENFORCED
) 
WITH (
  'connector' = 'hologres',
  'dbname' = 'bigdata01',
  'tablename' = 'goodme_dmp.test_sanhongbo_3',
  'username' = '*********',
  'password' = '*********',
  'endpoint' = 'hgprecn-cn-tl32b9p7800i-cn-hangzhou-vpc.hologres.aliyuncs.com:80',
  'mutatetype' = 'insertOrUpdate',
  'ignoredelete' = 'true',
  'useRpcMode' = 'true'
);

3.向映射表中插入数据

BEGIN STATEMENT SET;

insert into test_sanhongbo_3_1
  (id, `a`)
  SELECT 
    id,
    `a`
  from
    test_sanhongbo_1;

insert into test_sanhongbo_3_2
  (id, `b`)
  SELECT 
    id,
    `b`
  from
    test_sanhongbo_2;

end;

-- test_sanhongbo_1 test_sanhongbo_2 是两张 以Hologres存储介质 的flink sql 映射表

效果展示

-- 向Hologres源表test_sanhongbo_1、test_sanhongbo_2分别插入一条数据
INSERT INTO  goodme_dmp.test_sanhongbo_1 VALUES ('22222', 'aaaaaaa');
INSERT INTO  goodme_dmp.test_sanhongbo_2 VALUES ('22222', 'ccccccc');

结果表查询：