在Hologres中如何使用SQL实现INSERT OVERWRITE的功能 - 实时数仓Hologres

Hologres V2.0版本开始支持INSERT OVERWRITE存储过程，方便用户进行大批量数据的全量写入或者分区级数据批量写入。本文为您介绍在Hologres中如何使用存储过程实现INSERT OVERWRITE功能，如您实例版本低于V2.0，请升级实例，详情请参见实例升级。如您暂时不方便升级，也可使用临时表方式实现NSERT OVERWRITE功能。

使用限制

不支持使用此命令向有视图或物化视图依赖的表中导入数据。

使用存储过程实现INSERT OVERWRITE功能

命令格式

call hg_insert_overwrite('holo_tablename' regclass, ['partition_value' text|int|varchar|date], 'sql' text);

参数说明

参数	说明
holo_tablename	Hologres的内部表。即数据目标存储表，表必须已经存在，如果是分区表，需要指定`partition_value`。
partition_value	分区表的分区值。 `holo_tablename`为分区表时指定。支持将INT、TEXT、VARCHAR或DATE类型数据作为分区值。
sql	标准的SELECT语句。可用来查询MaxCompute或者Hologres的表，需确保SELECT出来的分区字段值必须完全等于`partition_value`。如果SQL语句中含有单引号（''），需要通过`$$sql$$`改写sql，以自动实现单引号转义。

使用示例

场景一：使用存储过程向Hologres的非分区表导入数据

-- 创建表A作为目标表

begin;

CREATE TABLE public.tableA (
    cid integer NOT NULL,
    cname text,
    code integer
    ,PRIMARY KEY (cid)
);

CALL set_table_property('public.tableA', 'orientation', 'column');
CALL set_table_property('public.tableA', 'storage_format', 'orc');
CALL set_table_property('public.tableA', 'bitmap_columns', 'cname');
CALL set_table_property('public.tableA', 'dictionary_encoding_columns', 'cname:auto');
CALL set_table_property('public.tableA', 'distribution_key', 'cid');
CALL set_table_property('public.tableA', 'time_to_live_in_seconds', '3153600000');
commit;

-- 创建表B作为数据输入

CREATE TABLE public.tableB (
    cid integer NOT NULL,
    cname text,
    code integer
    ,PRIMARY KEY (cid)
);

INSERT into public.tableB values(1,'aaa',10001),(2,'bbb','10002');

-- 使用hg_insert_overwrite 将表B数据插入表A

call hg_insert_overwrite('public.tableA' , 'select * from public.tableB;');

场景二：使用存储过程向Hologres的分区表导入数据

-- 创建表A作为目标表

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

-- 创建表B作为数据输入

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

insert into public.holo_child_3a values('a',1,'2034-10-19','a','20201215');
insert into public.holo_child_3b values('b',2,'2034-10-20','b','20201216');
insert into public.holo_child_3c values('c',3,'2034-10-21','c','20201217');

-- 使用insert overwrite 将表B数据插入表A

call hg_insert_overwrite('public.tableA' , '20201215',$$select * from public.tableB where ds='20201215'$$);

场景三：使用存储过程将MaxCompute的表数据导入Hologres

使用存储过程将MaxCompute表数据导入Hologres，详情请参见使用存储过程将数据导入Hologres内部表（Beta）。

使用临时表实现INSERT OVERWRITE功能

命令格式

您可以使用如下SQL语句实现INSERT OVERWRITE的功能。

BEGIN ;

-- 清理潜在的临时表
DROP TABLE IF EXISTS <table_new>;

-- 创建临时表
SET hg_experimental_enable_create_table_like_properties=on;
CALL HG_CREATE_TABLE_LIKE ('<table_new>', 'select * from <table>');

COMMIT ;

-- 向临时表插入数据
INSERT INTO <table_new> [( <column> [, ...] )]
VALUES ( {<expression>}  [, ...] )
[, ...] | <query>}

ANALYZE <table_new>;

BEGIN ;

-- 删除旧表
DROP TABLE IF EXISTS  <table>;

-- 临时表改名
ALTER TABLE <table_new> RENAME TO <table>;

COMMIT ;

参数说明

参数	说明
table_new	新创建的临时表名称。表名称也可以使用`Schema.Table`格式。
table	已存在的表名称。表名称也可以使用`Schema.Table`格式。
临时表DDL	创建临时表有如下两种方式。通过复制已有表创建新表的结构 `SET hg_experimental_enable_create_table_like_properties=on; CALL HG_CREATE_TABLE_LIKE ('<table_new>', 'select * from <table>');` 新建表的结构 `CREATE TABLE IF NOT EXISTS <table_new> ([ { column_name column_type [column_constraints, [...]] \| table_constraints [, ...] } ]); CALL set_table_property('<table_new>', property, value);`

使用示例

场景一：MaxCompute向Hologres的非分区表导入数据

在MaxCompute向Hologres导入数据的场景中，希望将数据全量覆盖，常见于离线加工后的结果表导出为线上服务表。此场景使用示例如下所示，将MaxCompute中的odps_region_10g表的数据写入Hologres的region表中，且将Hologres中region表的数据全量覆盖。

BEGIN ;

-- 清理潜在的临时表
DROP TABLE IF EXISTS public.region_new;

-- 创建临时表
SET hg_experimental_enable_create_table_like_properties=on;
CALL HG_CREATE_TABLE_LIKE ('public.region_new', 'select * from public.region');
COMMIT ;

-- 向临时表插入数据
INSERT INTO public.region_new
SELECT *
FROM public.odps_region_10g;

ANALYZE public.region_new;

BEGIN ;

-- 删除旧表
DROP TABLE IF EXISTS public.region;

-- 临时表改名
ALTER TABLE IF EXISTS public.region_new RENAME TO region;

COMMIT ;

场景二：MaxCompute向Hologres的分区表导入数据

在每天定期更新MaxCompute分区表的数据，且需要将MaxCompute分区表向Hologres的分区表导入数据的场景中，希望将数据全量覆盖，实现离线数据对实时数据的修正。此场景使用示例如下所示，将MaxCompute中的odps_lineitem_10g表的数据写入Hologres的lineitem表中，且全量覆盖Hologres中lineitem表的数据，两个表都是按照ds字段按天分区。

BEGIN ;

-- 清理潜在的临时表
DROP TABLE IF EXISTS public.lineitem_new_20210101;

-- 创建临时表
SET hg_experimental_enable_create_table_like_properties=on;
CALL HG_CREATE_TABLE_LIKE ('public.lineitem_new_20210101', 'select * from public.lineitem');
COMMIT ;

-- 向临时表插入数据
INSERT INTO public.lineitem_new_20210101
SELECT *
FROM public.odps_lineitem_10g
WHERE DS = '20210101'

ANALYZE public.lineitem_new_20210101;

BEGIN ;

-- 删除旧分区
DROP TABLE IF EXISTS public.lineitem_20210101;

-- 临时表改名
ALTER TABLE public.lineitem_new_20210101 RENAME TO lineitem_20210101;

-- 将临时表绑定至指定分区表
ALTER TABLE public.lineitem ATTACH PARTITION lineitem_20210101 FOR VALUES IN ('20210101');

COMMIT ;

场景三：Hologres向MaxCompute的非分区表导入数据

如果您需要从Hologres向MaxCompute的非分区表导入数据，建议采用临时表导入的方式，导入完成后将临时表改名为正式表即可。此场景使用示例如下所示，将Hologres中holotable表的数据写入MaxCompute的mc_holotable表中，且将MaxCompute的mc_holotable表数据全量覆盖。

-- 在MC中创建目标表的临时表
CREATE  TABLE if not exists mc_holotable_temp(
    age int,
    job string,
    name string
);

-- 在Hologres中创建临时表的映射
CREATE FOREIGN TABLE "public"."mapping_holotable_temp" (
 "age" int,
 "job" text,
 "name" text
)
SERVER odps_server
OPTIONS (project_name 'DLF_test',table_name 'mc_holotable_temp');
-- 在Hologres中更新原始表
update holotable set "job" = 'president' where "name" = 'Lily';
-- 将更新后的数据写入临时表的映射
insert into mapping_holotable_temp select * from holotable;

-- 在MaxCompute中删除旧的目标表
DROP TABLE IF EXISTS mc_holotable;
-- 临时表更名为目标表即可
ALTER TABLE mc_holotable_temp RENAME TO mc_holotable;

说明

导入数据支持部分导入和全表导入两种方式：

导出部分字段示例：

insert into mapping_holotable_temp
select x,x,x from holotable;  --x,x,x可以替换为您需要导出的字段名

导出全部字段示例：

insert into mapping_holotable_temp
select * from holotable;