Hologres：INSERT OVERWRITE

功能說明

• Hologres V3.1版本起支援原生INSERT OVERWRITE文法。

• 原生INSERT OVERWRITE文法針對不同類型的表：

  • 支援普通表，即非分區表。

  • 支援物理分區表的分區子表（做普通表處理），不支援物理分區表的分區父表。

  • 支援邏輯分區表，但必須指定分區。

使用限制

• 原生INSERT OVERWRITE文法會預設開啟混合DML事務：SET hg_experimental_enable_transaction = on;，Hologres的事務能力詳情，請參見SQL事務能力。

  • 同一事務中，不支援INSERT OVERWRITE和DDL混合。

  • 同一事務中，所有DML將在事務完成時，即執行COMMIT時，真正提交。

• 原生INSERT OVERWRITE文法不支援產生Binlog，對於已開啟Binlog的表。需要Session層級關閉Binlog，命令為SET hg_experimental_generate_binlog = off;。

• 原生INSERT OVERWRITE執行期間，預設保證表資料讀取的原子性，但表資料讀取會涉及更大的中繼資料開銷，可能導致SQL延時增加。Follower計算群組相較Leader計算群組，DQL延時受影響更大。如果原表Insert Overwrite長期未執行完成，表的讀取任務可能會出現報錯Data version is inconsistent或者Insert overwrite version not match。

  • 如果不需保證資料讀取的原子性，可針對DQL任務關閉GUC（set hg_experimental_enable_check_data_version=off），則DQL任務掃描的資料檔案可能部分為INSERT OVERWRITE執行前的資料檔案、部分為執行後的資料檔案。

  • 如果需要保證資料讀取的原子性，但需要減少DQL延時，建議直接使用Leader計算群組資源執行對應DQL任務。

命令格式

INSERT OVERWRITE <target_table_name> 
  [ PARTITION (<partition_key> = '<partition_value>') [, ...]]
  VALUES ( <expression>  [, ...] ) [, ...] | <query>;

參數說明

使用樣本

-- 建立表A作為目標表
CREATE TABLE public.tablea (
    cid INTEGER NOT NULL,
    cname TEXT,
    code INTEGER
    ,PRIMARY KEY (cid)
);

-- 建立表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');

-- 使用原生Insert Overwrite文法將表B資料插入表A
INSERT OVERWRITE public.tablea SELECT * FROM public.tableb;

-- 建立表A作為目標邏輯分區表
CREATE TABLE public.tablea(
  a TEXT , 
  b INT, 
  c TIMESTAMP, 
  d TEXT,
  ds TEXT,
  PRIMARY KEY(ds,b)
  )
  LOGICAL PARTITION BY LIST(ds);

-- 建立物理分區表B作為資料輸入
BEGIN;
CREATE TABLE public.tableb(
  a TEXT, 
  b INT, 
  c TIMESTAMP, 
  d TEXT,
  ds TEXT,
  PRIMARY KEY(ds,b)
  )
  PARTITION BY LIST(ds);
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
INSERT OVERWRITE public.tablea PARTITION (ds = '20201215') SELECT * FROM public.tableb WHERE ds='20201215';

功能說明

• Hologres自V3.1版本開始，支援邏輯分區表，預存程序（hg_insert_overwrite）也支援邏輯分區表，但在使用時需要明確指定分區。

• Hologres V3.0版本增強了hg_insert_overwrite能力，支援通過INSERT OVERWRITE命令直接匯入資料至分區父表。

• 自Hologres V2.0.15版本開始，支援通過set hg_experimental_hg_insert_overwrite_enable_view=on;命令開啟GUC，實現向有視圖依賴的表中匯入資料；暫不支援向有物化視圖依賴的表中匯入資料。

  自Hologres V3.0版本開始，無需設定上述GUC，即可支援向有視圖依賴的表中匯入資料；暫不支援向有物化視圖依賴的表中匯入資料。

• 使用預存程序（hg_insert_overwrite）匯入失敗時，可能會導致暫存資料表的殘留情況發生。Hologres V3.0版本前，需要手動清理暫存資料表；Hologres V3.0版本起，可以通過如下SQL清理暫存資料表。

  --- 刪除before_time之前系統建立的暫存資料表
  CALL hg_clean_insert_overwrite_tmp_tables(before_time::timestamptz); 

使用限制

• 如果選擇部分欄位匯入，欄位順序需要與源表保持一致且一一對應。

• 由於hg_insert_overwrite需要以表Owner的身份建立一張暫存資料表，因此僅Superuser和表的Owner有許可權執行hg_insert_overwrite操作。

• 目標表的分區鍵支援INT、TEXT或VARCHAR類型。

• Hologres V3.0版本起，明確要求不能在事務中使用hg_insert_overwrite，執行會報錯。

  說明

  在舊版本的事務中使用預存程序（hg_insert_overwrite），特定情況下會發生死結、卡住等潛在問題，而新版本對此將採取更加嚴格的措施。

• Hologres V3.0版本起，預存程序（hg_insert_overwrite）中select_query指定列的數量、資料類型均需和target_table的列嚴格對應，否則會報錯“error: table "hg_alias" has x columns available but x columns specified”，或“error: column xx is of type xxx but expression is of type xxx”。

行為變更

自Hologres V3.0版本起，預存程序（hg_insert_overwrite）有如下行為變更：

僅有target_table和select_query兩個入參時，且目標表是分區父表，則Hologres V3.0版本前會直接報錯。自Hologres V3.0版本起，存在以下可能：

• 當select_query執行結果對應的分區子表都已存在時，寫入成功。

• 當select_query執行結果對應的分區子表不存在時，可能報錯。

命令格式

-- V3.0版本前的hg_insert_overwrite文法
CALL hg_insert_overwrite('<target_table>' regclass, ['<partition_value>' TEXT], '<sql>' TEXT);

-- V3.0及以上版本的hg_insert_overwrite文法
CALL hg_insert_overwrite('<target_table>' regclass, ['<partition_value>' ARRAY], '<sql>' TEXT, ['<auto_create_partition>' BOOLEAN]);

參數說明

V3.0版本起，針對INSERT OVERWRITE分區父表，即target_table為分區父表的情況，不同參數設定的行為如下：

• 物理分區表

  參數取值		auto_create_partition
  		TRUE	FALSE
  partition_value	不指定	sql執行結果對應的target_table分區，全部執行資料覆寫。如果有不存在的分區子表，則先自動建立分區。 與sql執行結果無關的target_table分區，忽略。	sql執行結果中，如果對應的target_table分區都存在： 執行結果對應的target_table分區，全部執行資料覆寫。 與執行結果無關的target_table分區，忽略。 sql執行結果中，如果有對應的target_table分區不存在，則直接報錯，其餘已存在的分區也不執行覆寫。
  	指定	對於partition_value指定的target_table分區： 如果分區實際不存在：自動建立分區。 sql執行結果對應的分區：執行資料覆寫。 與sql執行結果無關的分區：直接清空。 對於partition_value未指定的target_table分區： sql執行結果如果包含未指定的分區：不處理。 與sql執行結果無關的分區：不處理。	對於partition_value指定的target_table分區： 如果分區實際不存在：直接報錯，其餘分區也不執行覆寫。 sql執行結果對應的分區：執行資料覆寫。 與sql執行結果無關的分區：直接清空。 對於partition_value未指定的target_table分區： sql執行結果如果包含未指定的分區：不處理。 與sql執行結果無關的分區：不處理。

• 邏輯分區表：

  邏輯分區表不涉及自動建立分區（auto_create_partition），忽略此參數。

  參數取值		說明
  partition_value	不指定	不支援。
  	指定	對於partition_value指定的target_table分區： sql執行結果對應的分區：執行資料覆寫。 與sql執行結果無關的分區：直接清空。 對於partition_value未指定的target_table分區： sql執行結果如果包含未指定的分區：不處理。 與sql執行結果無關的分區：不處理。

使用樣本

-- 建立表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');

-- 建立表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;

-- 或者為邏輯分區表
CREATE TABLE public.tableA_lp(
  a TEXT, 
  b INT, 
  c TIMESTAMP, 
  d TEXT,
  ds TEXT,
  PRIMARY KEY(ds,b)
  )
  LOGICAL PARTITION BY LIST(ds);

-- 建立表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');

-- 物理分區表
CALL hg_insert_overwrite('public.tableA' , '{20201215,20201216,20201217}'::text[],$$SELECT * FROM public.tableB$$);
-- 邏輯分區表
CALL hg_insert_overwrite('public.tableA_lp' , '{20201215,20201216,20201217}'::text[],$$SELECT * FROM public.tableB$$);

-- 在MaxCompute中建立一張非分區表。樣本選用MaxCompute公告資料集public_data專案下的customer表資料，其表DDL如下。
CREATE TABLE IF NOT EXISTS public_data.customer(
  c_customer_sk BIGINT,
  c_customer_id STRING,
  c_current_cdemo_sk BIGINT,
  c_current_hdemo_sk BIGINT,
  c_current_addr_sk BIGINT,
  c_first_shipto_date_sk BIGINT,
  c_first_sales_date_sk BIGINT,
  c_salutation STRING,
  c_first_name STRING,
  c_last_name STRING,
  c_preferred_cust_flag STRING,
  c_birth_day BIGINT,
  c_birth_month BIGINT,
  c_birth_year BIGINT,
  c_birth_country STRING,
  c_login STRING,
  c_email_address STRING,
  c_last_review_date STRING,
  useless STRING);

-- 在Hologres中建立一張外部表格，用於映射MaxCompute中的源頭資料表。
CREATE FOREIGN TABLE customer (
    "c_customer_sk" INT8,
    "c_customer_id" TEXT,
    "c_current_cdemo_sk" INT8,
    "c_current_hdemo_sk" INT8,
    "c_current_addr_sk" INT8,
    "c_first_shipto_date_sk" INT8,
    "c_first_sales_date_sk" INT8,
    "c_salutation" TEXT,
    "c_first_name" TEXT,
    "c_last_name" TEXT,
    "c_preferred_cust_flag" TEXT,
    "c_birth_day" INT8,
    "c_birth_month" INT8,
    "c_birth_year" INT8,
    "c_birth_country" TEXT,
    "c_login" TEXT,
    "c_email_address" TEXT,
    "c_last_review_date" TEXT,
    "useless" TEXT
)
SERVER odps_server
OPTIONS (project_name 'public_data', table_name 'customer');

-- 在Hologres中建立一張內部表（以列存表為例），用於接收MaxCompute源頭表資料。
BEGIN;
CREATE TABLE public.holo_customer (
 "c_customer_sk" INT8,
 "c_customer_id" TEXT,
 "c_current_cdemo_sk" INT8,
 "c_current_hdemo_sk" INT8,
 "c_current_addr_sk" INT8,
 "c_first_shipto_date_sk" INT8,
 "c_first_sales_date_sk" INT8,
 "c_salutation" TEXT,
 "c_first_name" TEXT,
 "c_last_name" TEXT,
 "c_preferred_cust_flag" TEXT,
 "c_birth_day" INT8,
 "c_birth_month" INT8,
 "c_birth_year" INT8,
 "c_birth_country" TEXT,
 "c_login" TEXT,
 "c_email_address" TEXT,
 "c_last_review_date" TEXT,
 "useless" TEXT
);
COMMIT;

-- 匯入資料至Hologres。
IMPORT FOREIGN SCHEMA <project_name> LIMIT TO
(customer) FROM server odps_server INTO PUBLIC options(if_table_exist 'update');--更新外部表格
SELECT pg_sleep(30);--等待一些時間再匯入Hologres，以防Hologres meta資訊更新緩衝慢導致的資料不一致而同步不成功

CALL  hg_insert_overwrite('holo_customer', 'SELECT * FROM customer where c_birth_year > 1980');

-- 在Hologres中查詢MaxCompute源表中的資料。
SELECT * FROM holo_customer limit 10;

-- 在MaxCompute中建立一張分區表。
DROP TABLE IF EXISTS odps_sale_detail;

CREATE TABLE IF NOT EXISTS odps_sale_detail 
(
    shop_name STRING
    ,customer_id STRING
    ,total_price DOUBLE
)
PARTITIONED BY 
(
    sale_date STRING
)
;

-- 向源表增加分區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)
;

-- 在Hologres中建立一張外部表格，用於映射MaxCompute中的源頭資料表。
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');

-- 在Hologres中建立一張內部表，用於接收MaxCompute源頭表資料。
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;

-- 匯入資料至Hologres。
CALL hg_insert_overwrite('holo_sale_detail', '20210815', $$SELECT * FROM public.odps_sale_detail WHERE sale_date='20210815'$$);

-- 在Hologres中查詢MaxCompute源表中的資料。
SELECT * FROM holo_sale_detail;

-- 在MaxCompute中建立一張分區表。
DROP TABLE IF EXISTS odps_sale_detail;

CREATE TABLE IF NOT EXISTS odps_sale_detail 
(
    shop_name STRING
    ,customer_id STRING
    ,total_price DOUBLE
)
PARTITIONED BY 
(
    sale_date STRING
)
;

-- 向源表增加分區20210815和20210816
ALTER TABLE odps_sale_detail ADD IF NOT EXISTS PARTITION(sale_date='20210815')
;
ALTER TABLE odps_sale_detail ADD IF NOT EXISTS PARTITION(sale_date='20210816')
;

-- 向分區寫入資料
INSERT OVERWRITE TABLE odps_sale_detail PARTITION(sale_date='20210815') VALUES 
('s1','c1',100.1),
('s2','c2',100.2),
('s3','c3',100.3)
;
INSERT OVERWRITE TABLE odps_sale_detail PARTITION(sale_date='20210816') VALUES 
('s1','c1',100.1),
('s2','c2',100.2),
('s3','c3',100.3)
;

-- 在Hologres中建立一張外部表格，用於映射MaxCompute中的源頭資料表。
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');

-- 在Hologres中建立一張內部表，用於接收MaxCompute源頭表資料。
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;

-- 匯入資料至Hologres。不指定分區子表且auto_create_partition為TRUE，系統會自動建立兩個分區子表並匯入資料
CALL hg_insert_overwrite ('holo_sale_detail', $$SELECT * FROM public.odps_sale_detail$$, TRUE);

-- 在Hologres中查詢資料。
SELECT * FROM holo_sale_detail;

命令格式

您可以使用如下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 ;

參數說明

使用樣本

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 ;

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 ;

-- 在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;